Question

Walmart is trying to compete with their competitors by enhancing the shipment. To do so, the company wants to replace its fork trucks with 25 robots. The new robotic system will have the following characteristics Initial cost per robot: $95,000 Annual Maintenance cost per robot: $450 increasing by $20 per year Batteries are rechargeable, but they will need to be replaced in Year 3 and Year 6 at a cost of $1,800 per robot YearCapital Maintenance Battery cost Net cost If the company's MARR is 8%, what is the Annual Equivalent cost of this project? Annual Equivalent

Correct Answer is -538998.72, please show all calculations/functions used in Excel. (note that negative sign simply means output, sign does not matter, so 538998.72 is correct)

Answer 1

Let's calculate the annual cash flows per robot as shown below:

CF0 = -95,000, CF1 = -450, CF2 = -470, CF3 = -490 - 1800 = -2290, CF4 = -510, CF5 = -530, CF6 = -550 - 1800 = -2350

Calculate NPV using 8% = NPV(8%, -450...-2350) - 95000 = $99,853.96

Annual equivalent cost can be calculated using PMT function = PMT(rate = 8%, nper = 6, pv = 99,853.96, fv = 0, 0) = $21,599.95

Hence, for the entire project, annualized cost = 21,599.95 x 25 = $539,998.72