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.

Transcribed Image Text:

Yellow highlighted cells are cells for inputs. Team should verify all other calculations & formats ATSV old @t=0 Equipment Tax Credit C Depreciaton per year Sales period 1 COGS %of sales SG&A exp. %of sales ATSV new @ t-6 Operating Life CFs Time Sales -COGS SG&A expenses Depreciation = EBIT -Taxes (40%) = Net Income +Depreciation =Operating CF Time 0 Investments Equipment ATSV old Tax credit NWC Terminal Non-OCF: D Inputs E F G ATSV formula = H 1 K L growth: g yrs 2-3= 15% g yrs 4-6 5% 1 ATSV new @t-6 NWC = Net Cash Flow $0 $0 $0 $0 $0 $0 = Cummulative CF $0 $0 $0 $0 $0 $0 $0 N P 1 Names of all members who contributed to overall project: 3 Q R S Include an NPV Profile T Note: Only 1 NPV Profile for base case model should be completed Some Tips for NPV Scenario / Risk Analysis = NPV of Full Project under different "What if?" Assumptions for COGS% (Top Table) & SalesYr1 (Bottom Table): 1. Complete the tables below to report NPVs of Projects Net CFs as you change the column input variable (COGS% or SalesYr1) in the origianl CF Model (to left) one at a time. 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 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. 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. 4. Remember to reset your Cash Flow Model to the Base Case Values when done populating your scenario tables. NPV Analysis Grid: NPV vs Discount Rate & Cost of Goods Sold (COGS) Percent Ranges COGS-> COGS-> COGS-> Cost of Capital 4% 6% 8% 10% 12% COGSbase%-20% i.e base*0.8 COGSbase % -10% i.e base*0.9 COGS Base % i.e base*1.0 COGSbase % +10% i.eba COGSbase % +20% i.e base*1.2 Yellow highlighted cells are cells for inputs. Team should verify all other calculations & formats ATSV old @t=0 Equipment Tax Credit C Depreciaton per year Sales period 1 COGS %of sales SG&A exp. %of sales ATSV new @ t-6 Operating Life CFs Time Sales -COGS SG&A expenses Depreciation = EBIT -Taxes (40%) = Net Income +Depreciation =Operating CF Time 0 Investments Equipment ATSV old Tax credit NWC Terminal Non-OCF: D Inputs E F G ATSV formula = H 1 K L growth: g yrs 2-3= 15% g yrs 4-6 5% 1 ATSV new @t-6 NWC = Net Cash Flow $0 $0 $0 $0 $0 $0 = Cummulative CF $0 $0 $0 $0 $0 $0 $0 N P 1 Names of all members who contributed to overall project: 3 Q R S Include an NPV Profile T Note: Only 1 NPV Profile for base case model should be completed Some Tips for NPV Scenario / Risk Analysis = NPV of Full Project under different "What if?" Assumptions for COGS% (Top Table) & SalesYr1 (Bottom Table): 1. Complete the tables below to report NPVs of Projects Net CFs as you change the column input variable (COGS% or SalesYr1) in the origianl CF Model (to left) one at a time. 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 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. 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. 4. Remember to reset your Cash Flow Model to the Base Case Values when done populating your scenario tables. NPV Analysis Grid: NPV vs Discount Rate & Cost of Goods Sold (COGS) Percent Ranges COGS-> COGS-> COGS-> Cost of Capital 4% 6% 8% 10% 12% COGSbase%-20% i.e base*0.8 COGSbase % -10% i.e base*0.9 COGS Base % i.e base*1.0 COGSbase % +10% i.eba COGSbase % +20% i.e base*1.2