[SOLVED] MS924 Spreadsheet Modelling & Demand Forecasting

MS924 Spreadsheet Modelling & Demand Forecasting
Individual Assignment 2019-20
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 V W1-W10 X1-X12 Y1-Y02 Z1-Z493
Demand/year (each product) 1550
650
120
30
15
Cost () (per unit) 40
5
50
5
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. 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.
Quarter
2014
2015
2016
2017
1 2 3
8 10 7
4
15 28 40 57
Past Sales of Product X1
15 17 25 26 31 34
14 21 28
using the spreadsheet model with Product X1 data to
We would like to be able to have a case study of
make sure we understand the choices we need to make in choosing model inputs and interpreting the
1

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
In summary our required deliverables are:
1. A spreadsheet model that meets our requirements as explained in our specification in the form of an *.xlsm file.
2. A written report (maximum 12 pages) 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 statement of your main learning point from doing this assignment.
3. Documentation of the VBA code used within the model in the form of a *.txt file.
We require the final deliverable by Monday 16th December 2019 (12:00 midday)
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 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 procedures that you used to develop your spreadsheet model. This file should have the following structure.
1) 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.
2) Inline comment: all macros, functions and procedures 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:
1. 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
2

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.
a. 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.
b. 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 report.
2. Provide effective guidance on using your spreadsheet model, including explanations of data inputs and outputs, as well as guidance on navigating and maintaining your tool.
3. 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 Thursday 16th January 2020.
3

Marks
Assignment element
20
Excel spreadsheet
30
Excel spreadsheet
20
VBA code
10
Report
20
Report
100
Assessment Criteria Blank Feedback Form for Information
Create a simple but appropriately organised spreadsheet model for demand forecasting that is easy to use and maintain.
Construct demand forecasts, such as adaptive Holt-Winters, and simple exponential smoothing, and calculate appropriate error measures of forecast accuracy
Demonstrate an understanding of basic principles of computer programming, especially the basic elements of VBA for developing macros within Excel
Demonstrate a sound understanding of demand forecasting modelling through appropriate interpretation of your analysis
Provide guidance for your spreadsheet model
T otal
General comments, including suggestions for improvement
4