Ques 3: Case Study

A small company has commissioned you to develop a useful spreadsheet

modelling tool to support their demand forecasting on a weekly basis. The

tool will be used by the Operations Manager who needs forecasts of

demand for 1-4 weeks ahead to help him plan production. He also needs a

useful way of monitoring the performance of forecasts that he can use in

weekly and monthly management reports since this information will help

inform inventory policies, for example.

Data and Information

To build, test and demonstrate the use of your modelling tool, the

Operations Manager has made available the data in the spreadsheet

"MS924 resit assignment data" and can be downloaded from myplace

page folder "Re-sit assignment 2021"

Task

You should develop a spreadsheet modelling tool that will support the

implementation of an adaptive exponential smoothing method for the

company, and you should include a mechanism for assessing forecast

error and summarizing it appropriately to meet the Operations Manager's

needs.

You should design your spreadsheet so that it can be used by company

staff who have no knowledge of demand forecasting methods, but who

will need to apply your model to make forecasts weekly and for multiple

products. Therefore your modelling tool should have the capability of

being used with multiple product lines and also be dynamically updated as

new demand data becomes available. This has implications not only for

7

updating the forecasts, but also for the windows over which forecast error

performance measures might be used.

You should also use the data provided by the company to test that your

model is working as expected and to illustrate to the user how to use your

spreadsheet model and how interpret the results. The company would

like a copy of your spreadsheet modelling tool (in an excel file), a concise

report (about 2 pages) that provides them with all the critical information

they need to use your model and make sense of the forecasts that it

provides. The spreadsheet modelling tool should be able to be used by

the Operations Manager and also peer reviewed by independent analysts.

In summary our required deliverables are:

1. A spreadsheet modelling tool that meets client requirements

2. A

analysis/interpretation of the data provided.

report that

provides user

guidance and detailed