{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

# 17model - A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19...

This preview shows pages 1–3. Sign up to view the full content.

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 B C D E F G H 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
KEY INPUT DATA Sales Growth Rate 10% Tax rate 40% Dividend growth rate 8% INCOME STATEMENT (in millions of dollars) Forecast basis 2002 % of sales 2003 Sales \$3,000.0 100.00% \$3,300.00 Costs except depreciation 2,616.2 87.21% \$2,877.82 Depreciation 100.0 3.33% \$110.00 Total operating costs 2,716.2 90.54% \$2,987.82 EBIT 283.8 9.46% \$312.18 Less Interest 88.0 \$88.00 Earnings before taxes (EBT) 195.8 \$224.18 Taxes (40%) 78.3 \$89.67
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### Page1 / 6

17model - A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online