See the attached file.
A real estate developer is planning a new mini apartment complex. Three types of units can be built: one-bedroom apartments, two-bedroom apartments, and three-bedroom apartments. Each one-bedroom apartment requires 650 square feet; each two-bedroom apartment requires 800 square feet; and each three-bedroom apartment requires 1,250 square feet.
The developer wants to keep a mix of apartment types in the complex. He believes that the number of one-bedroom apartments should be between 15% and 40% of the total number of apartments. Similarly, two-bedroom apartments should make up 10% to 35% of the total number of apartments, and three-bedroom apartments should not be more than 25% of the total number of apartments.
Local zoning laws do not allow the developer to build more than 42 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 project. Market studies show that one-bedrooms rent for $725 per month, two-bedrooms for $875 per month, and three-bedrooms for $1,325 per month. Assume that the silent partner will pay the market rate for its apartments.
a. Set up the above problem as a linear program.
b. Use Excel Solver to develop a building plan.
c. What is the solution if the size of one-bedroom apartments were only 675 square feet?
d. What is the solution if the size of one-bedroom apartments is 700 square feet and the proportion of 1 bedroom apartments is required to be at least 25% of the total number of units?
e. What would be the solution if we required that the maximum number of 3-bedroom apartments to be at most 30% of the total (rather than the current 25% of the total).
This is an example for how a LPP can be mathematically formulated and solved by using solver of M S excel. Detailed procedure is given. The Excel worksheet in which the problem is solved is also attached.