Here are the case study and the tips for the assignment
and remember need to use excel and excel solver to answer all case questions. thanks
1 | P a g e Production and Distribution Network for QuickClean Background QuickClean, a rapidly growing company based in Melbourne, Australia, manufactures throwaway paper towel and mop cloth and supplies its products to supermarkets in the whole Australia. Tony Richardson, director of supply chain at QuickClean, believes that the company is facing a critical supply chain problem as the current production and distribution network is inefficient. This is mainly due to the significant increase in transportation costs over the past few years which can continue in the coming years. The current centralized supply network design has resulted in long journey distance in distribution hence very high distribution cost. A quick decision on building one or more new plants in other cities could save the company significant amounts in transportation expense in the future. QuickClean was founded in the late 1990s and produced throwaway paper towel and mop cloth that could make household chores a lot simpler. Current and ultimate demand for the two products in the eight states of Australia is shown in Table 1. The company currently has a factory in Melbourne only that produces both products for all the states in Australia. The paper towel production line has a capacity of 12 million units per year, an annualized fixed cost (e.g., maintenance, insurance, and other overheads) of $2 million, and a variable cost (e.g., production, materials, etc.) of $0.5 per unit. The mop cloth production line has a capacity of 10 million units per year, an annualized fixed cost of $1.5 million, and a variable cost of $0.8 per unit. The current transportation costs per unit (same for paper towel and mop cloth) are shown in Table 2. Table 1 – Current (C) and ultimate (U) annual demand for QuickClean from all states in Australia State Paper Towel Demand Mop Cloth Demand State Paper Towel Demand Mop Cloth Demand New South Wales (NSW) 3,093,000 (C) 3,827,000 (U) 2,577,000 (C) 3,125,000 (U) South Australia (SA) 663,000 (C) 821,000 (U) 553,000 (C) 670,000 (U) Queensland (QLD) 1,876,000 (C) 2,322,000 (U) 1,564,000 (C) 1,896,000 (U) Victoria (VIC) 2,500,000 (C) 3,093,000 (U) 2083,000 (C) 2526,000 (U) Northern Territories (NT) 96,000 (C) 119,000 (U) 80,000 (C) 97,000 (U) Tasmania (TAS) 202,000 (C) 249,000 (U) 168,000 (C) 204,000 (U) Western Australia (WA) 1,016,000 (C) 1,257,000 (U) 847,000 (C) 1,027,000 (U) Australian Capital Territory (ACT) 154,000 (C) 192,000 (U) 128,000 (C) 155,000 (U) Tony has identified Sydney, Brisbane; Adelaide; and Perth as potential sites for new plants (see Figure 1). Each new plant could have a paper towel production line, a mop cloth production line, or both. Construction cost for a new plant is $8 million with a single 2 | P a g e production line and $10 million with both production lines regardless of location. Using newer production technology, a new paper towel line has a capacity of 8 million units per year, an annual fixed cost of $1.5 million, and a variable production cost of $0.4 per unit. A new mop cloth line has a capacity of 6 million units per year, an annual fixed cost of $1.2 million, and a variable production cost of $0.7 per unit. It is expected that ultimately the demand at the eight states would be as shown in Table 1. Tony is planning for the long‐term production and distribution network of the company. His primary objective is to minimize the total construction, fixed, variable, and transportation costs. Nonetheless, his boss also wants him to take into account strategic considerations. That is, if two options are similar in cost but one is strategically favourable, he may recommend it for the board’s consideration. So, Tony has to decide whether to build any new plant(s), where to build and which production line(s) to put into the new plant(s), given the current transportation costs (in Table 2) which can be double in the long run. Table 2 – Current transportation costs for QuickClean from plants (existing and new) to all states NSW QLD NT WA SA VIC TAS ACT Melbourne $0.11 $0.28 $0.35 $0.37 $0.18 $0.04 $0.06 $0.07 Sydney $0.04 $0.25 $0.37 $0.43 $0.22 $0.11 $0.14 $0.04 Brisbane $0.11 $0.19 $0.34 $0.46 $0.25 $0.20 $0.25 $0.15 Adelaide $0.17 $0.24 $0.27 $0.27 $0.09 $0.09 $0.15 $0.14 Perth $0.49 $0.44 $0.32 $0.11 $0.29 $0.41 $0.44 $0.46 For the purpose of his analysis, Tony uses a period of ten years (Year 1 to Year 10) as a basis for comparison. To make things simple, he assumes that the current year is Year 0 and the next year is Year 1. From Year 1 to Year 10, the ultimate demands from each of the states will the used. Transportation costs may or may not change. But if they do, he again assumes that the change would take effect in Year 1 and the new costs could maintain throughout the 10‐ year period. He also assumes that any new plant(s), if built, are available in Year 1 and the construction cost is incurred immediately. Therefore, the 10‐year total cost should include construction cost (if any), annualized fixed cost, variable production cost and transportation cost from Year 1 to Year 10. This will be used for comparison between options. Case Questions Assume you were Tony and you need to answer the following questions from your boss: 1. Assuming the present is Year 0, what is the current (or Year 0) total annual cost of serving the entire nation from Melbourne only given the current transportation cost? 2. (a) Given the current transportation cost, what is the 10‐year (Year 1 to Year 10) total cost of serving all the states from Melbourne only? (b) What if the transportation costs were double their current value? 3 | P a g e 3. At current transportation costs, would you recommend adding any plant(s)? If so, where should the plant(s) be built and what line(s) should be included? (Note: You can assume that the Melbourne plant will be maintained at its current capacity but could be run at lower utilization or even just one production line.) 4. Would your decision in Question 3 be different if transportation costs were double their current value? 5. If Tony could design a new network from scratch (assume that he did not have the Melbourne plant but could build it, if needed, at the same costs and capacity for the new plant specified in the case), what production network would you recommend if transportation costs remain at their current value? 6. Would your decision in Question 5 be different if transportation costs were double their current value? 7. If Tony were required to set up only one production line at each plant, and assuming he could design a new network from scratch as he did in Question 5, what production network would you recommend if transportation costs remain at their current value? 8. Would your decision in Question 7 be different if transportation costs were double their current value? 9. Based on the outcomes of the above analyses and taking into account the need for strategic consideration, what long‐term production and distribution network will you recommend to the boss and why? Figure 1 – Demand regions and plant sites for QuickClean Page 1 of 5 Tips for OMGT1053 Assignment 2 Production and Distribution Network Design for QuickClean Dear Students, Here are some tips to help you complete the quantitative assignment of the course. The assignment is about network optimization and will require the use of Solver to find the optimal solutions. For this assignment, you will need to first develop a generic model on a spreadsheet and validate it. Then, with proper modifications in terms of model input, e.g., demand figures, unit transportation costs, and constraints in Solver, e.g., condition that reflects the requirement of a particular option, the model can be used as a tool to help find the optimal production and distribution network design for QuickClean - the case company. You can set up the model using the SunOil example as a reference. Despite the fact that there is only one product in the SunOil example but two in the QuickClean case, both problems are identical in nature and are categorized as Capacitated Facility Location Problems. It means that their mathematical formulations are very similar, although some minor modification is needed in the case of QuickClean as production and transportation costs are separated. Basically, the objective function is a cost functional comprising fixed and variable operating costs. In the QuickClean case, fixed costs are the annualized costs for the production lines. Variable costs are production and material costs which vary with the volume of production. There are also transportation costs which are also variable as they vary with the amount of products distributed to customers. If new plants are built (for some of the options), there will be construction costs as well but they can be added to the total cost later. This will make the problem formulation a lot simpler. The constraints in the QuickClean case include demand constraint (i.e., all demand must be met), capacity constraint (i.e., available capacity cannot be exceeded), non-negativity constraint (i.e., values of all variables cannot be negative) and binary constraint (i.e., production lines can either be open or close only). There is no need for integer constraint (number of plants must be in whole number) because it is unlikely that there will be more than one plant at one potential site or city. The best way to go is to first create a model representing the current or the as-is situation, i.e., Year 0. This will be needed to answer Case Question 1. To work out the total operating cost for Year 0, you do not need Solver at all because everything is fixed and there is no alternative course of action, hence no need for optimization. However, it will still be good to use Solver to find the solution because this will allow you to (i) cross-check if your cost figure is correct and (ii) have a generic model that can be copied and modified for option or scenario testing. As the layout of all the other models will be the same as that of the generic model, and the Solver setups will be quite similar, you can simply copy the generic model and rename it to become another model for a particular option or scenario. There are four options to investigate in the QuickClean case. Total cost in a 10-year period (Year 1 to Year 10) is used as a basis for comparison. For each option, there are two scenarios: (1) unit transportation costs remain unchanged as they are; and (2) unit transportation costs are double in value. The four options are as follows: (1) Producing and distributing from Melbourne only to all demand regions (or states) (Q2a and Q2b); (2) Producing and distributing from Melbourne and other cities, such as Brisbane, Sydney, Adelaide, and Perth (Q3 and Q4); (3) Producing and distributing from any cities (i.e., Melbourne is no longer a must) (Q5 and Q6); and (4) Producing and distributing at any cities with only one production line at each plant (Q7 and Q8). Upon analysing the outcome of the four options (each with two scenarios), you will need to make a recommendation on which option to take considering cost and strategic value (Q9). For each option, you will need two models to find the optimal network configurations under the two scenarios. As mentioned above, this can be done easily by copying the validated generic model to a new spreadsheet and rename it. Next, alter the input parameters of the model and revise the constraints in the Solver dialogue box where appropriate to reflect the condition of the option. Then, run Solver to get the optimal solution for that scenario. Basically, the workbook should comprise 10 worksheets. Nine (9) of them Page 2 of 5 should be models (for Q1, Q2(a), Q2(b), Q3, Q4, Q5, Q6, Q7 and Q8), each representing a scenario under an option. The last worksheet (for Q9) is not a model but a summary of the model outputs and the final recommendation upon comparison and analysis of the findings. NOTE: DO NOT use only one single spreadsheet or a summary report in the Excel workbook for everything and expect the assessor to create the models for the different options or scenarios from scratch to check your answers. Marks will be deducted if the required nine models (each in one sheet) and one summary sheet are not provided. Unlike the SunOil example in which only operating cost is considered, we have to take into account construction cost of the new plants in the QuickClean case in deciding on the optimal long-run production and distribution network configuration. For simplicity reason, you can use the same formulation of the SunOil example (with minor modification) for the QucikClean case. In other words, it means that the model for the QuickClean case also does not have construction cost in the objective function. As such, you cannot make recommendation simply based on the Solver solutions. Instead, they can help you work out the total annual costs for production and distribution for the different options. You can use them to work out the 10-year total operating costs. Then, if new plants are built under an option, construction cost can be added to the 10-year operating costs. These 10-year total costs (fixed, variable, transportation, and construction), together with their corresponding configurations, can then be compared to determine which option would be most desirable from both a cost and a strategic perspective. Here are some suggestions for you to set up the model: 1. There are many ways to build the model. You are encouraged to use your own design keeping in mind that the model logic needs to be easy to follow and understand while the model layout is simple and clear. Proper colour scheme and legend should be used where appropriate to make