IT3158 Business Decision Modelling
Assignment 1: Linear Programming, Sensitivity Analysis, and Integer Linear Programming -
using Microsoft Excel Solver
This assignment is worth 30% of your final mark (subject to the hurdles described in the
FIT3158 Unit Guide and links therein). Among other things (see below), note the need to hit
the `Submit’ button (and the possible requirement of a video and/or an interview).
Due Date: Wednesday 30th August 2023, 11:55 pm
Method of submission: Your submission should consist of 2 files:
1. A Microsoft Excel spreadsheet named as:
FamilyName-StudentId-2ndSem2023FIT3158.xlsx
2. A text-based .pdf file named as: FamilyName-StudentId-2ndSem2023FIT3158.pdf
Both 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 submit
to Moodle. Please read submission instructions here and elsewhere carefully regarding
the use of Moodle.
Total available marks: 90 marks.
Note 1: Please recall the Academic Integrity rules (and, as per Moodle week 0, you might
also wish to confer with https://www.monash.edu/student-academic-success). This is an
individual assignment. Recall various resources from FIT3158 Moodle week 0.
In submitting this assignment, you acknowledge both that you are familiar with the relevant
policies, rules and regulations regarding Academic Integrity (including, e.g., doing your own
work, 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 contravention
of 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/or (if necessary) send private e-mail (being in mind
instructions in week 1 Introductory Lecture). If you are seeking to understand a concept
better, then try to word your question so that it is a long way removed from the Assignment.
You are reminded 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 process – 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
at 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 to
follow 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.
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). 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: 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 your
work. If you hit `Submit’ without all files uploaded then you will probably be deemed not to
have followed the instructions from the Notes above. If you leave your work as Draft and
have 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 that
your work is submitted. Late penalties will be as per Monash University Faculty of IT and
Monash University policies. Within the requirements of these policies just mentioned, late
penalties will accrue at a rate of at least 5% per calendar day – where 5% refers to a
proportion of the total marks available. It is expected that any work submitted at least 10
calendar days after the deadline will automatically be given a mark of 0.
Note 8: The notation 1E-12 corresponds to 1 x 10-12, or 0.000000000001. If you see a figure
of approximately this magnitude or comparable magnitude, then consider whether
or not it might be round-off error for something else.
Note 9: Save your file regularly. Most of the time, we expect that the Solver will run quickly.
But for problems with many variables and many constraints – especially involving integers
– 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 be long and
slow, then you can safely stop the program – if it becomes very slow – with reduced risk 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 might
be able to access after clicking on `Options’, which might be on the right about two-
thirds of the way down after you click on `Solver’) might affect the results provided
by the solver. (As an example, if dealing with integers then give some thought to `Integer
Optimality’.) Put another way, rather than just use the default settings, make sure to check
the solver settings and be willing to appropriately modify them if and as required.
We have a fictitious story which might possibly or might not involve none, one or more
of (e.g.) Ash Barty, Scott Boland and Jonas Vingegaard (or people who have the same name
– or names – as none, some or all of these people). Our fictitious story might possibly or
might not involve none, some or all of (e.g.) Bennelong Point, Geelong, Warrnambool, none,
some or various places on Monash University's Clayton campus (also possibly including
none or more other campuses in Australia, China, Indonesia and/or elsewhere), the National
Indigenous Space Academy (NISA), etc. None, one or more of these people – or other
people with the same or similar names – might or might not read (e.g.) “A pink rover tackles
the red planet — and barriers for women in science”
https://www.nature.com/articles/d41586-023-02344-8 from Nature journal (July 2023),
"Sharing your route in advance could cut electric car charging queue"
https://www.NewScientist.com/article/2268485-sharing-your-route-in-advance-could-cut-
electric-car-charging-queue and https://www.brown.edu/news/2022-02-07/skyrmions
“Researchers use tiny magnetic swirls to generate true random numbers",
https://www.monash.edu/student-academic-success/home and/or articles from Australia’s
https://www.ABC.net.au ABC, https://www.ABC.net.au/radionational/programs/scienceshow
and https://www.SBS.com.au SBS. None, one or more of these people – or other people with
the same or similar names – might or might not study works of (e.g.) Elizabeth Blackburn
(1948- ), George Dantzig (1914-2005), Emmy Noether (1882-1935), Elizabeth Scott (1917-
1988), Alan Turing (1912-1954), J?rn Utzon (1918-2008) and/or Chris Wallace (1933-2004),
etc. (and possibly one of the works of one of these people in the vicinity of Bennelong Point).
None, one or more of these people – or other people with the same or similar names – might
or might not listen to, or play, music from and by (e.g.) Lili Boulanger (1893-1918) and a
wide range of artists (possibly but not necessarily in the vicinity of Bennelong Point).
The optimisation to follow could include such considerations as (including but not limited to)
(e.g.) limiting pollution, saving time, reducing waste, making the most of limited resources,
improving organ donor and recipient compatibility, etc. It could involve work with the
United Nations (UN) and/or the International Space Station (ISS).
The optimisation problem is of the following form.
(For no particular reason, we assume the currency to be Australian$.)
Products P1, P2, P3, P4 and P5 have profits $350, $295, $330, $325 and $300 respectively.
Resources R1, R2, R3, R4 and R5 are available in quantities 950, 6015, 14390, 9925 and
13550 respectively.
The products use the following quantities of the resources.
P1 P2 P3 P4 P5
R1 1 2 1 1 1
R2 9 10 8 6 4
R3 12 18 11 16 17
R4 8 13 10 11 12
R5 13 19 16 15 14
1a) How many decision variables are there?
Give names to any decision variables - or at least to the quantities of them. (1 mark)
1b) Is this a minimisation problem, a maximisation problem or something else - and why?
(1 mark)
Assume that all variables are continuous. Do not assume at this stage that any variable is
required to be an integer.
1c) Write this out as an optimisation problem. Express all variables, the objective function
and all constraints. Also explain whether or not the problem is a linear programming (LP)
problem or not - and why or why not. (If the problem can be presented as an LP problem
then it should be done that way.) Place this in your machine-readable .pdf file. (9 marks)
1d) Translate this into - or implement this in - MicroSoft Excel so that you can use the
Solver. Make sure to implement the problem specification correctly.
Make sure also to use good spreadsheet lay-out and design - as taught and discussed in class.
(9 marks)
1e) Show your solution to this problem. More specifically, show values of the decision
variables - and also give the value of the objective function. (4 marks)
1f) Continuing on from 1d) and 1e), suppose we have the additional requirement that the
production of P5 is 0. Recalling note 4, with new section in your .pdf file and in a separate
spreadsheet tab, solve this problem, showing all working, decision variables and objective
function. (3 marks)
1g) Continuing on from 1d), 1e) and 1f), suppose we have the additional requirement that the
production of P4 and of P5 is 0 in both cases. Recalling note 4, with new section in your .pdf
file and in a separate spreadsheet tab, solve this problem - showing all working, decision
variables and objective function. (2 marks)
1h) Continuing on from 1d) and 1e), suppose we have the additional requirement that the
production of P1 and of P5 are equal. Recalling note 4, with new section in your .pdf file and
in a separate spreadsheet tab, solve this problem and show all working, etc. (3 marks)
1i) Continuing on from 1d) and 1e), it is proposed to make a new product, P6.
P6 will use 1, 8, 8, 14 and 14 of R1, R2, R3, R4 and R5 respectively; and P6 will have a
profit of $290. In an optimal solution, how much of P6 should be produced?
Recalling note 5 and all notes (and ignoring later questions such as 1k)), provide the simplest
argument that you can to support this answer. Show all working. (7 marks)
1j) Continuing on from 1d) and 1e), a trading partner offers the following deal. The trading
partner is willing to give us 9 of R2, 4 of R3 and 3 of R4. But, in return, the trading partner
wants 1 of R1 and 2 of R5. Is this a deal which we should accept (and, if so, how much
money would we expect to gain), decline (and, if so, how much money would we expect to
lose) or would it make no difference (and, if so, might or would any of the amounts of
production change)? Recalling all notes, show all working, explain your answer. (7 marks)
1k) Continuing on from 1d), 1e) and 1i), if we should make product P6 then how much profit
would we gain for each P6 that we make - and, if we should not make P6, then how much
money would we expect to lose from each P6 that we make, or is this amount $0? Recall
note 4 and note 5 and all notes, and explain your answer. (7 marks)
1l) Some changes are proposed to the profitability of all products. Product 2 will become
$150 more profitable and Product 4 will become $2 less profitable. Would this affect the
values of the decision variables (i.e., the number to make of the products)?
Would this affect the value of the objective function - and, if so, by how much?
In both cases, show all working and - recalling note 5 and all notes - answer as elegantly as
possible. (7 marks)
1m) Return now to the original problem, going back to 1b).
Assume now that all products must be produced in integer quantity.
Set up the problem and solve the problem – for decision variables and objective function.
(4 marks)
1n) Continuing on from part 1m), assume now that there are set-up (or start-up, or fixed)
costs associated with the various products as follows: (10000, 500, 1000, 8000, 10000).
Set up the problem and solve the problem – for decision variables and objective function.
(8 marks)
1o) Continuing on from part 1n), assume now that at most 2 of the products can be produced.
Set up the problem and solve the problem – for decision variables and objective function.
(7 marks)
1p) Continuing on from part 1n), assume now that at most 1 of the products can be produced.
Set up the problem and solve the problem – for decision variables and objective function.
(7 marks)
1q) Continuing on from part 1n), assume now that at least 3 of the product set-up costs must
be paid. Set up the problem and solve the problem – for decision variables and objective
function. (4 marks)