Question 1 - Meal Outsourcing
Mr. Eiríksson was recently hired as the head of operations at Icelandic Air. As a graduate of a top American MBA program he is more than familiar with the potential impact that data driven decision making can have. The first target of Mr. Eiríksson´s efficiency campaign is the in-flight meal production plan. Currently they produce most of their salads, soups, and deserts in-house but outsource the main entrees to local kitchens that have sprung up after the most recent wave of layoffs. The five kitchens IcelandicAir has been using are Anna's, Bjorn's, Dora's, Egils's, Fanney's and Gunnar's. All kitchens can produce salads, soups and deserts, but Anna's and Egil's are not set up for entrée production.
The table below has the cost per serving for both the in-house kitchen and the local kitchens (the costs per serving for the local kitchens includes all transportation costs).
|
|
Entrée
|
Salad
|
Desert
|
Soup
|
Production Capacity (servings)
|
|
Anna's
|
|
4.50
|
3.70
|
2.90
|
8,000
|
|
Bjorn's
|
5.90
|
4.80
|
3.90
|
3.35
|
8,000
|
|
Dora's
|
6.10
|
5.00
|
4.10
|
3.60
|
7,500
|
|
Egil's
|
|
5.00
|
3.70
|
3.30
|
8,000
|
|
Fanney's
|
6.10
|
4.80
|
4.15
|
3.55
|
7,500
|
|
Gunnar's
|
6.75
|
4.80
|
3.80
|
3.40
|
8,500
|
|
In-house
|
6.10
|
4.60
|
3.80
|
3.00
|
60,000
|
Each of the small kitchens has limited production capacity (per week), which is given in the last column in the table above, and the in-house kitchen can serve up to 60,000 servings a week. In any given week Icelandic Air needs 25,000 salads servings, 26,000 servings of soup, 28,000 desserts and 28,000 entrees.
(a) Specify the decision variables.
(b) Specify the objective (as a function of the decision variables and/or intermediate variables and/or other parameters).
(c) Specify the constraints (as a function of the decision variables and/or intermediate variables and/or other parameters).
(d) List any intermediate variables/calculations you utilized above (if any).
(e) Implement your model in Excel. What is the optimal solution (please specify both the objective and the values of your decision variables)? Include Exhibit 1-1 of your implemented model that is clearly labelled (with row and column headings) and shows the formulas.
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:
- What is the expected profit per month when Mr. Palsson starts the business on his own?
- What is the probability that he makes more than $6,667 a month?
- What is the probability that he makes more than $10,000 a month?
- What is the expected profit when Mr. Palsson enters a partnership with his aunt?
- What is the probability that he makes more than $6,667 a month?
- 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.
Please do all work in excel.