Product Mix Optimization for real estate developments can be tricky; we often come across this type of discussions in the board room:

"I want more retail space!", shouted the retail director frantically.

Everyone in the meeting was silent and somewhat shocked. Development director looked at the retail director in shock.

"In fact, having more retail space makes sense.", said the master planner (external consultant) calmly.

This is a typical conversation we hear in the product mix decision meetings in a real estate developer's office. I wonder why no one does a product mix optimization exercise before finalizing the project concept or going for a feasibility study.

Product Mix Optimization in a Mixed-use Development

In a mixed-use real estate development, deciding the product mix is a challenge. Most often this is decided by the master planner with little input from management; and without any economic consideration.

Deciding the product mix for a "for-sale" development is much easier compared to a "for-lease" development. And the simple reason is the time frame.

Even in a "for-sale" development there are many variables - supply, demand, regulatory constraints and absorption.

Now I will construct a sample problem and build an excel model to solve it.

Sample Problem Construction

There is a mixed-use development being planned on a land parcel. The municipal authority has provided few guidelines for the development:

• Maximum allowed GFA = 200,000 SqM
• Minimum Community Buildings GFA = 20%
• Maximum allowed commercial GFA = 35%

The developer has acquired the land and hired a master planner. The product mix has not yet been finalized.

The market research team has completed the assessment of the market thoroughly reviewing the demand and supply dynamics. They gave the below recommendation to the development director:

By the time project is constructed and delivered to the market, the market situation will be such that it can absorb 500,000 SqFt of retail, 900,000 SqFt of residential, 800,000 FqFt of commercial and 200,000 SqFt of hospitality.

Can you help the development director in deciding product mix?

Ok, now let's solve this.

First construct the problem ready for the solver. Tabulate the margin and recommended GFA for each asset class:

Now the objective function can be defined as

Maximize: Z = 3,500 X1 + 700 X2 + 1,500 X3 + 5,000 X4 + (-2,000) X5

Subject to:

Some of the constraints are overlapping; we can rearrange the constraints as follows:

You can see here that the market can absorb 800,000 sqft of commercial space but the municipality allows only 753,480 sqft. Moreover, as such there is no demand for paid community areas but authority demands minimum of 430,560sqft of community space.

Now the non-negativity constraints

X1, X2, X3, X4, X5 >= 0

Solving Product Mix Optimization in Excel Using Solver

First download the Excel workbook and refer to the sheet "Problem Formulation".

Optimization is to find the best way to do something; and Solver is an Excel add-in that can help us in optimization.

Solver optimizes by enabling a target cell to achieve some goal, this goal may be to minimize, maximize, or even achieve some set value.

Solver is an Excel add-in supplied with Excel, but not enabled by default. We will be using Excel 2010 for this exercise.

To enable solver

File> Options>Add-ins>Click the Solver Add-in>Manage >Select Excel Add-ins>Go

Solver will now appear on the Data Tab

Applying Solver

We have already formulated our problem in excel above, and it is almost ready for applying solver.

The Solver Window

This section will explain the solver window and its use in defining the problem within solver.

A Blank Solver Window

A Filled Solver Window

Set Objective

This is the Target cell which we are trying to solve the problem for. This is the objective function of linear programming.

Our Target cell is - D3, the total profit.

Equal To:

This section defines what we want to do with our Target Cell.

We want to maximize the profit, so we will set objective to max.

By Changing Variable Cells:

This refers to the cells which will be modified by Solver to try and solve the problem, i.e. in maximizing the solution. This will be our GFAs for each asset class (cells C7: G7) and we will input 0 initially in all these cells.

Subject to the Constraints:

Constraint is the element factor or a subsystem that works as a bottleneck. They are the rules which define the limits of a possible solution to the problem.

We will add six constraints:

1. The total GFA (X1+X2+X3+X4+X5+X6 <= 2,152,800)
2. Market absorption for Retail (x1 <= 500,000)
3. Market absorption for Residential (X2 <= 900,000)
4. Maximum allowed GFA for Commercial (X3 <= 753,480)
5. Market absorption for Hospitality (X4 <= 200,000)
6. Minimum required GFA for Community (X5 <= 430,560)

To do this we will use the Add Constraint Button

Subject to the Constraints > Add

We will define our constraints in solver like this:

Total GFA: H10 <= I10

Retail Absorption: H11 <= I11

Residential Absorption: H12 <= I12

Commercial Max GFA: H13 <= I13

Hospitality Absorption: H14 <= I14

Community Min GFA: H15 >= I15

We can always change or delete constraints if we make a mistake, so don't worry.

Running the Solver Model

Prior to running the model there are a few parameters we should look at to ensure the model solves correctly.

Generally we can accept the defaults but in this case we will change the following

Tick > Make unconstrained variable non-negative

Select a solving method > Simplex LP

In the option, there are few other parameters that we may need to change:

Precision is a number from 0 to 1

Integer optimality: 5%

Max time: 100 seconds

Iteration: 100

Others we will leave default, but if need to know more about other options, I suggest you to look Excel Help, by selecting the "Help" button. Or drop me an email.

Run the Model

To Run the Model, select the "Solve" Button from the main Solver menu.

For complex model complex it may stall and you won't see any movement for a while. Relax, Solver usually recovers from these problems itself.

Once the Solver model finishes it will display a Solver Results dialog box, the first thing to notice is that "Solver Found a Solution".

The worksheet cells values will be changed to show the solution if Solver has found a solution.

You now have various options:

• Keep Solver Solution
• Restore Original Values
• Reports
• Save Scenarios

We will keep Solver solution and generate the report.

If you select all items in the report, following sheets will be added to our workbook.

• Answer Report 1
• Sensitivity Report 1
• Limits Reports 1

Save Scenario

Selecting the "Save Scenario" button takes you to the Save Scenario dialog. Scenarios as available for use in the Scenario Manager, which is accessed from the Data, What-If-Analysis Tab

Check the Result

Solver reports are somewhat bookings and probably can't be used in the management report. But off course the number from these reports can be part of final management report.

What If Solver Doesn't Find a Solution?

It may happen that Solver returns an error that "Solver could not find a feasible solution."

This may happen because of the followings:

• Your model is under or over constrained
• Your model constraints are impossible to meet
• Your model constraints have an error

These can be easily resolved by checking the current constraints for errors and ambiguities

Now we know how much of each asset class we should build to maximize our development margin.

What should we do with an asset class that has a faster absorption but a lower margin? How can we build a product optimization model in excel which take care of absorption? Let us know in the comments below.

Download the Product Mix Optimization Excel Model