Outline

Topics covered in this module:

0. Preliminary concepts
   • Spreadsheet modeling / modelling, financial spreadsheeting, financial modelling
   • Microsoft Excel 2016 / Excel 2019 – new features
   •       New charts – Pareto, Waterfall, and Butterfly
   •       New Excel functions, including: IFS, SWITCH, CONCAT, and TEXTJOIN
   • Office 365 Excel (O365xl) continuous updates – monthly channel – more features than Excel 2019
   •       New charts – Map and Funnel
   •       New features – enhancements to PivotTables, PowerPivot, Power BI, and Get & Transform (Power Query)
   •       New Excel functions, including: IFS, SWITCH, CONCAT, and TEXTJOIN

1. Excel interface
   • Fluent ribbon – tabs, groups, items, and contextual tabs
   • Assumed skills – familiarity with items on the Home tab, basic data skills, and ability to create charts
   • Workbook elements – Name box, Formula bar, and Status bar
   • Right click shortcut menus, Shortcut keys
   • The Quick Access Toolbar (QAT), including customisation
   • Dialog boxes (including with tabs)
   • Worksheet elements – Active cell, selection, and current region
   • Excel Help, and Excel specifications
   •  
   • References:
   • A19 gp62-98 – ch1 Introducing Excel
   • W16 gp48-74 – ch1 Introducing Excel

2. Finance example – NPV
   • Calculating net present value (NPV) in spreadsheet table format
   • Excel equation object, and contextual tab
   • Using the Fill handle – copy, fill down or across, fill down adjacent
   • Fill series – numbers, dates, and source lists
   •  
   • References:
   • B14 – sec33.2 Financial functions pp855-863
   • W16 gp68-74 – ch1 – section: Creating your first workbook

3. Entering and editing worksheet data
   • Three layers – bottom layer (formula layer), top layer (cell – value layer), and drawing layer (floating objects – e.g. charts, equations, shapes); layer shortcuts Ctrl + ‘
   • Types of data – numbers, formulas, and text
   • Numbers – integer, floating point; fifteen digit limit; format shortcuts – Currency: Ctrl + Shift + $; Percentage: Ctrl + Shift + %, applied to the decimal; and Date: Ctrl + Shift + #
   • Mathematical operators, Logical operators (return TRUE / FALSE), Text, and Reference operators
   •  
   • References:
   • W16 gp75-96 / A19 gp99-131 – ch2 Entering and editing worksheet data
   • W16 gp97-117 – ch3 Essential worksheet operations / A19 gp99-131 – ch 3 Performing basic worksheet operations
   • W16 gp175-202 – ch6 Worksheet formatting / A19 gp224-275 – ch 5 Formatting worksheets
   • W16 gp 252-260 – ch10 – section: Understanding formula basics / A19 gp351-374 – ch 9 sections: Understanding formula basics, Entering formulas, Editing formulas, and Using cell references in formulas

4. Cells and ranges
   • Definitions: cell cf. range
   • Naming ranges – specifications. Name Box, and the Formulas > Defined Names group
   • The Name Manager – Edit, Delete and column Sort and Filter techniques
   • Selecting contiguous, and non contiguous ranges
   • Naming constants and formulas
   • plus – Cell comments – Insert, Edit, View, and Delete. Excel’s previous Comments are now named Notes (Jan 2019), and new Comments are threaded and include @Mentions
   •  
   • References:
   • W16 gp118-153 – ch4 Working with cells and ranges
   • A19 gp159-204 – ch4 Working with Excel ranges and tables – sections: 4.1 Understanding Excel cells and ranges to 4.5 Adding comments to cells. Notes vs comments are not covered in this edition

References:

A19 – Alexander M, Kusleika R, and J Walkenbach, (2018), Excel 2019 Bible, Wiley [My Books on Google Play (gp) page referencing]
B14 – Benninga S, (2014), Financial Modeling, MIT Press
W16 – Walkenbach J (2015), Excel 2016 Bible, Wiley [My Books on Google Play (gp) page referencing]

The module workbook is restricted access, and available to session participants only.