## LP and multicriteria optimisation

In this section the Constraint method (see Chapter 6) in multi-criteria optimisation is used for allocating different types of houses to a residential area. The municipality has come into contact with a project developer interested in developing four types of houses (see Fig. 1.7). The types of houses differ mainly

□ Microsoft Excel - e_lp-3jds ill] FJe Edit View Insert Format lools Data Window WB! Help n j i.j .J 4 7«, * -)-<»- s, >: •

Type*

question for help

WHHN

^SUMPRODUCTfBS2 GS2,B4:G4)

ill] FJe Edit View Insert Format lools Data Window WB! Help n j i.j .J 4 7«, * -)-<»- s, >: •

Type*

question for help

WHHN

^SUMPRODUCTfBS2 GS2,B4:G4)

A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K 3 | |

1 |
Endogenous variables |
A A |
A B | ||||||||

2 |
Outcome |
1125 |
125 | ||||||||

3 | |||||||||||

4 |
Objective function |
184.6221 |
189.6977 |
231412.1 | |||||||

5 |
required |
available | |||||||||

6 |
Min. area |
1 |
1 |
1250 |
=>= 1250 | ||||||

7 |
Min. area B |
1 |
125 |
=>= |
125 | ||||||

8 |
Max. money |
60 |
90 |
78750 |
100000 | ||||||

9 | |||||||||||

10 | |||||||||||

11 | |||||||||||

12 | |||||||||||

13 | |||||||||||

14 | |||||||||||

15 | |||||||||||

16 | |||||||||||

17 | |||||||||||

18 | |||||||||||

19 | |||||||||||

20 | |||||||||||

21 | |||||||||||

22 | |||||||||||

23 | |||||||||||

?4 |
► ir | ||||||||||

h A ► n|\ WB! Status \Sheetl/Sheet2 /Sheet3 / |
<l I |

Figure 1.6 Screenshot solved model (facility manager's problem)

Table 1.1 Data of four types of houses

Type Selling price Minimum Maximum Developer's fee

225 000 275 000 300 000 225 000

11 250 13 750 15 000 11 250

in the selling price, ranging from affordable to expensive houses. The municipality has limited the total number of houses to between 200 and 260. The selling prices have been established, as has the developer's fee. The municipality wants to make sure that not only the expensive types of houses (with the largest fees) will be built. This is done by restricting the minimum and maximum percentages of the affordable types (as percentages of the total number of houses built). Note that contrary to the previous examples, there are now two stakeholders instead of one. All data is summarised in Table 1.1.

Figure 1.7 Four house types

Figure 1.7 Four house types

Define the Adjustable cells

A |
B |
C |
D |
E |
F |
G |
H |
I | |

1 |
Endogenous variables |
N A |
N C |
N L |
N M |
N TOT | |||

2 |
Outcome |
0 |
0 |
0 |
0 |
0 |

In this case the Adjustable cells are the number of houses of type A, C, L, and M. Because there are also restrictions in regard to the total number of houses, we also create an adjustable cell for this. The cells B2 to F2 are the adjustables.

Define the Best cell

A |
B |
C |
D |
E |
F |
G |
H |
I | |

1 |
Endogenous variables |
N A |
N C |
N L |
N M |
N TOT | |||

2 |
Outcome |
0 |
0 |
0 |
0 |
0 | |||

3 | |||||||||

4 |
Objective function |
1 |
1 |
0 |

The municipality wants to build as many affordable houses (type A and M) as possible. The project developer, however, wants to make as much profit as possible. In LP terms: the two stakeholders have objective functions made up of different units (houses and euros). An objective function cannot be made up of different units. An advanced way to resolve this, using Preference Modelling, is described in Chapter 6. A less advanced, but very effective, way is to first optimise using one stakeholder's objective function and to then add this outcome as a restriction to the model. We will assume that the municipality's objective function is used for the first optimisation. The Best cell would then be the total number of affordable houses. Cell D4 is the cell that needs to be maximised.

A |
B |
C |
D |
E |
F |
G |
H |
I | |

1 |
Endogenous variables |
N A |
N C |
N L |
N M |
N TOT | |||

2 |
Outcome |
0 |
0 |
0 |
0 |
0 | |||

3 | |||||||||

4 |
Objective function |
1 |
1 |
0 | |||||

5 | |||||||||

6 |
Min. houses |
1 |
0 |
> = |
200 | ||||

7 |
Max. houses |
1 |
0 |
< = |
260 | ||||

8 |
Min. houses A |
1 |
-0.2 |
0 |
> = |
0 | |||

9 |
Max. houses A |
1 |
-0.3 |
0 |
< = |
0 | |||

10 |
Min. houses M |
1 |
-0.15 |
0 |
> = |
0 | |||

11 |
Max. houses M |
1 |
-0.2 |
0 |
< = |
0 | |||

12 |
Total houses |
-1 |
-1 |
-1 |
-1 |
1 |
0 |
= |
0 |

The Constraints are the restrictions given by the municipality. One special restriction needs to be added, defining the total number of houses as the sum of all different types of houses (row 12).

The model is now ready to be solved. Figure 1.8 shows a screenshot of the solved model with the result: a maximum of 130 affordable houses can be built. [O e_lp-4.xls]

The municipality's optimum is then added as a minimum restriction to the model. The objective function is also changed: the project developer's fee is maximised. Figure 1.9 shows a screenshot of the solved model, with the added restriction and altered objective function. The developer's optimal fee turns out to be € 3412 500.

Note that the only difference between the two outcomes is the number of houses of type C and type L. In the first run, it did not really matter how the number of houses of type C and L were distributed because the objective function was aimed at maximising the number of affordable houses. In the second run this did matter because the objective function was aimed at maximising the developer's fee and the fee of type L was higher than that of type C. Also note that both outcomes are in essence acceptable. The second outcome is better however, due to the higher profit the developer will make.

## Real Estate Essentials

Tap into the secrets of the top investors… Discover The Untold Real Estate Investing Secrets Used By The World’s Top Millionaires To Generate Massive Amounts Of Passive Incomes To Feed Their Families For Decades! Finally You Can Fully Equip Yourself With These “Must Have” Investing Tools For Creating Financial Freedom And Living A Life Of Luxury!

## Post a comment