123456789 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 66 67 68 69 70 71 72 73 74 B C D E F G H I 5/14/2004 Sales Ln(Sales) 2001 $2,058 7.63 2002 2,534 23.1% 7.84 2003 2,472 -2.4% 7.81 2004 2,850 15.3% 7.96 2005 3,000 5.3% 8.01 Average = 10.3% THE SALES FORECAST Average annual growth rate = 10.3% Compound annual growth rate = 9.9% (Use the RATE function.) Intercept = -438,077 (Using the INTERCEPT function) Chapter 12. Tool Kit for Financial Planning and Forecasting Financial Statements Annual Growth Rate The first step in a sales forecast are several ways to estimate the historical growth rate, ranging from the simple to the complicated. The simplest are to estimate the average annual growth rate and the compound annual growth rate. We could also use regression analysis to estimate future sales. The easiest way is to plot the points using the Chart Wizard, as we did below. Then select the Chart, go the menu bar and select Chart, Add Trendline…, go to the Options tab (see screen shot below), check "Display equation on chart" and set the Forecast for 1 unit Forward. This will print the regression line on the chart and show the forecast for the next year. 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 MicroDrive Inc.'s sales over the past 5 years: The chart shows the regression line. If you actually want the regression intercept and slope, the easiest way is to use the function Wizard to create the INTERCEPT and SLOPE functions, as shown below. Annual Sales y = 220x -438077 $0 $1,000 $2,000 $3,000 $4,0002001 2002 2003 2004 2005 Year Net Sales C14:C18 B14:B18 C14:C18 B14:B1875 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 B C D E F G H I Slope = 220 (Using the SLOPE function) Projected sales for 2006 = 3,243 (Using the TREND function) Implied growth rate = 8.1% Slope = 8.7% (Using the SLOPE function) g = 9.1% (1+g) rate using LOGEST = 1.0910358 g = 9.1% The historical growth rates range from 8.1% to 10.3 percent, depending on the method. You could always use the estimated interecept and slope to project the future sales, but an even easier way is to use the TREND function. This allows you to specify the past years and sales, and then specify a projected year. It then fits the regression line and gives you the projected value. See below for details. The compound growth rate is very sensitive to the particular starting and ending dates that are chosen. One way to smooth this out is to regress the natural log (LN) of sales versus the years. The slope coefficient is the estimate of the historical sales growth rate. See the chart below; we plotted the trendline and the regression equation. Instead of doing a full regression with the Y variable being the log of sales, we could find the slope of the "log" regression directly using the LOGEST function. In this function, we simply specify the original sales as the Y variable, the years as the X variable, and the function finds the "log-based" slope coefficient, which is an estimate of (1+g). To find the growth rate, raise e to the slope (this is eslope) and then subtract 1. Natural Log (LN) of Sales y = 0.0871x -166.67 7.60 7.70 7.80 7.90 8.00 8.10 8.202001 2003 2005 2007 C14:C1 B14:B1 2005146 147 148 B C D E F G H I 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%.149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 B C D E F G H I INCOME STATEMENT (in millions of dollars) 2004 2005 Sales $2,850.0 $3,000.0 Costs except depreciation $2,497.0 $2,616.2 Depreciation $90.0 $100.0 Total operating costs $2,587.0 $2,716.2 EBIT $263.0 $283.8 Less Interest $60.0 $88.0 Earnings before taxes (EBT) $203.0 $195.8 Taxes (40%) $81.2 $78.3 NI before preferred dividends $121.8 $117.5 Preferred dividends $4.0 $4.0 NI available to common $117.8 $113.5 Dividends to common $53.0 $57.5 Add. to retained earnings (RE) $64.8 $56.0 Shares of common equity 50 50 Dividends per share $1.06 $1.15 Price per share $26.00 $23.00 BALANCE SHEET (in millions of dollars) 2004 2005 Assets Cash $15.0 $10.0 ST Investments $65.0 $0.0 Accounts receivable $315.0 $375.0 Inventories $415.0 $615.0 Total current assets $810.0 $1,000.0 Net plant and equipment $870.0 $1,000.0 Total assets $1,680.0 $2,000.0 2004 2005 Liabilities and equity Accounts payable $30.0 $60.0 Accruals $130.0 $140.0 Notes payable $60.0 $110.0 Total current liabilities $220.0 $310.0 Long-term bonds $580.0 $754.0 Total liabilities $800.0 $1,064.0 Preferred stock $40.0 $40.0 Common stock $130.0 $130.0 Retained earnings $710.0 $766.0 Total common equity $840.0 $896.0 Total liabilities and equity $1,680.0 $2,000.0 The next step is to analyze the historical "Pro Forma" ratios. The actual historical statements are shown below. The ratios needed for the Pro Forma analysis are shown below the actual statements. The text examines a forecast for a firm using the percentage of sales of method. This forecasting method assumes that many items on the financial statements are proportional to sales. In particular, it assumes that the following items are proportional to sales: (1) Costs; (2) Cash (i.e., the company needs a certain amount of cash on hand, since it does not know exactly when the checks it writes or deposits will clear the bank); (3) Accounts receivable (the proportion will depend on the firm's credit policy, which is discussed in Chapter 14); (4) Inventories; (5) Net plant and equipment (this is reasonable for the long-term; in the short-term, firm's often have excess capacity, which we discuss later in this model); (6) Accounts payable; and (7) Accruals. It also assumes that Depreciation is proportional to Net plant and equipment. Other items on the financial statements are a direct result of the firm's financial policies (i.e., dividend policy and capital structure policy), which we discuss below.210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 B C D E F G H I Preliminary Pro Forma Ratios Historical Industry Forecast 2004 2005 Average Composite 2006 Costs /Sales 87.6% 87.2% 87.4% 87.1% 87.2% Depreciation /Net plant & equip. 10.3% 10.0% 10.2% 10.2% 10.0% Cash /Sales 0.5% 0.3% 0.4% 1.0% 0.3% Accounts Rec. /Sales 11.1% 12.5% 11.8% 10.0% 12.5% Inventory /Sales 14.6% 20.5% 17.5% 11.1% 20.5% Net plant & equip. /sales 30.5% 33.3% 31.9% 33.3% 33.3% Accounts Pay. /Sales 1.1% 2.0% 1.5% 1.0% 2.0% Accruals /Sales 4.6% 4.7% 4.6% 2.0% 4.7% Long-term bonds/operating assets 35.9% 37.7% 36.8% 30.9% 37.7% Other Inputs Sales Growth Rate 10% Tax rate 40% Dividend growth rate 8% Interest rate on notes payable and short-term investments 9% Interest rate on long-term bonds 11% Coupon rate on preferred stock 10% Table 12-2 MicroDrive, Inc.: Actual and Projected Income Statements (Millions of Dollars) Actual Forecast 2005 Forecast basis 2006 (1) (2) (3) Sales 3,000.0 $ 110% x 2005 Sales = 3,300.0 $ Costs except depreciation 2,616.2 87.2% x 2006 Sales = 2,877.6 $ Depreciation 100.0 10% x 2006 Net plant = 110.0 $ Total operating costs 2,716.2 $ 2,987.6 $ EBIT 283.8 $ 312.4 $ Less Interest 88.0 92.8 $ Earnings before taxes (EBT) 195.8 $ 219.6 $ Taxes (40%) 78.3 87.8 $ NI before preferred dividends 117.5 $ 131.8 $ Preferred dividends 4.0 4.0 $ NI available to common 113.5 $ 127.8 $ Shares of common equity 50.0 50.0 $ Dividends per share 1.15 $ 108% x 2005 DPS = 1.25 $ Dividends to common 57.5 $ 62.5 $ Add. to retained earnings 56.0 $ 65.3 $ Table 12-3 MicroDrive, Inc.: Actual and Projected Balance Sheets (Millions of Dollars) Actual Forecast 2005 Forecast basis 2006 (1) (2) (3) Assets Cash 10.0 $ 0.33% x 2006 Sales = 11.0 $ ST investments 0.0 0.0 Accounts receivable 375.0 12.50% x 2006 Sales = 412.5 Inventories 615.0 20.50% x 2006 Sales = 676.5 Total current assets 1,000.0 $ 1,100.0 $ Net plant and equipment 1,000.0 33.33% x 2006 Sales = 1,100.0 Total assets 2,000.0 $ 2,200.0 $ Liabilities and equity Accounts payable 60.0 $ 2.00% x 2006 Sales = 66.0 $ Accruals 140.0 4.67% x 2006 Sales = 154.0 Notes payable 110.0 224.7 Total current liabilities 310.0 $ 444.7 $ Long-term bonds 754.0 754.0 Total liabilities 1,064.0 $ 1,198.7 $ Preferred stock 40.0 40.0 Common stock 130.0 130.0 Retained earnings 766.0 831.3 Total common equity 896.0 $ 961.3 $ Total liabilities and equity 2,000.0 $ 2,200.0 $ Required assetsa 2,200.0 $ Specified sources of financingb 2,085.3 $ Actual Same: no new issue 2005 RE + 2006 Add. to RE = Interest rate x 2005 debt = Dividend rate x 2005 preferred = 2005 DPS x # shares = Note: we have used the ROUND function to make the calculations consistent with Previous plus "plug" if needed Previous plus "plug" if needed Same: no new issue Same: no new issue284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 B C D E F G H I Additional funds needed (AFN) 114.7 $ Required additional notes payable 114.7 $ Additional short-term investments 0.0 Required assets include all forecasted operating assets plus the short-term investments from the previous year. MicroDrive Statement of Cash Flows for Years Ending Dec. 31 Actual Forecast (in millions of dollars) 2005 2006 (1) (3) Operating Activities Net Income before preferred dividends 117.5 $ 131.8 $ Noncash adjustments Depreciation and amortization 100.0 $ 110.0 $ Due to changes in working capital Increase in accounts receivable (60.0) $ (37.5) $ Increase in inventories (200.0) $ (61.5) $ Increase in accounts payable 30.0 $ 6.0 $ Increase in accruals 10.0 $ 14.0 $ Net cash provided by operating activities (2.5) $ 162.8 $ Long-term investing activities Cash used to acquire fixed assets (230.0) $ (210.0) $ Financing Activities Sale of short-term investments 65.0 $ -$ Increase in notes payable 50.0 $ 114.7 $ Increase in bonds 174.0 $ -$ Payment of common and preferred dividends (61.5) $ (66.5) $ Net cash provided by financing activities 227.5 $ 48.2 $ Net cash flow (5.0) $ 1.0 $ Cash and securities at beginning of the year 15.0 $ 10.0 $ Cash and securities at end of the year 10.0 $ 11.0 $ ANALYSIS OF THE PLAN: FREE CASH FLOW, RATIOS, AND AFN Specified sources of financing include forecasted operating current liabilities, forecasted long-term bonds, forecasted preferred stock, forecasted common equity, and the amount of notes payable from the previous year.325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 B C D E F G H I Table 12-4 Model Inputs, AFN, and Key Ratios (Millions of Dollars) Preliminary Revised Industry Actual Actual Forecast Forecast Average 2004 2005 2006 2006 2005 (1) (2) (3) (4) Model Inputs Costs (excluding depreciation) as percent of sales 87.2% 87.2% 86.0% 87.1% Accounts receivable as percent of sales 12.5% 12.5% 11.8% 10.0% Inventory as percent of sales 20.5% 20.5% 16.7% 11.1% Model Outputs Net operating profit after taxes (NOPAT) 170.3 $ 187.4 $ 211.2 $ Net operating working capital (NOWC) $585 800.0 $ 880.0 $ 731.5 $ Total operating capital $1,455 1,800.0 $ 1,980.0 $ 1,831.5 $ Free cash flow (FCF) (174.7) $ 7.5 $ 179.7 $ Additional funds needed (AFN) 114.7 $ (57.5) $ Ratio Analysis Current ratio 3.2 2.5 3.1 4.2 Inventory turnover 4.9 4.9 6.0 9.0 Days sales outstanding 45.6 45.6 43.1 36.0 Total assets turnover 1.5 1.5 1.6 1.8 Debt ratio 53.2% 54.5% 51.4% 40.0% Profit margin 3.8% 3.9% 4.6% 5.0% Return on assets 5.7% 5.8% 7.2% 9.0% Return on equity 12.7% 13.3% 15.4% 15.0% Return on invested capital 9.5% 9.5% 11.5% 11.4% The following table shows key outputs of the preliminary plan. We used the Scenario Manager to develop the key outputs for the revised plan.359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 B C D E F G H I USING THE AFN EQUATION AFN= Required Assets -Spontaneous Liabilities -Retained Earnings Required Assets = Asset to Sales Ratio x Sales = 0.667 x $300.00 = $200.00 Spontaneeou Liabilities = Spontaneous Liab. to Sales Ratio x Sales = 0.067 x $300.00 = $20.00 Retained Earnings = Profit Margin x Sales x Retention Ratio = 0.038 x 3,300.0 $ x 0.493 = $61.58 AFN= Required Assets -Spontaneous Liabilities -Retained Earnings = $200.00 -$20.00 -$61.58 AFN= $118.42 We can look at the additional funds needed in another manner, using the AFN equation described in the text. This method identifies the additional funds needed as being the difference between the change in assets and 'the cumulative change in spontaneous liabilities and retained earnings. Notice that the AFN from the formula is a little different than the AFN from the percent of sales Pro Forma financial statements. This is because the AFN formula assumes all ratios in the projected year will be the same as the most recent year, including the profit margin. We can also rearrange the AFN formula to find the growth rate at which no external financing is required. At each place in the equation, we substitute gS0 for S and we substitute S0+gS0 for S1. We then solve for g. 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 B C D E F G H I g for zero AFN= Profit margin x Retention ratio ÷ Asset to Sales Ratio -Spontaneous Liab. to Sales Ratio = 0.038 x 0.493 ÷ 0.667 -0.067 = 0.01866 ÷ 0.581333333 g for zero AFN= 3.21% Therefore, if MicroDrive's ratios remain constant, MicroDrive can grow at about 3.21% without needing external financing. EXCESS CAPACITY ADJUSTMENTS 2005 Sales $3,000 2006 Sales $3,300 Percentage of capacity 96% 2005 Fixed Assets $1,000 Full capacity sales = $3,125 Target FA/Sales = 0.32 Required level of FA = $1,056 We have just stated that assuming current assets to grow at the same rate of sales is not necessarily correct. The same can be said of fixed assets. For instance, let us assume that the firm in our example is not operating at full capacity. This means that they could achieve a greater level of production from their fixed assets. Remember, sales for the last year were $3,000 million, while fixed assets were $1,000 million. Now, let us hypothesize that the firm was only operating at 96 percent of full capacity. We can use this information to calculate the firm's full capacity sales and the target fixed assets-to-sales ratio.