The project developers problem

A project developer wants to develop houses of type A and B. (See Fig. 1.3) He knows that he will make a profit of € 30 000 on type A and of € 50 000 on type B. The municipality has limited the number of houses of type A to a maximum of 60 and the number of type B to a maximum of 50. Every type A needs 1 parking place while type B needs 2 parking places. The municipality has limited the total number of parking places to 150. The project developer

Figure 1.3 Two house types

Figure 1.3 Two house types wants to know which combination of houses he should develop to make the greatest profit.

We use the modelling ABC to build the model:

Define the Adjustable cells

A

B

C

D

E

F

1

Endogenous variables

N A

N B

2

Outcome

0

0

In this case the Adjustable cells are the number of houses of type A and B. In Excel we create the entries as shown above. The cells B2 and C2 are the adjustables. Select these and click the 'Make Adjustable' button on the What's Best! toolbar.

Define the Best cell

A

B

C

D

E

F

1

Endogenous variables

N A

N B

2

Outcome

0

0

3

4

Objective function

30000

50000

0

The Best cell would be the total profit given the numbers of houses of type A and B. In Excel we expand the model as shown. Select cell D4 and click the 'Maximize' button on the What's Best! toolbar to identify this cell as the cell that needs to be maximised.

Cell D4 must be the outcome of B4 times B2 plus the outcome of C4 times C2. This is in fact a representation of Equation (1.1). In Excel there is the sumproduct function, which we can use to simplify the modelling. There are two ways to create the sumproduct function depending on the Excel version you are using. The difference is in the use of semicolons or commas to separate ranges of cells. The version we are using accepts the semicolon. Should you get an error message, try using a comma instead. So, in cell D4 we type:

= sumproduct(B2:C2;B4:C4)

To make it possible to copy this formula to other rows later on, we make the relationship to row 2 absolute instead of relative. This is done by preceding the reference with a dollar (string) sign. So the formula in cell D4 becomes:

= sumproduct(B$2:C$2;B4:C4) Define the Constraints that have to be met

A

B

C

D

E

F

1

Endogenous variables

N A

N B

2

Outcome

0

0

3

4

Objective function

30000

50000

0

5

required

available

6

Max. type A

1

0

< =

60

7

Max. type B

1

0

< =

50

8

Max. parking-places

1

2

0

< =

150

The Constraints are the restrictions given by the municipality. These are in fact representations of the restrictions to which Equation (1.2) is subject. Note that the formula created in cell D4 can be copied into cells D6 to D8. All other cells contain no formulas, just values entered. We have used the 'Constraint Less Than' button on the What's Best! toolbar to define the relationship between the required resources and available resources.

The model is now ready to be solved. Just click the 'Solve' button to start the solving process. Figure 1.4 shows a screenshot of the solved model with the result: developing 60 houses of type A and 45 houses of type B will yield the highest profit, namely € 4 050000. [O e_lp-1.xls] (see page viii).

Check these things if your model did not solve:

• Select the adjustable cells, here cells B2 to C2, and (re-)click the 'Make Adjustable' button;

• Select the best cell, here cell D4, and (re-)click the 'Maximize' button;

• Check for errors in the sumproduct formulas in the best cell and the constraints, here cells D4 and D6 to D8;

• Make sure you did not type in <=, >=, or = instead of using the toolbar for creating these relationships.

Fie Edit View Insert Format Tools Data Window WB! Help Type a question for help - _ iS X

Aral - 10 . B J u % • til S ffff I - ¿1 - A - I WBMAX -r f* =SUMPRODUCTfBS2 GS2 B4:C4)_

Fie Edit View Insert Format Tools Data Window WB! Help Type a question for help - _ iS X

Aral - 10 . B J u % • til S ffff I - ¿1 - A - I WBMAX -r f* =SUMPRODUCTfBS2 GS2 B4:C4)_

A

B

c

D

E

F

G

H

1

J

K 3

1

Endogenous variables

N A

N B

2

Outcome

GO

45

3

4

Objective function

30000

50000

4050000

5

required

available

6

Max. type A

1

60

=<= 60

7

Max. type B

1

45

<=

50

8

Max. parking-places

1

2

150

-<-

150

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

?4

► ir

n A ► n|\ WB! Status \Sheetl/Sheet2 /Sheet3 /

<1 1

Figure 1.4 Screenshot solved model (project developer's problem)

Best Investment Tips and Ideas

Best Investment Tips and Ideas

The dynamics of investing can be very emotional and stressful if not properly managed. When you are aware of what is all involved you give yourself the power to avoid those situations or at least manage them effectively. That will make your investments more exciting, rewarding, and enjoyable. Those positive factors will only lead to greater success in all that achieve with investments and life.

Get My Free Ebook


Post a comment