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

Fixed Expenses

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

Net Fixed Assets

4,748,000

4,532,000

Interest Expense

186,000

182,000

Total Assets

7,194,000

6,540,000

Earnings Before Taxes

650,000

424,000

Liabilities and Owners' Equity

Taxes

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

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.

2.Base Case Sales are exactly as expected.

3.Worst Case Sales are 15% less than expected.

What is the DFN under each scenario?