Hi please can someone help me with this problem.
Office Solutions (OS) has acquired a plot of land on which it proposes to build three office blocks, Alpha, Beta and Gamma. Alpha and Beta will take two years to build and Gamma will take three years. The planning consent stipulates that all the buildings must be complete within 5 years of beginning construction. OS has 60 skilled workers available. Alpha requires 50 workers at all times, Beta 20 and Gamma 30. When complete Alpha and Gamma will attract an annual rent of £50,000, and Beta £30,000. Construct a Binary Integer Programme to identify in which year OS should begin each building and the maximum rental revenue it can achieve to offset its costs over the five- year construction period.
I thought there should be 11 decision variables but I am not sure how to approach the problem. I have started by saying let x1 be the year 1 alpha will start building, x2 be year 2 alpha will start building, x3 be year 3 alpha will start building, x4 be year 4 alpha starts building, x5 be year 1 beta will start, x6 year 2 beta, x7 year 3 beta, x8 year 4 beta, x9 year 1 gamma, x10 year 2 gamma and x11 year 3 gamma. I don't understand how to do the constraints and put it into solver on excel? I can't figure out the constraints, I was thinking it maybe be like x1+x2+x3+x4 =50 x5+x6+x7+x8 = 20 x9+x10+x11 = 30 I know the decision variables need to be binary on Excel
Any help would be appreciated Thanks in advance
Many ways lead to rome. This is my approach. I distinguish the variables according to the blocks: X,Y,Z. Let $X_i=1$ if the building of office block A starts at year $i$, $0$ otherwise. Similar definitions for the variables $Y_i$ and $Z_i$
Office block A must start in year 1,2,3 or 4: $\sum\limits_{i=1}^4 X_i=1$
Office block B must start in year 1,2,3 or 4: $\sum\limits_{i=1}^4 Y_i=1$
Office block C must start in year 1,2 or 3: $\sum\limits_{i=1}^3 Z_i=1$
Next we have to tell the program at which years a block is built when it starts at year $i$. For this purpose we introduce new variables: $A_i=1$, if the building of office block is build in year $i$.
If it starts in year 1 then the bilding years are 1 and 2. $X_1=\frac{1}{2}\cdot \left( A_1+A_2 \right)$
Similar for $X_2, X_3$ and $X_4$: $X_2=\frac{1}{2}\cdot \left( A_2+ A_3 \right)$, $X_3=\frac{1}{2}\cdot \left( A_3+A_4 \right)$ , $X_2=\frac{1}{2}\cdot \left( A_4+A_5 \right)$
Similar constraints for block $B$ and block $C$ with the variables $B_i$ and $C_i$
For each year we have at most 60 workers available.
$50\cdot A_i+20\cdot B_i+30\cdot C_i\leq 60 \ \forall i\in \{1,2,3,4,5\}$
Block A is finished after two years if $X_1=1; 0$, otherwise. We earn in total 150,000. Block A is finished after three years if $X_2=1; 0$, otherwise. We earn in total 100,000. Block B finished after four years if $X_3=1; 0$, otherwise. We earn in total 50,000.
Similar for Block B and C.
Therefore the objective function is $150000\cdot (X_1+Y_1)+100000 \cdot (X_2+Y_2)+60000\cdot Z_1+50000\cdot (X_3+Y_3)+30000\cdot Z_2$
We have 15 binary decision variables: $A_1,..., A_5, B_1,...,B_5, C_1,...C_5$. The values of $X_i$´s,$Y_i$´s and $Z_i$´s are determined by the decision variables.