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.
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.
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.
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.
This article was originally published over at Startup Rocket here. 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.
Finally, we sincerely hope you found this series helpful. Feel free to ping us on Twitter (@wclittle, @troyhenikoff) with any questions. You can also keep up Will’s writing on early-stage startup life by subscribing to his newsletter here. Thanks!
Get a head start on your own entrepreneurial journey. Apply to an accelerator program. Applications close on October 15th.
In the first two posts in this 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:
We’ll discuss these types of learning nuances in more detail in our next (and final) post in this series. But first, we have some work to do in order to fill out the model.
Start by Building your Monthly Income Statement
To dive right in, assuming you’ve already created your first tab called “Assumptions”, create a second tab called “Monthly Income Statement” and begin to set it up like this:
Copy that formula in D2 and paste it into the next 58 columns in row 2 in order to set up 60 months (i.e. 5 years).
Next, we’ll focus on revenue. Starting in row 4 you’ll want to list the high-level revenue streams for your business.
For example, with Dollar Cave Club (the demo business we introduced in the last post), we are planning on four revenue streams:
We’ll write these in cells B4-B7, and in order to begin filling in cells C4-C7, we’ll need to ask ourselves how will we calculate our forecasted revenue for month one. This, as you may suspect, requires starting a new tab.
Create a new tab called “Sales Detail” and, from top to bottom, fill in the rows needed to roll up calculations for each revenue stream. This is where your spreadsheet will become unique for your business. As a guide, however, here’s what it looks like for Dollar Cave Club:
Note that we are using the necessary sub-rows to calculate the total for each revenue stream per month (i.e. rows 11, 15, 19, and 25).
From top to bottom in month 1 (i.e. column C), you’ll want to start with your actual numbers (or assumptions if you don’t have numbers yet) and fill in each cell appropriately with formulas using your assumptions, and – you guessed it – any additional custom detail tabs you need.
Right out of the gate here with our example business, in order to calculate the number of New Subscribers in month 1 (i.e. cell C4), we’re going to need to create another custom tab around traffic numbers to handle this calculation.
In order to calculate the number of new subscribers in month one, we’re going to need to add up anticipated traffic numbers from paid search (SEM), organic search, Facebook, direct, etc… (i.e. it will, again, be custom for your business).
Here’s what it looks like for Dollar Cave Club:
If you recall our initial Assumptions from the last article, here is where you’ll pull in variables like AverageCPCGoogle (for C4’s formula), AverageCPConFACEBOOK (for C7), VIsitsPerFBPicturePost (for C10), VisitsPerFBVideoPost (for C13), DirectTrafficMultiplier (for C15), and OrganicTrafficMonthlyGrowthRate (for D16 and beyond), etc…
Importantly, after a few months of testing, we’ll assume in this model that we’ll get to SteadyStateMonthlySEMSpend (i.e. $10k) and also apply that to our Facebook channel (see G3/H3, and G6/H6). This, for the purposes of our demo model, will then carry forward through our 60 months.
Back to Sales Detail
Now that we have our Traffic Detail filled out, we can go back to our Sales Detail and finally fill out C4 there (i.e. New Subscribers).
That formula will look like:
=ROUND(‘Traffic Detail’!C4*SEMToSubscriber + ‘Traffic Detail’!C7*FacebookToSubscriber + SUM(‘Traffic Detail’!C15:C16)*OrganicToSubscriberConversion,0)
…which will then be applied to the rest of row 4 and allow us to, using the proper assumption values, calculate our Lost/Ending Subscribers and ultimately our Subscription Revenue, which is what is then rolled up to our Monthly Income Statement for the C4 cell that started us down this rabbit trail to begin with.
Thus, to summarize, the blue values below are taken from our assumption values (inputs) and the formulas are calculated like so:
The other revenue stream totals can then be calculated similarly from our traffic detail tab and assumption values, which allows us to calculate Gross Revenue numbers on our income statement.
Back to the Monthly Income Statement
Now that we have the Sales Detail tab filled out, we can roll those numbers into our forecasted revenue cells like so:
And there we have it. Copy the formulas across the 60 columns and total them up into your Gross Revenue row.
Now we can turn our gaze to costs.
The bottom rows of the Income Statement will then examine the costs associated with running our business, and – similar to how we worked it for revenue – we’ll need to create custom tabs when appropriate.
For direct costs associated with the stuff we sell (versus indirect costs like salary and rent and such), we can add them in like so:
Note the formula for C11, which adds our assumed base cost to a scale factor applied to our traffic that we pull in from our Traffic Detail page. Our Subscription and eCommerce values are then calculated from our SubscriptionMargin and eCommerceMargin assumptions.
We can continue then by tallying our Gross Profit (i.e. C8 minus C14), add in our direct costs from Google and Facebook ads (pulled in from the Traffic Detail tab), and calculate our Contribution Margin (i.e. revenue minus direct costs).
Finally, we’ll finish up the costs part of our income statement with some standard indirect costs for example purposes:
Since these costs are calculated from the number of employees we expect to have, and the details of their salaries and benefits/taxes, this warrants the creation of a new tab.
The Employees Tab
Create a new tab called – you guessed it – “Employees”, and add in the details that are custom for your business. Like any detail tab, it really can be formatted however you’d like as long as you can send back up the numbers needed, but here is an example of how we did it for our demo company:
While most of this tab is pretty straight forward (and obviously the salary numbers will adjust based on your market), one thing to note in here is how we defined “Warehouse help” to increment based on volume, like so:
=ROUND((‘Sales Detail’!C7+’Sales Detail’!C17)/OrdersPerWarehouseHelpPerson,0)
In other words, as sales increase, we’ll add warehouse help based on our assumed OrdersPerWarehouseHelpPerson value. With our current assumption values, this starts to increment in month 22, but you can see how if things pick up, we’ll need to hire this person earlier.
Finishing with EBITDA and Notable Mentions
Finally, to finish off our income statement, we’ll add in:
For those unfamiliar with the term, EBITDA stands for Earnings before interest, taxes, and amortization, which is a sufficient number to end with here for financial modeling purposes.
You can also add in notable mentions like the number of employees, capital raised, important KPIs your are tracking, etc…
Follow the Principles, Don’t Just Copy What you See
We’ll of course provide a link to the full demo spreadsheet at the end of this series. While you can (and should) study other financial models to use as additional guides, it’s critically important for you to understand the principles behind what is going on here.
In short, start with the Monthly Income Statement and then go row-by-row, cell-by-cell and create custom detail tabs (and custom sub-detail tabs) as needed to perform the necessary calculations to keep your income statement clean.
This article was originally posted on Startup Rocket here. In the next and final post in this series, we’ll conclude by building out the monthly cash flow and balance sheet, including annual roll-up tabs and a discussion of best practices to keep your model updated. Subscribe to Will’s newsletter to get notified when the final article is up. Feel free to ping us on Twitter (@wclittle, @troyhenikoff) with any questions.
In the previous post of this series, we described what financial modeling is and why it is important for startup founders to build their own models from scratch. Today, 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 entering 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
Don’t just randomly guess your expected revenue and costs. Do your homework. Often industry averages will be published online for you to look up, or you can ask people in your network who are familiar with your business and market.
In addition, study your competition carefully. The obvious low-hanging-fruit research is if they have publicly available annual reports where you can glean specific information about cost of goods, productivity of sales reps, salary info, etc… But even if the data isn’t directly public, very often helpful numbers are posted in blogs or articles online.
But be careful – just because you find out that a large public company spends 12 percent of its annual revenue in marketing, does not mean that is the right ratio for you. Most startups spend money in marketing long before they have ANY revenue, it is how they intend to get revenue – very different!
Working with an Example Company
We’ll use an example company called Dollar Cave Club:
Dollar Cave Club is the latest in a series of companies that make recurring purchases easy and painless. Sign up for our $29.99 monthly subscription and we will send you a box each month with essentials for your Man Cave. One month it is a classic poker set, the next an amazing set of beer steins. If you cannot wait to get your favorite dart board, you can come to the site directly and buy anything we sell.
We get most of our new customers from SEO, advertising on Google and Facebook. We have such a highly desirable audience that many people want to advertise to our visitors on our site as well.
Subscribers can “add-on” to their monthly subscription by selecting any items in our catalogue and shipping is free! Our churn has been around 4 percent per month and our traffic to the site is growing as well as our sales.
Here are things we know about the company:
- Subscription Price $29.99
- Churn = 4 percent Monthly
- Something about SEO, SEM and Facebook
- Add-ons to subscriptions
- Some advertising revenue
Here are things we don’t know about the company:
- What impact does selling ads on our site have?
- What impact will increasing Ad spend have?
- Can we make Ad spend more realistic? How?
- What if we raise more money?
- What impact does conversion rate have on the business?
Start by Filling in your Revenue-related Assumptions
We’ll start the assumptions tab by filling in our best guess of every aspect of our company related to revenue (obviously you’ll add/remove things as you go, but this is a good place to start):
- Subscription Price:
- Churn on subscriptions per month:
- 4 percent
- Page views per visit:
- CPM growth per month on ads sold
- 10 percent
- Max CPM on ads sold
- Steady State Monthly SEM Spend
- Steady State Monthly Facebook spend
- SEM -> Subscription Conversion
- 2.00 percent
- Facebook -> Subscriber Conversion
- 1.00 percent
- SEM -> eCommerce Conversion
- 4.50 percent
- Facebook -> eCommerce Conversion
- 2.50 percent
- Organic Traffic -> Subscriber Conversion
- 0.20 percent
- Organic Traffic -> eCommerce Conversion
- 0.50 percent
- Organic SEO Traffic monthly growth rate
- 10 percent
- Direct Traffic Multiplier
- 33 percent
- Average CPC on SEM (Google)
- Average CPC on Facebook
- Visits per FB Post with Picture
- Visits per FB Post with Video
- Subscription Margin
- 25.00 percent
- eCommerce Margin
- 30.00 percent
Next, Fill in your Cost Assumptions
Similar to revenue assumptions above, now you should list out common monthly costs like IT infrastructure, rent, legal, taxes, travel, etc…
- Internet Infrastructure base cost / mo
- Incremental for each 1 million visits
- Orders / warehouse help person
- Taxes and benefits load
- 25 percent
- Rent per employee per month
- Tech Cost per employee per month
- Legal and Accounting Cost per month
- Travel cost per employee per month
- Training Budget per employee per month
- Recruiting per added employee
Finally, List out Cash and Operations Assumptions
- Opening Cash Balance
- Raise at Month 13
- Average Accounts Receivable Days
- Average Accounts Payable Days
- Inventory Days On Hand
Assign Descriptive Labels for your Assumptions Before you Use Them
For each of your assumptions, use the feature your spreadsheet software has to label your variables. Just like in programming, make your variable names as descriptive as possible so your model can be more easily read.
For example, here’s where we defined “Page views per visit” (PagesPerVisit):
Now whenever we refer to page views per visit in the rest of our spreadsheet, we can use the label PagesPerVisit rather than the ambiguous ‘Assumptions’!B18.
Controlling the Size of your Assumptions List
As you build out the assumptions tab for your business, it will be tempting to create tons of variables that you may or may not need as you build out the rest of your financial model. Therefore, it is best to stick with a core set of revenue and cost assumptions, and add in more when you realize you need them. Just remember, again, to assign your label names before you include the variable in an equation anywhere in your spreadsheet (a common Excel gotcha).
This article was originally posted on Startup Rocket here. In the next post in this series, we’ll continue building our example financial model by diving into the income statement and custom detail tabs. Subscribe to Will’s newsletter to get notified when the next articles are up. Feel free to ping us on Twitter (@wclittle, @troyhenikoff) with any questions.
This series is the result of a friendly debate I had recently with Troy Henikoff (former Techstars Chicago Accelerator Managing Director) regarding the best approach for founders to take when building a financial model. More accurately, the “debate” was a strong adverse reaction from Troy after I shared a template I built for Prota Ventures’ portfolio companies. His feedback was, essentially, to never use a template and instead build each model from scratch.
He invited me to a 90-minute lecture he gave where he overwhelmingly convinced me and the room that, indeed, founders need to take the time necessary to build their models from scratch. After I asked him where I could find his lecture material online, he suggested we co-author this article series since there weren’t many solid resources available. We sincerely hope you find this series helpful.
Here’s What We’re Planning to Cover
Our plan is to break this out into a four-part series and guide you through the components necessary for building your own financial model from scratch:
- Part 1: The Why and What of Financial Modeling
- Part 2: Assumptions
- Part 3: Income Statement and Custom Detail Tabs
- Part 4: Cash Flow, Balance Sheet and Keeping the Model Updated
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 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?
While it’s easy to search around and find a template to use, those templates were built by someone with a particular business in mind. Since every business is unique, this will lead you into trouble.
While it’s often helpful to learn from other people’s models to ensure, for example, that you aren’t missing anything important, you should never build your model using their template. You’ll end up banging your head against a wall when you need to change things, and you’ll inevitably be confused about some nuance that will come back to haunt you since you don’t understand it.
In other words, while you may think that a template will help you save time, what you are actually doing is acquiring “technical debt” that will end up costing you more time in the long run.
Plus, it’s critical to understand every column, row, cell and tab in your spreadsheet for two key reasons; it will help you better manage your business, and when the time comes to explain it to an investor, you’ll be able to explain exactly how it works and increase your odds of landing funding.
What are the Components of a Solid Financial Model?
Since most people are using the financial model to communicate projections to investors, it is critical that you speak the investors’ language. They are used to having financials in Excel, so you should build your model in Excel.
Google sheets is convenient for making changes and having multiple people editing, but sending an investor a model in Google sheets signals that you are not financially savvy.
Investors are also used to seeing three standard statements; an income statement, a balance sheet, and a statement of cash flow. Each of these is more credible if it has BOTH the past performance and the future projections in the same spreadsheet.
Your spreadsheet should contain a tab for each of these outputs along with an “assumptions” tab and custom detail tabs needed to help calculate the main outputs. We’ll walk through a specific example later in this series so you have a better understanding of what this should look like.
Profit is a Matter of Opinion, Cash is Fact
Because of various accounting nuances – such as fixed asset depreciation and deferring revenue – if you assign ten accountants to finish your books at the end of the year, you’ll get ten different answers for how much profit (or loss) you had in the year. While hopefully not far off from the others, each will have a slightly different report of your “profit” based on their accounting opinions.
However, the balance of your bank account is a specific number to point at; it’s a fact that your ten accountants should agree on.
Therefore, it’s important to remember that your financial model will have your own opinions baked in regarding your profit. This means that examining your cash flow carefully as you fine-tune your business assumptions is critical.
Why Investors Care about your Financial Model
Having a solid financial model is a significant step in communicating to investors that you are a logical thinker with a defensible plan and clearly understand your business and the levers that drive it.
Nobody expects your model to be perfect, as a matter of fact, when we present a model, we always open with the same line:
“The only thing we know for sure about this model is that it is wrong. But, if we look critically at it we can better understand the drivers of the business and what we need to be focused on to reduce our risk.”
Keep in mind, investors are looking for the big home runs, but they are also looking at reducing their risk. The model can help them get comfortable with the risk.
– – –
This article was originally published over at Startup Rocket here. In our next post in this series, we’ll dive in a step-by-step guide of how to build a financial model, starting with the assumptions tab. Subscribe to Will’s newsletter to get notified when the next articles are up. As we mentioned above, feel free to ping us on Twitter (@wclittle, @troyhenikoff) with any questions.