CHAPTER $5$: FINANCIAL STATEMENT FORECASTING$1.$Fashion Trends, Inc., a regional fashion apparel retailer, wants to prepare a $2018$ Pro Forma Income Statement and a $2018$ Balance Sheet using the following $2017$ and $2016$ data:Fashion Trends, Inc.Fashion Trends, Inc.Balance SheetBalance SheetFor the Period Ended Dec. $31,2017$As of Dec. $31,201720172016$Assets$20172016$Sales$6,148,0005,134,000$Cash and Equivalents$862,000678,000$ Cost of Goods Sold$4,176,0003,422,000$Accounts Receivable$1,006,000730,000$ Gross Profit$1,972,0001,712,000$Inventory$578,000600,000$ S$,$G&A Expenses$588,000590,000$Total Current Assets $2,446,0002,008,000$ Fixed Expenses$70,00070,000$Plant & Equipment$9,338,0008,644,000$Depreciation Expense$478,000446,000$Accumulated Depreciation$4,590,0004,112,000$EBIT$836,000606,000$Net Fixed Assets $4,748,0004,532,000$ Interest Expense$186,000182,000$Total Assets $7,194,0006,540,000$ Earnings Before Taxes$650,000424,000$Liabilities and Owners' EquityTaxes$195,000127,200$Accounts Payable$764,000540,000$Net Income$455,000296,800$Short$-$term Notes Payable$158,000198,000$Accrued Expenses$318,000228,000$Total Current Liabilities $1,240,000966,000$ Long$-$term Debt$2,046,0001,934,000$Total Liabilities $3,286,0002,900,000$ Common Stock$1,638,0001,616,000$Retained Earnings$2,270,0002,024,000$ Total Shareholder's Equity $3,908,0003,640,000$ Total Liabilities and Owners' Equity $7,194,0006,540,000$ 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 an iterative worksheet to eliminate it$.$c$)$Turn off iteration, and use the Scenario Manager to set up three scenarios:$1.$Best Case Sales are $15\%$ higher than expected$\mathrm{.}2.$Base Case Sales are exactly as expected$\mathrm{.}3.$Worst Case Sales are $15\%$ less than expected.What is the DFN under each scenario?