This problem asks you to prepare one-and five-year financial forecasts and conduct some sensitivity analysis and scenario

Question:

This problem asks you to prepare one-and five-year financial forecasts and conduct some sensitivity analysis and scenario analysis for Aquatic Supplies Company. A spreadsheet containing the company's 2017 financial statements and management's projections is available for download from McGraw-Hill's Connect or your course instructor (see the Preface for more information). Use this information to answer the questions posed in the spreadsheet.

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.  

AQUATIC SUPPLIES CO.
INCOME STATEMENT ($ millions)

2017Assumptions




Sales$582.76212%growth in sales
Cost of Goods Sold240.82839%percentage of sales
Gross Profit341.934

Selling, General, & Administrative Expense257.50749%percentage of sales
Operating Income84.427

Depreciation & Amortization25.22130%percentage of net PP&E
Operating Profit59.206

Interest Expense16.43010%percentage of long-term debt
Pretax Income42.776

Total Income Taxes14.97135%percentage of pretax income
Net income$27.805





BALANCE SHEET ($ millions)
ASSETS


Cash & Equivalents$7.1522%percentage of sales
Account Receivable70.53813%percentage of sales
Inventories39.0335%percentage of sales
Prepaid Expenses9.339no change
Other Current Assets27.0766%percentage of sales
Total Current Assets153.138

Net Property, Plant, & Equipment81.64815%percentage of sales
Intangible Assets9.415no change
Other Assets24.6425%percentage of sales
TOTAL ASSETS$268.843





LIABILITIES


Accounts Payable$36.9516%percentage of sales
Accrued Expenses31.2065%percentage of sales
Other Current Liabilities3.663no change
Total Current Liabilities71.820

Long Term Debt157.720initially constant
Accrued Wages21.4183%percentage of sales
Total Liabilities250.958





EQUITY


Common Stock1.702no change
Capital Surplus55.513no change
Retained Earnings118.729no dividends
Less: Treasury Stock158.059no change
Total Equity17.885

TOTAL LIABILITIES & EQUITY$268.843

External funding required



a. 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?   

b. Modify your forecast in part (a) assuming that long-term debt and interest expense increase in order to make up the external funding required for 2018. (Be sure to enable interative calculation in Excel.) How much additional long-term debt (compared to 2017) will be required under this assumption?

c. Why are your answers to part (a) and part (b) 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 total long-term debt would be required?  

e. 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?

f. 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?

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 is your projected value for long-term debt in 2022?

Intangible Assets
An intangible asset is a resource controlled by an entity without physical substance. Unlike other assets, an intangible asset has no physical existence and you cannot touch it.Types of Intangible Assets and ExamplesSome examples are patented...
Financial Statements
Financial statements are the standardized formats to present the financial information related to a business or an organization for its users. Financial statements contain the historical information as well as current period’s financial...
Accounts Receivable
Accounts receivables are debts owed to your company, usually from sales on credit. Accounts receivable is business asset, the sum of the money owed to you by customers who haven’t paid.The standard procedure in business-to-business sales is that...
Fantastic news! We've Found the answer you've been seeking!

Step by Step Answer:

Related Book For  book-img-for-question
Question Posted: