Case statement

UP Risk Management Course                                        December 2015

Instructor: Rick Zamora (all rights protected)

The ´Lampuga´ Case  (a fictitious case)

Instructions:  you must solve this case in a group of between 2 and 3 students. Present your calculations on an Excel spreadsheet, with a Summary Report specifying your answers clearly.

Time to deliver the solution: 2 weeks.

This case is meant for you to familiarize yourself with the Mathematics of Risk Simulation.

The Lampuga is one of the best restaurant brand names in Mexico City. The idea is to help the Lampuga owners to determine how risky it is to run Lampuga, financially, over the course of the next year, 2016. For the last two years, daily sales statistics have been compiled for the Lampuga Condesa outlet (Lampuga is open every day of the year). A daily count of customers has been gathered. Further, an average tab amount (in MX pesos) per customer has been compiled every day. The owners have the hunch that Lampuja daily sales (the total individual tabs or bills for all customers on a given day), are correlated to the daily affluence into the restaurant. On busy days, sales are not only bigger due to the larger number of customers eating there, but also because on such days customers tend to consume more, each of them, on average.

You are to help the Lampuga owners to prove or disprove this ´hunch´ using your Risk Simulation skills. The attached file contains the last two years´ worth of sales and attendance statistics.

Lampuga has a simple cost structure. 35% of the bill represents the variable cost to account for the food and drinks consumed by each patron. Fixed daily costs represent MX$ 20,000 to pay for rent, power, employees, etc.  

Remember that the daily statistics found in the attached Excel file have some data points which are about two years old. Therefore, it´d be a great idea to use Banco de México´s UDI daily database to bring historical peso revenues to today´s price levels (for example, 100 $ of consumption on March 2014 are, certainly, more likely about 105$ in today´s acquisitive power terms, than just $100!) It´d be advisable, then, to count on a modified set of individual customer bills´ figures, adjusted to today´s peso price levels immediately upon starting your analysis.

A host of issues should be addressed as part of your analysis. The following points should guide your work. Prepare your analyses on an Excel spreadsheet, and show all your calculations and the formulae you have used. Along with your Excel file, present an Executive Summary, in memo format, communicating to the Lampuga owners your findings. Hints are sprinkled throughout the rest of this case description. Good luck!

• Are customer daily attendances normally distributed? (hint: draw the curve, and also compare the empirical cumulative distribution function – CDF of the data, against the Gaussian CDF based on the data´s average and standard deviation)
• Are customer average bills per day normally distributed? (same hint)
• Are daily average customer bills correlated to daily customer attendances? (hint: check the linear regression of the two variables. Logically, you might want to consider daily customer attendances to be the independent variable. Remember, a low coefficient of determination does not mean there is no explanation of the behavior of the Y variable as a function of the X variable! What is the meaning of the Standard Error of the Y estimate? (you will use it later for your simulations, keep it in mind!) Is the predicted value of the X coefficient for the regression statistically significantly different from zero? (what is the t-statistic for the regressed value of the X coefficient?)
• Using the parameters found in your regression, you should be able to simulate the pattern of daily customer attendances for Lampuga. You should also be able to simulate the pattern of average daily customer bills for this restaurant. (important hint: since you may have corroborated that customer bills correlate to daily customer attendance, you should simulate customer attendances first. From customer attendance data, you should be able to use the linear relationship between the two variables to create a linearized predictor for average customer bills. This last parameter should become your average value for a specific day, from which you should further model, using random Gaussian behavior in adherence to the expected sigma for the variation of customer bills, the resulting stochastic average bill value for each given day (think how the Standard Error of Y estimate is critical for this!). Are your resulting simulations, based on the Gaussian and regression parameters you have thus far calculated, also Gaussian and consistent in their own patterns of centrality and ampleness of variation, with the actual (empirical) data for the years 2014 and 2015YTD (YTD = year-to-date)?
• Simulate a year´s worth of daily customer attendances and daily average customer bills (obviously reflecting the relationship that may exist between the two variables) at least 60+ times (i.e., 60times, or more, of a year´s worth of daily runs) Based on your simulations, answer the following questions:

• What is the probability that Lampuga will make at least 100,000$ in ´profits´ (before tax and considering that Lampuga lives in a cash=accrual accounting world) during the year? What is the probability that Lampuga will make at least $200,000 in such profits? $300,000? 400,000?
• What is the probability that, on any given day, Lampuga will ´lose´ money (i.e., that such ´profits´ will be negative)? What is the probability, on any given day, that Lampuga will make at least $1000 in such ´profits´? At least $10,000 in such ´profits´?

End of the case statement

...