无代写-FNCE90045
时间:2022-10-30
FNCE90045
Financial Spreadsheeting
SUBJECT GUIDE
Semester 2, 2022
Prepared by
Dr Ian O’Connor, CPA
email: ianpo@unimelb.edu.au
Department of Finance
Melbourne Business School
2
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
To view the subject objectives and the generic skills you will develop through
successful completion of this subject, please see the University Handbook:
https://handbook.unimelb.edu.au/view/2022/FNCE90045
3
Eligibility and Requirements
To view the eligibility and requirements, including prerequisites, corequisites,
recommended background knowledge and core participation requirements for this
subject, please see the University Handbook:
https://handbook.unimelb.edu.au/2022/subjects/FNCE90045/eligibility-and-requirements
Academic Staff Contact Details
Please see the subject LMS site for full contact details of the teaching staff in this
subject.
Subject Coordinator Contact Details
Name: Ian O’Connor
Email: ianpo@unimelb.edu.au
Phone: +61 3 8344 6812 (WFH)
Consultation Hours: Thursday 3:00 to 4:00 (via Zoom) Friday 9:30 to 10:00 (on
campus)
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. All correspondence relating to this subject will only be sent to
your University email address. Note that you must first activate your University email
address before you can send or receive emails at that address. You can activate your
email account at this link: http://accounts.unimelb.edu.au/.
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.
Assessment
Assessment Overview
Your assessment for this subject comprises the following:
Assessment Task Due Weighting
Assignment 1 (group – 3 to 5
members - self formed)
Friday 16th Sep
2022. (week 8)
25%
Assignment 2 (group - 3 to 5
members – self formed)
Friday 21st Oct 2022
(week 12)
25%
End-of-semester exam 2 hour
(open book)
Assessment period 50%
Assignment 1: covers material in classes 1 to 6. The assignment will be made
available in week 5 of semester. Self selection group membership must be
completed by the end of week 3 (Friday 12th Aug 2022).
Assignment 2: covers material in classes 7 to 12. The assignment will be made
available in week 8 of semester, and class material for week 11 and 12 will be
available at the end of week 9. Self selection group membership must be completed
by the end of week 8 (Friday 16th Sep 2022).
Marks will be equally distributed between group members, conditional on the
contribution of each group member. Groups will / can be formed across any of the
classes, and students from any degree.
Students are expected to attempt all relevant non-assessable exercises before
completion of the assignment. The completed assignments must be based on
material covered in the subject as taught during the semester.
End of semester examination: covers material from all classes, plus material from
assignments 1 and 2.
9
Assignment Submission
Assignment submission is via the LMS Assignment Submission link for all written
assignments. Please refer to the Turnitin section of the LMS website via for detailed
submission instructions if needed (http://go.unimelb.edu.au/zax6).
Please note that you are required to keep a copy of your assignment after it has
been submitted as you must be able to produce a copy of your assignment at the
request of teaching staff at any time after the submission due date.
Assignment Extension
Requests for an assignment extension should be submitted here:
http://go.unimelb.edu.au/yh9n.
Before completing this form, please read the Assignment Extension Policy, which
can be found at: Assessment and Results Policy (MPF1326) : Policy : The University
of Melbourne (unimelb.edu.au)
Subject Resources
Useful Textbook
Alexander M, Kusleika R, and J Walkenbach, 2018, Microsoft Office Excel 2019
Bible, Wiley Publishing
Other Recommended Textbooks
Materials will be made available on the LMS for each class.
Excel texts
There are a wide variety of text books available for Excel and Excel VBA. Most texts
from Excel 2010 onwards are useful. These textbooks include the Ribbon interface.
Texts by Mr Excel. See: https://www.mrexcel.com/
Jelen B, 2021, MrExcel 2021: Unmasking Excel, Holy Macro! Books.
Texts originated by Mr Spreadsheets. https://spreadsheetpage.com/john-
walkenbach-books/
Alexander M, Kuseika R and J Walkenbach, 2019, Excel 2019 Bible, Wiley
Publishing.
Alexander M and R Kuseika, 2019, Microsoft Office Excel 2019 Power
Programming with VBA, Wiley Publishing.
Finance texts using Excel
10
Benninga S and T Mofkadi, 2022, Financial Modeling: Uses Excel, MIT Press
(forthcoming)
Benninga S and T Mofkadi, 2017, Principles of Finance with Excel, Oxford
University Press.
Academic Integrity
Academic Honesty
The University maintains high academic standards in its courses and subjects and
expects students to conduct themselves in a manner which is fair, honest and
consistent with the principles of academic integrity, particularly when undertaking
assessment and research. http://academicintegrity.unimelb.edu.au/
Referencing
Each source used for a written piece of assessment must be referenced. This is to
acknowledge that your material is not based entirely on your own ideas, but is
based, in part, on the ideas, information, and evidence of others. This is desirable as
you are attending University in order to learn from others.
You will be required to use the APA system or Harvard System of referencing. The
library has prepared a website to help students correctly reference:
http://www.library.unimelb.edu.au/recite
It is important that all material you present for assessment is referenced correctly.
Material that has not been referenced correctly may be considered to be plagiarised,
and as such may be penalised. We will also look for evidence that material included
in the bibliography has been used in the assignment.
The Academic Skills Unit has produced resources to assist students with referencing
https://services.unimelb.edu.au/academicskills/undergrads/top_resources
The Library also provides advice on referencing: http://library.unimelb.edu.au/cite
University Services
Timetable
MyTimetable is a class timetabling system that creates individual timetables for
students based on submitted class preferences, ensuring everyone has an equitable
opportunity of getting their preferred class timetable. You will use this system to
create your class timetable prior to each study period.
By following a preference-based model, students who have other commitments,
such as employment or carer responsibilities, or who are returning or living overseas
11
during the timetabling period, aren’t disadvantaged by their limited availability. When
allocating class timetables, MyTimetable also takes into consideration factors such
as class size limits and potential clashes to ensure all students are equally
accommodated. Further information is available on the web at
https://students.unimelb.edu.au/admin/class-timetable
Stop 1: Connecting Students and Services
Stop 1 is here to provide you with a range of support services throughout your
university degree, from help with enrolment, administration and wellbeing to advice
on building your skills and experiences. https://students.unimelb.edu.au/stop1
Academic Skills
Academic Skills offers a range of workshops and resources to help you with study
skills including researching, writing and referencing, presentation skills and preparing
for exams. Visit their website via http://services.unimelb.edu.au/academicskills.
Service Finder
The University of Melbourne offers one of the most comprehensive student support
networks in Australia. Use this site to locate a wide range of services
http://services.unimelb.edu.au/finder
Student Counselling
Students attend counselling to talk about personal, emotional, or mental health
issues which might be affecting their study and life. The University’s Counselling and
Psychological Services (CAPS) provides free, confidential, short-term professional
counselling to currently enrolled students and staff.
https://services.unimelb.edu.au/counsel/individual
Student Equity and Disability Support
Student Equity and Disability Support provides services for students who need
ongoing support with their studies. They understand that adjustments to learning and
assessment are sometimes required to allow all students to reach their full potential.
Learn more about the services provided, how to find support and how to register for
assistance. http://services.unimelb.edu.au/student-equity
University of Melbourne Library Services
As well as holding an extensive collection of books, ebooks, digital media and
periodicals, library staff provide research guidance and support for students.
http://library.unimelb.edu.au/
These Business and Economics Library Guides have been designed specifically for
Faculty of Business and Economics staff and students.
http://unimelb.libguides.com/sb.php?subject_id=80310
12
Policy
Alternative Exam Arrangements (AEA)
The definition of and eligibility requirements for alternative exam arrangements (AEA)
can be found via http://students.unimelb.edu.au/admin/alternate .
Assessment and Results Policy
The University’s assessment policy provides a framework for the design, delivery
and implementation of assessment of students in award and non-award courses and
subjects. Assessment is designed to contribute to high quality learning by students,
and to allow for quality assurance and the maintenance of high academic standards.
https://policy.unimelb.edu.au/MPF1326
Assignment Extension
Requests for an assignment extension should be submitted here:
http://go.unimelb.edu.au/yh9n.
Before completing this form, please read the Assignment Extension Policy, which can
be found at: http://policy.unimelb.edu.au/MPF1326#section-4.37
Exam Policy
The University requires that you are available for the entire examination period.
Please see the University's Principal Dates via
https://www.unimelb.edu.au/dates#2021 for the full annual calendar. Supplementary
exams will not be provided in cases of absence during the examination period
unless the absence is due to serious illness or other serious circumstances and a
Special Consideration application is submitted and approved.
Information on Calculators in Examinations
Effective from 1 January 2017, the approved calculator for all subjects is the Casio
FX82 (any suffix). No equivalent models of calculators will be permitted in exams.
You are required to purchase your own calculator and are responsible for ensuring
your calculator is in good working order with fresh batteries.
Plagiarism and Collusion
Plagiarism (failure to cite your sources correctly and completely) and collusion
(unauthorised collaboration with another person to prepare an assessment task) are
considered academic misconduct and attract severe penalties. More information is
available on the University’s Academic Integrity website via
http://go.unimelb.edu.au/rha6.
13
Special Consideration
As a student, you may experience extraordinary or unusual circumstances, or
ongoing circumstances that adversely affect your academic performance. The
University has policies in place to support students who are experiencing academic
disadvantage. For more information, visit
http://students.unimelb.edu.au/admin/special.