Capital Expenditure Analysis

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 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 for differences in the assumptions for Epsilon vs Aguilera.

The Example
EXAMPLE SCENARIO:
Aguilera Acoustics, Inc., (AAI) projects unit sales for a new seven-octave voice emulation implant as follows:

Year Unit Sales
1 67,500
2 83,900
3 98,700
4 86,000
5 72,000
Production of the implants will require $1,500,000 in net working capital to start and additional net working capital investments each year equal to 15 percent of the projected sales increase for the following year. Total fixed costs are $1,950,000 per year, variable production costs are $230 per unit, and the units are priced at $355 each. The equipment needed to begin production has an installed cost of $18,500,000. Because the implants are intended for professional singers, this equipment is considered industrial machinery and thus qualifies as seven-year MACRS property. The accounting department supplies you with the following depreciation figures:

Year MACRS Depreciation
1 2,643,650 (14.29%)
2 4,530,650 (24.49%)
3 3,235,650 (17.49%)
4 2,310,650 (12.49%)
5 1,652,050 (8.93%)
In five years, this equipment can be sold for about 20 percent of its acquisition cost, and will have a book value of $4,127,350. AAI is in the 35 percent marginal tax bracket and has a required return on all its projects of 15 percent.

Determine the Payback Period, Internal Rate of Return and Net Present Value of this project.

 
Here’s how the template would be populated with the information for Aguilera:

First, organize the inputs into one easy-to-navigate area. This facilitates changing figures if your assumptions change.

Second, create the working area. This is where the computations will happen. Every cell in this area is either a computation or a cell reference. I do not enter any data or type any hard numbers in this area.

Notice the payback period: I don’t 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 $823,517.50 of the initial investment. That amount divided by the next year’s cash flow (823517.50 / 7274228) tells me the remainder is 0.11. Add that to the 3 full years I counted and I get the payback period of 3.11

NOTE ABOUT CHANGE IN NWC
The author bases his net working capital need on the anticipated sales increase for next year:

The final year, however, is when the total amount of NWC is no longer needed, so the company “releases” that total amount. The Year 5 formula is different:

Year 5 has a cash inflow of the total amount of NWC involved in the project (Notice the minus sign before the word SUM in the formula).

 
The Project
Consider the following scenario:

Epsilon Products, Inc., (EPI) projects unit sales for a new device as follows:
Year Unit Sales
1 87,500
2 105,000
3 119,000
4 108,000
5 92,000

Production of the device will require $1,000,000 in net working capital to start and additional net working capital investments each year equal to 9 percent of the projected sales increase for the following year. Total fixed costs are $1,450,000 per year, variable production costs are $250 per unit, and the units are priced at $350 each. The equipment needed to begin production has an installed cost of $24,000,000. This equipment qualifies as seven-year MACRS property, and the accounting department supplies you with the following depreciation figures:

Year MACRS Depreciation
1 3,429,600 (14.29%)
2 5,877,600 (24.49%)
3 4,197,600 (17.49%)
4 2,997,600 (12.49%)
5 2,143,200 (8.93%)

In five years, this equipment can be sold for about 15 percent of its acquisition cost, and will have a book value of $5,354,400. EPI is in the 35 percent marginal tax bracket and has a required return on all its projects of 16 percent.
Determine the Payback Period, Internal Rate of Return and Net Present Value of this project.

Required
• Use the Excel template on the class webpage (Capital Budgeting Template.xls) and analyze The Project, for Epsilon, above.
• You must complete this in Microsoft Excel, not another spreadsheet program (i.e. Do not use Google Sheets).