Question

**SHOW ALL WORK IN EXCEL QM**

Problem-7:

Management of the First Syracuse Bank is concerned about a loss of customers at its main office downtown. One solution that has been proposed is to add one or more drive-through teller stations to make it easier for customers in cars to obtain quick service without parking. Chris Carlson, the bank president, thinks the bank should only risk the cost of installing one drive-through. He is informed by his staff that the cost (amortized over a 20-year period) of building a drive-through is $12,000 per year. It also costs $16,000 per year in wages and benefits to staff each new teller window.

The director of management analysis, Beth Shader, believes that the following two factors encourage the immediate construction of two drive-through stations, however. According to a recent article in Banking Research magazine, customers who wait in long lines for drive-through teller service will cost banks an average of $1 per minute in loss of goodwill. Also, adding a second drive-through will cost an additional $16,000 in staffing, but amortized construction costs can be cut to a total of $20,000 per year if two drive-throughs are installed together instead of one at a time. To complete her analysis, Shader collected one month’s arrival and

service rates at a competing downtown bank’s drive-through stations. These data are shown as observation analyses 1 and 2 in the following tables.

• Simulate a 1-hour time period, from 1 to 2 p.m., for a single-teller drive-through.
• Simulate a 1-hour time period, from 1 to 2 p.m., for a two-teller system.
• Conduct a cost analysis of the two options. Assume that the bank is open 7 hours per day and 200 days per year.

OBSERVATION ANALYSIS 1: INTERARRIVAL TIMES FOR 1,000 OBSERVATIONS

TIME BETWEEN ARRIVALS (MINUTES)	NUMBER OF OCCURRENCES
1	200
2	250
3	300
4	150
5	100

OBSERVATION ANALYSIS 2: CUSTOMER SERVICE TIME FOR 1,000 CUSTOMERS

SERVICE TIME (MINUTES)	NUMBER OF OCCURRENCES
1	100
2	150
3	350
4	150
5	150
6	100

Answer 1

Simulate a 1-hour time period, from 1 to 2 P.M., for a single-teller drive-through.

The Annual Cost of waiting is $56,000

Random Number	Time Between Arrivals	Actual Time	Time Service Begins	Random Number	Service Time	Service Complete	Wait Time (Minutes)
52	3	01:03	01:03	60	3	01:06	0
37	2	01:05	01:06	60	3	01:09	1
82	4	01:09	01:09	80	5	01:14	0
69	3	01:12	01:14	53	3	01:17	2
98	5	01:17	01:17	69	4	01:21	0
96	5	01:22	01:22	37	3	01:25	0
33	2	01:24	01:25	6	1	01:26	1
50	3	01:27	01:27	63	4	01:31	0
88	4	01:31	01:31	57	3	01:34	0
90	4	01:35	01:35	2	1	01:36	0
50	3	01:38	01:38	94	6	01:44	0
27	2	01:40	01:44	52	3	01:47	4
45	2	01:42	01:47	69	4	01:51	5
81	4	01:46	01:51	33	3	01:54	5
66	3	01:49	01:54	32	3	01:57	5
74	3	01:52	01:57	30	3	02:00	5
30	2	01:54	02:00	48	3	02:03	6
59	3	01:57	02:03	88	5	02:08	46
67	3	02:00				Total	40

Simulate a 1-hour time period, from 1 to 2 P.M., for a two-teller system.

1. The Annual Cost of waiting is = 1 minute
2. 7 hours per 200 days per $1.00= $1,400.

Random	Time	Actual	Random	Service	Service Starts	Service Ends	Service Starts	Service Ends	Wait
Number	Between	Time	Number	Time	At	At	At	At	Time
	Arrivals				Window	Window	Window	Window	(Minutes)
					1	1	2	2
52	3	01:03	60	3	01:03	01:06			0
37	2	01:05	60	3			01:05	01:08	0
82	4	01:09	80	5	01:09	01:14			0
69	3	01:12	53	3			01:12	01:15	0
98	5	01:17	69	4	01:17	01:21			0
96	5	01:22	37	3	01:22	01:25			0
33	2	01:24	6	1			01:24	01:25	0
50	3	01:27	63	4	01:27	01:31			0
88	4	01:31	57	3	01:31	01:34			0
90	4	01:35	2	1	01:35	01:36			0
50	3	01:38	94	6	01:38	01:44			0
27	2	01:40	52	3			01:40	01:43	0
45	2	01:42	69	4			01:43	01:47	1
81	4	01:46	33	3	01:46	01:49			0
66	3	01:49	32	3	01:49	01:52			0
74	3	01:52	30	3	01:52	01:55			0
30	2	01:54	48	3			01:54	01:57	0
59	3	01:57	88	5	01:57	02:02			0
67	3	02:00							0

Conduct a cost analysis of the two options. Assume that the bank is open 7 hours per day and 200 days per year.

Options for teller drive:

                              1. Cost for one= $56,000 +$12,000+16,000

=$84,000

                              2. Cost for two= $1,400 + $20,00 + $32,000

= $53,400

                              3. Savings using the both tellers = $84,000 – $53,400

= $30,600