ON EXCEL

Kroger ClickList Robotic Pick System Kroger currently offers the ClickList service in over 1,000 of its stores. The service allows customers to place orders for groceries online and pick them up at the store without having to go inside and shop. Kroger currently has employees who assemble the ClickList orders by picking products from the store shelves and bagging them for transport out to the customer's vehicles. Kroger is considering the use of robots to take on some of the picking functions. Specifically, Kroger would like to implement a robotic system that can navigate store aisles and pick canned and boxed goods from shelves to fill part of the ClickList orders. Human workers would still need to gather any fresh produce, meats, bakery items and dry goods on the orders, but using robots for boxed and canned goods is expected to significantly reduce long-term labor costs for the ClickList service. Use the following facts to conduct a break-even analysis of the robot picker technology Costs Kroger has over 2,700 stores in total, but only 1,060 currently offer the ClickList service. Kroger plans to continue adding the ClickList service to more of its stores, but only current ClickList stores will be included in this analysis. Because the robots will be operating around customers, Kroger plans to trial the robots in just 20 select stores in 2019. If that trial is successful, Kroger plans to expand the robots to the other ClickList stores on the following implementation schedule: 200 stores in 2020, 230 stores in 2021, 270 stores in 2022, and 340 stores in 2023 Initial setup of the robot command and control system in each store will cost $180,000 and that cost will be incurred in the year each store implements the robots. . The number of robots per store will vary based on projected ClickList volume at each store. Based on the store implementation schedule, robot installations each year will be as follows: 160 robots in 2019, 1,240 robots in 2020, 1,550 in 2021, 1,620 in 2022, and 1,780 in 2023. The purchase and installation costs for each robot will be $78,000 and that cost will be incurred in the year they are implemented estimated to be $7,200 per robot starting in the year the robot is put in service estimated at $17,000 per store in which robots are implemented The robots will require regular maintenance and the annual cost for that maintenance is The software controlling the robots will also require upgrades and bug fixes. Those fees are Benefits The tangible benefits come from reduced labor costs and increased sales revenue. The robot's primary benefit will be to reduce the need for employees to pick boxed and canned goods for the ClickList orders. It is projected that Implementing each robot will reduce labor costs equivalent to employing one human picker working a 12-hour shift each day per year customer retention. It is estimated that each store with robot pickers will receive five additional The labor rate for ClickList pickers is $12.25 per hour and the ClickList service is offered 360 days The robots are also expected to reduce errors in item retrieval, which will lead to an increase in orders per day throughout the analysis period with an average profit per order of $14.50 Calculations The break-even analysis will cover a 5-year period from 2019-2023 For each year: Calculate the system costs and benefits. Calculate the net benefits of the system and the break-even totals for the system. Break even totals are simply the accumulated net benefits. The project breaks even when the accumulated net benefits value is positive For the analysis period Calculate the net present value (NPV) of the investment using a rate of 12%. Calculate the internal rate of return (IRR). Answer the four questions at the bottom of the spreadsheet. Based on the variables provided, in what year will the project break even? Based on their target rate of return of 12%, should Kroger go forward with this project? Should Kroger go forward with the project if the labor rate for ClickList pickers is $12 per hour? What is the NPV if the annual robot maintenance were decreased by $400? * Number of stores instts eschyes Numter of roncts being installed esch yeer Commend and contral system setup per store d instel etion c Annual software upgreces and b fixes per store ClickList service s ClickList picker shift lengt ClickList picker leoor rate per hour Aaditionel orders per store per cay with roace pickers Profit per sdcitional crcer PV Rete Rcoat purchase and instelleticn Totsl Annual Costs onel orders Totel Annuel Eenetits Break-Even, NPV nnuel Eenefits Break-Even Totes Net Present Value (NFV) Internal Rete of Return RR Questions Based on the variebles pravidec, in whet yeer willne Eased on theirtarget rat. cf return of 12%, snould Kroger eo orward with this praje? Should Kragergoorwand with the project ithe ebar rate for ClickList pickers is $12 per hour? What is the NFV ithe annuel rotot meintenance were decreesed ay 540O? Kroger ClickList Robotic Pick System Kroger currently offers the ClickList service in over 1,000 of its stores. The service allows customers to place orders for groceries online and pick them up at the store without having to go inside and shop. Kroger currently has employees who assemble the ClickList orders by picking products from the store shelves and bagging them for transport out to the customer's vehicles. Kroger is considering the use of robots to take on some of the picking functions. Specifically, Kroger would like to implement a robotic system that can navigate store aisles and pick canned and boxed goods from shelves to fill part of the ClickList orders. Human workers would still need to gather any fresh produce, meats, bakery items and dry goods on the orders, but using robots for boxed and canned goods is expected to significantly reduce long-term labor costs for the ClickList service. Use the following facts to conduct a break-even analysis of the robot picker technology Costs Kroger has over 2,700 stores in total, but only 1,060 currently offer the ClickList service. Kroger plans to continue adding the ClickList service to more of its stores, but only current ClickList stores will be included in this analysis. Because the robots will be operating around customers, Kroger plans to trial the robots in just 20 select stores in 2019. If that trial is successful, Kroger plans to expand the robots to the other ClickList stores on the following implementation schedule: 200 stores in 2020, 230 stores in 2021, 270 stores in 2022, and 340 stores in 2023 Initial setup of the robot command and control system in each store will cost $180,000 and that cost will be incurred in the year each store implements the robots. . The number of robots per store will vary based on projected ClickList volume at each store. Based on the store implementation schedule, robot installations each year will be as follows: 160 robots in 2019, 1,240 robots in 2020, 1,550 in 2021, 1,620 in 2022, and 1,780 in 2023. The purchase and installation costs for each robot will be $78,000 and that cost will be incurred in the year they are implemented estimated to be $7,200 per robot starting in the year the robot is put in service estimated at $17,000 per store in which robots are implemented The robots will require regular maintenance and the annual cost for that maintenance is The software controlling the robots will also require upgrades and bug fixes. Those fees are Benefits The tangible benefits come from reduced labor costs and increased sales revenue. The robot's primary benefit will be to reduce the need for employees to pick boxed and canned goods for the ClickList orders. It is projected that Implementing each robot will reduce labor costs equivalent to employing one human picker working a 12-hour shift each day per year customer retention. It is estimated that each store with robot pickers will receive five additional The labor rate for ClickList pickers is $12.25 per hour and the ClickList service is offered 360 days The robots are also expected to reduce errors in item retrieval, which will lead to an increase in orders per day throughout the analysis period with an average profit per order of $14.50 Calculations The break-even analysis will cover a 5-year period from 2019-2023 For each year: Calculate the system costs and benefits. Calculate the net benefits of the system and the break-even totals for the system. Break even totals are simply the accumulated net benefits. The project breaks even when the accumulated net benefits value is positive For the analysis period Calculate the net present value (NPV) of the investment using a rate of 12%. Calculate the internal rate of return (IRR). Answer the four questions at the bottom of the spreadsheet. Based on the variables provided, in what year will the project break even? Based on their target rate of return of 12%, should Kroger go forward with this project? Should Kroger go forward with the project if the labor rate for ClickList pickers is $12 per hour? What is the NPV if the annual robot maintenance were decreased by $400? * Number of stores instts eschyes Numter of roncts being installed esch yeer Commend and contral system setup per store d instel etion c Annual software upgreces and b fixes per store ClickList service s ClickList picker shift lengt ClickList picker leoor rate per hour Aaditionel orders per store per cay with roace pickers Profit per sdcitional crcer PV Rete Rcoat purchase and instelleticn Totsl Annual Costs onel orders Totel Annuel Eenetits Break-Even, NPV nnuel Eenefits Break-Even Totes Net Present Value (NFV) Internal Rete of Return RR Questions Based on the variebles pravidec, in whet yeer willne Eased on theirtarget rat. cf return of 12%, snould Kroger eo orward with this praje? Should Kragergoorwand with the project ithe ebar rate for ClickList pickers is $12 per hour? What is the NFV ithe annuel rotot meintenance were decreesed ay 540O