See attachment. Attached word document is the data. Attached excel is the worksheet for each task (1, 2, 3, 8). All of these worksheets was done you guys. What I need: written report ( Introduction, report based on those worksheet and conclusion). for task 8, report must be addressing the one in "YELLOW". Thank you
Task 1 Data from Financial Statements Particulars20202021 Current Assets128,950,000144,975,000 Inventories112,000,000127,000,000 Current Liabilities132,524,000144,002,000 Long-term debt115,000,000130,000,000 Total Owner's equity169,426,000170,473,000 Net IncomeN/A9,045,000 Financial Ratios 20202021 1. Working Capital Ratio0.971.01 2. Quick Ratio0.130.12 5. Debt-Equity Ratio0.680.76 6. Return on EquityN/A5.31% Capital Asset Pricing Model (CAPM) Expected market return6.00% Risk-free rate2.00% BEB Beta1.20 Rate of Return6.80% Task 2 Given: Tax Rate36% Cost of debt before tax 7.50% Long-Term Debt130,000,000.00 Current Portion of LT Debt5,350,000.00 Total Debt135,350,000.00 Cost of Equity6.80% Total Owner's equity$170,473,000.00 Calculations: After-tax cost of debt Cost of debt before tax *(1-Tax Rate) After-tax cost of debt 4.800% Proportions of debt and equity in the firm ParticularAmountWeight Total Debt$135,350,000.0044.26% Total Owner's equity$170,473,000.0055.74% Total$305,823,000.00100.00% We compute the WACC in this circumstance by multiplying the weight of each source of Capital with its Cost. Because the WACC is used to assess the present value of future cash flows, it is extremely essential. If the WACC is low (as it is here), it is a favourable sign since it indicates that future cash flows are more valuable. Calculation of WACC ParticularWeightCostWACC Total Debt44.26%4.80%2.12% Total Owner's equity55.74%6.80%3.79% Total100.00%5.91% The capital structure is 44.26% debt and 55.74% equity. This means that the firm is using mainly Equity to fund their operations. This is not ideal as it results in them having a higher tax liability as Lower tax shield is availed on the interest on debt taken, which could have resulted in lower WACC, since debt is cheaper. Task 3 - NPV 1. Calculation of NPV Calculation of Present Value of Cash Outflows YearCash OutflowDiscounting Factor @ 7%Present Value 0$600,000.001$600,000.00 10$200,000.000.51$101,669.86 20$200,000.000.26$51,683.80 30$1,000,000.000.13$131,367.12 Total$884,720.78 Calculation of Present Value of Cash Inflows YearParticularAmount 1-30Annual Savings$50,000.00 1-30Tax Rate (From Task 2)36% 1-30Additional Tax on Annual Saving$18,000.00 1-30Annual Savings net of Tax$32,000.00 1-30Add: Tax Sheild on Depreciation 1-30Tax Shield Depreciation on Initial $600k for 30 years$7,200.00 1-30Tax Shield Depreciation on Subsequent $200k for 20 years$3,600.00 Tax Shield Depreciation on Subsequent $200k for 10 years$7,200.00 Thus, Annual Net Cash Inflows and Present Value of Cash Inflows would be as follows YearCash InflowsDiscounting Factor @ 7%Present Value 1$39,200.000.9346$36,635.51 2$39,200.000.8734$34,238.80 3$39,200.000.8163$31,998.88 4$39,200.000.7629$29,905.49 5$39,200.000.7130$27,949.06 6$39,200.000.6663$26,120.62 7$39,200.000.6227$24,411.79 8$39,200.000.5820$22,814.76 9$39,200.000.5439$21,322.20 10$39,200.000.5083$19,927.29 11$42,800.000.4751$20,333.97 12$42,800.000.4440$19,003.71 13$42,800.000.4150$17,760.48 14$42,800.000.3878$16,598.58 15$42,800.000.3624$15,512.69 16$42,800.000.3387$14,497.84 17$42,800.000.3166$13,549.38 18$42,800.000.2959$12,662.98 19$42,800.000.2765$11,834.56 20$42,800.000.2584$11,060.33 21$50,000.000.2415$12,075.65 22$50,000.000.2257$11,285.66 23$50,000.000.2109$10,547.34 24$50,000.000.1971$9,857.33 25$50,000.000.1842$9,212.46 26$50,000.000.1722$8,609.77 27$50,000.000.1609$8,046.52 28$50,000.000.1504$7,520.11 29$50,000.000.1406$7,028.14 30$50,000.000.1314$6,568.36 Present Value of Cash Inflows$518,890.26 Thus, NPV is -$365,830.51 Since, NPV is Negative, Project should not be accepted Task 3 - MIRR Financing Rate7% Investment Rate 1 to 10 Years6% 10 to 20 years7.66% 21 to 30 years7.74% Using Cash Flows from NPV Calculation of Present Value of Cash Outflows YearCash OutflowDiscounting Factor @ 7%Present Value 0$600,000.001$600,000.00 10$200,000.000.51$101,669.86 20$200,000.000.26$51,683.80 30$1,000,000.000.13$131,367.12 Present Value of Cash Outflows$884,720.78 YearCash InflowsUsing the Investment Rate 1$39,200.006.00%$212,400.81 2$39,200.006.00%$200,378.12 3$39,200.006.00%$189,035.96 4$39,200.006.00%$178,335.81 5$39,200.006.00%$168,241.33 6$39,200.006.00%$158,718.24 7$39,200.006.00%$149,734.19 8$39,200.006.00%$141,258.67 9$39,200.006.00%$133,262.89 10$39,200.006.00%$125,719.71 11$42,800.007.66%$174,082.70 12$42,800.007.66%$161,691.27 13$42,800.007.66%$150,181.87 14$42,800.007.66%$139,491.73 15$42,800.007.66%$129,562.53 16$42,800.007.66%$120,340.11 17$42,800.007.66%$111,774.14 18$42,800.007.66%$103,817.91 19$42,800.007.66%$96,428.02 20$42,800.007.66%$89,564.15 21$50,000.007.74%$97,828.73 22$50,000.007.74%$90,798.34 23$50,000.007.74%$84,273.19 24$50,000.007.74%$78,216.96 25$50,000.007.74%$72,595.96 26$50,000.007.74%$67,378.91 27$50,000.007.74%$62,536.77 28$50,000.007.74%$58,042.62 29$50,000.007.74%$53,871.43 30$50,000.007.74%$50,000.00 Cash Flows Using Investment Rate$3,649,563.06 MIRR(Cash Flows Using Investment Rate/Present Value of Cash Outflows)^(1/n) - 1 4.84% Thus, MIRR is 4.84% Task 8 In case the Market price of share is 325 per shareIn case the Market price of share is 35.75 per shareSeller - In case the Market price of share is 35 per share Available Capital75000000Available Capital75000000Available Capital75000000 Amount per share325Amount per share35.75Amount per share35 No. of shares Repurchased230769No. of shares Repurchased2097902.0979021No. of shares Repurchased2142857.14285714 No. of Shares Repurchased [VALUE] 230769.230769230782097902.09790209772142857.1428571427