Supply Chain System Modelling. Portfolio (100%)
Coursework Task. Module content
The module will focus on a management science approach to model building/problem-solving and the evaluation of results generated through this analysis. The coursework task will focus on these four topics:
Forecasting.
Inventory Management: Economic Order Quantities
Delivery Algorithmns
Decision Making
Case Study:
Astrella Beer Company (ABC) is based in Catalonia, a region of Spain. The company has three factories producing beer at Berga, Lleida and Almeida, The beer is exported by a company called Beer Online Delivery Guaranteed Yesterday (BODGY) via five distributions centres three in Barcelona and two in Valencia. From these distributions centres the beer is exported to the ten customers; C1-C10.
Task 1: (20 marks)
Previously Astrella Beer Company (ABC) had maintained ample inventories of beer in their warehouse to fulfil orders immediately. So rough forecasts of demand was adequate to make decisions about stock replenishment. Scientific management is to be used to reduce inventory levels and minimise total inventory costs.
The logistics Manager Joan Brandon has developed a system of forecasting based on statistical analysis that predicts future consumption, see appendix A and the excel sheet,’ ABC Beer consumption monthly to Feb 2022’. The system is to generate two sets of forecasts:
- Moving average method
- Exponential smoothing
- Apply a monthly moving average technique to predict future consumption in Catalonia for the next three months. March to May 2022. You will need to choose and justify, the type of moving average chosen and how many months of previous data to use.
(6 marks)
- Apply the exponential smoothing method to forecast the future consumption for March 2022. You must decide and justify the initial estimate, the value of current demand (D) and the value of the smoothing constant (a).
(6 marks)
- Compare and critically evaluate your MAD and MSE values for sections (a) and (b)
(4 marks)
- Draw a graph showing the actual production figures against the forecasted levels for both methods in (a) and (b). Use this information and your MAD and MSE values to suggest possible further improvement in forecasting. (You do not have to implement these further improvements)
(4 marks)
Task 2: (20 marks)
Previously Astrella Beer Company (ABC) used to order the malt barley the main ingredient from the suppliers in bulk and store them at the distribution centre until orders arrived from customers. Sam Sidwell wants to reduce the storage costs and make the operation more efficient by reducing the order size down from the current policy of placing a weekly of approximately 400 kgs.
Typically the firm holds an average of 200Kg of stock. Astrella Beer Company (ABC) has notified Jo that the warehouse rent is £3 000, the annual delivery charges are £9 600, the wages for the purchase staff are £5 200 and the warehouse stores overheads are £14 400. Purchase department overheads are £4 800, warehouse staffing £19 200 and the cost of warehouse insurance is currently £10 000.
- (4 marks)
- (4 marks)
- (2 marks)
- (2 marks)
- (2 marks)
- (2 marks)
(4 marks)
Task 3: (20 marks)
Astrella Beer Company (ABC) operate three factories Berga (F1) Lleida(F2) and Almeida (F3) which supply to five wholesalersB1,B2,B3,V1,V2) which in turn supply ten customers (C1-C10).
- Draw a network diagram to illustrate this distribution network (4 marks)
The cost of distribution from warehouses to wholesalers is given in figure 2 and the cost of distribution from wholesalers to customers is given figure 3.
| B1 | B2 | B3 | V1 | V2 | |
| F1 | 10 | 9 | 13 | – | – |
| F2 | 11 | 12 | 12 | 12 | 14 |
| F3 | – | – | 11 | 12 | 13 |
Figure 2: The cost of distribution from warehouses to wholesalers
| C1 | C2 | C3 | C4 | C5 | C6 | C7 | C8 | C9 | C10 | |
| B1 | 7 | 9 | 10 | 8 | 10 | |||||
| B2 | 8 | 8 | 10 | 8 | 9 | 7 | ||||
| B3 | 9 | 9 | 8 | 8 | 9 | 11 | ||||
| V1 | 9 | 9 | 9 | 9 | 12 | 11 | ||||
| V2 | 8 | 8 | 9 | 10 | 9 |
Figure 3. The cost of distribution from wholesalers to customers
- What is the minimum cost of distribution for the whole supply chain for the month of March 2022 if the quantity produced at the factories; Berga(F1) 150, Lleida (F2) is 150 and Almeida(F3) is 100 and the customer orders are given in Figure 4.
| Order | C1 | C2 | C3 | C4 | C5 | C6 | C7 | C8 | C9 | C10 |
| 35 | 45 | 38 | 40 | 42 | 58 | 41 | 40 | 39 | 22 |
Figure 4. Number of customer orders.
(6 marks)
- Analysis the minimum cost for March 2022 and recommend to Sam Sidwell which wholesaler should be dropped to minimise the cost of the whole supply chain. What increase in cost would be incurred? Screenshot your Excel spreadsheet and annotate to justify your solution.
(6 marks)
- Sam criticises Jo because there is capacity of 40 units on all routes due to haulage limitations. Jo now alters her solution in Q3(b) by making the maximum amount to be shipped down each route a maximum of 30 units. What is the minimum total supply chain cost now? Does this affect your discussion to drop one of the wholesalers, discuss.
(4 marks)
Task 4: (20 marks)
Sam Sidwell has analysed customer trends and thinks there is a gap in the market for a Spanish tequila. There are two options for production. One is to outsource production to Ameida Alcohols (AA) sharing the production facilities they have. If sales are moderate this will work out well for Astrella Beer Company (ABC) as it will utilise spare capacity at the plant. However, if sales are strong Astrella Beer Company (ABC) are considering opening their own production facility which would satisfy the largest sales projection. If sales were only moderate the new plant would be under-utilised and therefore less efficient.
The sales for the tequila is unproven and sales are therefore hard to predict. (ABC) predicts there is an 80% chance of strong sales giving annual sales of 120 000 bottles and 20% chance of moderate sales giving annual sales 60 000 bottles. The revenue for the tequila would be 10 euros per bottle sold. Production costs for each bottle of tequilla are given in figure 5.
| Moderate Sales (€) | Strong Sales (€) | |
| Sharing AA’s facilities | 4 | 5 |
| ABC’s own factory | 7 | 6 |
Figure 5 Expected cost of production per bottle (Euro’s)
The annual cost of production at (AA’s) plant would be € 100 000. The cost of running their own plant would be € 200 000.
- Construct a decision tree to determine which production method would optimise the expected annual profit. (8 marks)
- The sales of the new design is very uncertain so (ABC) have decided to conduct a marketing survey to predict the likelihood of strong sales. The survey will give a percentage possibility of a positive or a negative survey. Using past alcoholic drinks, (ABC) knows that a positive survey gave 85% of strong sales and 15% moderate and a negative survey gave strong sales on 12% of occasions and 88% for moderate sales. Use Bayes formula to calculate posterior probabilities and redraw the decision tree. What is the expected annual profit ignoring the cost of the survey? (8 marks)
- Is it worthwhile conducting a survey? How much would it cost to make it worthwhile? (4 marks)
Task 5: (20 marks)
In order to fulfil the requirements of your Personal Development Plan (PDP) you must include a personal reflection on the process of managing your learning on this module. This is to be included at the end of your assignment and to be between 350 and 500 words in length.
You are expected to use appropriate problem solving software, Excel or Xpressive. In addition underpin your arguments with reference to models and concepts from the module and your wider reading.
Appendix A
ABC Beer consumption monthly to Feb 2022
| Monthly | Consumption | |
| 2018 | Jan | 9414.5 |
| Feb | 9731.2 | |
| Mar | 9988.8 | |
| Apr | 9458.0 | |
| May | 9831.8 | |
| Jun | 8176.2 | |
| Jul | 8461.7 | |
| Aug | 9119.9 | |
| Sep | 9655.3 | |
| Oct | 10271.5 | |
| Nov | 10049.8 | |
| Dec | 11729.6 | |
| 2019 | Jan | 10271.5 |
| Feb | 10501.4 | |
| Mar | 10257.8 | |
| Apr | 9453.5 | |
| May | 8812.8 | |
| Jun | 8350.5 | |
| Jul | 8311.5 | |
| Aug | 8573.3 | |
| Sep | 8540.4 | |
| Oct | 10955.6 | |
| Nov | 10142.2 | |
| Dec | 11520.9 | |
| 2020 | Jan | 9517.5 |
| Feb | 9306.9 | |
| Mar | 9108.5 | |
| Apr | 9297.4 | |
| May | 9376.0 | |
| Jun | 9619.7 | |
| Jul | 9597.5 | |
| Aug | 9947.1 | |
| Sep | 10079.8 | |
| Oct | 10251.1 | |
| Nov | 10537.1 | |
| Dec | 11654.1 | |
| 2021 | Jan | 10636.8 |
| Feb | 10642.4 | |
| Mar | 10803.7 | |
| Apr | 11197.9 | |
| May | 11214.1 | |
| Jun | 11070.9 | |
| Jul | 11001.8 | |
| Aug | 10273.7 | |
| Sep | 10737.2 | |
| Oct | 11891.2 | |
| Nov | 11696.9 | |
| Dec | 11475.4 | |
| 2022 | Jan | 9638.9 |
| Feb | 9705.0 |
This is an individual assignment
Submission Requirements
| Penalty for Late Submission |
Supply Chain System Modelling
ASSESSMENT Marking criteria: Coursework A
COURSEWORK 100% of total module.
| % mark | Grade Descriptors These are typical characteristics of the quality of work associated with each grade. The descriptors are illustrative only and for guidance only. They are not comprehensive. | Category |
| 70-100% | Excellent Outstanding; high to very high standard; a high level of critical analysis and evaluation, incisive original thinking; commendable originality; exceptionally well researched; high quality presentation; exceptional clarity of ideas; excellent coherence and logic. Trivial or very minor errors. For this assignment you will need to answer all 5 task questions. Throughout tasks 1-4 you will need to compare, contrast and critically analyse your results and justify your final recommendation. Critically reflect on the mathematical modelling assumptions you have made. Detail carefully how appropriate the model is and give examples of when and where the model might be used. | Distinction |
| 60-69% | Very good A very good standard; a very good level of critical analysis and evaluation; significant originality; well researched; a very good standard of presentation; pleasing clarity of ideas; thoughtful and effective presentation; very good sense of coherence and logic; Minor errors only. You will need to answer all 5 task questions. Throughout tasks 1-4 you will need to compare, contrast and critically analyse your results and justify your final recommendation. Critically reflect on the mathematical modelling assumptions you have made. How appropriate is the model? Give examples of when and where the model might be used. Compare different solutions and make recommendations throughout. | Merit |
| 50-59% | Good A good standard; a fairly good level of critical analysis and evaluation; some evidence of original thinking or originality; quite well researched; a good standard of presentation; ideas generally clear and coherent, some evidence of misunderstandings; some deficiencies in presentation. You will need to answer all 5 task questions. Throughout tasks 1-4 you will need to compare, contrast and critically analyse your results and justify your final recommendation. The solutions are largely correct. Some analysis of the assumptions made. Critical analysis and comparison of results is stated. Perhaps give examples of how the techniques can be applied. | Pass |
| 40-49% | Satisfactory A sound standard of work; a fair level of critical analysis and evaluation; little evidence of original thinking or originality; adequately researched; a sound standard of presentation; ideas fairly clear and coherent, some significant misunderstandings and errors; some weakness in style or presentation but satisfactory overall. You will need to answer most of the question including task 5; your reflections. Some correct solutions seen. Assumptions are considered with an attempt to compare and contrast solutions. Some examples of when and where the technique could be used would be valued. | |
| 35-39% | Unsatisfactory Overall marginally unsatisfactory; some sound aspects but some of the following weaknesses are evident; inadequate critical analysis and evaluation; little evidence of originality; not well researched; standard of presentation unacceptable; ideas unclear and incoherent; some significant errors and misunderstandings. Marginal fail. Most of the questions are not answered. Task 5; your reflection is omitted. Most solutions are incorrect or omitted. Assumptions are considered but little attempt to compare and contrast solutions. Few examples of when and where the technique could be applied. | Marginal Fail |
| 5-34% | Very poor Well below the pass standard; a poor critical analysis and evaluation; no evidence of originality; poorly researched; standard of presentation totally unacceptable; ideas confused and incoherent, some serious misunderstandings and errors. A clear fail well short of the pass standard. Most of the questions are not answered. Task 5; your reflection is omitted. All solutions are incorrect or omitted. Assumptions are not considered and no attempt to compare and contrast solutions. Few examples of when and where the technique. | Fail |
| 1-4% | Nothing of merit Nothing of value is contained in the submitted work. Nil Report grade. | |
| NS | Non-submission No work has been submitted. | |
| Z | Academic offence notation Applies to proven instances of academic offence. |
