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 spreadshee t 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//yyear (eeach product))
Cost (££)) (pper 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 to o 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 wi
ll include the following capabilities..
We want to be able to forecast demand when there might be
seasonal patterns and//oor 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 up
date 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 actu
al 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 i s 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 generat ed 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::
1.. A spreadsheet model that meets our requirements as explained in our specification in the form of an *..xxlsm fil e..
2.. A voice – over powerpoint presentation report (mmaximum 12 slides)) that includes (aa)) guidance for using and maintaining the spreadsheet model (bb)) analysis//iinterpretation of product X1 data in a form that is understandable to our managers and (cc)) a one – sli de 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 *..ttxt 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 forecasti ng;;
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 com puter 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 stud ents as well as staff can both ask questions and post responses..
For the final submission,, you should submit a copy of your spreadsheet model (iin an excel file)) together with your report (iin a voice – over powerpoint presentation file)) and the VBA code that is used within the spreadsheet model (iin 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 tha t 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 progr
am..
2))
Inline comment:: all macros,, functions and procures should start with a brief comment
describing the functional characteristics of it (wwhat 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 why your demand forecast method is appropriate an d 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 rela tion 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
2.. Provide effective guidance on using your spreadsheet model,, including explanations of data inputs and outputs as well,, navigating and maintainin g 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 a dded 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 (ee..gg.. code comments within the VBA editor for yourself,, as well as user,, as you build the macros))..
Since the int ernal model will be verified by technical peers (ii..ee.. 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 break down 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 acc uracy
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 t hrough 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