Hotel City Ledger Aging & Bad Debt Provision Model: hotel accounts receivable aging and bad debt provision template

Full description

For financial controllers, credit managers, accounts receivable teams, general managers and internal auditors who look after direct-billed accounts. You list each account with its segment, credit terms, credit limit and any specific provision, then paste invoices with payments and a dispute flag. The workbook works out the due date, outstanding balance, days since invoice, days past due and the aging bucket (current to over 120 days, from the invoice date or the due date). It builds an aged trial balance by account and segment, a provision matrix with your loss rate per bucket, a higher rate for disputed invoices and specific provisions that take an account out of the matrix, and the month-end movement: opening provision, write-offs, charge or release and closing provision. Twelve month-ends are rebuilt from invoice and payment dates to give DSO, best possible DSO, average days delinquent and the collection effectiveness index. A credit monitor flags accounts over limit or past terms and suggests the next action.

Save with a bundle: this workbook is also in the Hotel Finance & Investment Pro Bundle, 25 workbooks for $59 instead of $329. All 100 Pro workbooks are in the Pro Library for $119, and all 241 templates and workbooks are in the Ultimate Hospitality Library for $149.

What is inside

• Hotel_City_Ledger_Aging_Provision_Model.xlsx: 11 tabs, 5,643 formulas, 3 native charts, 14 drop-down and input rules, 15 integrity checks, a Method tab with 18 calculation notes.
• Invoices: 300 sample invoices over 16 months with 100 spare rows; Accounts: 40 sample accounts in 6 segments with 20 spare rows.
• Aging_Report: aged trial balance by account and by segment, disputed amounts, specific provisions.
• Provision: editable loss rates for 5 buckets, disputed-invoice rate, provision matrix, required provision and the month-end movement for the journal.
• Credit_Monitor: use of limit, past-due balance, oldest days past due, flags and a suggested action per account.
• DSO_Trend: 12 month-ends of credit sales, collections, AR, DSO, best possible DSO, days delinquent, CEI and aging by bucket with a stacked chart.
• Dashboard: 11 KPI tiles, a check tile, aging chart, DSO trend chart and the top 10 debtors, printable on one page.
• README.txt and LICENSE-and-Terms.txt.

How it works

1. Enter the report date, aging basis, bucket limits and targets on Settings.
2. List accounts with terms, limits and any specific provision on Accounts.
3. Paste invoices with amounts, payments, last payment date and dispute flag on Invoices.
4. Set loss rates and the opening provision on Provision to get the month-end charge or release.
5. Work the Credit_Monitor actions and confirm the Checks tab shows ALL OK.

Details

• Format: one XLSX workbook, English.
• Software: Excel 2016 or later, or LibreOffice Calc. No macros, no add-ins.
• Any currency. Up to 400 invoices and 60 accounts as delivered.
• Delivery: instant download.

Good to know

• Sample data is illustrative and does not describe any hotel or account. Replace it with your own figures.
• Not accounting, legal or tax advice. Loss rates and the provision policy must follow your accounting framework: verify local requirements and agree them with your auditor.
• The month-end rebuild assumes each invoice's payments arrived on its last payment date.
• Single-user license.

FAQ

Can I age from the due date instead of the invoice date?

Yes. Switch the aging basis on Settings. Buckets, the provision matrix and the month-end aging trend all follow.

How are disputed invoices provided for?

Each disputed invoice gets the higher of the disputed rate and its bucket rate. Accounts with a specific provision % are taken out of the matrix so nothing is provided twice.

What is CEI?

The collection effectiveness index: how much of the money you could have collected in the month you did collect. Above 80% is a common target; the target is editable.

How many invoices can it hold?

400 rows as delivered. Keep about 15 months of history so the 12-month DSO and CEI trend is complete.

Does it post the journal entry?

No. It gives the charge or release to post as bad debt expense and the closing provision to agree to the ledger.