Introduction
A financial model is an essential tool in undertaking a fundraise – it helps a business to understand how much capital to raise and when to do the raise. It portrays the business plan to prospective investors, enabling them to sense check forecast performance against recent historic trading, assess the riskiness of the investment thesis, calculate the cash burn rate, assess and sensitise key driving assumptions and model their expected returns.
We have built a simple Excel template that covers the basic figures that investors expect to see across all business types. This resource provides step by step instructions on completing the template for your business below (as well as prompts inside the model). Alternatively it can be used as a guide to amend and improve your existing model.
We recommend adding a tab that displays the key KPIs for the business – this cannot be templated as it varies for each and every business. These KPIs may also be linked to the turnover and cost assumptions in the model, if applicable.
These KPIs may include standard financial metrics, such as Turnover Growth and Gross Margin (included within the model), and key drivers of growth and return, such as number of users, price per user, volume, number of sites / locations, cost of customer acquisition, customer payback, return on investment etc.
The model includes two years of historic P&L data from Turnover to EBITDA. It does not require historic balance sheet and cash flow data as this is rarely held by early stage companies and is not always required by investors (although the cells have been left populated with “n/a” in case you want to include this). Instead, the closing balance sheet for the most recent fiscal year end is used as a starting point for the cash flow forecast. Historic P&L data is of particular interest to investors for the purposes of assessing the pace of growth to date and sense checking the forecasts. The balance sheet and cash flow data are of greater relevance to the future funding needs of the business. Investors may wish to diligence the historic cash flow of the business later in the process – at this time, the historic cash flow data can be added to this model, or a standalone model may be built to specifically address their question.
The first step is to complete and tailor the template for your business, or use this as a guide to review and amend your own financial model.
Formatting guidelines
- Cells with blue font and yellow highlight are inputs and can be edited - note that some example numbers have been included in the template
- Cells with black font are outputs and should not be edited
- Income is represented with positive numbers and costs with negative numbers (which appear in brackets)
Assets are represented with positive numbers and liabilities with negative numbers (which appear in brackets)
Introduction tab
Input the company name and the current fiscal year end – these inputs will change the column headings throughout the model.
Profit & Loss tab
Input historic P&L turnover and costs for the past two years (if applicable) to demonstrate recent trading history.
The P&L allows for 3 different categories of Turnover, each with their own Gross Profit so that you can model different margins, if desired. Add or remove categories as necessary to suit your business.
Forecast Turnover utilises a year on year growth rate assumption, i.e. how much Turnover grows from January one year to the January of the following year, thus taking into account any seasonality. For an annual growth rate of 30%, input 30% in every month of the relevant year.
Forecast Cost of Sales are based on a percentage of turnover.
The model provides for five categories of overhead costs. Add or remove rows here as appropriate and label accordingly. If you would like to include more detail, we recommend adding an additional “Overheads” tab that includes assumptions such as growth rates, to calculate forecast costs. It may also be helpful to provide a breakdown of salaries by employee.
Input the relevant tax rate – this may be zero if the company is loss making.
Balance Sheet tab
The key line items here are driven by separate schedules on the “Balance Sheet Schedules” tab and therefore do not require editing.
Lines are also included for Prepayments, Other Debtors, Accrued Revenue, Deferred Revenue, Other Creditors and Accrued Expenses. These line items may not be applicable to the business and will depend on your working capital cycle and the nature of certain payments, such as rent. To keep the model simple, these can be removed or left blank. If removed, make sure to remove the corresponding line items on the “Cash Flow” tab.
Below the balance sheet is a “Difference in Balance” row that should show “0” in every month, i.e. Total Assets should always equal Total Liabilities and Equity such that the balance sheet balances. Note that this row may show a delta until all inputs throughout the model have been populated. Once this has been done, if there is a consistent error in the balance, for example, 100 every month, then the starting shareholders equity balance must be amended by that amount in the Balance Sheet Schedules tab.
Cash Flow tab
The cash flow statement is generated automatically from other inputs so there is nothing to add on this tab, with the exception of the closing cash balance for the most recent fiscal year. The data for the cash flow graph is included in this tab.
Debt Schedule tab
The model allows for up to four lines of debt, which could be overdrafts, term loans, shareholder loans or venture debt. Rename each line of debt accordingly, input the applicable interest rates, current balances and any anticipated drawdowns or repayments.
If you have more than four lines of debt, copy and paste one of the schedules and add the corresponding lines to the Balance Sheet and Cash Flow.
Balance Sheet Schedules tab
Input the current tangible assets balance and any forecast capital expenditure – these are the expenses that you capitalise as opposed to expense in the P&L, for example, purchase of assets. The model calculates Capital Expenditure as a percentage of Turnover to provide a sense check.
The model assumes a depreciation policy of 20% of capital expenditure per annum, or 1.7% per month – this can be amended as appropriate. This rate is applied to the monthly opening fixed asset balance and thus is an approximation and assumes ongoing capital investment. To model this accurately, create an additional tab called “Fixed Asset Register” and create a schedule for each individual asset. The depreciation row should take the opening balance of the asset so that the value decreases to zero by year 5 (assuming a 20% annual rate of depreciation). Note that this level of detail is not likely to be required by investors because depreciation does not impact cash flow, EBITDA or equity returns.
A goodwill schedule is included for businesses that have undertaken acquisitions in the past. If this is not the case the schedule can be left blank or deleted (if deleted, remember to remove the corresponding amortisation and acquisition lines in the P&L, Balance Sheet and Cash Flow).
Input the trade debtor balance for each month of the current year, i.e. the balance at the end of each month representing income that has been invoiced but not yet received in cash. The model uses the current year data to calculate average debtor days, i.e. the number of days it takes for customers to pay, which may inform the assumption that you use for debtor days in the forecast years.
Complete the inventory / stock and trade creditor schedules in the same way.
Input the closing shareholders equity balance for the most recent fiscal year end, and any forecast equity investment or dividends. It is common to exclude the current fundraise from the model until negotiations with investors are more advanced and the specific commercial terms can be modelled.
Reviewing your financial model
Here are a few house-keeping checks:
A balancing balance sheet
If the balance sheet doesn’t balance, the model will show you what the delta is each month. If this delta is consistent every month, there is likely an error in the starting shareholders equity balance. If the delta increases each month, then it is likely that one of the items included in the balance sheet does not have a corresponding entry in the cash flow or vice versa (which may occur if you have added extra line items).
Cash headroom
A review of the cash graph will give you a sense for whether the model approximates your anticipated cash flow and an idea of how much funding you require.
Turnover and margin trends
Check that the trends in turnover and margins look sensible, realistic and there are no unusual movements. There is an annual summary to the right of each statement to provide the macro view. Ensure that the figures fairly reflect what you intend to achieve as a business and can be relied upon by investors.