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.
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:
$29.99
Churn on subscriptions per month:
4 percent
Page views per visit:
3.5
CPM growth per month on ads sold
10 percent
Max CPM on ads sold
$1.50
Steady State Monthly SEM Spend
$10,000
Steady State Monthly Facebook spend
$10,000
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)
$2.25
Average CPC on Facebook
$1.85
Visits per FB Post with Picture
5
Visits per FB Post with Video
25
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
$500
Incremental for each 1 million visits
$250
Orders / warehouse help person
5000
Taxes and benefits load
25 percent
Rent per employee per month
$500
Tech Cost per employee per month
$100
Legal and Accounting Cost per month
$500
Travel cost per employee per month
$250
Training Budget per employee per month
$100
Recruiting per added employee
$5,000
Finally, List out Cash and Operations Assumptions
For example:
Opening Cash Balance
$1,000,000
Raise at Month 13
$2,500,000
Average Accounts Receivable Days
30
Average Accounts Payable Days
60
Inventory Days On Hand
30
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.