Purpose of Spreadsheet:
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.
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:
Purpose
Worksheet Title
2General Input Enter general information here - used on several worksheets.
3Balance Sheet Enter comparative balances sheets for up to five periods.
4Income Statement Enter comparative income statements for up to five periods.
5Cash 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:
6Key Financial Data Calculates key financial information for further analysis.
7Ratio Analysis Calculates a series of ratios for further analysis.
8Benchmark Analysis Compare ratio analysis to industry averages.
9Horizontal Analysis Horizontal analysis with corresponding trend lines.
10Vertical Analysis Common size financials in percentages and graphs.
C) Pro Forma / Forecasted Financials for Budgeting:
11Pro Forma - Simple Set of pro forma financials using simple assumptions
12Pro Forma - Regression Set of pro forma financials using linear trending
13Pro Forma - Exponential Set of pro forma financials using exponential smoothing
14Scenario Analysis Example of Scenario Analysis and Goal Seek Analysis
15Budget Analysis Preliminary budget analysis
16Final 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@
ed on several worksheets. s for up to five periods. ents for up to five periods. ments for up to five periods. certain worksheet
n for further analysis.
ding trend lines.
ages and graphs.
mple assumptions
xponential smoothing Goal Seek Analysis tions and forecasts.。