ABC 123 Financials
Before we start to produce our financial model we need a fictional company to work with.
For the purposes of these lessons, you can follow along using the Introduction to Financial Modeling Example Workbook to follow along. There is no need to open/download immediately as we will be walking through some of the setup of the document here.
For the purposes of this example we will use some basic financial for ABC 123 Ltd, a widget manufacturer.
We will build a forecast for 3 years forward for ABC 123 so 2017, 2018 and 2019.
Below are the financials of ABC 123 Ltd for 2016. The figures are in $ thousands:
Other important assumptions for ABC 123 Ltd:
- Debtors pay on average after a 2 month credit period
- Creditors provide a 1 month credit period
- Sales have grown by 10% per year for the past 5 years and are expected to grow at the same rate for the next 3 years
- Rent is a fixed cost and will remain constant for the next 3 years
- Salary inflation is 5% per year
- General expenses and marketing expenses grow at 3% per year
- Corporate tax rate is 25% and is payable 6 months after the year end
- The company’s financial year is 1st January to 31st December and so follows the calendar year
- Depreciation is calculated at 25% per year on a straight-line basis. The gross asset value is $250,000 with an accumulated depreciation of $125,000, as at 3 December 2016.
Setting Up Your Spreadsheet
Key things to remember while setting up a template
- The template should be logically structured with separate sections / sheets for separate sections like Assumptions, Income Statement, Balance Sheet, Cashflow Statement, Calculations and Summary Analysis / Output. This will help avoid confusion.
- The structure of the model should be such that it makes it easy to navigate between the different sub-sections of the model.
One way of doing this is by making column A very small and having only the section headings in column A.
This means that you can use Ctrl + ↓ (Down arrow) to move quickly between section headings.
- Formatting of the sheet
- Inputs, calculations, historic data etc should all be formatted differently and so that they are easy to read. For example, typically you find hard coded assumptions in blue font with yellow background and calculations in black font.
- You should remove grid lines. You can remove the grid lines by selecting View -> Gridlines (or Alt+W+VG). This makes the model visually appealing and you can easily see the borders that will be printed. Gridlines are just for reference, not printing.
To see the printable area of your page, go to View>Page break view (Alt + W + I). Once you have your sheets in page break view, you can adjust the printable area by dragging the blue lines.
- Numbers should all have the same number of decimal points (if any), “x” should be used before multiples (for financial analysis), % for growth numbers and margins, etc.
Steps for setting up your model
- Start by inserting the name of the company in A1 and increasing the font size so that it stands out. As mentioned above, reduce the column width of column A to a minimum. This column will be used for section headings only.
- Increase the column width of column B as this column will contain all of the individual line item descriptions in your financial statements and assumptions.
- Leave column C blank for sub-headings.
- Columns D and beyond we will use for years.
Generally, 3-5 years of historic data is used and 5-10 years of future financials are projected. Label each column making clear whether the data it contains is actual historic data or a projection.
Color the background of the heading rows, make the font bold and put a border at the bottom for emphasis and to make it easy to distinguish from the rest of the model.
- In column B, put the currency and units for the financials. Like in our example we have the amount in $ thousands.
Below is the screenshot of the template for our example.
- Freeze the header rows so that when you scroll down in the sheet you still see the header. For e.g. our header is in rows 1-3 and column headings are in columns A and B. To do this we select cell C4 and then we go to View -> Freeze Panes -> Freeze Panes (or Alt + W + F) this will save you scrolling up and down repeatedly to check which year you are in. See screenshot here for how to find the Freeze Panes option:
- At this point you need to decide if you will be creating a single sheet model or a multi-sheet model.
A single sheet model is all on one Excel sheet, whereas a multi-sheet model will be split across 4 or more sheets, generally using one for each of Assumptions, Income Statement, Balance Sheet, Cash flow as a minimum, and often containing addition sheets for other calculations or output and analysis.
The benefit of a single sheet model is that if you are building a simple financial model they are much quicker and simpler to build, maintain, audit and format for printing.
However, if you are building a more complex model then having everything on one sheet becomes unmanageable and it is worth the time to set up a multi-sheet model.
In this example we’re going to build a multi-sheet model so let’s have a quick look at a single sheet model before we continue on.
Single Sheet Model
With single sheet model, you organize all the components of your model on one sheet, as different sections. They are also called vertical models for obvious reasons.
These models flow down vertically following the logic of the model so they start with assumptions, then the calculations and then the financial statements. They finish with the summary ratio analysis.
You should group the rows in each section, so that the model is easy to view.
Below is an example of the template for a single sheet model.
Multi sheet model
In a multi sheet model the assumptions, calculation, each financial statement and the analysis sheets are split onto separate sheets. A more complex model would also include separate tabs to contain more analysis and calculations of things like revenue forecasting, debt schedules etc.
After creating the header in one sheet of the model, you need to copy paste it into other sheets which will ensure your grid integrity, as discussed previously.
- Insert your desired layout for your Income Statement and Balance Sheet. For simplicity this should be based on your historic financial statements unless you feel you need to add to this analysis.
This will help ensure that all line items in your historic financial statements are incorporated into your model and make it easier to compare forecast figures with historic figures.
- It is essential to logically layout your assumptions so it they are easy to follow. A good way is to arrange your assumptions in the order in which they apply to your income statement, balance sheet and cash flow line items.
Only using formulas outside of the assumptions sheet will ensure that you don’t miss any assumptions.
- Correctly color coding the cells and creating a guide to the formatting codes is essential. This is usually done by creating a cover page with the details of the model and the color key. This will help anyone who reads the model to understand or modify it in the future.
You will have 5 main cell styles Business Comma, Percentage, Assumption/Input, Historical Financials and Formulas / Links.
You can create and save these cell styles by going to Home > Cell Styles > Create New Style (keyboard shortcut Alt + H + J + N).
You can also import styles from other open files by using Merge styles (Alt + H + J + M)
The normal cell format is the default cell format of the spreadsheet. You can go to Home > Cell Styles > Normal (right click)> Modify.
Then edit the number format as below
This will ensure that all the numbers in the workbook are by default displayed with one decimal place, negative numbers in brackets and zero as 0.0.
This style has the font color blue. This is used to clearly show that the data is historic financials.
When creating this style, only format the font color to Blue. Deselect all other settings. This is because you could have different number formats for you historic data, for example some might be %. The only purpose of this style is to change the font color, not the number format or other settings.
When creating this style, format only the font color to Blue, cell background to light yellow and borders to outline. Deselect the number, alignment and protection settings. You do not want to set a number format to your assumption. They can be % or number. You only want to apply font color, background color and border.
This is generally used for more advanced financial analysis which isn’t covered in this article. We’ve included this for completeness.
Instering Historic Finanicals
Once the template is ready, you need to input the historic income statement and balance sheet numbers. Your income statement should have the same rows as the companies’ published financials. However, some line items need to be show separately such as depreciation, amortization, interest income and interest expense, which will help in better forecasting the financials.
Historic financials are an integral part of the model. Based on these we can calculate ratios which can be used as assumptions for the future projections.
Since we are using a very simplified example, our income statement and balance sheet looks as below.
Note the checksum at the bottom of the balance sheet which is checking that it balances.
Calculating Historic Ratios and Building Assumptions
Once you have inserted your historic financials into the model you need to calculate your historic financial ratios. For example, metrics on revenue growth, COGS as % of revenue etc, to help you work out your assumptions.
Assumptions are the inputs that will drive the model. Based on the historic ratios, you insert the assumptions into the Assumptions tab.
If you are assuming constant growth rates / margins for any item it is advisable to keep it as hardcode in the first year and then link to the first year from the remaining years.
For example the sales growth rate in our example is 10%. In year 2017 we will input 10% as a hardcoded number. Then for 2018 and 2019 we will put a formula to link it to 2017. Thus, when a change is made to the number in 2017 cell, it will automatically update the number in 2018 and 2019. This is particularly useful when we do our scenario analysis. It also makes making changes very quick and simple if you have a 7 or 10 year financial model.
Our assumption sheet looks as below
Now that we’ve got a good start going, the next lesson will do a deeper dive into the calculations that we’ll be using throughout our model.