PLEASE INCLUDE EXCEL FORMULAS
Problem #2: 30 points Fashion Trends Inc., a regional fashion apparel retailer, wants to prepare a 2018 Pro Forma Income Statement and a 2018 Pro Forma Balance Sheet using the following 2017 and 2016 data: Fixed Expenses 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 Sheet Balance Sheet For the Period Ended Dec 31, 2017 As of Dec. 31, 2017 2017 2016 Assets 2017 2016 Sales 6,148,000 5,134,000 Cash and Equivalents 862,000 678,000 Cost of Goods Sold 4,176,000 3,422,000 Accounts Receivable 1,006,000 730,000 Gross Profit 1,972,000 1,712,000 Inventory 578,000 600,000 S.G&A Expenses 588,000 590,000 Total Current Assets 2,446,000 2,008,000 70,000 70,000 Plant & Equipment 9,338,000 8,644,000 Depreciation Expense 478,000 446,000 Accumulated Depreciation 4,590,000 4,112,000 EBIT 836,000 606,000 Ner Fixed Assets 4,748,000 4,532,000 186,000 182.000 Total Assets 7,194,000 6,540,000 Earnings Before Taxes . 650.000 424,000 Liabilities and Owners' Equity Taxes aces 195,000 127,200 Accounts Payable 764,000 540,000 Net Income 455,000 296,800 Short-term Notes Payable 158,000 198,000 Accrued Expenses 318,000 228,000 Total Current Liabilities 1,240,000 966,000 Long-term Debt 2,046,000 1,934,000 Total Liabilities 3,286,000 2,900,000 Common Stock 1,638,000 1,616,000 Retained Earnings 2,270,000 2,024,000 Total Shareholder's Equity 3,908,000 3,640.000 Total Liabilities and Owners' Equity 7,194,000 6,540.000 Interest Expense The firm has 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 $900,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. You are to take a full year's depreciation on the new asset in 2018. 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 $140,000. A. Forecast the 2018 sales based on the historical sales figures below. Make sure to include the table with Trend functions in Excel and also create a graphical forecast with a trend line. The graphic should be embedded in Tab 1 of your worksheet. Note: This should be the first tab on your Problem 2 worksheet. Sales data: Year Sales 2013 $2,500,000 2014 $3,175,000 2015 $4,200,000 2016 $5,500,000 2017 $6,148,000 B. What is the DFN for 2018? Note: The second tab on your Problem 2 worksheet should be the Proforma Income Statement and the third tab should be the Pro Forma Balance Sheet
