Below are some FAQs on the ‘Historical Financial Analysis’ Model:
What is the purpose of the model (overview)?
This section provides an overview of the ‘Historical Financial Analysis Model and how to use it. The ‘Historical Financial Analysis’ model is designed to help the user understand the historical financial statements of the company that they wish to analyse. The model is structured in the following way:
Each worksheet in the model will fall into any one of the above three categories. It is only in the “Input” worksheets (highlighted in blue) that the user has to enter data. The following 3 worksheets act as data input sheets in the model:
Before you start entering data in the worksheets, it would be good to spend a minute on the “Legend” worksheet which provides information regarding the meaning of different formatting of the cells. For instance,
- cells in which the user is required to input “hard-coded” data would be formatted as
- any cells which are drop-down menus would be formatted as
Note that apart from these two types of cells, you should not edit/ delete any other cell.
The model also contains a “Financial Analysis” tab located in the top menu of the excel workbook. Specific details on this are given below.
What is the “Financial Analysis” tab and how to use it?
The “Financial Analysis” tab is located in the top menu of the excel workbook (located in the excel ribbon).
This tab is divided into the following 5 sections:
- Add rows and columns; and
- Delete rows and columns.
These sections are explained below.
What is the FAQs section in the ‘Financial Analysis’ tab?
As the name suggests, clicking on the FAQs section in the ‘Financial Analysis’ tab would open this FAQs page when you are connected to the internet.
What is the ‘Results’ section in the ‘Historical Financial Analysis’ Model?
The “Results” section has the following two dropdown menus:
- Start Result: Select the result that you wish to start your analysis from. Even though the model may start from Financial Year 2007, in case you have financials going back to only Financial Year 2012, choosing ‘FY2012’ as your result will enable the analysis from that date.
- Latest Result: This is the latest result that your company has released and which you have updated in your model. Note: When you update a new result in your model by adding a new result column (discussed later), make sure you save the workbook, close it, and then reopen again for the ‘Latest Results’ dropdown menu to update with that latest result.
What is the ‘Adjustments’ section in the ‘Financial Analysis’ tab?
The adjustments section gives you the option of deciding whether you wish to adjust for the following loose ends in your analysis:
- Capitalising operating leases;
- Capitalising R&D (or any other) expenses;
- Computing the value of employee options; and
- Computing ratios such as Return on Capital, Return on Equity, etc. using average of the opening and closing balances or simply based on opening balances.
Simply click on the choices to ‘adjust/ not adjust’ for these loose ends.
What is the ‘Add rows and columns’ section in the ‘Financial Analysis’ tab?
Add Row: This section allows you add any additional rows for each section of the financial statements.
When you click on the Add Row button, a user form will pop-up which will prompt you to select the item (such as Revenues, Operating Expenses, etc.) in which you wish to add another row.
Add Column: Clicking this button will allow you to add a new column for a new result that you may need to update for your company. Note: When you update a new result in your model by adding a new result column, make sure you save the workbook, close it, and then reopen again for the ‘Latest Results’ dropdown menu to update with that new result.
What is the ‘Delete rows and columns’ section in the ‘Financial Analysis’ tab?
Just as the ‘add rows and columns’ section, this section allows you to delete any row in a specific section or delete the latest column.
How to incorporate debt value of operating leases, capitalising R&D expenses and option values for each year in the model?
In the I|Adjustments worksheet, you have been provided the option to compute the values of the above adjustments for each financial year. Although these are computed automatically, the values are not picked up in the I|Financials worksheet automatically. You would have to do this manually in the following way:
- Choose the financial year for which you wish to make those adjustments from the drop-down menu against cell “Calculate for”
- Once the computation is done, copy the output (for instance debt value of operating leases) and paste special “as values” (make sure you do not paste the formatting) in the relevant cell in the I|Financials worksheet.
- Repeat this process for each financial year separately.
What are the outputs from the model?
The output from the model is in the form of charts dashboard which are generated from the financials that you have input into the model. The following output sheets are generated:
What color theme to use?
As mentioned earlier, unique cell formatting has been applied to the cells to distinguish them from each other. The color theme that has been used is a customised color theme called CS-1.
It is possible that your excel file may be using the default color theme of Microsoft Excel. I would urge you to modify and use the following color scheme.
You can customise the color theme with the following steps in Microsoft Excel 2016:
- Go to Page Layout —> Colors
- Click on ‘Customize Colors’
- A new dialog box will open where you can customise the colors.