17model
10/6/2009 8:16
12/2/2002
Chapter 17.
Model for Financial Forecasting
Strategic planning is one of the core functions of an organization, and it involves the coordination of
operating plans with financial plans. While operational plans outline how the firm intends to reach its
corporate objectives, financial plans outline the manner in which the firm will obtain the necessary
productive assets to operate. Financial planning generally begins with a sales forecast, and that forecast
generally starts with a review of the firm's recent history.
Here are Allied Food's sales over the past 5 years:
1998
$2,058
1999
2,534
2000
2,472
2001
2,850
2002
3,000
We can use Excel's regression function to forecast sales for 2003, proceeding as follows:
Click Tools > Data analysis > Regression >OK.
This produces the dialog box shown here, which we filled in.
If you do not have the Data Analysis option in your Tools menu, refer to the directions in cells J6 through J10.
The regression output is shown beginning in Cell J13 (which we specified in the dialog box to get it out of the
way). A graph of the data is shown out in Cell L13, because we checked the "Line Fit Plot" box in the dialog
box.
The resulting regression equation is shown below, with the prediction for Year 2002 shown in yellow to
confirm that we have indeed set the equation up properly:
Predicted sales
=
Intercept
+
X- Coefficient
x
Year
=Predicted Value
-$437,417
+
$220.0
x
2002
=
$3,022.8
We can use the regression results to find the predicted sales for 2003.
We have, on row 50, the regression
equation. Currently, we show the year as 2002 in Cell F50.
We can type in 2003 in Cell F50 to override the
2002, and the equation will instantly recalculate the
predicted sales for 2003 as $3,242.8.
Note that the company actually predicted that sales in 2003 would be $3,300, not the regression prediction of
$3,242.8. Management started with the regression prediction, then modified it based on qualitative data to
$3,300, the forecasted value given in the text.
Management's sales forecast represents a growth rate of 10%.
The text examines a forecast for a firm using the percentage of sales of method.
Quite simply, this forecasting
method operates under the premise that as a firm expands, most of its financial statement items grow at the
same rate as sales, thus keeping everything at a constant percentage of sales.
In other words, assets have
constant productivity.
In the real financial environment, this assumption generally does not hold true.
Still,
the procedure does provides us with a good foundation to learn financial forecasting. The second step in the
financial forecast, after generating a sales forecast, is to construct a pro forma income statement.
A