The ultimate guide to financial modeling for startups

Revenue forecast example in Excel
(source: EY, 2018)

The way in which you build up your revenue forecast depends a bit on your business model. The example above includes a traditional business model of a company selling products/services per unit.

However, for a SaaS business it could be better to prepare a revenue forecast based on existing customers, new customers and the churn rate. You can look for a financial modeling template for specific companies or business models on the web. Our financial planning software for startups also includes the usage of different business models to build up your revenue forecast.

2. Cost of goods sold (COGS)

Cost of goods sold (COGS) are those costs that undoubtedly need to be made in order for a company to deliver a service or produce a good. Without these costs, the product or service would simply not exist.

COGS differ based on the type of offering you sell. For a company that sells tangible products they would include for instance the costs of the materials used in creating the good. For a company that sells consultancy hours they would include the personnel costs of the employees delivering the service.

For a SaaS business COGS are different compared to ‘normal’ businesses as there is no regular production or service delivery process involved. However, also SaaS companies definitely incur COGS, such as hosting costs, customer support and onboarding costs, and online payment costs. From these examples you can notice that all of these costs have to be incurred in order to produce the good or deliver the service.

Not sure how to forecast COGS? One way of tackling this, is by looking at the sales targets defined in your revenue forecast. Let’s assume you sell a tangible good. From creating the revenue projections you know already how many units of sales you aim to have. You then add per unit of sales the costs of raw materials and labor costs involved in producing those goods.

Example: if you sell plastic bottles, you could calculate how much plastic (in grams) you need per bottle and what would be the price of a kilogram of plastic. Moreover, you need to know how much paper label you need per bottle and what is the price of that. Also, you need to know the costs of the cap.

If you know all of these costs required to produce one bottle you can multiply them by the total number of bottles sold. Finally you add the personnel costs for employees that are involved in production.

If you would prepare this in Excel it would probably look something like this:

Cost of goods sold forecast example in Excel
(source: EY, 2018)

How to forecast COGS also depends on your business model. Sometimes it would make more sense to forecast COGS on total level, for instance per month. Or they could be a percentage of your revenues (for instance when you work with sales commissions). Our financial planning software for startups includes different types of COGS forecasting.

3. Operating expenses (OPEX)

Operating expenses are those expenses that a business incurs as a result of performing its normal business operations. Unlike the cost of goods sold, they are not necessarily needed to produce the goods that are sold or to deliver the services promised. They include costs related to the supporting and operational side of business, such as sales and marketing, research and development and general and administrative tasks.

Typical operating expenses for startups include: events, travelling, legal costs, online marketing, payroll costs (of employees not part of COGS), accounting, rent, utilities, insurance, prototyping, patent costs, IT costs, office supplies, promotional materials, etc.

If you are not sure about which expenses you might incur in the long term, you could always save a certain percentage of your revenues for the different expense categories. E.g. you could include 10% of your yearly revenues on a budget for sales and marketing activities.

Most important is that your spending on operating expenses aligns with your company strategy. Is the growth of your company heavily reliant on online marketing? Then you would expect significant spending in that category.

An example of what an operating expenses forecast could look like for instance for spending on sales and marketing, can be found below.

4. Personnel

Personnel is probably one of the easier forecasts to build. With your personnel forecast you project the number of employees hired including their respective salaries, additional benefits and payroll taxes. To make personnel forecasting more simple you could split up your personnel into different categories, for instance:

• Direct labor: here you include the employees that will be solely engaged with the production of the goods sold or services delivered. Think of engineers and technicians for companies selling tangible hardware products, a junior advisor in a consultancy company, or customer onboarding personnel in a SaaS business. These costs are not part of operating expenses but are part of the cost of goods sold.
• Sales and marketing: for instance sales managers, marketing managers, copywriters, social media experts, etc. These employees are part of your operating expenses.
• Research and development: R&D managers, (software) engineers, technicians, etc. These employees are part of your operating expenses.
• General and administration: here you include back office and C-level personnel, such as the CEO, CFO, CMO, secretaries, bookkeepers, etc. These employees are part of your operating expenses.

An example of what a personnel forecast could look like, for instance for personnel working on sales and marketing, can be found below.

Personnel forecast example in Excel
(source: EY, 2018)

If you want to check whether your personnel forecast is realistic, you could divide your projected revenues in a given year by the number of employees (‘FTEs’ or full time equivalents) for that year. This tells you how much revenue you expect to generate per employee and provides a solid basis for comparison with competitors and industry leaders.

When your revenue per employee is at a similar level compared to the top twenty tech companies (see the graph below) already in just a few years after your launch, this is a strong indicator that you might be too optimistic regarding your expected revenues or that you might invest insufficiently in personnel.

Revenue per employe
(source: EY, 2018)

5. Investments in assets (capital expenditures)

The fifth input sheet to your startup’s financial model are the investments in assets (or: capital expenditures). Capital expenditures are funds used by a company to acquire or upgrade physical assets such as physical property, intellectual property, buildings or equipment. This type of expense is made by companies to maintain or increase the scope of their operations. They can include everything from repairing a roof to building a brand new factory.

Typical capital expenditures depend on the type of business and industry. For startups it is quite common to invest in computers, software, office equipment and machinery, but buying a building would also apply as a capital expenditure.

Many startups are incentivized to categorize their expenses as capital expenditures instead of as operating expenses. This has to do with the fact that due to an accounting technicality payments related to investments in assets are spread out over several years in the profit and loss statement (see section ‘Deprecation’ below) and therefore do not show up all at once in the year of purchase. This means they have a less visible reducing impact on profits. Be aware that the rules for categorizing expenses as assets are quite strict though!

6. Financing

The final potential input sheet of a startup’s financial model could be a financing module. In this sheet you would add financing streams such as equity, loans or subsidies. The main goal of this would be to check the impact on your funding need when you add different types of funding in different years of the model.

When a model includes the possibility to input loans, it needs to account for the loan repayment and interest payments, as these have an impact on cash flows. Below you can find a simple example of a €100,000 loan with a duration of 10 years and an interest rate of 10%

Loan input and repayment scheme example in Excel
(source: EY, 2018)

The six different input sheets that are discussed above are all in some way linked to the outputs of the financial model. For the financial statements specifically the links are as follows:

• Revenues impact the top line of the profit and loss statement. In the P&L you deduct all costs, expenses and depreciation from the revenues to arrive at EBIT (earnings before interest and taxes). EBIT serves as input for the operational cash flow in the cash flow statement. If you deduct interest and taxes (see section ‘Taxes’ below) from EBIT, you arrive at the net profit. Revenues even impact the balance sheet as they define the accounts receivable position.
• Cost of goods sold also turns up in the profit and loss statement. Deducting them from the revenues results in the gross margin. The gross margin can also be presented as a percentage: the higher this percentage is, the more revenue is left for covering costs that are not directly related to production. Cost of goods sold also impacts the balance sheet as they define accounts payable and inventory.
  Operating expenses show up in the profit and loss statement as well. Deducting operating expenses and cost of goods sold from the revenues results in EBITDA (earnings before interest, taxes, depreciation and amortization).
• Personnel either shows up in the profit and loss statement as a separate line or it is included in the cost of goods sold or operating expenses. Personnel involved in delivering services or producing goods end up in cost of goods sold. All other personnel is part of operating expenses.
• Investments in assets (capital expenditures) do not show up in the profit and loss statement because, accounting-wise, they are not seen as costs or expenses. They are investments and can be capitalized, meaning a company can leverage their value for several years. Therefore, they show up as something a company owns in the assets side of the balance sheet. Their value is depreciated (reduced in value) over their lifetime which is shown as depreciation in the profit and loss statement.
  Even though investments do not show up as a cost or expense, investing in something does mean there is a cash outflow for your company (you have to pay, right?). Therefore investments also show up in the cash flow statement as investment cash flow.
• Financing impacts the financial statements in two ways. Firstly, new financing and changes in debt shows up in the cash flow statement as financing cash flow. Secondly, interest paid on debts end up in the profit and loss statement.

The financial statements themselves are also interrelated (see image below). For that reason it could be wise to have an experienced person supporting you building your model if you do not have this experience yourself, especially if you are looking for a more complex model including supporting schemes such as the ones mentioned in the next section.

Interdependencies in the financial statements
(source: EY, 2018)

If you do not want to worry about all the calculations and the interdependencies in a financial model, you could try out our financial planning software for startups, which does all the thinking for you.

As you might have noticed already, some of the elements mentioned above include some tweaking of the numbers before you get to the right information that is presented in the financial statements. Supporting schemes such as working capital, depreciation and taxes might be needed.

Moreover, when you build a financial model you automatically structure a whole lot of data which you can also use for other purposes, such as a company valuation. Therefore, below we present four elements that support a startup’s financial model.

1. Working capital

Working capital is the capital that you need in order to sustain your daily operations. Technically speaking working capital is a comparison of the value of your current assets compared to your current liabilities.

In other words: the value of the things your company owns and that can be converted to cash on the short term (in less than one year) compared to the value of the things your company owes to others that are due on the short term (less than one year as well). Current assets include cash, accounts receivable and inventory. Current liabilities for instance include accounts payable.

Working capital is extremely important for startups, because it is a measure of both a company's efficiency and its short-term financial health. Working capital can significantly affect cash flow, so if a company's current assets do not exceed its current liabilities, then it may run into trouble paying back creditors in the short term. The worst-case scenario is bankruptcy.

Working capital can be impacted by payment terms. In order to assess your working capital position you should therefore not only steer your company based on revenue targets, but also on your cash flows. Forecasting for cash flow provides you with an overview of the timing of incoming and outgoing cash flows. How to do this is discussed in section ‘Operational cash flow overview’.

Why is this important? Well, when you focus only on costs and revenues and not on the timing of receiving and sending payments you could end up in serious trouble.

Consider that a large firm orders one hundred 3D printers at a startup producing a new type of 3D printers. The client expects the printers to be delivered within one month. As large firms often use long payment terms it might take up to 90 days before the startup receives the actual payment for the order.

This means that our 3D printer startup needs to finance the raw materials and production process itself. After all, the company has to deliver within 30 days, but still has to wait for 90 days before the payment is received.

If the funds required for production are not available for the startup then the order might be cancelled leaving both parties unsatisfied. If this happens consistently, the startup could go bankrupt even though orders are coming in.

Working capital is calculated based on the number of days your sales and payables are outstanding and the number of days you hold inventory before selling it. It shows up in the balance sheet. Therefore, a financial model might need a separate scheme that calculates working capital based on revenues, cost of goods sold and days outstanding.

See for instance the example of the calculation of accounts receivable below. With revenues being €100,000 in year one and payment terms of 15 days for outgoing invoices the accounts receivable position at the end of the year is €4,110.

Accounts receivable calculation example in Excel
(source: EY, 2018)

2. Depreciation

Deprecation indicates the value reduction of assets a company owns. Based on the value of an asset and its useful lifetime depreciation is calculated. Depreciation is part of the profit and loss statement and impacts the value of assets on your balance sheet.

As an example, let’s say you want to buy some computers for your company. They cost you €20,000 and you can use them for four years. This means you will write off the total investment of €20,000 over a period of four years, which means you will depreciate their value with €5,000 every year for the coming four years (if they do not have any residual value left after that).

A financial model needs a separate scheme that calculates depreciation based on investments and their related useful lifetime. Below you can find an example calculation of depreciation.

As you can see, in year one €20,000 was invested in computers, software and equipment and in year two €30,000. Both are depreciated over four years, resulting in the total depreciation per year; being €5,000 for year one, €12,500 for year 2-4 and €7.500 for year five.

Depreciation calculation example in Excel
(source: EY, 2018)

3. Taxes

Every company that is incorporated and registered at the Chamber of Commerce has to pay yearly taxes over its financial results: the corporate income tax. Taxes are deducted from your results in the profit and loss statement. Here you can find a list of corporate income tax rates per country.

If you want to include tax carryforwards in your financial model, you likely need a separate tax scheme as part of your model. A tax carryforward works as follows. As an entrepreneur it is likely that you have negative results in the first couple of years of operations. If you have negative results this basically means you have expenses that exceed revenues (more costs than income) leading to an operating loss. If you have a loss, there is obviously no income to be taxed by the tax authorities. This loss can be leveraged in future tax reporting periods to offset taxable income (you can ‘carry it forward’), which reduce the amount of tax you will pay in that specific tax reporting period.

Below you can find an example of a tax carryforward calculation based on a corporate income tax rate of 23%. As you will notice, year one had a negative result of -€50,000 which is settled with the positive result of €230,589 for year two resulting in a taxable profit of €180,589, resulting in a lower tax burden for that year.

Tax carryforward calculation example in Excel
(source: EY, 2018)

4. Valuation

Many startups build a financial model for the purpose of raising funding. Part of the fundraising process are negotiations with an investor about the valuation of the company to be invested in. The good news is that when you have built a financial model for your company, all the ingredients are there to perform a valuation on your company as well by means of the discounted cash flow (DCF) method.

The main advantage of the discounted cash flow method is that it values a firm on the basis of future performance. This is perfect for a startup that might not have realized any historical performance yet, but expects large future earnings. During the (pre-)seed stage it is not uncommon for startups to not generate any revenues at all yet, while discussions with investors regarding ownership percentages and the accompanying valuation already take place. The discounted cash flow method is very suitable in that case, as it weighs future performance more than current performance.

The main downside of the DCF method when valuing startups is that the DCF is nothing more than a formula, a mathematical operation. This means that the quality of the valuation is extremely sensitive to the input variables of the formulas used to calculate the valuation. Moreover, it largely depends on your ability to create an accurate forecast of your firm’s future performance. After all, the future earnings are the foundation of the valuation.

A deep dive into discounted cash flow valuation is out of scope for this article. The main steps of performing a discounted cash flow valuation are presented below, but we have also written a deep dive into startup valuation:

• Step 1: create financial projections for your firm (tick in the box!).
• Step 2: determine the projected free cash flows.
• Step 3: determine the discount factor.
• Step 4: calculate the net present value of your free cash flows and terminal value by using the discount factor.
• Step 5: sum up all results of step 4.

Below you can find an example of a discounted cash flow valuation.

DCF valuation example in Excel
(source: EY, 2018)

Depending on the desired outcomes and the corresponding complexity of your financial model you can decide whether or not to add additional schemes such as working capital, depreciation and tax carryforwards. You can look for a financial model template including these elements on the web. If you do not want to worry about these elements at all, our financial planning software for startups does all the calculations for you.