The following tables report MicroDrive's financial statements in 2013. Assume that Hatfield is operating at its full capacity in 2013. The company expects its sales to grow at 13.5%. Assets-to-sale ratios and spontaneous-liabilities-to-sale ratio remain same as the 2013 level. Other financial and operating ratio also keep the same as the 2013 level. (Use the attached excel file to solve the questions) (1) Calculate AFN (additional funds needed) if the sales growth rate is 13.5% (assumes all financial and operating ratios keep the same as the 2013 level). (10 points) (2) What is MicroDrive's self-growth rate? (5 points) (Use goal seek and take a snap shot and copy paste next to your solution) (3) If MirceDriveis fixed asset capacity was used only to 70%, what is the AFN? (5 points) Table 1 MircoDrive's Balance sheet and Income statement Table 1 MircoDrive's Balance sheet and Income statement 2013 S 50 500 1,000 $ 1,550 2,000 $ 3,550 S MicroDrive's Most Recent Financial Statements (Millions, Except for Per Share Data) INCOME STATEMENTS BALANCE SHEETS 2012 2013 Assets 2012 Net sales $ 4,760 $5,000 Cash S 60 COGS (excl. depr.) 4,040 4,300 ST Investments 40 Depreciation 170 200 Accounts receivable 380 EBIT s 550 s 500 Inventories 820 Interest expense 100 120 Total CA $ 1,300 Pre-tax earnings 450 s 380 Net Plant and equip. 1,700 Taxes (40%) 180 152 Total assets $ 3,000 NI before pref. div. $ 270 s 228 Preferred div. 8 Liabilities and equity Net income $ 262 S 220 Accounts payable S 190 Accruals 280 Other Data Notes payable 130 Common dividends $48 $50 Total CL $ 600 Addition to RE $214 $170 Long-term bonds 1,000 Tax rate 40% 40% Total liabilities $ 1,600 Shares of common stock 50 Preferred stock 100 Earnings per share $5.24 $4.40 Common stock 500 Dividends per share $0.96 $1.00 Retained earnings 800 Price per share S40.00 $27.00 Total common equity $ 1,300 Total liabs. & equity $ 3,000 8 S 200 300 280 $ 780 1,200 $ 1,980 100 500 970 $ 1,470 $ 3,550 50 2013 50 S 500 1,000 $ 1,550 2,000 $ 3,550 450 MicroDrive's Most Recent Financial Statements (Millions, Except for Per Share Data) INCOME STATEMENTS BALANCE SHEETS 2012 2013 Assets 2012 Net sales $ 4,760 $ 5,000 Cash S 60 COGS (excl. depr.) 4,040 4,300 ST Investments 40 Depreciation 170 200 Accounts receivable 380 EBIT S 550 S 500 Inventories 820 Interest expense 100 120 Total CA $ 1,300 Pre-tax earnings S $ 380 Net Plant and equip. 1,700 Taxes (40%) 180 152 Total assets $ 3,000 NI before pref. div. $ 270 $ 228 Preferred dir. 8 8 Liabilities and equity Net income S 262 S 220 Accounts payable 190 Accruals 280 Other Data Notes payable 130 Common dividends $48 $50 Total CL S 600 Addition to RE $214 $170 Long-term bonds 1,000 Tax rate 40% 40% Total liabilities $ 1,600 Shares of common stock 500 500 Preferred stock 100 Earnings per share $5.24 $4.40 Common stock 500 Dividends per share $0.96 $1.00 Retained earnings 800 Price per share $40.00 $27.00 Total common equity $ 1,300 Total liabs. & equity $ 3,000 ca S 200 300 280 780 1,200 $ 1,980 100 500 970 $ 1,470 $ 3,550 (1) Additional Funds Needed (Please input numbers in the highlighted cells) So: (2) Self-growth Rate g: g (self-growth rate) S: gS: A.: Act / S (3) Excess Capacity Adjustments FA capacity was used only to this percent Lt/S: 2013 Fixed Assets Profit margin (M): Excess Fixed Assets for 2013 A* / s, if Fixed Assets had been Payout ratio (POR): operated at full capacity = (TA- Excess Sales AFN using revised Required Increase in Assets ratio in the AFN equation Spontaneous Increase in Payables and Accruals Addition to Retained Earnings. AFN The following tables report MicroDrive's financial statements in 2013. Assume that Hatfield is operating at its full capacity in 2013. The company expects its sales to grow at 13.5%. Assets-to-sale ratios and spontaneous-liabilities-to-sale ratio remain same as the 2013 level. Other financial and operating ratio also keep the same as the 2013 level. (Use the attached excel file to solve the questions) (1) Calculate AFN (additional funds needed) if the sales growth rate is 13.5% (assumes all financial and operating ratios keep the same as the 2013 level). (10 points) (2) What is MicroDrive's self-growth rate? (5 points) (Use goal seek and take a snap shot and copy paste next to your solution) (3) If MirceDriveis fixed asset capacity was used only to 70%, what is the AFN? (5 points) Table 1 MircoDrive's Balance sheet and Income statement Table 1 MircoDrive's Balance sheet and Income statement 2013 S 50 500 1,000 $ 1,550 2,000 $ 3,550 S MicroDrive's Most Recent Financial Statements (Millions, Except for Per Share Data) INCOME STATEMENTS BALANCE SHEETS 2012 2013 Assets 2012 Net sales $ 4,760 $5,000 Cash S 60 COGS (excl. depr.) 4,040 4,300 ST Investments 40 Depreciation 170 200 Accounts receivable 380 EBIT s 550 s 500 Inventories 820 Interest expense 100 120 Total CA $ 1,300 Pre-tax earnings 450 s 380 Net Plant and equip. 1,700 Taxes (40%) 180 152 Total assets $ 3,000 NI before pref. div. $ 270 s 228 Preferred div. 8 Liabilities and equity Net income $ 262 S 220 Accounts payable S 190 Accruals 280 Other Data Notes payable 130 Common dividends $48 $50 Total CL $ 600 Addition to RE $214 $170 Long-term bonds 1,000 Tax rate 40% 40% Total liabilities $ 1,600 Shares of common stock 50 Preferred stock 100 Earnings per share $5.24 $4.40 Common stock 500 Dividends per share $0.96 $1.00 Retained earnings 800 Price per share S40.00 $27.00 Total common equity $ 1,300 Total liabs. & equity $ 3,000 8 S 200 300 280 $ 780 1,200 $ 1,980 100 500 970 $ 1,470 $ 3,550 50 2013 50 S 500 1,000 $ 1,550 2,000 $ 3,550 450 MicroDrive's Most Recent Financial Statements (Millions, Except for Per Share Data) INCOME STATEMENTS BALANCE SHEETS 2012 2013 Assets 2012 Net sales $ 4,760 $ 5,000 Cash S 60 COGS (excl. depr.) 4,040 4,300 ST Investments 40 Depreciation 170 200 Accounts receivable 380 EBIT S 550 S 500 Inventories 820 Interest expense 100 120 Total CA $ 1,300 Pre-tax earnings S $ 380 Net Plant and equip. 1,700 Taxes (40%) 180 152 Total assets $ 3,000 NI before pref. div. $ 270 $ 228 Preferred dir. 8 8 Liabilities and equity Net income S 262 S 220 Accounts payable 190 Accruals 280 Other Data Notes payable 130 Common dividends $48 $50 Total CL S 600 Addition to RE $214 $170 Long-term bonds 1,000 Tax rate 40% 40% Total liabilities $ 1,600 Shares of common stock 500 500 Preferred stock 100 Earnings per share $5.24 $4.40 Common stock 500 Dividends per share $0.96 $1.00 Retained earnings 800 Price per share $40.00 $27.00 Total common equity $ 1,300 Total liabs. & equity $ 3,000 ca S 200 300 280 780 1,200 $ 1,980 100 500 970 $ 1,470 $ 3,550 (1) Additional Funds Needed (Please input numbers in the highlighted cells) So: (2) Self-growth Rate g: g (self-growth rate) S: gS: A.: Act / S (3) Excess Capacity Adjustments FA capacity was used only to this percent Lt/S: 2013 Fixed Assets Profit margin (M): Excess Fixed Assets for 2013 A* / s, if Fixed Assets had been Payout ratio (POR): operated at full capacity = (TA- Excess Sales AFN using revised Required Increase in Assets ratio in the AFN equation Spontaneous Increase in Payables and Accruals Addition to Retained Earnings. AFN