MSCI 342
Advanced Spreadsheet Modelling
Objectives
This module builds on the techniques developed during MSCI 242.
The aim is to improve the student’s technical competence, model design skills and VBA programming, to enable effective, powerful and dynamic models to be constructed.
As previously, case studies and real-life applications will often be used to illustrate these concepts, and show how value can be added to models used in management and analytical situations.
Contact
Each week there will be a 1-hr lecture/demonstration session, plus a 2-hr face-to-face practical computer workshop session.
Additional workshops and study tasks will also be made available during the term.
Assessment
Coursework 100%. A single, individual proj
User Notes
Level:
These workshops will assume the student has completed MSCI 242.
As with 242, each workshop leads into the next, in terms of difficulty, and so should always be attempted sequentially.
Students with prior experience of Excel are advised work at their own pace through the workshops and to try some of the additional, more technical aspects at the end of each workshop.
Version:
The workshop is written for the current campus PC version: Excel 2021
The notes are also mostly backwards compatible with Excel 2019/2016. However, the MacBook version of Excel will not be covered, and neither will the web-based version.
Excel 2021 is essentially the same as Excel 365, in respect of layout and functions.
Fonts:
All references to sheet names, menus, cell references, functions, formulas and key/button strokes will be in Tahoma font. All other text instructing you what to do will be in Times New Roman font.
e.g. =SUM(A1:A10) all functions will be in written in capitals
=Sheet1!A1 this is a reference to a cell, on a sheet
click Ctrl + C these are keyboard buttons
click Data → Sort these are menu selections
Mouse clicking:
There are various mouse-clicking and button-pressing operations needed to perform the following tasks. Here’s what they mean.
left-click and hold click the left mouse button and hold it down
left-click and drag click and hold the left mouse button, drag the mouse to another place, then release.
Data → Sort click the Data menu, then click the Sort icon
Keystrokes
Ctrl + C press and hold the Ctrl key then press the letter C
Answers to the Additional Tasks:
Answers and instructions to the additional tasks are included in this document.
However, you should always attempt the tasks on your own first, as this will improve your learning and skills.
Furthermore, a completed version of the Excel file, for each workshop, will also be available on moodle.
Optional Workshops:
These notes are not meant to be a complete, exhaustive set of instructions for Excel. There are many aspects of Excel, VBA and modelling in general that are not included, such as filtering, pivotttables, pivotcharts, power query, mapping, web-versions, the VBA object model… to name just a few.
However, optional workshops are available, and some are included in this document.
If you require any extra workshops then email the tutor at the address on page 1.