# Model for Financial Forecasting by ocak

VIEWS: 845 PAGES: 6

• pg 1
```									A 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 17model

B

C

D
8/12/2008 19:16

E

F

G

H 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 1999 2000 2001 2002 \$2,058 2,534 2,472 2,850 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 66 67 KEY INPUT DATA 68 69 Sales Growth Rate 10% 70 Tax rate 40% 71 Dividend growth rate 8% 72 73 74 INCOME STATEMENT 75 (in millions of dollars) Forecast basis 76 2002 % of sales 2003 77 Sales \$3,000.0 100.00% \$3,300.00 78 Costs except depreciation 2,616.2 87.21% \$2,877.82 79 Depreciation 100.0 3.33% \$110.00 80 Total operating costs 2,716.2 90.54% \$2,987.82 81 EBIT 283.8 9.46% \$312.18 a 82 Less Interest 88.0 \$88.00 83 Earnings before taxes (EBT) 195.8 \$224.18 84 Taxes (40%) 78.3 \$89.67 85 NI before preferred dividends 117.5 \$134.51 a 86 Preferred dividends 4.0 \$4.00 87 NI available to common \$113.5 \$130.51 88 b 89 Dividends to common \$57.5 \$62.10 90 Addition to retained earnings \$56.0 \$68.4 91 a 92 Indicates a 2002 amount carried over for the forecast. b 93 Indicates a projected amount. See text for explanation. 94 95 Now we need to generate the 2003 balance sheet. For this forecast, we assume that assets and spontaneous 96 liabilities will continue to grow at the same rate as sales. However, the addition to retained earnings will be 97 dependent upon the income statement. Moreover, if additional capital is needed, we assume that the 98 additional funds will be raised in the following manner: 99 100 ALLOCATION OF ADDITIONAL FUNDS NEEDED 101 102 Funds raised as notes payable 25% 103 Funds raised as long-term debt 25% 104 Funds raised as preferred stock 0% 105 Funds raised as new common stock 50% 106 107 After our first pass in this construction, the balance sheet, unlike its name, will not balance. 108 109 110 BALANCE SHEET (AFTER THE FIRST PASS) 111 (in millions of dollars) Forecast basis 2003 112 2002 % of sales 1ST PASS 113 Assets 114 Cash \$ 10.0 0.33% \$ 11.0 115 Accounts receivable \$ 375.0 12.50% \$ 412.5 116 Inventories \$ 615.0 20.50% \$ 676.5 117 Total current assets \$ 1,000.0 33.33% \$ 1,100.0 118 Net plant and equipment \$ 1,000.0 33.33% \$ 1,100.0 119 Total assets \$ 2,000.0 66.67% \$ 2,200.0 120 121 Liabilities and equity 122 Accounts payable \$ 60.0 2.00% \$ 66.0 Notice, total assets 123 Accruals \$ 140.0 4.67% \$ 154.0 exceeds liabilities and 124 Notes payable \$ 110.0 \$ 110.0 equity. This signifies 125 Total current liabilities \$ 310.0 10.33% \$ 330.0 that the firm needs 126 Long-term bonds \$ 754.0 \$ 754.0 additional funds. 127 Total debt \$ 1,064.0 35.47% \$ 1,084.0 128 Preferred stock \$ 40.0 \$ 40.0 129 Common stock \$ 130.0 \$ 130.0 130 Retained earnings \$ 766.0 +\$68.4 \$ 834.4 131 Total common equity \$ 896.0 29.87% \$ 964.4 132 Total liabilities and equity \$ 2,000 66.67% \$ 2,088.4 133 134 Additional funds needed (AFN) \$ 111.6

A 135 136 137 138 139 140 141 142 143 144 145 146 147 148 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

B

C

D

E

F

G

H

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. USING THE AFN EQUATION Change in assets Less change in sp. liab. Less additional retained earnings TOTAL AFN \$ \$ \$ \$ 200.0 20.0 68.4 111.6

Using the terms described above, we can now allocate the additional funds needed, i.e., we can determine the amount of new notes payable, long-term debt, and common stock that must be raised. HOW THE AFN WILL BE RAISED: % of AFN 25.00% 25.00% 0.00% 50.00% 100.00% Capital raised \$ 27.9 \$ 27.9 \$ \$ 55.8 \$ 111.6

Notes payable Long-term debt Preferred stock Common stock Total THE SECOND PASS

The next step is to create a new column in the balance sheet to be called "AFN". In this column, we input the capital to be raised, which we obtained above. Then, we create another column entitled "2nd Pass". This column is our revised balance sheet as adjusted for the additional external capital raised. BALANCE SHEET (AFTER THE SECOND PASS) (in millions of dollars) Forecast basis 2002 % of sales 1ST PASS Assets Cash \$10.0 0.33% \$11.0 Accounts receivable 375.0 12.50% 412.5 Inventories 615.0 20.50% 676.5 Total current assets 1,000.0 33.33% 1,100.0 Net plant and equipment 1,000.0 33.33% 1,100.0 Total assets \$2,000.0 66.67% \$2,200.0 Liabilities and equity Accounts payable

2003 AFN a

2ND PASS \$11.0 412.5 676.5 1,100.0 1,100.0 \$2,200.0

\$

60.0

2.00%

\$66.0

\$66.0

b 179 Notes payable \$ 110.0 110.0 \$27.9 137.9 180 Accruals \$ 140.0 4.67% 154.0 154.0 181 Total current liabilities \$ 310.0 10.33% 330.0 357.9 b 182 Long-term bonds \$ 754.0 754.0 \$27.9 781.9 183 Total debt \$ 1,064.0 35.47% 1,084.0 1,139.8 b 184 Preferred stock \$ 40.0 40.0 \$0.0 40.0 b 185 Common stock \$ 130.0 130.0 \$55.8 185.8 +68.4 c 186 Retained earnings \$ 766.0 834.4 834.4 187 Total common equity \$ 896.0 29.87% 964.4 1,020.2 188 Total liabilities and equity \$ 2,000.0 66.67% \$2,088.4 \$2,200.0 189 190 Additional funds needed (AFN) \$111.6 191 a 192 AFN stands for "Additional Funds Needed." This figure is determined at the bottom of the first pass. The next 193 column shows how the required \$111.6 of AFN will be raised. b 194 Indicates a 2002 amount carried over as the first-pass forecast. Arrows also indicate items whose values are 195 carried over from one pass to another. c 196 From Cell E90 of the Income Statement, this figure represents the Addition to Retained Earnings. 197 198 Note: The balance sheet won't always balance exactly after 2 passes, but it will always be very close. 199 200 Now we can use our forecasted data to estimate future operating performance and future free cash flow. This 201 is particularly important because the intrinsic value of the firm is the present value of all expected future free 202 cash flows. Technically, this process would require infinite iterations, but two passes does a really good job. 203

A 204 205 206 207 208 209 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

B

C

D

E

F

G

H

MEASURING OPERATING PERFORMANCE: FREE CASH FLOW AND RATIOS Forecast Free Cash Flow 2002 2003 NOPAT NOWC Total Operating Capital Free Cash Flow \$170.3 \$800.0 \$1,800.0 (\$109.7) \$187.3 \$880.0 \$1,980.0 \$7.3

The calculations show a marked improvement in Free Cash Flow, which indicates that the firm's operating performance should improve in 2003 over 2002. Ratio Analysis Current Ratio Inventory Turnover Days sales outstanding Total assets turnover Debt ratio Profit Margin Return on Assets Return on Equity Return on invested capital 2002 3.2 4.9 45.6 1.5 53.2% 3.8% 5.7% 12.7% 9.5% Forecast 2003 3.1 4.9 45.6 1.5 51.8% 4.0% 5.9% 12.8% 9.5% Industry Average 4.2 9 36 1.8 40.0% 5.0% 9.0% 15.0% 11.4% Appraisal Vs. Industry Conclusion Lower Poor Way lower Poor Higher Poor Lower Poor Higher Poor Lower Poor Way lower Poor Lower Poor Lower Poor

Trend Stable Stable Stable Stable Down Up Up a little Stable Stable

The ratio analysis shows that the firm is not operating as well as other firms in its industry. Its inventory, receivables (DSO), and total assets turnover are all stable, as they must be because we assumed constant ratios. The debt ratio improved a little because we assumed that 50% of the AFN would be raised as equity. However, the return ratios are all low, indicating that the firm is not operating very well. With this appraisal, the CFO would undoubtedly suggest that the operating executives should revise their operating plans and do things such as reduce inventories, collect receivables faster, economize on the use of fixed assets, and cut operating costs. Then, a new forecast, with new and higher targets, would be run, and executive bonuses would be based on meeting those higher targets.

FORECASTING CURRENT ASSETS USING SIMPLE LINEAR REGRESSION If we assume that the relationship between certain assets and sales is linear, we can use simple linear regression techniques to estimate the requirements for that asset for any increase in sales. Using Excel's Regression function (as done previously in this model to predict future sales), we can predict future asset requirements under this linear assumption. Below, we have compiled historical information about sales, inventories, and accounts receivable. Year 1998 1999 2000 2001 2002 2003 Sales 2,058 2,534 2,472 2,850 3,000 3,300 Inv 387 398 409 415 615 ? A/R 268 297 304 315 375 ?

\$ \$ \$ \$ \$ \$

Using this information, we run the regression for inventories and accounts receivable, and have included their summary outputs below. Remember, that we are asserting that required inventories are a function of projected sales. For that reason, sales are the x-variables and inventories are the y-variables in this regression. Forecasting Inventories Inventories 2003 = = = Intercept -35.702984 \$578.23 x coefficient 0.18603956 x 2003 Sales \$ 3,300

266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 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

A SUMMARY OUTPUT

B

C

D

E

F

G

H

Regression Statistics Multiple R 0.7109626 R Square 0.5054678 Adjusted R Square 0.3406238 Standard Error 77.747243 Observations 5 ANOVA df Regression Residual Total SS 1 18534.8985 3 18133.9015 4 36668.8 MS F Significance F 18534.89846 3.06633932 0.1782282 6044.633848

Intercept X Variable 1

Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% -35.702984 276.595259 -0.129080247 0.90546202 -915.95337 844.547402 -915.95337 0.1860396 0.10624175 1.751096606 0.17822815 -0.1520694 0.52414854 -0.1520694

This value for inventories is much less than the original value calculated using the percentage of sales method. Looking at the "Multiple R" value in the summary output, we see that the correlation between sales and inventories in this linear framework is 0.711. This implies that there is a moderately strong relationship between sales and inventories. While this figure is not a direct indicator of asset requirements, it does give financial managers a reasonable basis for forecasting the target inventory levels. Now, we will repeat this procedure to forecast accounts receivables Forecasting Accounts Receivable Receivables 2003 = = = Intercept 61.9971721 \$381.17 x coefficient 0.09671784 x 2003 Sales \$ 3,300

SUMMARY OUTPUT Regression Statistics Multiple R 0.8986746 R Square 0.8076161 Adjusted R Square 0.7434881 Standard Error 19.944247 Observations 5 ANOVA df Regression Residual Total SS 1 5009.48099 3 1193.31901 4 6202.8 MS F Significance F 5009.480993 12.5938185 0.038124 397.7730022

Intercept X Variable 1

Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% 61.997172 70.9540818 0.873764702 0.44655678 -163.8106 287.804939 -163.8106 0.0967178 0.02725385 3.548777043 0.03812395 0.0099838 0.18345183 0.00998384

Again, we observe that our regression estimate is less than the estimate as predicted by the percentage of sales method. However, we also observe that there is a stronger correlation between sales and receivables, than with inventories. We see this through the "Multiple R" of 0.899.

A 322 323 324 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 359 360 361 362 363 364 365 366 367 368

B

C

D

E

F

G

H

Below, the linear predictions of inventories and receivables have been inputted into the table seen above. Year 1997 1998 1999 2000 2001 2002 Sales 2,058 2,534 2,472 2,850 3,000 3,300 Inv 387 398 409 415 615 578 A/R 268 297 304 315 375 381

\$ \$ \$ \$ \$ \$

Now, we will compare the alternate prediction values and find the difference between them. Inventories Receivables 677 413 \$ 578 \$ 381 \$ 98 \$ 31

Percentage of sales method Less: Regression forecast Difference

Naturally, the question of why these methods deviate arises. It is important to remember that the percentage of sales method assumes that these assets grow at the same rate as sales, hence they are perfectly correlated. In reality, we must recognize that such correlation is unlikely. The more likely scenario is that the firm will begin to experience economies of scale. In other words, they will be able to build upon their distinctive competencies and gain greater efficiency from its productive assets. As a result, we would expect to see them have less capital intensity. The linear regression method is able to detect and project trends in the variable relationship. The percentage of sales method, however, fails to account for such trends, and fails to recognize optimality of assets.

EXCESS CAPACITY ADJUSTMENTS We have just stated that assuming current assets 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 2002 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. 2002 Sales 2003 Pred. Sales Percentage of capacity 2002 Fixed Assets Full capacity sales = Target FA/Sales = Required FA = \$3,000 \$3,300 96% \$1,000 \$3,125 0.32 \$1,056

```
To top