Answer these three questions in Excel. The third question is attached as a picture. .
#1 – Miko’ Bakery (sensitivity analysis application)
Miko’s Bakery is a pie shop that specializes in custard and fruit pies. It makes delicious pies and sells them at reasonable prices so that it can sell all the pies it makes in a day. Every dozen custard pies net Miko’s $15 and requires 12 pounds of flour, 50 eggs, 5 pounds of sugar, and no fruit mixture. Every dozen fruit pies nets a $25 profit and uses 10 pounds of flour, 40 eggs, 10 pounds of sugar, and 15 pounds of fruit mixture. On a given day, Miko’s found that they had 150 pounds of flour, 500 eggs, 90 pounds of sugar, and 120 pounds of fruit mixture available. Formulate and solve a linear program that will give the optimal production mix for this day at Miko’s.
a) What is the pie mix and the optimal profit?
b) If Miko’s could double its profit on custard pies, should more custard pies be produced? Explain.
c) If Miko’s raised the price on Fruit pies by $0.25 per pie and hence the profit, would the optimal production schedule change? Would profit change?
d) Suppose Miko’s found that 10% of its fruit mixture had been stored in containers that were not air-tight. For quality and health reasons, he decided to not use any of this portion of the mixture. How would this affect the optimal production schedule? Explain.
e) What is binding under the current production schedule?
f) How much would profit increase for each additional egg acquired and how much would it go down for each additional egg lost? What range would this apply to (range of feasibility)?
g) Miko’s has in the past made a third type of pie, a chocolate pie. Given the current prices of ingredients, Miko’s estimates that it would net a profit of $27 per dozen chocolate pies. Each dozen chocolate pies requires 15 pounds of flour, 30 eggs, 12 pounds of sugar and no fruit mixture. Reformulate and resolve the model with this new information. Would it be profitable to make any chocolate pies? Analyze the reduced cost for chocolate pies to explain why we are not making any.
#2 – Integer Staffing Model
Miko Consulting is expanding its IT consulting business to the Blair county area. The company wishes to be able to serve at least 75 personal and 25 corporate accounts per week. Miko Consulting plans to hire three levels of employees. Software Engineers, IT Specialists, and IT Technicians. The following table gives the weekly salary level as well as the projection of the expected number of accounts each can service weekly.
|
Employee Type |
Personal Accts |
Corporate Accts |
Weekly Salary |
|
Software Engineers |
3 |
4 |
$2200 |
|
IT Specialists |
5 |
1 |
$1400 |
|
IT Technicians |
5 |
0 |
$750 |
a) How many of each employee type should Miko consulting hire?
b) What is Miko consulting’s total weekly salary cost?
#3 – Binary Project Selection Model – Complete Chris’s Capital Budgeting Ballyhoo on page 550 of your course text and answer the questions below.
a) Which projects should Chris and his firm invest in?
b) What is the total expected NPV of doing the projects selected?
c) How much total capital is used over the 5 years?
d) Why are certain projects being left out?
0 comments