Financial Analysis & Forecasting |
|
|
Prepared by Matt H. Evans, CPA, CMA, CFM |
|
|
|
Purpose of Spreadsheet: |
|
|
Revised: |
11/22/2002 |
|
Home - Active Worksheet
active |
General Input
Wksh2 |
|
|
|
|
|
|
Balance Sheet
Wksh3 |
Income Statement
Wksh4 |
To illustrate concepts related to financial analysis and forecasting. |
|
|
|
|
|
Cash Flow Statement
Wksh5 |
Key Financials
Wksh6 |
The financial analysis uses a combination of ratios and industry averages to |
|
|
|
|
|
Ratio Analysis
Wksh7 |
Benchmark Analysis
Wksh8 |
evaluate the financial performance of the company. Trend line graphs are also |
|
|
|
|
|
Horizontal Analysis
Wksh9 |
Vertical Analysis
Wksh10 |
generated, comparing the company's performance with the industry averages. |
|
|
|
|
|
Pro Forma Financials - Simple Projection Model
Wksh11 |
Pro Forma Financials - Linear Trend Model
Wksh12 |
Finally, the historical information is used to prepare a set of pro forma |
|
|
|
|
|
Pro Forma Financials - Exponential Smoothing
Wksh13 |
Scenario Analysis
Wksh14 |
financial statements using both linear and non-linear functions. |
|
|
|
|
|
Budget Analysis
Wksh15 |
Final Budgets
Wksh16 |
|
|
|
|
|
|
|
|
Required Inputs: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
You will need to collect financial statements for several reporting periods. If you |
|
|
|
|
|
|
|
want to benchmark the performance against the industry, then you will also need |
|
|
|
|
|
|
|
to collect industry averages. The spreadsheet is setup to capture five reporting |
|
|
|
|
|
|
|
periods (annual, quarterly, monthly). All input fields are highlighted in yellow. |
|
|
|
|
|
|
|
For best results, SEC Filings are suggested since these reports provide more |
|
|
|
|
|
|
|
detail than published financial statements. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Note: A small red triangle in the upper right corner of a cell indicates that a comment has |
|
|
|
|
|
|
|
been inserted. Point your mouse over the cell and the comment will appear. |
|
|
|
|
Comment boxes are used to describe accounts, ratios, and other information used in this spreadsheet! Whenever you see a small red triangle, point your mouse over this cell for additional information.
|
|
|
|
|
|
|
|
|
|
|
If a cell appears in red, this indicates a warning concerning a calculation. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Worksheets: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
This spreadsheet consists of the following worksheets, divided into three sections: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
A) Input Worksheets for financial analysis using historical data: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Worksheet |
Title |
Purpose |
|
2 |
General Input |
Enter general information here - used on several worksheets. |
|
|
|
|
|
3 |
Balance Sheet |
Enter comparative balances sheets for up to five periods. |
|
|
|
|
|
4 |
Income Statement |
Enter comparative income statements for up to five periods. |
|
|
|
|
|
5 |
Cash Flow Statement |
Enter comparative cash flow statements for up to five periods. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Caution: If you enter less than five years of historical information, certain worksheet |
|
|
|
|
|
|
|
formulas may have to be revised. |
|
|
|
|
|
|
|
|
|
|
|
|
|
B) Output Worksheets for evaluating financial performance: |
|
|
|
|
|
|
|
|
6 |
Key Financial Data |
Calculates key financial information for further analysis. |
|
|
|
|
|
7 |
Ratio Analysis |
Calculates a series of ratios for further analysis. |
|
|
|
|
|
8 |
Benchmark Analysis |
Compare ratio analysis to industry averages. |
|
|
|
|
|
9 |
Horizontal Analysis |
Horizontal analysis with corresponding trend lines. |
|
|
|
|
|
10 |
Vertical Analysis |
Common size financials in percentages and graphs. |
|
|
|
|
|
|
|
|
|
|
|
|
C) Pro Forma / Forecasted Financials for Budgeting: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
11 |
Pro Forma - Simple |
Set of pro forma financials using simple assumptions |
|
|
|
|
|
12 |
Pro Forma - Regression |
Set of pro forma financials using linear trending |
|
|
|
|
|
13 |
Pro Forma - Exponential |
Set of pro forma financials using exponential smoothing |
|
|
|
|
|
14 |
Scenario Analysis |
Example of Scenario Analysis and Goal Seek Analysis |
|
|
|
|
|
15 |
Budget Analysis |
Preliminary budget analysis |
|
|
|
|
|
16 |
Final Budgets |
Set of budgets per various assumptions and forecasts. |
|
|
|
|
|
|
|
|
|
|
|
|
|
Note: Some additional worksheets (Answer Reports 1 & 2) may appear in the spreadsheet |
|
|
|
|
|
|
|
due to the running of Solver. |
|
|
|
|
|
|
Macros: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
No macros have been used in this spreadsheet to give everyone some assurance that no viruses |
|
|
|
|
|
|
|
are contained in the spreadsheet. However, you are free to add your own macros to save time. |
|
|
|
|
|
|
|
Tools > Macro > Record New Macro |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Excel Functions: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
This spreadsheet uses certain financial functions (such as =TREND) which might not be |
|
|
|
|
|
|
|
found in your version of Microsoft Excel. To take full advantage of financial and statistical |
|
|
|
|
|
|
|
functions, you should install the Add On package titled: Analysis TookPak. Go to the main |
|
|
|
|
|
|
|
tool bar, select Tools => Add-Ins => check the Analysis TookPak option, insert your |
|
|
|
|
|
|
|
Excel CD and install the Analysis ToolPak. Also, you might want to install the Solver |
|
|
|
|
|
|
|
Add-in since this is useful for solving special forecasting issues (such as finding the |
|
|
|
|
|
|
|
optimal exponential factor). |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Compatibility: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
This spreadsheet was created with Microsoft Excel 2000. Older versions of Excel (such as 97) |
|
|
|
|
|
|
|
may not be compatible with this spreadsheet. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Corrections: |
|
|
|
|
|
|
|
With any “attempt” to build an Excel Model, I can easily make some mistakes. |
|
|
|
|
|
|
|
So if you have suggestions to make the model better, drop me an email |
|
|
|
|
|
|
|
and I’ll be glad to improve the financial model. My email address is: |
|
|
|
|
matt@exinfm.com |
|
|