Need help with this case study. I am not sure how to do the calculations using the excel template.

Acct 6220 - Controllership and Financial Leadership Group Case Study #3 AMG, Inc. & Forsythe Solutions Framework for Solving: 1. Do not crunch the numbers for all 7,542 PCs. Focus on the decision for just one PC. There are no economies of scale for leasing or buying one or more than one computer. 2. Use the most aggressive depreciation schedule. This will give purchasing the most advantage over leasing but if the final solution favors leasing for one PC, it will be true for all PCs. Questions: 1. Should you recommend leasing or purchasing the PCs? Why? a. Consider how quickly technology is changing and disposal fees. b. Consider the difference between buying with cash and borrowing to buy. The latter offers tax shields but the savings does not make it the right option. The overall company strategy using leverage should be used to decide. 2. Why are the debt rates different for hardware and software leased over the same term? Hint: Software is not bought it is licensed. Software has neither resale value nor any equity insertion by the lessor. 3. Qualitatively explain if you should choose a rolling or coterminous lease and why. The volume of computers should be the deciding factor when all the leases end at the same time 4. Which is more advantageous, quarterly or monthly takedowns? Explain qualitatively and quantitatively. Hint: AMG will have only 4 lease contracts as opposed to 12. Quarterly takedowns are easier to administer in this case and a quarterly takedown schedule still reasonably reflects the timing of monthly rollouts. Make sure you explain this thoroughly in your answer 5. If you decide on leasing, which payment schedule and lease line are most appropriate and why? Hint: A 36 month lease would a capital lease but check all 6 scenarios to see if AMG would benefit from a 24 month lease and purchase of software. 6. How much would Forsythe make on the AMG deal? a. Determine the equity insertion amount. This the amount of money Forsythe invests in the lease. b. Calculate the value of the computer at the end of the lease c. Calculate the profit per computer and multiply by the number computer. 7. What are the key takeaways to remember when determining future lease versus buy decisions of this magnitude? Here's the basics. Be sure to expand on these and add others. a. Leasing technology can be cheaper than buying b. Operating leases provide off-balance sheet financing and are often preferred c. Technology changes rapidly thus shorter leases are better d. Software is not owned it is licensed through the lessor. Cash permitting, software should be bought outright. AMG Inc and Forsythe Solutions: Lease vs Buy Scenario 1: 1 PC Three-Year Case Buy Option Year 0 Year 1 Year 2 Year 3 (1) Computer costs (2) MACRS on HW Tax deduction (3) (MACRS 0.34) Tax deduction from undepreciated value of PC + (4) software Five-year MACRS depreciation (5) on software (6) Tax deduction on software Total cash flow (1) - (2) + (3) + (4) - (5) + (6) (7) (8) (9) (10) (11) NPV costs NPV tax deduction HW NPV loss on dep. HW NPV tax deduction on software NPV total cash flow $0 Net cost of buying $0 Sold after three years Scenario 2: 1 PC Two-Year Case Buy Option NPV component (24-month buy) (1) Computer costs (2) MACRS on HW Tax deduction (3) (MACRS 0.34) Year 0 Year 1 Year 2 Tax deduction from undepreciated value of PC + (4) software Five-year MACRS depreciation (5) on software (6) Tax break on software Total cash flow (1) - (2) + (3) + (4) - (5) + (6) (7) (8) (9) (10) (11) NPV costs NPV tax deduction HW NPV loss on dep. HW NPV tax deduction on software NPV total cash flow $ - Net cost of buying $ - Lease Options Hardware Cost Software Total 0 24-Month Rates HW SW Equity PV HW PV SW $ Total PV $ PMT HW PMT SW Total monthly payment $ HW LRF SW LRF $0.00 $0.00 - #DIV/0! #DIV/0! Two-Year Lease: NPV of Payments using GST WACC Assume payments are made in advance, lease HW & software Month 1 2 Monthly HW payment Monthly SW payment Total monthly payments Principle payment HW tax payment SW tax payment Tax deduction NPV total $ HW $ SW $ - 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Total HW monthly payments $0.00 $0.00 $0.00 $0.00 $0.00 Principle payment $0.00 $0.00 $0.00 $0.00 $0.00 Tax deduction $0.00 $0.00 $0.00 $0.00 $0.00 HW $ $0.00 $0.00 $0.00 $0.00 $0.00 SW Software is capitalized and depreciated over three years Tax break on SW $0.00 Net software cost $ NPV total $ - $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 6 7 8 9 Assume payments are made in advance, lease HW & buy software 36-Month Rates HW SW Equity PV HW $ PV SW $ PMT HW PMT SW Total monthly payment $ 8% 12% 0% - Three-Year Lease: NPV of Payments using GST WACC Assume payments are made in advance Month 1 2 3 Total HW & SW monthly payments $ $ $ $ Principle payment $0.00 $0.00 $0.00 Tax deduction $ $ $ $ NPV total $ $ $ $ $ 4 $0.00 - 5 $ $ $ $0.00 - $ $ $ $0.00 - $ $ $ $0.00 - $ $ $ $0.00 - $ $0.00 $ $ - 10 11 12 13 14 15 16 17 18 19 20 21 22 $ $ $ $ $ $ $ $ $ $ $ $ $ $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ - $0.00 $0.00 Assume payments are made in advance, lease HW & buy software HW LRF SW LRF #DIV/0! #DIV/0! Total HW monthly payments Principle payment Tax deduction HW NPV $ SW Tax break on SW $0.00 Net software cost $ NPV total $ - $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 23 24 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 23 24 25 26 27 28 29 30 31 32 33 34 35 36 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ - $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Buy vs Lease Options Analysis based on one PC: $750 HW, $250 SW Assumed purchased in the first quarter NPV 24 Months Buy HW & SW $ Lease HW & SW $ Lease HW & Buy SW $ - What would you recommend? 36 Months $ $ $ - 24-Month Leaseline Cash Outflow Assume purchase software & lease hardware Rolling leases #DIV/0! #DIV/0! Total PCs in Year 1: 0 #DIV/0! #DIV/0! 2002 2003 2004 1 Mar Total desktops/month Month 1 HW SW Month 2 HW SW Month 3 HW SW Month 4 HW SW Month 5 HW SW Month 6 HW SW Month 7 HW SW Month 8 HW SW Month 9 HW SW Month 10 HW SW Month 11 HW SW Month 12 HW SW 2 Apr 3 May 4 Jun 5 Jul 6 Aug 7 Sep 8 Oct 9 Nov 10 Dec 11 Jan 12 Feb 13 Mar 14 Apr 15 May 16 Jun 17 Jul 18 Aug 19 Sep 20 Oct 21 Nov 22 Dec 23 Jan 24 Feb #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! $ $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - Mar #DIV/0! Apr #DIV/0! May #DIV/0! Jun #DIV/0! Jul #DIV/0! Aug #DIV/0! Sep #DIV/0! Oct #DIV/0! Nov #DIV/0! Dec #DIV/0! Jan #DIV/0! Feb #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Apr #DIV/0! May #DIV/0! Jun #DIV/0! Jul #DIV/0! Aug #DIV/0! Sep #DIV/0! Oct #DIV/0! Nov #DIV/0! Dec #DIV/0! Jan #DIV/0! $ Feb #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! - $ NPV after tax $ $ #DIV/0! - $ After tax deduction $ $ Total NPV - $ Cash outflow $ $ Mar #DIV/0! - 25 Mar 26 Apr 27 May 28 Jun 29 Jul 30 Aug 31 Sep 32 Oct 33 Nov 34 Dec $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - 35 Jan $ $ Mar $ - $ Apr $ - $ May $ - $ Jun $ - $ Jul $ - $ Aug $ - $ Sep $ - $ Oct $ - $ Nov $ - $ $ Dec $ - $ Jan