Please Answer in Excel and SHow all Steps

X AFF 210 Problem Set TWO Winter 2022 PROBLEM ONE (20 MARKS) Sales Cost of Goods Sold Selling and Administrative Expenses Advertising Expenses Depreciation Earnings before Interest and Taxes Interest Expense Earrings before Taxes Taxes (389) Net Incuene AAA loc Income Statement Tor Year Ended Dec 31", 2021 2020 2019 $16.740,000 $14,025,000 SI2.750,000 10,250,000 9,250.000 8.600.500 1,290,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 1.217.500 2.896,700 2,711,050 1.614.050 1.100.745 1,030,199 $2.633.450 $1.795,954 $1,680,831 | Assets Cash & Equivalents Accounts Receivable Inventory Total Current Assets Prop, Plant & Equip-Net Total Assets AAA Ine Balance Sheet For Year Ended Dec 31". 2021 2020 $150.450 $102,750 2,635,300 2.200,700 827.500 787,500 9,613,250 3,091,250 5.893.670 5,287,550 $9 506920 $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 Lang-term Debt Total Liabilities Common Stock Retained Earnings Total Equity Total Liabilities and Equity $926.750 110,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,610 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,100 195,500 915 300 1,868,150 1,750,400 3,618,850 1,100.000 2.592.950 3,692,950 $7.311,800 OL IL AO ca ENG US 4x) 3.10 PM 2022-04-14 X Total Current Liabilities Lang-term Debt Total Liabilales Common Stock Retained Earnings Total Equity Total Liabilities and Equity 2,093,510 2,528 700 4.622.210 1,770,000 3,114,710 4,884,710 $9.506,920 1.955. 560 2,302,400 4,257,960 1.250,000 2.870,840 4,120,840 $8.378,500 1,868,150 1,750,400 3,618,850 1,100,000 2.392.950 3.692 050 $7,311,800 Page 2 AFF 210 Problem Sel TWO W 2002 Answer the following questions. Please note, short term investments are non operating current assets, noles payable and short-lerna debl are non-operating current liabilities. a) What is the Net Operating Profit after tax (NOPAT) for 2021? b) What is Operatina Cash Flow for 20219 c) What is Free Cash Flow for 20219 (Short-term deht is not part of operating networking capital) The stock is traded at $66 per saare at the end of 2021 and there are 195,000 shares outstanding What is MVA during 2021? e) Given the firm's WACC is 10%, what is EVA during 2021? 1) Creale Don Zincome statement and balance street for 2021, 2020 and 2019 ) 8) Create income statenent and balance sheet percentage change analysis for 2021 and 2020. (Use 2019 as the base year) OneDrive Screenshot saved The screenshot was added to your OneDrive. H LO ENG US 3.10 PM 2022-04-14 AFF 210 Problem Set Two Winter 2002 PROBLEM TWO (20 MARKS) In addition to the AAA Ltd, financial statements in Problem One, you are given more informations 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 56.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 9% 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 det income for 2022. per year compound to pay 4% per year compounded annually on its short-term debt and 8% annually on its long term debt. The interest expense on the short term debt in 2022 is calculated as interest rate un short-term debl amount of short-lerta debt outstanding at the end of 2021). The interest expense on the long-term debt in 2022 is calculated as: interest rate on lopetem debet* amount of long-term debt outstanding at the end of 2021] The company's tax rate is 38% The companys Based on the information provided you are to: a) Complete the pro fomnia income statement and balance sheet for 2022 b) Calculate tbe amount of Additional Funds Needed in 2022. 3:11 PM H LO AO ENG US x 2022-04-14 AFF 210 Problem Set Two Winter 20:22 Problem Three (20 marks) ABC i debating the purchase of a new digital printer. The printer they acquired 3 years ago for $1.800.000 is worth $1,200,000 today and will have a salvage value of $340,000 after 6 more years. The printer generates revenues of S660 000 per yenr The costs of operating the printer are $340,000 per year. The company currently bas $80,000 invested in operating net working capital. The investment in operating net working capital will remain at this level for the remaining 6 years of the project The new printer will cost $2,430,000. It will cost $120,000 to install the new printee. The new printer will generate revenues of $1,100,000 per year. In addition, the costs of operating the new printer will be $460.000 per year. The company will bave to increase ita investment in operating net working capital to $125,000 at time zero. Thereafter, operating networking capital will increase by $15,000 per year until the end of the project. At the end of 6 years, the new machine will have a salvage value of $20.000 The company's corporate tax rate : 34%, the CCA rate is 30% and the required rate of return is 8% Assume the asset class remains open Using net proseat value (NPV) calculation, determine if the company should purchase the new printer, Show all work. 3:11 PM H LO o AO ENG US x 2022-04-14 X AFF 210 Problem Set Two Winter 2022 Problem Four (20 marks) ABC Inc. is considering purchasing a new machine. The machine will cost $15,500,000. The machine will be used for a project that lasts 4 years. The expected salvare of the machine at the end of the project is $1,600,000. The machine will be used to produce widgets. The manufacturing department has forecasted that the company will be able to sell 480,000 widyels per year. The manulacturing department believes that the company will be able to charge $38 per widget The variable costs per widget are expected to be $21. The incremental fixed costs per year are expected to be $150,000. The company believes that the project will require an alvestament in operating networking capital of $240.000 Thereafter operating networking capital will increase by $20,000 per year to the end of the project Assume the asset class remains open. The CCA rate is 24%, the tax rate is 34%, and the required rate of retutta is 8%. a) Using net present value (NPV) calculation determine if the company should purchase the new machine Show all work b) Your boss has acumber of concerns regarding the project. Therefore, abe has asked you to determine the number of units that the company must sell each year for the NPV to be greater than zero c) Your bass has also asked you to determine which mput (units sold, price per unit, variable cost per unit, or fixed cost) has the greatest forecasting risk. Therefore, your hose has asked you to do a sensitivity analysis. Your buss Warils you to vary the input forecast by 5% and determine the impact on the NPV of the project Based on this analyses you are to determine which input has the greatest forecasting risk d) Finally, your boss has asked you to perform a scenario analysis. Therefore, your boss has a asked to include two new scenarios. A pessimistic scenario and an optimistic scenario. Your boss wishes to know the NPV of the project wider these two additional scenarios. The valus of the inputs for each scenario are shown in the table below. Persunistic 380,000 $32 $28 $250,000 Umuts sold Price per unit Variable cost per unit Fixed Cost Optimistic 700,000 S48 $18 S100,000 | . OL AO ENG US 3:11 PM 2022-04-14 X AFT 210 Problem Set TWO Winter 2022 Problem Five (20 marks) You have been asked to analyze two mutually exclusive projects Expected Cash Flow's | Year Expected Cash Flows Project B Project A 0 -191,000 -101,000 -48 500 14,500 1 2 -24,300 16,000 3 41.500 18,550 22.000 4 4 100,500 5 132,500 23,500 25,500 6 177,000 7 212,000 28,000 8 275,000 35,000 Construct the NPV profiles for Project A and Projoct B. (Note: plot the NPVs of both projects on the same graph.) The cost of capital ranges from 0% to 30% by increments of 2%. b) Calculate each project's IRR Calculate the crossover rate of the two projecta. d) d Calculate each project's MIRR at a cost of capital of 14% and reinvestment rate or 10%. e) Calculate each project's regular payback period. f Calculate each project's discounted payback period with a cost of capital of 14% Calculale each project's profitability index at a cost of capital of 14% b) Calculate each project's NPV at a required rate of return of 14% 1) Calculate each project's NPV at a required rate of return of 28% 1 LO AO ENG US x 3:11 PM 2022-04-14 X AFF 210 Problem Set TWO Winter 2022 PROBLEM ONE (20 MARKS) Sales Cost of Goods Sold Selling and Administrative Expenses Advertising Expenses Depreciation Earnings before Interest and Taxes Interest Expense Earrings before Taxes Taxes (389) Net Incuene AAA loc Income Statement Tor Year Ended Dec 31", 2021 2020 2019 $16.740,000 $14,025,000 SI2.750,000 10,250,000 9,250.000 8.600.500 1,290,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 1.217.500 2.896,700 2,711,050 1.614.050 1.100.745 1,030,199 $2.633.450 $1.795,954 $1,680,831 | Assets Cash & Equivalents Accounts Receivable Inventory Total Current Assets Prop, Plant & Equip-Net Total Assets AAA Ine Balance Sheet For Year Ended Dec 31". 2021 2020 $150.450 $102,750 2,635,300 2.200,700 827.500 787,500 9,613,250 3,091,250 5.893.670 5,287,550 $9 506920 $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 Lang-term Debt Total Liabilities Common Stock Retained Earnings Total Equity Total Liabilities and Equity $926.750 110,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,610 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,100 195,500 915 300 1,868,150 1,750,400 3,618,850 1,100.000 2.592.950 3,692,950 $7.311,800 OL IL AO ca ENG US 4x) 3.10 PM 2022-04-14 X Total Current Liabilities Lang-term Debt Total Liabilales Common Stock Retained Earnings Total Equity Total Liabilities and Equity 2,093,510 2,528 700 4.622.210 1,770,000 3,114,710 4,884,710 $9.506,920 1.955. 560 2,302,400 4,257,960 1.250,000 2.870,840 4,120,840 $8.378,500 1,868,150 1,750,400 3,618,850 1,100,000 2.392.950 3.692 050 $7,311,800 Page 2 AFF 210 Problem Sel TWO W 2002 Answer the following questions. Please note, short term investments are non operating current assets, noles payable and short-lerna debl are non-operating current liabilities. a) What is the Net Operating Profit after tax (NOPAT) for 2021? b) What is Operatina Cash Flow for 20219 c) What is Free Cash Flow for 20219 (Short-term deht is not part of operating networking capital) The stock is traded at $66 per saare at the end of 2021 and there are 195,000 shares outstanding What is MVA during 2021? e) Given the firm's WACC is 10%, what is EVA during 2021? 1) Creale Don Zincome statement and balance street for 2021, 2020 and 2019 ) 8) Create income statenent and balance sheet percentage change analysis for 2021 and 2020. (Use 2019 as the base year) OneDrive Screenshot saved The screenshot was added to your OneDrive. H LO ENG US 3.10 PM 2022-04-14 AFF 210 Problem Set Two Winter 2002 PROBLEM TWO (20 MARKS) In addition to the AAA Ltd, financial statements in Problem One, you are given more informations 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 56.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 9% 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 det income for 2022. per year compound to pay 4% per year compounded annually on its short-term debt and 8% annually on its long term debt. The interest expense on the short term debt in 2022 is calculated as interest rate un short-term debl amount of short-lerta debt outstanding at the end of 2021). The interest expense on the long-term debt in 2022 is calculated as: interest rate on lopetem debet* amount of long-term debt outstanding at the end of 2021] The company's tax rate is 38% The companys Based on the information provided you are to: a) Complete the pro fomnia income statement and balance sheet for 2022 b) Calculate tbe amount of Additional Funds Needed in 2022. 3:11 PM H LO AO ENG US x 2022-04-14 AFF 210 Problem Set Two Winter 20:22 Problem Three (20 marks) ABC i debating the purchase of a new digital printer. The printer they acquired 3 years ago for $1.800.000 is worth $1,200,000 today and will have a salvage value of $340,000 after 6 more years. The printer generates revenues of S660 000 per yenr The costs of operating the printer are $340,000 per year. The company currently bas $80,000 invested in operating net working capital. The investment in operating net working capital will remain at this level for the remaining 6 years of the project The new printer will cost $2,430,000. It will cost $120,000 to install the new printee. The new printer will generate revenues of $1,100,000 per year. In addition, the costs of operating the new printer will be $460.000 per year. The company will bave to increase ita investment in operating net working capital to $125,000 at time zero. Thereafter, operating networking capital will increase by $15,000 per year until the end of the project. At the end of 6 years, the new machine will have a salvage value of $20.000 The company's corporate tax rate : 34%, the CCA rate is 30% and the required rate of return is 8% Assume the asset class remains open Using net proseat value (NPV) calculation, determine if the company should purchase the new printer, Show all work. 3:11 PM H LO o AO ENG US x 2022-04-14 X AFF 210 Problem Set Two Winter 2022 Problem Four (20 marks) ABC Inc. is considering purchasing a new machine. The machine will cost $15,500,000. The machine will be used for a project that lasts 4 years. The expected salvare of the machine at the end of the project is $1,600,000. The machine will be used to produce widgets. The manufacturing department has forecasted that the company will be able to sell 480,000 widyels per year. The manulacturing department believes that the company will be able to charge $38 per widget The variable costs per widget are expected to be $21. The incremental fixed costs per year are expected to be $150,000. The company believes that the project will require an alvestament in operating networking capital of $240.000 Thereafter operating networking capital will increase by $20,000 per year to the end of the project Assume the asset class remains open. The CCA rate is 24%, the tax rate is 34%, and the required rate of retutta is 8%. a) Using net present value (NPV) calculation determine if the company should purchase the new machine Show all work b) Your boss has acumber of concerns regarding the project. Therefore, abe has asked you to determine the number of units that the company must sell each year for the NPV to be greater than zero c) Your bass has also asked you to determine which mput (units sold, price per unit, variable cost per unit, or fixed cost) has the greatest forecasting risk. Therefore, your hose has asked you to do a sensitivity analysis. Your buss Warils you to vary the input forecast by 5% and determine the impact on the NPV of the project Based on this analyses you are to determine which input has the greatest forecasting risk d) Finally, your boss has asked you to perform a scenario analysis. Therefore, your boss has a asked to include two new scenarios. A pessimistic scenario and an optimistic scenario. Your boss wishes to know the NPV of the project wider these two additional scenarios. The valus of the inputs for each scenario are shown in the table below. Persunistic 380,000 $32 $28 $250,000 Umuts sold Price per unit Variable cost per unit Fixed Cost Optimistic 700,000 S48 $18 S100,000 | . OL AO ENG US 3:11 PM 2022-04-14 X AFT 210 Problem Set TWO Winter 2022 Problem Five (20 marks) You have been asked to analyze two mutually exclusive projects Expected Cash Flow's | Year Expected Cash Flows Project B Project A 0 -191,000 -101,000 -48 500 14,500 1 2 -24,300 16,000 3 41.500 18,550 22.000 4 4 100,500 5 132,500 23,500 25,500 6 177,000 7 212,000 28,000 8 275,000 35,000 Construct the NPV profiles for Project A and Projoct B. (Note: plot the NPVs of both projects on the same graph.) The cost of capital ranges from 0% to 30% by increments of 2%. b) Calculate each project's IRR Calculate the crossover rate of the two projecta. d) d Calculate each project's MIRR at a cost of capital of 14% and reinvestment rate or 10%. e) Calculate each project's regular payback period. f Calculate each project's discounted payback period with a cost of capital of 14% Calculale each project's profitability index at a cost of capital of 14% b) Calculate each project's NPV at a required rate of return of 14% 1) Calculate each project's NPV at a required rate of return of 28% 1 LO AO ENG US x 3:11 PM 2022-04-14