Due Date: Sunday by 11:59 p.m. EST of Unit 3 Total Polnts: 100 Instructions: Using the Excel Financial Forecast worksheet from Assignment #1, determine the 'sensitivity of the model by adjusting the values of growth rate in sales and cost of goods soldet sales. Increase and decreases the % values for 2016 and observe and record the results. Create an Excel chart for each (growth rate in sales and cost of goods soldet sales) and explain the resulting impact on external funding required. o For example: Change growth in sales from 70% to 95% by 5% increments, recording, and charting the resulting change in external funding required. Chart this data and provide justification. . Explain the results of your analysis of the growth rate in sales and cost of goods soldet sales. o Include the two charts (properly labeled) in your summary Explain how these results may affect the external funding the client is looking for. Requirements: . . All questions posed must be addressed completely. Length: two-page Executive Summary Include the two charts. All sources used must be properly cited in APA format. . Be sure to read the criteria, by which your assignment will be evaluated, before you write, and again after you write. Evaluation Rubric for Sensitivity of Financial Data Assignment Deficient (0-12 Points) Chart and evaluation are Inadequate or missing. CRITERIA Exemplary Proficient (18-20 Points) (13-17 Points) Growth Rate of Sales Chart and evaluation Chart and evaluation Chart are clearly presented, are presented, including all key though key details details. may not be clearly or completely presented Cost of Goods Chart and evaluation Chart and evaluation Sold/Net Sales are clearly presented, are presented, including all key though key details details may not be clearly or completely presented. Results Analysis Analysis fully Analysis mostly addresses the results addresses the results of the growth rate in of the growth rate in sales and cost of sales and cost of goods soldet sales. goods soldet sales. Chart and evaluation are inadequate or is missing Analysis is inadequate or missing. Impact Analysis Analysis fully Analysis mostly addresses how the addresses how the results may affect the results may affect the external funding external funding. Analysis is inadequate or missing. Length (9-10 Points) 2 pages (7 - 8 Points) 1 page Clear and Professional Writing and APA Format Writing and format are few errors that do clear, professional, not impede APA compliant, and professional error-free. presentation. (0 - 6 Points) Less than 1 page or more than 2 pages Errors impede professional presentation; guidelines not followed. 1 Use this data to calculate 2016 based on 2015 actuals. Note: you will need to modify: Long-term Debt, Current long-term debt and Net Fixed Assets based on your company's data. Locate this information for your company 20 2017 2015 $ 20,613 Facts and Assumptions Year Net sales Growth rate in sales Cost of goods soldet sales Gen., sell, and admin. expenseset sales Long-term debt Current portion long-term debt Interest rate Tax rate Dividend/earnings after tax Current assetset sales Net fixed assets Current liabilitieset sales lOwners' equity $ $ 760 $ 100 $ 35% 86% 12% 660 $ 100 $ 10% 45% 50% 29% 280 $ 14.5% 30% 86% 11% 560 Decrease - 10% 100 Stays Constant 10% 45% 50% 29% 270 Decrease -5% 14.4% $ 1.730 B B D E IL F G H 2015 2017 Complete the formulas to calculate 2017 estimates) 22 INCOME STATEMENT 23 Year 24 Net sales 25 Cost of good sold 26 Gross profit 27 Gen., sell, and admin. exp. 28 Interest expense 29 Earnings before tax 30 Tax 31 Earnings after tax 32 Dividends paid 33 Additions to retained earnings 34 BALANCE SHEET 35 Current assets 36 Net fixed assets 37 Total assets 38 Current liabilities 39 Long-term debt 40 Equity 41 Total liabilities and shareholders' equity 42 2016 $ 27,828 23,932 3,896 3,339 76 481 216 264 132 132 $ 8,070 280 8,350 4,035 660 1,862 6,557 12 LEYTEDNAL CUINNINC DEOLIDEN 1 702 Due Date: Sunday by 11:59 p.m. EST of Unit 3 Total Polnts: 100 Instructions: Using the Excel Financial Forecast worksheet from Assignment #1, determine the 'sensitivity of the model by adjusting the values of growth rate in sales and cost of goods soldet sales. Increase and decreases the % values for 2016 and observe and record the results. Create an Excel chart for each (growth rate in sales and cost of goods soldet sales) and explain the resulting impact on external funding required. o For example: Change growth in sales from 70% to 95% by 5% increments, recording, and charting the resulting change in external funding required. Chart this data and provide justification. . Explain the results of your analysis of the growth rate in sales and cost of goods soldet sales. o Include the two charts (properly labeled) in your summary Explain how these results may affect the external funding the client is looking for. Requirements: . . All questions posed must be addressed completely. Length: two-page Executive Summary Include the two charts. All sources used must be properly cited in APA format. . Be sure to read the criteria, by which your assignment will be evaluated, before you write, and again after you write. Evaluation Rubric for Sensitivity of Financial Data Assignment Deficient (0-12 Points) Chart and evaluation are Inadequate or missing. CRITERIA Exemplary Proficient (18-20 Points) (13-17 Points) Growth Rate of Sales Chart and evaluation Chart and evaluation Chart are clearly presented, are presented, including all key though key details details. may not be clearly or completely presented Cost of Goods Chart and evaluation Chart and evaluation Sold/Net Sales are clearly presented, are presented, including all key though key details details may not be clearly or completely presented. Results Analysis Analysis fully Analysis mostly addresses the results addresses the results of the growth rate in of the growth rate in sales and cost of sales and cost of goods soldet sales. goods soldet sales. Chart and evaluation are inadequate or is missing Analysis is inadequate or missing. Impact Analysis Analysis fully Analysis mostly addresses how the addresses how the results may affect the results may affect the external funding external funding. Analysis is inadequate or missing. Length (9-10 Points) 2 pages (7 - 8 Points) 1 page Clear and Professional Writing and APA Format Writing and format are few errors that do clear, professional, not impede APA compliant, and professional error-free. presentation. (0 - 6 Points) Less than 1 page or more than 2 pages Errors impede professional presentation; guidelines not followed. 1 Use this data to calculate 2016 based on 2015 actuals. Note: you will need to modify: Long-term Debt, Current long-term debt and Net Fixed Assets based on your company's data. Locate this information for your company 20 2017 2015 $ 20,613 Facts and Assumptions Year Net sales Growth rate in sales Cost of goods soldet sales Gen., sell, and admin. expenseset sales Long-term debt Current portion long-term debt Interest rate Tax rate Dividend/earnings after tax Current assetset sales Net fixed assets Current liabilitieset sales lOwners' equity $ $ 760 $ 100 $ 35% 86% 12% 660 $ 100 $ 10% 45% 50% 29% 280 $ 14.5% 30% 86% 11% 560 Decrease - 10% 100 Stays Constant 10% 45% 50% 29% 270 Decrease -5% 14.4% $ 1.730 B B D E IL F G H 2015 2017 Complete the formulas to calculate 2017 estimates) 22 INCOME STATEMENT 23 Year 24 Net sales 25 Cost of good sold 26 Gross profit 27 Gen., sell, and admin. exp. 28 Interest expense 29 Earnings before tax 30 Tax 31 Earnings after tax 32 Dividends paid 33 Additions to retained earnings 34 BALANCE SHEET 35 Current assets 36 Net fixed assets 37 Total assets 38 Current liabilities 39 Long-term debt 40 Equity 41 Total liabilities and shareholders' equity 42 2016 $ 27,828 23,932 3,896 3,339 76 481 216 264 132 132 $ 8,070 280 8,350 4,035 660 1,862 6,557 12 LEYTEDNAL CUINNINC DEOLIDEN 1 702