4 5 Sales 1 Grass Profi Fashion Trends, Inc. Income Statement For the Periol Ended Dec 31, 2017 2017 2016 2016 6,140,000 3,134,000 4,176,000 3,422,000 average of two-your proportion of sales 1,712,000 1,972,000 588,000 590,000 average of two-year peoportion of sales 70,000 70,000 475,000 446,000 2018 depreciation exponer will be increased by addicional depreciation expense from the new invernment of Plant & Equipment 606,000 816,000 186,000 182,000 650,000 424,000 127,200 195,000 455,000 296,800 Assumptions The firm has forecasted sales of $7,100,000 and a tax rate of 60% for 2018. Cost of goods and 1,08A expense in 2018 are expected to be the average of their two year proportion of sales. On the balance sheet, accounts receivable, try aunts payable, and accrued expenses are expected to be at the two-year average of the proportion of these them relation to sales. The firm has planned an investment of $500,000 in feed as in 2018, with an estimated of 10 years and no salvage value These feed assets will be depreciated using redation method. All other fac statements are expected to remain constant in 2018. interest in short-term debt and 7 o pay 30% 3016 long term debt Assume that the vidends in 2018 will be the same as those paid in 2017. Cost of Goods Sold Selling and G&A Expenses Fixed Expenses 10 Depreciation Expense 11 ERIT 12 Imerest Expense 11 Earnings Before Taxes 14 Taxes 15 Net Income 38 17 Forecast 18 15 Nates 20 Tax Rate 21 22 Additional Depron 23 Sentb-term Interest Rate 24 Long-term lnterest Rate 25 40% 4% 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? 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. 4 5 Sales 1 Grass Profi Fashion Trends, Inc. Income Statement For the Periol Ended Dec 31, 2017 2017 2016 2016 6,140,000 3,134,000 4,176,000 3,422,000 average of two-your proportion of sales 1,712,000 1,972,000 588,000 590,000 average of two-year peoportion of sales 70,000 70,000 475,000 446,000 2018 depreciation exponer will be increased by addicional depreciation expense from the new invernment of Plant & Equipment 606,000 816,000 186,000 182,000 650,000 424,000 127,200 195,000 455,000 296,800 Assumptions The firm has forecasted sales of $7,100,000 and a tax rate of 60% for 2018. Cost of goods and 1,08A expense in 2018 are expected to be the average of their two year proportion of sales. On the balance sheet, accounts receivable, try aunts payable, and accrued expenses are expected to be at the two-year average of the proportion of these them relation to sales. The firm has planned an investment of $500,000 in feed as in 2018, with an estimated of 10 years and no salvage value These feed assets will be depreciated using redation method. All other fac statements are expected to remain constant in 2018. interest in short-term debt and 7 o pay 30% 3016 long term debt Assume that the vidends in 2018 will be the same as those paid in 2017. Cost of Goods Sold Selling and G&A Expenses Fixed Expenses 10 Depreciation Expense 11 ERIT 12 Imerest Expense 11 Earnings Before Taxes 14 Taxes 15 Net Income 38 17 Forecast 18 15 Nates 20 Tax Rate 21 22 Additional Depron 23 Sentb-term Interest Rate 24 Long-term lnterest Rate 25 40% 4% 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? 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