Please use excel and take pictures of the formulas. There's a button where you press "show functions" in the formulas tab.
A trend table needs to he created. Also, a pro forma income statement and pro forma balance sheet.
Problem 12:30 points Fashion Trends Inc. a regional fashion apparel retail ends Inc., a regional fashion apparel retailer, wants to prepare a 2018 Pro Forma income Statement and 2018 P ar alange Sheet using the following 2017 and 2016 data: 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 TJC STATEMENT Balance Sheet For the Period Ended Dec 31, 2017 As of Dec. 31. 2017 1 000 4,176,000 1.972,090 500 700 3,134,000 3,422.000 1,712.000 701 LOX Cost of Goods Sold Grow Prefir SGEA Expenses Fired Expenses Depreciation Expense ERIT Interest Expense Earnings Before Taces Tanes Net Inc 186.000 650.000 R2,000 424,000 Cash and Equivalents Accounts Receivable Inn Toval Care Plant & Equipment Accumulated Depeciation Nered Tord Assets Lian and Egy Accounts Pavable Short-term Notes Payable Agend Expenses Talwrnah Long-term Debt Tocal tiones Common Stock Retained Earnings Tonal Shah Equity Total Llaws and Owners' Equity 455.000 960 1 000 318.000 7240 000 19.000 22.000 1,618 000 2.270.000 BORN 779,00 1.616.000 2.000.000 3,640,000 650.000 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 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 Tabl of your worksheet. Note: This should be the first tab on your Problem 2 worksheet. Sales data: Year 2013 2014 2015 2016 2017 Sales $2.500.000 $3.175.000 $4,200.000 S5.500.000 S6,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. Fixed Expenses Depreciation Expense EBIT Interest Expense Earnings Before Taxes Taxes Net Income 388, 70,000 478,000 836,000 186,000 650,000 195.000 455,000 390.000 70,000 446,000 606,000 182.000 424.000 127.200 296,800 2.446,000 9,338,000 4,590,000 4.748,000 7,194,000 2,008,00 8,644,00 4.112.00 4.532.00 6,540,00 Total Current Assets Plant & Equipment Accumulated Depreciation Net Fixed Assets Total Assets Liabilities and Owners' Equity Accounts Payable Short-term Notes Payable Accrued Expenses Total Current Liabilities Long-term Debt Total Liabilities Common Stock Retained Earnings Total Shareholder's Equity Total Liabilities and Owners' Equity 764,000 158,000 318.000 1.240,000 2,016,000 3.286.000 1,638,000 2.270,000 3.908,000 7.194.000 540,00 198,00 228.00 966,00 1.934,00 2.900.00 1,616,00 2,024,00 3,640.00 6,540,00 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 2013 2014 2015 2016 2017 Sales $2,500,000 $3,175,000 $4,200,000 $5,500,000 $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. 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: 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, Ine. B IJ. STATEMENT Balance Sheet For the Period Ended Dec 31, 2017 As of Dec. 31, 2017 2017 2016 2017 2016 Sales 6,148,000 5,134,000 Cash and Equivalents 862.000 67800 Cost of Goods Sold 4,176,000 3,422,000 Accounts Receivable 1,006,000 730,000 Gross Profit 7,972,000 1,712,000 Inventory 578,000 600.000 S.G&A Expenses 588,000 590,000 Toral Current Assets 2.446,000 2,008,000 Fixed Expenses 70,000 70,000 Plant & Equipment 9.118.000 8.6-14.000 Depreciation Expense 478,000 446.000 Accumulated Depreciation 590.000 4.112.000 EBIT 836,000 606,000 Net Fixed Assets 4.748,000 ,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 Onemers' Equity Tas 195,000 127,200 Accounts Payable 76-8.000 540,000 Net Income 455.000 296,800 Short-term Notes Payable 158,000 198,000 Accrued Expenses 3IR.000 228.000 Total Current Liabilities 1.240,000 966,000 Long-term Debt 2.046.000 1.934.000 Total abilities 3.256.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 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 ver 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 det Assume that the dividends in 2018 will be $140,000. Fashion Trends Inc., a regional fashion appard retailer, wants to prepare a 2018 Pro Forma Income Statement and a 2018 Pro Forma Balance Sheet using the following 2017 and 2016 data: 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: Fu Twente FaNaTres, Inc. Blanche For the Period Ended Dec 31, 2017 A L 2012 2017 2016 Sales 6.1 S.134.000 Cash and Events 852.000 67000 Cost of Goods Sold 4,176.000 3.493000 Ac Rece 1,006,000 710,000 Gres Prefa 1.972.0 2.90200 Inventory STRO 600,000 SCA E SO Tum 15000 MOOD Fund Expo 70.000 70.000 Plant A DD 544000 Depreciation Expense 478.000 446000 Accumulated Depreciation 4,50.00 4,112.000 ERIT Net Food LION 312000 Interest Expense Tour TOLON 0. Earnings Before Teres LOE Tas Accounts Payable 500.000 Norince Short Notes 198,000 Accrued Expenses BIOCO 000 Toul 1.616.000 2.024.000 Carack Retained Thr Taala 'raiy 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 sava value. These fixed assets will be depreciated using the straight-line depreciation method. You we take a full year's depreciation on the news in 2018. All other financial statement s expected to remain constant in 2018. Assume the firm pays 4% interest on short-term debt and 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 graphical forecast with a trendline. The graphic should be embedded in Tabl of your worksheet. Note: This should be the first tab on your Problem 2 worksheet Sales data: Saar 2013 2014 2015 2016 2017 Sales S2.500.000 S3,175.000 $4.200,000 $5.500.000 S6,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 Problem 12:30 points Fashion Trends Inc. a regional fashion apparel retail ends Inc., a regional fashion apparel retailer, wants to prepare a 2018 Pro Forma income Statement and 2018 P ar alange Sheet using the following 2017 and 2016 data: 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 TJC STATEMENT Balance Sheet For the Period Ended Dec 31, 2017 As of Dec. 31. 2017 1 000 4,176,000 1.972,090 500 700 3,134,000 3,422.000 1,712.000 701 LOX Cost of Goods Sold Grow Prefir SGEA Expenses Fired Expenses Depreciation Expense ERIT Interest Expense Earnings Before Taces Tanes Net Inc 186.000 650.000 R2,000 424,000 Cash and Equivalents Accounts Receivable Inn Toval Care Plant & Equipment Accumulated Depeciation Nered Tord Assets Lian and Egy Accounts Pavable Short-term Notes Payable Agend Expenses Talwrnah Long-term Debt Tocal tiones Common Stock Retained Earnings Tonal Shah Equity Total Llaws and Owners' Equity 455.000 960 1 000 318.000 7240 000 19.000 22.000 1,618 000 2.270.000 BORN 779,00 1.616.000 2.000.000 3,640,000 650.000 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 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 Tabl of your worksheet. Note: This should be the first tab on your Problem 2 worksheet. Sales data: Year 2013 2014 2015 2016 2017 Sales $2.500.000 $3.175.000 $4,200.000 S5.500.000 S6,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. Fixed Expenses Depreciation Expense EBIT Interest Expense Earnings Before Taxes Taxes Net Income 388, 70,000 478,000 836,000 186,000 650,000 195.000 455,000 390.000 70,000 446,000 606,000 182.000 424.000 127.200 296,800 2.446,000 9,338,000 4,590,000 4.748,000 7,194,000 2,008,00 8,644,00 4.112.00 4.532.00 6,540,00 Total Current Assets Plant & Equipment Accumulated Depreciation Net Fixed Assets Total Assets Liabilities and Owners' Equity Accounts Payable Short-term Notes Payable Accrued Expenses Total Current Liabilities Long-term Debt Total Liabilities Common Stock Retained Earnings Total Shareholder's Equity Total Liabilities and Owners' Equity 764,000 158,000 318.000 1.240,000 2,016,000 3.286.000 1,638,000 2.270,000 3.908,000 7.194.000 540,00 198,00 228.00 966,00 1.934,00 2.900.00 1,616,00 2,024,00 3,640.00 6,540,00 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 2013 2014 2015 2016 2017 Sales $2,500,000 $3,175,000 $4,200,000 $5,500,000 $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. 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: 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, Ine. B IJ. STATEMENT Balance Sheet For the Period Ended Dec 31, 2017 As of Dec. 31, 2017 2017 2016 2017 2016 Sales 6,148,000 5,134,000 Cash and Equivalents 862.000 67800 Cost of Goods Sold 4,176,000 3,422,000 Accounts Receivable 1,006,000 730,000 Gross Profit 7,972,000 1,712,000 Inventory 578,000 600.000 S.G&A Expenses 588,000 590,000 Toral Current Assets 2.446,000 2,008,000 Fixed Expenses 70,000 70,000 Plant & Equipment 9.118.000 8.6-14.000 Depreciation Expense 478,000 446.000 Accumulated Depreciation 590.000 4.112.000 EBIT 836,000 606,000 Net Fixed Assets 4.748,000 ,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 Onemers' Equity Tas 195,000 127,200 Accounts Payable 76-8.000 540,000 Net Income 455.000 296,800 Short-term Notes Payable 158,000 198,000 Accrued Expenses 3IR.000 228.000 Total Current Liabilities 1.240,000 966,000 Long-term Debt 2.046.000 1.934.000 Total abilities 3.256.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 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 ver 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 det Assume that the dividends in 2018 will be $140,000. Fashion Trends Inc., a regional fashion appard retailer, wants to prepare a 2018 Pro Forma Income Statement and a 2018 Pro Forma Balance Sheet using the following 2017 and 2016 data: 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: Fu Twente FaNaTres, Inc. Blanche For the Period Ended Dec 31, 2017 A L 2012 2017 2016 Sales 6.1 S.134.000 Cash and Events 852.000 67000 Cost of Goods Sold 4,176.000 3.493000 Ac Rece 1,006,000 710,000 Gres Prefa 1.972.0 2.90200 Inventory STRO 600,000 SCA E SO Tum 15000 MOOD Fund Expo 70.000 70.000 Plant A DD 544000 Depreciation Expense 478.000 446000 Accumulated Depreciation 4,50.00 4,112.000 ERIT Net Food LION 312000 Interest Expense Tour TOLON 0. Earnings Before Teres LOE Tas Accounts Payable 500.000 Norince Short Notes 198,000 Accrued Expenses BIOCO 000 Toul 1.616.000 2.024.000 Carack Retained Thr Taala 'raiy 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 sava value. These fixed assets will be depreciated using the straight-line depreciation method. You we take a full year's depreciation on the news in 2018. All other financial statement s expected to remain constant in 2018. Assume the firm pays 4% interest on short-term debt and 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 graphical forecast with a trendline. The graphic should be embedded in Tabl of your worksheet. Note: This should be the first tab on your Problem 2 worksheet Sales data: Saar 2013 2014 2015 2016 2017 Sales S2.500.000 S3,175.000 $4.200,000 $5.500.000 S6,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
