写作FIT5097语言编程、辅导Analysis设计程序

” 写作FIT5097语言编程、辅导Analysis设计程序Monash UniversityFaculty of Information TechnologyFIT5097 Business Intelligence Modelling2nd Semester 2020Assignment: Linear Programming, Sensitivity Analysis,Transshipment/Network Modelling and Integer Linear Programming – andInventory Management – using Microsoft Excel SolverThis assignment is worth 30% of your final mark (subject to the hurdles described in theFIT5097 Moodle preview [or Unit Guide] and links therein). Among other things (seebelow), note the Need to hit the `Submit button (i.e., dont just leave your work inunsubmitted `draft mode) and also note the requirement of an interview.Due Date: Wednesday 14th October 2020, 11:45pm in time zone of Melbourne, AustraliaMethod of submission: Your submission should consist of 2 files:1. A Microsoft Excel spreadsheet named as:FamilyName-StudentId-2ndSem2020FIT5097.xlsx2. A text-based .pdf file named as: FamilyName-StudentId-2ndSem2020FIT5097.pdfBoth the files must be uploaded on the FIT5097 Moodle site by the due date and time. Thetext-based .pdf file will undergo a similarity check by Turnitin at the time you submit toMoodle. Please read submission instructions on the last page carefully re use of Moodle.Please read all instructions – including the notes below – carefully.Total available marks = 100 marks.(60 + 32 + 10 = 102 marks are available. Any mark over 100 will be rounded down to 100.)Note 1: Please recall the Academic Integrity exercises from week 1 and the start of semester. Insubmitting this assignment, you acknowledge both that you are familiar with the relevant policies, rulesand regulations regarding Academic Integrity and also that you are familiar with the consequences ofbeing deemed to be in contravention of these policies. Students are expected to do their own work andnot to share their Work. Among other things, students are reminded not to post even part of a proposedpartial solution to a Moodle forum, Ed Discussions or other location. Students are reminded of thepotentially serious consequences of being found guilty of an academic integrity violation. Put plainand simply, please take great care in this regard.Note 2: It is your responsibility to be familiar with the special consideration policies and specialconsideration process – and also with other policies (e.g., academic integrity, etc).Note 3: You will be required to be prepared to (present and) be interviewed about the work duringlab/tute/studio time – to be determined by your lecturer and tutor, currently scheduled for week 10, butpossibly to be scheduled for week 11. (Stay tuned for confirmation of the week of your compulsoryAssignment interview.) This is a compulsory part of your assessment only to be re-scheduled if youhave an approved application for special consideration. Students should be familiar with the specialconsideration policies and the process for applying. Students who do not attend the scheduledassignment interview without valid approved grounds for special consideration will possibly be given amark of 0 for the assignment – i.e., we reserve the right to give any such student a mark of 0 for theirassignment in such cases. As previously advised, students should be familiar with the specialconsideration policies and the process for applying.Note 4: As a general rule, dont just give a number or an answer like `Yes or `No without at leastsome clear and sufficient explanation – or, otherwise, you risk being awarded 0 marks for the relevantexercise. Evidence of working is expected to be shown. Make it easy for the person marking yourwork to follow your reasoning. Evidence of working includes – but is not limited to – showing clearlyrelevant spreadsheet tabs for every question and sub-question requiring calculations. Please1understand that a failure to require a spreadsheet tab when one is relevant for a question orsub-question could result in very few – or potentially even zero – marks for the relevant question orsub-question. Your .pdf should typically cross-reference the corresponding answer in yourspreadsheet. For each sub-question and exercise, provide a clearly labelled spreadsheet tab with clearcontent and appropriate use of Colours, accompanied with clearly cross-referenced clear .pdfexplanation. Put another way, make sure that everything in your assignment is there, and make it easyfor the marker to find it. Again, without clear cross-reference between .pdf and spreadsheet tab, there isthe possibility that Any such exercise will be awarded 0 marks.Note 5: As a general rule, if there is an elegant way of answering a question – e.g., withoutunnecessarily re-running the Solver – then try to do it that way. (Recall, e.g., sensitivity report andsome notions from Week 4.) More generally, more elegant solutions are preferable – and will at leastsometimes be given more marks or perhaps many more marks. Among other things, if a problem is alinear programming (LP) problem, then it would be more elegant to solve it using a linear simplexmodel (than, e.g., a non-linear model) where possible. In similar vein, a linking constraint (whereappropriate) 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 (in spreadsheet form or typeddocument), 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 andsubstantial) late penalties, Then make sure to upload the correct files and (not to leave your files as`Draft but) also to hit Submit to make sure that your work is submitted.Note 8: The notation 1E-12 corresponds to 1 x 10-12, or 0.000000000001. If you see a figure ofapproximately this magnitude or comparable magnitude, then consider whether or not it might be asmall rounding error for something else. The notation 1E+30 corresponds to 1 x 1030, or1,000,000,000,000,000,000,000,000,000,000, but is often used in MicroSoft Excel to denote infinity.Note 9: For all solutions involving integer constraints, first see whether you can get the optimalinteger solution – and, that failing, see whether you can get an integer solution within a relatively smallpercentage (e.g., 1% or less, if possible) of the optimal relaxed solution (where the relaxed solutiondoes not have the integer constraints). (The reason for the last sentence is an acknowledgment thatobtaining the optimal integer solution typically requires much more run-time than obtaining the optimalrelaxed solution.) At the very least, make it clear to the person marking your work exactly what youredoing, and why.Question 1 Linear Programming and variants [6 + 6 + 4 + 3 + 3 + 3 + 3 + 4 + 2 + 2 +3 + 3 + 3 + 3 + 3 + 3 + 3 + 3 = 60 marks]We use resources to make products. Consider 6 such resources and 5 such products. The variousresources that we use might include (e.g.) linen, elastic, plastic, foam, etc. The various products that wemake might include (e.g.) soap, sanitiser, washable masks, disposable masks, filters, shields, otherpersonal protection equipment (PPE), etc. (Alternatively, the products might possibly be variousgraphical processing units – or GPUs – and the various resources might possiblybe Solder, Copper wire,Plastic, Aluminium, Bearings, Die size.)We show below the profit of each product, the number of each resource required to make each product,and the total availability of each resource.Unless we are explicitly Told that a variable is integer-valued (or otherwise discrete-valued or binary,etc), it will probably be safer not to make such an assumption and rather instead allow the variable tobe continuous-valued. (Note that sub-questions such as part 1m will occur later.) If unsure, clearlystate and justify any assumptions. Please state such continuous values to at least three decimal places.2Product 1 Product 2 Product 3 Product 4 Product 5Profit of Product $510 $300 $510 $270 $810Resource AvailabilityResource 1 2 10 2 3 6 2487Resource 2 6 3 6 3 10 3030Resource 3 2 3 10 6 2 5217Resource 4 7 6 5 4 3 4000Resource 5 5 6 3 10 2 4999Resource 6 10 3 5 3 4 2769We wish to produce products – given constraints – so as to optimise our objective function.Bearing in mind the introductory material above, the questions follow below:1a) Formulate a Linear Programming (an LP) model for this problem. Save yourformulation in the text-based .pdf file[FamilyName-YourStudentId-2ndSem2020FIT5097.pdf]. (6 marks)1b) Create a MicroSoft Excel spreadsheet model for this problem. Store the modelin your Excel workbook[FamilyName-YourStudentId-2ndSem2020FIT5097.xlsx] and name your firstExcel worksheet (spreadsheet tab) for this question something like (e.g.)LotsOfProducts 1b (6 marks)1c) Solve the problem – using Microsoft Excel Solver. Generate the Sensitivityreport for the problem and name your Excel worksheet (spreadsheet tab) (e.g.) Qu1b Sensitivity Rep. (4 marks)Using the Microsoft Excel Solver sensitivity report (as appropriate), provide answers (in the.pdf file) to the following questions: (You must include explanations with youranswers.)1d) What is the optimal production plan (X1, X2, X3, X4, X5) and the associated profit?Refer to your answers to any of a), b) and/or c) above as appropriate. (3 marks)For the remaining parts of this question, explain your answer(s), typically referring to relevantspreadsheet entry/ies and/or specific relevant parts of spreadsheet reports.Throughout, recall note 4 above: “Note 4: As a general rule, dont 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 0marks for the relevant exercise. Evidence of working is expected to be shown. Make it easy for the personmarking your work to Follow your reasoning. Evidence of working includes – but is not limited to – showingclearly relevant spreadsheet tabs for every question and sub-question requiring calculations. Please understandthat a failure to require a spreadsheet tab when one is relevant for a question or sub-question could result in veryfew – or potentially even zero – marks for the relevant question or sub-question. Your .pdf should typicallycross-reference the corresponding answer in your spreadsheet. For each sub-question and exercise, provide aclearly labelled spreadsheet tab with clear content and appropriate use of colours, accompanied with clearlycross-referenced clear .pdf explanation. Put another way, make sure that everything in your assignment is there,and make it easy for the marker to find it. Again, without clear cross-reference between .pdf and spreadsheet tab,there is the possibility that any such exercise will be awarded 0 marks.31e) Which constraints, if any, are binding? Refer to your answers to any of theabove parts as appropriate, and explain your reasoning. (3 marks)1f) The people running the company are now offered the opportunity of an exchangeof goods.The offer is for the company to receive 1 of Resource 2, 10 of Resource 4 and 100 ofResource 5 but for the company to have to relinquish (or surrender, or give away. or pay forthese resources with) 10 of Resource 1, 5 of Resource 3 and 3 of Resource 6.Should the company accept this offer?Clearly explain with clear calculations (to at least 3 decimal places) how much money thecompany would gain or lose by agreeing to such an exchange, making it clear whether thiswould result in a gain or a loss.Let us return to the original problem above (prior to the company being made an offer) frompart d.A proposal is put forward to produce a new product called Product 6.Product 6 would have a profit of $155 and would require the following resources: 2 ofResource 2, 4 of Resource 4 and 5 of Resource 5.1g) Would we expect Product 6 to be produced – i.e., if we are to produce products tooptimise our objective function, would we produce any copies of this new product?If we would expect Product 6 to be produced, then how much less profitable could Product 6be and still be produced?If we would not expect Product 6 to be produced, then how much more profitable wouldProduct 6 need to be in order to be produced?Let us again return to the original problem above from part d, where the profitability of thevarious products was (510, 300, 510, 270, 810).Various employees at the company have considered making changes which would affect theprofitability of various products.One change would result in (512, 301, 511, 269, 811).1h) Explaining your reasoning, when compared to your original answer using(510, 300, 510, 270, 810), would your optimal amount to be produced of each of Product1, …,Product5 change? Explain clearly why or why not. And, if the amounts produced wouldchange, explain clearly with any necessary or relevant calculations what they would changeto.1i) A second change, if it really could be carried out in practice, would doublethe values to become (1020, 600, 1020, 540, 1620).Explaining your reasoning, when compared to your original answer using (510, 300, 510, 270,810), would your optimal amount to be produced of each of Product1, …, Product5change? Explain clearly why or why not. And, if the amounts produced would change,explain clearly with any necessary or relevant calculations what they would change to.41j) A third change, which would probably not be a good idea, would halve thevalues to become (255, 150, 255, 135, 405).Nonetheless, if such a change were to take place then, explaining your reasoning, whencompared to your original answer using (510, 300, 510, 270, 810), would your optimalamount to be produced of each of Product1, …, Product5 change? Explain clearly why orwhy not. And, if the amounts produced would change, explain clearly with any necessary orrelevant calculations what they would change to.1k) Returning to the original problem and solution from part d, suppose we nowintroduce the requirement that Product1, Product2 and Product 5 must be produced in equalamounts.Compared to the original feasible region (from part d), does adding this new requirementmake the feasible region larger, stay the same, smaller, or something else? Clearly explainyour answer.1l part 1) Continuing from part k with this newly introduced requirement thatProduct1, Product2 and Product 5 must be produced in equal amounts, what is the optimalamount to be produced of each of Product1, Product2, Product3, Product4, Product5?11 part 2) What is the resultant profit (stated to at least 3 decimal places)?For both part 1 and part 2 of 1l, (in keeping with note 4,) clearly show all working.Returning to the original problem from part d, suppose we now introduce the additionalrequirement that Product1, Product2, Product 3, Product 4 and Product 5 must be produced ininteger amounts.1m part 1) What is the optimal amount to be produced of each of Product1, Product2,Product3, Product4, Product5?1m part 2) What is the optimal value of the objective function?For both part 1 and part 2, (in keeping with note 4,) clearly show all working.1n) Continuing on from part m above, assume the same unit profits as before butnow with fixed start-up costs as given below.Product Product 1 Product 2 Product 3 Product 4 Product 5Unit Profit $510 $300 $510 $270 $810Fixed-cost (Start-up cost) 2000 4000 8000 16000 100051n part 1) What is the optimal amount to be produced of each of Product1, Product2,Product3, Product4, Product5?1n part 2) What is the optimal value of the objective function?For both part 1 and part 2, (in keeping with note 4,) clearly show all working.Return to part n above.Suppose we now impose the additional constraint that, if Product3 is produced,then there must be a minimum of 225 and a maximum of 325 of Product3 produced.1o part 1) What is the optimal amount to be produced of each of Product1,Product2, Product3, Product4, Product5?1o part 2) What is the optimal value of the objective function?For both part 1 and part 2, (in keeping with note 4,) clearly show all working.Return to part n above.Suppose we now change the additional constraint from part 1o (immediately above) to bethat, if Product3 is produced, thenthere must be a minimum of 300 and a maximum of 450 Product 3 produced,and (also) the amount produced of Product3 must also be a multiple of 50.1p part 1) What is the optimal amount to be produced of each of Product1,Product2, Product3, Product4, Product5?1p part 2) What is the optimal value of the objective function?For both part 1 and part 2, (in keeping with note 4,) clearly show all working.Return to part n above.Now suppose that we introduce the requirement that, if Product 2 is produced, then theamount of Product 2 produced must be one of 102, 103, 105, 107, 111 and we also introducethe further additional requirement that, if Product 4 is produced, then the amount produced ofProduct4 must be one of 320, 330, 350, 370, 410.1q part 1) What is the optimal amount to be produced of each of Product1,Product2, Product3, Product4, Product5?1q part 2) What is the optimal value of the objective function?For both part 1 and part 2, (in keeping with note 4,) clearly show all working.6Remove these most recent additional constraints and again return to part n above.Suppose we now add the requirements thatProduct1 and Product2 are produced in equal abundance,Product4 and Product5 are produced in equal abundance,if Product 3 is produced then neither product 1 nor product 2 is produced,if Product 3 is produced then at least 10 and at most 100 of Product 5 are produced.1r part 1) What is the optimal amount to be produced of each of Product1, Product2,Product3, Product4, Product5?1r part 2) What is the optimal value of the objective function?For both part 1 and part 2, (in keeping with note 4,) clearly show all working.Throughout, recall note 4 above: “Note 4: As a general rule, dont 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 0marks for the relevant exercise. Evidence of working is expected to be shown. Make it easy for the personmarking your work to follow your reasoning. Evidence of working includes – but is not limited to – showingclearly relevant spreadsheet tabs for every question and sub-question requiring calculations. Please understandthat a failure to require a spreadsheet tab when one is relevant for a question or sub-question could result in veryfew – or potentially even zero – marks for the relevant question or sub-question. Your .pdf should typicallycross-reference the corresponding answer in your spreadsheet. For each sub-question and exercise, provide aclearly labelled spreadsheet tab with clear content and appropriate use of colours, accompanied with clearlycross-referenced clear .pdf explanation. Put another way, make sure that everything in your assignment is there,and make it easy for the marker to find it. Again, without clear cross-reference between .pdf and spreadsheet tab,there is the possibility that any such exercise will be awarded 0 marks.7Question 2 Transshipment and networks [2 + 3 + 6 + 4 + 3 + 3 + 3 + 3 + 3 + 2 =32 marks]Suppose we have a product (possibly masks, possibly shields, possibly containers of handsanitiser) that we wish to move from two locations (lets call them node 1 and node 2, bothwith a supply of 75) to two other locations (lets call them node 7 and node 8, with demandsof 80 and 70 respectively).We initially assume the transportation costs along edges in the network to be as follows:From To Unit Cost ($)Students are expected and required to address question 2 in terms of linear programming (LP)and – if required – the closest possible variants.2a) State the variables, and use these variables to state the objective function that we wishto optimise. (We assume that the cost is something that we wish to minimise.)2b) How many variables are there? Informally in terms of the network, being as specificas you can, what do the variables correspond to?2c) Solve the problem of the flow along edges giving the minimum cost.Show the amounts of flow along the edges. State the value of the objective function.State the number of edges with non-zero flow (and, for ease of reference, call this e2c).2d) Assuming that the number of edges with non-zero flow is less than e2c(equivalently,less than or equal to e2c- 1), again solve the problem of the flow along edges giving theminimum cost.Show the amounts of flow along the edges. State the value of the objective function.State the number of edges with non-zero flow.82e) If the problem is to have a solution of finite cost (any possible solution at all) inwhich goods get from the source/supply/starting points to the demand/sink destination points,what is the smallest number of edges that can have non-zero flow for such a solution tooccur?Hint: One way of doing this is to introduce a very large penalty for each edge with non-zeroflow.In that case (if we require that only this smallest possible number of edges be used), what isthe minimum such cost? (If you followed the hint immediately above, then make sure toremove the newly introduced large penalty when giving your answer.)2f) Return to the problem from parts a, b and c above.Due to maintenance problems along the edge between node 4 and node 5, the unit cost ofusing this edge is $40/unit up to 30 units, then $60/unit thereafter.Show how to solve this problem. In keeping with note 4, solve this problem.2g) Following on from part f above, due to further maintenance problems along the edgebetween node 4 and node 5, the unit cost of using this edge is $40/unit up to 30 units, then$60/unit up to 55 units (i.e., we could have 30 units @ $40/unit and 25 units @ $60/unit, as30 + 25 = 55), then $110/unit thereafter.Show how to solve this problem. In keeping with note 4, solve this problem.2h) We modify the original problem from parts a, b and c to be a shortest path problem.The edge costs (from parts a, b and c) should now be assumed to be the length of the edge.What is the shortest path from node 2 to node 8, and what is the length of the path?Show how to solve this problem. In keeping with note 4, solve this problem.2i part 1) Following on from part h, how would you modify your answer if we require thatthe path from node 2 to node 8 has to go through node 5?2i part 2) Following on from part h, how would you modify your answer if we require thatthe path from node 2 to node 8 has to go through node 6?Show how to solve this problem. In keeping with note 4, solve both parts of this problem.Following on from the themes of part h and part i above, we now ask an open question worthbonus marks. (The motivation might be that someone has to collect face masks and shieldson their way to a destination, but the order in which they collect them doesnt matter.)2j) Suppose we have a start node (call it A), and a destination node (call it D) and twointermediate nodes (call them B and C respectively) that we have to go through. Suppose alsothat we are allowed to go A to B to C to D and we are also allowed to go A to C to B to D,and that this is not known or specified in advance. How might we set this up as a linearprogramming (LP) problem?We do not require a complete solution for 2j immediately above but wish you to explain indetail how you would set this up.9Throughout, recall note 4 above: “Note 4: As a general rule, dont 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 0marks for the relevant exercise. Evidence of working is expected to be shown. Make it easy for the personmarking your work to follow your reasoning. Evidence of working includes – but is not limited to – showingclearly relevant spreadsheet tabs for every question and sub-question requiring calculations. Please understandthat a failure to require a spreadsheet tab when one is relevant for a question or sub-question could result in veryfew – or potentially even zero – marks for the relevant question or sub-question. Your .pdf should typicallycross-reference the corresponding answer in your spreadsheet. For each sub-question and exercise, provide aclearly labelled spreadsheet tab with clear content and appropriate use of colours, accompanied with clearlycross-referenced clear .pdf explanation. Put another way, make sure that everything in your assignment is there,and make it easy for the marker to find it. Again, without clear cross-reference between .pdf and spreadsheet tab,there is the possibility that any such exercise will be awarded 0 marks.Question 3 Economic Order Quantity (EOQ) [10 marks]Suppose that we have an ordering problem with variable costs.We have a deterministic annual demand of 1000. The cost of placing an order (of anypositive non-zero amount) is $21 for an order. The holding cost of storing items is 25% (or1/4) per annum of the cost of the goods. (Equivalently, if we wish to change from a yearsannual demand to the demand over 10 years in a decade, the deterministic demand in a decadewould be 10,000 and the holding cost would be 250% of the cost of the goods. It will be safeto address the problem in terms of years rather than decades.) As many goods as required canbe held in inventory indefinitely and not be thrown away.The cost of each good is $4.00 up to 794 units ordered. If we order from 795 up to 1099, weget a 5% discount and the cost of each good is $3.80. If we order from 1100 up to 1859, weget an 8% discount and the cost of each good is $3.68. If we order 1860 or more, we get a15% discount and the cost of each good is $3.40.What is the optimal order quantity and the optimal total annual cost?In keeping with note 4, clearly show all working.A note about your Spreadsheet ModelWhen building your model, bear in mind the goals and guidelines for good spreadsheet designas discussed in Lecture 3. Marks are given for good spreadsheet design. Marks will possiblyalso be given for originality. Format both your models clearly with comments (and, ifpossible, shading), etc. so that it is easy for the user to distinguish which cells are occupied bydecision variables, LHS and RHS constraints, and the objective function. Include a textbox ineach worksheet that describes the formulation in terms of cell references in your model.Instructions:You are to upload your submission on the FIT5097 Moodle site and should include thefollowing:1. A text-based .pdf document (save as:FamilyName-StudentId-2ndSem2020FIT5097.pdf) that includes all your answers toQuestions 1 and 2 and 3 (except for the Microsoft Excel Solver part of eachquestion); and102. A Microsoft Excel workbook (save as:FamilyName-StudentId-2ndSem2020FIT5097.xlsx) that includes the followingspreadsheets:i. the spreadsheet model for Question 1;ii. Sensitivity Rep the sensitivity report for the Question 1 model (and any otherrelevant parts);iii. other relevant things (including any calculations) for Question 1;iv. relevant things (including any calculations) for Question 2v. relevant things (including any calculations) for Question 3vi. etc.vii. Anything else you deem sufficiently relevant.Recall that, at the time you submit (1 and 2) to Moodle, the text-based .pdf will undergo asimilarity check by Turnitin. This is done at the time you upload your assignment to Moodle.It is also our intention to perform such a check on your .xls/.xlsx file at the same time.(This ends the submission instructions. Please read them and the notes on pages 1-2carefully. Also recall that, as a general rule, when answering questions, dont just give anumber or an answer like `Yes or `No without at least some clear and sufficientexplanation.)Late penalties:Work submitted after the deadline (possibly with a small amount of grace time) will besubject to late penalties in accordance with the FIT5097 Unit Guide and Faculty andUniversity policies, and (unless any of the following contravenes the relevant policies)certainly no less than 5% per calendar day, possibly as much as 10% per calendar day.If you do not submit matching .pdf and .xls/.xlsx files (e.g., if you submit two files but one isblank or unreadable, or if you only submit one file), then your work will be deemed late – andwill be subject to the relevant penalties, possibly receiving a mark of 0.Work submitted 10 or more calendar days after the deadline will be given a mark of 0.Plagiarism declaration:You are required to state explicitly that you have done your own work, however the Moodleassignment submission details permit you to declare this.For example, if you are presented with an Assignment Electronic Plagiarism Statement, thenyou are required to complete the Assignment Electronic Plagiarism Statement quiz on theFIT5097 Moodle site and accept t”