MS924 Spreadsheet Modelling & Demand Forecasting
Individual Assignment 2017-8
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
1
In summary our required deliverables are:
- A spreadsheet model that meets our requirements as explained in our specification in the form of an *.xlsm file.
- A voice-over powerpoint presentation report (maximum 12 slides) that includes (a) guidance for using and maintaining the spreadsheet model (b) analysis/interpretation of product X1 data in a form that is understandable to our managers and (c) a one-slide statement of your main learning point from doing this assignment.
- Documentation of the VBA code used within the model in the form of a *.txt file.
We require the final deliverable by 7 January 2018.
Learning Objectives and Assessment Guidance
This assignment seeks to address the core learning outcomes for the class. That is:
- Create simple but appropriately organised spreadsheet models for complex problems;
- Use the spreadsheet to support traditional operational research techniques such as forecasting;
- Construct and interpret forecasts using smoothing methods and decompositional analysis;
- Compute and interpret forecast errors to track accuracy of forecasts;
- Understand the nature of effective forecasting systems;
- Understand basic principles of computer programming;
- Understand the basic elements of VBA for developing macros within Excel.
Please note: As well as speaking with tutors and lecturers in class and in office hours, general questions can be posted to the MS924 discussion forum where students as well as staff can both ask questions and post responses.
For the final submission, you should submit a copy of your spreadsheet model (in an excel file) together with your report (in a voice-over powerpoint presentation file) and the VBA code that is used within the spreadsheet model (in a txt file). Files should be uploaded to the MS924 Myplace site.
For the VBA code text file, this should be a single file that contains all user defined macros, user defined functions and user defined procures that you used to develop your spreadsheet model. This file should have the following structure.
- A header at the beginning of the file with the following information: the developers’ names and registration numbers; a brief description of purposes of the program.
- Inline comment: all macros, functions and procures should start with a brief comment describing the functional characteristics of it (what it does).
Your spreadsheet model and supporting report should address the requirements of MAD Ltd and we advise that you should:
- Describe how you made demand forecasts for the example product data, including describing the patterns in past sales in a language that will be meaningful to operational staff, explaining why your demand forecast method is appropriate and why you selected particular parameter values, as well as showing the forecasts generated by your model and a discussion of their accuracy.
- Note 1 – There is no need to implement all demand forecasting methods introduced in class. The client requires an adaptive method and one that is general enough to model different types of common patterns that might be expected in sales data, such as the Holt-Winters method.
- Note 2 – It can be useful to show a client a comparison of the new modelling approach in relation to the existing one so that the change in performance and user inputs can be appreciated. Hence we also advise that you implement simple exponential smoothing for Product X and compare its forecasts with those you generated under your proposed model. This discussion can form part of your analysis of Product X1 given in your powerpoint presentation report.
2
- Provide effective guidance on using your spreadsheet model, including explanations of data inputs and outputs as well, navigating and maintaining your tool.
- State the assumptions underpinning your spreadsheet model and so advise when it will be appropriate for demand forecasting and when it will be insufficient and other methods might be required. That is, be open about what capability you have added and when it will be useful and when not. By acknowledging limitations you can make suggestions for the next generation of the spreadsheet model.
Within the requirements set, there is room for creativity in the approach of each analyst to this problem.
You should take care to maintain version control of your files, make regular back-ups, conduct appropriate testing, and include explanation (e.g. code comments within the VBA editor for yourself, as well as user, as you build the macros).
Since the internal model will be verified by technical peers (i.e. the class lecturers) it is important that the assessors are able to see the calculations and also the VBA code. We recommend that your calculations are executed within the spreadsheet, as opposed to the macros, therefore we recommend that you develop your working spreadsheet model using the data provided by the company and then build your VBA functionality.
The assignment feedback form is shown on the following page. This is the criteria and marks breakdown that will be used in assessing the spreadsheet, report and VBA code. These criteria relate to the general class outcomes, although some have been rephrased to make them more understandable in the context of this assignment problem.
Marks and feedback should be returned to you by Monday 29 January 2018.
3
Assessment Criteria – Blank Feedback Form for Information
Marks Assignment element
| Create a simple but appropriately organised spreadsheet model | 20 | Excel spreadsheet |
| for demand forecasting that is easy to use and maintain. | ||
| Construct demand forecasts, such as adaptive Holt-Winters, and | 30 | Excel spreadsheet |
| simple exponential smoothing, and calculate appropriate error | ||
| measures of forecast accuracy | ||
| Demonstrate an understanding of basic principles of computer | 20 | VBA code |
| programming, especially the basic elements of VBA for | ||
| developing macros within Excel | ||
| Demonstrate a sound understanding of demand forecasting | 10 | Voice-over |
| modelling through appropriate interpretation of your analysis | presentation report | |
| Provide guidance for your spreadsheet model | 20 | Voice-over |
| presentation report | ||
| Total | 100 | |
| General comments, including suggestions for improvement | ||
4