Question 2 Mr. Palsson is an MBA candidate who is considering his life choices. He has spent the past year and a half preparing for a career in consulting and has recently landed a job with a respectable firm with a annual starting salary of $83,000. Although quite satisfied with the offer, he is also considering starting a restaurant. He has always had passion for food, and believes he can start a new restaurant to compete for the lunch crowd in Downtown College Park, given the poor food choices available. In particular, he has built a business plan for the restaurant. He has figured that rent, utilities and other unavoidable costs will be $4,000 a month. Variable costs will mostly consist of labor costs, which he expects to be uniformly distributed between $5,000 and $7,000, and raw material costs that depend on the number of prepared meals. Depending on the interest in the lunches, he estimates he can set the price per lunch somewhere between $15 and $20, and still have a healthy volume of 3,000 meals served per month. Although his price calculations are based on a volume of 3,000 he also acknowledges that there will be some variation around his estimate, in particular he models the volume as a normal distribution with a standard deviation of a 1,000. His estimated probabilities for the success of the lunch place and the associated prices are summarized in Exhibit 1. The lunch meals, will be higher quality lunches, and he estimates the raw material will be $11 on average. The restaurant business is a risky business and his well off aunt has offered to partner up with him. In particular she has offered to guarantee a minimum salary of $3,500 a month, but instead if the monthly profit is greater than $9,000, she receives 90% of every dollar in profit above the $9,000 mark. Interest level Price per Meal Probability Great $ 20.00 20% Good $ 18.50 35% Medium $ 16.50 30% Low $ 15.00 15% Exhibit 1: Price and Interest Level Scenarios Assumptions: • You may ignore all tax considerations. If you choose to model them be specific about the assumptions you make • For simplicity, assume that profit=Mr. Palsson’s salary. • You may ignore any long-term considerations (such as long term personal growth, etc) and focus your analysis on the monthly level The task: a) Build an Excel model that calculates the expected profit for Mr. Palsson in a particular month if he undertakes the venture on his own, when the interest in the restaurant is at the medium level, and the variable cost is set at the expected value. Include Exhibit 3-1 of your model that shows formulas and row and column headings. What is the profit for Mr. Palsson in this scenario? b) Extend the model to include both profit calculations when Mr. Palsson enters the restaurant business on his own as well as when he partners up with his aunt. Convert your static model to a simulation model by specifying appropriate probability distributions for uncertain inputs using @Risk functionality. Then, run the simulation for both scenarios (without and with support from the aunt) and answer the following questions based on your results: o What is the expected profit per month when Mr. Palsson starts the business on his own? o What is the probability that he makes more than $6,667 a month? o What is the probability that he makes more than $10,000 a month? o What is the expected profit when Mr. Palsson enters a partnership with his aunt? o What is the probability that he makes more than $6,667 a month? o What is the probability that he makes more than $10,000 a month? Include an Exhibit 3-2 of your updated model, be sure to show formulas and include row and column headings. Provide a histogram of the profit (salary) under partnership and label it as Exhibit 3-3. How would you (briefly) explain the shape of the histogram. c) What would you recommend Mr. Palsson do (200 words max)? Include any additional Exhibits to support your answers as Exhibit 3-extra.