Course Description

Accounting and finance professionals widely acknowledge how technological change is having a profound impact on their industry. The implementation of cloud accounting and artificial intelligence is moving transactional work to computer systems and lower-cost outsourced resources. With tremendous change, comes tremendous opportunity. Technology has enabled businesses to aggregate and access data faster than ever before. It is essential for financial professionals to be dynamic in their skillset. Those who possess the skills to effectively organize, format, manipulate, analyze, and present data will thrive and advance in today’s economy. 

Microsoft Excel is overwhelmingly the preferred analysis tool for financial professionals. In a recent Encoursa poll of nearly 4,000 professionals, over 80% said they use Excel three or more hours per day. This extrapolates to almost 1,000 hours per year! Even though they spend an extensive amount of time in Excel, most accounting and finance professionals are not even scratching the surface of the value it can deliver.   

The problem with most Excel training courses is that they are not tailored specifically for financial professionals. Because Excel is widely used across many professions, training programs typically take a broad educational approach and a lot of the training is irrelevant to those in the profession. As CPAs, we developed this course specifically for other financial professionals. We drew on our experiences across public accounting, client services, corporate finance, and start-up environments, to bring you a thorough yet tailored course featuring the most important Excel tools every financial professional should know. Our goal with this course is simple: 

We want you to walk away with a new set of Excel skills that will enable you to spend less time “crunching the numbers” and more time to bringing value to your clients, co-workers, and stakeholders. 

For a detailed breakout of the topics covered in this course, please refer to the learning objectives section. Please note that this course is designed for those who have access to Microsoft Excel 2010 or newer. The recordings and screenshots in this course are from a 2019 Office 365 version of Excel, but there will be only minor differences when using Excel 2010 or newer.

Learning Objectives
After completing this course, you should be able to:
Learning Activity #1: Excel Navigation and Keyboard Shortcuts

• Identify the layout of an Excel workbook.
• Identify how to customize the ribbon tabs and quick access toolbar.
• Learn highly relevant general, display and navigation keyboard shortcuts.

Learning Activity #2: Formatting and Basic Formulas

• Use arithmetic, comparison, text, and reference operators in formulas.
• Apply alignment and formatting tools to data to make it organized and presentable.
• Apply conditional formatting to a data set.
• Use the SUMIF function to sum data based on a specific criterion.

Learning Activity #3: Formula References, Paste Special, AND/OR Functions

• Identify when to use absolute, mixed, and relative references in formulas.
• Recognize the paste special functionality and operations.
• Incorporate the AND and OR functions into formulas.

Learning Activity #4: IF, Text, and Lookup Functions

• Identify scenarios to apply IF and nested IF statements.
• Use text functions to format strings of text.
• Use lookup functions to establish data relationships and return values.

Learning Activity #5: Formula Errors, Sorting, Filtering, and Subtotaling

• Identify the different types of Excel formula errors and understand their explanations.
• Apply filters and sorting tools to a data set.
• Recognize the subtotal function operations and understand how it applies to filtered and hidden data.

Learning Activity #6: Tables and Pivot Tables

• Convert data into a table and recognize the key advantages of using tables.
• Create a pivot table and use the fields to perform calculations.
• Recognize how to manually add calculated fields to a pivot table.

Learning Activity #7: Other Useful Functions and Printing

• Identify scenarios to apply the PV, FV, NPV, and IRR finance functions.
• Use the SUMIFS function to sum data based on multiple criteria.
• Use the RAND, RANDBETWEEN, and RANK functions on a data set.
• Use the COUNT, COUNTA, COUNTIF, and COUNTIFS functions on a data set.
• Identify how to round numbers using the ROUND function.
• Use the AVERAGE, MEDIAN, MODE, MIN, and MAX numbers on a data set.
• Identify print tools to customize a worksheet for optimal printing.

Learning Activity #8: Graphics and Charts

• Identify how to use shapes to create a process flowchart.
• Understand how to create a chart and customize its features.

Learning Activity #9: Financial Dashboards

• Identify how to create a dynamic chart using pivot table inputs.
• Identify how to create visual filters using slicers.
• Use formula operators to create dynamic commentary.

Learning Activity #10: Macros and External Sources

• Discover how to record, run, and edit a macro.
• Identify how to create a table using external web data that updates dynamically.

Learning Activity #11: What’s New in Excel 2019 and Excel 365

• Identify how to use the XLOOKUP function to perform a lookup on a data set.
• Recognize optimal scenarios to use the TEXTJOIN, IFS, MAX/MIN IFS, and SWITCH functions.
• Identify how to retrieve real-time data using the "Stocks" and "Geography" buttons.

Learning Activity #12: Qualified Assessment Activity

• Complete the “Qualified Assessment Activity” file in preparation for the final assessment questions.

Learning Objectives

• Please refer to the "description" section for the learning objectives broken out by learning activity.

Prerequisites

• A basic understanding of Microsoft Excel and access to Microsoft Excel 2010 or newer.