Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Using the spreadsheet and the information presented, extend the forecast for this company through 2016 and SHOW ALL YOUR WORK. What is the company's projected

image text in transcribed

Using the spreadsheet and the information presented, extend the forecast for this company through 2016 and SHOW ALL YOUR WORK.

  1. What is the company's projected external financing required in 2016? How does this number compare to the 2015 projection?

  1. Perform a sensitivity analysis on this projection. How does the company's projected external financing required change if the ratio of cost of goods sold to net sales declines from 86 percent to 84 percent? If you link your assumptions in the pro forma to the cells containing the assumptions in the spreadsheet, it will be easy to change one number and see the resulting change.

  1. Perform a scenario analysis on this projection. How does the company's projected external financing required change if a severe recession occurs in 2016? Assume net sales decline 5 percent, cost of goods sold rises to 88 percent of net sales due to price cutting, and current assets increase to 35 percent of net sales as management fails to cut purchases promptly in response to declining sales.
2015 2016 2014 $ 20,613 25% 86% 12% 660 $ $ 760 $ 3 Facts and Assumptions 4 Year 5 Net sales 6 Growth rate in sales 7 Cost of goods soldet sales 8 Gen., sell, and admin. expenseset sales 9 Long-term debt 10 Current portion long-term debt 11 Interest rate 12 Tax rate 13 Dividend/earnings after tax 14 Current assetset sales 15 Net fixed assets 16 Current liabilitieset sales 17 Owners' equity $ 100 $ 100 $ 30% 86% 11% 560 100 10% 45% 50% 29% 10% 45% 50% 29% 280 $ 14.5% $ 270 14.4% $ 1,730 2014 2016 $ 18 INCOME STATEMENT 19 Year 20 Net sales 21 Cost of good sold 22 Gross profit 23 Gen., sell, and admin. exp. 24 Interest expense 25 Earnings before tax 26 Tax 27 Earnings after tax 28 Dividends paid 29 Additions to retained earnings 2015 25,766 22,159 3,607 3,092 231 285 128 156 78 78 $ 30 BALANCE SHEET 31 Current assets 32 Net fixed assets 33 Total assets 34 Current liabilities 35 Long-term debt 36 Equity 37 Total liabilities and shareholders' equity 38 39 EXTERNAL FUNDING REQUIRED 7,472 280 7,752 3,736 660 1,808 6,204 $ 1,548 2015 2016 2014 $ 20,613 25% 86% 12% 660 $ $ 760 $ 3 Facts and Assumptions 4 Year 5 Net sales 6 Growth rate in sales 7 Cost of goods soldet sales 8 Gen., sell, and admin. expenseset sales 9 Long-term debt 10 Current portion long-term debt 11 Interest rate 12 Tax rate 13 Dividend/earnings after tax 14 Current assetset sales 15 Net fixed assets 16 Current liabilitieset sales 17 Owners' equity $ 100 $ 100 $ 30% 86% 11% 560 100 10% 45% 50% 29% 10% 45% 50% 29% 280 $ 14.5% $ 270 14.4% $ 1,730 2014 2016 $ 18 INCOME STATEMENT 19 Year 20 Net sales 21 Cost of good sold 22 Gross profit 23 Gen., sell, and admin. exp. 24 Interest expense 25 Earnings before tax 26 Tax 27 Earnings after tax 28 Dividends paid 29 Additions to retained earnings 2015 25,766 22,159 3,607 3,092 231 285 128 156 78 78 $ 30 BALANCE SHEET 31 Current assets 32 Net fixed assets 33 Total assets 34 Current liabilities 35 Long-term debt 36 Equity 37 Total liabilities and shareholders' equity 38 39 EXTERNAL FUNDING REQUIRED 7,472 280 7,752 3,736 660 1,808 6,204 $ 1,548

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Accounting questions

Question

What is a prepayment?

Answered: 1 week ago