[SOLVED] Excel VBAMS924 Spreadsheet Modelling & Demand Forecasting

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