Capital Budgeting Problem Parameters:

Consider the following expansion capital budgeting problem.

The project is expected to have a 6-year life for the firm. currently has a book value (BV) of $200k and an estimated market salvage value of $375k. The new project will require new equipment costing $2000k, which will be depreciated straight-line to a book value of $200k at the end of 6 years.

generate an immediate tax credit of 5% of the equipments cost.

The expansion will require an additional investment in NWC of $200k.

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 projects 6 year life.

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

Use the Excel template to complete the capital budgeting analysis. Your Excel analysis should clearly indicate the cash flow analysis timeline and should provide the projects NPV, IRR, PBP, PI, and also illustrate the projects NPV Profile.

K L M N O P Q R S T U V W K L M N P Names of all members who contributed: Include an NPV Profile D E F G H I J 2 Yellow highlighted cells are cells for inputs. Team should verify all other calculations & formats D E F G H I J Inputs 5 ATSV old @t=0 ATSV formula - 6 Equipment 7 Tax Credit 8 Depreciaton per year 9 Sales period 1 growth: gyrs 2-3 15% gyrs 4-6 5% 10 COGS %of sales 11 SG&A exp. %of sales 12 ATSV new@t=6 + WN- 14 Operating Life CFs 15 Time 16 Sales 17 - COGS 18 - SG&A expenses 19 - Depreciation 20 = EBIT 21 - Taxes (40%) 22 - Net Income 23 Depreciation 24 - Operating CF NPV Scenario / Risk Analysis: Complete the grids below to report 5x5 Grids of NPV vs input variable changes noted 26 Time O Investments 27 Equipment 28 ATSV old 29 Tax credit 30| JwL NPV Analysis Grid: NPV vs Discount Rate & Salvage Value Ranges SV -> SV-20% SV-10% SV Base SV-10% SV-20% 300 ATSV -> ATSV-20% ATSV-10%ATSV Bas ATSV-10%ATSV-20% $NPV in Cells: Cost of Capital 4% 6% 32 Terminal Non-OCF: 33 ATSV new@t=6 34 NWC 35 - Net Cash Flow 36 - Cummulative CF 8% 10% 12% $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 1 8 38 Cost of Capital 39 NPY 40 IRR = 41 PBP - NPY Analysis Grid: NPY vs Discount Rate & Year 1 Sales Ranges 421 PIE Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 $NPV in Cells: Cost of Capital 4% 44 NPV Data Table Rates NPYI 6% 87 10% 1274 8898 Notes? Reminders: NPV Profile Use NPVs from table on left to populate grids for different scenarios K L M N O P Q R S T U V W K L M N P Names of all members who contributed: Include an NPV Profile D E F G H I J 2 Yellow highlighted cells are cells for inputs. Team should verify all other calculations & formats D E F G H I J Inputs 5 ATSV old @t=0 ATSV formula - 6 Equipment 7 Tax Credit 8 Depreciaton per year 9 Sales period 1 growth: gyrs 2-3 15% gyrs 4-6 5% 10 COGS %of sales 11 SG&A exp. %of sales 12 ATSV new@t=6 + WN- 14 Operating Life CFs 15 Time 16 Sales 17 - COGS 18 - SG&A expenses 19 - Depreciation 20 = EBIT 21 - Taxes (40%) 22 - Net Income 23 Depreciation 24 - Operating CF NPV Scenario / Risk Analysis: Complete the grids below to report 5x5 Grids of NPV vs input variable changes noted 26 Time O Investments 27 Equipment 28 ATSV old 29 Tax credit 30| JwL NPV Analysis Grid: NPV vs Discount Rate & Salvage Value Ranges SV -> SV-20% SV-10% SV Base SV-10% SV-20% 300 ATSV -> ATSV-20% ATSV-10%ATSV Bas ATSV-10%ATSV-20% $NPV in Cells: Cost of Capital 4% 6% 32 Terminal Non-OCF: 33 ATSV new@t=6 34 NWC 35 - Net Cash Flow 36 - Cummulative CF 8% 10% 12% $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 1 8 38 Cost of Capital 39 NPY 40 IRR = 41 PBP - NPY Analysis Grid: NPY vs Discount Rate & Year 1 Sales Ranges 421 PIE Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 $NPV in Cells: Cost of Capital 4% 44 NPV Data Table Rates NPYI 6% 87 10% 1274 8898 Notes? Reminders: NPV Profile Use NPVs from table on left to populate grids for different scenarios