CASE STUDY: Simulation LIQUOR-RUN 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 Liquor-Run, Inc. is a chain of beverage supply stores located throughout Northern California. The stores are designed to cnable 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, liquor, beer, and other supplies are stored at various locations along both sides of the service lanc. 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. Service Entrance Service Land C2 Waiting Area Liquor-Run'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. Number of Customers Arriving Probability 0.31 0239 0.19 0.09 0.02 0 1 2 3 4 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 Liquor-Run 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 will record the daily totals (profit, lost customers, etc) and then repeat the day (using the F9 key) thirty-one times to simulate the profitability and lost sales for the month USING THE INFORMATION PROVIDED IN THIS CASE STUDY, PREPARE AN EXECUTIVE SUMMARY: 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 (O if there is no one waiting or arriving and I 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 find out 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 lost customers per day. CASE STUDY: Simulation LIQUOR-RUN 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 Liquor-Run, Inc. is a chain of beverage supply stores located throughout Northern California. The stores are designed to cnable 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, liquor, beer, and other supplies are stored at various locations along both sides of the service lanc. 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. Service Entrance Service Land C2 Waiting Area Liquor-Run'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. Number of Customers Arriving Probability 0.31 0239 0.19 0.09 0.02 0 1 2 3 4 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 Liquor-Run 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 will record the daily totals (profit, lost customers, etc) and then repeat the day (using the F9 key) thirty-one times to simulate the profitability and lost sales for the month USING THE INFORMATION PROVIDED IN THIS CASE STUDY, PREPARE AN EXECUTIVE SUMMARY: 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 (O if there is no one waiting or arriving and I 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 find out 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 lost customers per day