Skip to main content
by learnformula
Explore courses
search
For Business
Team license & compliance management
For Higher Education
Academic partnerships
For Government Agencies
Government training solutions
For Instructors & Course Creators
Teach on our marketplace
Become a Sponsor
Reach professionals where they learn
Log inStart 7-Day Free TrialStart Free TrialStart Free Trial
search
Explore courses
  1. Courses
  2. /Office Productivity
  3. /MS Excel

Excel University Lesson 505: Data-Driven Accounts Receivable Management

Use Excel and Power BI to prove the effectiveness of your collections process and optimize your AR strategy.

Created byJeff Lenningworkspace_premiumBest Instructor
AdvancedUpdated Sep 21, 2026

What You'll Learn

  • check_circleCalculate and interpret Days Sales Outstanding (DSO) to measure collection efficiency.
  • check_circleApply Power Query transformations to calculate invoice age based on dynamic dates.
  • check_circleConstruct histograms and box plots to visualize the distribution of open invoices.
  • check_circleImplement VLOOKUP logic within data models to create custom aging intervals.
  • check_circleDevelop a repeatable process for updating and refreshing collection performance reports.

About This Course

This course provides a practical framework for evaluating the performance of third-party collections agencies using accounts receivable data. It covers the calculation of Days Sales Outstanding (DSO) and the creation of distribution-based reports to monitor invoice aging. Participants will learn to transform raw invoice data using Power Query, build data models, and develop visualizations including histograms and box plots. The content emphasizes a data-driven approach to decision-making, enabling professionals to objectively assess whether collection efforts are improving payment timelines.

Topics Covered

  • Days Sales Outstanding (DSO) calculation
  • Invoice aging distribution analysis
  • Power Query data transformation techniques
  • Data model relationship management
  • Histogram creation for invoice intervals
  • Box plot visualization for trend analysis
  • VLOOKUP true logic in data models
  • Dynamic as-of-date variable implementation

Course Content

3 activities • 3 videos • duration: 1h 1m

  • videocamCase Study 5 - Excel
    Duration 28:12
  • videocamCase Study 5 - Power BI
    Duration 11:36
  • quizLecture Quiz

Your Instructor

Jeff Lenning

CPA and Microsoft Certified Trainer

menu_book36 courses
star1,189 reviews

In his live CPE training sessions, Jeff Lenning CPA has shown thousands of CPAs and accounting professionals across the country how to use Excel to streamline their work and become more efficient. Several publications have featured his Excel articles, including the Journal of Accountancy and California CPA Magazine. After graduating from the University of Southern California, Jeff began his career in the audit practice at Arthur Andersen’s Orange County office. He was a senior financial analyst at Gateway computers and the accounting manager at Interpore Cross International. In 2000, he founded Click Consulting Inc., a technology-consulting firm that specializes in Excel training, consulting, development, and personal coaching. He is a past president of the CalCPA OCLB Chapter, the former chair of the California CPA State Technology Committee, co-chair of the California CPA OCLB Chapter Technology Committee, a board member of the California CPA OCLB Chapter, on the California Society of CPAs Council, and an instructor for the AICPA and the California CPA Education Foundation. A Microsoft Office Certified Excel Expert, he is a nationally recognized speaker and trainer. He was recently recognized with the California CPA Education Foundation's Award for Instructor Excellence.

Credit Information

What Students Are Saying

0.0 out of 5
Student's Choice
0 reviews

Frequently Asked Questions

We are a registered provider with 300+ associations and regulatory bodies worldwide. We operate across 27 global markets including Canada, the US, Australia, and the UK. Every course page clearly displays its specific accreditations. Upon completion, you receive a professional certificate that can be validated online. Our certificates include all necessary accreditation details, credit hours, and completion dates, and are formatted specifically to meet the submission requirements of most global regulatory bodies.

You May Also Like

  • Finance: Budgeting & Financial Forecasting

    Critical Skills for CFOs & Controllers: Budgeting & Forecasting Strategies for Today’s World

    starRated5.0out of 5,(126 reviews)
    2.5 CPD hrs
  • Accounting & Tax: Technology for Accountants

    Claude Fundamentals for Accountants (2026 Update)

    starRated5.0out of 5,(675 reviews)
    2.5 CPD hrs
  • Personal Development: Stress Management

    Stress Management for Work

    starRated4.9out of 5,(144 reviews)
    3 CPD hrs