I need the answers to the blue colored cells.

Can you please show me how you would calculate the answers using excel, Thank you so much! Please answer all parts.

Morris Company, a small manufacturing firm, wants to acquire a new machine that costs $30,000. Arrangements can be made to lease or purchase the machine. The firm is in the 40% tax bracket. The firm has gathered the following information about the two alternatives: Lease: Morris would obtain a 5-year lease requiring annual end-of-year lease payments of $10,000. The lessor would pay all maintenance costs; insurance and other costs would be borne by the lessee. Morris would be given the right to exercise its option to purchase for $3,000 at the end of the lease term. Purchase: Morris can finance the purchase of the machine with an 8.5%, 5-year loan requiring annual end-of-year installment payments. The machine would be depreciated under MACRS using a 5-year recovery period. The exact depreciation rates over the next six periods would be 20%, 32%, 19%, 12%, 12%, and 5%, respectively. Morris would pay $1,200 per year for a service contract that covers all maintenance costs. The firm plans to keep the machine and use it beyond its 5-year recovery period. To Do Create a spreadsheet similar to Tables 17.1, 17.2, and 17.3 to answer the following: a. Calculate the after-tax cash outflow from the lease for Morris Company. b. Calculate the annual loan payment. c. Determine the interest and principal components of the loan payments. d. Calculate the after-tax cash outflows associated with the purchasing option. Calculate and compare the present values of the cash outflows associated with both the leasing and purchasing options. f. Which alternative is preferable? Explain. Solution Cost of new machine Tax rate Lease payment Option to purchase at end of lease Loan rate Term of loan/lease (years) Annual cost of maintenance 30,000 40% 10,000 3,000 8.50% $ 1,200 a. Calculate the after-tax cash outflow from the lease for Morris Company. 10,000 40% Before-tax payment Tax rate After-tax cash outflow for years 1 to 4 Cash outflow for year 5 b. Calculate the annual loan payment. $ 30,000 Loan Maturity (years) Discount Rate Payment 8.50% c. Determine the interest and principal components of the loan payments. Discount Rate 8.50% Beginning Balance Year Payment Principal Interest End Balance $ 30,000 d. Calculate the after-tax cash outflows associated with the purchasing option. Tax rate Cost of new machine 40% 30,000 Total Deductions After-tax Cash Outflow Depreciation Interest Tax Shield Maintenance End of Year Loan Payment Costs $ 1,200 - $ 1,200 1,200 | $ 1,200 1,200 MACRS 20.0% 32.0% 19.0% 12.0% 12.0% Calculate and compare the present values of the cash outflows associated with both the leasing and purchasing options. After-tax cost of debt 5.10% Leasing After-Tax Cash Outflow Purchasing After-Tax Cash Outflow PV $ - PV $ - PV of Cash Outflow f. Which alternative is preferable? Explain. Since the present value of cash outflows for is lower than for Ithe alternative is preferred. Morris Company, a small manufacturing firm, wants to acquire a new machine that costs $30,000. Arrangements can be made to lease or purchase the machine. The firm is in the 40% tax bracket. The firm has gathered the following information about the two alternatives: Lease: Morris would obtain a 5-year lease requiring annual end-of-year lease payments of $10,000. The lessor would pay all maintenance costs; insurance and other costs would be borne by the lessee. Morris would be given the right to exercise its option to purchase for $3,000 at the end of the lease term. Purchase: Morris can finance the purchase of the machine with an 8.5%, 5-year loan requiring annual end-of-year installment payments. The machine would be depreciated under MACRS using a 5-year recovery period. The exact depreciation rates over the next six periods would be 20%, 32%, 19%, 12%, 12%, and 5%, respectively. Morris would pay $1,200 per year for a service contract that covers all maintenance costs. The firm plans to keep the machine and use it beyond its 5-year recovery period. To Do Create a spreadsheet similar to Tables 17.1, 17.2, and 17.3 to answer the following: a. Calculate the after-tax cash outflow from the lease for Morris Company. b. Calculate the annual loan payment. c. Determine the interest and principal components of the loan payments. d. Calculate the after-tax cash outflows associated with the purchasing option. Calculate and compare the present values of the cash outflows associated with both the leasing and purchasing options. f. Which alternative is preferable? Explain. Solution Cost of new machine Tax rate Lease payment Option to purchase at end of lease Loan rate Term of loan/lease (years) Annual cost of maintenance 30,000 40% 10,000 3,000 8.50% $ 1,200 a. Calculate the after-tax cash outflow from the lease for Morris Company. 10,000 40% Before-tax payment Tax rate After-tax cash outflow for years 1 to 4 Cash outflow for year 5 b. Calculate the annual loan payment. $ 30,000 Loan Maturity (years) Discount Rate Payment 8.50% c. Determine the interest and principal components of the loan payments. Discount Rate 8.50% Beginning Balance Year Payment Principal Interest End Balance $ 30,000 d. Calculate the after-tax cash outflows associated with the purchasing option. Tax rate Cost of new machine 40% 30,000 Total Deductions After-tax Cash Outflow Depreciation Interest Tax Shield Maintenance End of Year Loan Payment Costs $ 1,200 - $ 1,200 1,200 | $ 1,200 1,200 MACRS 20.0% 32.0% 19.0% 12.0% 12.0% Calculate and compare the present values of the cash outflows associated with both the leasing and purchasing options. After-tax cost of debt 5.10% Leasing After-Tax Cash Outflow Purchasing After-Tax Cash Outflow PV $ - PV $ - PV of Cash Outflow f. Which alternative is preferable? Explain. Since the present value of cash outflows for is lower than for Ithe alternative is preferred