Capital Budgeting Spreadsheet Project

Introduction

This project is an application of capital budgeting and cash flow analysis. The Excel template is similar to what I might build in the so-called real world, and could be very useful to have on a flash drive somewhere for future reference

To make the project easier, I recommend using the template to build the example problem (based on Holliday Manufacturing, as discussed in the video) before trying the actual problem in the template. Once you have the example built and the numbers match the illustrations below, you know your spreadsheet works correctly. Then substitute the assumptions for the actual project and the spreadsheet will do the computations. Watch the depreciation assumptions on Gamma vs Holliday.

The Example

Holliday Manufacturing is considering the replacement of an existing machine. The new machine costs $1.2 million and requires installation costs of $150,000. The existing machine can be sold currently for $185,000 before taxes. The old machine is 2 years old, cost $800,000 when purchased, and has a $384,000 book value and a remaining useful life of 5 years. It was being depreciated under MACRS using a 5-year recovery period, so it has the final 4 years of depreciation remaining. If it is held for 5 more years, the machines market value at the end of year 5 will be zero. Over its 5-year life, the new machine should reduce operating costs by $350,000 per year, and will be depreciated under MACRS using a 5-year recovery period. The new machine can be sold for $200,000 net of removal and cleanup costs at the end of 5 years. A $25,000 increase in net working capital will be required to support operations if the new machine is acquired. The firm has adequate operations against which to deduct any losses experienced on the sale of the existing machine. The firm has a 9% cost of capital and is subject to a 40% tax rate. Should they accept or reject the proposal to replace the machine?

Heres how the template would be populated with the information for Holliday:

Notice the payback period: I dont normally use a fancy formula to automate the computation, it is too much trouble for the time savings. At the end of Year 3, the company has recouped all but $234,600of the initial investment. That amount divided by the next years cash flow (234600/258800) tells me the remainder is 0.91. Add that to the 3 full years I counted and I get the payback period of 3.91

The Project

Consider the following scenario:

Gamma Inc. is considering the replacement of an existing machine. The new machine costs $1.8 million and requires installation costs of $250,000. The existing machine can be sold currently for $125,000 before taxes. The existing machine is 3 years old, cost $1 million when purchased, and has a $290,000 book value and a remaining useful life of 5 years. It was being depreciated under MACRS using a 5-year recovery period. If it is held for 5 more years, the machines market value at the end of year 5 will be zero. Over its 5-year life, the new machine should reduce operating costs by $650,000 per year, and will be depreciated under MACRS using a 5-year recovery period. The new machine can be sold for $150,000 net of removal and cleanup costs at the end of 5 years. A $30,000 increase in net working capital will be required to support operations if the new machine is acquired. The firm has adequate operations against which to deduct any losses experienced on the sale of the existing machine. The firm has a 15% cost of capital, is subject to a 40% tax rate and requires a 42-month payback period for major capital projects.

Should they accept or reject the proposal to replace the machine? What is the NPV? What is the IRR? What is the payback period?

Use the following table for depreciation:

5-Year MACRS

Year 1 20%

Year 2 32%

Year 3 19%

Year 4 12%

Year 5 12%

Year 6 5%

Required

Use the Excel template on the class webpage (Capital Budgeting Template.xls) and analyze the scenario above.

Answer the question, Should they accept or reject the proposal to replace the machine? below the area of the spreadsheet with the NPV, IRR and PBP.

Example Capital Budgeting Problem [Compatibility Mode] Excel FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVTEW VIEW Michael Dimond- SplitView Side by Side Formula Bar 2/ Synchronous Scrolling e Reset Window Position Hide Zoom 100% Zoom to New Arrange Freeze Selection Window Switch Macros Windows Page Break Page Custom Gridlines.] Headings All Panes ,Unhide Preview Layout Views Workbook Views Show Zoom Window Macros fr Example Problem 1 Example Problem 3 Assumptions 4 Tax Rate Old machine purchased for Old Machine Book Value 40% 800,000 384,000 6 9 Initial Investment 10 11 Installed cost of new asset 1,200,000 150,000 12 Cost of the new machine 13 Installation costs 14 Total cost of new machine 15 After-tax proceeds from sale of old asset 16 Proceeds from sale of existing machine 17 Tax on sale of existing machine 18 Total after-tax proceeds from sale 19 Increase in net working capital 20 Initial investment 21 22 Terminal CF 23 After-tax proceeds from sale of new asset 24 Proceeds from sale of new asset 25 Tax on sale of new asset 26 Total after tax proceeds-sale of new asset 27 Change in net working capital 28 Terminal cash flow 1,350,000 185,000 (79,600) old machine sold at less than book value. Tax rate = 40% 264,600 25,000 1,110,400 200,000 53,000 BV of new machine 67,500 147,000 25,000 172,000 This cash flow happens at the end of Year 5 Blank Forat Gamma Hollidayhet Sheet3 - + 130%