Startup Financial Model: A Complete Step-by-Step Guide
Though a general financial model for startup template is an easy way to create a financial model, you will oftentimes find it hard to use that for your own business.
Why? Because each startup has its own unique needs. For example, an internet venture relies heavily on web traffic whereas a physical store is more on foot traffic.
The solution? Well, you need to create your own startup financial model XLS, that is, a financial model that is created through Microsoft Excel.
By preparing your own worksheet, you will be able to tweak it according to your perceived needs.
Right from the start of the creation, the Excel file will be uniquely created for your startup.
You can outsource the creation of this unique file. However, it’s better if you will be the one who will create it. Besides, you’re the only one who knows your business up to the minute detail.
What can you get from this guide?
This guide will teach you the important basics of creating a startup financial model for an entrepreneur. It will present to you the necessary concepts you need in order to create a working model.
There’s a portion that covers a detailed step-by-step guide on how to create Excel financial modeling templates.
The steps will be based on a sample financial model for a startup that you must download.
Piece by piece, you will know how this sample model was prepared. There will also be screenshots to better help you understand the lessons.
Please take note that this sample model is not a good fit for all businesses.
Our main objective here is not to replicate the sample, but rather, for you to have a working knowledge as to how you will be able to translate your startup’s operations into a financial model.
Already too excited to create your own model? Please contain your excitement for now.
Before you start creating a financial model for your startup, here are some tips you need to keep in mind.
Tips on how to create a flexible financial model template for startup
Plans cannot foretell the exact future. After all, plans are just subjective expectations. In other words, always expect different possibilities. Startups are volatile; there would be a lot of changes.
These possibilities should be contained in your financial model as well.
You must know what variables will affect your financial model. This helps you identify which cells should need manual inputs. All changing variables must be in cells that can be manually inputted.
Your model must be easy to modify. For example, if you change your Sales assumptions, it will take only a few manual inputs; all computations will work automatically. There’s no need for you to recreate each financial report.
To do this, have a separate Excel tab for all your assumptions. This should include all the factors that you expect will change.
These could be the Number of Units Sold, Price, cost of borrowing money, Depreciation rate, Days Sales Outstanding, Days Payables Outstanding, Days Inventory Outstanding, Planned Fixed Asset Acquisitions or even Tax Rates.
Cells should be properly labeled. This does not only mean that the cells be properly named as to what they are representing.
The creator should also clearly indicate which cells need manual inputs, and which cells do not.
For proper labeling, use legends. The best way to do this is by using colors.
For example, you can use light blue to indicate manual input, white for cells that must not be edited and yellow for cells which are results of operations of other cells.
Learn more about financial modeling careers.
Step by Step guide on how to make a startup financial model
You will learn how to create a financial model of your own using the steps we will discuss here.
To reiterate, the model we will be creating here is not a good fit for all startups. However, learning how it was made will give you a good idea of how you can create your own.
These are the tabs of our sample financial model:
- Balance Sheet
- Income Statement
- Cash Flows
The spreadsheet uses colors as legends:
Before we start creating a spreadsheet, you should survey first your needs or the needs of third parties that might use it.
Step 1. Identify not just your needs, but also of potential investors
Who is the possible audience for your financial model?
- And, of course, you…
As the owner, why do you need a startup financial model? Your first reason could be, you are needing a way to properly quantify the operations of your business.
You would like to see what your income could be in the coming years. Moreover, you want to know the current valuation of your startup.
How about the potential investors? What could be their possible needs? First of all, they want to know the profitability of your startup.
They want to know the rates of return figures such as the IRR. Of course, if they are going to invest money (as well as time and efforts) so somebody else’s business, they would like it to be worth it all.
The creditors, on the other hand, would like to see if you will be able to repay loans on time or not.
They would like to see if the profits of the business will suffice to meet maturing obligations.
Furthermore, they might want to see if the founders have sources of cash other than the regular income of the business, just like saleable assets.
All of these needs should be the foundations of the model you will be creating. That financial model should be structured to meet the specific needs of intended users.
As part of the creation process, list down all the possible needs of you as the owner, and of the potential angel investors/venture capitalists.
Use these needs to create an idea map of your financial model.
As to the time frame projection of financials for a startup, don’t go too far away.
Three years will do. After all, the operations of startups are highly unpredictable. We rather focus on the medium-term estimates rather than on long-term ones.
Step 2. Create an Assumptions Tab
The Assumptions tab, as the name suggests, will contain all the assumptions that will govern your whole financial model.
This feature will allow you to play on the figures. For example, you can see the different income statements at differing levels of price just by changing the value of a cell or two.
In other words, this tab will allow you to make your model flexible.
How will you know what items must be included on your Assumptions page?
The answer will depend on your idea map. Based on your plan, what inputs do you need?
All these values must be included in the Assumptions page. Remember, in a good financial model, no inputs are entered twice.
For our sample financial model, we had subdivided the inputs into four major areas you can find it under the Assumptions tab:
Part I. Sales/Revenue Assumptions:
This relates to inputs related to Sales. To estimate the sales for the start-up, we assumed that it will have the same Sales figures of similar start-ups:
Take a look at the table above. We assume that Klestar, Inc. and Off, Inc. are similar to the start-up we will analyze. We will assume that the sales of our start-up will be the average sales of similar start-ups.
Thus, the first year (Y0) sale we will use is 212,500.
We also had cells for inputting the sales growth rate for the next two years (Y1 and Y2).
The assumptions will, of course, differ depending on the nature of the business. For example, an internet financial model for a startup would need assumptions related to web traffic and conversions.
Part 2. Investment Acquisition Assumptions:
In this table, we will input all major investments we are planning for the first three years.
You will notice that we had divided the columns into short-term (Quarters 1-4 for Year 0) and another three columns for Y0 (total), Y1 and Y2. There is also a column dedicated to Initial Investments.
Here’s the logic for this table. On the upper portion, you will place all the investments (we included Cash) you are planning to the appropriate time column.
You should match it with an appropriate funding on the lower portion (either Interest-bearing loan or Share Capital).
The two yellow rows must match. Meaning, all assets must be matched with funding.
If the funding is insufficient, the “More Funding” row will display “YES”, otherwise, it will say “NO”. This was created using the IF Function.
The last line, Cumulative NCA (non-current asset) was created to facilitate easy computation of depreciation for our financial statements.
Suggested: Read more about Financial Modeling Careers
Part 3. Operating and Payroll Assumptions:
Mainly, this is the assumptions related to expenses, especially operating expenses and payroll.
This is easy to accomplish. You just had to create separate tables for operating expenses and payroll. On the left side, indicate the labels. On the right side, indicate the corresponding amounts.
Part 4. Miscellaneous Assumptions:
All the other assumptions that don’t belong with the three.
This is very useful especially if you hadn’t anticipated the need for a certain input.
If during the time you are creating the model, you had discovered you need a certain input, you can place is on the Assumption tab as part of Miscellaneous Assumptions.
Most of the inputs above were discovered needed only by the time this sample financial model was being created.
Step 3. Balance Sheet Projections
A three-year balance sheet projection is a good model to make.
To make things more precise, you can opt to make the first year in chunks of four quarters or twelve months.
You can even make this same set-up for the next two years.
What are the important balance sheet items you need to add?
For the assets, you need to include Cash, Accounts Receivables, Inventory, and other major Asset line items.
If the asset value is significant enough to affect decisions, then include a separate line item for that asset or group of similar assets.
In our Excel financial model example, the Cash line items are linked to the Ending Cash Balance of the Cash Flow Statements.
For this to work, the Balance Sheet and Cash Flow Statement had the same column labels. Both had an Initial Investment column as well as short and long-term portions.
Accounts Receivables uses the formula [(Sales/30)*Days Sales Outstanding]. If you click on the cells, you can see where these are linking to.
Inventory uses the formula [(Cost of Goods Sold/30)*Days Inventory Outstanding]. Cost of Goods Sold is under the Income Statement while the Days Inventory Outstanding can be found amount the Miscellaneous Assumptions.
The other assets are linked to the Investment Acquisition Assumptions under the Assumptions tab.
Accounts Payables uses the formula [(Cost of Goods Sold/30)*Days Payables Outstanding]. If you click on the cells, you can see where these are linking to.
The Interest-bearing Payables line is linked to the Investment Acquisition Assumptions under the Assumptions tab.
Shared Capital has linked also to the Investment Acquisition Assumptions under the Assumptions tab.
The Retained Earnings has the formula Net Income of Prior Periods fewer Dividends Paid. You can find these components under the Income Statement and Cash Flows tabs.
Step 4. Income Statement Projections
For the Income Statement items, we will use the same time frames to maintain consistency.
The Sales/Revenue figures were taken from the Assumptions Tabs. The base figure (Y0) is based on the average sales of similar start-ups.
Year 1 and Year 2 amounts are marked-up based on the growth figures placed under the Sales Assumptions cells.
The expenses figures were all taken from the Assumptions Page. The growth of expenses in Y1 and Y2 were computed based on the growth figures inputted in the Assumptions tab.
Other Income Statement Items:
Depreciation and Amortization are based on the rate placed on the Assumptions tab. The same goes for the Interest Expense and Taxes figures.
Step 5. Cash Flow Projections
The Cash Flow Statements of our startup financial model template uses the Indirect Method of computing ending cash.
This method starts with Net Income. Items will be added or subtracted to come up with the ending cash balance.
First, Depreciation and Amortization is a non-cash item, thus, it will be added back to Net Income to get the ending cash balance.
All increases in current assets are deducted (ex. Accounts receivables and Inventory), and all decreases are added back.
The opposite is true for current liabilities (ex. Accounts payable). Increases are added while decreases are deducted.
These types of activities refer to transactions involving non-current (long-term) assets such as Land, Building, and Machinery.
All Increases in long-term assets are deducted from Net Income, while all decreases are added back.
Financing activities include all investor activities (withdrawals, dividends, and investments) as well as long-term debt transactions (financing, principal repayment or interest payments).
All financing activities which generate cash are added to net income. All withdrawals, dividends or repayments are deducted.
However, interest expenses are not deducted from Net Income. Why? Because Interest expense was already deducted from Sales to compute for net income. In other words, we are avoiding a double deduction.
All changes are added to the cash of the prior period in order to compute the ending cash balance.
Step 6. Valuation of the Company
Computing for the value of the startup is important. Based on the size and projected sales of the company, how much is it worth today?
If the value of the company is high, and the financial model assumptions are convincing, startups will find it easy to find more investors or creditors for their company.
In our sample startup financial model Excel template, we will be using the Discounted Cash Flow Model.
Discounted Cash Flows refer to the present value of all cash flows that are expected to be generated by a business over a specified range of time.
Thus, the DCF Model attempts to value a company based on the potential of the company to generate cash flows. We will use the Weighted Average Cost of Capital as the discount rate.
We won’t be discussing the DCF Model in detail. If you want, FinanceWalk has a separate guide on DCF financial models. We will just be having an overview.
In this DCF Model, we will compute three stages. The first stage, the present value of unlevered free cash flows, is the present value of the cash that is free for disposal. In our example, our computation will be for the first three years:
The second stage is the terminal value.
In our sample, this is the value of the company from the fourth year (Y3) and forward.
The base value here is Y2 Unlevered Free cash flow multiplied by 1 plus the growth rate. The Present Value is then computed using the WACC (Cost of Capital).
The third stage is the Equity Value of the firm. It is computed by adding Stage 1, Stage 2 and the Net Debt of the business. Net Debt is the difference between Gross Debt and Cash.
Step 7. Funding
Use the funding portion to help you decide which among the schemes available to you are most advantageous. On the first line, input your requirements for the period.
You have the option to place a Cash Safety Margin on the second line. Input the Safety % on the Assumptions tab.
We have listed here several funding sources:
On the left side, input the cost (in %) of each funding source. There are columns that will automatically compute the cost of your funding choice.
If you have insufficient funding for your requirement, the last line will display “YES”.
Here’s the thing. You can read this article all day and not gain anything. Why? Because learning is by doing.
Think of a beautiful start-up you’ve always wanted to start. Think of all the details. Create assumptions that would govern your start-up.
Now, time to roll-up your sleeves. Using the steps discussed above, try creating your own startup financial model.
You don’t have to copy everything. Be creative. You can always create your own style.