Sales Cristal Clear, Inc. Income Statement For the Year Ended Dec. 31, 2017 Cost of Goods Sold 2018* 2017 4,330,000 3,240,000 3,005,089 2,466,000 1,324,911 774,000 2016 988,411 207,000 Gross Profit G&A Expenses Other Expenses Depreciation EBIT 115,000 371,000 86,429 135,071 20,000 176,000 Interest Expense Earnings Before Taxes 73,833 54,000 61,238 122,000 Taxes Net Income 24,495 36,743 48,800 73,200 Notes: Tax Rate 40% Short-term Interest Rate 3% Long-term Interest Rate 8% 2018 Sales Forecast 4,500,000 The data and template for Questions 8 are located in two tabs: "Q8-IS" and "Q8-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 2. Base Case - - Sales are 15% higher than expected. Sales are exactly as expected. 3. Worst Case - Sales are 15% less than expected. Sales Multiplier for Scenarios DFN for Scenarios