## 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]

## 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.

## Post a comment