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 MirceDrive's fixed asset capacity was used only to 70%, what is the AFN? (5 points) Table 1 MircoDrivs's Balance sheet and Income statement Table 1 MircoDrive's Balance sheet and Income statement 2013 $ 50 500 1,000 $ 1,550 2,000 $ 3,550 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 $ 550 S 500 Inventories 820 Interest expense 100 120 Total CA $ 1,300 Pre-tax earnings S 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 $ 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 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 50 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 MirceDrive's fixed asset capacity was used only to 70%, what is the AFN? (5 points) Table 1 MircoDrivs's Balance sheet and Income statement Table 1 MircoDrive's Balance sheet and Income statement 2013 $ 50 500 1,000 $ 1,550 2,000 $ 3,550 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 $ 550 S 500 Inventories 820 Interest expense 100 120 Total CA $ 1,300 Pre-tax earnings S 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 $ 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 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 50