Must Know Excel Functions for CPAs (Course Id 600)

• Who is this course for?
  This course is designed for CPAs and other professionals seeking Continuing Professional Education (CPE) credits who want to improve their Excel skills for increased productivity and accuracy.

• What is this course about or what problem does this course solve?
  The course teaches essential Excel functions that help CPAs efficiently manage and analyze data, reducing errors and increasing effectiveness in tasks involving calculations, logic, text manipulation, lookups, and financial modeling.

• How can the knowledge from this course be used?
  Participants can apply the knowledge to streamline spreadsheet workflows, perform complex calculations, automate data analysis, and create more accurate and efficient Excel-based reports and tools.

• Why is this course important to a CPA, Accountant, or IRS Enrolled Agent?
  Mastering Excel functions is crucial for CPAs to handle large datasets, perform financial analyses, and make data-driven decisions with speed and accuracy, all of which are core competencies in accounting work.

• When is this course relevant or timely?
  The course is especially relevant in today’s data-driven work environments and for any CPA who relies on Excel for financial reporting, client service, or internal operations.

• How is a course like this consumed or used?
  This is a self-study course delivered in text format with step-by-step instructions, practice exercises, and a final exam that must be passed with a score of 70% or higher to earn 6 CPE credits.

This course explores functions in Excel that CPAs need to use to enhance productivity and ensure accurate answers when working with data.  Functions are built-in, preset formulas in the Excel program that calculate certain values or accomplish a certain calculation goal.  Functions are essential to CPAs as they provide a simplified user interface to work with complex formulas.  This course covers basic functions and then goes into logic functions, text functions, lookup functions and financial functions. This course provides step by step instruction and Excel practice exercises.

Functions discussed include: Database functions such as DSUM and DCOUNT; Lookup functions such as XLOOKUP and INDEX MATCH, Logic functions such as IF OR,  IFERROR;  Text functions such as CONCAT, LEN,TEXTSPLIT and TEXTBEFORE.  A lot of time is given to financial functions with multiple little case studies as it delves into PV,PMT,NPER FV and RATE. Basically, every important function you need to know!

NASBA QAS - Text - NASBA Registry - 600

Course Learning Objectives

• Recognize functions used to sum and count data and their uses
  
• Define logical functions to locate items to test and to locate and handle exceptions
  
• Define text functions to manipulate text data
  
• Define the VLOOKUP function, XLOOKUP function and a combination of the index and match functions
  
• Compute a payment, rate, term, present value or future value using time value of money functions

Chapter 1 - Must Know Excel Functions for CPAs

Introduction

The SUM Function

The SUBTOTAL Function

The SUMPRODUCT Function

The DSUM Function

The SUMIF Function

The SUMIFS Function

Review Questions

The COUNT Function

The COUNTA Function

The COUNTBLANK Function

The COUNTIF and COUNTIFS Functions

Using COUNTIF to Find Duplicates

The COUNTIFS Function

The DCOUNT Function

The AGGREGATE Function

Review Questions

Logic Functions

Boolean Logic

Logical Functions for Selection of Items

Locating Formula Exceptions

ISERROR

Handling Formula Exceptions

IFERROR and IFNA

IFNA

Review Questions

Working with Text

LEFT RIGHT MID

CONCAT

TEXTBEFORE,TEXTAFTER, TEXTSPLIT TEXTJOIN

LEN

Numbervalue

Lookup Functions

Index and Match Functions

Vlookup Function

XLOOKUP

Date Functions

DATEVALUE

NOW() Function

DAYS

Review Questions

Time Value of Money Functions

Present Value of a Single Sum

Future Value of a Single Sum

Present Value of Annuities

Future Value of Annuities

Computing the Periodic Payment

Computing the Rate

Computing Number of Periods

RRI Function

PDURATION Function

Review Questions

Other functions

IFS

SWITCH

Comprehensive Case Study Exercises

Suggested Answers to Comprehensive Case Study Exercises

Link for Spreadsheet

Glossary