Scenario
The Magma Manufacturing Company delivers various white goods (major appliances such as fridges, freezers, washing machines, etc) by light truck, from its national UK distribution centres to eight retail outlets in the UK. The management have asked you to see if you can make this more efficient – the company costs and delivers in standard pallets. To do this you must find a way to minimise itst delivery costs. You have the following information:
The company distribution centres are designated: Distribution Centre A (DA), Distribution Centre B (DB), and Distribution Centre C (DC).
The retail centres are designated: Retail Centre 1 (R1), Retail Centre 2 (R2) and so on, up to Retail Centre 8 (R8).
Delivery costs per standard pallet are as follows:
Cost per Pallet Delivery
R1 R2 R3 R4 R5 R6 R7 D8
DA 2.30 3.00 2.35 2.10 2.25 3.00 2.50 3.20
DB 1.95 2.25 3.90 4.00 1.60 2.25 3.90 4.20
DC 3.45 2.50 1.90 1.10 4.00 2.20 3.10 2.15
Retail Centre Demand (Pallets)
R1 R2 R3 R4 R5 R6 R7 D8
Demand 1,400 1,200 2,700 3,400 2,100 1,300 1,050 600
Distribution Centre Maximum Pallet Capacity
DA 7,000
DB 8,500
DC 10,000
Task
Draw a diagram showing the decision variables of the distribution centres and retail outlets.
Define the objective formula.
Using a Microsoft Excel Spreadsheet and Solver, design a model to minimise the costs of delivery, showing which centres to use and how much they should deliver.
* Week 5: Supply Chain Modelling covers the technical aspects of building a Microsoft Excel Spreadsheet Solver for this assignment.
Identify the costs.
Incorporate the following changes and model the new delivery cost schedule.
Retail Centre 4 (R4) experiences a fire and has to close, reducing demand to 0.
Local wage negotiations mean Distribution Centre B (DB) costs per pallet increase by 1.00 per pallet for each retail centre.
Prepare the presentation with appropriate images, including screenshots, transportation and goods type, and clearly describing the Excel Solver, the formulas used and the results of your modelling.
Advise the company of the advantages and limitations of the use of Excel Solver in this instance.
