Please show in excel with formulas. Thank you!

Fashion Trends, Inc. Income Statement For the Period Ended Dec. 31, 2017 2017 2016 Sales 6,148,000 5,134,000 Cost of Goods Sold 4,176,000 3,422,000 Gross Profit 1,972,000 1,712,000 S,G&A Expenses 588,000 590,000 Fixed Expenses 70,000 70,000 Depreciation Expense 478,000 446,000 EBIT 836,000 606,000 Interest Expense 186,000 182,000 Earnings Before Taxes 650,000 424,000 195,000 127,200 Net Income 455,000 296,800 Fashion Trends, Inc. Balance Sheet As of Dec. 31, 2017 Assets 2017 Cash and Equivalents 862,000 Accounts Receivable 1,006,000 Inventory 578,000 Total Current Assets 2,446,000 Plant & Equipment 9,338,000 Accumulated Depreciation 4.590.000 Net Fixed Assets 4,748,000 Total Assets 7,194,000 Liabilities and Owners' Equity Accounts Payable 764,000 Short-term Notes Payable 158,000 Accrued Expenses 318,000 Total Current Liabilities 1,240,000 Long-term Debt 2,046,000 Total Liabilities 3,286,000 Common Stock 1.638.000 Retained Earnings 2,270,000 Total Shareholder's Equity 3,908,000 Total Liabilities and Owners' Equity 7,194,000 2016 678,000 730,000 600,000 2,008,000 8,644,000 4,112,000 4,532,000 6,540,000 Taxes 540,000 198,000 228,000 966,000 1,934.000 2,900,000 1,616,000 2,024,000 3,640.000 6,540,000 You can download Exercise 4.1 template for this exercise and start working on the problem. The firm has forecasted sales of $7,100,000 and a tax rate of 40% for 2018. Cost of goods sold and S.G&A expense in 2018 are expected to be the average of their two-year proportion of sales. On the balance sheet, accounts receivable, inventory, accounts payable, and accrued expenses are expected to be at the two-year average of the proportion of these items in relation to sales. The firm has planned an investment of $500,000 in fixed assets in 2018, with an estimated life of 10 years and no salvage value. These fixed assets will be depreciated using the straight line depreciation method. All other financial statement items are expected to remain constant in 2018. Assume the firm pays 4% interest on short-term debt and 7% on long term debt. Assume that the dividends in 2018 will be the same as those paid in 2017. 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: 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. What is the DFN under each scenario? Fashion Trends, Inc. Income Statement For the Period Ended Dec. 31, 2017 2017 2016 Sales 6,148,000 5,134,000 Cost of Goods Sold 4,176,000 3,422,000 Gross Profit 1,972,000 1,712,000 S,G&A Expenses 588,000 590,000 Fixed Expenses 70,000 70,000 Depreciation Expense 478,000 446,000 EBIT 836,000 606,000 Interest Expense 186,000 182,000 Earnings Before Taxes 650,000 424,000 195,000 127,200 Net Income 455,000 296,800 Fashion Trends, Inc. Balance Sheet As of Dec. 31, 2017 Assets 2017 Cash and Equivalents 862,000 Accounts Receivable 1,006,000 Inventory 578,000 Total Current Assets 2,446,000 Plant & Equipment 9,338,000 Accumulated Depreciation 4.590.000 Net Fixed Assets 4,748,000 Total Assets 7,194,000 Liabilities and Owners' Equity Accounts Payable 764,000 Short-term Notes Payable 158,000 Accrued Expenses 318,000 Total Current Liabilities 1,240,000 Long-term Debt 2,046,000 Total Liabilities 3,286,000 Common Stock 1.638.000 Retained Earnings 2,270,000 Total Shareholder's Equity 3,908,000 Total Liabilities and Owners' Equity 7,194,000 2016 678,000 730,000 600,000 2,008,000 8,644,000 4,112,000 4,532,000 6,540,000 Taxes 540,000 198,000 228,000 966,000 1,934.000 2,900,000 1,616,000 2,024,000 3,640.000 6,540,000 You can download Exercise 4.1 template for this exercise and start working on the problem. The firm has forecasted sales of $7,100,000 and a tax rate of 40% for 2018. Cost of goods sold and S.G&A expense in 2018 are expected to be the average of their two-year proportion of sales. On the balance sheet, accounts receivable, inventory, accounts payable, and accrued expenses are expected to be at the two-year average of the proportion of these items in relation to sales. The firm has planned an investment of $500,000 in fixed assets in 2018, with an estimated life of 10 years and no salvage value. These fixed assets will be depreciated using the straight line depreciation method. All other financial statement items are expected to remain constant in 2018. Assume the firm pays 4% interest on short-term debt and 7% on long term debt. Assume that the dividends in 2018 will be the same as those paid in 2017. 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: 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. What is the DFN under each scenario