Bakery Management System in Excel: Recipe Costing, Daily Production, Sales, Waste and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: bakery-management-system-template.xlsx: a 8-sheet system with example data and real formulas (Read me, Setup, Recipes, Production, Sales, Report, Costing card, 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

Shraddha Bakery, Pune, 1 to 17 October 2026 (made-up data). Seven ingredients, five products, two channels (Counter and Order).

Sales are ₹1,89,110 and profit ₹97,102 for the period. For scaling one recipe to more servings, see the recipe cost calculator; this page runs the whole bakery.

2. Build it sheet by sheet

Step 1. Setup: prices, overhead and the waste limit

1. In B3 type the as-of date, in B4 the overhead per unit for gas and packing (₹3) and in B5 the waste alert level (8%).
2. Type the ingredients in A8:C14 with Ingredient, Unit, Price per unit. When butter goes up, change C10 once and every recipe cost updates.

Step 2. Recipes: cost of one unit

1. Row 4 repeats the ingredient names (=Setup!A8 and across) and row 5 their prices, so the grid always matches Setup.
2. From row 7, one product per row: name, selling price in B, then the quantity of each ingredient for ONE unit in C:I.
3. In J7 work out the cost: =SUMPRODUCT($C$5:$I$5,C7:I7)+Setup!$B$4. SUMPRODUCT multiplies each quantity by its price and adds them up.
4. In K7 the margin =B7-J7 and in L7 the margin % =K7/B7. Pav earns 50%, muffins only 41%.

Step 3. Production and Sales: one row per entry

1. Production headers: Date, Product, Qty baked, Cost. In D5: =C5*XLOOKUP(B5,Recipes!$A$7:$A$11,Recipes!$J$7:$J$11,0).
2. Sales headers: Date, Product, Qty sold, Channel, Amount. In E5: =C5*XLOOKUP(B5,Recipes!$A$7:$A$11,Recipes!$B$7:$B$11,0).
3. Pick the product and channel from drop-downs so spellings match the Recipes list.

Step 4. Report: waste and profit per product

1. B5 baked =SUMIFS(Production!$C$5:$C$1000,Production!$B$5:$B$1000,A5) and C5 sold the same way from Sales (SUMIFS).
2. D5 waste =B5-C5 and E5 waste % =IFERROR(D5/B5,0).
3. F5 sales, G5 cost and H5 profit =F5-G5. I5 flags the product: =IF(E5>Setup!$B$5,"Check","OK").
4. Rows 12 to 19 show the last 7 days of sales and rows 21 to 23 sales by channel.

Step 5. Costing card: print one product

1. Type a product in B4 of Costing card. B7 brings its quantity: =XLOOKUP($B$4,Recipes!$A$7:$A$11,Recipes!C$7:C$11,0) and D7 the cost =B7*C7.
2. For Cake 500 g the card shows butter ₹52 and eggs ₹28 as the big items, and ₹113.75 in all. Set the print area to A1:D16.

Step 6. Dashboard: one look each morning

1. KPI cards: Sales =Report!F10, Profit =Report!H10, Margin %, Waste %, Best seller =INDEX(Report!A5:A9,MATCH(MAX(Report!F5:F9),Report!F5:F9,0)) and products to check.
2. Chart 1: sales by product. Chart 2: sales over the last 7 days.

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. Typing recipe quantities for a whole tray. Recipes work per ONE unit. A tray of 12 needs the tray quantity divided by 12, or the cost is 12 times too high.
2. Hard-coding prices inside the recipe. Keep prices on Setup. A price typed into a formula never updates when the market moves.
3. Forgetting the overhead. Gas, electricity and boxes are real costs. Leave B4 at 0 and every margin looks better than it is.
4. Logging returns as negative sales. Leftover pieces are waste, which the Report already counts as baked − sold. A negative sale counts them twice.
5. Different spellings of a product. "Pav" and "Pav (6)" are two products to SUMIFS. Pick names from the drop-down.

5. More real examples

Example 1: Sweet shop. Price per kg instead of per piece: quantities in Recipes per kg, sales in kg with one decimal.

Example 2: Home baker taking orders. Use only the Order channel, add a Delivery date column and filter Sales by date for the day's list.

Example 3: Biscuit unit. Add a Lot no column to Production and track rejects per lot, as in the manufacturing rejects project.

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 the cost of a recipe in Excel?

Put ingredient prices in one row and the quantity per unit in the row of each product, then =SUMPRODUCT(prices,quantities) plus any overhead.

How do I find bakery waste percentage?

Waste = baked − sold for the period, and waste % = waste ÷ baked. Here that is 187 ÷ 3,540 = 5.3%.

Should prices include GST?

The template uses prices as charged at the counter. GST on bakery items depends on the item and your registration; confirm with your accountant before adding a tax column.

Can I change an ingredient price mid-month?

Yes, but past costs will also change because Recipes uses today's price. Note the date of the change, or copy the old cost into a separate column first.

How do I see sales per channel?

SUMIFS on the Channel column, as in Report rows 21 to 23.

7. Quick quiz

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