70.2K

Verified Solution

Link Copied!

Fashion Trends, Inc. a regional fashion apparel retailer, wants to prepare a 2012 Pro Forma Income Statement and a 2012 Balance Sheet using the following 2011 and 2010 data:

Fashion Trends Inc.
Pro Forma Income Statement
As of Dec. 2011 and 2010
	2012*	2011	2010
Sales	$ 7,100,000.00	$ 6,148,000.00	$ 5,134,000.00
Cost of Goods Sold	$ 5,016,402.76	$ 4,176,000.00	$ 3,422,000.00
Gross Profit	$ 2,083,597.24	$ 1,972,000.00	$ 1,712,000.00
Selling and G&A Expenses	$ 710,116.97	$ 588,000.00	$ 590,000.00
Fixed Expenses	$ 70,000.00	$ 70,000.00	$ 70,000.00
Depreciation Expenses	$ 528,000.00	$ 478,000.00	$ 446,000.00
EBIT	$ 775,480.27	$ 836,000.00	$ 606,000.00
Interest Expense	$ 195,200.00	$ 186,000.00	$ 182,000.00
Earnings Before Taxes	$ 580,280.27	$ 650,000.00	$ 424,000.00
Taxes	$ 232,112.11	$ 188,000.00	$ 128,000.00
Net Income	$ 348,168.16	$ 462,000.00	$ 296,000.00
*Forecast
Notes
Tax Rate	40%
Additional Depreciation	50000
Short term interest rate	7%
Long term interest rate	9%

Fashion Trends Inc.
Pro Forma Balance Sheet
As of Dec. 31 2011 and 2010
Assets	2012*	2011	2010
Cash and Equivalents	$ 862,000.00	$ 862,000.00	$ 678,000.00
Accounts Receivable	$ 924,519.19	$ 1,006,000.00	$ 730,000.00
Inventory	$ 635,454.13	$ 578,000.00	$ 600,000.00
Total Current Assets	$ 2,421,973.32	$ 2,446,000.00	$ 2,008,000.00
Plant & Equipment	$ 9,838,000.00	$ 9,338,000.00	$ 8,644,000.00
Accumulated Depreciation	$ 5,118,000.00	$ 4,590,000.00	$ 4,112,000.00
Net Fixed Assets	$ 4,720,000.00	$ 4,748,000.00	$ 4,532,000.00
Total Assets	$ 7,141,973.32	$ 7,194,000.00	$ 6,540,000.00
Liabilities & Owners' Equity
Accounts Payable	$ 693,808.72	$ 764,000.00	$ 540,000.00
Short-term Notes Payable	$ 158,000.00	$ 158,000.00	$ 198,000.00
Accrued Expenses	$ 290,652.57	$ 318,000.00	$ 228,000.00
Total Current Liabilites	$ 1,142,461.29	$ 1,240,000.00	$ 966,000.00
Long-term Debt	$ 2,046,000.00	$ 2,046,000.00	$ 1,934,000.00
Total Liabilites	$ 3,188,461.29	$ 3,286,000.00	$ 2,900,000.00
Common Stock	$ 1,638,000.00	$ 1,638,000.00	$ 1,616,000.00
Retained Earnings	$ 2,402,168.16	$ 2,270,000.00	$ 2,024,000.00
Total Shareholder's Equity	$ 4,040,168.16	$ 3,908,000.00	$ 3,640,000.00
Total Liabilites and Owners' Equity	$ 7,228,629.45	$ 7,194,000.00	$ 6,540,000.00
*Forecast
Discretionary Financing Needed	-86.66	surplus
Net Addition to Plant & Equipment	500000
Life of New Equipment in Years	10
New Depreciation (Straight Line)	50000

Fashion Trends, Inc.
Statement of Cash Flows
For the Year Ended Dec. 31, 2011
Cash Flows from Operations
Net Income	462.00
Depreciation Expense	478.00
Change in Accounts Receivable	-276.00
Change in Inventories	22.00
Change in Accounts Payable	224.00
Change in Prepaid Expenses	90.00
Total Cash Flows from Operations		1,000.00
Cash Flows from Investing
Change in Plant & Equipment	-694.00
Total Cash Flows from Investing		-694.00
Cash Flows from Financing
Change in Short-term Notes Payable	-40.00
Change in Long-term Debt	112.00
Change in Common Stock	22.00
Cash Dividends Paid to Shareholders	-216.00
Total Cash Flows from Financing		-122.00
Net Change in Cash Balance		184.00

The firm has forecasted sales of $7,100,000 and a tax rate of 40% for 2012. The cost of goods sold in 2012 is expected to change with sales by 105% of the two-year arithmetic average of the proportion of this item in relation to sales. The same formula should be applied for selling and G&A expenses, except that this item is expected to be 95% of the mentioned arithmetic average. On the other hand, accounts receivable, inventory, accounts payable, and accrued expenses are expected to change with sales at the two-year arithmetic average of the proportion of these items in relation to sales. The firm has planned an investment of $500,000 in fixed assets in 2012, 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 2012. Assume the firm pays 7% interest on short-term debt and 9% on long term debt. Assume that the dividends in 2012 will be the same as those paid in 2011. Using the Elvis Products International example from Chapter 5, respond to the following:

a. What is the Discretionary Financing Needed (DFN) in 2012? Is this a surplus or deficit?

b. Assume that the DFN will be absorbed by long-term debt. Set up an iterative worksheet to eliminate it.

c. Assume that the DFN will be absorbed by short-term debt. Set up an iterative worksheet to eliminate it.

d. 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?

*2011 and 2010 were given 2012 was my own numbers. Net income is correct, but i am not sure about my balance sheet

Answer & Explanation Solved by verified expert

See Answer