Calculations should be done in Excel format

Case

Advanced Computers has decided to proceed with the manufacture and distribution of the virtual keyboard (VK) the company has developed. To undertake this venture, the company needs to obtain equipment for the production of the microphone for the key board. Because of the required sensitivity of the microphone and its small size, the company needs specialized equipment for production.

Lucas Johnson, the company Treasurer, has found a vendor for the equipment. Memtech Acoustical Equipment has offered to sell Advanced Computers the necessary equipment at a price of $4.3 million. Because of the rapid development of new technology, the equipment will be fully depreciated after four years (i.e., 48 months) with the straight-line depreciation approach. The depreciation expense incurs at the end of each month. At the end of the fourth year, the market value of the equipment is expected to be $450,000.

Alternatively, the company can lease the equipment from Hendrix Leasing. Hendrix Leasing proposed the following lease contract to Advanced Computers: The lease contract calls for monthly payment of $93,000 due at the beginning of each month for four years. Additionally, Advanced Computers must make a security deposit $200,000 at the beginning of the lease contract (i.e., at the beginning of the first month) and the deposit will be returned when the lease expires at the end of the last month.

Advanced Computers can borrow a loan with the interest rate of 10% per year before taxes to finance the equipment. The company has a marginal tax rate of 21%.

A financial analyst who is working for Mr. Johnson was asked to make an analysis and then make a recommendation to him whether Advanced Computers should buy or lease the equipment based on the lease terms proposed by Hendrix, Inc.

In addition, Mr. Johnson is planning to negotiate the lease terms with Hendrix Leasing to get a better deal. Suppose that only monthly payment of the lease is negotiable and other terms cannot be negotiated. Hendrix Leasing can borrow a loan with the interest rate of 4% per year before taxes and its marginal tax rate is also 21%. Mr. Johnson asked the analyst to recommend a monthly payment that is lower than $93,000. However, the proposed monthly payment will make net advantage to leasing (NAL) for both Advanced Computers and Hendrix Leasing positive.

1. Calculate net advantage to leasing (NAL) for Advanced Computers based on the lease contract proposed by Hendrix Leasing.

2. Calculate the monthly lease payment to make NAL for Advanced Computers breakeven, i.e., NAL=0.

3. Calculate the monthly lease payment to make NAL for Hendrix Leasing breakeven, i.e., NAL=0.

4. After the range of the monthly lease payment that may make NAL for Advanced Computers and Hendrix Leasing positive has been figured, recommend a lease payment to help Mr. Johnson negotiate a better deal with Hendrix Leasing.

5. Calculate NAL for Advanced Computers and Hendrix Leasing based on the monthly lease payment recommended to verify that NALs for both lessee and lessor are positive.

6 One-page memo to summarize the issue and the quantitative analysis. Based on the analysis, make recommendations about whether Advanced Computers should buy or lease the equipment based on the lease term proposed by Hendrix Leasing. What is the monthly lease payment range to help Mr. Johnson negotiate with Hendrix Leasing to get a better deal? What is the monthly lease payment recommended to Mr. Johnson to help him propose to Hendrix Leasing? Should the monthly lease payment proposed could benefit both Advanced Computers and Hendrix Leasing?