80.2K

Verified Solution

Link Copied!

Using the Excel Financial Forecast worksheet, determine the 'sensitivity' of the model by adjusting the values of growth rate in sales and cost of goods sold in column D. Increase and decreases the % values for 2017 and observe and record the results. Create an Excel chart for each (growth rate in sales and cost of goods sold) the resulting impact on external funding required. Explain your observations on: How did adjusting growth rate of sale and cost of good sold affect the income statement and balance sheet? What is External Funding Required and how was it affected by adjusting sales projections and cost of goods sold? O O 2017 50% 86% 11% 7 A Facts and Assumptions Year Net sales Growth rate in sales Cost of goods sold Gen., sell, and admin. Expenses Long-term debt Current portion long-term debt Interest rate Tax rate Dividend/earnings after tax Current assets Net fixed assets Current liabilities Owners' equity 2015 2016 $ 1,093 $ 1,476 35% 86% 12% $ 6.53 $ 7 $ $ $ 10% 45% 50% 29% $ 7.67 $ 8 $ 14.5% $ 3.58 10% 45% 50% 29% 8 14.4% 2015 2016 2017 1,476 $ 2,213 1,269 1,903 207 310 177 243 INCOME STATEMENT Year Net sales Cost of good sold Gross profit Gen., sell,, and admin. exp. Interest expense Earnings before tax Tax Earnings after tax Dividends paid Additions to retained earnings 1 21 29 45 13 20 16 o 100 l 25 12 12 BALANCE SHEET $ 428 $ Current assets Net fixed assets 642 8 8 Total assets 436 214 650 319 7 12 Current liabilities Long-term debt Equity Total liabilities and shareholders' equity 7 12 232 338 EXTERNAL FUNDING REQUIRED $ 204 $ 312 Using the Excel Financial Forecast worksheet, determine the 'sensitivity' of the model by adjusting the values of growth rate in sales and cost of goods sold in column D. Increase and decreases the % values for 2017 and observe and record the results. Create an Excel chart for each (growth rate in sales and cost of goods sold) the resulting impact on external funding required. Explain your observations on: How did adjusting growth rate of sale and cost of good sold affect the income statement and balance sheet? What is External Funding Required and how was it affected by adjusting sales projections and cost of goods sold? O O 2017 50% 86% 11% 7 A Facts and Assumptions Year Net sales Growth rate in sales Cost of goods sold Gen., sell, and admin. Expenses Long-term debt Current portion long-term debt Interest rate Tax rate Dividend/earnings after tax Current assets Net fixed assets Current liabilities Owners' equity 2015 2016 $ 1,093 $ 1,476 35% 86% 12% $ 6.53 $ 7 $ $ $ 10% 45% 50% 29% $ 7.67 $ 8 $ 14.5% $ 3.58 10% 45% 50% 29% 8 14.4% 2015 2016 2017 1,476 $ 2,213 1,269 1,903 207 310 177 243 INCOME STATEMENT Year Net sales Cost of good sold Gross profit Gen., sell,, and admin. exp. Interest expense Earnings before tax Tax Earnings after tax Dividends paid Additions to retained earnings 1 21 29 45 13 20 16 o 100 l 25 12 12 BALANCE SHEET $ 428 $ Current assets Net fixed assets 642 8 8 Total assets 436 214 650 319 7 12 Current liabilities Long-term debt Equity Total liabilities and shareholders' equity 7 12 232 338 EXTERNAL FUNDING REQUIRED $ 204 $ 312

Answer & Explanation Solved by verified expert

See Answer