Question

EXCEL Complete probleman Onexcell Annual property taxes 47. Lb.2 & LO.3 (NPV; Pl; sensitivity analysis) Westside Warehouse is considering peengineering some production operations with automated equipment. The new equipment would have an initial cost of $5,000,000 including installation. The vendor has indicated that the equipment has an expected life of seven years with an estimated salvage value of $400,000. Estimates of annual labor savings and incremental costs associated with operating the new equipment follow. bonAnnual labor cost savings (14 workers) $950,000 Annual maintenance costs 40,000 Annual property taxes 28,000 Annual insurance costs 44,000 a. Assuming the company's cost of capital is 6 percent, compute the NPV of the auto- mated equipment. (Ignore taxes.) b. Based on the NPV, should the company invest in the new equipment:

Answer 1

Please see the table below. Please be guided by the second column titled “Linkage” to understand the mathematics. The last row highlighted in yellow is your answer. Figures in parenthesis, if any, mean negative values. All financials are in $. Adjacent cells in blue contain the formula in excel I have used to get the final output. Part (a), (b) and (c) have been ordered inline. Formula screenshot is also attached. Part (d) and (e) have been solved after formula screenshot.

U Linkage 4 157 Year 0 1 2 3 6 7 158 Initial investment 159 Labor cost savings A (5,000,000) 950,000 950,000 950,000 (40,000) (40,000) (40,000) (28,000) (28,000) (28,000) (44,000) (44,000) (44,000) 950,000 950,000 950,000 950,000 (40,000) (40,000) (28,000) (28,000) (44,000) (44,000) (40,000) (40,000) (28,000) (44,000) 160 maintenance cost 161 Property taxes D (28,000) E (44,000) 162 insurance costs F 163 Salvage value 400,000 G Sum of all above (5,000,000) 838,000838,000 838,000 838,000 164 Net cashflows 838,000 838,000 1,238,000 165 166 Cost of capital 6% 167 168 Part (a) NPV Excel formula (55,941.51) =NPV(N166,0164:U164)+N164 169 Since NPV is negative, the company should not invest in the project 170 Part (b) 171 172 Part (c) Profitability index Excel formula 0.991+N168/-N158 T P O

`Formula screenshot:

Part (d) We will have to resort to Goal Seek function in excel. Our objective is to find that value of labor cost savings at which NPV is zero.

Q R M Linkage A EN157+ 1 0157+1 #$157+1 P157+1 Q157+1 #R157 + 1 T157+ 1 157 Year -5000000 158 Initial investment 159 Labor cost savings T159 950000 0159 0159 $159 P159 R159 C -40000 0160 P160 0160 $160 160 maintenance cost R160 T160 D E 0161 P161 Q161 $161 T161 161 Property taxes -28000 R161 44000 0162 P162 $162 T162 162 insurance costs Q162 R162 F 163 Salvage value 164 Net cashflows 165 166 Cost of capital 400000 =SUM(0158:0163-SUMIP158:P163) =SUM(Q158:Q163)-SUM(R158:R163)SUM(S158:5163) SUMIT158:T163-SUM(U158:U163) G Sum of all aboveSUM(N158:N163) 0.06 167 Excel formula NPV(N166,0164:U164) N164 168 Part (a) NPV 169 170 Part (b) Since NPV is negative, the company should not invest in the project 171 172 Part (e) Profitability index Excel formula =1+N168 / -N158

Data tab ===> What If Analysis ==> Goal Seek

O R S T U Linkage 158 Initial investmen A 159 Labor cost savin B 160 maintenance cosC 161 Property taxes D 162 insurance costs E 21 4 157 Year 0 1 6 7 (5,000,000) 950,000950,000 (40,000) (40,000) (28,000) 950,000 950,000 950,000 950,000 950,000 (40,000) (40,000) (40,000) (40,000) (28,000) (28,000)| (44,000) (44,000)| (40,000) (28,000) (28,000) (28,000) (28,000) (44,000) (44,000) (44,000) (44,000) (44,000) 163 Salvage value 164 Net cashflows all above 400,000 838,000 838,000 (5,000,000) 838,000 838,000 838,000 838,000 1,238,000 165 ? Goal Seek 166 Cost of capital 6% Set cell: $N$168 167 To yalue: Excel formu (55,941.51) 168 Part (a) NPV By changing cell: $o$159| 169 Since NPV is negative, the company should not invest in the project 170 Part (b) Cancel OK 171 172 Part (c) Profitab Excel formu 0.99 M

Goal seek will give you an output of $  960,021 as minimum labor cost savings to make the project just NPV 0 and push it in acceptable zone.

Part (e)

A capital budgeting decision is by and large dictated by numerical considerations. However various qualitative considerations are also involved such as those mentioned below. Because of these considerations, a company may select an investment against their investment criteria or drop a project that otherwise meet their investment criteria.

• Business and professional ethics: may not allow a company to enter into businesses like gambling, casino, lottery etc. even if they generate rate of return in excess of hurdle rate.
• Safety and health issues: a company decides not to enter into a terrorism prone area even if the project guarantees a handsome return
• Company culture
• Environmental concerns
• Reputation
• Alignment with the long term strategy, mission and vision
• Urgency
• Employees’ morale
• Investors and customers image
• Legal matters
• Regulatory environment