This article was originally published over at Startup Rocket here, and written by Will Little and Troy Henikoff.
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. Feel free to ping us on Twitter (@wclittle, @troyhenikoff) with any questions.
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.
Download a Copy of the Financial Modeling Presentation
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).
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):
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).
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.
And on your annual cash flow tab, for example, we suggest adding add a helpful row that reveals your lowest balance of the year:
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).
Walking a potential investor through this model to show both the past performance and forecasted numbers in one place is, therefore, an immensely powerful tool.
As a homework assignment, for those interested, see if you can add an assumption value called “Maximum marketing spend of Cash Balance” and model in this throttle to avoid burning through too much cash. This is an example of the types of custom features you can add into your own model.