Question

Real Estate Development

A real estate developer plans to build an apartment building near a major university aimed at generating rental income from graduate students.  Four types of apartment are being considered in the planning process: studio apartments and 1, 2 or 3-bedroom units.  It is assumed that studio apartments will be 500 sq. ft., 1-bedroom units will be 700 sq. ft., 2-bedroom units will be 800 sq. ft. and 3-bedroom units will be 1,000 sq. ft.

The develop does not believe that the building should have more than 15 1-bedroom units, 20 2-bedroom unit and 5 3-bedroom units.  Current zoning restrictions limit the building to 40,000 sq. ft. in total and no more than 45 residential units.  A local temporary housing agency has invested in the project, on the condition that it can lease five 1-bedroom units and eight 2-bedroom units for its own clients.

Estimated market rents for apartments are: studio - $350/mo., 1-bedroom - $450/mo., 2-bedroom - $550/mo. and 3-bedroom - $750/mo.  

1. Create a linear spreadsheet model to advise the developer on the optimal design.

2. What constraints are binding?

Answer 1

As a first step, create the following sheet. Put together all the information at one place. The yellow coored cell contains the objective function. Orange color cell contains the area constraint. Adjacent cell in blue contains the excel formula used to get these functions.

Once you have created this, we are ready to run the solver.

Now go to Data tab, and select Solver. The following window will pop up. Feed in the information on objective function and the constraints. Please check the boxes as shown in the snapshot.

Then click on solve. Retain the solver solution.

You will get the following output.

Type	Nos.
Studio	50
1 bedroom	5
2 Bedrooms	8
3 Bedrooms	5

Following constraints are binding:

• Total area constraint is binding
• Min number of units constraint for 1 bedroom is binding
• Min number of units contraint for 2 bedrooms is binding