IT3158程序代写、代做Python，Java 编程

日期：2023-08-19 02:49

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)