FM12 Ch 12 Mini Case - Chapter 12 Mini Case PDF

Preview

Summary

Chapter 12 Mini Case, Chapter 12 Mini Case...

Description

Chapter 12 Mini Case Figure MC-1. Financial Statements and Other Data (Millions except per share data) Balance Sheet, Hatfield, 12/31/10 Cash and securities $20 Accounts receivable 290 Inventories 390 Total current assets $700 Net fixed assets 500 Total assets $1,200 Accounts pay. + accruals Notes payable Total current liabilities Long-term debt Total liabilities Common stock Retained earnings Total common equity Total liab. & equity

$100 80 $180 520 $700 300 200 $500 $1,200

Selected Ratios and Other Data, 2010 Sales, 2010 (S0): Expected growth in sales: Profit margin (M): Assets/Sales (A 0*/S0): Payout ratio (POR): Equity multiplier (Assets/Equity): Total liability/Total assets Times interest earned (EBIT/Interest): Increase in sales (ΔS = gS0): (Payables + Accruals)/Sales (L0*/S 0): Operating costs/Sales: Cash/Sales: Receivables/Sales: Inventories/Sales: Fixed assets/Sales: Tax rate: Interest rate on all debt: Price/Earning (P/E): ROE (Net income/Common equity): DuPont ROE Hatfield Industry

AFNHatfield

Income Statement, Hatfield, 2010 Sales Total operating costs EBIT Interest EBT Taxes (40%) Net income Dividends Add. to retain. earnings Shares outstanding EPS DPS Year-end stock price

PM x Sales/Assets 1.20% 1.67 2.74% 2.00

Hatfield $2,000 15.0% 1.2% 60.0% 37.5% 2.40 58.3% 1.67 $300 5.0% 95.0% 1.0% 14.5% 19.5% 25.0% 40.0% 10.00% 10.0 4.80% x

=

Add'l Req'd Assets

-

=

(A0*/S0)∆S

-

Industry $2,000 15.0% 2.74% 50.0% 35.0% 2.13 53.0% 5.20 $300 4.0% 93.0% 1.0% 11.0% 15.0% 23.0% 40.0% 9.5% 12.0 11.64%

Sales set equal to Hatfield to make the data comparable.

Assets/Equity 2.40 2.13

Spontaneou s liabilities (L0*/S0)∆S

ROE 4.80% 11.67%

-

AFNHatfield

=

(A0*/S0)(gS0)

-

(L0*/S0)(gS0)

-

=

$180

-

$15.00

-

=

$147.75 million

Self-Supporting Growth Rate. This is the maximum growth rate that can be attained withou external funds, i.e., the value of g that forces AFN = 0, holding other things constant. We fou = 1.439%, with Excel's Goal Seek function and also algebraically, as explained below.

1. Using algebra. The self-supporting growth rate can also be found by solving the equatio the 3rd row above AFN, then finding the value of g that causes AFN to equal zero. This resu same value as we find with Goal Seek. The algebriac solution is easy if we give you the equ you had to solve the AFN equation for g, you would probably find the Goal Seek solution ea PM(1 – POR)(S0) Self-Supporting g

=

$15.00 =

A0* – L0* – PM(1 – POR)S 0

= $1,085.00

Therefore, if the firm's ratios remain constant, the company can grow at about 1.44% withou financing.

2. Using Goal Seek. To find the self-supporting growth rate with Goal Seek, first highlight c with Excel 07, on the Main Menu bar click Data>What-If-Analysis>Goal Seek. With Excel 03 c Tools>Goal Seek. Then complete the dialog box as shown to the right. When you click OK change to 1.439%, which will cause Cell B56 to change to $0.00. Record the new growth rat return to the base case by clicking Cancel. Or, you could click OK to leave the new growth D25 and then over-type it with 15% in that cell to get back to the base case.

Goal Seek is one of Excel's most useful features. We use it elsewhere in this chapter to find amount of new capital. In capital budgeting, we use it to see how high the WACC can go bef becomes negative, how low the WACC must be for the NPV to be positive, how low the initia to achieve a positive NPV, how long a project must last to achieve a positive NPV, and so fo worked on real world cases dealing with almost every chapter in the text, and we almost alw occasion to use Goal Seek. We can't overemphasize its usefulness.

Forecasted Financial Statements

Forecast the financial statements using the following assumptions. (1) Operating ratios rem unchanged. (2) No additional notes payable, LT bonds, or common stock will be issued. (3) rate on all debt is 10%. (4) If additional financing is needed, then it will be raised through a l The line of credit will be tapped on the last day of the year, so there will be no additional int due to the line of credit. (On Tab 2 we relax this assumption and assume that the line of cre smoothly throughout the year.) (5) Interest expenses for notes payable and LT bonds are b average balances during the year. (6) If surplus funds are available, the surplus will be paid special dividend payment. (7) Regular dividends will grow by 15%. (8) Sales will grow by 15 called the "Steady" scenario because operations remain unchanged The same assumption

called the Steady scenario because operations remain unchanged. The same assumption Target scenario, except there are improvements in several areas of operations.

Use the Scenaro Manager to change scenarios. Inputs for Forecasts Sales growth rate Operating costs/Sales Cash/Sales Receivables/Sales Inventories/Sales Fixed assets/Sales Payables and accruals/ Sales Growth rate in regular dividends Interest rate on all debt Tax rate Hatfield 2010

Hatfield 2010 15.0% 95.0% 1.0% 14.5% 19.5% 25.0% 5.0% 15.0% 10.0% 40.0% Forecast Factor

Forecast Scenarios Steady Target 15.0% 15.0% 95.0% 93.0% 1.0% 1.0% 14.5% 11.0% 19.5% 15.0% 25.0% 23.0% 5.0% 4.0% 15.0% 15.0% 10.0% 10.0% 40.0% 40.0%

Scenario: Steady Balance Sheet Assets Cash Accounts receivable Inventories Total current assets Net fixed assets Total assets

$20 290 390 $700 500 $1,200

1.00% 14.50% 19.50%

Factor × Forecasted Sales Factor × Forecasted Sales Factor × Forecasted Sales

25.00%

Factor × Forecasted Sales

Liabilities & equity Accts pay. and accruals Notes payable: Planned Line of credit (LOC) Total current liabs LT debt: Planned Total liabilities Common stock Retained earnings Total common equity Total liab. & equity

$100 80 0 $180 520 $700 300 200 $500 $1,200

5.00%

Factor × Forecasted Sales Carry over 2010 amount New LOC if AFN > 0

Basis for 2011 Forecast

Carry over 2010 amount Carry over 2010 amount 2010 + Add'n to RE from Income St.

AFN = TA – (Planned Liab & Equity) New line of credit (if AFN > 0) = Special dividend (if AFN ≤ 0) =

Scenario: Steady Income Statement Sales Total operating costs EBIT Interest: NP planned Interest: LT debt planned

Hatfield 2010 ### 1,900.0 $100.0 8.0 52.0

Forecast Factor 15.00% 95.0% 10.0% 10.0%

Basis for 2011 Forecast (1 + Factor) × 2010 Sales Factor × Forecasted Sales Rate x Avg Balance Rate x Avg Balance

Interest: Line of credit Earnings before taxes (EBT) Taxes Net inc. for common (NI) Dividends- regular (DIVs) Special dividends Add. to ret. earnings

Performance Net operating profits after taxes Net operating working capital Total operating capital Free cash flow Return on invested capital AFN EPS DPS (regular dividends) Payout ratio (all dividends) Profit margin Sales/Assets (Assets turnover) Assets/Equity ROE Operating costs/Sales Total liability/Total assets TIE ratio

0.0 $40.0 16.0 $24.0 $9.0

10.0%

Rate x Beginning Balance

40%

Tax rate × EBT

15%

$15.0

Hatfield 2010 $60 $600 $1,100 NA 5.5% NA $2.40 $0.90 37.5% 1.2% 1.67 2.40 4.8% 95.0% 58.3% 1.67

(1 + g) × 2010 Dividends Special dividend if AFN ≤ 0 NI − all dividends

Forecast Scenarios Steady Target $69 $97 $690 $529 $1,265 $1,058 -$96 $139 5.5% 9.1% $142.4 -$92.3 $3.30 $6.06 $1.04 $1.04 31.4% 169.3% 1.4% 2.6% 1.67 2.00 2.64 2.51 6.3% 13.2% 95.0% 93.0% 62.1% 60.2% 1.92 2.68

See Tab 2 for a forecasting model that incorporates feedback effects.

ADJUSTED FOR INTEREST ON ADDED NOTES PAYABLE Adjusted for New Interest Data Used in the Scenarios Steady State Target Active Inputs for Forecasts Hatfield 2010 Steady Target Target Growth rate 15.0% 15.0% 15.0% 15.0% Operating costs/sales 95.0% 95.0% 93.0% 93.0% Cash/Sales 1.0% 1.0% 1.0% 1.0% Receivables/Sales 14.5% 14.5% 11.0% 11.0% Inventories/Sales 19.5% 19.5% 15.0% 15.0% Fixed assets/Sales 25.0% 25.0% 23.0% 23.0% Payables and Accruals/ Sales 5.0% 5.0% 4.0% 4.0% Interest rate on notes payable 10.0% 10.0% 9.5% 9.5% Payout ratio 37.5% 37.5% 35.0% 35.0% Tax rate 40% 40% 40% 40% P/E ratio 10.0 10.0 12.0 12.0 Shares outstanding (millions) 10.000 10.000 10.000 10.000

Adjusted for New Interest Balance Sheet Assets Cash Accounts receivable Inventories Total current assets Net fixed assets Total assets

Hatfield 2010

Forecast Factor

is data is for: Target Procedure for 2011 Forecast

$20 290 390 $700 500 $1,200

1.00% 11.00% 15.00%

Factor × Forecasted Sales Factor × Forecasted Sales Factor × Forecasted Sales

23.00%

Factor × Forecasted Sales

4.00%

Factor × Forecasted Sales Carry over 2010 amount New notes (+/-) to balance

Claims on Assets Accts payable and accruals $100 Notes payable 80 Add' notes to balance 0 Total current liabs $180 Long Term Debt 520 Total liabilities $700 Common stock 300 Retained earnings 200 Total common equity $500 Total liabs and equity $1,200 Shares outstanding 10.000 Year-end stock price $24.00

Adjusted for New Interest 2010 Income Statement Sales Total operating costs EBIT Interest on initial debt Interest on 1/2 of new debt Total interest Earnings before taxes (EBT) Taxes Net income for common (NI) Dividends (DIVs) Add. to ret. earns (NI – DIVs) Shares outstanding EPS DPS Stock Price

2010 Actual ### 1,900.0 $100.0 60.0 0.0 $60.0 $40.0 16.0 $24.0 $9.0 $15.0 10.000 $2.40 $0.90 $24.00

Carry over 2010 amount Carry over 2010 amount 2010 + Add'n to RE from Income Statement

Difference between Assets and Liab+Equity:

Adjusted for New Interest Scenario: Target % of Sales Factors 15.00% (1 + Factor) × 2010 Sales 93.0% Factor × Forecasted Sales 9.5% 9.5%

40%

Carry over 2010 amount Interest rate × (0.5 × Δ notes)

Tax rate × 2011 EBT

35%

12.0

Adjusted for New Interest Performance

2010 Actual

EPS Year-end stock price Profit margin (PM) Sales/Assets (Assets turnover) ROE

$2.40 $24.00 1.2% 1.67 4.8%

Adjusted for New Interest Final Steady State Target Steady Target $2.86 $6.30 $28.62 $75.56 1.24% 2.74% 1.67 2.00 5.52% 11.64%

Debt/Assets Assets/Equity TIE ratio Payout ratio

58.3% 2.40 1.67 37.5%

62.4% 2.66 1.71 35.0%

53.0% 2.13 2.87 35.0%

Final comment: Different problems require somewhat different models--one size does not f example, a firm's growth rate might be low, and if that resulted in a negative AFN, then a mo have to be programmed to do something with the excess funds. The model on Tab 2 is an e

4/11/10

$2,000 1,900 $100 60 $40 16 $24 $9 $15 10 $2.40 $0.90 $24.00

Add'n to RE S1 × M × (1–POR)

S1 × M × (1–POR) $17.25

t raising und this rate, g

n as shown on ults in the uation, but if asier.

1.382%

ut external

cell B56. Then, click K, Cell D25 will te and then rate in Cell

d the required fore the NPV al cost must be orth. We have ways have

main The interest line of credit. erest charges dit is accessed ased on the out as a %. This is ns apply to the

Active is Steady 15.0% 95.0% 1.0% 14.5% 19.5% 25.0% 5.0% 15.0% 10.0% 40.0% w/o AFN 2011

With AFN 2011

$23.0 333.5 448.5 $805.0 575.0 $1,380.0

$23.0 333.5 448.5 $805.0 575.0 $1,380.0

$115.0 80.0

$115.0 80.0 142.4 $337.4 520.0 $857.4 300.0 222.7 $522.7 $1,380.0

0 $195.0 520.0 $715.0 300.0 222.7 $522.7 $1,237.7 $142.4 $142.4 $0.0

w/o AFN 2011 $2,300.0 $2,185.0 $115.0 8.0 52.0

$0.00

With AFN 2011 $2,300.0 $2,185.0 $115.0 $8.0 $52.0

0.0 $55.0 $22.0 $33.0 $10.4 $22.7

Active is Steady $69 $690 $1,265 -$96 5.5% $142.4 $3.30 $1.04 31.4% 1.4% 1.67 2.64 6.3% 95.0% 62.1% 1.92

This data is identical to that used for the unadjusted forecasts. It is repeated here for convenience.

$0.0 $55.0 $22.0 $33.0 $10.4 $0.0 $22.7

2011 Forecast $23 253 345 $621 529 $1,150

$92 80 -83 $89 520 $609 300 241 $541 $1,150 $0

Forecast 2011 $2,300.00 2,139.00 $161.00 60.00 -3.94 $56.06 $104.94

After executing a scenario, G177=0, so Assets>Claims and the difference is shown in G185. Type this difference into G177 to balance, forcing assets = claims. Do this before looking at performance measures, as those measures will be wrong until the balance sheet is in balance.

After executing a scenario, G177=0, so Assets>Claims, and the difference is shown in G185. Type this difference into G177, which will reduce the difference in G185. Then add the small difference to the number in G177 to further reduce the difference, and continue (if necessary) until G177 shows a zero. Alternatively, put the pointer on G185 and do a Goal Seek to force G185 to zero by changing G177. Do this before looking at performance measures, as those measures will be wrong until the balance sheet is in balance.

Retained earnings will be too high if Add'l notes = 0 because it won't reflect interest on the new notes payable.

This is the additional interest after Assets = Claims.

Interest is too low until Assets = Claims.

$62.97 $22.04 The addition to retained earnings is not correct until Assets=Claims because until then the appropriate amount of interest has not been deducted, so earnings and thus retained earnings will not be correct.

$75.56

AAfter running a scenario and adjusting to force Assets = TClaims, we copy the results from Column G into column E or F.

$75.56 2.74% 2.00 11.64%

53.0% 2.13 2.87 35.0%

fit all. For odel would example.

Steady-no feedback

Steady 0.15 0.95 0.01 0.145 0.195 0.25 0.05 0.15 0.1 0.4

Page 13

Target- no feedback

Target 0.15 0.93 0.01 0.11 0.15 0.23 0.04 0.15 0.1 0.4

Page 14

4/11/10 Chapter 12 Mini Case

In Tab 1, we assumed that the line of credit was drawn upon on the last day of the year, so there would be no interest expense. It is more realistic to assume that the line will be accessed throughout the year. This means adding to the LOC's balance would cause interest expense to go up, causing net income to go down, causing the additon to retained earnings to fall, causing the balance of retained earnings on the balance sheet to fall. But this would mean the balance sheets don't balance and additional amounts of the LOC would be required. But this would cause the cycle to begin again. This is called financing feedback. If you just program this into Excel, it will cause the model to have circularity. You can address this by setting the Iteration feature in Excel to iterate. Or you could use Goal Seek to find the proper amount of the LOC. But there is an easier adjustment that we explain below. We begin by presenting the financial statements for convenience. Figure MC-1. Financial Statements and Other Data (Millions except per share data) Balance Sheet, Hatfield, 12/31/10 Cash and securities $20 Accounts receivable 290 Inventories 390 Total current assets $700 Net fixed assets 500 Total assets $1,200 Accounts pay. + accruals Notes payable Total current liabilities Long-term debt Total liabilities Common stock Retained earnings Total common equity Total liab. & equity

Income Statement, Hatfield, 2010 Sales $2,000 Total operating costs 1,900 EBIT $100 Interest 60 EBT $40 Taxes (40%) 16 Net income $24 Dividends $9 Add'n to retain. earnings $15 Shares outstanding 10 EPS $2.40 DPS $0.90 Year-end stock price $24.00

$100 80 $180 520 $700 300 200 $500 $1,200

Selected Ratios and Other Data, 2010 Sales, 2010 (S0): Expected growth in sales: Profit margin (M): Assets/Sales (A 0*/S0): Payout ratio (POR): Equity multiplier (Assets/Equity): Total liability/Total assets Times interest earned (EBIT/Interest): Increase in sales (ΔS = gS0): (Payables + Accruals)/Sales (L0*/S 0): Operating costs/Sales: Cash/Sales: Receivables/Sales: Inventories/Sales: Fixed assets/Sales: Tax rate:

Hatfield $2,000 15.0% 1.2% 60.0% 37.5% 2.40 58.3% 1.67 $300 5.0% 95.0% 1.0% 14.5% 19.5% 25.0% 40.0%

Industry $2,000 15.0% 2.74% 50.0% 35.0% 2.13 53.0% 5.20 $300 4.0% 93.0% 1.0% 11.0% 15.0% 23.0% 40.0%

Set equal to Hatfield to make the data comparable.

Interest rate on all debt: Price/Earning (P/E): ROE (Net income/Common equity):

10.00% 10.0 4.80%

9.5% 12.0 11.64%

Forecasted Financial Statements: Incorporating Financing Feedback

We forecast the financial statements with these assumptions (most are the same as in Tab 1): (1) Operating ratios remain unchanged. (2) No additional notes payable, LT bonds, or common stock will be issued. (3) The interest rate on all debt is 10%. (4) If additional financing is needed, then it will be raised through a line of credit. The line of credit will be accessed smoothly throughout the year. The interest expense for the LOC will be based on its average balance during the year. (5) Interest expenses for notes payable and LT bonds are based on the average balances during the year. (6) If surplus funds are available, the surplus will be paid out as a special dividend payment. (7) Regular dividends will grow by 15%. (8) Sales will grow by 15%. This is called the "Steady" scenario because operations remain unchanged. The same assumptions apply to the Target scenario, except there are improvements in several areas of operations.

Only 2 formulas must change to incorporate the financing feedback from the LOC. Change the LOC in cell H131 and change the LOC interest expense in cell H143. We explain these changes below.

Use the Scenaro Manager to change scenarios. Inputs for Forecasts Sales growth rate Operating costs/Sales Cash/Sales Receivables/Sales Inventories/Sales Fixed assets/Sales Payables and accruals/ Sales Growth rate in regular dividends Interest rate on all debt Tax rate Hatfield 2010

Hatfield 2010 15.0% 95.0% 1.0% 14.5% 19.5% 25.0% 5.0% 15.0% 10.0% 40.0%

Forecast Scenarios Steady Target 15.0% 15.0% 95.0% 93.0% 1.0% 1.0% 14.5% 11.0% 19.5% 15.0% 25.0% 23.0% 5.0% 4.0% 15.0% 15.0% 10.0% 10.0% 40.0% 40.0%

Active is Steady 15.0% 95.0% 1.0% 14.5% 19.5% 25.0% 5.0% 15.0% 10.0% 40.0%

Forecast Factor

Basis for 2011 Forecast

w/o AFN 2011

Scenario: Steady Balance Sheet Assets Cash Accounts receivable Inventories Total current assets Net fixed assets Total assets

$20 290 390 $700 500 $1,200

1.00% 14.50% 19.50%

Factor × Forecasted Sales Factor × Forecasted Sales Factor × Forecasted Sales

25.00%

Factor × Forecasted Sales

Liabilities & equity Accts pay. and accruals Notes payable: Planned Line of credit (LOC) Total current liabs LT debt: Planned

$100 80 0 $180 520

5.00%

Factor × Forecasted Sales Carry over 2010 amount New LOC if AFN > 0 Carry over 2010 amount

$23.0 333.5 448.5 $805.0 575.0 $1,380.0

$115.0 80.0 0 $195.0 520.0