Templates

Capital Project Appraisal Workbook

The arithmetic of a capital expenditure decision: incremental cash flows with working capital and salvage, 100% bonus depreciation against MACRS, the WACC and hurdle rates, NPV, IRR, MIRR, payback, profitability index, sensitivity and scenarios, mutually exclusive options, lease versus buy and capital rationing: nine tabs, one fictional company, no macros.

Enter your own project. With the book's case, a $4,500,000 production line, the workbook computes a net present value of $739,000 at an 11% hurdle, an IRR of 15.0%, a payback of 4.9 years, a break-even at 90.8% of forecast revenue, a net advantage to leasing of -$371,000, and the best combination of seven projects within an $8,400,000 budget. A Checks tab runs 59 checks against the book.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 212 free models.

Capital_Project_Appraisal_Workbook.xlsx (50 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind the workbook: The Capital Budgeting Handbook, PDF and paperback, with the full case, the Line 7 capital request and the checklists.

What is in the workbook

Inputs
All the assumptions of the Ellsmere case in one place: the Line 7 capital cost, revenue, growth, variable and fixed costs, erosion, the rent given up, working capital, salvage, the tax rate, the depreciation switch and the state share of the tax rate that follows MACRS; the inputs of the cost of capital and the hurdle rates by class; the palletizing and forklift options; the lease offer; and the capital envelope and the contingency rate. Replace them with your own project's figures to use the workbook.
Depreciation
The MACRS 7-year percentages from Table A-1 of IRS Publication 946, the deductions and tax shields under 100% bonus depreciation and under MACRS, and the present value of each shield at the Class 2 hurdle. It shows the $225,000 that the timing of the deduction is worth to Line 7, and the South Carolina adjustment, year by year, with its present value of -$36,000 (Chapter 4).
Cash Flows
The full Line 7 table of Chapter 3, year by year: revenue, contribution, fixed costs, erosion, rent given up, EBITDA, tax depreciation, tax, working capital, salvage, capital spend, net cash flow, discount factors, present values and the cumulative rows used for payback.
Metrics
The cost of equity, the after-tax cost of debt and the WACC of Chapter 5; the net present value, internal rate of return, modified internal rate of return, payback, discounted payback, both versions of the profitability index and the accounting rate of return of Chapters 6 to 8; the year 1 book and tax results; and the net present value at the WACC and at two points above the hurdle.
Sensitivity
The sensitivity table of Chapter 11, built by recomputing the whole cash flow for each case in a single row, the break-even revenue and variable cost ratio, and the three scenarios with their probability-weighted net present value. Add rows to test other combinations.
Exclusive Choices
The robotic cell against the conveyor, with the incremental cash flows and the crossover rate (Chapter 9); the mold program with two internal rates of return (Chapter 7); and the forklift fleets compared by equivalent annual cost and over a common 40-year horizon.
Lease vs Buy
The comparison of Chapter 10 at the after-tax cost of debt: buying with bonus depreciation, leasing with payments in advance, buying with MACRS, the no-tax case, the rate implicit in the lease and the break-even payment.
Capital Plan
The seven candidates of Chapter 13, with Line 7 at its capital requested, contingency included, and at its value after the state adjustment, ranked by profitability index and selected until the envelope is spent, then all 128 combinations tested to find the one with the highest total net present value, the best plans without and with Line 7, and the net present value at which Line 7 would be in the best plan.
Checks
A list of 59 figures printed in the book, each compared with the workbook's formulas. With the book's inputs, the tab shows ALL CHECKS OK. With your own inputs, it will show differences by design.

Every assumption is shaded in a light color on the Inputs tab; every other cell is a formula, with no macros, no links to other files and no circular references. Every ratio is guarded against an empty divisor. Replace the inputs with your own company's figures; the Checks tab will then show differences from the book by design.

From The Capital Budgeting Handbook. To read: a capital budgeting example with taxes · NPV versus IRR conflicts · lease versus buy for equipment.