Fashion Trends, Inc., a regional fashion apparel retailer, wants to prepare a 2021 Pro Forma Income Statement and a 2021 Balance Sheet using the following 2020 and 2019 data: Fashion Trends, Inc. Fashion Trends, Inc. Income Statement Balance Sheet For the Period Ended Dec. 31, 2020 As of Dec. 31, 2020 2020 2019 Assets 2020 2019 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 2021. Cost of goods sold and S.G&A expense in 2021 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 2021, 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 2021. Assume the firm pays 4% interest on short-term debt and 7% on long term debt. Assume that the dividends in 2021 will be the same as those paid in 2020. a) What is the Discretionary Financing Needed (DFN) in 2021? Is this a surplus or deficit? (Analysis & Identify and Access Needed Information 8 pts) b) DFN will be absorbed by long-term debt. Set up an iterative worksheet to eliminate it. (Analysis, Identify and Access Needed Information & Use Information Effectively to Accomplish a Specific Purpose 12 pts) c) Interpretation: please describe where you obtained information to develop analyses and interpret the results. Please include your written description in the interpretation tab. If you do not complete this section with your words or leave the interpretation tab as a blank, you receive 0 point in this HW. A B D E F 1 Please complete the yellow highlighted parts. Please show the formulas if the number or figure 2 3 Fashion Trends, Inc. 4 Balance Sheet 5 For the Period Ended Dec. 31, 2020 6 2021* 2020 2019 7 Sales 8 Cost of Goods Sold 9 Gross Profit 10 Selling and G&A Expenses 11 Fixed Expenses 12 Depreciation Expense 13 EBIT 14 Interest Expense 15 Earnings Before Taxes 16 Taxes 17 Net Income 18 19 * Forecast 20 21 Notes 22 Tax Rate 40% 23 24 Additional Depreciation 25 Sorth-term Interest Rate 26 Long-term Interest Rate 27 Scenario Multiplier for part c 28 DFN for Scenarios in part c 29 A B C D E F 2 3 4 5 2020 2019 16 Fashion Trends, Inc. Balance Sheet As of Dec. 31, 2020 6 Assets 2021* 7 Cash and Equivalents 8 Accounts Receivable 9 Inventory 10 Total Current Assets 11 Plant & Equipment 12 Accumulated Depreciation 13 Net Fixed Assets 14 Total Assets 15 Liabilities and Owners' Equity Accounts Payable 17 Short-term Notes Payable 18 Accrued Expenses 19 Total Current Liabilities 20 Long-term Debt 21 Total Liabilities 22 Common Stock 23 Retained Earnings 24 Total Shareholder's Equity 25 Total Liabilities and Owners' Equity 26 27 * Forecast 28 Discretionary Financing Needed 29 Total Accumulated DFN 30 31 2020 Dividends 32 Net Addition to Plan and Equipment 33 Life of New Equipment in Years 34 Salvage Value of New Equipment 35 New Depreciation (Straight Line) 36 Iteration "Surplus" or "Deficit" or "Balanced" Fashion Trends, Inc., a regional fashion apparel retailer, wants to prepare a 2021 Pro Forma Income Statement and a 2021 Balance Sheet using the following 2020 and 2019 data: Fashion Trends, Inc. Fashion Trends, Inc. Income Statement Balance Sheet For the Period Ended Dec. 31, 2020 As of Dec. 31, 2020 2020 2019 Assets 2020 2019 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 2021. Cost of goods sold and S.G&A expense in 2021 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 2021, 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 2021. Assume the firm pays 4% interest on short-term debt and 7% on long term debt. Assume that the dividends in 2021 will be the same as those paid in 2020. a) What is the Discretionary Financing Needed (DFN) in 2021? Is this a surplus or deficit? (Analysis & Identify and Access Needed Information 8 pts) b) DFN will be absorbed by long-term debt. Set up an iterative worksheet to eliminate it. (Analysis, Identify and Access Needed Information & Use Information Effectively to Accomplish a Specific Purpose 12 pts) c) Interpretation: please describe where you obtained information to develop analyses and interpret the results. Please include your written description in the interpretation tab. If you do not complete this section with your words or leave the interpretation tab as a blank, you receive 0 point in this HW. A B D E F 1 Please complete the yellow highlighted parts. Please show the formulas if the number or figure 2 3 Fashion Trends, Inc. 4 Balance Sheet 5 For the Period Ended Dec. 31, 2020 6 2021* 2020 2019 7 Sales 8 Cost of Goods Sold 9 Gross Profit 10 Selling and G&A Expenses 11 Fixed Expenses 12 Depreciation Expense 13 EBIT 14 Interest Expense 15 Earnings Before Taxes 16 Taxes 17 Net Income 18 19 * Forecast 20 21 Notes 22 Tax Rate 40% 23 24 Additional Depreciation 25 Sorth-term Interest Rate 26 Long-term Interest Rate 27 Scenario Multiplier for part c 28 DFN for Scenarios in part c 29 A B C D E F 2 3 4 5 2020 2019 16 Fashion Trends, Inc. Balance Sheet As of Dec. 31, 2020 6 Assets 2021* 7 Cash and Equivalents 8 Accounts Receivable 9 Inventory 10 Total Current Assets 11 Plant & Equipment 12 Accumulated Depreciation 13 Net Fixed Assets 14 Total Assets 15 Liabilities and Owners' Equity Accounts Payable 17 Short-term Notes Payable 18 Accrued Expenses 19 Total Current Liabilities 20 Long-term Debt 21 Total Liabilities 22 Common Stock 23 Retained Earnings 24 Total Shareholder's Equity 25 Total Liabilities and Owners' Equity 26 27 * Forecast 28 Discretionary Financing Needed 29 Total Accumulated DFN 30 31 2020 Dividends 32 Net Addition to Plan and Equipment 33 Life of New Equipment in Years 34 Salvage Value of New Equipment 35 New Depreciation (Straight Line) 36 Iteration "Surplus" or "Deficit" or "Balanced