{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

17 Chapter model

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

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

17 Chapter model 3/22/2010 7:09 2/16/2006 Chapter 17. Financial Planning and Forecasting Allied Food's sales: 2001 \$2,058 2002 2,534 2003 2,472 2004 2,850 2005 3,000 2006 trend line forecast 3,273 We use Excel's LOGEST function to forecast sales for 2006, proceeding as follows: Click fx to open the function wizard, then click "Statistical," then LOGEST, and then specify B9:B13 as the range for the y variable and A9:A13 as the x variable range. Then click OK to get 1.091036 , which is 1 plus the growth rate. Subtract 1 to get the growth rate, 0.091 = 9.1%. Allied increased the 9.1% historical growth rate to 10% due to other factors management considered. Can be used to get an estimate of funds needed if ratios remain constant. A* \$2,000 AFN = \$3,000 = \$200.0 -\$20.0 -\$65.98 \$3,300 = \$114.0 ΔS \$300 Data used in eqn are from 2005 financial statements shown below plus L* \$200 the forecasted \$3,300 sales for 2006. M 3.916% RR 51.06% 10.000% The Sales Forecast (Section 17.2) THE AFN EQUATION (Section 17.3) (A*/S 0 )ΔS - (L*/S 0 )ΔS - MS 1 (RR) S 0 S 1 The AFN equation can be used to estimate the maximum growth rate without resorting to external financing. First, we define the sales growth rate as follows: g S = ΔS/S 0 = Now we can substitute different values for S 1 until we find the value at which AFN = 0 and then see what the corresponding g is at that S 1 . Using goal seek, we find that a value of \$3,103 for S 1 , which translates to a sales growth rate of 3.447%, is consistent with AFN = \$0. Therefore, Allied could grow at a rate of 3.447% without having to seek external funds. Plug in \$3,103.422 for S 1 to see that this statement is correct. 2001 2002 2003 2004 2005 \$1,500 \$2,000 \$2,500 \$3,000 \$3,500 f(x) = 220x - 438077.2 Sales, 2001-2005 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

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

View Full Document
FINANCIAL STATEMENTS: INCREASE ITEMS AT THE SALES GROWTH RATE KEY INPUT DATA: Raise AFN as follows: 10.00% Notes 7% These percentages a 10.00% LT debt 46% close to the current Tax rate 40.00% Com. Stk. 47% capital structure. Interest rate on all debt 10.25% 100% Income Statements Actual Increases 2006 Forecast 2005 with sales 1st Pass Adjustment 2nd Pass Adjustment Sales \$3,000.0 \$3,300.0 \$3,300.0 Costs except depr'n 2,616.2 \$2,877.8 \$2,877.8 Depreciation 100.0 \$110.0 \$110.0 Total operating costs \$2,716.2 \$2,987.8 \$2,987.8 EBIT \$283.8 \$312.2 \$312.2 Less Interest 88.0 carried over \$88.0 \$6.1 \$94.1 \$0.2 EBT \$195.8 \$224.2 \$218.1 Taxes (40%) 78.3 \$89.7 \$87.2 Net income \$117.5 \$134.5 \$130.9 Common dividends \$57.5 \$63.3 \$63.3 Addition to RE \$60.0 \$71.3 \$67.6 Retention Ratio 0.5298 0.5167 Sales Growth Rate, g s Dividend growth rate, g d Other values could be used for the input data. The sales growth rate was analyzed above, and the inter and dividend growth rate both reflect the analysts' opinions about these factors during the coming year. T managers have additional knowledge and insights, so they may ask the analysts to adjust their initial assu With the model, such adjustments are very easy--just change the input data.
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}