Excel for Accounting (Microsoft 365 Apps):

Exam Objectives

Download Exam Overview

Excel for Accounting (Microsoft 365 Apps): Exam MO-220

The Microsoft Office Specialist: Excel for Accounting Associate certification demonstrates competency in the fundamentals of preparing accounting data for analysis, preparing a trial balance, preparing and analyzing financial statements, and performing routine accounting activities. Workbook examples include trial balances, financial statements, payroll data, bank reconciliations, and amortization schedules.

An individual earning this certification has approximately 150 hours of instruction and hands-on experience with both Microsoft Excel and accounting, has proven competency at an industry associate-level, and is ready to enter the job market. This individual can perform a wide range of accounting tasks in Excel and apply accounting principles to real-world scenarios, demonstrating proficiency that is directly applicable to accounting job roles.

Perform Accounting Data for Analysis

Import and export accounting data in Excel

• Import data from various sources
• Save and export workbooks in various file formats

Clean accounting data in Excel

• Remove duplicate data to ensure accuracy
• Delete or hide rows, columns, and cells to simplify data
• Format cells for consistency

Verify accounting data integrity in Excel

• Apply Data Validation rules to restrict input
• Identify data outside defined standards or expected values

Transform accounting data in Excel

• Split a single data column into multiple columns
• Use text functions to extract or combine values
• Use date functions to transform transaction dates
• Use Fill features to extend series and patterns

Prepare a Trial Balance

Organize accounting transactions in Excel

• Sort transactions
• Filter transactions

Organize financial data using Excel formulas

• Use math and statistical functions
• Use logical functions
• Use lookup functions

Prepare and Analyze Financial Statements*

Prepare financial statements in Excel

• Use formulas to aggregate data
• Format data for presentation

Analyze financial statements in Excel

• Calculate profitability ratios
• Calculate liquidity ratios
• Calculate solvency ratios

Correct financial statement errors in Excel

• Use Conditional Formatting to identify data anomalies
• Use Formula Auditing features to verify and correct
calculations

Present financial information visually in Excel

• Create PivotTables to summarize financial data
• Create dashboards and charts
• Evaluate and improve accessibility and usability

Perform Routine Accounting Activities

Prepare amortization schedules in Excel

• Calculate fixed monthly payments
• Create amortization schedules

Prepare bank reconciliations in Excel

• Use functions to identify outstanding checks and deposits in
transit
• Use Conditional Formatting to identify reconciling items
• Calculate adjusted cash balances

Manage accounts receivable and accounts payable
in Excel

• Use functions to organize sales and purchase data
• Create accounts receivable or accounts payable
aging reports
• Use functions to calculate bad debt expense

Perform payroll activities in Excel

• Calculate gross and net pay for multiple employees
• Calculate employer payroll tax liabilities
• Use functions to assess irregularities

Create depreciation schedules in Excel

• Calculate periodic depreciation
• Project future depreciation