For the sake of simplicity, we're going to ignore all the usual accounting nuances involved in modeling a company and just focus on keeping track of the cash. (Most of the complexities involved in accounting just arise from differences in timing of cash flows vs when certain items were "earned" or "incurred").
A typical user of a model is:
- A founder or executive who uses it to manage the company, or
- An investor who uses it to evaluate the potential risks and rewards that an investment in the company might entail
A financial model has many uses, including to:
- Validate the economics of your business. Are you sure you can make more money than you spend in the long run?
- Find out when you expect to run out of cash, and how much cash you will need until your business is self-sustaining. After entering assumptions about income, expenses, and capital raised, you will start to have a clear picture about how cash flows in and out of your business.
- Serve as a key piece of the pitch to an investor. A well-crafted model shows how you will make money, how much money you will need to build the business, what the expected timeline is, and who you will need to hire to get there. It also helps quantify potential return on investment.
- Show investors that you are rigorous about managing your business. A thoughtful model can drive confidence that you are going to be effectively deploying their capital.
- Test key assumptions. How does the timing of hires affect how much cash you need to raise? What is the impact of a 6 month delay in product launch?
- Clarify your thinking about the business. In order to build a model, you are forced to concretely write down all of your assumptions about the business that may have just been floating around in your mind until now. For example, will you be able to charge customers $500 for your product, or $5,000? Where that number ends up being will have a drastic impact on your company.
- Track your performance over time. A model can help enforce some discipline. Are you where you expected to be 3 months ago? Why or why not?
The sections shown on this summary page include:
- Income Statement. Shows how much money we expect to make, how much we expect to spend, and our Net Income (by simply subtracting Expenses from Revenue).
- Operating Metrics. Summarizes some of the interesting metrics from our projections, which were used to produce the Income Statement (which we'll see shortly).
- Cash Summary. Shows how much cash we expect to have on hand at the end of each period, along with how much investment capital we expect to raise.
Each row represents either:
- A label
- A cash inflow or outflow over time, which is summed over the indicated period. For example, cell G8 above shows the sum of Revenue earned from Jan 1, 2019, through Dec 31, 2019, or
- A snapshot of some metric as it stands at the end of the indicated period. For example, cell G23 above shows what the cash balance was on Dec 31, 2019 (but gives us no information about what the balance was throughout the year)
- The Model tab, where we'll forecast our revenue and most of our expenses
- The Personnel tab, where we'll forecast our personnel expenses (which the Model tab then links to), and
- The Summary tab, which provides an overview of the model's most interesting components (which links to the other two tabs)
The steps we're going to go through to construct this model are:
- Forecast our Revenue
- Forecast our Expenses
- Calculate our Net Income
- Forecast our Cash Balance (assuming we don't raise outside capital). This will tell us if we need to raise additional capital, and if so, how much, and
- Forecast Investment Capital raised (if required)
There are many ways to forecast the revenue of a company, and the method you choose should be tailored to the business in question. Examples of the more common possibilities include:
- Price * Units. The most basic and general of the methods. Apple sells X number of iPhones at an average of $Y each, and Revenue = X * Y.
- Subscription. Once you sign up a customer, by default they will continue paying you money every so often, and a certain percentage of them unsubscribe (churn) each period. This is the method we'll walk through below.
- Service Contracts. Many B2B companies have contracts with given clients where the client agrees to pay a certain amount of money on a given date (or dates). These might be recurring, they might not.
Note that some of the math may look funny here because the quarterly detail is hidden. Full detail can be found in the excel file linked above.
In this example, we're forecasting revenue for the company by:
- Forecasting New Subscriptions (line 10). We've just entered hardcodes here for simplicity, but these could be the result of calculations related to a marketing / sales funnel
- Forecasting Subscriber Attrition (line 11). We've input assumptions about the quarterly attrition rate over time on line 14, and use that to calculate this line
- Forecasting # of Subscriptions (line 12), which starts with existing subscriptions, adds new subscriptions, and subtracts subscriber attrition
- Forecasting Revenue per Active Subscription (line 16), which is hardcoded and assumed to stay flat
- Forecasting Total Revenue (lines 17 and 19) by multiplying # of Subscriptions by Revenue per Subscription. Note that we only receive revenue from subscriptions, so Subscription Revenue also equals Total Revenue.
Many companies spend 80%+ of their cash on personnel
Your categories will likely vary
2021 quarterly net income
Note that things get more complicated in the real world if the timing of when you "earn" revenue or "incur" expenses gets out of alignment with when the actual cash changes hands (for example, if your customers take a while to pay you even if you've already delivered product), but we're ignoring those differences for now and just looking at the cash.
If our forecasted cash balance ever turns negative, we essentially have three options to rectify the situation. We can:
- Increase our Revenue projections (i.e. make more money)
- Decrease our Expense projections (i.e. spend less money)
- Get money from investors
Cash including investment
According to our model, after raising capital we should be on track to reach profitability and have a self-sustaining business in 2021.
Want help building your model? We at Fivecast Financial have built models for hundred of companies to help them successfully raise capital and understand their businesses better. I'd love to hear from you at email@example.com.