Question

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.
income statement				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?

Answer 1

Fashion Trends Inc.
Proforma Income Statement
for the year ended Decembr 31, 2018
Sales	7100000
Cost of goods sold	4781581
Gross Profit	2318419
S G & A Expenses	741340
Fixed Expenses	70000
Depreciation Expense	528000
EBIT	979079
Interest Expense	170000
Earnings Before Taxes	809079
Income Tax (40%)	323631
Net Income	485447
Workings:
Depreciation Expense
New investment	500000
SalvageValue	0
Depreciable cost	500000
Estimated life	10 years
Annual depreciation	50000
Depreciation expense in 2017	478000
Depreciation expese for 2018	528000
Interest Expense
Short term debt	158000
Rate of interest	4%
Interest expense on ST. Debt (1 )	6320
Long term debt	2046000
Rate of interest	8%
Interest expense on LT. Debt (2)	163680
Total Interest Expense (1) + (2)	170000

	2017	2016	Total
			Amount	%
Sales	6148000	5134000	11282000
Cost of goods sold	4176000	3422000	7598000	67%
S G & A Expenses	588000	590000	1178000	10%

2.

Fashion Trends Inc.
Proforma Balance Sheet
As at December 31, 2018
Assets
Cash and Equivalents	862000
Accounts Receivable	1092501
Inventory	741340
Total Current Assets	2695842
Plant & Equipment	9838000
Accumulated Depreciation	5118000
Net Fixed Assets	4720000
Total Assets	7415842
Loabilities and Owners' Equity
Accounts Payable	820635
Short Term Notes Payable	158000
Accrued Expenses	343609
Total Current Liabilities	1322244
Long-Term Liabilities	2046000
Total Debt	3368244
Common Stock	1638000
Retained Earnings	2546447
Total Shareholders' Equity	4184447
Total liabilities and owners' equity	7552691
Discretionary finance needed	136849
Retained Earnings
Balance, January 1, 2017	2024000
Net income	455000
Total	2479000
Balance, December 31, 2017	2270000
Dividends Paid - 2017	209000
Balance, January 1, 2018	2270000
Net income	485447
Total	2755447
Dividends Paid - 2018	209000
Balance, December 31, 2018	2546447

	2017	2016	Total
			Amount	%
Sales	6148000	5134000	11282000
Accounts Receivable	1006000	730000	1736000	15%
Inventory	578000	600000	1178000	10%
Accounts Payable	764000	540000	1304000	12%
Accrued Expenses	318000	228000	546000	5%

1. Best Case : DFN $178,347

2. Base Case : DFN $136,849

3. Base Case : DFN $ 95,352