A building company needs to move earth and level the terrain at three sites: site $1$ has a surplus of $300$ truckloads of earth; site $2$ has a surplus of $1200$ truckloads of earth and site $3$ has a deficit of $700$ truckloads of earth. Earth at surplus locations is loaded via bulldozers onto dumper trucks and then transported to site $3$ to rectify the deficit. Any earth in excess of site $3$s deficit must be disposed off at a dumpsite. The distances between the relevant sites are shown in the table below and running a dumper truck cost $1$X per mile. You may assume that the company has sufficient dumper trucks available.

From$/$to Site $3$ Dump site

Site $14$ miles $1$ mile

Site $28$ miles $6$ miles

Bulldozers are required to load dumper trucks at the surplus locations $($sites $1$ & $2)$ as well as to level the earth at site $3.$ One bulldozer can load up to $50$ dumper trucks per day; one bulldozer can also level the earth at site $3$ of up to $100$ truckloads per day. Bulldozers are not required at the dump site. The company has $5$ bulldozers available, which are pre$-$positioned at different sites $($see below$).$

Bulldozers can be transported between sites $1,2$ and $3,$ but this will take a $2$ day lead time. When bulldozers are not needed anymore, they are transported to the company$$s depot, and this takes one full day. Operating a bulldozer at a site costs $2500$ per day per bulldozer; transporting a bulldozer between sites costs $700$ per bulldozer $($lead time is $2$ days$)$; transporting $($and retiring$)$ a bulldozer to the depot costs $300$ per bulldozer $($lead time $1$ day$).$ The company needs to complete the project within $10$ working days. This means that at the end of day $10($or earlier$)$ all $5$ bulldozers have to be back at the depot.

X $=8$ and Y $=1.$ Your dumper truck running cost per mile is the $18$ The value for Y determines the bulldozer pre$-$positioning $($at the start of the planning horizon$).$ If Y $<5,$ then $1$ bulldozer is positioned at site $1$; $1$ bulldozer is positioned at site $2$; and $3$ bulldozers are positioned at site $3.$ If Y $>=5,$ the $4$ bulldozers are positioned at site $1$; $1$ bulldozer at site $2$ and $0$ bulldozers at site $3.$

You may assume that all bulldozer transfers are initiated at the beginning of a work day. For example, if a bulldozer is transferred at the start of day t from site $1$ to site $3,$ then this bulldozer cannot be working during days t and t$+1,$ and will be available to work on site $3$ at the start of day t$+2.$ When working at a site, the bulldozer cost is $2500$ per day, irrespective of whether the bulldozer is working $($or not$)$ at its maximum capacity $($i$.$e$.,$ loading at most $50$ trucks per day at sites $1$ & $2,$ or levelling earth at most at $100$ truck loads per day at site $3).$

The company carrying out the works wants to find a good$/$optimal terrain levelling & bulldozer allocation plan and has hired you as a Management Science consultant for advice.

In this report, you first analyse the problem $($how can you structure this problem and what type of model$($s$)$ are you considering? what are the key decisions to be made? What are the different and relevant cost components? What are the key decision variables? What are the different types of constraints? What additional assumptions $($if any$)$ do you make?$).$ Then you describe the model$($s$)$ that you have developed$/$formulated $($this is all independent of excel!$)$: you need to define precisely the decision variables, the objective function and the constraints. Explanation is important here and you won$$t be able to write down all the constraints but you should include an example of each different type. Probably some variables are of the integer type! Next you show a screenshot of your model set up in excel $($this is the implementation$)$ and you highlight any specific features $($explanation is needed for example, if the structure of the model is not clearly visible in excel$).$ Finally, you need to interpret and discuss the solution $$ how are bulldozers used$/$transferred between sites? How many truck loads are transported between sites? What are the different costs? Is the prepositioning of bulldozers a good one? Any recommendations to the company?

please write the constraint that would be used for excel solver