I need assistance trying to run a simulation in excel with the information provided. I needed to complete analysis. I have the raw data in excel. Im not sure how to send that to you
Bling Max Car Wash Bernard "Bernie" West stood under a tall palm admiring his new business and the many vehicles entering it on a sunny Florida day. Bernie's Bling-Max Carwash had exceeded his expectations as a small service business among many similar enterprises in Fort Lauderdale. Owned and operated by 32 year old Bernie, he had purchased the business outright about six months ago, borrowing against his house and drawing down all his savings. The carwash, about 8 years old with a single auto wash bay, was not the newest equipment to be found in a modern automated car wash, but it had been flawlessly maintained and was the popular high pressure, touch free, Pulse-Pro equipment with excellent reliability. Best yet, the business was located in the perfect spot in Fort Lauderdale, near a major intersection close to offices, popular clubs, restaurants, and busy shopping areas. There were no nearby competitors and Bernie wanted to keep it that way. Bernie assessed the current business situation. Bling-Max offers its customers three levels of wash service, "Economy", "Custom", and "Deluxe", which are priced at $8.00, $9.00, and $10.00 respectively. Based on Bernie's market research, the prices are slightly high but still competitive, compared to other automated washes. More important than pricing, he knew he had to do something to reduce wait times to keep customers from going elsewhere. Bling-Max is open 7 days per week, 365 days per year from 8:00 AM to 6:00 PM. This 10 hour operating schedule provides 14 "off-hours" for the necessary cleaning, preventive maintenance, and refill of wash chemicals. During operating hours, arriving cars line up in a perimeter corral queue that holds 14 or more cars, depending on their size. The first car arriving on or after 8:00 AM can pay with a quick credit card swipe and enter the wash. Successively arriving cars can swipe and enter as soon as they reach the control panel at the wash entrance, providing it is before 6:00 PM closure. At 6:00 PM the control panel lights up the "CLOSED" sign and will not accept new customers. The last car entering just before 6:00 PM gets a complete wash cycle and exits normally. Any cars remaining in line when the service ends at 6:00 PM are let out a special gate by the maintenance technician who has arrived to perform the standard daily maintenance. To better understand customer complaints about the long lines and wait time problem, Bernie gathered random sample data at various times of day and on different days, recording timing of customer arrivals entering the corral and what wash service they ultimately picked. The wash process times (Service Times) for the "Economy", "Custom", and "Deluxe" washes are 6, 7, and 8 minutes respectively, which includes credit card swipe, entry, and drive-off times. Since these three times are predominantly controlled by sophisticated electronic timers in the automated wash itself, any driver-induced variance from the 6, 7, and 8 minutes is of no consequence (for example, there are no 6.2 or 7.9 minute wash cycles, only 6, 7, and 8 min.). The customer Inter-Arrival Times (IAT) and selected wash services are shown in the Excel file Q1 Raw Data Worksheet.xlsx. 3 With six months financials completed, Bernie is confident that all-in costs (utilities, maintenance, chemicals, advertising, etc.) are 35% of revenues. Since customers are balanced between offices and shopping during the week, and restaurants, bars, and shopping on weekends, there seems to be little variation from day to day or within seasons. Demand is steady and strong all day, every day, except for an estimated 6% rain periods when the wash is idle. The current car wash has the dryer blowers attached to the robotic wash arm, so the vehicle stays put after the wash and wax cycle and during the one minute drying cycle. Bernie has found like-new blow-off equipment at an industrial distributor and it is ready to install. He can purchase and install the blow-off section at the car wash exit portal, instead of the wash arm, for a total investment of $11,500, complete. This would de-couple the one minute drying cycle from the wash cycle, speeding up the service process by one minute per car, no matter which wash they chose. As a car finished the wash cycle it would move forward toward the exit to get dried off. This investment would separate the wash and dry-off cycles, reducing all wash cycles by 1 minute and allowing the next customer to enter one minute earlier than before, thereby improving throughput. It makes sense to Bernie that his wait times could be improved by a shorter cycle, but it is not obvious to what extent wait times and lines would be improved, nor whether the investment is financially justified. He could reduce average process time just by selling more economy washes. He also knows that he is not on the low side of competition with his pricing and is concerned that long waits make it too easy for a competitor to setup a nearby carwash and steal his market. Bernie is pondering adding a fourth wash selection and related pricing adjustments, as well the merits of investing in a new blow-off section. Bernie knows that he can borrow the $11,500 for the new blow-off from his commercial bank with a 3 year loan at 6.5% APR, but there is gnawing risk in taking out this loan and pledging his carwash equity as collateral. He is thinking about his wife, their two-year old son, and a new child on the way as he considers his next steps. This business is everything to him and his family. Can he afford to take this investment risk, should he look for alternative improvements, or can he risk a big loss of business if he does nothing? Bernie's brother Craig has an automated car wash in Georgia. Craig's wash has four wash selections, consisting of Bernie's three washes plus a higher-end "Elite" wash using Rain-X ™ brand glass treatment chemicals from a special dispenser that costs $8,000 and adds 1 minute to the Deluxe cycle time. Having run periodic specials Craig has given Bernie data on the relative popularity of the four levels of washes based on price. Bernie is considering further investment to add this fourth wash selection and making pricing adjustments accordingly, but he needs more clarity as he weighs reduction of wait times with return on investment. Bernie remembers some of Peter Drucker's wisdom as he ponders his next steps: "Efficiency is doing things right and Effectiveness is doing the right things", coupled with the key question "What does the customer most value?" 4 Bling Max Case Assignment Instructions Put yourself in the position of a management consultant helping Bernie with his business improvement decision. Organize your approach as follows: 1. Analysis of Raw Data: Fit and describe the distributions represented by the IAT (inter-arrival times) and customer-selected services data in the furnished Excel file titled "Q1 Raw Data Worksheet". Paste the RSPE histograms you develop for the IAT and ST (service time) data on your Q1 Raw Data Worksheet and into the Appendix of your report. 2. Current Situation: Open the furnished Excel file "Q2 Simulation Worksheet" and build an RSPE simulation model for Bling-Max, set the initial seed to 1234, run 10,000 iterations, and enter "current situation" data into Table 1. The current situation is long wash cycles with the integrated blow-off attached to the wash arm. 3. New Blow-off Investment: Save-as your completed Q2 Simulation Worksheet using file name Q3 Simulation Worksheet. Update the model to reflect the improved cycle times from the new blow-off. Enter the Q3 output data into Table 1. 4. New Blow-off + 4-washes with Pricing "a": Save-as your completed Q3 Simulation Worksheet using file name Q4 Simulation Worksheet. Update the model to reflect the improved cycle times from the new blow-off plus the impact of Q4 pricing (ref. Fig 3). Enter the Q4 output data into Table 1. 5. New Blow-off + 4-washes with Pricing "b": Save-as your completed Q4 Simulation Worksheet using file name Q5 Simulation Worksheet. Update the model to reflect the improved cycle times from the new blow-off plus the impact of Q5 pricing (ref. Fig 3). Enter the Q5 output data into Table 1. Analyze the results from the Q2, Q3, Q4, and Q5 simulation scenarios. What business decision would you make and why? What are the implications from the standpoint of business performance and from customer perception and retention? Follow the guidelines found on BB in the Format of a Management Report and the Grading Rubric when writing a report. Place Table 1 and output charts in your report Appendix section and use the model output to justify your conclusions and recommendations. 5 6
Recently Asked Questions
- A firm has a portfolio composed of stock A and B with normally distributed returns. Stock A has an annual expected return of 15% and annual volatility of 20%.
- I crated a matrix modeling the system for the below, can you tell me if it is correct or is there something I need to add. X1 + x2 + x3 +
- Tango Bank has contracted to lend $80 million to Delta Co. in three months’ time. This loan will be for a period of six months. To hedge against the risk of