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:

Routine Trip Related Activity percentage

Maintenance-related activities percentage

Supervisor Salary

$5,000

62%

38%

Head cashier Salary

4,000

77%

23%

Associate cashiers Salary ($1,800 each Assoc. cashier)

3,600

50%

50%

Maintenance workers salary

3,630

100%

Cashiers' service vehicle-used for Routine trips

1,682

100%

Maintenance service vehicle-used for maintenance activities

1,545

100%

Office rent and utilities-used equally preparing for routine and maintenance activities

1,576

50%

50%

Machine lease, space rent, and utilities ($1,400 each machine)

14,000

Total operating cost for month

$35,033

Related monthly activity information for this month follows:

Machine

Routine 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

7

580

150

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.

b. You have determined that ABC would be a more effective costing method. You have decided to

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.

Routine Trips

Maintenance

Machine lease, space rent, and utilities

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

Office rent and utilities-used equally preparing for routine and maintenance activities

Machine lease, space rent, and utilities ($1,700 each machine)

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

d. How can ABC cost information by used by this banking company to improve overall

management of monthly operating costs? Use the text box to type in your detailed response.

Your explanation needs to include information from a source other than the textbook to support your comments and not from Investopedia or Wikipedia. You need to include a reference using style

Here is a link to Style information:

https://apastyle.apa.org/

e. What if Machine 9 information was incorrect and instead of 60 routine trips it was 62 routine trips, what happens to the cost of all the machines? Comment on the impact of the change for Machines 1-10.

To complete part E, you should copy your completed Part 3 solution worksheet within this workbook and rename the worksheet Part 3 E. Also, do not forget to comment on the change for all the machines on the Part 3 E Worksheet.

You should only need to update the routine trips for machine 9 to determine the new cost structure, since your Part 3 E should have all the cell references, so once you change the routine trips the solution should automatically change and you should comment on the change on Part 3E.

There are several ways to make a copy of a worksheet. I like to just right-click the worksheet tab of the worksheet I want to copy or you can use the format area on the Home Ribbon.

https://support.microsoft.com/en-us/office/move-or-copy-worksheets-or-worksheet-data-47207967-bbb2-4e95-9b5c-3c174aa69328

Please rename the new worksheet Part 3 E by clicking on the new worksheet tab and type in the new worksheet name.

https://www.techonthenet.com/excel/sheets/rename2016.php

Please contact me if you have questions.