Module Descriptors
SPREADSHEET MODELLING TECHNIQUES
MATH40235
Key Facts
Faculty of Computing, Engineering and Sciences
Level 4
15 credits
Contact
Leader: Sarah Easton
Email: S.J.Easton@staffs.ac.uk
Hours of Study
Scheduled Learning and Teaching Activities: 36
Independent Study Hours: 114
Total Learning Hours: 150
Assessment
- CLASS-TEST weighted at 50%
- CLASS-TEST - SECOND CLASS TEST weighted at 50%
Module Details
Module Texts
For background reading:
Excel 2007 Bible, John Walkenbach, Wiley, Jan 2007, ISBN: 0470044039
Excel 2007 Bible, John Walkenbach, Wiley, Jan 2007, ISBN: 0470044039
Module Special Admissions Requirements
This module is only available to students on the Mathematics and Applied Statistics award.
Module Additional Assessment Details
1. Lab Based Test 1 requiring the prior preparation (with the option to work in a small group) of a spreadsheet model for use in the test: One hour and 15 minutes weighted 50% (Learning Outcomes 1, 2)
2. Lab Based Test 2: One hour and 15 minutes weighted 50% (Learning Outcomes 2, 3)
2. Lab Based Test 2: One hour and 15 minutes weighted 50% (Learning Outcomes 2, 3)
Module Indicative Content
This module will utilise some of the advanced spreadsheet functions and features available to aid modelling/analysis/decision making for quantity based problems. Those met will include amongst others: a range of functions to aid modelling, lookup functions for value selection, array formulae for criteria based analysis, customising worksheet interfaces, solver for problem optimisation, sensitivity analysis using scenarios and data tables, and pivot tables for cross tabulations.
Students will also be introduced to VBA macros together with some basic tools of programming enabling the automation of processes within a spreadsheet. Principles met will include: recording macros, editing macros, use of message boxes and input boxes to allow interaction with the user, if statements, for each and do loops, an introduction to forms, and the development of personalised functions.
Students will also be introduced to VBA macros together with some basic tools of programming enabling the automation of processes within a spreadsheet. Principles met will include: recording macros, editing macros, use of message boxes and input boxes to allow interaction with the user, if statements, for each and do loops, an introduction to forms, and the development of personalised functions.
Module Resources
Excel
Module Learning Strategies
Students will experience 24 hours of computer based in-class practical sessions (2 hours per week).
There will be a further 12 hour of class contact (1 hour per week) consisting some weeks of a lecture addressing issues relevant to spreadsheet modelling in general and/or the content of the practical sessions, and in the remaining weeks of a practical surgery in which students can access additional lab based contact time if required. Independent study time will be spent completing the worksheets, further practising the methods met in class, and preparing for assessments.
There will be a further 12 hour of class contact (1 hour per week) consisting some weeks of a lecture addressing issues relevant to spreadsheet modelling in general and/or the content of the practical sessions, and in the remaining weeks of a practical surgery in which students can access additional lab based contact time if required. Independent study time will be spent completing the worksheets, further practising the methods met in class, and preparing for assessments.