Below are the 2017 financial statements for Aquatic Supplies Co. Also appearing
ID: 2821914 • Letter: B
Question
Below are the 2017 financial statements for Aquatic Supplies Co. Also appearing are management’s 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.
Prepare pro forma financial statements for Aquatic Supplies for 2018 assuming that long-term debt and interest expense remain at their 2017 levels. What is Aquatic's external funding required for 2018?
Modify your forecast in part (a) assuming that long-term debt and interest expense increased in order to make up the external funding required for 2018. (Be sure to enable interactive calculation in Excel.) How much will additional long-term debt (compared to 2017) be required under this assumption?
Why are your answers to part (a) and part (b) different?
Perform a sensitivity analysis of Aquatic Supplies Co.’s external financing needs as determined in part (b). Assume sales grow by 17% instead of 12%. How much total long-term debt be required?
Perform a scenario analysis on the company’s 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%. How much long-term debt is required in this scenario?
Return now to the original assumptions (sales growth=12%, COGS=39%, inventory=5%, AR=13%) and extend your projections in part (b) through 2022. Continue to assume that all external funding needs will be met with debt at 10% interest. What is your projected value for long-term debt in 2022?
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 is your projected value for long-term debt in 2022?
AQUATIC SUPPLIES CO.
INCOME STATEMENT ($ millions)
2017
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 Expense
257.507
49%
percentage of sales
Operating Income
84.427
Depreciation & 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 pretax income
Net income
$27.805
BALANCE SHEET ($ millions)
ASSETS
Cash & Equivalents
$7.152
2%
minimum 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 Property, Plant, & Equipment
81.648
15%
percentage of sales
Intangible Assets
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
Less: Treasury Stock
158.059
no change
Total Equity
17.885
TOTAL LIABILITIES & EQUITY
$268.843
AQUATIC SUPPLIES CO.
INCOME STATEMENT ($ millions)
2017
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 Expense
257.507
49%
percentage of sales
Operating Income
84.427
Depreciation & 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 pretax income
Net income
$27.805
BALANCE SHEET ($ millions)
ASSETS
Cash & Equivalents
$7.152
2%
minimum 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 Property, Plant, & Equipment
81.648
15%
percentage of sales
Intangible Assets
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
Less: Treasury Stock
158.059
no change
Total Equity
17.885
TOTAL LIABILITIES & EQUITY
$268.843
Explanation / Answer
Solution for part (a):
Aquatic Supplies Co.
Income Statement (in $ millions)
2017
Assumptions
Pro-forma 2018 ignoring inter-dependencies
Sales (a)
$582.762
12%
$652.693
Cost of goods sold (b)
$240.828
39%
$254.550
Gross profit (c = a – b)
$341.934
$398.143
Selling, general & administrative expenses (d)
$257.507
49%
$319.820
Operating income before depreciation (e = c – d)
$84.427
$78.323
Depreciation, depletion & amortization (f)
$25.221
30%
$29.371
Operating profit (g = e – f)
$59.206
$48.952
Interest Expense (h)
$16.430
Initially constant
$16.430
Pre-tax income (i = g – h)
$42.776
$35.522
Total income taxes (j)
$14.971
35%
$11.383
Net income (k = i – j)
$27.805
$21.139
Aquatic Supplies Co.
Balance Sheet (in $ millions)
2017
Assumptions
Pro-forma 2018 ignoring inter-dependencies
ASSETS
(in $)
Cash & Equivalents
7.152
2%
13.054
Account Receivable
70.538
13%
84.850
Inventories
39.033
5%
32.635
Prepaid Expenses
9.339
No change
9.339
Other Current Assets
27.076
6%
39.162
Total Current Assets
153.138
179.039
Net Plant, Property & Equipments
81.648
15%
97.904
Intangibles
9.415
No change
9.415
Other Assets
24.642
5%
32.635
TOTAL ASSETS
268.843
318.993
LIABILITIES
Accounts Payable
36.951
6%
39.162
Accrued Expenses
31.206
5%
32.635
Other Current Liabilities
3.663
No change
3.663
Total Current Liabilities
71.820
75.459
Long Term debt
157.720
Initially constant
157.720
Accrued wages
21.418
3%
19.581
Total Liabilities
250.958
252.760
EQUITY
Common Stock
1.702
No change
1.702
Capital Surplus
55.513
No change
55.513
Retained Earnings
118.729
No dividends paid so all income is retained
139.868
Less: Treasury Stock
158.059
No change
158.059
TOTAL EQUITY
17.885
39.024
TOTAL LIABILITIES & EQUITY
268.843
291.784
Aquatic Supplies will need an additional $27.209 million in debt to finance its activities for 2018.
Solution for part (b):
Aquatic Supplies Co.
Income Statement (in $ millions)
2017
Assumptions
Pro-forma 2018 including inter-dependencies
Sales (a)
$582.762
12%
$652.693
Cost of goods sold (b)
$240.828
39%
$254.550
Gross profit (c = a – b)
$341.934
$398.143
Selling, general & administrative expenses (d)
$257.507
49%
$319.820
Operating income before depreciation (e = c – d)
$84.427
$78.323
Depreciation, depletion & amortization (f)
$25.221
30%
$29.371
Operating profit (g = e – f)
$59.206
$48.952
Interest Expense (h)
$16.430
Was initially constant
$18.636
Pre-tax income (i = g – h)
$42.776
$30.316
Total income taxes (j)
$14.971
35%
$10.611
Net income (k = i – j)
$27.805
$19.705
Aquatic Supplies Co.
Balance Sheet (in $ millions)
2017
Assumptions
Pro-forma 2018 ignoring inter-dependencies
ASSETS
(in $)
Cash & Equivalents
7.152
2%
13.054
Account Receivable
70.538
13%
84.850
Inventories
39.033
5%
32.635
Prepaid Expenses
9.339
No change
9.339
Other Current Assets
27.076
6%
39.162
Total Current Assets
153.138
179.039
Net Plant, Property & Equipments
81.648
15%
97.904
Intangibles
9.415
No change
9.415
Other Assets
24.642
5%
32.635
TOTAL ASSETS
268.843
318.993
LIABILITIES
Accounts Payable
36.951
6%
39.162
Accrued Expenses
31.206
5%
32.635
Other Current Liabilities
3.663
No change
3.663
Total Current Liabilities
71.820
75.459
Long Term debt
157.720
Was initially constant
186.363
Accrued wages
21.418
3%
19.581
Total Liabilities
250.958
281.403
EQUITY
Common Stock
1.702
No change
1.702
Capital Surplus
55.513
No change
55.513
Retained Earnings
118.729
No dividends paid so all income is retained
138.434
Less: Treasury Stock
158.059
No change
158.059
TOTAL EQUITY
17.885
37.590
TOTAL LIABILITIES & EQUITY
268.843
318.993
When the interdependency between long-term debt and interest expense is included, interest expense increases from $16.430 million to $18.636 million and the need for new financing rises from $27.209 million to $28.643 million ($186.363 - $157.720 = $28.643), or $1.434,000. (Note: it is necessary to use manual calculation in this spreadsheet).
Solution for part (c):
The increase in the loan need calculated in part (a) was $27.209 million. The comparable number in part (b) was $28,643 million. They differ because the loan need in part (b) includes the interdependence between the loan and interest expense. Although the interest expense rises $2,206,000 in part (b), the tax-deductibility of interest results in an increased loan need of $1,434,000 [$2,206,000 * (1 – 0.35) = $1,434,000] or 5.3%.
Solution for part (d):
Aquatic Supplies Co.
Income Statement (in $ millions)
2017
Assumptions
Sensitivity analysis 2018
Sales (a)
$582.762
12%
$681.832
Cost of goods sold (b)
$240.828
39%
$265.914
Gross profit (c = a – b)
$341.934
$415.917
Selling, general & administrative expenses (d)
$257.507
49%
$334.097
Operating income before depreciation (e = c – d)
$84.427
$81.820
Depreciation, depletion & amortization (f)
$25.221
30%
$30.682
Operating profit (g = e – f)
$59.206
$51.137
Interest Expense (h)
$16.430
Was initially constant
$19.482
Pre-tax income (i = g – h)
$42.776
$31.656
Total income taxes (j)
$14.971
35%
$11.080
Net income (k = i – j)
$27.805
$20.576
Aquatic Supplies Co.
Balance Sheet (in $ millions)
2017
Assumptions
Sensitivity analysis 2018
ASSETS
(in $)
Cash & Equivalents
7.152
2%
13.637
Account Receivable
70.538
13%
88.638
Inventories
39.033
5%
34.092
Prepaid Expenses
9.339
No change
9.339
Other Current Assets
27.076
6%
40.910
Total Current Assets
153.138
186.615
Net Plant, Property & Equipments
81.648
15%
102.275
Intangibles
9.415
No change
9.415
Other Assets
24.642
5%
34.092
TOTAL ASSETS
268.843
332.397
LIABILITIES
Accounts Payable
36.951
6%
40.910
Accrued Expenses
31.206
5%
34.092
Other Current Liabilities
3.663
No change
3.663
Total Current Liabilities
71.820
78.664
Long Term debt
157.720
Was initially constant
194.816
Accrued wages
21.418
3%
20.455
Total Liabilities
250.958
293.935
EQUITY
Common Stock
1.702
No change
1.702
Capital Surplus
55.513
No change
55.513
Retained Earnings
118.729
No dividends paid so all income is retained
139.305
Less: Treasury Stock
158.059
No change
158.059
TOTAL EQUITY
17.885
38.461
TOTAL LIABILITIES & EQUITY
268.843
332.397
Comparing long-term debt in parts (b) and (d), the loan need rises to $8.453 ($194.816 - $186.363) as sales rise to 17%.
Solution for part (e):
Aquatic Supplies Co.
Income Statement (in $ millions)
2017
Assumptions
Scenario analysis 2018
Sales (a)
$582.762
12%
$699.314
Cost of goods sold (b)
$240.828
39%
$265.739
Gross profit (c = a – b)
$341.934
$433.575
Selling, general & administrative expenses (d)
$257.507
49%
$342.664
Operating income before depreciation (e = c – d)
$84.427
$90.911
Depreciation, depletion & amortization (f)
$25.221
30%
$31.469
Operating profit (g = e – f)
$59.206
$59.442
Interest Expense (h)
$16.430
Was initially constant
$15.763
Pre-tax income (i = g – h)
$42.776
$43.679
Total income taxes (j)
$14.971
35%
$15.288
Net income (k = i – j)
$27.805
$28.391
Aquatic Supplies Co.
Balance Sheet (in $ millions)
2017
Assumptions
Scenario analysis 2018
ASSETS
(in $)
Cash & Equivalents
7.152
2%
13.986
Account Receivable
70.538
13%
69.931
Inventories
39.033
5%
20.979
Prepaid Expenses
9.339
No change
9.339
Other Current Assets
27.076
6%
41.959
Total Current Assets
153.138
156.195
Net Plant, Property & Equipments
81.648
15%
104.897
Intangibles
9.415
No change
9.415
Other Assets
24.642
5%
34.966
TOTAL ASSETS
268.843
305.473
LIABILITIES
Accounts Payable
36.951
6%
41.959
Accrued Expenses
31.206
5%
34.966
Other Current Liabilities
3.663
No change
3.663
Total Current Liabilities
71.820
80.588
Long Term debt
157.720
Was initially constant
157.630
Accrued wages
21.418
3%
20.979
Total Liabilities
250.958
259.197
EQUITY
Common Stock
1.702
No change
1.702
Capital Surplus
55.513
No change
55.513
Retained Earnings
118.729
No dividends paid so all income is retained
147.120
Less: Treasury Stock
158.059
No change
158.059
TOTAL EQUITY
17.885
46.276
TOTAL LIABILITIES & EQUITY
268.843
305.473
Under this scenario the loan need falls to $28.733 million ($186.363 million - $157.630 million).
Solution for part (f):
As shown in the calculations, cash and equivalents in 2022 equals $20.541 million and long-term debts equals $184.462 million.
Solution for part (g):
In this scenario, the indicated worksheet reveals that cash and equivalents will be $18.771 million and long-term debt will be $256.300 million, a substantial increase over the “base case” debt observed in part (f).
2017
Assumptions
Pro-forma 2018 ignoring inter-dependencies
Sales (a)
$582.762
12%
$652.693
Cost of goods sold (b)
$240.828
39%
$254.550
Gross profit (c = a – b)
$341.934
$398.143
Selling, general & administrative expenses (d)
$257.507
49%
$319.820
Operating income before depreciation (e = c – d)
$84.427
$78.323
Depreciation, depletion & amortization (f)
$25.221
30%
$29.371
Operating profit (g = e – f)
$59.206
$48.952
Interest Expense (h)
$16.430
Initially constant
$16.430
Pre-tax income (i = g – h)
$42.776
$35.522
Total income taxes (j)
$14.971
35%
$11.383
Net income (k = i – j)
$27.805
$21.139
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.