Capital Budgeting Case / Problem Parameters: A capital project and budgeting decision is being considered that would involve an expansion, along with a replacement of some old equipment. The project is expected to have a 6-year life for the firm.

The new project will require new equipment costing $2000k ($2 million), which will be depreciated straight-line to a book value of $200k at the end of 6 years.

Due to new energy efficient technology, replacing the old equipment with the new more efficient equipment will generate an immediate tax credit of 5% of the equipment's cost. Although tax laws may vary, for simplicity assume this tax credit does not reduce the $2000k cost of the new equipment for the straight-line depreciation calculation.

This project will replace some existing equipment which currently has a book value (BV) of $200k and an estimated market salvage value of $375k. Assume that this old equipment is sold in a separate transaction at time zero and that this sale also does not reduce the $2000k cost of the new equipment for the straight-line depreciation calculation.

The expansion will require an additional investment in NWC of $200k at time zero, which will be recovered at the end of the 6-year life.

Sales are expected to increase by $1000k the first year and grow by 15% in years 2 and 3, then by 5% annually during the remaining 6-year life.

Cost of goods sold is forecasted to be 45% of the increased sales, and other selling and general administrative expenses are forecasted to be 10% of the increased sales.

It is forecasted that the new equipment will have a salvage value of $300k at the end of the project's 6-year life (and the $200k book value from the straight-line depreciation).

The firm's weighted average cost of capital (WACC) for projects of this risk level is 8%. The firm's marginal tax rate is T = 40%.

Use the Excel template to complete the cash flow model and the capital budgeting analysis. Your Excel analysis should clearly indicate the cash flow model analysis timeline and should provide the project's: ? NPV, ? IRR, ? PBP, ? PI, ? Scenario Analysis => completion of the scenario analysis grids which provide NPV of the entire cash flow model under the different scenarios indicated in the grids, and ? the project's NPV Profile graph (with proper labels). use Excel to show formulas.

Yellow highlighted cells are cells for inputs. Team should verify all other calculations & formats C D E F G H K M N 0 Q R S Inputs ATSV old @ t=0 ATSV formula = Names of all members who contributed to overall project: Include an NPV Profile Equipment Note: Only 1 NPV Profile for base case model should be completed Tax Credit Depreciaton per year C K A W N P Sales period 1 growth: g yrs 2-3 = 15%% g yrs 4-6 = 5% COGS %of sales SG &.A exp. %of sales ATSV new @ t=6 Operating Life CF Some Tips for NPV Scenario / Risk Analysis = NPV of Full Project under different "What if?" Assumptions for CoGS%% (Top Table) & SalesYr1 (Bottom Table): Time 0 1 2 3 4 6 1. Complete the tables below to report NPVs of Projects Net OFs as you change the column input variable (CoG5%% or SalesYr) in the origianl OF Model (to left) one at a time. Sale - DOGS 2. Top Table => Change CoGS* in the base model (cell D10) to the scenario column's new "What if?' value & then copy/paste the new NPVs from Column D (below Row 45] for the 5 scenario rates - SG&A expenses into the scenario table for the column. Do this 1 column (scenario) at a time . Center column (scenario) is the base case & thus should be identical in both tables. - Depreciation = EDIT 3. Bottom Table => Change Yr1 Sales in the base model (cell E16) to the scenario column value & copy/paste the new NPVs from Column D (below Row 45) for the 5 scenario rates into the scenario table for the column. Do this 1 column (scenario) at a time. Again, center column (scenario] is the base case & should be identical in both tables. -Taxes (4095) = Net Income 4. Remember to reset your Cash Flow Model to the Base Case Values when done populating your scenario tables. + Depreciation = Operating CF Time O Investments NPV Analysis Grid: NPV vs Discount Rate & Cost of Goods Sold (CoGS] Percent Ranges Equipment ATSV old COGS-> Coosbase -20% CoGsbase -10%% COGS Base % Colsbasel + 10%% Colsbase's +20%% Tax credit COGS-> Le basak'0.8 Le basek'0.9 La baek 1.0 La basak *1.1 La book*1.2 NWC COGS-> Cost of Capital 49% Terminal Non-OCF: 6% ATSV new @ 1-6 8% NWC 10% = Net Cash Flow so So So So So SO SO 12% = Cummulative OF $0 so so so soCost of Capital INPV IRR = NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges POP = Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 PI= Sales Yr.1 -> 2095 -10% +10 +20% Sales Yr.1 -> La banak *0.9 Le banook*1.0 i.a banjok*1.2 NPV Data Table SNPV in Cells: Rates 0-26%% by 2 increments} NPV Cost of Capital 4% 695 895 10% 12% Notes / Reminders: NPV Profile Use NPVs from table on left to populate grids for different scenarios