Yann grew up in Normandy of France and then moved to the US working in hospitality consulting for almost 20 years. Always fascinated about his hometown cuisine, Yann wants to open a gourmet French restaurant in Dallas. Leveraging his experience in the hospitality industry, Yann has developed a pro-forma monthly cash flow analysis, which is shown in Exhibit 3.1, to better understand the profitability and feasibility of this business. Number of Customers Average Sales Direct Cost of Sales (per customer) Gross Margin Server Payroll Other Fixed Costs Total Operating Expenses Profit Exhibit 3.1: Monthly Cash Flow for Yann's Restaurant 2500 $30.00 per customer 40% of average Sales $ 45,000.00 $10 $8,000 $33,000 $ 12,000.00 =(Number of Customers) (Average Sales)*(1-Direct Cost Percentage) per customer =(Server Payroll per Customer)*(Number of Customers)+(Other Fixed Costs) =(Gross Margin)-(Total Operating Expenses) Unfortunately, just about every number in this analysis is uncertain. Yann wants to build a Monte Carlo simulation model to better understand the risks of this venture. From his interaction with restaurant owners in other cities, Yann estimates probability distributions for the variables in his pro-forma analysis as follows: Number of customers per month: This is very hard to predict. Yann is optimistic about the demand but recognizes the uncertainty and assigns a lognormal distribution with mean 2,500 and standard deviation 800. Average sales per customer: Yann's prices will be set according to his estimate of what customers are willing to pay. At this point, he is uncertain about the average sales per customer and assigns a normal distribution with a mean of $30.00 per customer and a standard deviation of $7.00. Direct cost of sales per customer as a percentage of Average Sales: This includes the costs of food served to customers as well as other incidental expenses (e.g., laundry), which are expected to be proportional to the average sales. Yann assigns uniform distribution between 20% and 60% to this percentage. Server Payroll costs per customer: Yann assigns a triangular distribution with a most likely value of $10, an optimistic value of $7 and a pessimistic of $15. Other fixed costs: Yann has identified a location in norther suburb of Dallas and estimates that rent, utilities, and insurance for that location will cost about $8,000 dollars per month. He is quite confident about this and does not feel the need to model it as uncertain. Build a simulation model of this venture to answer the following questions. For all questions, write your answers on the Answer Sheet for this problem (provided separately) and submit appropriate documentation with the answer sheet. a) What is the estimated expected profit per month? What is the probability that Yann will lose money for a given month? What about the probability of making more than $20,000 per month with this restaurant? (10 points) In his original analysis, Yann assumed that he has enough capacity in his restaurant to meet all demand. Upon reflection, he realizes that the size of the restaurant may limit the number of customers that he can serve and that his probability distribution for the number of customers represents actual demand rather than the number of customers he can actually serve. With his planned location (call it Location 1), he could serve at most 3,500 customers per month. If demand exceeds this level, customers would be turned away. This capacity constraint would limit the upside potential associated with very high demands. Thinking through this, he decides to reconsider two of the other locations he had considered previously to see how they might compare in light of the uncertainty about demand. The maximum capacity (in terms of customers per month) and fixed costs (rent, utilities and insurance, etc) for the locations are shown in Exhibit 3.2. These three locations are all comparable in other respects and the assumptions about demand (= the number of potential customers) and average sales, direct costs of sales and payroll are all as above. Exhibit 3.2: Candidate Locations for Yann Location 1 2 3 Maximum Capacity (Customers per month) 3500 2500 5000 Fixed Cost ($ per month) 8000 5000 12500 b) Revise your previous spreadsheet model and set it up as a parametrized simulation model (with location being the parameter). Run the three simulations, one for each location. What are the expected profits for these three locations? Based on this, which location is the best? (10 points) c) Suppose Yann is risk averse. To select a location, he needs to make the tradeoff between expected profit versus the risk associated with each location. Draw an overlay chart of the profits for the three locations based on your simulation in Part b) to determine if one location is riskier than the others. Can you rule out any of the locations for Yann? Namely, is there any location is dominated by another location in terms of profitability? (Hint: drawing the overlay chart in cumulative view with smooth line may be helpful!) (5 points) If you are using cloud-based ASP, you may report the standard deviation of the profits for the three locations based on your simulation in Part b) to determine if one location is riskier than the others. And then answer the questions accordingly.