Skip to main content Skip to search

Financial Model

Financial Modeling for Startup, Final Part: The Balance Sheet, Cash Flow and Unit Economics

In the first three articles in this series, we looked at the big-picture motivation for startup financial modeling, why it’s important to start with your assumptions, and how to practically build your income statement and custom detail tabs.

Today, we’ll finish off the series by examining how to construct the final components necessary to complete your model, including a quick discussion of unit economics and how to best keep your model updated.

Download a Copy of the Example Model

To make following along this final article easier, download a free copy of our example financial model here. Remember, understanding the nuances of your own unique business is critical, so only use this (and any other model you find online) as a guide while you build your own model from scratch.

The Balance Sheet

Create a new tab entitled “Monthly Balance Sheet” and set up the 60 month columns similar to the other tabs we created thus far in the series. The balance sheet is designed to give you a quick look at your company’s assets, liabilities, and equity situation (past, present, and forecasted).

screen-shot-2016-10-05-at-1-39-22-pm

You can see that, similar to how we rolled up numbers from custom detail tabs for the income statement, right out of the gate in cell C4 here we need to calculate the expected cash at the end of month 1 by creating a cash flow tab (which we’ll do below).

But first, in your downloaded model, take a look at the rest of the cells down column C to see how the numbers are calculated from the assumption values. While explaining the details of each formula is beyond the scope of this article, it’s helpful to Google for terms like Inventory Days on Hand and Accounts Receivable Days if you are unfamiliar with them and how they are used to calculate the applicable cells on your balance sheet.

While we left these two assumptions at 30 days for our example model for teaching purposes, take a look at what happens to your balance sheet when you drop the Accounts Receivable Days to 3, which may be more realistic for an eCommerce business with credit card payments and relatively low transaction values.

Cash Flow

Swinging back around to cell C4 (month 1 cash) on your Balance Sheet, now it’s time to create a new tab called “Monthly Cash Flow” and set it up appropriately for your business.  Here is our example (with added rows we didn’t model, for you to consider):

screen-shot-2016-10-05-at-1-39-36-pm

From top to bottom, notice how Gross Sales and Change in A/R (Accounts Receivable) affects your Total Receipts, based how your Accounts Receivable Days assumption value affects your balance sheet (i.e. with our assumed values, we are modeling collecting zero cash in the first month).

Also notice how your Net Cash Flow from Operations is significantly less than your expenses, given that it takes time for money to leave your bank account to pay your vendors (i.e. our “Average Accounts Payable Days” assumption).

Finally, the Ending Cash Balance you calculate can then be used to populate your month 1 cash on your Balance Sheet tab.

Unit Economics

For many business types, it is extremely useful to create a separate tab called “Unit Economics” to calculate the forecasted Lifetime Value (LTV) of your unit (which is a “subscriber” in our example model) and Customer Acquisition Cost (CAC).

screen-shot-2016-10-05-at-1-39-49-pm

With our assumed 4% churn percentage, this means we can model that our average customer stays with us 25 months. Multiple these 25 months by the Gross Profit per subscription (which is also an assumption value) and that leads to our projected LTV of $213.59.

Pulling in the CAC per outbound channel we are modeling (from our assumptions) allows us then to see how we are anticipating to tangibly grow the business. As long as the LTV/CAC ratio is (and remains) over 1, then your model is telling you that your business can grow.  Obviously, the higher the LTV/CAC ratio, the better.

Roll up your Income Statement, Balance Sheet and Cash Flow into Annual Summaries

Finally, to give you, your team, and your investors a helpful glance at what your projected five years look like, create separate tabs called “Annual Income Statement”, “Annual Balance Sheet”, and “Annual Cash Flow” and use formulas like “=SUM(‘Monthly Income Statement’!C4:N4)” to roll up 12 months worth of values from your monthly tabs.

screen-shot-2016-10-05-at-1-40-16-pm

And on your annual cash flow tab, for example, we suggest adding add a helpful row that reveals your lowest balance of the year:

screen-shot-2016-10-05-at-1-40-35-pm

Congratulations! If you’ve been following along thus far, you now have a complete financial model for your business. If you’ve done it correctly, you’ve most likely identified many assumptions that will need to be rigorously validated, and understood the key target assumptions that must be met in order for you to build a viable business.

Keeping your Model Updated

It’s hard to overstate the importance of reconciling your model to what actually happens month over month. This will help you drill down your assumption values and face the reality of the long-term health of your business.

Practically, be sure to keep a copy of your original model for reference. Then, after you’ve reconciled your books every month, open up your “active” financial model and (1) tweak the assumptions to get the current month close to actual, then (2) plug in the actual numbers in your monthly statements (i.e. replacing the formulas from your assumption values, which will remain in place for future months and now be more accurate).

This article is cited from: Startup Financial Modeling, Part 4: The Balance Sheet, Cash Flow and Unit Economics
Read more

Financial Modeling for Startup, Part 3: The Income Statement and Custom Detail Tabs

In the previous two posts in this Financial Modeling for Startup series, we examined why building a financial model for your startup is important and how to practically get started with your assumptions tab. Today, we’ll continue by diving into the income statement and supporting tabs used to calculate your projected revenue and expenses. The final part of this series will go over cash flow, the balance sheet and keeping the model updated.

But first, in case you aren’t yet convinced that it’s worth your time to build a financial model for your business, here’s a quick video we put together showing how a model can be used to gain insight about what assumptions drive forecasted cash most significantly:

Read more

Financial Modeling for Startups, Part 2: Assumptions

In the previous post of Financial Modeling for Startups, its described what financial modeling is and why it is important for startup founders to build their own models from scratch. Now, we’ll begin by diving into how to practically start building a financial model. In coming weeks, we will also be covering the income statement as well as cash flow, balance sheet and keeping the model updated.

Good financial models are built from the bottom up. They have assumptions that flow into backup sheets that flow into monthly statements that flow into annual summaries. You literally start with the smallest component and start building up.

What is an Assumptions Tab?

The assumptions tab should be the first tab within your spreadsheet and contain variables that will be referenced from other tabs. Rather than manually enter data into your income statement, you’ll roll up variables across your assumption and detail tabs. The ONLY place you should ever type a number is into an assumption cell. Every other cell is a calculation based on the assumptions!

The types of assumptions you’ll want to define are unique for your business type. Most commonly they are related to expected revenue from each product/service you sell, expected costs, initial investment dollars in the bank, etc…

Where to Get Your Data?

Read more

Financial Modeling for Startups

We are calling all startup founders!

A startup financial model is important in raising funding, building startup financial projections or planning the future of your business. To have a startup financial model, you can learn and read other people’s financial model. Well, that’s one thing. But how about create one that professionally designed for your startup? This article will guide the Startups business for building their own financial model from scratch and ultimately communicate clearly to a great deal of investors.

What is a Financial Model?

In short, a financial model is an abstract mathematical representation of how a company works (and more importantly, how it will work going forward). The model has inputs and outputs. The inputs are the assumptions that drive the model, things like what drives your customer acquisition cost, what your churn rates are, how much you pay people, etc. The outputs are a set of projections that show how the company will perform if the assumptions are true. One model can produce multiple sets of projections given different assumptions.

Based on a set of assumptions, a financial model is used to make smart decisions (e.g. how many sales people to hire and what to pay them). The model includes financial projections that are tied mathematically to the assumptions, which allows operators to “play with the variables” in order to understand how certain decisions might affect the future health of their company.

Why Should Founders Care about Building a Financial Model?

Troy Henikoff (Techstars Chicago Managing Director) has an important story to share on this topic:

“When fundraising for SurePayroll, we had some very high level financials in the pitch deck. Inevitably, VC’s would ask where the numbers came from. I would tell them that we had a very detailed financial model that drove it, I was setting the bait…

They would ask to be sent a copy of the model and I would refuse. I would only share it by first sitting down with them and an associate and reviewing the model in person and after that 90 minute session, I would leave them a copy of the model to play with further.

They would insist that they could figure it out without the meeting, but I ALWAYS held my ground. I wanted the meeting not just to save them time and frustration learning a new model, but more importantly to get more face time with them in a situation where I was going to shine.

I knew the model inside and out since I built it; I could answer any question about any cell and look like a genius. In the end, I did eight of these meetings and EVERY ONE of the firms that did the 90 minute meeting with me on the financial model either made an investment in the company or made an offer to invest in the company. Every single one.”

Why is it Important for Founders to Build a Financial Model from Scratch?

Read more