$1$

$6$

$.$

$8$

$$Excel Problem

$*$

Acme Manufacturing currently employs

$1$

$3$

$$people in its receiving department. Each receiving dock clerk earns $

$5$

$0$

$,$

$0$

$0$

$0$

$$per year. Each employee can unload up to

$2$

$0$

$0$

$$pallets of inventory per day. It currently receives on average

$2$

$4$

$5$

$0$

$$pallets per day.

Acme is considering two options to improve productivity of its receiving dock employees. Option

$1$

$($

Automation

$)$

$$involves investing $

$2$

$0$

$0$

$,$

$0$

$0$

$0$

$$in automation equipment that would enable each employee to unload up to

$3$

$5$

$0$

$$pallets of inventory per day. Option

$2$

$($

RFID

$)$

$$involves investing $

$5$

$0$

$,$

$0$

$0$

$0$

$$in RFID equipment that would enable each employee to unload up to

$2$

$7$

$5$

$$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 yorkers. Four workers would be eliminated if Acme chooses option

$1$

$($

automation

$)$

$$and one worker would be eliminated if it chooses option

$2$

$($

RFID

$)$

$.$

Required

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

$2$

$6$

$0$

$0$

$$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

$($

Note: standard cost equals total costs