Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

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.