Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
FNCE90045
Financial Spreadsheeting
SUBJECT GUIDE
Subject Outline
Introduction
Welcome to FNCE90045 Financial Spreadsheeting
Financial spreadsheeting (financial modelling) provides a practical application of
Microsoft Excel to business spreadsheet tasks. Office 365 (Excel) Windows version
is the base application for FNCE90045 Financial Spreadsheeting
Subject Overview and Aims
The overall aim of this subject is to give students an in-depth knowledge of
Microsoft Excel in the context of a business / finance environment.
The course is taught in two parts. In the first part (weeks one to six), the focus in
on Financial Applications using Excel: basic spreadsheeting skills, functions and the
function wizard, worksheet formatting and graphics. Financial problem solving tools:
Solver, Goalseek and the Analysis Toolpak. Data management tools, subtotals,
consolidation, sorting, filtering and pivot tables.
The second part of the course (week’s seven to twelve) focuses on Financial
Applications and programming Excel in Visual Basic for Applications V7.1 (VBA).
Programming principles with applications in finance: object orientated programming,
variables and control structures, writing Excel financial functions and subroutines,
and creating add-ins. Communicating with the user of financial applications through
message boxes, input boxes and dialogue boxes. Conquering complicated financial
spreadsheet challenges.
An overall theme is data – collection and shaping, transformation and analysis,
presentation and reporting.
Learning Outcomes
Subject Objectives and Generic Skills
3
Eligibility and Requirements
Academic Staff Contact Details
Please see the subject LMS site for full contact details of the teaching staff in this
subject.
Email Protocol
Please note that we are only able to respond to student emails coming from a
University email address. Please do not use personal email addresses such as
Yahoo, Hotmail or even business email addresses. Emails from non-University email
addresses may be filtered by the University’s spam filter, which means that we may
not receive your email.
While academic staff endeavor to address queries received via email, it is more
appropriate to resolve substantive questions during lectures and tutorials and/or
during normal consultation hours. With this in mind, we encourage students to attend
all lectures and tutorials and to familiarise themselves with the consultation hours
offered by the lecturers and tutors in this subject.
4
Classes
Class Times
• Please refer to the Student Portal for the latest class times and venues.
• Classes:
• FNCE90045/U/1/SM2/L01/01
o #1 Thursday (5:15 PM to 8:15 PM) – Online via Zoom
o This class is recorded.
• FNCE90045/U/1/SM2/L01/02
o #2 Friday (10:00 PM – 1:00 PM) – PAR The Spot – 6015
o On-campus version is recorded.
o Friday 23 September 2022 is a University Holiday – no class
Lecture Participation Requirements
• Each student is required to be familiar with the required reading BEFORE
each class
• Classes include demonstration of spreadsheet techniques
Class Schedule
Week Topic
The Excel section revolves around the Stock Analyser project. Data
collection, preparation and collation. Summarising the data, analysis, and
presentation - all within a dynamic framework
1 Spreadsheet modeling
0. Preliminary concepts
1. Spreadsheets on Collins St / Pitt St / Wall St / Century …
2. Dates and dollars
3. Finding your way
About spreadsheeting
0. Preliminary concepts
1. Excel interface
2. Exercise - financial
3. Entering and editing worksheet data
5
4. Cells and ranges
2 Spreadsheet tables, formulas and functions
0. Preliminary concepts
1. Tables
2. Formulas and functions
3. Functions – financial
4. Array – formulas, and constants
3 Spreadsheet functions, and decision management
0. Preliminary concepts
1. Functions – text, date and time
2. Functions – logical
3. Functions – lookup and reference
4. Functions – financial data analysis
4 Spreadsheet data organization
0. Preliminary concepts
1. Data import – financial, Gat & Transform, and an
introduction to Power Query and the M language
2. Data summary - financial
3. Create a data selector panel
4. Navigating a data range
5 The spreadsheet Stock Analyser project
0. Preliminary concepts
1. Analyser vectors
2. Multiple dynamic vectors and charts
3. Improving spreadsheet accuracy
4. Matrices – applications in finance
6 Spreadsheets for business – finance tools
0. Preliminary concepts
1. What-if analysis
2. Random number generators (RNG)
3. Random numbers in business and finance
4. Pivot Tables, and an introduction to Power Pivot and DAX
The coding section – using VBA, is designed to provide skills to make
repetitive tasks less arduous and challenging tasks less difficult.
6
7 Unleashing the power of the financial spreadsheet.
0. Preliminary concepts
1. Getting started with VBA
2. The Visual Basic Editor (VBE)
3. Writing code – sub procedures
4. Controlling code execution – loops and control structures.
8 Spreadsheet programming basics
0. Preliminary concepts
1. Functions: built-in and custom
2. Custom functions (1)
3. VBA arrays
4. Custom functions (2), plus an introduction to R and BERT
9 Communicating with the spreadsheet user
0. Preliminary concepts
1. Communicating with the spreadsheet user (1)
2. Message boxes
3. Input boxes
4. Inbuilt dialog boxes
10 Spreadsheet UserForms
0. Preliminary concepts
1. The option priced user form
2. User Forms
3. Input boxes
4. User form code
11 Spreadsheet controls
0. Preliminary concepts
1. The Excel Controls Group
2. Worksheet controls
3. Macros that run automatically
4. xlWings and Python
[End of semester exam overview.]
12 Spreadsheets – final components
0. Preliminary
1. Workbooks and ranges
7
2. Solver (Excel)
3. Monte Carlo simulation with VBA
4. Summary
A more detailed reading list will be provided with class materials available
through the LMS.
Session material available at https://excelatfinance.com can be accessed via
LOGIN > XLF: FINANCIAL MODELING with user name: FNCE90045, and
password xlf#90045onLine
Important note: Some adjustments to the class schedule may occur during
semester
Using Lecture Capture
Audio and video recordings of lectures delivered in this subject will be made available
for on the Friday of each teaching week. Most classes are split into a number of
separate files each week
You can access recorded lectures by clicking on the Lecture Recordings (or similar)
menu item on the LMS page for this subject.
Private Tutoring Services
Faculty has become increasingly concerned about the existence of a number of
private tutoring services operating in Melbourne that heavily target University of
Melbourne students enrolled in FBE subjects.
Students are urged to show caution and exercise their judgement if they are
considering using any of these services, and to please take note of the following:
Any claim by any of these businesses that they have a “special” or “collaborative” or
“partnership” style relationship with the University or Faculty is false and misleading.
Any claim by a private tutoring service that they are in possession of, or can supply
you with, forthcoming University exam or assignment questions or “insider” or
“exclusive” information is also false and misleading.
The University has no relationship whatsoever with any of these services and takes
these claims very seriously as they threaten to damage the University’s reputation
and undermine its independence.
It is also not appropriate for students to provide course materials (including
University curricula, reading materials, exam and assignment questions and
8
answers) to operators of these businesses for the purposes of allowing them to
conduct commercial tutoring activities. Doing so may amount to misconduct and will
be taken seriously. Those materials contain intellectual property owned or controlled
by the University.
We encourage you to bring to the attention of Faculty staff any behaviour or activity
that is not aligned with University expectations or policy as outlined above.