please solve using excel and show work thank you

PROBLEM Two (20 MARKS) In addition to the AAA Ltd. financial statements in Problem One, you are given more information as follows. Sales are forecast to increase by 30% in 2022. Short-term Debt, Long-term Debt, and Common Stock will not change. Net Plant and Equipment is forecasted to be $6,250,000 next year. In 2022, the company's dividend payout ratio will be 80%. Cost of goods sold is expected to be 60% of sales. Selling and Administrative Expenses will be 8% of sales. Advertising Expenses will be 4% of sales. Depreciation expense in 2022 is expected to be $185,500. Cash is expected to be 3% of sales, accounts receivable will be 16% of sales and inventory will be 6% of sales. Accounts payable will be 7% of sales. Accrued wages payable will be 1% of sales and Income taxes payable will be 10% of the forecasted net income for 2022. The company is expected to pay 4% per year compounded annually on its short-term debt and 8% per year compounded annually on its long-term debt. The interest expense on the short-term debt in 2022 is calculated as: [interest rate on short-term debt * amount of short-term debt outstanding at the end of 2021]. The interest expense on the long-term debt in 2022 is calculated as: [interest rate on longterm debt * amount of long-term debt outstanding at the end of 2021]. The company's tax rate is 38%. Based on the information provided you are to: a) Complete the pro-forma income statement and balance sheet for 2022. b) Calculate the amount of Additional Funds Needed in 2022. PROBLEM ONE (20 MARKS) Sales Cost of Goods Sold Selling and Administrative Expenses Advertising Expenses Depreciation Earnings before Interest and Taxes Interest Expense Earnings before Taxes Taxes (38%) Net Income AAA Inc Income Statement For Year Ended Dec 31st, 2021 2020 2019 $16,740,000 $14,025,000 $12,750,000 10,250,000 9,250,000 8,600,500 1,230,000 1,075,000 950,800 650,000 525,000 250,000 154,000 122,800 105,400 4,456,000 3,052,200 2,843,300 208,500 155,500 132,250 4,247,500 2,896,700 2,711,050 1,614,050 1,100,746 1,030,199 $2,633,450 $1,795,954 $1,680,851 AAA Inc Balance Sheet 2021 2019 Assets Cash & Equivalents Accounts Receivable Inventory Total Current Assets Prop, Plant & Equip - Net Total Assets For Year Ended Dec 31st, 2020 $150,450 $102,750 2,635,300 2,200,700 827,500 787,800 3,613,250 3,091,250 5,893,670 5,287,550 $9,506,920 $8,378,800 $85,650 1,800,400 675,350 2,561,400 4,750,400 $7,311,800 2021 2020 2019 Liabilities & Equity Accounts Payable Accrued Wages Payable Income Tax Payable Short-term Debt Total Current Liabilities $926,750 140,300 250,660 775,800 2,093,510 $787,850 128,640 220,320 818,750 1,955,560 $645,250 112,400 195,500 915,300 1,868,450 no Selling and Administrative Expenses Advertising Expenses Depreciation Earnings before Interest and Taxes Interest Expense Earnings before Taxes Taxes (38%) Net Income 1,230,000 650,000 154,000 4,456,000 208,500 4,247,500 1,614,050 $2,633,450 1,075,000 525,000 122,800 3,052,200 155,500 2,896,700 1,100,746 $1,795,954 950,800 250,000 105,400 2,843,300 132,250 2,711,050 1,030,199 $1,680,851 AAA Inc Balance Sheet For Year Ended Dec 31st, 2021 Assets Cash & Equivalents Accounts Receivable Inventory Total Current Assets Prop, Plant & Equip - Net Total Assets $150,450 2,635,300 827,500 3,613,250 5,893,670 $9,506,920 2020 $102,750 2,200,700 787,800 3,091,250 5,287,550 $8,378,800 2019 $85,650 1,800,400 675,350 2,561,400 4,750,400 $7,311,800 2021 2020 2019 Liabilities & Equity Accounts Payable Accrued Wages Payable Income Tax Payable Short-term Debt Total Current Liabilities Long-term Debt Total Liabilities Common Stock Retained Earnings Total Equity Total Liabilities and Equity $926,750 140,300 250,660 775,800 2,093,510 2,528,700 4,622,210 1,770,000 3,114,710 4,884,710 $9,506,920 $787,850 128,640 220,320 818,750 1,955,560 2,302,400 4,257,960 1,250,000 2,870,840 4,120,840 $8,378,800 $645,250 112,400 195,500 915,300 1,868,450 1,750,400 3,618,850 1,100,000 2,592,950 3,692,950 $7,311,800 PROBLEM Two (20 MARKS) In addition to the AAA Ltd. financial statements in Problem One, you are given more information as follows. Sales are forecast to increase by 30% in 2022. Short-term Debt, Long-term Debt, and Common Stock will not change. Net Plant and Equipment is forecasted to be $6,250,000 next year. In 2022, the company's dividend payout ratio will be 80%. Cost of goods sold is expected to be 60% of sales. Selling and Administrative Expenses will be 8% of sales. Advertising Expenses will be 4% of sales. Depreciation expense in 2022 is expected to be $185,500. Cash is expected to be 3% of sales, accounts receivable will be 16% of sales and inventory will be 6% of sales. Accounts payable will be 7% of sales. Accrued wages payable will be 1% of sales and Income taxes payable will be 10% of the forecasted net income for 2022. The company is expected to pay 4% per year compounded annually on its short-term debt and 8% per year compounded annually on its long-term debt. The interest expense on the short-term debt in 2022 is calculated as: [interest rate on short-term debt * amount of short-term debt outstanding at the end of 2021]. The interest expense on the long-term debt in 2022 is calculated as: [interest rate on longterm debt * amount of long-term debt outstanding at the end of 2021]. The company's tax rate is 38%. Based on the information provided you are to: a) Complete the pro-forma income statement and balance sheet for 2022. b) Calculate the amount of Additional Funds Needed in 2022. PROBLEM ONE (20 MARKS) Sales Cost of Goods Sold Selling and Administrative Expenses Advertising Expenses Depreciation Earnings before Interest and Taxes Interest Expense Earnings before Taxes Taxes (38%) Net Income AAA Inc Income Statement For Year Ended Dec 31st, 2021 2020 2019 $16,740,000 $14,025,000 $12,750,000 10,250,000 9,250,000 8,600,500 1,230,000 1,075,000 950,800 650,000 525,000 250,000 154,000 122,800 105,400 4,456,000 3,052,200 2,843,300 208,500 155,500 132,250 4,247,500 2,896,700 2,711,050 1,614,050 1,100,746 1,030,199 $2,633,450 $1,795,954 $1,680,851 AAA Inc Balance Sheet 2021 2019 Assets Cash & Equivalents Accounts Receivable Inventory Total Current Assets Prop, Plant & Equip - Net Total Assets For Year Ended Dec 31st, 2020 $150,450 $102,750 2,635,300 2,200,700 827,500 787,800 3,613,250 3,091,250 5,893,670 5,287,550 $9,506,920 $8,378,800 $85,650 1,800,400 675,350 2,561,400 4,750,400 $7,311,800 2021 2020 2019 Liabilities & Equity Accounts Payable Accrued Wages Payable Income Tax Payable Short-term Debt Total Current Liabilities $926,750 140,300 250,660 775,800 2,093,510 $787,850 128,640 220,320 818,750 1,955,560 $645,250 112,400 195,500 915,300 1,868,450 no Selling and Administrative Expenses Advertising Expenses Depreciation Earnings before Interest and Taxes Interest Expense Earnings before Taxes Taxes (38%) Net Income 1,230,000 650,000 154,000 4,456,000 208,500 4,247,500 1,614,050 $2,633,450 1,075,000 525,000 122,800 3,052,200 155,500 2,896,700 1,100,746 $1,795,954 950,800 250,000 105,400 2,843,300 132,250 2,711,050 1,030,199 $1,680,851 AAA Inc Balance Sheet For Year Ended Dec 31st, 2021 Assets Cash & Equivalents Accounts Receivable Inventory Total Current Assets Prop, Plant & Equip - Net Total Assets $150,450 2,635,300 827,500 3,613,250 5,893,670 $9,506,920 2020 $102,750 2,200,700 787,800 3,091,250 5,287,550 $8,378,800 2019 $85,650 1,800,400 675,350 2,561,400 4,750,400 $7,311,800 2021 2020 2019 Liabilities & Equity Accounts Payable Accrued Wages Payable Income Tax Payable Short-term Debt Total Current Liabilities Long-term Debt Total Liabilities Common Stock Retained Earnings Total Equity Total Liabilities and Equity $926,750 140,300 250,660 775,800 2,093,510 2,528,700 4,622,210 1,770,000 3,114,710 4,884,710 $9,506,920 $787,850 128,640 220,320 818,750 1,955,560 2,302,400 4,257,960 1,250,000 2,870,840 4,120,840 $8,378,800 $645,250 112,400 195,500 915,300 1,868,450 1,750,400 3,618,850 1,100,000 2,592,950 3,692,950 $7,311,800