A complete Accounts Receivable aging, customer risk and reporting system for desktop Excel

The ExcelBell AR Aging & Management System is a ready-built workbook for businesses that need more than a basic aging table, but do not need, or do not want to purchase, a separate Accounts Receivable platform.

You enter or paste your invoice data in one place. The workbook calculates due dates, open balances, days overdue, overdue balances, aging buckets, priorities and risk scores. It then turns that data into a Customer Summary, a management Dashboard, monthly trend analysis, printable Customer Statements, a Management Report and a Check Sheet that helps you review the file before sharing it.

The workbook is designed for Microsoft Excel 2019 or later desktop versions, including Microsoft 365 desktop Excel. It does not use VBA macros, Power Query, external connections or PivotTables. Everything needed for the standard reporting workflow remains inside the file.

What is included

The workbook includes:

• automatic invoice aging and open balance calculations
• a consolidated Customer Summary for up to 500 customers
• a Dashboard with key Accounts Receivable indicators and trends
• a Monthly Summary with capacity for up to 240 reporting periods
• customer risk scores, risk levels and collection priorities
• a printable Management Report
• a Customer Statement generator
• a dedicated Check Sheet
• protected formulas and clearly separated input areas
• capacity for up to 2,000 invoices
• compatibility with Microsoft Excel 2019 or later desktop versions, including Microsoft 365 desktop Excel

How this workbook started

I did not originally plan to build a commercial Accounts Receivable product.

The workbook started from a report I had to prepare for one specific case. The first version was what most of us would build when the request seems temporary: one worksheet, a few formulas, an aging table and several summaries. It solved the immediate problem, so there was no reason to make it more complicated.

The report was requested again the following month. It then became part of quarterly reporting, followed by semi-annual and annual reporting. Each time I returned to the file, I found something that required manual work or did not give me enough information. The aging table showed which invoices were overdue, but it did not give me a practical customer-level view. The totals were useful, but they did not show whether collection performance was improving. The report worked, but too much of the process still depended on rebuilding, checking and remembering what had to be updated.

That was the point at which the project became much larger than I had expected. I kept adding the things I needed in a real reporting process: a Customer Summary, monthly trends, a management Dashboard, a printable report, a Customer Statement, formula checks, capacity warnings, protection and instructions for someone opening the workbook for the first time.

The development took more than three months. This is still the first public version of the product, but internally it went through enough rebuilding to reach version 1.5 before release. The most time-consuming part was not adding more formulas. It was making all the worksheets agree with one another, keeping the file compatible with Excel 2019, removing the dependence on PivotTables and creating a workflow that another person could follow without knowing how the workbook had been built.

I started with the same idea behind many templates on the market: a simple file that gives the user a faster starting point. I eventually realised that a simple file would not have been enough for my own recurring reporting needs, and I could not reasonably expect it to be enough for a customer dealing with the same problem.

The result is still an Excel workbook, but it has been built as a complete reporting product rather than a collection of unrelated sheets.

Who this workbook is for

The workbook is intended for accountants, bookkeepers, finance managers, financial controllers, credit controllers, consultants and business owners who prepare Accounts Receivable reports in Excel.

It is particularly useful when the current process involves exporting invoice data from accounting software and then creating the actual analysis in a separate spreadsheet.

A typical user may already have an accounting system that records invoices and payments correctly, but still need Excel for questions such as:

• How much is outstanding as of a specific date?
• How much is overdue?
• Which customers should be contacted first?
• Which balances have moved into older aging categories?
• Which customers represent the largest exposure?
• Is collection performance improving over time?
• What information should be included in a monthly management report?
• How can a customer statement be prepared without filtering and copying invoice rows manually?
• How can the final workbook be checked before it is distributed?

The system is designed for businesses managing up to 2,000 invoices and 500 customers in one file. It is not intended to replace an ERP, accounting platform, credit bureau, collection agency system or a multi-user cloud application.

The reporting workflow

The workbook follows a fixed sequence so that the reporting process does not have to be reinvented every month.

1. Configure the workbook

The Settings sheet contains the company information, currency, reporting date and other workbook preferences.

The reporting date can use the current date or a custom date. The custom option is important for month-end, quarter-end and year-end reporting because the report should reflect the chosen period, even when it is prepared several days later.

2. Enter or paste invoice data

The user enters the required source fields in the blue input columns of the Invoices sheet. The remaining fields are calculated automatically.

The standard source fields are:

• Customer
• Invoice Number
• Invoice Date
• Payment Terms
• Invoice Amount
• Paid Amount

3. Review customer-level results

The Customer Summary groups invoice data by customer and calculates balances, aging, invoice counts, overdue percentages, weighted overdue days, risk scores and ranking information.

4. Analyse the Dashboard

The Dashboard provides an overview of the receivables position, aging distribution, customer concentration and monthly trends.

5. Generate reports and statements

The Management Report and Customer Statement are prepared for printing or PDF export.

For the best result, select the relevant report area and choose Print Selection, Landscape Orientation and Fit Sheet on One Page in Excel’s print settings. Review the print preview before printing or exporting, because margins and scaling may vary between printers and PDF drivers.

The Customer Statement is generated by selecting a customer from the dropdown, so there is no need to filter invoice data or create and format a separate worksheet manually.

6. Complete the checks

The Check Sheet reviews key elements of the workbook and displays an overall status before the report is shared.

Workbook structure

Start Here

The Start Here sheet explains how the workbook should be used and provides a clear path through the file.

This may sound like a small feature, but it became necessary once the workbook grew beyond a few worksheets. A person opening the file for the first time should not have to inspect every tab to understand where to begin, which cells can be changed or what needs to be checked at the end.

The sheet explains the seven main steps:

1. Configure company information, report date, currency and preferences in Settings.
2. Paste or enter invoice data in the blue columns of the Invoices sheet.
3. Review customer balances, overdue amounts and risk information in Customer Summary.
4. Analyse the KPIs and trends in the Dashboard.
5. Generate a printable statement for a selected customer.
6. Use the Management Report for a structured reporting output.
7. Review the Check Sheet before sharing the file or exporting reports.

Settings

The Settings sheet contains the inputs that control the rest of the workbook.

The most important setting is the Report Date. Aging should always be measured against a clearly defined date. Using the current date is useful for live monitoring, while a custom date is required when the report needs to reproduce a historical position.

For example, a report for June 30 should still show the June 30 aging position if it is prepared on July 4. Without a custom reporting date, several invoices would appear four days older and could move into different aging categories before the report is even completed.

The Settings sheet also contains company information and the reporting currency used in the workbook. These values flow into the relevant reports and statements so they do not need to be entered repeatedly.

Invoices

The Invoices sheet is the main data source.

The workbook is designed so that the user edits only the input columns. Formula columns are calculated automatically and protected to reduce accidental changes.

For every invoice, the workbook calculates:

• Due Date
• Open Balance
• Days Overdue
• Overdue Balance
• Aging Bucket
• Priority
• Risk Score

This structure supports unpaid, partially paid and fully paid invoices. If an invoice has been partially settled, only the remaining balance is included in the Accounts Receivable totals. A fully paid invoice remains part of the historical data but is excluded from the open and overdue balances.

The source data should use consistent customer names. Excel cannot know that three slightly different spellings refer to the same legal entity. If the same customer is entered as “ABC Ltd”, “ABC Limited” and “A.B.C. Ltd”, those names may be treated as separate customers in the summary.

Excel is many things, but it is not a mind reader. To the workbook, “ABC Ltd” and “ABC Limited” are two perfectly respectable strangers.

Customer Summary

The Customer Summary is the operational part of the workbook.

The invoice table is necessary for transaction detail, but collection activity is usually organised by customer. A customer may have ten invoices with different dates, partial payments and aging categories. Reviewing those rows separately makes it difficult to see the total exposure and decide how urgent the account is.

The Customer Summary places one customer on each row and brings together:

• Current balance
• 1 to 30 Days
• 31 to 60 Days
• 61 to 90 Days
• 91+ Days
• Total Outstanding
• Total Overdue
• Open Invoices
• Average Days Overdue
• Maximum Days Overdue
• Risk Score
• Risk Level
• Oldest Open Invoice Date
• Oldest Due Date
• Total Invoice Amount
• Total Paid Amount
• Number of All Invoices
• Overdue Percentage
• Weighted Average Days Overdue
• ranking and sort fields used by the reports

The worksheet helps distinguish between a large customer balance and an urgent collection case. Those are not always the same thing.

A customer with a large balance made up mainly of recent invoices may require monitoring, but not immediate escalation. Another customer may owe a smaller amount, but if nearly all of it is overdue and several invoices are more than 90 days old, that account may deserve attention first.

Monthly Summary

The Monthly Summary stores the period-based calculations used for trend analysis.

The workbook supports up to 240 monthly periods, which is equivalent to 20 years of monthly reporting. The sheet includes fields for period, month, year, quarter, invoiced amount, paid amount, open balance, overdue balance, collection rate, invoice counts, average invoice value and weighted average days overdue.

This sheet exists because a current aging report cannot explain whether performance has improved. A business can have the same overdue balance in two different months but arrive there through very different movements. New overdue invoices may have replaced old invoices that were collected, or old balances may simply have remained unpaid while current activity declined.

The monthly structure provides the context required to interpret the current position.

Dashboard

The Dashboard brings the main indicators and charts into one page.

I designed it for the first stage of a management review. It should give enough information to understand the overall Accounts Receivable position, identify concentration and collection risk, and decide where further review is needed without forcing the reader to inspect the full customer table immediately.

The two KPI rows show:

• Total Invoiced
• Total Open Balance
• Overdue Balance
• % Overdue
• Total Invoices
• Open Invoices
• Overdue Invoices
• Overdue Customers

The Important Metrics panel adds portfolio, risk, collection and exposure indicators, including Current Balance, Cash at Risk (+61 Days), 91+ Days Balance, Critical Customers, Highest Risk Customer, Collection Risk Average, Collection Rate, Outstanding Rate, Avg Days Overdue, Weighted Avg. Days Overdue, Largest Exposure and Oldest Open Invoice information.

The charts show the aging mix, collection performance, open balance development, weighted overdue days and concentration among the largest customer balances.

The Dashboard is not intended to replace the detail sheets. Its purpose is to show where further review is needed. The Customer Summary and Invoices sheet remain available when a KPI or chart needs to be investigated.

Management Report

The Management Report is the structured reporting output of the workbook.

The Dashboard works well on screen, but it is not always the best format for printing, distributing as a PDF or including in a monthly reporting pack. The Management Report reorganises the main information into a cleaner print-ready layout.

It includes the most relevant KPIs, aging information, customer analysis and supporting tables without trying to reproduce the entire Dashboard.

For the best print or PDF result, select the report area and choose Print Selection, Landscape Orientation and Fit Sheet on One Page. Always review the print preview before exporting because printer margins and PDF drivers can affect scaling.

Customer Statement

I added the Customer Statement because identifying an overdue customer is only part of the work. Someone still needs to prepare the information that will be sent to that customer.

Without a statement generator, the user would have to filter the invoice table, copy the relevant rows, add company information, calculate totals and format a separate document. The Customer Statement uses the data already available in the workbook and creates a printable output for a selected customer.

After selecting the customer, review the invoice details, totals, company information and Report Date. For the best print or PDF result, use Print Selection, Landscape Orientation and Fit Sheet on One Page, then confirm the layout in print preview.

It can support payment reminders, balance confirmation, reconciliation and dispute follow-up.

Check Sheet

The Check Sheet was added because an interconnected workbook can look correct even when something underneath has changed.

A user may paste more records than the supported limit, overwrite a formula, leave required information blank or create a mismatch between source data and summary outputs. Some problems produce an obvious Excel error. Others produce a number that looks reasonable, which makes them harder to notice.

Excel does not always announce that something has gone wrong. Sometimes it simply continues calculating with remarkable confidence.

The Check Sheet provides a separate place to review workbook integrity. It includes individual controls and a master status that shows whether all checks passed, whether warnings require review or whether action is required.

It does not replace professional review. It makes that review more systematic.

About and Helper sheets

The About sheet contains product, version, compatibility and support information.

The Helper sheet contains supporting calculations used by several visible reports. It is intentionally kept out of the normal user workflow. The user does not need to interact with the helper logic during ordinary use, and editing it can affect formulas, rankings and chart sources elsewhere in the workbook.

How the invoice calculations work

Due Date

Due Date is calculated from Invoice Date and Payment Terms.

This saves one manual input and, more importantly, keeps the aging logic consistent. An incorrect due date affects every time-based calculation that follows, including Days Overdue, Aging Bucket, Overdue Balance, Priority and Risk Score.

Open Balance

Open Balance is the Invoice Amount less the Paid Amount.

This calculation allows the workbook to handle partial payments correctly. If an invoice for 10,000 has received a payment of 7,500, only the remaining 2,500 is included in the open balance.

Days Overdue

Days Overdue compares the invoice Due Date with the selected Report Date.

Invoices that have not reached the due date are Current. Fully paid invoices are excluded from overdue analysis.

The exact number of overdue days remains available because an aging bucket alone can hide important differences. Two invoices can both appear in the 31 to 60 Days category while one is 32 days overdue and the other is 59 days overdue.

Aging Bucket

Open balances are grouped into:

• Current
• 1 to 30 Days
• 31 to 60 Days
• 61 to 90 Days
• 91+ Days

These categories are fixed in the standard version of the workbook. Keeping them fixed protects the consistency of formulas, charts, checks and reports.

Overdue Balance

An invoice can be open without being overdue. The Overdue Balance includes only the portion of an open invoice whose due date has passed.

This distinction is important because Total Open Balance and Overdue Balance describe different parts of the portfolio. A business can have a high receivables balance because of recent sales while still maintaining a healthy aging profile.

Priority and Risk Score

Each invoice receives a Priority and Risk Score based on the workbook logic.

These fields are intended to support sorting and initial review. They are not a legal, accounting or credit bureau assessment, and they do not predict whether a customer will default.

Their role is practical: when the invoice table contains hundreds or thousands of records, the user needs a quicker way to identify which items may deserve attention.

Reading the Dashboard

The Dashboard is most useful when the indicators are read together. The names below match the labels used in the workbook.

Total Invoiced

Total Invoiced is the combined invoice value recorded in the workbook.

It provides scale and historical context, but it is not the same as the current unpaid exposure. For that, the user should review Total Open Balance.

Total Open Balance

Total Open Balance is the combined unpaid portion of all invoices as of the selected Report Date.

An increase can have several causes. The business may have issued more invoices, customers may be paying more slowly, or a small number of large balances may remain unpaid. It should therefore be reviewed together with Overdue Balance, % Overdue, Aging Distribution and the largest customer exposures.

A growing open balance is not automatically negative. In a growing business, it may follow higher sales. The concern begins when the overdue share and aging severity increase at the same time.

Overdue Balance

Overdue Balance is the portion of Total Open Balance that has passed the agreed payment date.

The amount still needs context. A rising overdue balance may be caused by several newly overdue invoices or by older balances that remain unresolved. Those situations require different collection responses.

% Overdue

% Overdue compares Overdue Balance with Total Open Balance.

The percentage helps the user judge portfolio quality when the overall size changes. If Overdue Balance remains unchanged while Total Open Balance falls, the percentage becomes worse even though the overdue amount did not increase.

Total Invoices

Total Invoices shows the number of invoice records included in the workbook.

It gives context for the scale of the dataset and should not be confused with Open Invoices, which includes only invoices that still have a remaining balance.

Open Invoices

Open Invoices shows the number of invoices with an unpaid balance.

The amount outstanding measures financial exposure. The invoice count gives an idea of operational workload. A small number of large invoices may require account-level escalation, while many small open items may point to a reconciliation or process issue.

Overdue Invoices

Overdue Invoices shows how many open invoices have passed their due date.

Read it together with Overdue Balance. A high count with a relatively low balance may indicate many small unresolved items, while a low count with a high balance may indicate concentration in a few material invoices.

Overdue Customers

Overdue Customers shows how many customer accounts have at least one overdue invoice.

This metric helps separate a broad portfolio issue from a problem concentrated in a small number of customers.

Important Metrics: Portfolio

The Portfolio group provides a more detailed view of the open balance:

• Current Balance shows the portion that is open but not yet due.
• Overdue Balance repeats the overdue exposure for quick reference.
• Cash at Risk (+61 Days) highlights balances in the 61 to 90 Days and 91+ Days categories.
• 91+ Days Balance isolates the oldest aging bucket.

These indicators show whether the exposure is mainly recent or concentrated in older, more difficult balances.

Important Metrics: Risk

The Risk group includes:

• Critical Customers
• Highest Risk Customer
• Collection Risk Average

Critical Customers is an exception count. Highest Risk Customer identifies the account with the highest workbook-based risk assessment. Collection Risk Average provides a portfolio-level view of customer risk.

These measures support prioritisation, but they do not replace customer-level review in Customer Summary.

Important Metrics: Collections

The Collections group includes:

• Collection Rate
• Outstanding Rate
• Avg Days Overdue
• Weighted Avg. Days Overdue

Collection Rate and Outstanding Rate show the relationship between paid and unpaid invoice value. Avg Days Overdue describes the general delay among overdue invoices. Weighted Avg. Days Overdue gives more influence to larger overdue balances and can reveal financial deterioration that a simple average may hide.

Important Metrics: Exposure

The Exposure group includes:

• Largest Exposure (Value)
• Largest Exposure (Customer)
• Oldest Open Invoice (No.)
• Oldest Open Invoice (Days)

These measures identify concentration and long-standing exceptions. A large balance is not automatically overdue, and the oldest invoice is not automatically the largest. The purpose of the group is to make both types of exposure visible immediately.

Dashboard charts

Aging Distribution

Aging Distribution shows how Total Open Balance is divided between Current, 1 to 30 Days, 31 to 60 Days, 61 to 90 Days and 91+ Days.

This is a snapshot of the aging position at the selected Report Date. It should not be described as a time trend. Its purpose is to show where the open balance is concentrated and how much has progressed into older categories.

Collection Rate

Collection Rate shows the development of collection performance across the reporting periods stored in Monthly Summary.

The chart is most useful when read together with the invoiced and paid amounts behind the percentage. A change in the rate can reflect collection performance, billing volume or timing differences between invoice and payment periods.

Open Balance

Open Balance shows how unpaid exposure develops across reporting periods.

A rising line may follow increased invoicing, slower collections or both. The Monthly Summary provides the supporting values needed to identify the cause.

Weighted Average Days Overdue

Weighted Average Days Overdue tracks the age of overdue balances while giving more influence to larger amounts.

This chart is useful when invoice counts or simple averages appear stable but financially significant invoices continue to age.

Outstanding Balance Distribution (Top 10 Customers by Outstanding Balance)

This chart shows how the largest customer balances contribute to the portfolio’s open exposure.

It helps identify concentration risk and shows whether Total Open Balance is spread across many customers or depends heavily on a small number of accounts. A large balance is not automatically a collection problem, so each customer should also be reviewed by overdue amount, aging profile and risk level.

Customer-level collection analysis

The Customer Summary is designed to answer a practical question: where should collection attention begin?

A ranking based only on balance is not enough. Consider two customers:

Metric	Customer A	Customer B
Total Outstanding	40,000	16,000
Total Overdue	5,000	14,500
Overdue Percentage	12.5%	90.6%
Weighted Average Days Overdue	18	83
Risk Level	Low	Critical

Customer A represents the larger exposure. Customer B represents the more urgent collection problem.

The workbook keeps both perspectives available.

New to receivables aging? Read our complete guide to the aging formula in Excel and learn how 30, 60, and 90-day aging works.

Current balance

Current balance shows invoices that remain open but have not yet reached the due date.

These invoices do not require the same action as overdue items, but a large Current balance can still create concentration risk. Finance teams may want to monitor upcoming due dates for customers with substantial exposure or weaker payment history.

Aging balances

The customer-level aging columns show whether the balance is concentrated in one category or spread across several periods.

A customer whose balance gradually moves from Current into older buckets may be deteriorating even if Total Outstanding remains stable.

Total Outstanding and Total Overdue

Total Outstanding measures the full open exposure to the customer.

Total Overdue identifies the portion that collection activity should address.

The two values should be considered together. A customer with a high overdue amount and very little Current business may present a different risk from a customer with the same overdue amount but a much larger, active and mainly Current account.

Open Invoices

Open Invoices shows the number of unresolved items.

A high count can indicate collection workload, but it may also point to unapplied payments, partial settlements, disputes or small residual balances that need reconciliation.

Average and Maximum Days Overdue

Average Days Overdue describes the general payment delay.

Maximum Days Overdue identifies the oldest individual item.

When the maximum is much higher than the average, the user should inspect the oldest invoice separately. It may be a dispute, missing document, credit note issue or legacy balance rather than a normal collection delay.

Oldest Open Invoice Date and Oldest Due Date

These dates allow the user to locate long-standing balances without filtering the full invoice table.

They are particularly useful during weekly collection reviews and when deciding whether an invoice needs escalation or accounting cleanup.

Total Invoice Amount, Total Paid Amount and Number of All Invoices

These measures provide context about the customer relationship.

A customer with many historical invoices and only one open item may be a normal exception. A customer with a short history and most invoices still unpaid may deserve closer monitoring.

Overdue Percentage

Overdue Percentage helps compare customers of different sizes.

It should not be used without the underlying amounts. A 100% overdue balance of 200 is not automatically more important than a 25% overdue balance of 100,000.

Weighted Average Days Overdue

Weighted Average Days Overdue reflects both the age and value of overdue balances.

This measure helps separate several old low-value invoices from a large overdue exposure that has remained unpaid.

Customer Risk Score and Risk Level

The customer score combines selected indicators into one measure that supports sorting and prioritisation.

The risk level translates the score into categories such as No Risk, Low, Medium, High and Critical.

The classification provides a consistent starting point, but it should be interpreted with the account detail. A customer can reach a high score because of balance size, age, invoice count or a combination of factors.

Ranking

The ranking fields support the Top Customer analysis and make long customer lists easier to review.

They help the user locate the accounts with the largest exposure or highest priority without building a separate PivotTable or manual list.

A practical collection review

The workbook can support a weekly collection meeting without replacing the company’s action log or communication records.

A practical sequence is:

1. Review Critical customers.
2. Review High Risk customers.
3. Identify the largest overdue balances.
4. Check the oldest open invoices.
5. Review customers whose risk level increased.
6. Identify newly overdue large invoices.
7. Investigate unresolved disputes and unapplied payments.
8. Assign the next action and responsible person outside the workbook or in the company’s existing collection process.

For a selected customer, the user can move from the Customer Summary to the invoice detail and then generate a Customer Statement if required.

Trend analysis

A current report tells the user where the business stands. The Monthly Summary helps explain how it arrived there.

Total Invoiced and Paid Amount

These measures show the relationship between billing activity and payments.

A high paid amount is positive, but it may include collections of old invoices. A low open balance can also result from lower invoicing rather than better collection performance. Reviewing invoiced, paid and remaining balances together avoids these misleading conclusions.

Open Balance

The monthly Open Balance shows how much exposure remains unpaid for each reporting period.

When it rises, the user can review whether the cause is increased sales, slower collections or a combination of both.

Overdue Balance

The Overdue Balance trend shows whether payment delays are becoming more significant.

A single unusual month may be caused by timing. A repeated increase over several periods is more likely to indicate a collection problem or customer deterioration.

Collection Rate

Collection Rate provides a simple view of payments relative to invoicing.

The metric is most useful as a pattern. A falling trend combined with rising Open Balance, rising Overdue Balance and increasing Weighted Average Days Overdue deserves investigation.

Open and Overdue Invoice Counts

Invoice counts help explain whether changes are caused by a few material invoices or by a wider increase in unresolved items.

The required response may be different. A single large invoice may need management escalation, while hundreds of small balances may require process improvement or reconciliation work.

Average Invoice Value

Average Invoice Value helps identify whether changes in receivables are being driven by larger transactions.

An increase in invoice size can create greater concentration and working-capital exposure even when the number of invoices remains stable.

Weighted Average Days Overdue

The monthly weighted measure shows whether the most financially significant overdue balances are getting older.

An increasing trend can reveal deterioration that is not visible in invoice counts.

Recognising a deteriorating pattern

A common deterioration pattern may look like this:

1. Collection Rate falls slightly.
2. Open Balance begins to rise.
3. More invoices become overdue.
4. Overdue Balance increases.
5. Weighted Average Days Overdue rises.
6. More customers move into High or Critical Risk.

No individual change has to look dramatic. The value of trend analysis is seeing the sequence before the position becomes severe.

Reports and customer communication

Management Report

The Management Report is intended for periodic reporting and internal discussion.

It provides a consistent format so that the reader can compare one reporting period with another without learning a new layout each time. A structured report also reduces the need to copy charts and tables into a separate document every month.

For the best print or PDF result, select the report area and choose Print Selection, Landscape Orientation and Fit Sheet on One Page. Review the preview before exporting because margins and scaling can vary between printers and PDF drivers.

Customer Statement

The Customer Statement turns workbook data into a document that can support customer communication.

Before sending it, review the selected customer, invoice details, totals, company information and Report Date. Then use Print Selection, Landscape Orientation and Fit Sheet on One Page, and confirm the result in print preview.

The statement is a practical reporting output. It does not replace the official accounting ledger or any legally required documentation.

Check Sheet

The Check Sheet should be reviewed before either report is printed or exported.

A clean status does not remove the user’s responsibility to review source data, but it provides an additional safeguard against common spreadsheet problems.

Reliability and design decisions

Formula protection

The formula cells are protected because accidental edits in one area can affect several reports.

The workbook is not protected to make it mysterious. The logic is protected because the product is intended for users who want to operate the system, not rebuild it.

Input cells remain available where the user is expected to enter information.

No VBA

The workbook does not contain VBA macros.

This avoids macro security warnings and makes the file easier to use in organisations that restrict macro-enabled workbooks.

No Power Query

The standard workflow does not require Power Query or linked source files.

The invoice data is entered or pasted into the workbook. This keeps the file portable and avoids broken query paths when it is moved or shared.

No PivotTables

The final version does not depend on PivotTables.

I originally used PivotTables during development, but they created problems for a commercial product. They had to be refreshed, could be changed accidentally and complicated compatibility with the fixed reports and charts.

PivotTables are excellent tools, provided someone remembers to refresh them.

Replacing them with formula-based summaries required significantly more work, but it produced a more controlled experience for the end user.

Excel 2019 compatibility

Excel 2019 compatibility was a deliberate requirement.

The workbook does not rely on dynamic array functions such as FILTER, SORT or UNIQUE. Supporting older versions required helper formulas and preallocated ranges, but it means the product is not limited to Microsoft 365 users.

The workbook is intended for desktop Excel. Browser and mobile versions may not reproduce all protection, print settings, charts and workbook behaviour in the same way.

Defined limits

The standard version supports:

• 2,000 invoices
• 500 customers
• 240 monthly periods

These limits are stated clearly because a workbook should not silently stop including data after the user exceeds its designed capacity.

If the business regularly needs more than these limits, a larger or customised reporting solution would be more appropriate.

How much manual work can this replace?

The amount of time saved depends on the existing process.

A manual Accounts Receivable report may include:

• exporting and cleaning data
• calculating due dates and open balances
• assigning aging categories
• building customer summaries
• creating charts
• preparing a print report
• filtering individual customer accounts
• checking totals
• repeating the work next month

A reasonable monthly example might look like this:

Task	Estimated Time
Exporting and cleaning data	20 minutes
Updating formulas and aging logic	15 minutes
Preparing customer summaries	20 minutes
Updating charts	15 minutes
Preparing management output	20 minutes
Checking totals and errors	20 minutes
Preparing customer-specific information	30 minutes
Total	140 minutes

The workbook does not remove the need to investigate balances, communicate with customers or apply professional judgement. Those are the parts of the process that finance teams should be spending time on.

The time saving comes from not rebuilding the reporting structure repeatedly.

What the workbook does not do

The workbook is deliberately limited to Accounts Receivable reporting and analysis.

It does not:

• create official accounting entries
• issue invoices
• connect directly to bank feeds
• send automated collection emails
• manage legal debt recovery
• provide a formal credit score
• predict customer insolvency
• support real-time multi-user collaboration
• replace an ERP or accounting system
• guarantee collection results

The accuracy of the outputs depends on the accuracy and completeness of the invoice and payment information entered by the user.

Frequently asked questions

How does the accounts receivable aging calculation work?

The workbook automatically assigns open balances to Current, 1–30, 31–60, 61–90, and 91+ day buckets based on the selected report date. For a detailed explanation of the calculation, see our Accounts Receivable Aging Formula in Excel guide.

Does the workbook work with Excel 2019?

Yes. Microsoft Excel 2019 is the earliest supported desktop version.

Does it work with Microsoft 365?

Yes. It is designed for Microsoft 365 desktop Excel as well as other desktop versions released after Excel 2019.

Does it contain macros?

No. The workbook does not use VBA.

Does it require Power Query?

No.

Does it use PivotTables?

No. The final customer summaries, reports and chart support ranges are formula based.

Do I need advanced Excel knowledge?

Normal use does not require advanced formula knowledge. The user should be comfortable entering or pasting data, selecting worksheet tabs, using dropdown lists and printing or exporting reports.

Which fields do I need to enter?

The standard invoice inputs are Customer, Invoice Number, Invoice Date, Payment Terms, Invoice Amount and Paid Amount.

Can the workbook handle partial payments?

Yes. Only the unpaid part of an invoice is included in Open Balance.

Are paid invoices included in overdue totals?

No. Fully paid invoices are excluded from open and overdue balances.

Can I prepare a report for an earlier date?

Yes. The Settings sheet allows a custom Report Date.

Can I use today’s date automatically?

Yes. The report mode can use the current date.

How many invoices and customers can I add?

The standard version supports up to 2,000 invoices and 500 customers.

Can I add more than the stated limits?

The formulas and report ranges are designed and tested for the stated limits. Records beyond them may not be included correctly.

Can I generate Customer Statements?

Yes. The Customer Statement sheet generates a printable statement for the selected customer. For the best result, use Print Selection, Landscape Orientation and Fit Sheet on One Page, then review the print preview.

Can I print the Management Report?

Yes. It is designed for printing and PDF export. Use Print Selection, Landscape Orientation and Fit Sheet on One Page, then review the print preview.

Can I change the company information and currency?

Yes. These settings are available in the Settings sheet. The standard workbook uses one reporting currency at a time.

Does it connect directly to accounting software?

No. Data exported from accounting software must be pasted into the required input columns.

Can I customise the workbook?

The standard product is protected to preserve formulas and report integrity. Changes outside the intended input areas may affect calculations, checks and outputs.

Is the Risk Score a formal credit assessment?

No. It is a workbook-based indicator for internal review and prioritisation.

Is this accounting software?

No. It is a reporting and analysis workbook. Official invoices, payments and accounting records should remain in the accounting system.

Can it be used on a Mac?

The workbook does not rely on Windows-only VBA, but desktop Excel versions can differ in formatting, protection and print behaviour. The complete experience is designed around desktop Microsoft Excel, and users should verify compatibility with their specific Mac version.

Can it be used in Excel Online?

Desktop Excel is recommended. Excel Online may not reproduce all workbook behaviour, print settings and formatting exactly.

Should I keep a backup?

Yes. Keep an untouched original file and maintain backups of active reporting copies.

Important information

The workbook is a reporting and decision-support tool. It does not provide accounting, legal, tax, credit or financial advice, and it does not constitute an audit or guarantee that outstanding balances will be collected.

Users are responsible for verifying source data, reviewing the final outputs, maintaining official accounting records and applying their own professional judgement and company policies.

Public Release 1.0

The first public release includes:

• automatic Due Date, Open Balance, Days Overdue and Overdue Balance calculations
• standard aging categories
• invoice priorities and risk scores
• formula-based Customer Summary
• customer aging, overdue percentage and weighted aging measures
• Customer Risk Score and Risk Level
• Dashboard KPIs and charts
• Monthly Summary with multi-year capacity
• Top Customer analysis
• printable Management Report
• Customer Statement generator
• Start Here and Settings sheets
• Check Sheet and master status
• formula protection
• compatibility with Microsoft Excel 2019 or later desktop versions
• no VBA, Power Query, PivotTables or external connections

Ready to improve your Accounts Receivable reporting?

This workbook is for finance professionals and businesses that already use Excel but want a reporting process that is more complete, consistent and easier to repeat.

You still control the source data and the collection decisions. The workbook handles the structure around them.

Enter the invoices once, review the customer position, understand the trends, prepare the reports and complete the checks in the same file.