▪ Mode of Submission: Online on Moodle ▪ Type of Assignment: Group Assignment ▪ Due Date: Week 12 Monday 21st October 2019 11PM This group assignment requires the collaborative effort of three students per group. Students must form their group (within the same tutorial class) and elect a group leader to coordinate this assignment. He or she must inform the relevant tutor of their group members’ names prior to the end of Week 4. Once confirmed, no change of group member is allowed without prior approval from the FIT3158 lecturer. About 15-20 minutes will be allocated at the end of each week’s tutorial for group members to come together to discuss and work on the assignment. You may also use this time to consult your tutor if you have any issues with your assignment. Every student in the group is required to actively participate and would be used as a basis for marks adjustment for the final score (if necessary) of the assignment. Learning Objectives (Learning Outcomes: 1, 2, 3, 4 & 5) The aim of this assignment is to provide students with an opportunity to develop their problem solving as well as modelling skills. In so doing, the following objectives are sought: ▪ The ability to critically analyse business problems, for example, Linear Programming or Integer Linear Programming problems in Part I and as relevant in subsequent Parts; ▪ The ability to design, implement and analyse mathematical optimisation and spreadsheet-based models; ▪ The ability to conduct sensitivity analysis and to interpret the results of mathematical decision models to aid business decision; ▪ The ability to critically assess the accuracy and applicability of their model; ▪ The ability to present and communicate findings in a clear and concise manner with relevant discussion and commentary with regard to raised questions Assessment Criteria: ▪ Correct formulation ▪ Good spreadsheet design. (Refer to Lecture 2) ▪ Correct and reliable results. ▪ Correct response to the questions asked. ▪ Good analysis and discussion/recommendations showing understanding. FIT 3158 Business Decision Modelling Semester 2 2019 Assignment – Part I Delivery structure and submission instructions: Your delivery for the Assignment should include: 1) Separate Excel workbooks for each part in the assignment containing: a) Relevant Case Model for each part b) Relevant Case Reports for the Case Model in each part (Answer/Sensitivity/Limits) 2) A single pdf/word document addressing the questions of all Parts (Part I – Part III) in the Assignment organised in a clear and logical manner according to Assignment Part and relevant section and question numbers under each part. You are to upload your submission on the FIT3158 Moodle site under the “FIT3158 Assignment” submission link by the due date. All submission must be in the form of; Excel Workbooks, saved as:
Assignment-.xlsx (or xlsm) and a pdf/Word document saved as Assignment.docx (or .pdf). Assignment Specifications and Deliverables: Assignment – Part 1 (32 Total Marks – Accounting for 8 Marks out of 20) Bill Nash, the CEO of MeWrite Books, a small but growing paper-book manufacturer is planning to establish a stronger position for the company on the market. Computers, word processors, apps and mobile phones not-withstanding, he feels that there is still a place for physical books, especially regarding the use of notebooks, personal journals and graph-books. In particular, he is aiming for a middle ground between high-end product lines consisting of paper books with elaborate designs, higher quality sheen paper with good binding and high price and the countless companies offering low priced books with few trims, simple binding and coarse low- quality paper. Bill Nash wishes to make products with quality paper and durable binding that offers joy to write on and plot but with limited trims and elaborate designs. From surveys targeting a cross-section of the market from school and university students to writing connoisseurs like authors and professional draftsmen he knows that most people are willing to buy a paper book for writing or graphing purposes with quality durable paper but at a lower price compared to products offering similar quality paper with more elaborate designs at higher prices. On the other hand, most also prefer to use a book with higher quality paper and lasting binding particularly for graphing purposes or as a journal/notebook than what is available in almost all products at the lower end of the price market. MeWrite Books are only available in a simple range of cover colours and are available in most reputable stores for stationary. The company only makes three products a 180-page notebook, a personal journal and a 180-page graph-book. The per-unit profit of the items are $5 each for the notebook and graph book and $8 for the personal journal. These amounts include the cost of materials, labour and quality control as applicable. Bill Nash believes that the company can sell any number of paper books it produces, but the production is limited by resource availability. Recent industrial action and cash-flow problems have caused the suppliers of these resources to only sell in limited amounts to MeWrite Books. In particular, Bill can only source at most 1200 pounds of grade II Wood Pulp for covers, 2500 pounds of grade I Wood Pulp fibres for paper and 800 pounds of Binding Resin each week from his suppliers. It is unlikely that these figures will change in the immediate future. However, the suppliers are willing to sell any amount Bill needs up to his limit and when he requires them without the need to buy fixed quantities of resources in advance of the production making these costs variable costs rather than fixed costs to produce MeWrite Books. Each notebook requires 1.2 pounds of grade I wood pulp fibre, 0.6 pounds of grade II wood pulp and 0.3 pounds of binding resin while each graph-book requires 2.1 pounds of grade I wood pulp fibre, 1 pound of grade II wood pulp fibre and 0.4 pounds of binding resin. The personal Journal requires 2 pounds of grade I wood pulp, 0.8 pounds of grade II wood pulp and 0.4 pounds of binding resin. Bill is trying to come up with a weekly plan for its product mix using LP. While Bill knows that other constraints including labour will also need specific consideration, he wants a simple first-pass model that is restricted to the above primary resource constraints. Section A Considering the above, formulate the weekly production mix problem for MeWrite Books as an LP Model. Implement the formulation in Excel following goals/guidelines for good spreadsheet design and solve using solver. Then answer the following questions in Section B assuming each question as independent of the other questions unless explicitly referred to. (2x2 marks, formulation and excel implementation) Section B 1) What is the optimal mix of weekly products and the optimal value for weekly profit? 2) Are there alternate optimal solutions to the problem? Explain your answer. 3) Is the solution degenerate? Explain your answer. 4) What are the marginal values of one unit for each type of wood pulp and binding resin? 5) If an additional 300 pounds of grade I wood pulp is available through local sourcing for $3 more per pound than the normal price, should the company go ahead with the purchase? Explain your answer. 6) If MeWrite Books goes ahead with the purchase in Q5, what is the new optimal product mix and the new optimal profit value? Explain your answer. 7) Suppose MeWrite Books is able to source some additional grade II wood pulp at $1.5 per pound over its normal cost of $6 per pound in 100-pound packages, should Bill Nash go ahead with the purchase? Explain your answer. 8) If after negotiation the supplier in Q7 agrees to supply grade II wood pulp in 20-pound packages at the same additional price per pound, how many packages should be bought? What will be the new optimal product mix and the optimal profit if this amount of grade II wood pulp is added? 9) The usual supplier for grade II wood pulp is only able to send 1000-pounds for a particular week instead of 1200-pounds due to a logistics issue. If MeWrite Books only gets the 1000-pounds, what is the new product mix to produce and the optimal solution? Explain your answer. 10) MeWrite Books has the opportunity to sell some of their available grade II wood pulp at $8 per-pound (usually sourced by MeWrite Books for $6 per pound) to a non-competitor company. If the company is willing to buy 250 pounds, should MeWrite sell the grade II wood pulp? If not, what amount would MeWrite Books be willing to sell? Explain your answer. 11) What happens to the optimal weekly product mix and optimal profit value if MeWrite Books goes ahead with the transaction as suggested in your answer for Q10? 12) MeWrite Books is planning to add a new “Diary Planner” to its products mix with a design that requires 1.8 pounds of grade I wood pulp, 0.7 pounds of grade II wood pulp and 0.4 pounds of binding resin. If the company expects to sell a Diary Planner with a unit profit of $7.50, should the design be approved? Explain your answer. 13) If the unit profit of Journals falls by $1, what effect will it have on the solution and the optimal profit value? Explain your answer. 14) If the unit profit of Notebooks falls by 50 cents, what effect will it have on the solution and the optimal profit value? Explain your answer. (x2 marks for each question) 
