1 of
9
BOC Model for Chapter 8, Financial Forecasting
3/1/03
Note also that the model contains circular references.
This is done deliberately, so if Excel asks about it, just click
"Cancel."
Finally, note that we use Excel's Scenario Manager in this model.
You can skip this, but Scenario Manager is a very useful
tool, and we use it in a number of subsequent chapter models.
Therefore, if you have time, It would pay you to to learn how to use
it.
See the explanation on the tab labeled "Using the Scenario Manager Tool" when you get to the appropriate section of the
Main Model.
THE SALES FORECAST
Financial planning begins with a sales forecast, and that forecast generally starts with a review of the
firm's recent history.
Here are Company X's sales over the past 5 years:
Sales
Growth
1999
$844.5
2000
$1,400.0
65.8%
2001
$1,500.0
7.1%
2002
$1,666.7
11.1%
2003
$2,000.0
20.0%
Average =
26.0%
To make the graph, highlight the range A12:B16, click the chart wizard, select the scatter diagram, and then choose the
graph with dots and curved lines. You can "pretty up" the graph, but you don't have to. You can add the trend line by having
the chart active, then clicking "chart" on the tool bar and then "add trend line." Then click "Options" and check the
appropriate boxes to get the equation and R-square.
There are several ways to estimate the historical growth rate.
The simplest is the average annual growth rate as shown above.
We could also get the point-to-point compound growth rate, from 1998 to 2003, using the RATE function:
Compound annual growth rate =
24.1%
Note, though, that this ignores all data for the intervening years, hence is too heavily influenced by the first and last years.
It is generally better to use regression to estimate growth, as this takes account of all the data. Also, it is better to regress
logs of the data rather than the data themselves. You could use Excel's regression function, but it is easier to use the
LOGEST function, proceeding as follows:
Click fx > Statistical > LOGEST. Then specify C13:C17 as the Y variable range
and G 13:B17 as the X variable range.
Click OK and subtract 1.0 to get the growth rate: This is the best historical growth
rate:
20.9%
The regression forecast, rounded to 20%, is used in the forecast.
20%
This model goes through the steps required to make and then evaluate a financial forecast.
No forecast will ever be exactly
correct because the inputs cannot be predicted exactly.
Still, given the inexact nature of the forecasted inputs, this model is
accurate enough for most purposes.
The model goes through a series of iterations to find the AFN that causes the liabilities and capital to equal total assets.
FOR
THE ITERATIONS TO OCCUR, YOUR COMPUTER MUST BE SET TO ITERATE.
Click
Tools>Options>Calculations
and then check the
"Iterations" box and enter 100 in the number of iterations box.
We provide historical data on Company X, along with industry average data.