Management Science. Academic Year

1

Management Science

Spreadsheet Modelling and Demand Forecasting

Academic Year 2015-16

MS924

Ashwin Arulselvan: [email protected]

Lesley Walls: [email protected]

1. Rationale (including Peer/External comment)

Spreadsheets provide an easily accessible tool to support business modelling and analysis.

Spreadsheets are a popular modelling support tool because they facilitate easy manipulation of data and thus greatly reduce the time necessary to set-up and modify models. Analysis carried out in a spreadsheet is easily transferred into other software packages or presented visually, so that it provides a good platform for report writing.

2. Class Description

This module will demonstrate how spreadsheets can be used to support the analytical techniques whose theory is taught on other modules, for example, forecasting, simulation, optimisation, data analysis, as well as being used to support technical report writing. Most prominence is given to demand forecasting and students will be introduced to different types of forecasting technique for short term smoothing through to decompositional analysis. These methods will be implemented using spreadsheet models. This module also extends the basics of using a spreadsheet to explore the

principles of effective computer programming through the development of macros to automate the functionality available within spreadsheets.

3. Class Aims

The aim of this module is to develop an understanding of how spreadsheets can be used to effectively support business modelling, particularly in relation to the development of demand forecasting systems for which students will be introduced to the toolbox of commonly used techniques.

2

4. Learning Outcomes

a. Subject specific knowledge and skills

 Create simple but appropriately organised spreadsheet models for complex problems;

 Use the spreadsheet to support traditional operational research techniques such as forecasting, optimisation and simulation;

 Describe the main categories of forecasting technique, their data requirements and applicability to different operational situations;

 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. b. Cognitive abilities and non-subject specific skills

• Further develop ability to see patterns in numerical data;

• Develop students ability to express modelling process algorithmically;

• Develop student’s ability to express problems in forms conducive for the software support available.

5. Teaching and Learning Methods

This class is delivered through computer laboratory based activities, supported by appropriate e-learning material, exercise, reading and cases as well as some supporting lectures.

6. Indicative Structure

An introductory session will provide an overview of the motivation for the class and the linkages between the three main themes, which are programming, spreadsheets and demand forecasting. Each theme will be covered in depth and integrated through activities and a major assignment. The first phase will introduce spreadsheet modelling through consideration of some standard OR problems (e.g. optimisation, discrete event simulation) and show how to develop more advanced spreadsheet models by adding customised VBA programs. The second phase of demand forecasting will consider the role of forecasting in operations and a selection of forecasting techniques from simple smoothing methods through to automatic forecasting systems. The third phase will develop the basics of programming with particular emphasis on VBA and examine how these can be used to build more complex spreadsheet models. A class plan for topics and associated reading is given on page 5 of this outline.

3

7. Reading List

Makridakis, S., Wheelwright, S., and Hyndman, R (1998) Forecasting: Methods and Applications (Third Edition), John Wiley – very good basic explanations for all.

Hyndman R and Athanasopoulos, G (2014) Forecasting Methods and Principles, online open access text books, https://www.otexts.org/fpp - good if your are comfortable with more mathematical

representation of forecasting.

Walkenbach J and Pieterse JK (2007) Excel 2007 VBA Programming For Dummies, ISBN: 978-0-470-04674-6

8. Resources (staff/library/computing/a-v/accommodation)

Excel 2007 will be used within the computer laboratories, which are in GH6.34

9. Assessment including Reassessment

Class tests will form part of the assessment for each theme in order to provide feedback on the basic concepts. An individual assignment will require a spreadsheet tool to be developed for a forecasting system and will require students to integrate learning from all three themes. The class tests are worth 30% of the marks for the class (i.e. 10% for each class test) and the assignment is worth 70%.

Test 1 will be in week 7 and tests 2 &3 will be in week 11. The assignment will be available in week 3 and is due on 12 January 2015.

10. Indicative Hours

Activity Hours

Lecturer led session, including computer labs

20

Class test 2

Assignment 30

Pre-reading 12

Individual study, including computer based work

4

11. Matrix of Learning Outcomes/Assessment

subject specific knowledge and skills teaching/ learning method

evidence of outcome Create simple but appropriately organised

spreadsheet models for complex problems

C,dr Ep

Use the spreadsheet to support traditional operational research techniques such as optimisation and simulation

C,dr Ep

Understand basic principles of computer programming

L,c,dr Ep, ct Understand the basic elements of VBA for developing

macros within Excel

C,dr Ep, ct Create simple but appropriately organised

spreadsheet models for complex problems

C,dr Ep

Describe the main categories of forecasting technique, their data requirements and applicability to different operational situations;

L,c,dr, ir Ep, ct

Construct and interpret forecasts using smoothing methods and decompositional analysis;

L,c,dr, ir Ep, ct Compute and interpret forecast errors to track

accuracy of forecasts;

L,c,dr, ir Ep, ct Understand the nature of effective forecasting

systems;

L,c,dr, ir, cd Ep, ct

cognitive abilities and non-subject specific skills Further develop ability to see patterns in numerical data;

L,c,dr Ep, ct Develop students ability to express modelling

process algorithmically;

L,c,dr Ep, ct Develop students ability to express problems in

forms conducive for the software support available.

L,c,dr Ep

l – lecture led session, c – computer lab session, dr – directed reading, ir – independent

reading, rp- research papers, ep – empirical project, gp- group presentation, e – exam,

cd – class discussion, ct – class test

5

Schedule for 2014-15

Week Day

/Time Date Location Topic Lead

Tutor Myplace E-notes to be read BEFORE named lab Computer Lab Exercises 1 Fri 9-11 3 Oct GH898 Introduction and scene setting

Lesley Introducing demand

forecasting None

2 Mon

10-12 6 Oct GH624

Spreadsheet

basics Reb Introducing excel 3 Mon

10-12 13 Oct GH634 Forecasting Lesley

Simple smoothing methods Simple smoothing methods exercises (embedded in e-notes) 4 Mon

10-12 20 Oct GH634 Forecasting Lesley Forecast error and measurement

Forecast error and measurement exercises

(embedded in e-notes) 5

Mon

10-12 27 Oct GH634 Forecasting Lesley Holt-Winters forecasting Holt-Winters forecasting exercises (embedded in e-notes) 6 Mon

10-12 3 Nov GH634 Spreadsheets Reb Designing a spreadsheet 7

Mon

10-12 10 Nov GH634 Class test 1

Spreadsheets Reb

Classical OR problems and solver 8

Mon

10-12 17 Nov GH634 Spreadsheets Reb Simulations and macros 9

Mon

10-12 24 Nov GH634 VBA Reb

10

Mon

10-12 1 Dec GH634 VBA Reb

11

Mon

10-12 8 Dec GH634 Class tests