Workflow · Finance close · Opening soon
Bad debt reserve from aging
Use your QuickBooks AR Aging Summary export and your AR Aging Loss Rates file. Mammoth expands the aging buckets, matches the rates, and calculates reserve by customer.
Someone typed
“calculate each customer's bad debt reserve from the AR aging buckets using the loss rate for each bucket”
The agent read it as
Calculate each customer's bad debt reserve from the AR aging buckets in QuickBooks AR Aging Summary using the loss rate for each bucket in AR Aging Loss Rates.
What the agent built
4 steps, each one an ordinary Mammoth step you can open and change.
File
QuickBooks AR Aging Summary
CSV export · 12 rows
File
AR Aging Loss Rates
CSV export · 5 rows
Step
Expand aging buckets
Step 1
Step
Standardize rate buckets
Step 2
Join
Add bucket loss rates
60 of 60 paired
Step
Calculate reserve by customer
Step 4
Result
Customer Bad Debt Reserve
12 rows
Schedule
Runs when you say
Proposed
- FileQuickBooks AR Aging SummaryCSV export · 12 rows
- FileAR Aging Loss RatesCSV export · 5 rows
- StepExpand aging bucketsStep 1
- StepStandardize rate bucketsStep 2
- JoinAdd bucket loss rates60 of 60 paired
- StepCalculate reserve by customerStep 4
- ResultCustomer Bad Debt Reserve12 rows
- ScheduleRuns when you sayProposed
Checked on the sample files: every step ran, 12 rows came out, and the join paired 60 of 60 rows.
- Download QuickBooks AR Aging Summary sample (12 rows)QuickBooks: Reports > Who Owes You > A/R Aging Summary > Export
- Download AR Aging Loss Rates sample (5 rows)QuickBooks: Reports > Accountant & Taxes > Aging Detail > Loss rate assumptions sheet
What you get back
The result of that run, the rows to look at first.
Customer Bad Debt ReserveSample data12 rows
| Customer | Bad debt reserve |
|---|---|
| Northside Builders | 7,999 |
| Bluewater Logistics | 192 |
| Maple Street Cafe | 85.50 |
| Pioneer Medical Group | 439.50 |
| Summit Hardware | 258 |
| Riverbend Printing | 137 |
| Lighthouse Catering | 103.50 |
| Harbor Supply Co | 147.60 |
| Greenleaf Dental | 129 |
| Oak & Vine Market | 653 |
| Crescent Auto Repair | 233 |
| Evergreen School Supply | 111.50 |
How do you calculate bad debt reserve from aging?
Export your QuickBooks AR Aging Summary and your AR Aging Loss Rates file. Mammoth expands the aging buckets, applies the matching loss rate to each bucket, and calculates each customer’s bad debt reserve.
How it works
How to use the Customer Bad Debt Reserve workflow
- 1Get early accessWe invite people in small batches, job by job. You'll land on this workflow, already built and checked on the sample files.
- 2Swap in your exportsUpload your own files as they come out of your tools. The agent checks the workflow on your rows.
- 3Change it in words, then turn it onSay what to change and see the new result before anything saves. Then choose when it runs.
Customer Bad Debt Reserve: questions
What export should you use?
Use the QuickBooks AR Aging Summary export for the balances, and a separate spreadsheet with your aging loss rates for each bucket.
What happens if a row does not match?
Rows that do not match a bucket or rule are left out of that step or shown for review, so you can correct the source data or the rule before you run it again.
How do you change the rules?
Edit the rules in words, such as which aging buckets to use and which loss rate belongs to each bucket, then run the workflow again.
Get early access to this workflow
It will open already built and checked on the sample files. Swap in your own exports and see your result.
Workflows like this
Consolidate entities, compare budget to actual, build a trial balance, review month-on-month moves and work out commission.
- Profit and loss by monthBuild a monthly profit and loss statement from a QuickBooks general ledger export, with revenue, COGS, gross profit, expenses, and net income.See the workflow Built by the agent
- Real estate commission splitWork out real estate commission on each closed sale and split it between the listing agent, buying agent and brokerage.See the workflow Built by the agent
- Loan amortization from statementsBuild a monthly loan amortization schedule from an Excel export, with interest, principal, and remaining balance for each loan.See the workflow Built by the agent
- Accrued expenses scheduleBuild an accrued expenses schedule from QuickBooks exports. Match purchase orders to vendor bills, calculate unbilled amounts, and flag items that need accrual.See the workflow Built by the agent
- Cash conversion cycle by monthWork out cash conversion cycle each month from QuickBooks exports, using month-end balances and monthly sales.See the workflow Built by the agent
- Expense report from card transactionsTurn Amex card exports into an expense report per employee, with category totals and missing receipts flagged.See the workflow Built by the agent