Power of Excel: Financial Management Badge 2.0

The Power of Excel: Financial Management Badge 2.0 is awarded to individuals who have completed Levels 1 - 3 in a financial based Power of Excel Series and solved an Excel Challenge since January 2021. Attendees have learned a combination of the functions noted below and applied their skills by solving a Challenge to reflect an understanding of visualizing data through the use of Pivot Tables, slicers, and other analytical tools in Excel. Some of the functions that are covered in these Bootcamps include: Level 1: *Recommended experience: no experience required* - Calculating expected portfolio return - SUMPRODUCT - TRANSPOSE - Array functions - The significance of CTRL + SHIFT + ENTER - Calculating arithmetic and geometric average stock returns - Calculating standard deviation - The significance of statistical population (.P) versus statistical sample (.S) functions - Calculating the correlation and covariance of a two-stock portfolio - Introduction to financial modelling and analysis - Working with effective and annual interest rates - EFFECT - NOMINAL - Time value of money (TVM) functions like PMT, FV, and PV - Data tables and sensitivity analysis - VLOOKUP - Tables - Drop down lists - Conditional formatting - COLUMNS - Collapsing rows - Custom conditional formatting - CASE: Construct an amortization schedule to model the life of a home mortgage Level 2: *Recommended experience: Level 2 builds on and uses the skills learned in Level 1* - Construction of a fraud detection engine based on the Benford distribution - IF statements - COUNTIF - LEFT - CHISQ.TEST - OFFSET - LOG - Basic charting - ISTEXT - MATCH - Pivot tables - CASE: Use an A/R transaction list to calculate the individual turnover period on transactions, then rollup these calculations into the average A/R turnover period. Also, calculate bad debt expense Level 3: *Recommended experience: Level 3 builds on and uses the skills learned in Level 2* Introduction to more advanced financial modelling Model structuring and formatting Named arrays from selection Circular referencing Circular modelling of income tax and net income Circular modelling of depreciation and accumulated depreciation Circular modelling of Marketable securities and Revolver SLOPE INTERCEPT Charting linear regressions Regression reports using the Data Analysis ToolPak Introduction to multivariate and nonlinear regression modelling Introduction to modelling options portfolios and analyzing their linear characteristics CASE: Construct a stock portfolio performance dashboard comparing an 8 stock portfolio to the TSX and a comparative savings account. Present the portfolio’s beta, annualized return, and dollar premium return over the market and savings account