The case of the missing cash: a CFO's afternoon in Excel

Tom Bauer had turned a profit eleven months running and couldn't tell you why his bank line was pinned to the ceiling. "We're profitable," he said, sliding his laptop across the table. "So where's the cash?" On the screen was his whole year, exported from the accounting system into one heroic, unreadable spreadsheet — numbers typed over numbers, a tab named "Final FINAL v3." The bank wanted a refinance package by Friday. It was Tuesday afternoon.

Mara had seen this spreadsheet a hundred times. Not Tom's — everyone's. A profitable business that can't breathe is the most common mystery in small-company finance, and you crack it the same way every time: about a dozen Excel formulas, used in the right order. Here's how the afternoon went.

First, stop matching by hand — XLOOKUP

The chart of accounts lived on one tab; the balances on another, in a different order, because of course they did. Tom had been eyeballing them across two windows. Mara's first move on any messy file is to stitch the tables together, so a number always knows its own name.

=XLOOKUP("A/R", accounts, balances)pulls a value out of a table by its key, and it replaced VLOOKUP for good reasons: you don't count columns, it can look to the left, it defaults to an exact match, and it has a not-found answer built in. VLOOKUP's column-number argument silently breaks the moment someone inserts a column — and you find out from a wrong number on a board slide, not an error. (On an older Excel, =INDEX(return_range, MATCH(value, lookup_range, 0)) does the same job, just wordier.)

Eight thousand rows, one question — SUMIFS

Next, the transaction dump: eight thousand rows of every dollar that moved all year, which is to say, no view at all. Tom couldn't tell you what he'd spent on marketing without scrolling for an hour. Mara needed spend by department, by month — a real management view — without touching the raw data underneath it.

That's SUMIFS, the engine of every management report. =SUMIFS(amount, department, "Marketing", month, "Jun") totals exactly the rows that match. The trick that makes it powerful: point the criteria at cells, not typed-in text, and one formula filled across a grid builds an entire actuals-by-month-by-account table that re-totals itself the instant the data changes. (Its cousins COUNTIFS and AVERAGEIFS count the invoices past sixty days and average the deal size by rep the same way.) Within a minute the pile was a table — and marketing had quietly crept up forty percent.

The price that "held steady" — SUMPRODUCT

"But the prices held," Tom insisted. They hadn't — not the way that matters. The list prices held; the mix shifted, and the cheap, fast-moving product carried more of the volume. You can't see that by averaging the price column — you have to weight each price by how much of it actually sold.

That's SUMPRODUCT: multiply two columns, then add, in a single cell. =SUMPRODUCT(units, price) is the true revenue from the mix; =SUMPRODUCT(units, price) / SUM(units)is the weighted-average price. Tom's list prices averaged sixty-two dollars; weighted by what actually sold, he was realizing fifty-four. That eight-dollar gap, across a year of volume, was most of his "missing" margin. Reach for SUMPRODUCT whenever the question has an "and then multiply" hiding inside it.

Make the calendar behave — EOMONTH and EDATE

To compare anything month over month, Mara needed a clean monthly spine — and Tom's had a column headed "Apr 31," a date that has never existed. Typing dates by hand is how April grows a thirty-first day. =EOMONTH(start, 0) returns the true last day of the month; drag it across and the header row always lands on the right day. =EDATE(start, 12) jumps exactly a year — the right way to roll a contract or a loan forward without an off-by-a-day error compounding down the model. Thirty seconds, and the timeline was real.

The row that told the truth — growth, margin, and a markup trap

With a clean spine, Mara dropped a single growth row under revenue and dragged it across. The story jumped off the screen: revenue climbing, margin sliding underneath it. None of this is fancy — it's arithmetic — which is exactly why it's worth getting precisely right, because a CFO says these numbers out loud, in rooms, and a sign error gets repeated.

• Period growth: (this_period / last_period) - 1, formatted as a percent.
• CAGR (compound annual growth): (end / start)^(1 / years) - 1 — the honest way to describe multi-year growth, because it smooths the lumpy years instead of cherry-picking two of them.
• Margin: profit / revenue. Gross, operating, net — same shape, different profit line on top.
• Markup is not margin— the one that had quietly bitten Tom. Margin is profit over price; markup is profit over cost. He'd been quoting a 33% markup as a 33% margin: a $40 item that cost $30 is a 33% markup but only a 25% margin. He thought he kept more of every sale than he did.

Should Tom buy the forklift? — XNPV, XIRR, PMT

Two decisions were on the table: refinance the line into a term loan, and — while he was at it — put a $30,000 forklift on a credit card. The time value of money settles both, with two warnings that save real money:

• Use XNPV and XIRR, not plain NPV and IRR. The plain versions assume your cash flows land in tidy, equal periods. Real ones don't — a deposit now, a payment in seven weeks, a balloon in March. =XIRR(amounts, dates) and =XNPV(rate, amounts, dates) take the actual dates and are simply more correct.
• IRR flatters early cash and hides scale.A project can post a gorgeous IRR on a trivial dollar return. Read it next to XNPV — which answers "how many dollars of value" — never on its own.

=PMT(rate, periods, amount)sized the term-loan payment in one cell, and it fit the cash flow. The forklift-on-a-card, run through XNPV at the card's real rate, did not — while the warehouse racking Tom had been putting off paid back twice as fast. Pressure-test the financing before you sign, not after.

Don't hide the clues — IFERROR

Mara's growth row threw a #DIV/0! in the first month — no prior period to divide by. =IFERROR(formula, "—") swapped the red error for a clean dash. But she left the account lookups alone, on purpose. The discipline is to wrap meaning, not mistakes: IFERROR around a calc that's expected to hit a harmless edge case is tidy; IFERROR around a lookup that should always find a match doesn't fix the model, it gags it. A missing match there isn't noise — it's the exact clue you're hunting for.

Where the cash actually went — FILTER, UNIQUE, SORT

Now the reveal. The cash wasn't missing — it never is. It had turned into the two things that don't spend: receivables and inventory. To prove it, Mara filtered the open invoices for everything past sixty days, pulled the unique customers, and sorted by size — three formulas, no copy-paste.

=FILTER(invoices, days_open > 60) spilled every late invoice live; =UNIQUE(customers) deduplicated the names; =SORT(...)ranked them biggest first. One customer — Tom's largest, the one he'd never dream of chasing — was sitting on ninety days of unpaid invoices. Add the pallets of slow stock SUMIFS had already flagged, and there was the entire "missing" credit line, parked in plain sight. Growth had eaten the cash and left it in the warehouse and the receivables ledger — the working-capital trap every growing business walks into. (Dynamic arrays like these spill automatically — they replace the copy, paste-special, remove-duplicates, re-sort ritual, the four steps where stale data sneaks back in.)

Friday, 5:00 — the one-pager

Mara handed Tom a single page: revenue and margin trended on a clean monthly spine, the weighted-price slip named, the cash tied up in receivables and inventory quantified to the dollar, and a refinance that fit the payment PMT had sized. The bank renewed the line. Tom put a collections call on his calendar instead of a forklift on his card.

None of it took a four-hundred-function wizard. It took about a dozen formulas, each pointed at the right question and wired into a layout you could actually read.

The shortlist, on one screen

Learn only these, wire them into a clean layout, and you've covered the vast majority of real finance work:

• XLOOKUP — pull a value from a table by its key, safely.
• SUMIFS / COUNTIFS / AVERAGEIFS — totals, counts, and averages by any condition.
• SUMPRODUCT — multiply-then-add; weighted averages and multi-criteria sums.
• EOMONTH / EDATE — period headers and date math that survive February.
• XNPV / XIRR / PMT — investment and financing decisions, with real dates.
• IFERROR — clean output where an error is expected.
• FILTER / UNIQUE / SORT — live lists without the copy-paste.