D 1 2 3 4 Axxery 3 Cash and Exquivalent Accom Receivable Inventory Total Current Ass Accumulated Depreciation Fashion Trends, Inc Balance Sheet As of Dec. 31, 2017 2018- 2017 62,000 1,006,000 $78,000 2,446,000 2916 671,000 730,000 average of two-year proportion of sele 600,000 age of two-yor proportion c 2,00,000 8644,000 2018 Plant & Equipment will be insed by new investment of 5500,000 4112,000 Accumulated depreciation 2017 accumulood deprciation deprecation exp2018 (25) 4.332.000 540,000 age of two-year proportion of sales 198,000 228,000 average of two your proportion of 966,000 Plant & Equipment 10 9,338,000 4,590,000 11 Net Fund Asset 4,745,000 12 Total Ass 7,194,000 6,500,000 13 Lisbon and Owners' Equity 14 Accounts Payable 364,000 15 Shon-mm Notes Payable 16 Accund Expenses 17 Total Current Liabilities 18 Long-term Debt 19 Total Lis 158,000 318,000 1,240,000 2,046,000 1,934,000 3.286,000 2900,000 20 21 Common Stock 1,638,000 1,616,000 Retained Earnings 22 Total Shareholder's Equity 23 Total Liebes and Owners' Equity 2,270,000 2,024,000 390.000 3,640,000 7,194,000 6.340,000 24 25 Forset 36 Discmary Financing Neodol 27 Toul Accumulated DFN 29 2017 Dividends 30 Net Addon to Plan and Equipment 31 Life of New Equipment in Years 32 Salvage Value of New Fiquipm 33 New Depciation (Swaight Line) 34 500,000 Assume but the dividends in 2018 will be the same as boer pil in 2017 The firm has forecasted sales of $7,100,000 and a tax rate of 40% for 2018. Cost of goods sold and SG&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? Hints: 1. Since the dividends in 2018 will be the same as those paid in 2017, you need to calculate the dividends paid in 2017 first from 2016 and 2017 IS and BS. 2. "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." By straight line depreciation method, the depreciation expense in 2018 (in Income Statement) will depreciation method, the depreciation expense in 2018 (in Income Statement) will be increased by $50,000 (-$500,000/10). In Balance Sheet, Plant and Equipment in 2018 will be increased by $500,000. The accumulated depreciation will be depreciation expense in 2018 (from IS) + accumulated depreciation in 2017