The data and template for Questions 8 are located in two tabs: "QB-IS" and "QB-BS". Do the Financial Forecast of Income Statement and Balance Sheet for 2018. The assumptions are as follows: The firm has forecasted sales of $4,500,000 and a tax rate of 40% for 2018. Cost of goods sold, SG&A expense, and other expenses in 2018 are expected to be the average of their two-year proportion of sales. Depreciation in 2018 is assumed to the same as in 2017. On the balance sheet, accounts receivable, Inventory, accounts payable, and other current liabilities are expected to be at the two-year average of the proportion of these items in relation to sales. All other financial statement items are expected to remain constant in 2018 (i.e., depreciation, cash, plant and equipment, short-term notes payable, and common stock). Assume the firm pays 3% interest on short-term debt and 8% on long term debt. Assume that the dividend per share in 2018 is $0.45 per share, and the total number of shares outstanding is 100,000. a) What is the Discretionary Financing Needed (DFN) in 2018? Is this a surplus or deficit? b) DFN will be absorbed by long-term debt. Set up a forecasted Income Statement and Balance Sheet using long-term debt as plug item. c) Use the Scenario Manager to set up three scenarios for DFN: 1. Best Case- Sales are 15% higher than expected. 2. Base Case-Sales are exactly as expected. 3. Worst Case-Sales are 15 % less than expected. Cristal Clear, Inc. Income Statement For the Year Ended Dec. 31, 2017 2018* 1 2 3 4 5 Sales 6 Cost of Goods Sold 7 Gross Profit 8 G&A Expenses 9 Other Expenses 10 Depreciation 11 EBIT 12 Interest Expense 13 Earnings Before Taxes 14 Taxes 15 Net Income 16 17 Notes: 18 Tax Rate 19 Short-term Interest Rate 20 Long-term Interest Rate 21 22 2018 Sales Forecast 23 Sales Multiplier for Scenarios 24 DFN for Scenarios 25 40% 3% 8% 4,500,000 2017 2016 4,330,000 3,240,000 3,005,089 2,466,000 1,324,911 774,000 988,411 207,000 115,000 371,000 86,429 20,000 135,071 176,000 73,833 54,000 61,238 122,000 24,495 48,800 36,743 73,200 The data and template for Questions 8 are located in two tabs: "QB-IS" and "QB-BS". Do the Financial Forecast of Income Statement and Balance Sheet for 2018. The assumptions are as follows: The firm has forecasted sales of $4,500,000 and a tax rate of 40% for 2018. Cost of goods sold, SG&A expense, and other expenses in 2018 are expected to be the average of their two-year proportion of sales. Depreciation in 2018 is assumed to the same as in 2017. On the balance sheet, accounts receivable, Inventory, accounts payable, and other current liabilities are expected to be at the two-year average of the proportion of these items in relation to sales. All other financial statement items are expected to remain constant in 2018 (i.e., depreciation, cash, plant and equipment, short-term notes payable, and common stock). Assume the firm pays 3% interest on short-term debt and 8% on long term debt. Assume that the dividend per share in 2018 is $0.45 per share, and the total number of shares outstanding is 100,000. a) What is the Discretionary Financing Needed (DFN) in 2018? Is this a surplus or deficit? b) DFN will be absorbed by long-term debt. Set up a forecasted Income Statement and Balance Sheet using long-term debt as plug item. c) Use the Scenario Manager to set up three scenarios for DFN: 1. Best Case- Sales are 15% higher than expected. 2. Base Case-Sales are exactly as expected. 3. Worst Case-Sales are 15 % less than expected. Cristal Clear, Inc. Income Statement For the Year Ended Dec. 31, 2017 2018* 1 2 3 4 5 Sales 6 Cost of Goods Sold 7 Gross Profit 8 G&A Expenses 9 Other Expenses 10 Depreciation 11 EBIT 12 Interest Expense 13 Earnings Before Taxes 14 Taxes 15 Net Income 16 17 Notes: 18 Tax Rate 19 Short-term Interest Rate 20 Long-term Interest Rate 21 22 2018 Sales Forecast 23 Sales Multiplier for Scenarios 24 DFN for Scenarios 25 40% 3% 8% 4,500,000 2017 2016 4,330,000 3,240,000 3,005,089 2,466,000 1,324,911 774,000 988,411 207,000 115,000 371,000 86,429 20,000 135,071 176,000 73,833 54,000 61,238 122,000 24,495 48,800 36,743 73,200