Excel file has been completed only written report is required. Part of a group assignment, requires analysis, recommendation. Files are attached
Instructions:
The same group working for Assignment 1 will continue to work together for Assignment 2 unless prohibited by circumstances. Any change in group composition, only when absolutely required, needs to obtain the prior approval of the local lecturer and the course coordinator.
Read the case study “New Bakery for Oz Bread” which is attached below.
Develop an Excel workbook with multiple worksheets, each representing a supply network model for Oz Bread under different scenarios optimized using Solver. You may wish to refer to the tips provided below for advice on how to create the model and use it as a tool for analysis in this case.
Write a 3,000-3,500 word business report summarising the case, the objectives, the issues identified, and your analysis of the different networks options under consideration, together with your recommendation and conclusion. Answer each of the case questions separately in a separate section at the end of the report. Use summary tables and figures to present the findings where necessary and appropriate. Show the mathematical formulation of the problem and the Solver setup in the appendix.
1 | P a g e New Bakery for Oz Bread Background Oz Bread, a rapidly developing new bakery in Melbourne, is facing a critical supply chain problem. Mitchell McGuire, supply chain manager of Oz Bread, was asked by the boss to find a solution. Given the continuous growth in business over the years, it is obvious that the current production and distribution network of the company needs to be restructured. Oz Bread started off with a single baking facility in Mentone. Every day, the freshly baked breads and pies are delivered to its shops located in Glen Waverley, Doncaster, Melbourne CBD, Thomastown, St. Albans, and Hoppers Crossing. Business is growing and soon the maximum daily production capacity at the Mentone baking plant will be reached. A quick decision on building one or more new baking plants could save the company significant amount of money in lost sales in the future. A new baking plant will take a year to build from planning to completion. For example, if Oz Bread decides in this year to build a new baking plant, the earliest date the new facility is available will be next year. Oz Bread was founded eight years ago and has been producing since then fresh breads and delicious gourmet meat pies for Melburnians. Current average daily demands for their breads and pies, which are relatively stable throughout the year, are shown in Table 1. The shops open 360 days a year. It is expected that the demands (breads and pies alike) at the existing shops will grow by the percentages shown in Table 1 for another three years before they become stabilized due to market saturation. For simplicity reason, it can be assumed that the increase in demand takes effect all of a sudden at the beginning of each year and now it is the beginning of the current year. At present, the company has one baking plant in Mentone which produces both products for the entire metropolitan area of Melbourne. Table 1 – Average daily demand for breads and pies at Oz Bread in current year Shop Daily Demand Glen Waverley Doncaster Melbourne CBD Thomastown St. Albans Hoppers Crossing Breads 700 1000 1,500 500 800 1,000 Pies 400 700 1,000 300 450 750 Growth in Year 1 15% 12% 20% 18% 15% 12% Growth in Year 2 10% 6% 10% 12% 8% 6% Growth in Year 3 5% 3% 2% 6% 4% 3% The bread production line at the Mentone baking plant has a capacity of 6,000 units per day, an annualized maintenance and overhead cost of $200,000 a year, and a production cost of $0.3 per unit. The pie production line has a capacity of 4,000 units per day, an annualized maintenance and overhead cost of $300,000 a year, and a production cost of $0.5 per unit. 2 | P a g e For simplicity reason, it can be assumed that the annualized maintenance and overhead cost will not be incurred if the production line is not running in the year. New Network Options Upon careful analysis of the locations of the existing shops and possible expansion of the company’s business in the future, Mitchell has identified three suburbs – Prahran, Northcote, and Laverton North – as potential sites for the new baking plants. At the new facilities, a bread production line or a pie production line or both can be set up. Using newer baking technologies, the new plants can run at lower costs. Production capacities, construction costs, annualized fixed costs (i.e. maintenance and overhead costs), and unit production costs of the new plants are shown in grey in Table 2. It can be assumed that all these costs will remain unchanged in the next three years until the demands become stabilized. For the new plants, a saving of 30% from the construction cost can be achieved if only one production line is constructed. However, once the plant with a single production line is built, it will not be possible to add another production line in the future. Shutting down the existing facility at Mentone can recover at most $100,000 in scrap value. If any of the new plant constructed at Prahran, Northcote, or Laverton North has to be shut down in the end due to underutilization, the maximum scrap value that can be retrieved is 10% of the construction cost. To make things simple, net present value is not considered in this case. Table 2 – Cost figures of the current and the potential new bakery facilities for Oz Bread Plant Attribute Existing Potential Site Mentone Prahran Northcote Laverton North Capacity for Baking Breads per Day 6,000 6,000 7,000 7,500 Capacity for Baking Pies per Day 4,000 4,500 5,200 5,500 Construction Cost Already built $1,200,000 $1,500,000 $1,600.000 Annual Fixed Cost for Baking Breads $200,000 $220,000 $240,000 $240,000 Annual Fixed Cost for Baking Pies $300,000 $300,000 $320,000 $320,000 Variable Cost for Baking Breads $0.3 /unit $0.25 /unit $0.25 /unit $0.25 /unit Variable Cost for Baking Pies $0.5 /unit $0.45 /unit $0.45 /unit $0.45 /unit The current transportation costs per unit from the Mentone baking facility to the shops are shown in Table 3. The estimated transportation costs per unit (in current year) from the potential sites for the new plants to the shops are also shown in in grey Table 3. It can be assumed that these costs will remain more or less the same in the next three or more years. 3 | P a g e Table 3 – Existing and estimated transportation costs per unit for breads and pies (at current year) Shop Plant Glen Waverley Doncaster Melbourne CBD Thomastown St. Albans Hoppers Crossing Mentone (Existing) $0.10 $0.12 $0.11 $0.20 $0.22 $0.24 Prahran $0.10 $0.12 $0.04 $0.12 $0.15 $0.17 Northcote $0.18 $0.16 $0.05 $0.04 $0.10 $0.11 Laverton North $0.22 $0.24 $0.10 $0.13 $0.04 $0.05 Based on the above information, Mitchell has to decide for the next three years where to build the new plants and, if so, which production lines to put into the new facilities. Case Questions Assume you were Mitchell and you need to answer the following questions of your boss: 1. As‐Is Situation: What is the current annual cost of serving all the shops from the Mentone baking plant? 2. Scenario A: If the existing Mentone baking plant must be kept but not necessarily making both breads and cakes, what new plants would you recommend for the next three years? Where should they be built? What production lines should be included and how should the shops be served by the existing and the new baking plants? Make your recommendations on a year‐by‐year‐basis (starting from Year 1). 3. Scenario B: If the existing Mentone baking plant must be scrapped because of its out‐of‐ date production technology and new plants are to be built in other suburbs instead, what production network would you recommend and how should the shops be served by the new baking plants? Again, make your recommendations on a year‐by‐year‐basis (starting from Year 1). 4. Scenario C: If only one production line is to be run at each baking plant at any time (i.e., either baking breads or pies but not both), assuming you could use any plant including the existing one at Mentone, what production network would you recommend and how should the shops be served by the baking plants? Again, make your recommendations on a year‐by‐ year‐basis (starting from Year 1). 5. Action Plan: Taking into account the construction costs of the new plants and the scrap value of the existing plant and assuming the demand for breads and pies will become stabilized in three years, what is the network configuration you would recommend for Oz Bread for the long run and how should the shops be served by the baking plants? Analyze the total costs involved under the three scenarios taking into account the construction cost of the new plants and the scrap value of the existing baking facility at Mentone. Upon the 4 | P a g e analysis, generate an action plan for your final recommendation on a year‐by‐year basis from Year 0 to Year 3 assuming the current year is Year 0, i.e., what should Oz Bread do at the beginning of Years 0, 1, 2 and 3, if any. Notes: 1. In calculating the new demand, round to the nearest whole number. You can generate the Year 1 demand from the Year 0 figures, round them to integers and then use the rounded figures to generate the Year 2 figures. Repeating the same procedure, you can get the Year 3 figures. 2. For simplify reason, net present value, inflation, and depreciation, etc. can be ignored in the calculation. 3. You need to note that it might not be always desirable to make your recommendations entirely on the basis of the solutions given by Solver. This is because, for simplicity reason, your problem formulation might not include construction cost. As such, Solver would find an optimized solution taking into account operating cost only. So, it might recommend using multiple plants to minimize total operating cost. You may come into a situation that a new plant is needed in one year but will no longer be required in another year leading to waste. Therefore, you are strongly recommended to use the Solver solutions as reference only and use your logical thinking to revise the year‐by‐year configurations. The objective is to minimize the long‐run operating cost (referring to that of Year 3) while minimizing the total construction cost of the new facilities (from Year 0 to Year 2). In doing so, the operating costs of Year 1 and Year 2 may be higher than that of the Solver solutions for a particular scenario but on the whole the total construction cost and the long‐run operating cost can be minimized. 4. In building new plants, you need to note that if only one production line is included at the beginning to save cost, it will not be possible to add another production line at a later stage. So, if a new plant is to run one production line in one year and then the other production line in another year, both production lines must be included when the plant is first built. In other words, if both production lines in a plant will be used even though not at the same time, they must be provided right at the beginning when the plant is constructed. This will affect the total costs of the three scenarios as well as the action plan. 1 | P a g e OMGT1053 ASSIGNMENT 2 REPORT ASSESSMENT SUMMARY Group: Date: Name Student Number Assessment Outcome Criterion Weight Comments Mark Report Content ‐ All required tasks completed ‐ Rich and substantive content evident ‐ Accurate and updated information used ‐ Vital information included 30% Analysis and Discussion ‐ Derivation of optimization model clearly explained ‐ Evidence of in‐depth analysis ‐ Systematic and logical explanation ‐ Convincing arguments supported, where appropriate, by empirical evidence or references 40% Report Organization ‐ Well‐developed and logical report structure ‐ Appropriate use of headings and sub‐headings ‐ Contents in sections and sub‐ sections reflect headings indicated 10% Report Presentation ‐ Clear writing, high readability ‐ Appropriate use of tables, charts and figures ‐ Professionally acceptable presentation quality (e.g., no typographical and spelling errors and sloppy formatting) ‐ Consistent in‐text referencing using Harvard Referencing Style ‐ List of References matches references used in report 10% Effort Invested ‐ Clear evidence of significant efforts expended on assignment 10% TOTAL 2 | P a g e Penalty for failure to acknowledge information sources and ideas appropriately, including poor referencing, based on Similarity Index indicated in Turnitin Report (applicable where Similarity Index > 20%): Comments Penalty Applied Overall Comments raw data Plant Cost and Capacity BreadsPies PlantAnnual fixed costVariable cost/unitDaily capacityAnnual fixed costVariable cost/unitDaily capacityCurrent Year 0360 days Mentone$ 200,000$ 0.306000$ 300,000$ 0.504000 Prahran$ 220,000$ 0.256000$ 300,000$ 0.454500 Northcote$ 240,000$ 0.257000$ 320,000$ 0.455200 Laverton$ 240,000$ 0.257500$ 320,000$ 0.455500 Daily Demand by Productyear 1 demand ShopGlen waverleyDoncasterMelbournce CBDThomastownSt. AlbansHoppers crossing805112018005909201120year 3 Demand Breads70010001500500800100046078412003545188409301223202070110341223 Pies400700100030045075015%12%20%18%15%12%5318561346420581917 Annual growth year 2 Demand5%3%2%6%4%3% 886118719806619941187 Transportation Cost Per Unit5068311320396559890 Plant/ShopGlen waverleyDoncasterMelbournce CBDThomastownSt. AlbansHoppers crossing10%6%10%12%8%6% Mentone$ 0.10$ 0.12$ 0.11$ 0.20$ 0.22$ 0.24 Prahran$ 0.10$ 0.12$ 0.04$ 0.12$ 0.15$ 0.17 Northcote$ 0.18$ 0.16$ 0.05$ 0.04$ 0.10$ 0.11 Laverton$ 0.22$ 0.24$ 0.10$ 0.13$ 0.04$ 0.05Input parameters Daily Production and distribution of Breads Plant/ShopGlen waverleyDoncasterMelbournce CBDThomastownSt. AlbansHoppers crossingLine open 1 or close 0Total Daily Production Mentone0 Prahran0 Northcote0 Laverton0 Total Daily supply000000 Daily Production and distribution of Pies Plant/ShopGlen waverleyDoncasterMelbournce CBDThomastownSt. AlbansHoppers crossingLine open 1 or close 0Total Daily Production Mentone0 Prahran0 Northcote0 Laverton0 Total Daily supply000000 Annual Cost Fixed cost0Decision variables Variable cost0Constraint Transportation cost0Cost Total operational cost0 Question 1 Plant Cost and Capacity BreadsPies PlantAnnual fixed costVariable cost/unitDaily capacityAnnual fixed costVariable cost/unitDaily capacityCurrent Year 0 Mentone$ 200,000$ 0.306000$ 300,000$ 0.504000 Prahran$ 220,000$ 0.256000$ 300,000$ 0.454500 Northcote$ 240,000$ 0.257000$ 320,000$ 0.455200 Laverton$ 240,000$ 0.257500$ 320,000$ 0.455500 Daily Demand by Product ShopGlen waverleyDoncasterMelbournce CBDThomastownSt. AlbansHoppers crossingTotal Demand Breads7001000150050080010005500 Pies40070010003004507503600 Annual growth Transportation Cost Per Unit Plant/ShopGlen waverleyDoncasterMelbournce CBDThomastownSt. AlbansHoppers crossing Mentone$ 0.10$ 0.12$ 0.11$ 0.20$ 0.22$ 0.24 Prahran$ 0.10$ 0.12$ 0.04$ 0.12$ 0.15$ 0.17 Northcote$ 0.18$ 0.16$ 0.05$ 0.04$ 0.10$ 0.11 Laverton$ 0.22$ 0.24$ 0.10$ 0.13$ 0.04$ 0.05 Annual Cost Fixed cost$ 500,000 Variable cost$ 1,242,000 Transportation cost$ 519,840 Total cost$ 2,261,840 Scenario A Year 1 Plant Cost and Capacity BreadsPies PlantAnnual fixed costVariable cost/unitDaily capacityAnnual fixed costVariable cost/unitDaily capacityConstruction CostCurrent Year 1 Mentone$ 200,000$ 0.306000$ 300,000$ 0.5040000 Prahran$ 220,000$ 0.256000$ 300,000$ 0.454500$ 1,200,000not including fix cost (maintenance and overhead cost) Northcote$ 240,000$ 0.257000$ 320,000$ 0.455200$ 1,500,000only operational cost (variable and transportation cost) Laverton$ 240,000$ 0.257500$ 320,000$ 0.455500$ 1,600,000 Constraint (Binary)1010 Daily Demand by Product1100 ShopGlen waverleyDoncasterMelbournce CBDThomastownSt. AlbansHoppers crossingtotal demand daily1110 Breads year 18051120180059092011206355MentonePrahanNorthcoteLaverton North Pies year 146078412003545188404156Bread Line is opened/ closedYear 110102110 Annual growth 15%12%20%18%15%12%Pie Line is opened/ closedYear 11100100-1 either line is opened/closedYear 11110 Transportation Cost Per Unitboth line is opened/closedYear 11000 Plant/ShopGlen waverleyDoncasterMelbournce CBDThomastownSt. AlbansHoppers crossingbread line is open or notCurrent year1000 Mentone$ 0.10$ 0.12$ 0.11$ 0.20$ 0.22$ 0.24Year 10010 Prahran$ 0.10$ 0.12$ 0.04$ 0.12$ 0.15$ 0.17pie line is open or notCurrent year1000 Northcote$ 0.18$ 0.16$ 0.05$ 0.04$ 0.10$ 0.11Year 10100 Laverton$ 0.22$ 0.24$ 0.10$ 0.13$ 0.04$ 0.05Input parameters Fixed cost200000220000240000240000 Daily distribution of Breads300000300000320000320000 Plant/ShopGlen waverleyDoncasterMelbournce CBDThomastownSt. AlbansHoppers crossingTotal Daily Production Mentone0000000Daily Production of BreadYear 10063550Daily capacity for bread6000600070007500 Prahran0000000Daily Production of PiesYear 10415600Daily capacity for pies4000450052005500 Northcote8051120180059092011206355 Laverton0000000 Total Daily supply805112018005909201120total supplyConstraintBread- 0- 07,000- 0variable cost/unitBread0.30.250.250.25 Pies- 04,500- 0- 0Pies0.50.450.450.45 Daily distribution of Pies Plant/ShopGlen waverleyDoncasterMelbournce CBDThomastownSt. AlbansHoppers crossingTotal Daily Production Mentone0000000 Prahran46078412003545188404156 Northcote0000000 Laverton0000000 Total Daily supply4607841200354518840 Annual Cost Fixed cost540000Decision variables Variable cost1245222Constraint Transportation cost397426Cost Total operational cost2182647.6not including long term fix cost objective function total construction cost$ 2,700,000less$ 360,000=$ 2,340,000 scrap value total cost$ 4,522,647.60 A Year 2 Plant Cost and Capacity BreadsPies PlantAnnual fixed costVariable cost/unitDaily capacityAnnual fixed costVariable cost/unitDaily capacityConstruction CostCurrent Year 2 Mentone$ 200,000$ 0.306000$ 300,000$ 0.5040000 Prahran$ 220,000$ 0.256000$ 300,000$ 0.454500$ 1,200,000not including fix cost (maintenance and overhead cost) Northcote$ 240,000$ 0.257000$ 320,000$ 0.455200$ 1,500,000only operational cost (variable and transportation cost) Laverton$ 240,000$ 0.257500$ 320,000$ 0.455500$ 1,600,000 Constraint (Binary)1010 Daily Demand by Product1010 ShopGlen waverleyDoncasterMelbournce CBDThomastownSt. AlbansHoppers crossingtotal demand daily1010 Breads year 28861187198066199411876895MentonePrahanNorthcoteLaverton North Pies year 250683113203965598904502Bread Line is opened/ closedYear 110102020 Annual growth 10%6%10%12%8%6%Pie Line is opened/ closedYear 110101-11-1 either line is opened/closedYear 11010 Transportation Cost Per