Monash University Faculty of Information Technology 2nd Semester 2024
FIT3158 Business Decision Modelling
Assignment 2: Linear Programming, Integer Linear Programming, Network Analysis,Transportation, Transshipment and Economic Order Quantity (EOQ) - using Microsoft ExcelSolver
This assignment is worth 24% of your final mark (subject to the hurdles described in theFIT3158 Unit Guide and links therein). Among other things (see below), note the need to hitthe `Submit’ button (and the possible requirement of a video and/or an interview).Due Date: Monday 30th September 2024, 11:55 pmMethod of submission: Your submission should consist of 2 files:
- A Microsoft Excel spreadsheet named as:FamilyName-StudentId-2ndSem2024FIT3158Asst2.xlsx
- A text-based .pdf file named as: FamilyName-StudentId-2ndSem2024FIT3158Asst2.pdfBoth the files must be uploaded on the FIT3158 Moodle site by the due date and time.The text-based .pdf file will undergo a similarity check by Turnitin at the time you submitto Moodle. Please read submission instructions here and elsewhere carefully regardingthe use of Moodle.
Total available marks = 50 + 34 + 16 = 100 marks.
Note 1: Please recall the Academic Integrity rules (and, as per Moodle week 0, you mightalso wish to confer with https://www.monash.edu/student-academic-success). This is anindividual assignment. Recall various resources from FIT3158 Moodle week 0.
n submitting this assignment, you acknowledge both that you are familiar with the relevantpolicies, rules and regulations regarding Academic Integrity (including, e.g., doing your ownwork, not sharing your work, not using ChatGPT in particular, not using generative AI at all)and also that you are familiar with the consequences of being deemed to be in contraventionof these policies.
Note 2: And a reminder not to post even part of a proposed partial solution to a forum or
other public location. This includes when you are seeking clarification of a question.
If you seek clarification on an Assignment question then – bearing in mind the above – word
your question very carefully and see the `Welcome to FIT3158’ Ed Discussion forum post
(bearing in mind instructions in week 1 Introductory Lecture). If you are seeking to
nderstand a concept better, then (please see the Welcome post in Ed Discussion, and) try toword your question so that it is a long way removed from the Assignment (which, in turn,
makes it easier to answer – and probably also able to be answered by more people). You arereminded that Monash University takes academic integrity very seriously.
Note 3: As previously advised, it is your responsibility to be familiar with the special
consideration policies and special consideration 代 写FIT3158 Business Decision Modellingprocess – as well as academic integrity.Please see the relevant links within FIT3158 Moodle (week 0 and perhaps also elsewhere).Students should be familiar with the special consideration policies and the process for
applying. Note 4: As a general rule, don’t just give a number or an answer like `Yes’ or `No’ without
t least some clear and sufficient explanation - or, otherwise, you risk being awarded 0
marks for the relevant exercise. Make it easy for the person/people marking your work tofollow your reasoning. Your .pdf should typically cross-reference the corresponding
answer in your spreadsheet. For each and every question, sub-question and exercise,
provide a clearly labelled separate spreadsheet tab with clear content, accompanied with
clearly cross-referenced clear .pdf explanation. Without clear cross-reference between .pdf
and spreadsheet tab – and without a separate spreadsheet tab for each sub-question -
there is the possibility that any such exercise will be awarded 0 marks.
Your .pdf file should not be generated from LaTeX (and/or Overleaf), nor OpenOffice, nor
LibreOffice.
Note 4b (and this is part of Note 4) : Re-iterating a point above, for each and every question,
sub-question and exercise, clearly explain your answer and clearly show any working.
Note 5: As a general rule, if there is an elegant way of answering a question without
unnecessarily re-running the Solver, try to do it that way. (Recall, e.g., sensitivity report and
some notions from Week 3 and thereabouts.) More generally, more elegant solutions are
preferable - and will at least sometimes be given more marks (possibly many more marks,
and the inelegant way might possibly get very few marks). Among other things, if a problem
is a linear programming (LP) problem, then it would be more elegant to solve it using the
linear simplex model. In a similar vein, a linking constraint (where appropriate) will be far
preferable to a seemingly equivalent use of the IF() function.
Note 6: You should include a screenshot of the relevant spreadsheet tab in your .pdf file.
Apart from this, all of your submitted work should be in machine readable form, and none of
your submitted work should be hand-written.
Note 7: If you wish for your work to be marked and not to accrue (possibly considerable)
late penalties, then make sure to upload the correct files and (not to leave your files
as Draft). You then need to determine whether you have all files uploaded and that you are
ready to hit `Submit’. Once you hit `Submit’, you give consent for us to begin marking yourwork. If you hit `Submit’ without all files uploaded then you will probably be deemed not toave followed the instructions from the Notes above. If you leave your work as Draft andhave not hit `Submit’ then we have not received it, and it can accrue late penalties once the
deadline passes. In short, make sure to hit ‘Submit’ at the appropriate time to make sure thatyour work is submitted. Late penalties will be as per Monash University Faculty of IT andMonash University policies. Within the requirements of these policies just mentioned, late
penalties will accrue (in line with policy) at a rate of at least 5% per calendar day – where 5%refers to a proportion of the total marks available. It is possible that any work submitted atleast 10 calendar days after the deadline might be automatically given a mark of 0.
Note 8: The notation 1E-12 corresponds to 1 x 10-12, or 0.000000000001. A similar comment
applies to 1E-14. If you see a figure of approximately this magnitude or comparablemagnitude, then consider whether or not it might be round-off error for something else.Unless otherwise stated, numbers should be stated to at least 3 significant figures, and
numbers less than 10 in magnitude should be stated to at least 3 decimal places. Note 9: Save your file(s) regularly. Most of the time, we expect that the Solver will runquickly. But for problems with many variables and many constraints – especially involvingintegers – please be mindful that if you are not careful to do some of the things mentioned in
class to help your program finish more quickly, then there is a risk that your program might
possibly go through at least tens or hundreds of thousands of subproblems and become very
slow (as you wait and wait and …). If you save your file before starting a run that could belong and slow, then you can safely stop the program – if it becomes very slow – with reducedrisk of losing your edit changes.
Note 10: As a general rule for solving a problem using MicroSoft Excel Solver, please
consider carefully whether the various solver (settings or) Options (which you mightbe able to access after clicking on `Options’, which might be on the right about twothirds of the way down after you click on `Solver’) might affect the results providedby the solver. (As an example, if dealing with integers then give some thought to IntegerOptimality’.) Put another way, rather than just use the default settings, make sure to checkthe solver settings and be willing to appropriately modify them if and as required.The optimisations to follow throughout Assignment 2 (Questions 1, 2 and 3) could
variously) include such considerations as (including but not limited to) (e.g.) limitingpollution, saving time, reducing waste, making the most of limited resources, improvingorgan donor and recipient compatibility, etc. It could involve work with the United Nations(UN) and/or the International Space Station (ISS). Any transportation, transshipment and/orshortest path problems could possibly pertain to emergency vehicles (e.g., ambulance, fire,police) and to Search and Rescue (e.g., drones) – or simply cars and trucks.
Question 1 (5 + 4 + 4 + 4 + 6 + 5 + 6 + 5 + 5 + 6 = 50 marks)
Throughout Question 1 and the entire Assignment 2, follow the given notes (near the top –
e.g., Note 4, Note 5, Note 6, etc.) and other instructions.Consider now Figure 1 immediately below but with the modifications stated just below it. We make some changes. First, the edge JI now has cost 20. We also add another node (orvertex), K. We also add three edges – namely, HK (cost 45), JK (cost 70) and IK (cost 45).This describes the network which we analyse at least for the start of Question 1.efore starting the exercises in Question 1, please remember that it is the modified version ofFigure 1 (with modifications stated immediately above) that should be used at least to startthe question.
Unless stated otherwise, throughout Question 1 we use the modified version of Figure 1.tated edge costs are the costs of moving one vehicle – e.g., moving 2 vehicles long an edge
with stated cost 64 would cost 128.
1a) We wish to move 70 vehicles from A to K.What is the shortest total cost, and what is the flow along the individual edges?
Express all variables, the objective function, all constraints, working and solution (values ofdecision variables, value of objective function).Also explain whether or not the problem is a linear programming (LP) problem or not - andwhy or why not.If the problem can be presented as an LP problem then it should be done that way.)urther to Note 4 and Note 6, (here and elsewhere) please place your working and solutionsas instructed.
1b) We wish to move 100 vehicles from B to I.
What is the shortest total cost, and what is the flow along the individual edges?Express all variables, the objective function, all constraints, working and solution (values ofdecision variables, value of objective function).
Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutionsas instructed. 1c) We wish to move 70 vehicles from A to I.
What is the shortest total cost, and what is the flow along the individual edges?Express all variables, the objective function, all constraints, working and solution (values ofecision variables, value of objective function).Also explain whether or not the problem is a linear programming (LP) problem or not - andwhy or why not.(If the problem can be presented as an LP problem then it should be done that way.)Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutionsas instructed.
1d) We wish to move 100 vehicles from B to K.What is the shortest total cost, and what is the flow along the individual edges?
Express all variables, the objective function, all constraints, working and solution (values of
decision variables, value of objective function).
Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutions
as instructed.
1e) We re-visit the problem from 1d). However, we make some changes.The edges HK and IK (which had cost of 45 in parts 1a-1d) are now both modified asfollows. For flow up to 75 (i.e., < 75), these edges still cost 45 per unit. But for flows above
75, the excess flow costs 80 per unit. So, as an example, if HK has a flow of 90 then thacosts 45 * 75 + 80 * (90 – 75) = 45 * 75 + 80 * 15 = 3375 + 1200 = 4575 for the flow on that
edge.What is the shortest total cost, and what is the flow along the individual edges?Express all variables, the objective function, all constraints, working and solution (values ofdecision variables, value of objective function).Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutionsas instructed.
1f) We re-visit the problem from 1d) and 1e). However, we make some changes.
The edges HK and IK (which had cost of 45 in parts 1a-1d) are now both modified as
follows. For flow up to 75 (i.e., < 75), these edges still cost 45 per unit. But for flows above
75, the excess flow now costs 110 per unit. So, as an example, if HK has a flow of 90 then
that costs 45 * 75 + 110 * (90 – 75) = 45 * 75 + 110 * 15 = 3375 + 1650 = 5025 for the flown that edge.What is the shortest total cost, and what is the flow along the individual edges?Express all variables, the objective function, all constraints, working and solution (values ofdecision variables, value of objective function).
Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutions
s instructed. 1g) Following on from 1f, we include the additional constraint that at most 4 edges have
sitive non-zero flowWhat is the shortest total cost, and what is the flow along the individual edges?
Express all variables, the objective function, all constraints, working and solution (values ofecision variables, value of objective function).Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutions
as instructed.
1h) Combining 1a and 1b (in some sense), we now have 70 vehicles at A and 100 cars at B.
We are required to supply 70 vehicles to K and 100 vehicles to I.
What is the shortest total cost, and what is the flow along the individual edges?
Express all variables, the objective function, all constraints, working and solution (values of
decision variables, value of objective function).
Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutions instructed.r the earlier parts of the question, we have assumed that there was just one type of vehicle.
So, those (sub-)questions could be done whether everything was a car or everything was aruck.For the remainder of the question, we now assume that there is more than one type of vehicle(e.g., ambulance, fire truck, police car, other truck, other car). If someone has demand for an
ambulance then they want an ambulance and they might have no need for a fire truck.For simplicity, we might just assume that there are just two different types of vehicle – carand truck. For the avoidance of doubt, we want the correct number of cars to go from
source/supply to demand/destination/sink, and we also want the correct number of trucks togo from source to sink. Put in other words, if a demand node seeks 2 cars and 1 truck but
nstead gets 1 car and 2 trucks then that is not a correct answer.
1i) Combining 1a, 1b, 1c and 1d (in some sense), we now have two types of vehicles (cars
and trucks). The cars can be thought of as in 1h. More specifically, we have 70 cars at A and
100 cars at B. We are required to supply 70 cars to K and 100 cars to I. However, we alsohave 70 trucks at A and 100 trucks at B. We are required to supply 100 trucks to K and 70
trucks to I.What is the shortest total cost, and what is the flow (both cars and trucks) along the individualedges?Express all variables, the objective function, all constraints, working and solution (values of
ecision variables, value of objective function).
Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutions
as instructed. 1j) This is the same as 1i, but with the additional constraint that no edge can carry more than00 vehicles. So, whether it’s (e.g.) 100 cars or (e.g.) 100 trucks or (e.g.) 27 cars and 73
trucks, etc., no edge can carry more than 100 vehicles.What is the shortest total cost, and what is the flow (both cars and trucks) along the individual
edges?
Express all variables, the objective function, all constraints, working and solution (values ofdecision variables, value of objective function).
Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutionsas instructed.
--
Question 2 (6 + 6 + 6 + 6 + 6 + 4 = 34 marks)
Throughout Question 2 and the entire Assignment 2, follow the given notes (near the top –
e.g., Note 4, Note 5, Note 6, etc.) and other instructions.
There are 8 (agents or) workers to be potentially assigned to 12 possible (tasks or) jobs.
The workers put in their first 4 preferences (P1, P2, P3, P4).
For each worker that gets their 1st preference, there is a score of 10.
For each worker that gets their 2nd preference, there is a score of 6.
For each worker that gets their 3rd preference, there is a score of 3.For each worker that gets their 4th preference, there is a score of 1.No additional scores are given when a worker gets their 5th, 6th, 7th, … and/or 12th preference.The preferences (P1, P2, P3, P4) for each person (a, b, c, d, e, f, g, h) are given below:
2a) Each of the eight workers is to be assigned to at most 1 job each.
No job can be done by more than one worker.
What is the maximum score that can be obtained, and what is the assignment of workers to
jobs?
Express all variables, the objective function, all constraints, working and solution (values of
decision variables, value of objective function).
Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutions
as instructed. 2b) We modify 2a so that each of the eight workers can be assigned to at most 4 jobs each.No job can be done by more than one worker.What is the maximum score that can be obtained, and what is the assignment of workers to
jobs?Express all variables, the objective function, all constraints, working and solution (values ofdecision variables, value of objective function).
Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutionsas instructed.
2c) We modify 2b so that no more than 6 of the 8 workers can be used.
As before, each of these six workers can be assigned to at most 4 jobs eachNo job can be done by more than one worker.What is the maximum score that can be obtained, and what is the assignment of workers to
jobs?
Express all variables, the objective function, all constraints, working and solution (values ofdecision variables, value of objective function).
Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutionsas instructed.
2d) We modify 2a and 2b so that all 8 workers can be used but each of these eight workers
can be assigned to at most 2 jobs each.No job can be done by more than one worker.What is the maximum score that can be obtained, and what is the assignment ofworkers tojobs?
Express all variables, the objective function, all constraints, working and solution (values ofdecision variables, value of objective function).
Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutions
as instructed. 2e) We now modify 2d so that at most one (but not both) of worker a and worker b can do atask. If either worker a does at least one task or worker b does at least one task, then thther of these two workers can not do any tasks. Put another way, if worker a does at leasne task then worker b does no task, and if worker b does at least one task then worker a does
o task.Apart from this, all 8 workers can be used but each of these eight workers can be assigned to
at most 2 jobs each.No job can be done by more than one worker.
What is the maximum score that can be obtained, and what is the assignment of workers to
jobs?
Express all variables, the objective function, all constraints, working and solution (values of
decision variables, value of objective function).
Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutions
as instructed.
2f) We modify 2d so that at most 6 workers can be used, and each of these six workers can
be assigned to at most 2 jobs each.No job can be done by more than one worker.
What is the maximum score that can be obtained, and what is the assignment of workers tojobs?Express all variables, the objective function, all constraints, working and solution (values ofdecision variables, value of objective function).Further to Note 4 and Note 6, (here and elsewhere) please place your working and solutions
as instructed.
--
Question 3 (5 + 5 + 4 + 2 = 16 marks)
This question concerns variants of the Economic Order Quantity (EOQ) model.
Throughout Question 3 and the entire Assignment 2, follow the given notes (near the top –
e.g., Note 4, Note 5, Note 6, etc.) and other instructions.
Consider a monthly demand of 2000, a monthly holding cost of 0.02 = 2%, a cost per unit of
100 Malaysian ringgits (MYR) and an ordering cost of 500 MYR.
Assume no quantity discounts and no back-order penalty. 3a) Calculate the economic order quantity (EOQ) value, Q*.Show your calculations in your .pdf file.There might be more than one way to do it in your spreadsheet tab.Whatever you do, show working and solution values (any decision variable or decision
variables, any optimised objective function).
3b) We re-visit Qu 3a but with a back-order penalty of p = MYR 102.32 per annum.Calculate Q*, S* and the maximum amount by which we would be out of stock beforereplenishing with new stock.Also state the minimum cost.
Show your calculations in your .pdf fileAlso show your calculations in your spreadsheet tab.
Show working and solution values (any decision variable or decision variables, any optimisedobjective function).
3c) We re-visit Qu 3a with discount for bulk orders.For an order of 1200 or more, there is a bulk discount of 19.5%.Calculate the optimal Q* and the minimum cost.
Show your calculations in your .pdf file.Also show your calculations in your spreadsheet tab.
Show working and solution values (any decision variable or decision variables, any optimisedobjective function).
3d) Which is cheaper – the value from Qu 3b or the value from Qu 3c?Adjust the back-order penalty p from Qu 3b until the answers to 3b and 3c agree.
What is the new value of p?Did you have to increase or decrease p - and, if you had to change p, then by how much?Recall notes and other instructions at the start of the Assignment and throughout the
Assignment – and in the Ed Discussion `Welcome to FIT3158’ post - and follow these.
END OF FIT3158 ASSIGNMENT 2 (Monash University, 2nd semester 2024)
标签：function,working,Decision,Modelling,Note,FIT3158,objective,variables,your From： https://www.cnblogs.com/WX-codinghelp/p/18426595