Assignment
For the purpose of the assignment, you should assume the role of a consultant who has been commissioned by a small manufacturing company, called MAD Ltd, to build a spreadsheet model for demand forecasting. The Operations Manager of the client organization has prepared an outline requirements specification for the spreadsheet model. This specification is provided below.
Client Spreadsheet Model Requirements Specification
At MAD Ltd we have a portfolio of 600 products, which are classed into five categories according to their value as measured by annual demand and unit cost as shown in the table below.
Products Demand/year (each product) Cost (£) (per unit)
V 1450 45
W1-W10 625 7
X1-X12 110 55
Y1-Y02 25 5
Z1-Z493 10 3
Currently we use simple exponential smoothing to forecast demand on a quarterly basis for all products. However our current forecasts are not as accurate as we would like because forecast error is too large for some products. This is leading to problems such as carrying too much inventory or failing to meet demand.
We would like a spreadsheet tool that has the functionality for generating adaptive demand forecasts from past sales data method. This will include the following capabilities.
- We want to be able to forecast demand when there might be seasonal patterns and/or trends as well as random variation in the sales data.
- We want to be able to use the spreadsheet model for all our products and to update forecasts every quarter as new sales data becomes available to generate updates.
- We also want the spreadsheet model to provide sensible estimates of forecast error, so that we can understand the accuracy and precision of forecasts in relation to actual sales for products over meaningful time windows to support management reporting.
We want to be assured of the validity of the calculations being conducted in the spreadsheet model, therefore we have made available sales data for product X1. This data is shown in the table below.
|
|
|
Past Sales of Product X1
|
|
|
Quarter
|
1
|
2
|
3
|
4
|
|
2013
|
8
|
10
|
7
|
15
|
|
2014
|
15
|
17
|
14
|
28
|
|
2015
|
25
|
26
|
21
|
40
|
|
2016
|
31
|
34
|
28
|
57
|
We would like to be able to have a case study of using the spreadsheet model with Product X1 data to make sure we understand the choices we need to make in choosing model inputs and interpreting the output from the model. We want to be able to check the credibility of analysis. We might also use this example to train other colleagues in using the forecasts generated by the spreadsheet model. We also want to have visibility of the internal calculations in your forecast modelling as well as the code so that they can be verified.
Finally, we would like guidance on how operations staff should use the spreadsheet tool for our full product range and advice on how our IT staff should maintain the spreadsheet model.