Part 3 Happy Banking Company has ten automatic teller machines (ATM) spread throughout the city maintained by the ATM Department. You have been assigned the task of determining the cost of operating each machine. Management will use the information you develop to evaluate the desirability of continuing to operate each machine and/or changing security arrangements for a particular machine. The ATM Department consist of a total of six employees: a supervisor, a head cashier, two associate cashiers, and two maintenance personnel. The associate cashiers make between two and four daily trips to each machine to collect and replenish cash and to replenish supplies, deposit tickets and so forth. Each machine contains a small computer that automatically summarizes and reports transactions to the head cashier. The head cashier reconciles the activities of the two associate cashiers to the computerized reports. The supervisor, who does handle cash, reviews the reconciliation. When an automatic teller's computer, a customer, or a cashier reports a problem, the two maintenance employees and one cashier are dispatched immediately. The cashier removes all cash and transaction records, and the maintenance employees repair the machine. Monthly operating costs and information on percentage of time that used in activities for a recent month follows: Operating costs: Supervisor Salary Head cashier Salary Associate cashiers Salary ($1,800 each Assoc. cashier) Maintenance workers salary Cashiers' service vehicle-used for Routine trips Maintenance service vehicle-used for maintenance activities Machine lease, space rent, and utilities ($1,400 each machine) Office rent and utilities-used equally preparing for routine and maintenance activities Total operating cost for month Routine Trip Related Maintenance- Activity related activities percentage percentage $5,000 62% 38% 4,000 77% 23% 3,600 50% 50% 3,630 100% 1,682 100% 1,545 100% 1,576 50% 50% 14,000 $35,033 Related monthly activity information for this month follows: Monthly Operating cost Activity Rate $35,033.00 Routine Machine Trips Maintenance Hours 1 35 6 2 90 17 3 60 15 4 80 30 5 75 15 6 30 10 7 90 25 8 20 5 9 60 20 10 40 580 7 150 $17,516.50 $11,677.67 $8,758.25 $7,006.60 $5,838.83 $5,004.71 $4,379.13 $3,892.56 $3,503.30 Additional Information follows: 1. The office is centrally located with about equal travel time to each machine. 2. Maintenance hours include travel time. 3. The cashiers' service vehicle is used exclusively for routine visits. Required-Answer the following questions in the area below each question. I have tried to set up some structure in some of the analysis in the solution area below. You are required to use cell referencing in all your calculations in Excel to earn credit. Using cell referencing will all you to perform what-if analysis for E. Do not type in numbers but use cell references. a. Determine the monthly operating costs of a machine when cost assignments are based on the number of machines. Conceptually this is like the plantwide overhead rate per unit. Hint: Do not make this complicated, since you are given the total operating cost and know there are 10 machines. $3,503.30 b. You have determined that ABC would be a more effective costing method. You have decided to A60:J107 split the costs into 3 activities: Routine trip, maintenance, and machine lease-space rent-utilities. Develop an analysis to determine the activity rates for each of the 3 activities. Round answers to the nearest cent. Machine lease, Routine Trips space rent, and Maintenance utilities Supervisor Salary Head cashier Salary Associate cashiers Salary ($1,800 each Assoc. cashier) Maintenance workers salary $9 6.896551724 6.206896552 6.25862069 $33 26.66666667 $500 400 24 24.2 360 363 Cashiers' service vehicle-used for Routine trips 3 11.21333333 168.2 Maintenance service vehicle-used for maintenance activities 2.663793103 10 154.5 Office rent and utilities-used equally preparing for routine and maintenance activities 2.717241379 10.50666667 157.6 Machine lease, space rent, and utilities ($1,400 each machine) 24.13793103 93.33333333 1,400 Total per activity Quantity of activity Unit activity rates c. Determine the operating costs assigned to each machine when activity-based costing is used. Machine Routine Trips Maintenance Machine lease, space rent, and utilities Total 1 2 3 4 5 6 7 8 9 10 Total