A real estate developer is planning to build an office complex. Currently, there are three office sizes under construction; small, medium, and large. Small offices can be rented for $650 per month, medium offices can be rented for $780 per month, and large offices can be rented for $1100 per month. Each small office requires 430 square feet of space, each medium office requires 600 and each large office requires 850 square feet. The current plot of land available to the developer is 50,000 square feet. The developer wants to ensure that the office complex has at least 5 units of each office size. Moreover, zoning restrictions limit the total number of offices to 45.
a) Formulate a Linear Program that determines how many of each office size should be built to maximize revenue from rentals.
b) Use Excel solver to model and solve the problem. Attach four snapshots of your setup: Final look, Formula view, Solver menu, Sensitivity report (Answer report optional).
Use the answer report and/or sensitivity report to answer the questions below (i.e. do not modify and re-solve the model to answer any of the following parts).
c) How many small, medium, and large offices should the developer build? What is the total optimal monthly revenue?
d) Which constraints are binding? Which constraints are non-binding?
e) How much square footage would remain unused if the developer implements the optimal solution?
f) What would be the impact on the optimal number of offices and the total rental revenue (OFV) if small offices can be rented for $800/month rather than $650/month?
g) What would be the impact on the optimal number of offices and the total rental revenue (OFV) if medium offices can be rented for $680/month instead of $780/month?
h) What would be the impact on the optimal number of offices and the total rental revenue (OFV) if medium offices can be rented for $1150 rather than $780/month?
i) Suppose that the square footage available to the developer decreases to 35,000 square feet. What impact would this have on the total rental revenue (OFV)?
j) Suppose that the minimal number of small offices that the developer needs to build changes to 8 offices. What impact would this have on the total rental revenue (OFV)?
k) Suppose that new zoning restrictions will require no more than 40 office spaces on this land (instead of 45). What impact would this have on the total rental revenue (OFV)?