The facility managers problem

A facility manager needs to replace the flooring of an office. He has the choice of two types of flooring. The one is less expensive but has high annual maintenance costs. The other is costly but has low annual maintenance costs. Material A costs € 60 per square metre and has an annual maintenance cost of € 10 per square metre. Material B costs € 90 per square metre and has an annual maintenance costs of € 8 per square metre. The total flooring area is 1250 square metres. At least the entrance area, which is 125 square metres, should be made of material B. The cost of capital is 5%. The manager has a budget of € 100 000. The total lifespan of each material is 20 years. The manager wants wants to know which combination of materials would result in the lowest life

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

question for help

WBMAX

^SUMPRODUCTfBS2 GS2,B4:G4)

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

question for help

WBMAX

^SUMPRODUCTfBS2 GS2,B4:G4)

A

B

C

D

E

F

G

H

I

J

K 3

1

Endogenous variables

N HOUS

N SERV

2

Outcome

6

4

3

4

Objective function

5

3

42

5

required

available

6

Max. area

1

2

14

=<= 14

7

Max. time

2

1

16

=<=

16

8

Max. money

8

5

68

80

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.5 Screenshot solved model (municipality's problem)

cycle costs (initial investment plus the net present value of the maintenance costs). The maintenance costs need to be paid at the end of every year.

Define the Adjustable cells

A

B

C

D

E

F

1

Endogenous variables

A A

A B

2

Outcome

0

0

In this case the Adjustable cells are the quantities of the materials A and B. The cells B2 and C2 are the adjustables.

Define the Best cell

A

B

C

D

E

F

1

Endogenous variables

A A

A B

2

Outcome

0

0

3

4

Objective function

184.62

189.70

0

The Best cell would be the total life cycle costs given the areas of type A and B. Cell D4 is the cell that needs to be minimised.

Cell D4 must be the outcome of B4 times B2 added to the outcome of C4 times C2. This is a representation of Equation (1.1). In this case the c in Equation (1.1) stands for the life cycle costs per square metre of material A and B. These are made up of both the initial costs and the present value of the annual maintenance costs. The net present value (NPV) of m annual payments C at interest rate r is (See Section 2.3):

where:

Therefore, in cell B4 we type:

= 60+10*(1/(1+0.05)*(1-1/(1+0.05)~20))/(1-1/(1+0.05))

and in cell C4 we type:

= 90+8*(1/(1+0.05)*(1-1/(1+0.05)~20))/(1-1/(1+0.05))

Define the Constraints that have to be met

A

B

C

D

E

F

1

Endogenous variables

A A

A B

2

Outcome

0

0

3

4

Objective function

184.62

189.70

0

5

required

available

6

Min. area

1

1

0

> =

1 250

7

Min area B

1

0

> =

125

8

Max. money

60

90

0

< =

100 000

The Constraints are the restrictions in area and money.

The model is now ready to be solved. Figure 1.6 shows a screenshot of the solved model with the result: using 1125 square metres of material A and 125 square metres of material B yields the lowest life cycle costs, of € 231412. [O e_lp-3.xls]

Real Estate Essentials

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!

Get My Free Ebook


Post a comment