case study

CASE STUDY: Simulation

DRIVE-THRU COFFEE 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. In an effort to begin reopening states, many coffee shops have changed their focus from a meet-up, hangout type atmosphere to a “to-go” coffee drive thru or pickup experience. Despite the clear need to adapt to this “new normal”, most shops are not set-up for a “drive thru” model but instead must offer curbside pick-up.

Since many suburban shops have sufficient parking lot space to wait, the transition to the curbside model can easily be managed. However, some coffee shops in urban areas or on busy roads may find this transition difficult. In particular, Starbeans, a popular coffee shop on a busy intersection with limited waiting space will be making a decision to see if it is feasible to implement the curbside model. Due to the dangerous intersection, there are no walk up customers.

Starbeans is considering launching the curbside model and needs to analyze if this a feasible option. Since Starbeans would like to make sure that all customized orders are fresh, customers will only be able to place their orders and pay by phone or app once they have arrived at the coffee shop. Once the order is ready, a barista will walk to the customer’s car and deliver the order. As each customers arrives at the coffee shop, s/he needs to see if there is sufficient space curbside to place and wait for their order. Only if there is curbside space available, will they actually place their order and wait in the car line to receive their customized order.

To analyze the problem and make an informed decision whether it is wise to open for curbside orders, Starbeans needs to evaluate the store profitability and the number of lost customers as a result of the limited waiting space. Each day the store is open for the curbside service, the fixed cost would be $950 per day, including employee wages, utilities, and overhead.

In order to evaluate the daily profitability, you will examine the store's operation in 3 minute increments, which is the average time it takes Starbeans to fulfill a customer order. In other words, during each 3 minute period, you will simulate the number of customer arrivals, determine the number of who are forced to “balk” due to no available waiting space, and the profit generated during that time period if there is a customer to be served.

Based on historical data, during each three minute period, zero to four customers arrive based on the following probability distribution:

Number of Customers Arriving Probability 0 0.39 1 0.26 2 0.15 3 0.11 4 0.09

Waiting Lane

Car 3 Car 1 Car 2 Exit

Also, according to sales records, the profit generated by each customer varies by the order size which is normally distributed with a mean of $6.25 and a standard deviation of $1.80. The reason that Starbeans is hesitant to open using the curbside model is the limited waiting space for cars. As a result, when customers arrive, they will only be able to join the line of customer cars if there are less than five cars in the waiting (including the car being served). If a customer arrives and there are already five cars at the coffee shop, the customer will drive past (balk) and go to another coffee shop in the area – resulting in a lost customer and a lost profit. You have been hired as a consultant to advise Starbeans if they should consider this curbside model (or remain closed until they could reopen in their pre-COVID capacity. You should develop a simulation model for one day (10 hours) which is 200 three-minute periods. For each period, you will determine the expected profit and number of lost sales. You will compile this information to find the daily totals (profit, lost customers, etc). Repeat this simulation (using the F9 key) thirty-one times to simulate the expected profitability and lost sales for the entire month of July. USING THE INFORMATION PROVIDED IN THIS CASE STUDY, PREPARE AN EXECUTIVE SUMMARY: The Executive Summary should be approximately 1 to 2 pages (plus appendices) and include the following sections: 1. (20 points) Case Synopsis (include a brief summary of the case and the data provided) 2. (40 points) Methodology (including a discussion of what information was provided and how you used this

information to analyze the problem)

a) Conduct a one day simulation using Excel (200 customers (periods) per day). HINT: set up a simulation to evaluate what happens during each of the 200 three-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 31 days to predict the average total profit per day and the average number of lost customers

per day. You can repeat the simulation 31 times (eg. using F9) and keep track of the total profit and number of balks per day.

3. (20 points) Findings and Conclusions (include summary of analysis results. What other factors should also be considered in making this decision)

4. (20 points) Recommendations (based on your simulation and other factors, what advice would you give to Starbeans regarding converting to the curbside model for the foreseeable future? Consider profitability of the store and daily fixed expenses.)