Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Chapter 3 Problem 7 Note: Manual calculation mode is on. You must press F9 key to calculate. Facts and Assumptions Year 2014 2015 2016 Net
Chapter 3 Problem 7 | ||||
Note: Manual calculation mode is on. You must press F9 key to calculate. | ||||
Facts and Assumptions | ||||
Year | 2014 | 2015 | 2016 | |
Net sales | $20,613 | |||
Growth rate in sales | 25% | 30% | ||
Cost of goods sold/net sales | 86% | 86% | ||
Gen., sell,, and admin. expenses/net sales | 12% | 11% | ||
Long-term debt | $760 | $660 | $560 | |
Current portion long-term debt | $100 | $100 | $100 | |
Interest rate | 10% | 10% | ||
Tax rate | 45% | 45% | ||
Dividend/earnings after tax | 50% | 50% | ||
Current assets/net sales | 29% | 29% | ||
Net fixed assets | $280 | $270 | ||
Current liabilities/net sales | 14.5% | 14.4% | ||
Owners' equity | $1,730 | |||
INCOME STATEMENT | ||||
Year | 2014 | Forecast 2015 | 2016 | |
Net sales | $25,766 | |||
Cost of good sold | 22,159 | |||
Gross profit | 3,607 | |||
Gen., sell,, and admin. exp. | 3,092 | |||
Interest expense | 231 | |||
Earnings before tax | 285 | |||
Tax | 128 | |||
Earnings after tax | 156 | |||
Dividends paid | 78 | |||
Additions to retained earnings | 78 | |||
BALANCE SHEET | ||||
Current assets | $7,472 | |||
Net fixed assets | 280 | |||
Total assets | 7,752 | |||
Current liabilities | 3,736 | |||
Long-term debt | 660 | |||
Equity | 1,808 | |||
Total liabilities and shareholders' equity | 6,204 | |||
EXTERNAL FUNDING REQUIRED | $1,548 | |||
Chapter 3 Problem 13 | ||||||||||||
13. | Below are the 2014 financial statements for Aquatic Supplies Co. Also appearing are managements forecasts for how individual financial statement items will vary in the | |||||||||||
future. The company expects sales to grow 12% next year. Aquatic Supplies finances all of its needs with 10-year long-term debt at 10% interest, while excess cash at the end | ||||||||||||
of the year is added to the cash balance. | ||||||||||||
a. | Prepare a spreadsheet to estimate Aquatic Supplies 's 2015 need for external funding assuming long-term debt and interest expense remain at their 2011 levels. | |||||||||||
b. | Modify your spreadsheet forecast in part (a) to capture the interdependence between the loan and interest expense. That is, switch your spreadsheet to "manual calculation" | |||||||||||
and include the necessary loan and added interest expense in your forecast. | ||||||||||||
c. | Is the required loan in part (b) equal to the required loan you calculated in part (a)? Why are they different? | |||||||||||
d. | Perform a sensitivity analysis of Aquatic Supplies Co.s external financing needs as determined in part (b). Assume sales grow at 17% instead of 12%. How much does the | |||||||||||
bank loan increase as sales go from 12% to 17%? | ||||||||||||
e. | Perform a scenario analysis on the companys projection as determined in part (b). Assume sales grow 20%, the cost of goods sold is 38% of sales, inventory falls from 5% | |||||||||||
of sales to 3%, and accounts receivable fall from 13% of sales to 10%. What happens to the loan need in this scenario relative to your answer in part (b)? | ||||||||||||
f. | Return now to the original assumptions and extend your projections in part (b) through 2019. Continue to assume that all external funding needs will be met with debt at 10% | |||||||||||
interest and any excess cash will add to the companys cash balance. What are your projected values for long-term debt and cash and equivalents in 2019? | ||||||||||||
g. | Perform a scenario analysis on your 5-year projection in part (f). Assume growth in sales is 10%, the cost of goods sold is 41% of sales, and selling, general and | |||||||||||
administrative expenses are 50% of sales. What are your projected values for long-term debt and cash balance in 2019? | ||||||||||||
Aquatic Supplies Co. | ||||||||||||
Income Statement (in $ millions) | ||||||||||||
2014 | Assumptions | |||||||||||
Sales | $582.762 | 12% | growth in sales | |||||||||
Cost of Goods Sold | 240.828 | 39% | percentage of sales | |||||||||
Gross Profit | 341.934 | |||||||||||
Selling, General, & Administrative Exp. | 257.507 | 49% | percentage of sales | |||||||||
Operating Income Before Deprec. | 84.427 | |||||||||||
Depreciation,Depletion,&Amortization | 25.221 | 30% | percentage of net PP&E | |||||||||
Operating Profit | 59.206 | |||||||||||
Interest Expense | 16.430 | initially constant | ||||||||||
Pretax Income | 42.776 | |||||||||||
Total Income Taxes | 14.971 | 35% | percentage of earnings before taxes | |||||||||
Net income | $27.805 | |||||||||||
Balance Sheet (in $ millions) | ||||||||||||
ASSETS | ||||||||||||
Cash & Equivalents | $7.152 | 2% | minimum cash balance as % of sales | |||||||||
Account Receivable | 70.538 | 13% | percentage of sales | |||||||||
Inventories | 39.033 | 5% | percentage of sales | |||||||||
Prepaid Expenses | 9.339 | no change | ||||||||||
Other Current Assets | 27.076 | 6% | percentage of sales | |||||||||
Total Current Assets | 153.138 | |||||||||||
Net Plant, Property & Equipment | 81.648 | 15% | percentage of sales | |||||||||
Intangibles | 9.415 | no change | ||||||||||
Other Assets | 24.642 | 5% | percentage of sales | |||||||||
TOTAL ASSETS | $268.843 | |||||||||||
LIABILITIES | ||||||||||||
Accounts Payable | $36.951 | 6% | percentage of sales | |||||||||
Accrued Expenses | 31.206 | 5% | percentage of sales | |||||||||
Other Current Liabilities | 3.663 | no change | ||||||||||
Total Current Liabilities | 71.820 | |||||||||||
Long Term Debt | 157.720 | initially constant | ||||||||||
Accrued wages | 21.418 | 3% | percentage of sales | |||||||||
Total Liabilities | 250.958 | |||||||||||
EQUITY | ||||||||||||
Common Stock | 1.702 | no change | ||||||||||
Capital Surplus | 55.513 | no change | ||||||||||
Retained Earnings | 118.729 | no dividends paid so all income is retained | ||||||||||
Less: Treasury Stock | 158.059 | no change | ||||||||||
TOTAL EQUITY | 17.885 | |||||||||||
TOTAL LIABILITIES & EQUITY | $268.843 | |||||||||||
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started