Acme Manufacturing currently employs 13 people in its receiving department. Each receiving dock clerk earns $50,000 per year. Each employee can unload up to 200 pallets of inventory per day. It currently receives on average 2450 pallets per day. Acme is considering two options to improve productivity of its receiving dock employees.

Option 1 (Automation) involves investing $200,000 in automation equipment that would enable each employee to unload up to 350 pallets of inventory per day. Option 2 (RFID) involves investing $50,000 in RFID equipment that would enable each employee to unload up to 275 pallets per day. For both options, Acme plans to keep total costs of the receiving function the same as they currently are by eliminating some workers. Four workers would be eliminated if Acme chooses option 1 (automation) and one worker would be eliminated if it chooses option 2 (RFID).

REQUIRED

a. Download the spreadsheet from the course website and complete it by creating formulas to calculate:

i. Receiving capacity

ii. Cost per pallet received (Note: standard cost equals total costs divided by maximum capacity)

iii. Cost of receiving capability used

iv. Cost of unused receiving capability

b. Acme now wishes to further downsize its operations under options 1 and 2, but without impairing its ability to receive and process incoming shipments. Thus, it desires to keep enough employees to be able to process at least 2600 pallets per day. Management realizes it cannot employ a fraction of a worker, thus there will continue to be some excess capacity with both options. 

Complete the remainder of the spreadsheet to show:

i. Number of employees required under all three options

ii. Actual receiving capability under all three options

iii. Cost per pallet received

iv. Cost of receiving capability used

v. Cost of unused receiving capability