finance mini case

finance mini case

4/11/10
Chapter 9 Mini Case
Figure MC-1. Financial Statements and Other Data (Millions except per share data)

Balance Sheet, Hatfield, 12/31/10                Income Statement, Hatfield, 2010
Cash and securities        $40         Sales        $2,000
Accounts receivable        290         Total operating costs        1,900
Inventories        390         EBIT        $100
Total current assets        $720         Interest        60
Net fixed assets        500         EBT        $40
Total assets        $1,220         Taxes (40%)        16
Net income        $24
Accounts pay. + accruals        $120         Dividends        $9
Notes payable        80         Add. to retain. earnings        $15
Total current liabilities        $200         Shares outstanding        10
Long-term debt        520         EPS        $2.40
Total liabilities        $720         DPS        $0.90
Common stock        300         Year-end stock price        $24.00
Retained earnings        200
Total common equity        $500
Total liab. & equity        $1,220

Selected Ratios and Other Data, 2010            Hatfield    Industry
Sales, 2010 (S0):    $2,000    $2,000
Expected growth in sales:    15.0%    15.0%
Profit margin (M):    1.2%    2.74%
Assets/Sales (A0*/S0):    61.0%    50.0%
Payout ratio (POR):    37.5%    35.0%
Equity multiplier (Assets/Equity):    2.44    2.13
Total liability/Total assets    59.0%    53.0%
Times interest earned (EBIT/Interest):    1.67    5.20
Increase in sales (ΔS = gS0):    $300     $300
(Payables + Accruals)/Sales (L0*/S0):    6.0%    4.0%
Operating costs/Sales:    95.0%    93.0%
Cash/Sales:    2.0%    1.0%
Receivables/Sales:    14.5%    11.0%
Inventories/Sales:    19.5%    15.0%
Fixed assets/Sales:    25.0%    23.0%
Tax rate:    40.0%    40.0%
Interest rate on all debt:    10.00%    9.5%
Price/Earning (P/E):    10.0    12.0
ROE (Net income/Common equity):    4.80%    11.64%

DuPont ROE    PM    x      Sales/Assets       x        Assets/Equity     =    ROE
Hatfield    1.20%    1.64        2.44    4.80%
Industry    2.74%    2.00        2.13    11.67%

AFNHatfield    =    Add’l Req’d Assets        –    Spontaneous liabilities    –    Add’n to RE
=     (A0*/S0)∆S          –     (L0*/S0)∆S     –    S1 × M × (1–POR)

=     (A0*/S0)(gS0)           –     (L0*/S0)(gS0)     –    S1 × M × (1–POR)

=             –         –

AFNHatfield    =          million

Self-Supporting Growth Rate.  This is the maximum growth rate that can be attained without raising external funds, i.e., the value of g that forces AFN = 0, holding

other things constant. Find g 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 equation as shown on the 3rd row above AFN, then finding the value of g that causes

AFN to equal zero.  This results in the same value as we find with Goal Seek.  The algebriac solution is easy if we give you the equation, but if you had to solve the

AFN equation for g, you would probably find the Goal Seek solution easier.
PM(1 – POR)(S0)
Self-Supporting g    =            =         =
A0* – L0* – PM(1 – POR)S0

Therefore, if the firm’s ratios remain constant, the company can grow at about g without external financing.

2.  Using Goal Seek.  To find the self-supporting growth rate with Goal Seek, first highlight cell B56. Then, with Excel 07, on the Main Menu bar click Data>What-If-

Analysis>Goal Seek. With Excel 03 click Tools>Goal Seek.  Then complete the dialog box as shown to the right.   When you click OK, Cell D25 will change to the self-

supporting g, which will cause Cell B56 to change to $0.00.  You should click OK to leave the new growth rate in Cell D25.

Goal Seek is one of Excel’s most useful features.  We use it elsewhere in this chapter to find the required amount of new capital. In capital budgeting, we use it to

see how high the WACC can go before the NPV becomes negative, how low the WACC must be for the NPV to be positive, how low the initial cost must be to achieve a

positive NPV, how long a project must last to achieve a positive NPV, and so forth.  We have worked on real world cases dealing with almost every chapter in the text,

and we almost always have 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 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 tapped on the last day of the year, so there will be no additional interest charges due to the line of credit. (On Second Section we relax this assumption and

assume that the line of credit is accessed smoothly throughout 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.

Use the Scenaro Manager to change scenarios.

Inputs for Forecasts            Hatfield 2010    Forecast Scenarios        Active is
Steady    Target
Sales growth rate            15.0%    15.0%    15.0%
Operating costs/Sales            95.0%    95.0%    93.0%
Cash/Sales            2.0%    2.0%    1.0%
Receivables/Sales            14.5%    14.5%    11.0%
Inventories/Sales            19.5%    19.5%    15.0%
Fixed assets/Sales            25.0%    25.0%    23.0%
Payables and accruals/ Sales            6.0%    6.0%    4.0%
Growth rate in regular dividends            15.0%    15.0%    15.0%
Interest rate on all debt            10.0%    10.0%    10.0%
Tax rate            40.0%    40.0%    40.0%

Scenario:         Hatfield    Forecast            w/o AFN    With AFN
Balance Sheet        2010    Factor    Basis for 2011 Forecast        2011    2011
Assets
Cash        $40         Factor × Forecasted Sales
Accounts receivable        290         Factor × Forecasted Sales
Inventories        390         Factor × Forecasted Sales
Total current assets        $720
Net fixed assets        500         Factor × Forecasted Sales
Total assets        $1,220

Liabilities & equity
Accts pay. and accruals        $120        Factor × Forecasted Sales
Notes payable: Planned        80        Carry over 2010 amount
Line of credit (LOC)        0        New LOC if AFN > 0
Total current liabs        $200
LT debt: Planned        520        Carry over 2010 amount
Total liabilities        $720
Common stock        300        Carry over 2010 amount
Retained earnings        200        2010 + Add’n to RE from Income St.
Total common equity        $500
Total liab. & equity        $1,220
AFN = TA – (Planned Liab & Equity)
New line of credit (if AFN > 0)  =
Special dividend (if AFN ≤ 0)  =

Scenario:         Hatfield    Forecast            w/o AFN    With AFN
Income Statement        2010    Factor    Basis for 2011 Forecast        2011    2011
Sales        $2,000.0        (1 + Factor) × 2010 Sales
Total operating costs        1,900.0        Factor × Forecasted Sales
EBIT        $100.0
Interest: NP planned        8.0        Rate x Avg Balance
Interest: LT debt planned        52.0        Rate x Avg Balance
Interest: Line of credit        0.0        Rate x Beginning Balance
Earnings before taxes (EBT)        $40.0
Taxes         16.0        Tax rate ×  EBT
Net inc. for common  (NI)        $24.0
Dividends- regular (DIVs)        $9.0        (1 + g) × 2010 Dividends
Special dividends                Special dividend if AFN ≤ 0
Add. to ret. earnings         $15.0        NI − all dividends

Hatfield 2010    Forecast Scenarios        Active is
Performance                Steady    Target
Net operating profits after taxes            $100
Net operating working capital            $600
Total operating capital            $1,100
Free cash flow            NA
Return on invested capital            9.1%
AFN            NA
EPS            $2.40
DPS (regular dividends)            $0.90
Payout ratio (all dividends)            37.5%
Profit margin            1.2%
Sales/Assets (Assets turnover)            1.64
Assets/Equity            2.44
ROE            4.8%
Operating costs/Sales            95.0%
Total liability/Total assets            59.0%
TIE ratio            1.67

See Below as Section 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
Inputs for Forecasts        Hatfield 2010    Forecast Scenarios        Active
Steady    Target
Growth rate        15.0%    15.0%    15.0%
Operating costs/sales        95.0%    95.0%    93.0%
Cash/Sales        2.0%    2.0%    1.0%
Receivables/Sales        14.5%    14.5%    11.0%
Inventories/Sales        19.5%    19.5%    15.0%
Fixed assets/Sales        25.0%    25.0%    23.0%
Payables and Accruals/ Sales        6.0%    6.0%    4.0%
Interest rate on notes payable        10.0%    10.0%    9.5%
Payout ratio        37.5%    37.5%    35.0%
Tax rate        40%    40%    40%
P/E ratio        10.0    10.0    12.0
Shares outstanding (millions)        10.000    10.000    10.000

Adjusted for New Interest        Hatfield    Forecast    Scenario        2011
Balance Sheet        2010    Factor    Procedure for 2011 Forecast        Forecast
Assets
Cash        $40         Factor  ×   Forecasted Sales
Accounts receivable        290         Factor  ×   Forecasted Sales
Inventories        390         Factor  ×   Forecasted Sales
Total current assets        $720
Net fixed assets        500         Factor  ×   Forecasted Sales
Total assets        $1,220

Claims on Assets
Accts payable and accruals        $120        Factor  ×   Forecasted Sales
Notes payable        80        Carry over 2010 amount
Add’ notes to balance        0        New notes (+/-) to balance
Total current liabs        $200
Long Term Debt        520        Carry over 2010 amount
Total liabilities        $720
Common stock        300        Carry over 2010 amount
Retained earnings        200                 2010 + Add’n to RE from Income St.
Total common equity        $500
Total liabs and equity        $1,220
Shares outstanding        10.000            Difference between Assets and Liab+Equity:
Year-end stock price        $24.00

Adjusted for New Interest
Adjusted for New Interest        2010 Actual    % of Sales Factors    Scenario:        Forecast 2011
2010 Income Statement
Sales        $2,000.0        (1 + Factor)  ×   2010 Sales
Total operating costs        1,900.0        Factor  ×   Forecasted Sales
EBIT        $100.0
Interest on initial debt        60.0        Carry over 2010 amount
Interest on 1/2 of new debt        0.0            Interest rate × (0.5 × Δ notes)
Total interest        $60.0
Earnings before taxes (EBT)        $40.0
Taxes         16.0        Tax rate × 2011 EBT
Net income for common  (NI)        $24.0
Dividends (DIVs)        $9.0
Add. to ret. earns (NI – DIVs)        $15.0
Shares outstanding        10.000
EPS        $2.40
DPS        $0.90
Stock Price        $24.00

Adjusted for New Interest            Adjusted for New Interest
Performance                Forecast Scenarios        Active is
2010 Actual    Steady    Target
EPS            $2.40
Year-end stock price            $24.00                                    `

Profit margin (PM)            1.2%
Sales/Assets (Assets turnover)            1.64
ROE            4.8%
Debt/Assets            59.0%
Assets/Equity            2.44
TIE ratio            1.67
Payout ratio            37.5%

Final comment:  Different problems require somewhat different models–one size does not fit all.  For example, a firm’s growth rate might be low, and if that resulted

in a negative AFN, then a model would have to be programmed to do something with the excess funds.  The model on Tab 2 is an example.

1. Based on the data in Figure MC-1, how well run does Hatfield appear to be in comparison to other firms in its industry?  What are its primary strengths and

weaknesses?  Be specific in your answer, and point to various ratios that support your position.  Also, use the DuPont equation as one part of your analysis.

find the cost of your paper