Financial Planning and Forecasting Tool Kit

Financial Planning and Forecasting Tool Kit - B C D E F G H...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: B C D E F G H I 1 12/20/2006 2 3 Tool Kit for Financial Planning and Forecasting Financial Statements 4 5 MicroDrive's recent financial statements are shown below. 6 7 INCOME STATEMENT 8 (in millions of dollars) 2006 2007 9 10 Sales $2,850.0 $3,000.0 11 Costs except depreciation $2,4 97.0 $2,616.2 12 Depreciation $90.0 $100.0 13 Total operating costs $2,587.0 $2,716.2 14 EBIT $263.0 $283.8 15 Less Interest $60.0 $88.0 16 Earning s before taxes (EBT) $203.0 $195.8 17 Taxes (4 0%) $81.2 $78.3 18 NI before preferred dividends $121.8 $117.5 19 Preferred dividends $4 .0 $4 .0 20 NI available to common $117.8 $113.5 21 22 Dividends to common $53.0 $57.5 23 Add. to retained earnings (RE) $64 .8 $56.0 24 25 Shares of common equity 50 50 26 Dividends per share $1.06 $1.15 27 Price per share $26.00 $23.00 28 29 BALANCE SHEET 30 (in millions of dollars) 31 2006 2007 32 Assets 33 Cash $15.0 $10.0 34 ST Investments $65.0 $0.0 35 Accounts receivable $315.0 $375.0 36 Inventories $4 15.0 $615.0 37 Total current assets $810.0 $1,000.0 38 Net plant and equipment $870.0 $1,000.0 39 Total assets $1,680.0 $2,000.0 40 41 2006 2007 42 Liabilities and equity 43 Accounts payable $30.0 $60.0 44 Accruals $130.0 $14 0.0 45 Notes payable $60.0 $110.0 46 Total current liabilities $220.0 $310.0 47 Long -term bonds $580.0 $754 .0 48 Total liabilities $800.0 $1,064 .0 49 Preferred stock $4 0.0 $4 0.0 50 Common stock $130.0 $130.0 51 Retained earning s $710.0 $766.0 52 Total common equity $84 0.0 $896.0 53 Total liabilities and equity $1,680.0 $2,000.0 54 55 56 SALES FORECAST (Sect ion 14 .2) 57 58 Strategic planning is one of the core functions of an organization, and it involves the coordination of operating plans with 59 financial plans. While operational plans outline how the firm intends to reach its corporate objectives, financial plans 60 outline the manner in which the firm will obtain the necessary productive assets to operate. Financial planning generally 61 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: 62 63 64 Annual Growth 65 Sales Rate Ln(Sales) 66 2003 $2,058 7.63 67 2004 2,534 23.1% 7.84 68 2005 2,4 72 -2.4 % 7.81 69 2006 2,850 15.3% 7.96 70 2007 3,000 5.3% 8.01 71 Averag e = 10.3% 72 73 SALES FORECAST THE 74 75 first step in a sales forecast are several ways to estimate the historical growth rate, ranging from the simple to the complicated. The The 76 simplest are to estimate the average annual growth rate and the compound annual growth rate. 77 78 Averag e annual g rowth rate = 10.3% 79 80 Compound annual g rowth rate = 9.9% (Use the RATE function.) 81 82 83 could also use regression analysis to estimate future sales. The easiest way is to plot the points using the Chart Wizard, as we did We 84 below. Then select the Chart, go the menu bar and select Chart, Add Trendline..., go to the Options tab (see screen shot below), check 85 "Display equation on chart" and set the Forecast for 1 unit Forward. This will print the regression line on the chart and show the 86 forecast for the next year. 87 88 89 Annual Sa les Net 90 Sale s $4 ,00 0 91 92 93 $3,00 0 94 f(x) = 2 20x - 4 3851 7.2 95 96 $2 ,00 0 97 98 99 $1 ,00 0 100 101 $0 102 103 200 3 20 04 20 05 20 06 200 7 104 Ye ar 105 106 107 chart shows the regression line. If you actually want the regression intercept and slope, the easiest way is to use the function Wizard The 108create the INTERCEPT and SLOPE functions, as shown below. to 109 110 111 D66:D70 112 C66:C70 113 114 115 116 117 118 119 120 121 Intercept = -4 38,517 (Using the INTERCEPT function) 122 123 124 D66:D70 125 C66:C70 126 127 128 129 130 131 132 133 134 135 Slope = 220 (Using the SLOPE function) 136 137 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. 138 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 This 139 projected value. See below for details. 140 141 142 D66:D70 143 C66:C70 144 C15:C19 145 C156 146 147 148 149 150 151 152 153 154 155 156 Projected sales for 2008 = 3,24 3 (Using the TREND function) 157 Implied g rowth rate = 8.1% 158 159 160 161 compound growth rate is very sensitive to the particular starting and ending dates that are chosen. One way to smooth this out is to The 162 regress the natural log (LN) of sales versus the years. The slope coefficient is the estimate of the historical sales growth rate. See the 163 below; we plotted the trendline and the regression equation. chart 164 165 Natural Log (LN) of Sales 166 167 8.10 168 f(x) = 0.0871275215x - 166.8424 267996 8.00 169 170 7.90 171 7.80 172 173 7.70 174 7.60 175 7.50 176 177 7.40 178 2002 2003 2004 2005 2006 2007 2008 2009 179 180 181 Slope = 8.7% (Using the SLOPE function) 182 183 To find the growth rate, raise e to the slope (this is eslo pe ) and then subtract 1. 184 185 g = 9.1% 186 187 188 189 Instead of doing a full regression with the Y variable being the log of sales, we could find the slope of the "log" 190 regression directly using the LOGEST function. In this function, we simply specify the original sales as the Y variable, 191 the years as the X variable, and the function finds the "log-based" slope coefficient, which is an estimate of (1+g). 192 193 (1+g ) rate using LOGEST = 1.091036 194 g = 9.1% 195 196 The historical g rowth rates rang e from 8.1% to 10.3 percent, depending on the method. 197 198 199 Management started with the regression prediction, then modified it based on qualitative data to $3,300, the 200 forecasted value given in the text. Management's sales forecast represents a growth rate of 10%. 201 202 203 AFN FORMULA (Sect ion 14 .3) T HE 204 205 can look at the additional funds needed using the AFN equation described in the text. This method We 206 identifies the additional funds needed as being the difference between the change in assets and 'the cumulative change in spontaneous liabilities and retained earnings. 207 208 209 Forecast g rowth rate in sales = 10% 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 AFN= Required Assets $200.00 $118.4 2 AFN= Required Assets Required Assets Spontaneous Liabilities Asset to Sales Ratio 0.667 $200.00 Spontaneous Liab. to Sales Ratio 0.067 $20.00 Profit Marg in 0.0378 $61.58 Retained Earnings Sales $300.00 = = = x x Spontaneous Liabilities = = = x x Sales $300.00 Retained Earnings = = = x x Sales $3,300.0 x x Retention Ratio 0.4 93 - 226 = 227 AFN= 228 229 230 can also rearrange the AFN formula to find the growth rate at which no external financing is required. At each place We in 231the equation, we substitute gS 0 for S and we substitute S0+gS0 for S1. We then solve for g. 232 Spontaneous Asset to 233 g for zero AFN= Profit marg in x Retention ratio Liab. to Sales Ratio Sales Ratio 234 x = 0.038 0.4 93 0.667 0.067 235 = 0.01866 0.5813333 236 g for zero AFN= 3.21% 237 238 Therefore, if MicroDrive's ratios remain constant, MicroDrive can g row at about 3.21% without needing external financing . 239 240 FINANCIAL ST AT EMENT FORECAST ING: T HE PERCENT OF SALES MET HOD (Sect ion 14 .4 ) 241 242 243 The 244 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) 245 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 246 the bank); (3) Accounts receivable (the proportion will depend on the firm's credit policy, which is discussed in Chapter 27); (4) clear Inventories; (5) Net plant and equipment (this is reasonable for the long-term; in the short-term, firm's often have excess capacity, which 247 we discuss later in this model); (6) Accounts payable; and (7) Accruals. It also assumes that Depreciation is proportional to Net plant and 248 equipment. Other items on the financial statements are a direct result of the firm's financial policies (i.e., dividend policy and capital 249 structure policy), which we discuss below. 250 251 252 next step is to analyze the historical "Pro Forma" ratios. The actual historical statements are shown above in Rows 7-53. The The 253 needed for the Pro Forma analysis are shown below. ratios 254 255 Preliminary 256 Pro Forma Ratios Actual Historical Industry Forecast 257 2006 2007 Averag e Composite 2008 258 259 Costs / Sales 87.6% 87.2% 87.4 % 87.1% 87.200% 260 Depreciation / Net plant & equip. 10.3% 10.0% 10.2% 10.2% 10.000% 261 Cash / Sales 0.5% 0.3% 0.4 % 1.0% 0.333% 262 Accounts Rec. / Sales 11.1% 12.5% 11.8% 10.0% 12.500% 263 Inventory / Sales 14 .6% 20.5% 17.5% 11.1% 20.500% 264 Net plant & equip. / sales 30.5% 33.3% 31.9% 33.3% 33.333% 265 Accounts Pay. / Sales 1.1% 2.0% 1.5% 1.0% 2.000% 266 Accruals / Sales 4 .6% 4 .7% 4 .6% 2.0% 4 .667% 267 Long -term bonds/operating assets 35.9% 37.7% 36.8% 30.9% 37.700% 268 269 Other Inputs 270 271 Sales Growth Rate 10% 272 Tax rate 4 0% 273 Dividend g rowth rate 8% 274 Interest rate on notes payable and short-term investments 9% 275 Interest rate on long -term bonds 11% 276 Coupon rate on preferred stock 10% 277 278 279 Table 14 -2 MicroDrive, Inc.: Actual and Projected Income Statements (Millions of Dollars) 280 Actual Forecast 281 2007 Forecast basis 2008 282 (1) (2) (3) 283 Sales $3,000.0 110% x 2007 Sales = $3,300.0 284 Costs except depreciation 2,616.2 87.2% x 2008 Sales = $2,877.6 Note: we have used the ROUND function 285 Depreciation 100.0 10% x 2008 Net plant = $110.0 to make the calculations consistent with 286 Total operating costs $2,716.2 $2,987.6 the textbook. 287 EBIT $283.8 $312.4 288 Less Interest 88.0 Interest rate x 2007 debt = $92.8 289 Earning s before taxes (EBT) $195.8 $219.6 290 Taxes (4 0%) 78.3 $87.8 291 NI before preferred dividends $117.5 $131.8 292 Preferred dividends 4 .0 Dividend rate x 2007 preferred = $4 .0 293 NI available to common $113.5 $127.8 294 295 Shares of common equity 50.0 $50.0 296 Dividends per share $1.15 108% x 2007 DPS = $1.25 297 Dividends to common $57.5 2008 DPS x # shares = $62.5 298 Additions to retained earning s $56.0 $65.3 299 300 301 Table 14 -3 MicroDrive, Inc.: Actual and Projected Balance Sheets (Millions of Dollars) 302 Actual Forecast 303 2007 Forecast basis 2008 304 (1) (2) (3) 305 Assets 306 Cash $10.0 0.33% x 2008 Sales = $11.0 307 ST investments 0.0 Previous plus "plug " if needed 0.0 308 Accounts receivable 375.0 12.50% x 2008 Sales = 4 12.5 309 Inventories 615.0 20.50% x 2008 Sales = 676.5 310 Total current assets $1,000.0 $1,100.0 311 Net plant and equipment 1,000.0 33.33% x 2008 Sales = 1,100.0 312 Total assets $2,000.0 $2,200.0 313 314 Liabilities and equity 315 Accounts payable $60.0 2.00% x 2008 Sales = $66.0 316 Accruals 14 0.0 4 .67% x 2008 Sales = 154 .0 317 Notes payable 110.0 Previous plus "plug " if needed 224 .7 318 Total current liabilities $310.0 $4 4 4 .7 319 Long -term bonds 754 .0 Same: no new issue 754 .0 320 Total liabilities $1,064 .0 $1,198.7 321 Preferred stock 4 0.0 Same: no new issue 4 0.0 322 Common stock 130.0 Same: no new issue 130.0 323 Retained earning s 766.0 2007 RE + 2008 Add. to RE = 831.3 324 Total common equity $896.0 $961.3 325 Total liabilities and equity $2,000.0 $2,200.0 326 327 Required assetsa $2,200.0 b 328 Specified sources of financing $2,085.3 329 Additional funds needed (AFN) $114 .7 330 331 Required additional notes payable $114 .7 332 Additional short-term investments 0.0 333 334 Required assets include all forecasted operating assets plus the short-term investments from the previous year. 335 Specified sources of financing include forecasted operating current liabilities, forecasted long-term bonds, forecasted 336 preferred stock, forecasted common equity, and the amount of notes payable from the previous year. 337 338 339 MicroDrive Statement of Cash Flows for Years Ending Dec. 31 Actual Forecast 340 (in millions of dollars) 2007 2008 341 (1) (3) 342 Operating Activities 343 Net Income before preferred dividends $117.5 $131.8 344 Noncash adjustments 345 Depreciation and amortization $100.0 $110.0 346 Due to chang es in working capital 347 Increase in accounts receivable $(60.0) $(37.5) 348 Increase in inventories $(200.0) $(61.5) 349 Increase in accounts payable $30.0 $6.0 350 Increase in accruals $10.0 $14 .0 351 Net cash provided by operating activities $(2.5) $162.8 352 353 Long -term investing activities 354 Cash used to acquire fixed assets $(230.0) $(210.0) 355 356 Financing Activities 357 Sale of short-term investments $65.0 $358 Increase in notes payable $50.0 $114 .7 359 Increase in bonds $174 .0 $360 Payment of common and preferred dividends $(61.5) $(66.5) 361 Net cash provided by financing activities $227.5 $4 8.2 362 363 Net cash flow $(5.0) $1.0 364 Cash and securities at beg inning of the year $15.0 $10.0 365 Cash and securities at end of the year $10.0 $11.0 366 367 368 369 ANALYSIS OF THE PLAN: FREE CASH FLOW, RATIOS, AND AFN 370 371 following table shows key outputs of the preliminary plan. We used the Scenario Manager to develop the The key 372 outputs for the revised plan. 373 374 Table 14 -4 Model Inputs, AFN, and Key Ratios (Millions of Dollars) 375 Preliminary Revised Industry 376 Actual Actual Forecast Forecast Averag e 377 2006 2007 2008 2008 2007 378 (1) (2) (3) (4 ) 379 Model Inputs 380 (excluding depreciation) as percent of sales Costs 87.2% 87.2% 86.0% 87.1% 381 Accounts receivable as percent of sales 12.5% 12.5% 11.8% 10.0% 382 Inventory as percent of sales 20.5% 20.5% 16.7% 11.1% 383 384 Model Outputs 385 operating profit after taxes (NOPAT) Net $170.3 $187.4 $211.2 386 operating working capital (NOWC) Net $585 $800.0 $880.0 $731.5 387 operating capital Total $1,4 55 $1,800.0 $1,980.0 $1,831.5 388 cash flow (FCF) Free $(174 .7) $7.5 $179.7 389 Additional funds needed (AFN) $114 .7 $(57.5) 390 391 Analysis Ratio 392 Current ratio 3.2 2.5 3.1 4 .2 393 Inventory turnover 4 .9 4 .9 6.0 9.0 394 sales outstanding Days 4 5.6 4 5.6 4 3.1 36.0 395 assets turnover Total 1.5 1.5 1.6 1.8 396 ratio Debt 53.2% 54 .5% 51.4 % 4 0.0% 397 Profit marg in 3.8% 3.9% 4 .6% 5.0% 398 Return on assets 5.7% 5.8% 7.2% 9.0% 399 Return on equity 12.7% 13.3% 15.4 % 15.0% 400 Return on invested capital 9.5% 9.5% 11.5% 11.4 % 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 EXCESS CAPACITY ADJUSTMENTS 444 445 We 446 have just stated that assuming current assets to grow at the same rate of sales is not necessarily correct. The same can be 447said of fixed assets. For instance, let us assume that the firm in our example is not operating at full capacity. This means that 448 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 449 capacity. We can use this information to calculate the firm's full capacity sales and the target fixed assets-to-sales ratio. 450 451 452 2007 Sales $3,000 453 2008 Sales $3,300 454 Percentag e of capacity 96% 455 2007 Fixed Assets $1,000 456 457 Full capacity sales = $3,125 458 459 Targ et FA/Sales = 0.32 460 461 Required level of FA = $1,056 Spontaneous Liabilities $20.00 - Retained Earnings $61.58 Due to its use of the automatic iteration feature, the Web 14A Tool Kit is shown in a separate file, FM12 Ch 14 Web 14A Tool Kit.xls. Tool Kit for Web Extension 14A: Advanced Techniques for Forecasting Financial Statement Accounts REGRESSION APPROACH Relationships between sales and other financial statement accounts are not always proportional. Therefore, in some cases it is preferrable to use a regression approach. Financial Statement Data for MicroDrive ($ Millions) Year 2003 2004 2005 2006 2007 Sales Inventories $2,058 $387 2,534 398 2,472 409 2,850 415 3,000 615 Accounts Receivable $268 298 304 315 375 Inventories vs. Sales $ 700 Invent ories ($ Millions) $ 600 $ 500 $ 400 $ 300 $ 200 $ 100 $0 $ 2,000 $ 2,200 $ 2,400 $ 2,600 $ 2,800 $ 3,000 $ 3,200 f(x) = 0.1860395634x - 35.7029844369 R = 0.5054678216 Sales ($ Millions) Accounts Receivable vs. Sales $ 400 $ 350 Invent ories ($ Millions) $ 300 $ 250 $ 200 $ 150 $ 100 $ 50 $0 $ 2,000 $ 2,200 $ 2,400 $ 2,600 $ 2,800 $ 3,000 $ 3,200 f(x) = 0.0966267108x + 62.4325312292 R = 0.8098551635 Sales ($ Millions) SECTION 14.3 SOLUTIONS TO SELF-TEST 3 Suppose MicroDrive's growth rate in sales is forecast as 15 percent. If all ratios stay the same, what is the AFN? Sales growth rate S0 A*/ S0 L*/ S0 Profit margin (M) Retention ratio Sales S1 AFN 15% $3,000 66.666% 6.667% 3.783% 49.330% $450.00 $3,450.00 $205.62 ...
View Full Document

This note was uploaded on 02/27/2012 for the course BUS 510 taught by Professor Mehdi during the Spring '11 term at University of La Verne.

Ask a homework question - tutors are online