Catering Management System in Excel: Packages, Events, Payments, Kitchen Quantities, Margin and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: catering-management-system-template.xlsx: a 8-sheet system with example data and real formulas (Read me, Setup, Quantities, Events, Payments, Requirement, Report, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Rani Caterers, Solapur, as of 17 October 2026 (made-up data). Four packages from ₹350 to ₹650 a plate; 50% advance to confirm; 5% food buffer; GST on outdoor catering 5% (an input; confirm with your accountant).

10 events, 2,930 guests and ₹16,81,500 before GST with a 42.2% margin after food and staff. For a home function list, see the guest list and catering budget.

2. Build it sheet by sheet

Step 1. Setup and Quantities

1. B4 GST, B5 advance needed to confirm (50%), B6 extra food buffer (5%).
2. Packages in A9:C12 with rate and food cost per plate.
3. Quantities: items down column A, packages across row 4, raw quantity for ONE plate in the grid.

Step 2. Events: amount, payments and margin

1. A5 ="EV-"&TEXT(ROW()-4,"000"). H5 rate from the package with XLOOKUP and J5 amount =G5*H5+I5.
2. K5 GST =ROUND(J5*Setup!$B$4,0), L5 total, M5 paid with SUMIFS from Payments, N5 balance.
3. O5 food cost from guests × cost per plate, P5 staff and hire, Q5 margin =J5-O5-P5.

Step 3. Events: status

1. R5: =IF(E5<Setup!$B$3,IF(N5<=0,"Done","Collect balance"),IF(M5>=L5*Setup!$B$5,"Confirmed","Advance due")).
2. Colour Collect balance and Advance due red with formula-based formatting.

Step 4. Requirement: the shopping list

1. Type an event ID in B4; B5:B8 bring client, date, package and guests.
2. B10 per plate: =INDEX(Quantities!$B$5:$E$5,MATCH($B$7,Quantities!$B$4:$E$4,0)) (INDEX and MATCH).
3. C10 to buy: =ROUND($B$8*B10*(1+Setup!$B$6),1). Set the print area and hand it to the kitchen.

Step 5. Report and Dashboard

1. Report by event type: events, guests, amount, margin and margin %; rows 12 to 15 plates and food cost by package.
2. KPI cards: events, guests, amount, margin %, balance and events needing follow-up. Charts: margin by event type and plates by package.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. No buffer on quantities. Guests bring guests. A 5% buffer avoids running out of the main dish.
2. Quantities for the whole event typed in. Type quantity per plate once; the Requirement sheet scales it for any guest count.
3. Margin before staff cost. Cooks, servers and vessels can be 10% of the amount. Leave them out and margin looks too good.
4. Final guest count not updated. Change Guests when the client confirms; amount, food cost and the shopping list all follow.
5. Balance chased after the event. The status shows Advance due before the event; collect then.

5. More real examples

Example 1: Tiffin service. One row per customer per month with plates and a monthly rate, like the cloud kitchen system.

Example 2: Corporate canteen. Daily plates per company with a monthly bill using SUMIFS by date.

Example 3: Sweet orders for festivals. Packages become boxes; quantities per box drive the purchase list.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate catering quantities per guest in Excel?

Keep the quantity per plate for each package and multiply by guests × (1 + buffer), as in Requirement C10.

How do I pick a package column with a formula?

INDEX the row of the item with MATCH on the package name in the header row.

What GST applies to outdoor catering?

Outdoor catering is generally taxed at 5% without input tax credit in many cases; rules depend on the premises. Confirm with your accountant.

How do I show events where the advance is not in?

Before the event, compare paid with total × advance %; below it shows Advance due.

How do I work out margin per event?

Amount before GST − food cost − staff and hire, as in Events Q5.

7. Quick quiz

Ten questions on this page. Sign in to save your score.