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.
