Montclair State University Bru Thru Simulation Case Study Discussion

User Generated

Pnzzvr91

Business Finance

Montclair State University

Description

THE FILE IS ATTACHED TO THE QUESTIONS:

PLEASE MAKE SURE TO ATTACH AN EXCEL DOCUMENT WITH THE ANSWER

SHOWING ALL FORMULAS WITHIN THE EXCEL DOCUMENT

QUESTIONS:

a) Conduct a one day simulation using Excel (100 customers (periods) per day). HINT: set up a simulation to repeat what happens during each of the 100 six-minute periods. For each period, keep track of the number of customers currently waiting, the number of customers arriving, number of customers being served (0 if there is no one waiting or arriving and 1 otherwise), the order size (profit) for that customer (if there is a customer), and number of customers balking.

b) For each day, determine the total revenue per day and the number of lost customers per day.

c) Simulate 30 days to find out the average total profit per day and the average number of lost customers per day. You can repeat the simulation twenty times (eg. using F9) and keep track of the total profit and number of lost customers per day.

Unformatted Attachment Preview

BRU-THRU For this assignment, you will analyze a case study, develop a simulation model in Excel, and prepare a short executive report (plus appendices) that discusses the conditions of the case and your recommendations. Bru-Thru, Inc. is a chain of beverage supply stores located throughout upstate New York. The stores are designed to enable customers to pick-up beverages, snacks, and party supplies without getting out of their cars. A typical store design is shown in the Figure below. A service lane runs through the middle of the store, and soft drinks, beer, and other supplies are stored at various locations along both sides of the service lane. When a customer drives into the store, the store clerk takes the order, fills the order, and collects the money. The customer remains in the car when receiving the service. When additional customers arrive at the store, they wait in a line outside the store until the preceding customer's order is complete. Then the next customer in line drives into the store for service. Bru-Thru's President is considering opening a new store near a shopping center and has requested planning information on the projected operation of the store, including profitability of the store, and the potential number of lost sales due to waiting lines. The fixed costs for the new store including rent, salaries, and overhead comes to $1250 per day. In modeling the system we will study the store's operation in terms of what happens during time periods of 6 minutes each. That is, we will count the number of customer arrivals, count the number of customers lost, and determine whether or not a customer is being serviced during each 6-minute interval. We assume that the average time to fill each order is 6 minutes. Based on a study of traffic flow, the company has estimated that the probability distribution of customer arrivals is as shown in Table 1. As the table shows, there is a 0.31 probability of no customers arriving during a given 6-minute interval, a 0.39 probability of one customer, and so on. Numberer of Customers Arriving 0 1 2 3 4 Probability 0.31 0.39 0.19 0.09 0.02 Sales records from the company's other stores show that customers vary in terms of the size of the order placed. The revenue generated by each customer is normally distributed with a mean of $14 and a standard deviation of $4. As an additional operation condition, experience with other company stores indicates that customers will wait for service only if there are less than four cars in the waiting line. If a customer arrives and there are already four cars in the waiting area, the customer will drive off. This failure to enter the waiting line is referred to as balking and results in a lost customer and a lost profit. Use simulation techniques to determine if the Bru-Thru president should open a new store near the shopping center. You should develop a simulation model for one day or 100 periods -- since each period represents a 6- minute time interval, ten customers can be serviced per hour and the store is open 10 hours per day (thus 100 customers (periods) per day). After developing the simulation, you wi ll record the daily totals (profit, lost customers, etc) and then repeat the day (using the F9 key) thirty times to simulate the profitability and lost sales for the month. QUESTIONS: a) Conduct a one day simulation using Excel (100 customers (periods) per day). HINT: set up a simulation to repeat what happens during each of the 100 six-minute periods. For each period, keep track of the number of customers currently waiting, the number of customers arriving, number of customers being served (0 if there is no one waiting or arriving and 1 otherwise), the order size (profit) for that customer (if there is a customer), and number of customers balking. b) For each day, determine the total revenue per day and the number of lost customers per day. c) Simulate 30 days to find out the average total profit per day and the average number of lost customers per day. You can repeat the simulation twenty times (eg. using F9) and keep track of the total profit and number of lost customers per day.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

The solution is attached.Please, give me feedback.

Simulation:

Customer waiting limit:

Period
1
2
3
4
5
6
7
8

Random #
0.2850
0.5756
0.3070
0.5866
0.2217
0.5183
0.3383
0.4094

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

0.2396
0.3500
0.8050
0.6916
0.6874
0.0654
0.6509
0.7802
0.4005
0.2238
0.8436
0.4599
0.7651
0.0483
0.0737
0.9289
0.1503
0.7571
0.5808
0.9962
0.4785
0.3679
0.8481
0.9298
0.5786
0.5262
0.9295
0.1143
0.5366
0.7051
0.6223
0.2101
0.7611
0.5537

4

# of customers # of customers
customers
# of customers
waiting
arriving
being served
balking
0
0
0
0
0
1
1
0
0
0
0
0
0
1
1
0
0
0
0
0
0
1
1
0
0
1
1
0
0
1
1
0
0
0
0
1
1
1
0
0
1
1
0
1
1
2
1
0
2
1
2
2
3
3...

Similar Content

Related Tags