Chapter 6 - Financial Modeling

Question 1

1. Why are models circular?

Answer 1

Models can be circular for a number of reasons, but the most common
reason relates to interest. If the model shows that the company
needs more cash, the model will draw down from the revolving credit
facility. Drawing down from the revolving credit facility will result in
more interest expense, which will require more money, which will lead
to a larger drawdown, which will result in more interest expense, and
so forth. Similarly, excess cash will result in more interest income, which
will result in more cash and more interest income, and so forth.

Question 2

2. What are some best practices for building financial models?

Answer 2

Some best practices for building models include keeping all of the
financial statements on one tab, having no hardcodes on the model tab,
having a separate tab for assumptions, being consistent with formatting
and formatting as you go, keeping a list of your assumptions and
sources, turning off gridlines, saving often, and saving new versions.

Question 3

3. How do you calculate the revolver?

Answer 3

Note that there are several formulas you can use to calculate the
change to the revolver. One such formula is as follows. To calculate the
change to the revolver, you can take the negative of the minimum of
the beginning revolver balance and everything else that happens to cash
during that period including the beginning cash balance.

Question 4

4. How might you forecast revenue for a retailer?

Answer 4

You might forecast revenue for a retailer simply by growing last
period’s revenue by some growth rate. You might also forecast revenue
by multiplying the number of stores or store square footage by some
appropriate metric for sales per store or sales per square footage. In
a very detailed model, you might forecast revenue by aggregating the
revenue forecast for each store or even for each product sold by the
company.

Question 5

5. How might you forecast revenue for a telecommunications company?

Answer 5

You might forecast revenue for a telecommunications company simply
by growing last period’s revenue by some growth rate. You might
also forecast revenue by multiplying average revenue per user by the
number of users. You might forecast the number of users each period by
adding last period’s users plus new users less churn.

Question 6

6. Why is it important to spread historical financials when building
   models?

Answer 6

several reasons. First, you will calculate certain ratios and statistics
based on the historical financial statements that you can use to help select
your model drivers and assumptions. Second, the historical balance
sheet is the beginning balance sheet for your forecast model. Third, it
is important to compare historical financial statements with projected
financial statements to determine the reasonableness of your forecasts,
and to help spot modeling mistakes and/or poorly chosen assumptions.

Question 7

7. Walk me through building a model.

Answer 7

First, you need to learn about the company, its industry, and the factors
that will drive the company’s business. Second, you need to spread
the historical financial statements and calculate key statistics such as
growth rates and margins from those statements. Next, you need to select
your model drivers and assumptions. After that, you can model the
three financial statements, beginning with the income statement, then
the balance sheet, and finally the cash flow statement. You will also
include other supporting schedules such as the debt schedule. Assuming
the model balances, you should stress‐test it, and then check it to
make sure your assumptions are reasonable and that you do not have
any modeling or linking errors. Finally, you can create any supporting
schedules or exhibits that you need, and perform any additional analysis
such as a DCF analysis.

Question 8

8. What is the best way to check a model?

Answer 8

Once a model has been stress‐tested and the balance sheet balances,
the best way to check a model is to print it out and analyze the results.
Compare the projected results with historical results and look for any
large increases or decreases from year to year. Also, calculate key ratios
with a calculator and compare them to the results from the model printout.
If there are figures that look odd or unreasonable, then go back into
Excel and check the model formulas and assumptions.