Answer the following questions: a. What is the Discretionary Financing Needed (DFN) in 2020? Is this a surplus or deficit? b. Assume that the DFN will be absorbed by Additional Notes Payable, if there is a deficit. If there is a DFN surplus, assume that Notes Payable will remain at the 2019 level and that the firm will keep excess funds as extra cash. Set up a worksheet to make the balance sheet balance. Check by changing investment to fixed assets whether balance sheet balances at various levels of the investment into fixed assets. c. Use the Scenario Manager to set up two scenarios for the expected level of investment in the repair equipment: 1) Cheaper technology can be obtained for $350,000, The Expected life is, however, only 4 years and the salvage value is expected zero. 2) Original estimate of $520,000 investment with the expected life of 10 years and the salvage value of zero. What is the DFN under each scenario? Why do you think the amount that needs to be borrowed increases or decreases? Create a Scenario Summary and provide written answer(s). Make sure to name the cells you use in the scenario summary d. Use the Scenario Manager to set up three scenarios for the expected level of sales and COGS as % of sales: 1) Best Case - Sales growth is 20% and COGS is only 72% of sales 2) Base Case - Sales growth is 9% and COGS is as projected under point 2 3) Worst Case - Sales growth is 0% and COGS is increased to 78% of sales. What is the DFN under each scenario? Why do you think the amount that needs to be borrowed increases or decreases? Create a Scenario Summary and provide written answer(s). Make sure to name the cells you use in the scenario summary Income Statement Particulars Year Year Year 2020 2019 2018 10511616 96501258245000 7867666 7125000 6271900 2635970.2 2525125 1973500 Cost of Goods Sold Gross Profit Selling & Expenses Fixed Expenses EBITDA 10246348 925000 $16000 320000 32000 320000 1291154 120125 7400 Depreciation Operating Income 172000 20000 10000 1119335.4 1160125 737500 interest Expenses Pre Tax Income 276000 17500 17500 R411541 981625 562500 253000:62 3337525191250 590234.79 647872.5371250 Net Income Notes: Tax Rate Share OS pps 110000 1.44 10000 90000 1. 2 LI Balanceshoot Assets Cash & Equivalents Accounts Receivables Inventory Current Assets 2020 2019 2018 296567.33 275000 200000 56600 560000 40000 1526000 1500000 1300000 23891873 2115000 2010000 PPE Ace Depe Net Plant & Equipment 660000 602000 DO 6120000 5200000 470000 310000 69000 490000 Total Assets 8427387 0000 60000 Liabilities & Equity Accounts Payable Notes Payable Accured Exp Current Liabilities 504125 225000 14100 870825 525000 200000 225000 1000 100 110000 90000 66000 Long term Debt Term Liabilities 2300000 20000 2000000 2300000 2300000 2300000 Co Stock 3599100 2750000 2250000 Add: Paid up Capital 0 899100 980000 Retained Eamins 1657462291220900 710000 Total Stockholders Equity 52565621 425000 2940000 Total Liabilities & Equily 8427387 8025000 6900000 Working Notes: 1 2019 2018 2020 Average COGS% to sales 74% 78% 75% S&D Ep .59% 9,90% 9.74% 2. Percentage to sales 2019 2018 Cash & Equivalents 2.5% 2.79% Accounts Receivables S. 497% inventory 2020 2.8296 19 Income Statement Particulars Year Year Year 2020 2019 2018 10$18636 96501258245000 7867666 7125000 6271900 2635970.2 2525125 1973500 Cost of Goods Sold Cross Profit Selling & Expenses Fixed Expenses EBITDA 10246348 925000 $16000 320000 32000 320000 12911154 120125 87500 Depreciation Operating Income 172000 20000 10000 1119335.4 1160125 737500 interest Expenses Pre Tax Income 276000 17500 17500 8411541 981625 562500 253000:62 333752.191250 59031479 647872.5371250 Net Income Notes: Tax Rate Share OS pps 110000 1.44 10000 90000 1. 2 LI Balanceshoot Assets Cash & Equivalents Accounts Receivables Inventory Current Assets 2020 2019 2018 296567.33 275000 200000 56680O S60000 40000 1526000 1500000 1300000 23891873 2115000 2010000 PPE Ace Deper Net Plant & Equipment 660000 6120000 5200000 602000 470000 310000 DOO S D 4890000 Total Assets 8427387 0000 60000 Liabilities & Equity Accounts Payable Notes Payable Accured Exp Curret Liabilities 504125 225000 14100 870825 525000 200000 225000 100 100 110000 90000 66000 Long em Debt Term Liabilities 2300000 20000 200000 2300000 2300000 2300000 Co Stock 3599100 2750000 2250000 Add: Paid up Capital 0 899100 980000 Retained Eamins 1657462-291220000 710000 Total Stockholders Equity 52565623 425000 290000 Total Liabilities & Equily 8427387 8025000 6900000 Working Notes: 1 2019 2018 2020 Average COGS% to sales74% 78% 75% S&D Ep 9 .59% 9,90% 9.74% 2. Percentage to sales 2019 2018 Cash & Equivalents 2.5% 2.79% Accounts Receivables 50% 47 In non 2020 2.8296 19% Answer the following questions: a. What is the Discretionary Financing Needed (DFN) in 2020? Is this a surplus or deficit? b. Assume that the DFN will be absorbed by Additional Notes Payable, if there is a deficit. If there is a DFN surplus, assume that Notes Payable will remain at the 2019 level and that the firm will keep excess funds as extra cash. Set up a worksheet to make the balance sheet balance. Check by changing investment to fixed assets whether balance sheet balances at various levels of the investment into fixed assets. c. Use the Scenario Manager to set up two scenarios for the expected level of investment in the repair equipment: 1) Cheaper technology can be obtained for $350,000, The Expected life is, however, only 4 years and the salvage value is expected zero. 2) Original estimate of $520,000 investment with the expected life of 10 years and the salvage value of zero. What is the DFN under each scenario? Why do you think the amount that needs to be borrowed increases or decreases? Create a Scenario Summary and provide written answer(s). Make sure to name the cells you use in the scenario summary d. Use the Scenario Manager to set up three scenarios for the expected level of sales and COGS as % of sales: 1) Best Case - Sales growth is 20% and COGS is only 72% of sales 2) Base Case - Sales growth is 9% and COGS is as projected under point 2 3) Worst Case - Sales growth is 0% and COGS is increased to 78% of sales. What is the DFN under each scenario? Why do you think the amount that needs to be borrowed increases or decreases? Create a Scenario Summary and provide written answer(s). Make sure to name the cells you use in the scenario summary Income Statement Particulars Year Year Year 2020 2019 2018 10511616 96501258245000 7867666 7125000 6271900 2635970.2 2525125 1973500 Cost of Goods Sold Gross Profit Selling & Expenses Fixed Expenses EBITDA 10246348 925000 $16000 320000 32000 320000 1291154 120125 7400 Depreciation Operating Income 172000 20000 10000 1119335.4 1160125 737500 interest Expenses Pre Tax Income 276000 17500 17500 R411541 981625 562500 253000:62 3337525191250 590234.79 647872.5371250 Net Income Notes: Tax Rate Share OS pps 110000 1.44 10000 90000 1. 2 LI Balanceshoot Assets Cash & Equivalents Accounts Receivables Inventory Current Assets 2020 2019 2018 296567.33 275000 200000 56600 560000 40000 1526000 1500000 1300000 23891873 2115000 2010000 PPE Ace Depe Net Plant & Equipment 660000 602000 DO 6120000 5200000 470000 310000 69000 490000 Total Assets 8427387 0000 60000 Liabilities & Equity Accounts Payable Notes Payable Accured Exp Current Liabilities 504125 225000 14100 870825 525000 200000 225000 1000 100 110000 90000 66000 Long term Debt Term Liabilities 2300000 20000 2000000 2300000 2300000 2300000 Co Stock 3599100 2750000 2250000 Add: Paid up Capital 0 899100 980000 Retained Eamins 1657462291220900 710000 Total Stockholders Equity 52565621 425000 2940000 Total Liabilities & Equily 8427387 8025000 6900000 Working Notes: 1 2019 2018 2020 Average COGS% to sales 74% 78% 75% S&D Ep .59% 9,90% 9.74% 2. Percentage to sales 2019 2018 Cash & Equivalents 2.5% 2.79% Accounts Receivables S. 497% inventory 2020 2.8296 19 Income Statement Particulars Year Year Year 2020 2019 2018 10$18636 96501258245000 7867666 7125000 6271900 2635970.2 2525125 1973500 Cost of Goods Sold Cross Profit Selling & Expenses Fixed Expenses EBITDA 10246348 925000 $16000 320000 32000 320000 12911154 120125 87500 Depreciation Operating Income 172000 20000 10000 1119335.4 1160125 737500 interest Expenses Pre Tax Income 276000 17500 17500 8411541 981625 562500 253000:62 333752.191250 59031479 647872.5371250 Net Income Notes: Tax Rate Share OS pps 110000 1.44 10000 90000 1. 2 LI Balanceshoot Assets Cash & Equivalents Accounts Receivables Inventory Current Assets 2020 2019 2018 296567.33 275000 200000 56680O S60000 40000 1526000 1500000 1300000 23891873 2115000 2010000 PPE Ace Deper Net Plant & Equipment 660000 6120000 5200000 602000 470000 310000 DOO S D 4890000 Total Assets 8427387 0000 60000 Liabilities & Equity Accounts Payable Notes Payable Accured Exp Curret Liabilities 504125 225000 14100 870825 525000 200000 225000 100 100 110000 90000 66000 Long em Debt Term Liabilities 2300000 20000 200000 2300000 2300000 2300000 Co Stock 3599100 2750000 2250000 Add: Paid up Capital 0 899100 980000 Retained Eamins 1657462-291220000 710000 Total Stockholders Equity 52565623 425000 290000 Total Liabilities & Equily 8427387 8025000 6900000 Working Notes: 1 2019 2018 2020 Average COGS% to sales74% 78% 75% S&D Ep 9 .59% 9,90% 9.74% 2. Percentage to sales 2019 2018 Cash & Equivalents 2.5% 2.79% Accounts Receivables 50% 47 In non 2020 2.8296 19%