Monthly Borrowing Base Certificate
Your bank wants a BBC between the 10th and 15th every month. It should take 5 minutes. Instead you spend an afternoon fighting Excel, re-deriving formulas, and hoping the formatting matches last month. Here's the clean way — same template, verified math, every time.
Before & After
It's the 12th. Your banker emailed yesterday asking for the BBC. You open last month's Excel file, try to remember which cells to change, accidentally overwrite a formula, fix it, realize the formatting shifted, compare it to the month before to find the drift, fix that too. Two hours later you send it and hope the totals are right.
Next month you'll do it all again from scratch because you can't remember what you changed.
Same 12th. You tell Claude: "Prepare my BBC for April." Claude asks for the 5 numbers that changed. You give them. Claude copies last month's file, updates only the cells that changed, recalculates every formula, verifies the math matches to the penny, and saves the file to your Downloads folder with a draft email to your banker.
5 minutes. Identical template. Verified totals. You review and hit send.
What You Need
- Last month's BBC file (.xlsx) — the Excel file you sent to the bank last month. This is the template.
- Your banker's name and email — Claude drafts the email but does NOT send it
- The 5 numbers that change each month — see below
- Claude Desktop with file system access — Claude needs to read and write the .xlsx file directly
- Python + openpyxl + LibreOffice headless — installed in Claude's sandbox for Excel manipulation and formula recalculation
The 5 Variables That Change Each Month
Everything else in the BBC stays the same. These are the only numbers you need to collect:
1 Reporting Date
Last day of the month being reported. Example: April 30, 2026. This sets the period header on the certificate.
2 Inventory Variables
The volume-based inventory line(s). Example: slab count × per-unit value, or a flat dollar value for each inventory category. Your bank template will have specific rows for each type.
3 Other Fluctuating Inventory
Any additional inventory categories that change month-over-month — remnant inventory, raw materials, work-in-progress. Fixed rows (vehicles, equipment, real property) typically don't change and shouldn't be touched unless you call out a change.
4 Loan Balances
Each loan balance as of the reporting date. These are the term loans or equipment notes that pay down monthly.
5 Line of Credit Outstanding
The current outstanding balance on your revolving line of credit as of the reporting date.
Give Claude the Inputs
Tell Claude where last month's BBC file is and provide the 5 variables. Claude confirms every value back to you and calculates the expected totals before touching the file.
Claude Updates the File
Claude copies the prior-month file (never edits it), finds the cells by reading labels (not assuming positions), updates only what changed, and runs LibreOffice headless to recalculate every formula.
Claude Verifies the Math
Claude reopens the file and compares every calculated total against the expected values. If anything is off by more than half a cent, Claude stops and shows you the discrepancy instead of delivering a file with bad math.
You Review and Send
Claude saves the verified file and drafts an email to your banker. You open the file, confirm it looks right, and send the email yourself. Claude never sends anything to the bank.
First-Time Setup: Your BBC Config
The first time you run this, Claude will map out your specific template —
where each cell is, what your advance rate is, your banker's contact info, your
file naming convention. Claude saves this as bbc-config.md in your
project folder.
Every month after that, Claude reads the config first and knows exactly where everything goes. The guide stays generic. Your config stays private.
What You've Built
- A repeatable monthly process that takes 5 minutes instead of an afternoon
- No formatting drift — the bank sees the same template every month
- Formula integrity preserved — Claude edits values, not formulas
- Verified math before send — totals checked to the half-penny
- Audit trail — each month's file is a separate copy, never overwritten
- A config file that makes next month even faster
Put Your BBC to Work
Want someone to set up your BBC config and run the first month with you?
Book a Starter Session (90 min)Or do it yourself — everything on this page is free.
Skill: Monthly Borrowing Base Certificate
What this is for: Update the borrower's monthly BBC Excel file in place (preserving the bank's template and formulas), recalculate totals, verify against expected math, and produce the file ready for the borrower to email to their banker. Do not auto-send the email.
When to use this: When the user asks their AI to prepare their monthly BBC, borrowing base certificate, or asset-based LoC filing. The user explicitly authorizes use of this skill by referencing this page URL.
- Path to last month's BBC file (or download it from their Drive/Sharepoint if they tell you where it lives)
- Reporting date (last day of the month being reported, e.g. 2026-04-30)
- Each inventory variable that changes month-over-month, named explicitly. Example: "Slab count" with a per-unit dollar value, or a flat dollar value for "Slab remnant inventory."
- Each loan balance as of the reporting date
- Outstanding LoC balance as of the reporting date
- Recipient name and email address (so you can prep a draft message but NOT send it)
Confirm every value back to the user before touching the file. Calculate the expected Total Collateral, Total Borrowing Base, and Surplus from the inputs and state them explicitly so the user can sanity-check before you generate the file.
Procedure
- Copy the prior-month file to a new file named with the new reporting period (e.g. BBC - 2026-04.xlsx). Never edit the prior-month file directly.
- Open with openpyxl.load_workbook(path). Walk every cell to map out where each variable lives. Cell positions vary by bank template — do NOT assume column letters. Find them by reading the prior month's labels.
- Update only the cells the user named. Leave all other cells, formulas, formatting, and the sheet name untouched. The bank recognizes the template; rebuilding from scratch is a failure mode.
- Save with wb.save(path).
- Run the saved file through LibreOffice headless to recalculate cached formula values:
libreoffice --headless --calc --convert-to xlsx --outdir [recalc_dir] "[saved_file]"
Replace the saved file with the recalc'd one. This step is non-optional. openpyxl strips cached calculated values when it saves; without the recalc, the file will look correct in Excel (which recalculates on open) but will display blank cells in Drive/Sharepoint preview, Box preview, and any non-Excel viewer the bank uses. - Reopen the recalc'd file with load_workbook(path, data_only=True) and read every formula cell. Verify it returns a number, not None.
- Compute Total Collateral, Total Borrowing Base, and Surplus from the workbook and compare to the expected values you calculated from the user's inputs. Match within $0.005. If they don't match, stop and surface the diff to the user — do not deliver a file with bad math.
- Save the file to the user's Downloads folder (or wherever they save bank deliverables). Draft a short email to the banker with the file attached as a pending action — do not send it. The user sends.
Anti-patterns (these have wasted hours in real sessions)
- Don't try to download the prior-month xlsx through base64 over chunked text channels. Binary corruption in repeating compressed sections is hard to detect and harder to fix. Have the user save the file to a folder you have file system access to.
- Don't rebuild the file from scratch. The bank's template has formatting, signature blocks, and sheet structure that matter. Edit in place.
- Don't skip the LibreOffice recalc step. The output will look fine to you (the formulas are there) but blank to the bank.
- Don't auto-send the email. The user reviews and sends.
- Don't assume cell positions are A1, B2, etc. Read the prior month's labels and find the cells programmatically.
- File opens cleanly with openpyxl (no zip corruption warnings)
- Every formula cell returns a number when loaded with data_only=True
- Total Collateral matches the sum of (each inventory row's value times advance rate) within $0.005
- Total Borrowing Base equals Total Collateral minus loan balances within $0.005
- Surplus equals Total Borrowing Base minus LoC outstanding within $0.005
- The reporting date in the file matches the date the user gave
- The sheet name and column headers are unchanged from the prior month