Now we have our template set up we need to start on our forecast financials.
Remember that when writing a formula you should first link the cell first to the assumption that you have used and only then link to the driving financial number.
This makes it easy to trace the assumption driving the forecast when auditing and reviewing an Excel model, as typing Ctrl + [ will take you straight to the first linked cell in the formula.
So, for example, if you are forecasting sales you would write the formula as (1+ growth assumption) * prior year’s sales so that Ctrl +[ would take you to the growth assumption.
This particularly important when creating multi-sheet models.
There are as many approaches to forecasting revenue as there are different business, however there are two that are commonly used.
- Assuming a growth rate:
This is usually based on an historic average, on the industry’s projected growth rate or guidance given by the company.
- Based on the assumptions for the drivers of revenue:
More complex models will use the underlying drivers of the business to forecast growth rather than simply a top level number. These drivers are often based on things like number of sales people and their effectiveness, or a combination of market size, growth and penetration for the business.
For a widget manufacturer like ABC 123 it might be that you assume a certain capacity utilisation for its factory.
We can see the previous year’s growth rate and so for our example we will simply extrapolate that by using applying (1+growth rate) to the previous year’s sales figure.
Forecasting COGS (Cost of Goods Sold):
We will forecast COGS as a % of revenue. Our COGS are 50% of revenue.
Calculating Gross Profit:
This is simply sales less COGS.
Forecasting SG&A (Selling, General and Administrative expenses):
For simplicity we are forecasting SG&A as a % of revenue.
In more complex models SG&A is often split into a fixed portion and a variable portion. These are then forecast separately.
Rent is a fixed cost, with no escalation, unless this is contained within the lease. Similar fixed expenses are things like property taxes and some types of insurance.
Variable costs change with sales. These tend to be things like marketing expenses, travel expenses, legal and accountancy expenses all of which increase with increased activity.
Finally you have salaries. These are fixed in the short term but obviously vary in the long term as if the business doubles in size it will clearly need to recruit further staff.
To keep things simple we will calculate these expenses as variable expenses in the same way as we calculated Sales using
(1+growth rate)*prior year’s cost.
Calculating EBITDA (Earnings Before Interest, Tax, Depreciation and Amortization):
This is simply Gross profit less SG&A.
Forecasting Depreciation and Amortization:
Depreciation applies to physical assets whereas amortization applies to intangible assets. In both cases we need to calculate how these assets reduce in value over time.
For this we use something called a BASE analysis. BASE stands for
B – Beginning balance
A – Additions (Purchase of a new asset, Capital Expenditure on an existing asset)
S – Subtractions (Depreciation, Amortization, Sale of an asset)
E – Ending balance = B + A – S
For simplicity, our model won’t include purchasing or selling any assets. We look at this in article 5 of this series.
We will simply forecast our Net Asset Value (NAV = Cost of assets less accumulated depreciation) as our opening NAV less depreciation and amortization during the period.
We will link our closing balance for 2016 to our historic balance sheet number. The opening balance for 2017 = closing balance for 2016.
Again for simplicity we will be using the straight-line method of depreciation. There are many methods used to calculate depreciation as you might imagine but this is the simplest and most widely used.
To calculate this we will need a section to calculate the accumulated depreciation and the Net Asset Value.
We will also have to ensure that our accumulated depreciation does not exceed the gross asset value. This would clearly be nonsense and would give rise to a negative asset value on the balance sheet.
We calculate accumulated depreciation as the lesser of either:
- Current year depreciation plus previously accumulated depreciation, where the current year’s depreciation charge is calculated simply as the gross asset value multiplied by the annual depreciation %; or
- The gross asset value.
We do this so that we can never have a negative net asset value, or looked at another way depreciation of more than the asset’s gross value.
Then in the depreciation row of the BASE analysis we calculate the current year’s depreciation as the difference between the total accumulated depreciation at the end of the current year and the total at the end of the previous year.
The closing balance is the opening balance less the depreciation charged in the current year.
We link the depreciation number from the calculation sheet to the relevant line on the face of the Income Statement.
Calculating EBIT (Earnings Before Interest and Tax).
EBIT = EBITDA – Depreciation
Forecasting tax in detail is a highly complex area. For the purposes of this introductory article we will simply calculate it by applying a simple tax rate from the assumptions sheet to the EBIT figure.
We will then calculate the Net Profit as EBIT – Tax.
Our simple forecast only requires a forecast for fixed assets, debtors, creditors and tax payable. Cash is calculated by the cash flow statement.
Forecasting Fixed assets:
We link the fixed asset line to the closing balance of the fixed asset BASE calculation in the calculation sheet which contains the Net Asset Value at the end of the period.
Debtors are forecasted based on receivable days/ month. Since we have a 2 months credit period for our debtors, it means that the sales of the last 2 months are yet to be collected at the year end.
We calculate debtors as = receivable months * (Sales / 12)
Calculating Total Assets:
This will be the sum of the line items in the asset section, so fixed assets (NAV) plus Debtors plus cash.
Creditors are forecasted based on payable days/month. Since we have a 1 month credit period for our payments, we will have the payments for the last month to be paid to our suppliers as at the balance sheet date.
We calculate creditors as = receivable months * (COGS / 12)
Forecasting Tax Payable:
This will be done by creating another BASE analysis. The current year’s tax due will be added to the opening balance as that will be paid next year as our assumption is it’s paid 6 months after the year end. We will subtract the previous year’s tax as that will have been paid in the current year.
Calculating Total Liabilities:
Total liabilities is the sum of all the line items in the liability side of your balance sheet so the sum of creditors, tax payable and any overdraft that the business has.
We are not assuming any issue of new equity during the period and so share capital will remain the same. The accumulated income will increase by each year’s net profit since we are not assuming that any dividends are paid. This is looked at in article 5.
Calculating Total Liabilities and Equity:
This is a simple sum of the two lines Total Equity and Total Liabilities.
Input Balance Check Formula:
It is usual to create a formula to check that your balance sheet balances, so Total Assets = Total Equity plus Total Liabilities.
Create a new row (usually with red text) to show the difference between total assets and total liabilities. This row should always be zero.
For now, it shows a difference as we still need to forecast our cash / overdraft.
Creating A Cash Flow Statement
Forecast Operating Cash Flow
You start your cash flow statement with the Net Profit and work backwards from there to add / subtract the differences between your income statement and your actual cash paid / received..
You start by adding back tax, depreciation and any other non-cash expenses that appear on the face of your Income Statement. There are no others in our simple example.
Next you deduct cash expenses that are not reflected in your net income. The best example of this is tax paid during the year. This balance was actually included in the previous year’s income statement because it is paid 6 months after the year end in this example.
Finally you add changes in operating working capital, which reflects changes between the opening and closing debtors and creditors position at the balance sheet date.
Operating working capital is calculated as non-cash current assets less current liabilities. An increase in operating working capital will result in reduction in cash and vice-versa.
Complex models will show the change in debtors, creditors and stock separately but for our purposes it is fine to simply use one line.
Forecasting Investing Cash Flow
This section of a cash flow statement catches capital expenditure and investment. It includes things like purchases of capital equipment and investment of other sorts. It also includes the proceeds from capital sales and the sale of investments.
For our simple model we do not need this.
Forecasting Financing Cash Flow
This section of a cash flow statement catches the outcome from financing activities that don’t show up on the face of a balance sheet. So things like the issues of debt and / or equity, the repayment of debt and the payment of dividends.
In our simple example, we have assumed a no debt and no dividends and so we do not need this currently.
Forecasting Closing Cash:
We calculate closing cash using a very simple addition of the opening cash position and the change during the period.
The change in our example is simply the change in Operating Cash Flow. In most models this would also include the Investing and Financing Cash Flow figures as well.
If the closing cash balance is negative, it is an overdraft which will be shown on the liability side of the balance sheet and cash will show as zero. If cash is greater than zero then the overdraft will show as zero and the cash will be shown in the asset side of the balance sheet.
Now we need to link this to the balance sheet to get our balance sheet to balance.
We need to put in two formulas:
- If the cash balance is positive it will show in the cash balance in the asset part of the balance sheet:
- It will appear as an overdraft in the balance sheet, in the liability section of the balance sheet if it is negative.
In the cash row of the balance sheet the formula is
= Max (closing cash balance,0)
This will ensure that if the cash balance is negative that it is not show here as a negative figure.
In the overdraft row, insert
=-Min(closing cash balance,0)
This will ensure that if it is a negative figure it will be shown here.
Once you have done this your balance sheet should now balance. As shown above your check sum now shows a zero balance as you would expect.
Now that you have forecasted all the financial statements for one year and the balance sheet balances, you can simply copy the formulas along the rows.
Once all your financial statements are forecasted, you need to create a summary output for the model. This is often in the form of a ratio analysis.
For our model, we have analysed key growth ratios, margins, balance sheet ratios.
When saving and closing your model, do Ctrl + Home in each sheet and then close the model on the output page. This will ensure that the next person opening the model opens it at a sensible place and all sheets open to their top left hand corner, not halfway through.
The model that we have created is a very simple model.
In our next article we will look at how we can use it to run scenarios and produce data tables and then finally in article 5 we will look at how we can increase the complexity of our model by going back and including things like stock and dividends and also how to handle purchases of new fixed assets.