Aging buckets
IntermediateAccounts receivable aging is a key tool in finance and accounting. By categorizing outstanding invoices into "buckets" based on how many days old they are, you can quickly identify problem accounts and prioritize collection efforts.
In this exercise, you'll work with a list of outstanding invoices. Column C contains the invoice date, and column E contains today's date. You need to:
- Calculate the age of each invoice (in days) in column F
- Categorize each invoice into an aging bucket in column G
The standard aging buckets are:
- Current: 0-30 days
- Slightly overdue: 31-60 days
- Moderately overdue: 61-90 days
- Seriously overdue: 91+ days
Date math in Excel
To find the number of days between two dates, simply subtract the earlier date from the later date. Excel stores dates as numbers, so =Today - InvoiceDate gives you the number of days.
Nested IF for bucketing
To assign a bucket, you'll use nested IF statements. Start by checking the smallest threshold and work your way up, or start with the largest and work down. For example:
=IF(condition_1, result_1, IF(condition_2, result_2, IF(...)))
Your task
- In column F, calculate how many days old each invoice is
- In column G, use a nested IF to assign the correct aging bucket
- Fill your formulas down for all 6 invoices
Need some help?
Hint 1
For the days calculation in column F, subtract the invoice date (column C) from today's date (column E).
Hint 2
For the aging bucket, check thresholds in order: if days <= 30 return "Current"; if <= 60 return "Slightly overdue"; if <= 90 return "Moderately overdue"; otherwise return "Seriously overdue".
Hint 3
Your nested IF in column G should reference the days value you calculated in column F (e.g., F2).
Aging buckets
IntermediateAccounts receivable aging is a key tool in finance and accounting. By categorizing outstanding invoices into "buckets" based on how many days old they are, you can quickly identify problem accounts and prioritize collection efforts.
In this exercise, you'll work with a list of outstanding invoices. Column C contains the invoice date, and column E contains today's date. You need to:
- Calculate the age of each invoice (in days) in column F
- Categorize each invoice into an aging bucket in column G
The standard aging buckets are:
- Current: 0-30 days
- Slightly overdue: 31-60 days
- Moderately overdue: 61-90 days
- Seriously overdue: 91+ days
Date math in Excel
To find the number of days between two dates, simply subtract the earlier date from the later date. Excel stores dates as numbers, so =Today - InvoiceDate gives you the number of days.
Nested IF for bucketing
To assign a bucket, you'll use nested IF statements. Start by checking the smallest threshold and work your way up, or start with the largest and work down. For example:
=IF(condition_1, result_1, IF(condition_2, result_2, IF(...)))
Your task
- In column F, calculate how many days old each invoice is
- In column G, use a nested IF to assign the correct aging bucket
- Fill your formulas down for all 6 invoices
Need some help?
Hint 1
For the days calculation in column F, subtract the invoice date (column C) from today's date (column E).
Hint 2
For the aging bucket, check thresholds in order: if days <= 30 return "Current"; if <= 60 return "Slightly overdue"; if <= 90 return "Moderately overdue"; otherwise return "Seriously overdue".
Hint 3
Your nested IF in column G should reference the days value you calculated in column F (e.g., F2).