QUESTION:

DATA FOR QUESTION:

52 You need to use cell referencing so I can track your calculations in Excel and to perform what-if analysis for E. 53 54 a. Determine the monthly operating costs of a machine when cost assignments are 55 based on the number of machines. Conceptually this is like the plantwide overhead rate per unit. 56 Hint: Do not make this complicated, since you are given the total overhead cost and know there are 10 machines. A B 1 Part 1 2 Happy Banking Company has ten automatic teller machines (ATM) spread throughout the city 3 maintained by the ATM Department. You have been assigned the task of determining 4 the cost of operating each machine. Management will use the information you develop 5 to evaluate the desirability of continuing to operate each machine and/or changing 6 security arrangements for a particular machine. 7 8 The ATM Department consist of a total of six employees: a supervisor, a head cashier, 9 two associate cashiers, and two maintenance personnel. The associate cashiers make 10 between two and four daily trips to each machine to collect and replenish cash and to 11 replenish supplies, deposit tickets and so forth. Each machine contains a small computer 12 that automatically summarizes and reports transactions to the head cashier. The 13 head cashier reconciles the activities of the two associate cashiers to the 14 computerized reports. The supervisor, who does handle cash, reviews the reconciliation. 15 When an automatic teller's computer, a customer, or a cashier reports a problem, the 16 two maintenance employees and one cashier are dispatched immediately. The cashier 17 removes all cash and transaction records, and the maintenance employees repair the 18 machine 19 19 20 Monthly operating costs and information on percentage of time that used in activities for a recent month follows: 21 Operating costs: 22 Supervisor Salary 23 Head cashier Salary 24 Associate cashiers Salary ($1,800 each Assoc. cashier) 25 Maintenance workers salary ($1,800 each Maintenance worker) 26 Cashiers' service vehicle-used for Routine trips 27 Maintenance service vehicle-used for maintenance activities 28 Office rent and utilities-used equally preparing for routine and maintenance activities 29 Machine lease, space rent, and utilities ($1,700 each machine) 30 Total operating cost for month $4,970 4,200 3,600 3,600 1,500 1,544 2,436 17,000 $38,850 Routine Trip Related Maintenance- Activity related activities percentage percentage 60% 40% 75% 25% 50% 50% 100% 100% 100% 50% 50% 32 Related monthly activity information for this month follows: Routine Maintenance 33 Machine Trips Hours 30 90 60 30 120 OVOCA 90 120 60 50 44 710 160 45 46 Additional Information follows: 47 1. The office is centrally located with about equal travel time to each machine. 48 2. Maintenance hours include travel time. 49 3. The cashiers' service vehicle is used exclusively for routine visits. 52 You need to use cell referencing so I can track your calculations in Excel and to perform what-if analysis for E. 53 54 a. Determine the monthly operating costs of a machine when cost assignments are 55 based on the number of machines. Conceptually this is like the plantwide overhead rate per unit. 56 Hint: Do not make this complicated, since you are given the total overhead cost and know there are 10 machines. A B 1 Part 1 2 Happy Banking Company has ten automatic teller machines (ATM) spread throughout the city 3 maintained by the ATM Department. You have been assigned the task of determining 4 the cost of operating each machine. Management will use the information you develop 5 to evaluate the desirability of continuing to operate each machine and/or changing 6 security arrangements for a particular machine. 7 8 The ATM Department consist of a total of six employees: a supervisor, a head cashier, 9 two associate cashiers, and two maintenance personnel. The associate cashiers make 10 between two and four daily trips to each machine to collect and replenish cash and to 11 replenish supplies, deposit tickets and so forth. Each machine contains a small computer 12 that automatically summarizes and reports transactions to the head cashier. The 13 head cashier reconciles the activities of the two associate cashiers to the 14 computerized reports. The supervisor, who does handle cash, reviews the reconciliation. 15 When an automatic teller's computer, a customer, or a cashier reports a problem, the 16 two maintenance employees and one cashier are dispatched immediately. The cashier 17 removes all cash and transaction records, and the maintenance employees repair the 18 machine 19 19 20 Monthly operating costs and information on percentage of time that used in activities for a recent month follows: 21 Operating costs: 22 Supervisor Salary 23 Head cashier Salary 24 Associate cashiers Salary ($1,800 each Assoc. cashier) 25 Maintenance workers salary ($1,800 each Maintenance worker) 26 Cashiers' service vehicle-used for Routine trips 27 Maintenance service vehicle-used for maintenance activities 28 Office rent and utilities-used equally preparing for routine and maintenance activities 29 Machine lease, space rent, and utilities ($1,700 each machine) 30 Total operating cost for month $4,970 4,200 3,600 3,600 1,500 1,544 2,436 17,000 $38,850 Routine Trip Related Maintenance- Activity related activities percentage percentage 60% 40% 75% 25% 50% 50% 100% 100% 100% 50% 50% 32 Related monthly activity information for this month follows: Routine Maintenance 33 Machine Trips Hours 30 90 60 30 120 OVOCA 90 120 60 50 44 710 160 45 46 Additional Information follows: 47 1. The office is centrally located with about equal travel time to each machine. 48 2. Maintenance hours include travel time. 49 3. The cashiers' service vehicle is used exclusively for routine visits