How can I solve the following questions using Solver in Excel? A real estate dev

Question

How can I solve the following questions using Solver in Excel?

A real estate developer is planning to build an office complex. There are three office sizes currently under consideration: small, medium, and large. Small offices can be rented for $600 per month, medium offices can be rented for $750 per month, and large offices can be rented for $1,000 per month. Each small office requires 600 square feet, each medium office requires 800 square feet, and each large office requires 1,000 square feet. The current plot of land available to the developer is 100,000 square feet. The developer wants to ensure that the office complex has at least 3 units of each office size. Moreover, zoning restrictions limit the total number of offices to 50. The developer solved this problem such that he could accrue maximum rent from the small, medium, and large offices he builds. Your job is to analyze this sensitivity report and answer the following questions:

a. How many small and large offices should the developer build?

b. What is the total optimal monthly revenue?

c. If the developer implements the optimal solution, what amount of square footage would remain unused?

d. What is the impact on the optimal allocation of offices and the objective function value if small offices can be rented for $800 per month rather than $600 per month?

e. What impact would an increase in 52,800 sq. ft of additional footage have on the optimal objective function value?

f. What impact will an increase in the monthly rental of small offices to $650 and simultaneous decrease to $800 in the monthly rental of large offices have on the current optimal solution and the objective function value?

Sensitivity Report Adjustable Cells Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease Name Cell SB$4 SC$4 SDS4 Optimal Values Small Optimal Values Medium Optimal Values Large 600 750 1000 400 250 IE+30 E+30 1E+30 250 0 0 Constraints Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 48200 Cell SES8 SES9 SES10 SES11 SES12 Name 100000 51800 Square footage Minimum no. of small Minimum no. of medium Minimum no. of large Total no. of offices E+30 41 41 400 250 3 E+30 41 50 1000 50 51.8

Explanation / Answer

a)

Let S = small office, M = medium, and L = large.

Max 600S + 750 M + 1000 L

subject to

600 S + 800 M + 1000 L <= 100000 (the square foot condition)

S >= 3; M >= 3; L >= 3 (there must be at least 3 units of each office size)

S+M+L <= 50 (there can be at most 50 rental units).

To solve the problem, as there can be only 50 rental units, and their maximum size is 1000, the maximum square footage that they can use is 50,000 < 100000, so the square footage constraint is not binding (in fact, because there must be at least 3 small and 3 medium units, the possible square footage is even less than 50,000; this doesn't matter as it is already non-binding).

If the square footage constraint is not binding, then, since there is a restriction on the number of units of any type, we want to have the most possible large units, then the most possible medium units.

We must have 3 small and 3 medium units. We can have at most 50 units. Then, we should have 44 large units. This means 3 small and 3 medium units.

The maximum income is then 3*600+3*750+44*1000 = 48050

Solution: S=3, M= 3, L = 44;

b) objective function = 48050.

C) 100000-48200=51800

D) optimal number of office sizes would remain the same as the increase of 200 is within allowable limits. Objective function value would increase by 3*200= 600. Thus the value of objective function is 48800.

E) the additional footage will have no effect as we have already exhausted the number of offices allowed with footage to spare.

F) since the allowable increase in rent for small and allowable decrease in the rent of large are greater than the changes as mentioned in the question there will be no change in allocation. The value of objective function would change by 50*3 -200*44=-8650.new value would be 48050-8650=39400

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.