Things to know before building the financial model | List of required Excel worksheets

 

There are few things that a modeler
should know before building the financial model. We will try to keep this
post as short as possible and most importantly to the point, which will help
you to build the model with much more ease.

Start with determining the number of
tabs or worksheets to be built before starting the modeling exercise i.e. before
punching even a single number you should know how many tabs or how many
worksheets will be required for a particular financial model of a company.

Following are the common tabs (in
sequence) for a financial model of a publicly listed company:

1. Cover sheet: 

It should always be updated with recent
information. It contains information such as Name of the project (for example
Five-year projection of the company “Alpha”), Ticker, Model date, Creator, Reviewer,
Confidential messages, Disclaimer, etc

2. Output sheet: 

People usually place it at the end of the model
but the right approach is to places it just after the cover sheet. It includes
key statistics and ratios of the company. It is one of the important sheet
where the investor will be spending a lot of his time. 

3. Valuation sheet / Credit rating
sheet: 

It will include
the summary of your valuation approaches (It can also be clubbed with output
sheet)

4. Assumption sheet: 

It includes the key assumptions that were
used to build the model. Basically, it shows how the revenue and costs are
derived for a particular company, the assumed growth rate, etc.

5. Working sheets: 

It includes the Income statement, Balance
sheet, Cash flow statement, Working capital schedule, Depreciation and
amortization schedule, Operating statistics, etc. In other words, the working
sheet includes all the quantitative part of the financial model.

6. Qualitative data/additional sheets: 

These sheets do not impact the financial
model directly but they are used by the investor to get the necessary
information about the company which may be quantitative as well as qualitative
in nature.

Such as, what is the reason behind
increasing revenue or increasing cost? (This information can be found out in the
Management Discussion and Analysis (MD&A) section in the annual report). Examples
of the sheets that needs to be included are MD&A analysis, Management’s
guidance and recommendation, Expectation of sales, revenue, repayment of the debt,
etc. for next quarter or year, Board of directors and management profiles, Management
compensation summary, etc.

Include all the other relevant information
that you believe is important for the investor in his decision-making process.

These were few initial things that a
modeler should know before building the financial model. I hope this post
was useful, keep checking out our latest post for the next part of the
financial modeling series which will be out any soon.

Note: This post is a continuation of
our financial modeling series, listed below

Financial Modeling series:

1. What is financial modeling – It’s scope, uses, types, and how to build a financial model? | Explained

2. Excel best practices while preparing financial models | List of important MS-Excel shortcuts

3. Things to understand before buildingthe financial model

Thanks for reading

The Finance Magic