Monash University Faculty of Information Technology FIT5097 Business Intelligence Modelling 2nd Semester 2019 Assignment: Linear Programming, Sensitivity Analysis, Network Modelling and Integer Linear Programming – and Inventory Management – using Microsoft Excel Solver This assignment is worth 30% of your final mark (subject to the hurdles described in the FIT5097 Unit Guide and links therein). Among other things (see below), note the need to hit the `Submit’ button and the requirement of an interview. Due Date: Thursday 26th September 2019, 11:55pm Method of submission: Your submission should consist of 2 files: 1. A Microsoft Excel spreadsheet named as: FamilyName-StudentId-2ndSem2019FIT5097.xlsx 2. A text-based .pdf file named as: FamilyName-StudentId-2ndSem2019FIT5097.pdf Both the files must be uploaded on the FIT5097 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 on last page carefully re use of Moodle. Total available marks: 58 + 25 + 17 = 100 marks. Note 1: Please recall the Academic Integrity exercises from week 1 and the start of semester. In submitting this assignment, you acknowledge both that you are familiar with the relevant policies, rules and regulations regarding Academic Integrity and also that you are familiar with the consequences of being deemed to be in contravention of these policies. Note 2: As previously advised, it is your responsibility to be familiar with the special consideration policies and special consideration process. Note 3: You will be required to be prepared to (present and) be interviewed about the work during lab/tute/studio time – to be determined by your lecturer and tutor, currently scheduled for week 10, but possibly to be scheduled for Week 11. (Stay tuned for confirmation of the week of your compulsory Assignment interview.) This is a compulsory part of your assessment – only to be re-scheduled if you have an approved application for special consideration. Students should be familiar with the special consideration policies and the process for applying. Students who do not attend the scheduled assignment interview without valid approved grounds for special consideration will possibly be given a mark of 0 for the assignment – i.e., we reserve the right to give any such student a mark of 0 for their assignment. As previously advised, 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 marking your work to follow your reasoning. Your .pdf should typically cross-reference the corresponding answer in your spreadsheet. For each sub-question and exercise, provide a clearly labelled spreadsheet tab with clear content, accompanied with clearly cross-referenced clear .pdf explanation. Without clear cross-reference between .pdf and spreadsheet tab, there is the 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 without unnecessarily re-running the Solver, try to do it that way. (Recall, e.g., sensitivity report and some notions from Week 4.) More generally, more elegant solutions are preferable – and will at least sometimes be given 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 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 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 of approximately this magnitude or comparable magnitude, then consider whether or not it might be round-off error for something else. Question 1 – Many looms, many components [6 + 6 + 2 + 2 + 2 + 2 + 2 + 3 + 3 + 3 + 1 + 3 + 3 + 3 + 3 + 3 + 3 + 2 + 3 + 3 = 58 marks] Consider 6 types of loom: L1 (loom 1), L2 (loom 2), L3 (loom 3), L4 (loom 4), L5 (loom 5) and L6 (loom 6), with the production of products L1, …, L6 in quantities X1, …, X6 made up of 8 components (or things) : Component1 (beams, Resource1 or thing1), Component2 (labour, Resource2 or thing2), Component3 (heddles, Resource3 or thing3), Component4 (or harnesses, Resource4 or thing4), Component5 (or shafts, Resource5 or thing5), Component6 (or Resource6 or thing6), Component7 (or Resource7 or thing7), and Component8 (or Resource8 or thing8). (We will possibly sometimes use the terms Li and Xi interchangeably.) The profit for each of X1, …, X6 is c1, …, c6 shown below. Unless stated otherwise, you should not assume that the Xi are integers. We also show below the amounts of Component1, Component2, …, Component8 required to make each of L1, …, L6; and, for each of Component1, Component2, …, Component8, we show how much of that component (or resource) is available. L1 L2 L3 L4 L5 L6 Available Component1 1 6 8 10 12 2 1577 Component2 6 1 5 4 2 3 1990 Component3 3 4 1 2 5 6 1321 Component4 6 5 2 1 3 4 1920 Component5 3 2 6 4 1 5 1149 Component6 2 5 6 3 4 1 1000 Component7 3 2 5 6 1 4 1231 Component8 5 6 2 1 3 4 2000 Profit 300 320 340 360 380 400 We wish to produce the number to maximise total profit. The questions follow below: a) Formulate a Linear Programming (an LP) formulation for this problem. Save your formulation in the text-based .pdf file [FamilyName-YourStudentId-2ndSem2019FIT5097.pdf]. (6 marks) b) Create a spreadsheet model for this problem. Store the model in your Excel workbook [FamilyName-YourStudentId-2ndSem2019FIT5097.xlsx] and name your spreadsheet something like (e.g.) ‘LotsOfLooms’ (6 marks) c) Solve the problem – using Microsoft Excel Solver. Generate the Sensitivity report for the problem and name your spreadsheet (e.g.) ‘Qu 1 LotsOfLooms Sensitivity Rep’. (2 marks) Using the Microsoft Excel Solver sensitivity report, provide answers (in the .pdf file) to the following questions: (You must include explanations with your answers.) d) What is the optimal production plan (X1, X2, X3, X4, X5, X6) and the associated profit? Refer to your answers to any of a), b) and/or c) above as appropriate. (2 marks) For the remaining parts of this question, explain your answer(s), typically referring to relevant spreadsheet entry/ies and/or specific relevant parts of spreadsheet reports. Throughout, recall Note 4 above: “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 marking your work to follow your reasoning. Your .pdf should typically cross-reference the corresponding answer in your spreadsheet. For each sub-question and exercise, provide a clearly labelled spreadsheet tab with clear content, accompanied with clearly cross-referenced clear .pdf explanation. Without clear cross-reference between .pdf and spreadsheet tab, there is the possibility that any such exercise will be awarded 0 marks.’’ e) Continuing from the above, and now setting X3 = X4 = X5 = X6 = 0, what is the optimal production plan and the associated profit?
(2 marks) f) Returning to part d, removing the requirement that X3 = 0 and requiring that X4 = X5 = X6 = 0, what is the optimal production plan and the associated profit? (2 marks) g) Return to part d and remove the requirements that X3 = X4 = X5 = X6 = 0, and now add the requirement that X3 = X4 = X5 = X6. What is the optimal production plan and the associated profit? (2 marks) h) In part g, which constraints – if any – are binding? Explain clearly. (3 marks) i) For the problem in part d, what is the effect on the optimal solution (i.e., on the objective function) if we increase the amount of Resource1 available by 7? Explain clearly. (3 marks) j) For the problem in part d, what is the effect on the optimal solution if we increase the amount of Resource8 available by 4%? Explain clearly. (3 marks) k) Returning to the problem in part d, choose exactly one of the following to answer: k.1 is the solution to part d degenerate (in the sense of the shadow prices not being unique)? Clearly explain why or why not OR k.2 is the solution to part d unique (i.e., is the choice of (X1, X2, X3, X4, X5, X6) unique)? Clearly explain why or why not. Also, make it explicitly clear which of these two you are doing: k.1 or k.2 . (1 mark) l) Returning to the problem in part d, suppose we change the profitability of each loom as follows: we increase the profitability of all of them by 2%. What effect – if any – will this have on (X1, X2, X3, X4, X5, X6)? Clearly explain why. (3 marks) m) Returning to the problem in part d, suppose that 9 extra of Resource4 are available at a total additional cost of $10 each. Are these worth buying? Explain why or why not – and, if they are worth buying, how much extra profit would be made by doing so. (3 marks) n) Returning to the problem in part d, suppose that L7 (or loom 7) is proposed using 1 of each of the Resources 1, 4, 5, 6 and 8, and with a profitability of $100. Should L7 be made – and why or why not? Argue and explain as clearly as you can. (3 marks) The introduction above to Question 1 included the statement: “Unless stated otherwise, you should not assume that the Xi are integers.’’ This applies to 1a-1n above. For each of 1o-1t (1o, 1p, 1q, 1r, 1s, 1t) below, throughout the rest of this question, state clearly whether or not you are assuming that all the Xi are integers. o) Returning to the problem in part d, suppose that we have the additional constraint as follows: if X5 > 0 then X1 = 0. What are the optimal values of (X1, X2, X3, X4, X5, X6) and the objective function? Clearly explain. (3 marks) p) Returning to the problem in part d, suppose that we have the additional constraint as follows: if X1 > 0 then X2 > 80. (Alternatively, you can make this additional constraint as follows: if X1 > 0 then X2 > 80. Choose exactly one of these two additional constraints, but make it very clear which of these two additional constraints you are using.) What are the optimal values of (X1, X2, X3, X4, X5, X6) and of the objective function? Clearly explain. (3 marks) q) Returning to the problem in part d, suppose that the number of Li’s produced (i.e, the number of different types of loom, Li, for which Xi > 0) is an odd number (1, 3, 5, …), with the other looms Li all having Xi = 0. What are the optimal values of (X1, X2, X3, X4, X5, X6) and the objective function? Clearly explain. (3 marks) r) Returning to the problem in part d, suppose that the number of Li’s produced (i.e, the number of different types of loom, Li, for which Xi > 0) is an even number (0, 2, 4, 6, …) , with the other looms Li all having Xi = 0. What are the optimal values of (X1, X2, X3, X4, X5, X6) and the objective function? Clearly explain. (2 marks) s) Returning to the problem in part d, suppose that for each Li that is produced (i.e., for each Li that has Xi > 0), there is a start-up cost of $2000. What is the new objective function? What are the optimal values of (X1, X2, X3, X4, X5, X6) and of the objective function? Clearly explain. (3 marks) t) Returning to the problem in part s) immediately above, suppose that, in addition, to the start-up costs of $2000 each, there is an additional cost of $950 if the number of looms produced (i.e, the number of different types of loom, Li, for which Xi > 0) is an even number (0, 2, 4, 6, …). What is the new objective function? What are the optimal values of (X1, X2, X3, X4, X5, X6) and of the objective function? Clearly explain. (3 marks) Throughout – both above (in Question 1) and below – recall Note 4 above on page 1. Throughout – both above (in Question 1) and below – recall Note 4 above on page 1. Question 2 – Trans-shipment [10 + 4 + 4 + (3 x 1) + 4 = 10 + 4 + 4 + 3 + 4 = 25 marks] Consider a network trans-shipment problem. We use some place names for which the transport costs might or might not be realistic – at least given current technology/ies – but possibly these given transport costs might one day become realistic. Here, we let A, C, G, K, M and W respectively stand for the Australian locations of Aldinga, Canberra, Geelong, Kalgoorlie, Mittagong and Wagga Wagga (which we might sometimes abbreviate and refer to as `Wagga’). We have supply (or source) of 80 and 90 each at Aldinga and Canberra respectively. We wish to move these goods to Mittagong and Wagga, where the respective demands are 70 and 100 respectively. In being transported from the sources (Aldinga and Canberra) to the sinks (Mittagong and Wagga), each item will go via (the intermediate nodes) either Geelong or Kalgoorlie. We note in passing that, not too far from Geelong (in the general direction of Melbourne) are Little River and Mount Rothwell, where the interesting historical site of Wurdi Youang (see, e.g., https://www.bbc.com/news/magazine-15098959 ) is located. 2a) Transportation costs along edges are as follows: To From Geelong Kalgoorlie Mittagong Wagga Wagga Aldinga 5 8 Canberra 7 4 Geelong 2 3 Kalgoorlie 3 6 Solve for the flow along all edges giving rise to the minimum total cost. As in earlier instructions (e.g., Note 4), show your answer clearly both in your .pdf and a corresponding cross-referenced spreadsheet tab – and clearly explain your working. (10 marks) 2b) The problem from 2a above is now modified. The road from Geelong to Mittagong is modified so that the first 40 units remain at $2/unit, but thereafter the cost is $9/unit. Similarly, the road from Geelong to Wagga Wagga is modified so that the first 50 units remain at $3/unit, but thereafter the cost is $10/unit. Solve for the flow along all edges giving rise to the minimum total cost. As in earlier instructions, show your answer clearly both in your .pdf and a corresponding cross-referenced spreadsheet tab – and clearly explain your working. (4 marks) 2c) Returning again to 2a, the problem from 2a above is now modified. For each two adjoining towns between which there is non-zero flow, the government charges a start-up tax (or edge penalty) of $150. Solve for the flow along all edges giving rise to the minimum total cost. As in earlier instructions, show your answer clearly both in your .pdf and a corresponding cross-referenced spreadsheet tab – and clearly explain your working. Show the number of edges with non-zero flow. (4 marks) 2d) (3 x 1 = 3 marks) 2d.i) We now have something of a hybrid of 2b and 2c above. We start with the same problem as in 2b. We then follow 2c, but the edge penalty that we add for non-zero flow between two adjoining towns (is not $150 as in 2c but) is $140. Solve for the flow along all edges giving rise to the min
imum total cost. As in earlier instructions, show your answer clearly both in your .pdf and a corresponding cross-referenced spreadsheet tab – and clearly explain your working. Show the number of edges with non-zero flow. 2d.ii) Repeat 2d.i but with penalty for non-zero flow between two adjoining towns of (not $150 and not $140 but) $160. Solve for the flow along all edges giving rise to the minimum total cost. As in earlier instructions, show your answer clearly both in your .pdf and a corresponding cross-referenced spreadsheet tab – and clearly explain your working. Show the number of edges with non-zero flow. 2d.iii) Repeat 2d.i and 2d.ii but with penalty for non-zero flow between two adjoining towns of $280. Solve for the flow along all edges giving rise to the minimum total cost. As in earlier instructions, show your answer clearly both in your .pdf and a corresponding cross-referenced spreadsheet tab – and clearly explain your working. Show the number of edges with non-zero flow. 2e) Returning now to 2a, suppose we have the additional requirement that the total of the flow from Aldinga to Geelong plus the flow from Kalgoorlie to Mittagong must be between 100 and 120 inclusive (i.e., at least 100 and at most 120). Solve for the flow along all edges giving rise to the minimum total cost. As in earlier instructions, show your answer clearly both in your .pdf and a corresponding cross-referenced spreadsheet tab – and clearly explain your working. (4 marks) Throughout – both above and (in Question 3) below – recall Note 4 above: “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 marking your work to follow your reasoning. Your .pdf should typically cross-reference the corresponding answer in your spreadsheet. For each sub-question and exercise, provide a clearly labelled spreadsheet tab with clear content, accompanied with clearly cross-referenced clear .pdf explanation. 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) [9 + 4 + 4 = 17 marks] This question is based on the economic order quantity (EOQ) theme. FictitiousWidgetCompany needs 1000 widgets per month. Widgets cost $625 each, but every time that it orders one or more widgets, there is an order (or delivery) cost of $15. The annual holding cost of a widget is 40% of the purchase cost of a widget. a) Based on the Economic Order Quantity (EOQ) model, what is the optimal number of widgets to order at a time? Show clear calculations to get your result, with clearly documented cross-reference to any spreadsheet tab that you might use. Such a spreadsheet tab should also be clearly laid out. Clearly explain your working. (9 marks) b) Following on your answer to a) immediately above, how many orders should be placed per annum? Again, show clear calculations to get your result, with clearly documented cross-reference to any spreadsheet tab that you might use. Such a spreadsheet tab should also be clearly laid out. Clearly explain your working. (4 marks) In a) and b) above, back-orders were not allowed and never took place. Leading into part c), the company now decides to modify this policy. The company has since calculated – or, at least, estimated – that the back-order penalty from disgruntled customers for an item out of stock is $250 for every 3 months that an item is out of stock, pro rata (e.g., $500 for every 6 months, etc.). c) Re-visiting (a) above but now allowing for the possibility of back-orders, what is the optimal number of widgets to order at a time? Again, show clear calculations to get your result, with clearly documented cross-reference to any spreadsheet tab that you might use. Such a spreadsheet tab should also be clearly laid out. Clearly explain your working. (4 marks) Throughout, recall Note 4 above: “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 marking your work to follow your reasoning. Your .pdf should typically cross-reference the corresponding answer in your spreadsheet. For each sub-question and exercise, provide a clearly labelled spreadsheet tab with clear content, accompanied with clearly cross-referenced clear .pdf explanation. Without clear cross-reference between .pdf and spreadsheet tab, there is the possibility that any such exercise will be awarded 0 marks.’’ A note about your Spreadsheet Model When building your model, bear in mind the goals and guidelines for good spreadsheet design as discussed in Lecture 3. Marks are given for good spreadsheet design. Marks will possibly also be given for originality. Format both your models clearly with comments (and, if possible, shading), etc. so that it is easy for the user to distinguish which cells are occupied by decision variables, LHS and RHS constraints, and the objective function. Include a textbox in each 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 the following: 1. A text-based .pdf document (save as: FamilyName-StudentId-2ndSem2019FIT5097.pdf) that includes all your answers to Questions 1 and 2 and 3 (except for the Microsoft Excel Solver part of each question); and 2. A Microsoft Excel workbook (save as: FamilyName-StudentId-2ndSem2019FIT5097.xlsx) that includes the following spreadsheets: i. the spreadsheet model for Question 1; ii. Sensitivity Rep – the sensitivity report for the Question 1 model (and any other relevant parts); iii. other relevant things (including any calculations) for Question 1; iv. relevant things (including any calculations) for Question 2 v. relevant things (including any calculations) for Question 3 vi. 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 a similarity 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-2 carefully. Also recall that, as a general rule, when answering questions, don’t just give a number or an answer like `Yes’ or `No’ without at least some clear and sufficient explanation.) Late penalties: Work submitted after the deadline (possibly with a small amount of grace time) will be subject to late penalties in accordance with the FIT5097 Unit Guide and Faculty and University policies, and certainly no less than 5% per calendar day. If you do not submit matching .pdf and .xls/.xlsx files (e.g., if you submit two files but one is blank or unreadable, or if you only submit one file), then your work will be deemed late – and will 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 Moodle assignment submission details permit you to declare this. For example, if you are presented with an ‘Assignment Electronic Plagiarism Statement’, then you are required to complete the ‘Assignment Electronic Plagiarism Statement’ quiz on the FIT5097 Moodle site and accept the Student Statement (electronic version of the Assignment cover sheet). If you do not accept the Student Statement, then your assignment may not be marked, and you may be given a
mark of 0. Recall instructions above and notes on pages 1 to 2 (including but not only, e.g., Note 4, Academic Integrity, Special Consideration, make sure to hit the `Submit’ button, the scheduled interview is compulsory if you want a mark of greater than 0, etc.), and please follow these carefully. And a reminder not to post even part of a proposed partial solution to a forum or other public location. You are reminded that Monash University takes academic integrity very seriously. *** END FIT5097 Assignment Faculty of I.T., Monash University 2nd semester 2019 ***
