Your credit line comes with a monthly chore. After the books close, someone exports the receivables aging and the inventory report from QuickBooks, pastes them into the bank's spreadsheet, works out which customers don't count this month, and fixes the formulas that broke since last time. Then someone signs it. If it's wrong, the lender's next field exam may find it.
A borrowing base certificate is the same arithmetic every month. Take total receivables and inventory from your books, subtract what your loan agreement says doesn't count, and multiply what's left by the advance rates. It's slow to rebuild because those exclusions live in your loan agreement, not in QuickBooks. Write them down once, map each one to a report, and the certificate can come from your books the same way every time.
What is the monthly rebuild costing you?
Fill this in for the last certificate you sent:
| Your worksheet | Last month |
|---|---|
| Hours from month-end close to certificate sent | |
| People involved, including whoever checks it | |
| Corrections the bank or a field exam asked for this year | |
| Days of borrowing availability you waited on because the certificate was late |
The Secured Finance Network (SFNet), the secured-lending trade association, notes that reporting frequency varies by borrower and can be daily, weekly or monthly. A process that barely works monthly will not survive a move to weekly.
What goes on a borrowing base certificate?
Your bank's form sets the exact lines, but most follow the same shape:
- Gross receivables from your aging report.
- Less ineligible receivables, the amounts your agreement excludes.
- Eligible receivables × the receivables advance rate.
- Gross inventory at cost.
- Less ineligible inventory.
- Eligible inventory × the inventory advance rate, often capped at a dollar amount.
- Total borrowing base, limited by the line's maximum.
- Less the loan balance and any reserves your agreement sets, which leaves availability.
The Office of the Comptroller of the Currency (OCC) lists receivables commonly treated as ineligible in its handbook for bank examiners, Asset-Based Lending:
- receivables delinquent long enough to question whether they'll be collected (the handbook says an account is normally ineligible when past due by three times the terms, for example 90 days past due on 30-day terms);
- amounts over a single-customer concentration limit;
- receivables from affiliates;
- re-aged receivables;
- government receivables;
- foreign receivables;
- contra accounts, where you both sell to and buy from the same company;
- receivables owed by an insolvent customer;
- unbilled receivables.
It also describes cross-aging: many agreements make all of a customer's balance ineligible once some percentage of it is past the cutoff. On inventory, it notes that consigned goods are ineligible, and that agreements may exclude old or slow-moving stock or goods held at certain locations.
Your agreement may differ on every point. Its definitions section is the only rulebook that counts.
Why does it take so long every month?
- The rules are in a PDF, not the books. The certificate depends on the "eligible receivables" definition in your loan agreement. Often only the person who first built the spreadsheet has read it closely.
- The aging may not measure what the bank measures. Intuit says QuickBooks aging is measured from each transaction's due date, not the invoice date. If your agreement counts days from the invoice date, the columns don't line up with the rule, and someone adjusts by hand.
- Cross-aging needs a calculation per customer. It's the share of each customer's balance over the cutoff, and it changes every month.
- Contra accounts need two lists matched. A customer that is also a vendor often has slightly different names in the two lists, so the match is done by eye.
- One dollar, two rules. An invoice can be both past due and part of a cross-aged customer. Excluded twice, it understates your availability.
- Unapplied payments and credit memos. They change the aging total and the customer balances the rules are tested on.
How do you build it from QuickBooks Online yourself?
- Copy the rules out of the agreement. Make one numbered list: each ineligible category, its threshold (days, percentage or dollar cap), and each advance rate. Have your lender or attorney confirm anything ambiguous.
- Close the month first. Apply customer payments and credit memos before you pull any report.
- Run the aging as of month-end. In QuickBooks Online, go to Reports > Standard reports, find the Accounts receivable aging detail report under Who owes you, and set the report date to the last day of the month. Export it to Excel.
- Tie it to the balance sheet. The aging total should equal accounts receivable on your balance sheet on the same date. If it doesn't, stop and find out why before going further.
- Give each rule its own column. Mark each invoice with the first rule that excludes it, in a fixed order, so no dollar is excluded twice.
- Match customers to vendors for contra accounts. Keep the match list and reuse it next month.
- Pull inventory as of the same date. Intuit's reports article says the Inventory Valuation Summary report shows the quantity on hand, value and average cost for each product (look under Standard reports, in the Sales and customers group). Apply the inventory exclusions from your list.
- Keep last month's certificate beside this one. Big swings in any line deserve a one-line note before anyone signs.
When does doing it yourself stop working?
- The bank moves you from monthly to weekly certificates.
- You run more than one company file or warehouse, and the certificate combines them.
- A field exam finds errors, and the bank adds reserves or asks for more detail.
- Only one person can produce it, and the date it's due falls during their vacation.
What are your options?
| Option | What you get | What to watch |
|---|---|---|
| Do it yourself | The steps above, in your own spreadsheet | Rules applied by memory, and formulas that break quietly |
| Buy a tool | A borrowing base or lender-reporting app, or your lender's online portal | Whether it reads your agreement's exact rules, and how your data gets in |
| Hire a freelancer | A controller or bookkeeper who prepares the certificate each month | Whether the rules are written down, so someone else could do it |
| An outside team that learns how you work and builds on your data | Your current certificate studied first, then rebuilt from your books on a fixed set of written rules | You own the result, and every line is traceable to a report |
The Reports & answers page covers the last row. If you'd rather show us the certificate than describe it, a walk-through is 30 minutes on screen.
What can you hand off, and what should stay with your team?
| Hand off | Keep |
|---|---|
| Pulling the aging and inventory reports each month | Signing the certificate |
| Applying the written eligibility rules | Interpreting the agreement, with your lender or attorney |
| Tying the totals to your balance sheet | Decisions about credit to customers near the cutoff |
| Flagging big month-over-month changes | Conversations with the bank |
Questions to ask anyone you hire, us included
- Where will the eligibility rules be written down, and who confirms they match the agreement?
- How do you stop the same dollar being excluded twice?
- Will you rebuild last month's certificate first and show me any differences from the one I sent?
- What happens when the bank changes its form or a rule?
- Can my team produce it without you?
FAQ
What is a borrowing base certificate? SFNet describes it as a document the borrower prepares to show the status of its collateral and loan at a given point in time; in most cases it shows receivables and inventory.
Why doesn't my QuickBooks aging match what the bank counts as eligible? The aging lists everything owed, bucketed by due date. The bank counts only what your agreement calls eligible, and it may measure age differently, for example from the invoice date rather than the due date.
What is cross-aging? A rule in many agreements that makes a customer's whole balance ineligible once a set share of it is past the cutoff. Your agreement sets the percentage.
Can the certificate be produced automatically? The data pulls and the math can. Someone at your company still reviews it and signs it, and that should stay true.
Does this replace my CPA or my lender's field exam? No. A consistent certificate makes both easier, but it doesn't replace either.