Creating Custom Financial Statements using Excel (Course Id 691)

• Who is this course for?
  This course is designed for CPAs and other professionals who need Continuing Professional Education (CPE) and want to improve their ability to create custom financial statements using Excel.

• What is this course about or what problem does this course solve?
  The course addresses the limitations of general ledger software in producing flexible financial statement formats by teaching users how to use Excel to create customized financial statements.

• How can the knowledge from this course be used?
  Learners can apply Excel functions such as LEFT, RIGHT, MID, SUMIF, VLOOKUP, and INDIRECT to efficiently map and format financial data into tailored financial statements from a trial balance.

• Why is this course important to a CPA or Accountant?
  It enhances their practical skills in Excel, enabling them to generate client-specific or industry-specific financial reports that general accounting software might not support.

• When is this course relevant or timely?
  This course is timely for professionals working with trial balances from systems lacking customizable reporting capabilities or those needing to prepare specialized financial presentations.

• How is a course like this consumed or used?
  Delivered as a QAS self-study text-based course, it includes review and final exam questions and requires familiarity with Excel formulas and functions to complete.

Most general ledger packages do a great job of aggregating journal entries and creating a trial balance. Where many fall short is in the presentation of financial information in the form of financial statements. Many have default financial statements that conform to the presentation needs of a manufacturing entity or a wholesaler, but are not very flexible beyond that. If they do provide for customizable financial statement formats, it can be a difficult and time consuming process.

The good news is that using Excel to prepare custom financial statements from a trial balance is a relatively easy and straightforward process. If the general ledger account numbers already have financial statement mapping embedded in them, the process is a few simple steps. If the general ledger account numbers do not indicate the appropriate financial statement line item, there is one intermediate step that provides a map to the financial statement from the trial balance.

Using the techniques in this course, you can create custom financial statements in any form or format that is necessary using Excel.

NASBA QAS - Text - NASBA Registry - 691

Course Learning Objectives

• Define certain text functions and use them to capture a portion of a general ledger account number
• Recognize the uses of the SUMIF function
• Define and use the VLOOKUP function
• Associate the INDIRECT function with referencing Excel worksheets

Chapter 1 - Creating Custom Financial Statements using Excel

Course Learning Objectives

Course Overview

Trial Balance with Financial Statement Mapping Embedded in the Account Number

LEFT, RIGHT and MID Excel Functions

Creating the Balance Sheet

Handling a Trial Balance with No Financial Statement Mapping in the Account Number

Using the INDIRECT Function

Review Questions

Review of Excel Functions Utilized

The SUM Function

Working with Text

The SUMIF Function

The VLOOKUP Function

Summary

Review Questions

Glossary