Pathway to Excel Certification Part 3 – Aging Schedules


- +


The Aging Schedules exercise is designed to reinforce accounting concepts taught in Intermediate Financial Accounting, as well as provide exposure to dozens of Excel techniques that appear on the Microsoft Excel Certification exam. This assignment covers roughly 25% of the topics on the Microsoft Office Specialist Excel certification exam (MOS 200) by way of  38 different Excel concepts:

  • Enabling Get Data from Text
  • Opening and Saving .xlsx files
  • Using the Text Import Wizard
  • Assigning Worksheet Names
  • Importing Tab-delimited text files
  • Importing Fixed-width text files
  • Removing extraneous column breaks
  • Importing Comma-delimited text files
  • Opening CSV files
  • Moving Worksheets
  • Autofit column widths
  • Inserting worksheets
  • Assigning the percent style to the numbers
  • Using the VLOOKUP function
  • Using the Find and Replace feature
  • Correcting #VALUE errors
  • Correcting #N/A errors
  • Using the DATE function
  • Using the IF function
  • Using the SUMIFS function
  • Using the SUMIF function
  • Using the COUNT function
  • Using the COUNTIF function
  • Using the COUNTIFS function
  • Copying formulas down a column
  • Averaging using funcitons
  • Using the LARGE function
  • Using INDEX with MATCH
  • Using the SMALL function
  • Creating a Text Box
  • Sorting with multiple columns
  • Creating a PivotTable
  • Creating a Page Footer
  • Drilling-down a PivotTable
  • Creating a PivotChart
  • Filtering data
  • Removing blank rows
  • Using the Subtotal feature
  • Formatting Subtotals

Two “on on your own” tasks are included:

  • Calculating the allowance for bad debt based on different percentages for each aging bucket.
  • Recording the increase or decrease in bad debt for five months via general journal entries.

Rest assured, you can always ask for help via if you get stuck.