A food-and-beverage minimum is not a price, it is a floor. Couples spend over it and never see it; couples who trim the guest list hit it and get a line on the invoice they did not expect. MAX with a zero floor makes that line appear exactly when it should and stay invisible the rest of the time.
A $12,000 Saturday minimum against 110 guests at $78 plus a $2,400 bar leaves a $1,020 shortfall on the contract.
The example
Three events on the same calendar, each with the minimum that goes with its date.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Event | Minimum | Guests | Per plate | Bar | F&B subtotal | Shortfall |
| 2 | Saturday in June | $12,000 | 110 | $78 | $2,400 | $10,980 | $1,020 |
| 3 | Friday in October | $8,000 | 140 | $65 | $1,800 | $10,900 | $0 |
| 4 | Sunday in January | $5,000 | 60 | $62 | $900 | $4,620 | $380 |
The formula
Build the subtotal first — the couple should be able to see what they spent before they see what they owe:
How it works
Why MAX rather than a plain subtraction:
C2*D2is the food: final guest count times the per-plate price. Use the guaranteed count from the contract, not the RSVP count — the venue bills the guarantee.+E2adds the bar. Whether the bar counts toward the minimum is a contract term, and it is the single most common thing couples and venues disagree about. If it does not count, leave it out of the subtotal and say so in the header.B2-F2is the gap. On the October event this is negative, which is not a discount — it just means the minimum was cleared.MAX(0,...)floors it. Without the floor, the October row shows a $2,900 credit and someone eventually pays it out.
Name the line item what it is on the invoice. “Food and beverage minimum shortfall” is a term in the contract; a surprise “room fee” is a chargeback waiting to happen.
Try it: interactive demo
Enter the contracted minimum, the guaranteed count, the plate price and the bar estimate.
Variations
Total billed either way
Add the shortfall back to the subtotal — the result is simply the greater of the minimum and what they spent, which is what the couple pays.
Guests needed to clear the minimum
Solve for headcount so a planner can tell a couple exactly how many more chairs make the shortfall disappear.
Pitfalls & errors
Service charge and tax almost never count toward a food-and-beverage minimum. If your subtotal includes them the shortfall comes out too small and the venue quietly loses the difference on every under-minimum event.
Show the shortfall on the proposal, not just the final invoice. A couple who can see that eight more guests eliminates a $1,020 line will usually invite eight more guests, which is better for everyone.
Do not let the shortfall go negative even internally. A negative shortfall looks like a credit in any downstream sum, and it will net against a real shortfall on another event in the monthly total.
Practice workbook
Frequently asked questions
Does the bar count toward the minimum?
Can a couple pay the shortfall in upgrades instead of cash?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class