Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

BUSI4489 Management Science for Decision Support

Assignment 2023/24

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 £1X per mile. You may assume that the company has sufficient dumper trucks available.

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.

Some information in this problem depends on your personal student ID number. Let X and Y be the last two digits of your student iD. For example, if your ID number ends with 07, then X = 0 and Y = 7. Your dumper truck running cost per mile is the £10. 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.

You have to produce a report (indicative word limit 1500 words -  the word limit does not include diagrams, tables, formulas etc, and references to the literature are not required for this coursework – do not (massively) exceed the word limit!).

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 submit your report before the due date (Thursday 30^th November 2023, 3pm) electronically through Moodle. Your report should be submitted as a word or pdf document (do not submit Excel files). Note that this is an individual assignment and that you have to write up your report individually. This assignment counts for 25% of the module mark.

Hint: You need to be a little careful when defining decision variables and especially constraints. Only define variables/constraints if these are really required. Note that Solver in Excel is limited to 200 variables and 100 constraints but this should be enough.

Good luck!