QuestionQuestion

Transcribed TextTranscribed Text

Mini Case_1 Figure MC-1. Financial Statements and Other Data (Millions except per share data) Calculations need to be cell referenced Balance Sheet, Hatfield, 12/31/10 Cash and securities Accounts receivable Inventories 390 Income Statement, Hatfield, 2010 Sales $3,000 Total operating costs 2,800 EBIT $200 Interest 54 EBT $146 Taxes (40%) 58 Net income $88 Dividends $28 Add. to retain. earnings $60 Shares outstanding 10 EPS $8.76 DPS $2.80 Total current assets Net fixed assets Total assets Accounts pay. + accruals Notes payable Total current liabilities Long-term debt $720 500 $1,220 $120 80 Total common equity $500 Total liab. & equity $1,220 Selected Ratios and Other Data, 2010 Sales, 2010 (S0): Expected growth in sales: Profit margin (M): Assets/Sales (A0*/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*/S0): 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): $200 520 $720 300 Retained earnings 200 Total liabilities Common stock Year-end stock price Industry $3,000 20.0% 3.67% 37.0% 35.0% 2 50.0% 5.20 $600 3.0% 90.0% 1.0% 8.2% 11.5% 14.8% 40.0% 9.0% 12.0 19.84% Assets/Equity = 2.44 2.00 $87.60 $40 290 DuPont ROE Hatfield Industry PM 2.92% 3.67% x Sales/Assets 2.46 2.70 Hatfield $3,000 18.0% 2.9% 40.7% 32.0% 2.44 59.0% 3.70 $540 4.0% 93.3% 1.3% 9.7% 13.0% 16.7% 40.0% 9.00% 10.0 17.52% x ROE 17.52% 19.84% AFNHatfield = = = Add'l Req'd Assets (A0*/S0)∆S (A0*/S0)(gS0) - Spontaneous liabi. - (L0*/S0)∆S - (L0*/S0)(gS0) - Add'n to RE - S1 × M × (1–POR) - 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. 1. Using algebra. The self-supporting growth rate can also be found by solving the equation below 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. Self-Supporting g = PM(1 – POR)(S0) A0* – L0* – PM(1 – POR)S0 = = 2. Using Goal Seek. To find the self-supporting growth rate with Goal Seek, first highlight cell B56. Then, on the Main Menu bar click Data>What-If-Analysis>Goal Seek. When you click OK, Cell D25 will change to the answer, which will cause Cell B56 to change to $0.00. Record the new growth rate and then return to the base case by clicking Cancel. Or, you could click OK to leave the new growth rate in Cell D25 and then over-type it with 18% in that cell to get back to the base case. In this assignement, please Leave the new value for D25 to receive credits for goal seek. 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 9%. (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 Tab 2 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 11.91%. (8) Sales will grow by 18%. 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 Scenario: Total current assets Net fixed assets Total assets Accts pay. and accruals Notes payable: Planned Line of credit (LOC) Total current liabs LT debt: Planned Total liabilities Common stock Scenario: Income Statement EBIT Earnings before taxes (EBT) Taxes Net inc. for common (NI) Dividends- regular (DIVs) Special dividends Add. to ret. earnings New line of credit (if AFN > 0) = Special dividend (if AFN ≤ 0) = Balance Sheet Assets Hatfield 2010 $120 80 0 18.00% 93.30% 1.30% 9.70% 13.00% 16.70% 4.00% 11.91% 9.00% 40.00% Forecast 18.00% 93.30% 1.30% 9.70% 13.00% 16.70% 4.00% 11.91% 9.00% 40.00% Factor × Forecasted Sales Forecast Scenarios Target 20.00% 90.00% 1.00% 8.20% 11.50% 14.80% 3.00% 12.90% 9.00% 40.00% Active is 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 Steady w/o AFN With AFN Factor Basis for 2011 Forecast 2011 2011 Cash Accounts receivable Inventories $40 290 390 Factor × Forecasted Sales Factor × Forecasted Sales Factor × Forecasted Sales $720 500 $1,220 Liabilities & equity Factor × Forecasted Sales Carry over 2010 amount New LOC if AFN > 0 Carry over 2010 amount Carry over 2010 amount $200 520 $720 300 Retained earnings 2010 + Add'n to RE from Income St. Total common equity Total liab. & equity AFN = TA – (Planned Liab & Equity) 200 $500 $1,220 Hatfield 2010 Forecast Factor Basis for 2011 Forecast w/o AFN 2011 With AFN 2011 Sales Total operating costs $3,000.0 2,800.0 (1 + Factor) × 2010 Sales Factor × Forecasted Sales Interest: NP planned Interest: LT debt planned Interest: Line of credit $200.0 7.2 46.8 0.0 $146.0 58.4 Rate x NP Rate x L-T Debt Rate x Beginning Balance Tax rate × EBT (1 + g) × 2010 Dividends Special dividend if AFN ≤ 0 NI − all dividends $87.6 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 $28.0 $0.0 $59.6 Hatfield 2010 Steady Forecast Scenarios Target Active is $120 $600 $1,100 NA 10.9% NA $8.76 $2.80 32.0% 2.9% 2.46 2.44 17.5% 93.3% 59.0% 3.70 ADJUSTED FOR INTEREST ON ADDED NOTES PAYABLE Adjusted for New Interest Data Used in the Scenarios Inputs for Forecasts Hatfield Steady State Target Active Growth rate Operating costs/sales Cash/Sales Receivables/Sales Inventories/Sales Fixed assets/Sales Payables and Accruals/ Sales Interest rate on notes payable Payout ratio Tax rate P/E ratio Shares outstanding (millions) 2010 Steady 18.0% 18.0% 93.3% 93.3% 1.3% 1.3% 9.7% 9.7% 13.0% 13.0% 16.7% 16.7% 4.0% 4.0% 9.0% 9.0% 32.0% 32.0% 40% 40% 10.0 10.0 10.000 10.000 Target 20.0% 90.0% 1.0% 8.2% 11.5% 14.8% 3.0% 9.0% 35.0% 40% 12.0 10.000 Adjusted for New Interest Hatfield Total current assets $720 Net fixed assets 500 Total assets $1,220 Claims on Assets Accts payable and accruals $120 Notes payable 80 Shares outstanding 10.000 Forecast This data is for: Factor × Forecasted Sales Factor × Forecasted Sales Carry over 2010 amount Difference between Assets and Liab+Equity: 2011 Balance Sheet 2010 Factor Procedure for 2011 Forecast Forecast Assets Cash $40 Accounts receivable 290 Inventories 390 Factor × Forecasted Sales Factor × Forecasted Sales Factor × Forecasted Sales 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 Statement Total common equity $500 Total liabs and equity $1,220 Year-end stock price $87.60 Adjusted for New Interest Adjusted for New Interest 2010 Income Statement Hatfield 2010 Forecast Factors Scenario: Interest rate × (NP+L-T Debt) Interest rate × (0.5 × Δ notes) Tax rate × 2011 EBT Payout Ratio × NI Forecast 2011 Sales $3,000.0 Total operating costs 2,800.0 (1 + Factor) × 2010 Sales Factor × Forecasted Sales EBIT $200.0 Interest on initial debt Interest on 1/2 of new debt 54.0 0.0 Total interest $54.0 Earnings before taxes (EBT) $146.0 Taxes 58.4 Net income for common (NI) Dividends (DIVs) Add. to ret. earns (NI – DIVs) $87.6 $28.0 $59.6 Shares outstanding EPS DPS Stock Price 10.000 $8.76 $2.80 $87.60 Adjusted for New Interest Adjusted for New Interest Performance EPS Year-end stock price Profit margin (PM) Sales/Assets (Assets turnover) ROE Debt/Assets Assets/Equity TIE ratio Payout ratio Hatfield 2010 $8.76 $87.60 2.9% 2.46 17.5% 59.0% 2.44 3.70 32.0% Steady State Steady Final Target Target Active 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.

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

Financial Statement Case
    $80.00 for this solution

    PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

    Find A Tutor

    View available Finance Tutors

    Get College Homework Help.

    Are you sure you don't want to upload any files?

    Fast tutor response requires as much info as possible.

    Decision:
    Upload a file
    Continue without uploading

    SUBMIT YOUR HOMEWORK
    We couldn't find that subject.
    Please select the best match from the list below.

    We'll send you an email right away. If it's not in your inbox, check your spam folder.

    • 1
    • 2
    • 3
    Live Chats