1. Write out detail explanation of answer clearly in a Word document.
2. include a copy of your sensitivity report and other portions of the spreadsheet needed to answer the questions asked.
3. Need screen shots of parameter screens etc...
Minicase #1
A real estate developer is planning to build an apartment building specifically for graduate students on a parcel of land adjacent to a major university. Four types of apartments can be included in the building: efficiencies, and one-, two-, and three-bedroom units.
Each efficiency requires 500 square feet; each one-bedroom apartment requires 700 square feet; each two-bedroom apartment requires 800 square feet; and each three-bedroom unit requires 1,000 square feet. The developer believes that the building should include no more than 15 one-bedroom units, 22 two-bedroom units and 10 three-bedroom units. Local zoning ordinances do not allow the developer to build more than 40 units in this particular building location, and restrict the building to a maximum of 40,000 square feet.
The developer has already agreed to lease 5 one-bedroom units and 8 two-bedroom units to a local rental agency that is a 'silent partner" in this endeavor. Market studies indicate that efficiencies can be rented for $550permonth, one bedrooms for $650 per month, two-bedrooms for $750 per month and three-bedrooms for $950 per month.
The developer wants to know how many units of each type should be included in the building plans in order to maximize the potential rental income from the building?
QUESTIONS
1. Write out the complete algebraic formulation (decision variables, objective function, constraints).
2. Build a spreadsheet model that incorporates the entire algebraic model you just developed in question 1. Find the optimal solution using the Solver. Make sure you save the sensitivity report.
a. How many of each type of housing unit should be included (fractions are allowed)?
b. What is the maximum total rental income possible?
c. Which constraint(s) are binding?
3. Using the sensitivity report:
a. How much would the rental income go up if we could add one more 3-bedroom unit?
b. How many more 3-bedroom units could be added at the same shadow price?
c. How much would the rental income go up if we could increase the total number of units to 41?
d. What would happen to the rental income if one efficiency unit had to be included in the optimal mix?
e. How much could the rentdecrease on the 2-bedroom and still keep the same plan (i.e., optimal mix of units)?
Minicase #2
The Frisbee Pie company can make pie tins for bakeries, which are willing to pay ($46 - $2.75*Q1) per dozen, where Q1 is the number of dozens of pie tins. But the fraternities and sororities at the nearby university are willing to pay ($42 - $3.25*Q2) per dozen, where Q2 is the number of dozens of pie tins {to be used to throw around the front lawns of their houses}. It costs ($26 + $2.95*Q) to produce Q dozen tins. Management wants to maximize profit (i.e., revenue minus cost).
QUESTIONS
4. Write out the complete algebraic formulation (decision variables, objective function, constraints).
5a. How many dozen (fractions are allowed) should be made for sale to the bakeries?
5b. What price per dozen should be charged for the ones sold to the bakeries?
5c. How many dozen (fractions are allowed) should be made for sale to the fraternities/sororities?
5d. What price per dozen should be charged for the ones sold to the fraternities/sororities?
5e. What is the total profit?