Cafe Management System V1.0 in Excel VBA

The Cafe Management System runs a small cafe or coffee shop from one macro-enabled Excel file: orders with guest checks and kitchen tickets, menu costing, ingredient stock, purchases from suppliers, staff, expenses and profit. It has 15 worksheets, 10 VBA forms, 4 pivot dashboards, a live Profit & Loss statement and 3 printable documents that come out at A4, 4 inch and 3 inch. Nothing is typed into a data sheet. Every order, purchase bill, menu item and expense goes in through a checked form.

Watch the Cafe Management System V1.0 in Excel VBA walkthrough

Sample data ships with 1,525 cafe orders (3,200 order lines), 140 purchase lines and 176 expense vouchers, across 20 menu items, 16 ingredients, 6 suppliers and 8 staff, so every chart is populated the moment you open it. Replace it with your own and the whole system follows. If you are comparing cafe management software and want something without a monthly fee, this cafe management system keeps the data on your own PC. It covers cafe inventory management down to the par level, works as a menu costing template with food cost % on every item, and gives you coffee shop management software features in a single Excel workbook.

✅ Instant download · One-time payment · No subscription · No per-user fees · Lifetime access

🎁 Try it free for 7 days before you buy. Every form, dashboard and printout works in the free trial, so you can test it on your own PC first.

🔑 Key Features of the Cafe Management System

📌 Multi-item cafe orders. The New Order form is a basket. Pick the server, order type (Dine-in, Takeaway, Delivery or Online Pickup), table and day part, then type part of an item name, set the quantity and add it. The price comes from the menu and is locked. Every order gets its own ORD- number.

📌 Guest check and kitchen ticket. After saving, print the guest check for the customer and the kitchen order ticket for the bar and kitchen. The ticket lists each item with its station and quantity and carries no prices.

📌 Menu costing and food cost %. Each menu item holds its selling price and recipe cost. The sheet works out the food cost %, the contribution margin, units sold, revenue and gross profit straight from the orders.

📌 Menu engineering classes. Every item is classed as a Star, Plowhorse, Puzzle or Dog from its sales and its margin, so you can see what to promote, what to re-price and what to drop.

📌 Ingredient stock with par levels. Opening stock plus purchased quantity minus used quantity gives the stock in hand. Against each par level the status reads In Stock, Reorder Now or Out of Stock, and the MAIN page counts what needs reordering.

📌 Purchase bills from suppliers. A purchase basket works like the order basket: choose the supplier, add ingredients with quantity and unit cost, and record the bill status and received status. Print a purchase order for the supplier.

📌 Staff and expenses. The Staff Register keeps role, shift, hourly rate and hire date, with the order lines served and sales handled by each person. The Expense Register records rent, wages, utilities, card fees, permits and the rest under EXP- voucher numbers.

📌 Four dashboards and a live Profit & Loss. Sales, Menu, Inventory and Expense dashboards are driven by slicers. The Profit & Loss shows twelve months and a year total, from net revenue and cost of goods sold to net profit and net margin, with a sales tax memo.

📌 Printing that fits the counter. Guest Check, Kitchen Order Ticket and Purchase Order print from the same data at A4, 4 inch (104 mm) and 3 inch (80 mm). Paper size and what the print button does (preview, print or save as PDF) are set on the Settings sheet.

📌 Type-to-search lists and locked dates. Menu item, server and supplier lists filter as you type, and date boxes open a calendar picker, so 03/04 never means March on one PC and April on another.

📦 What’s Inside the Cafe Management System

Fifteen worksheets, in the order you work in them.

MAIN – the menu screen

The file opens here every time. Five live KPI cards (Net Sales, Orders, Avg. Check, Food Cost % and Reorder / Out) sit above eight colour-coded cards: Orders, Menu, Purchase, Ingredients & Suppliers, Staff & Expenses, Dashboards, Accounts and Print & Setup. Thirty-two buttons, no menu hunting.

Cafe Sales Dashboard

Net Sales, Items Sold, Profit After Food Cost, Discount Given and Sales Tax Collected, with Net Sales by Month, Share of Net Sales by Menu Category, and Net Sales and Gross Profit by Server. Slicers for Order Type, Day Part and Payment Mode drive every figure and chart.

Menu Performance Dashboard

Menu Revenue, Units Sold, Menu Gross Profit, Avg. Food Cost % and Menu Items, with Revenue by Menu Category, Share of Gross Profit by Menu Class, and the Top 10 Menu Items by Units Sold. Filter by menu category, menu class or status.

Ingredient Inventory Dashboard

Stock Value on Hand, Units In Hand, Ingredients Tracked, Units Used and Units Purchased, with Stock Value by Ingredient Category, Share of Stock Value by Supplier, and Stock In Hand against Par Level by Ingredient. Slicers for ingredient category and stock status.

Expense Dashboard

Total Expenses, Expense Net of Tax, Vouchers, Avg. Voucher and Sales Tax Paid, with Expenses by Month, Share of Expenses by Payment Mode, and Expenses by Category. Filter by payment mode or bill status.

Profit & Loss statement

A live formula statement: twelve months and a year total, from gross sales and discounts through cost of goods sold and gross margin, every operating expense category, down to net profit and net margin. A tax summary at the foot shows tax collected on sales against tax paid on purchases. The Financial Year dropdown picks the year.

Order Register – the main ledger

One row per order line under its ORD- number: date, day part, order type, table, server, menu item, category, quantity, unit price, discount, sales tax, net amount, food cost and gross profit. New Order, Edit Order, Delete Order, Guest Check and Kitchen Ticket sit on the action bar.

Menu & Costing

Every menu item with its MN- code, category, selling price, recipe cost, food cost %, contribution margin, sold quantity, sales revenue, item gross profit and menu class.

Purchase Register

Supplier bills under PB- numbers, one row per ingredient line, with quantity, unit cost, tax, grand total, bill status (Paid, Pending or Overdue) and received status. Print Order turns any bill into a purchase order.

Ingredient Stock

Each ingredient with its unit, unit cost, opening stock, purchased quantity, used quantity, stock in hand, par level, stock status, stock value and supplier. Purchases add to stock by themselves; usage is recorded with the Edit / Record Usage button.

Supplier Register

Contact person, phone, email, city and payment terms (Cash on Delivery, Net 7, Net 15 or Net 30), with the ingredients supplied, purchase lines and purchase value worked out for you.

Staff Register

Role, shift, hourly rate, hire date and contact details for each staff member, plus the order lines served and sales handled from the Order Register.

Expense Register

Every running cost under an EXP- voucher number: date, category, description, paid to, amount, tax, payment mode and bill status. It feeds the Expense Dashboard and the Profit & Loss.

Category Master

The dropdown lists in one place: tables, menu categories, ingredient categories, staff roles and cities. Add or remove a value with Manage Dropdown Lists and it shows up in the forms straight away.

Settings

Cafe name, licence and tax numbers, address, currency code and symbol, sales tax rate, document prefixes, edit and delete permissions, and the printing defaults. Apply Currency & Tax updates every money column at once.

📊 Cafe Management System vs. a Cafe Spreadsheet vs. Cloud Cafe POS Software

For a single-counter cafe that wants orders, menu costing, stock and profit in one file without a monthly subscription, this is the sweet spot. If you need card-terminal payments, online ordering or several tills at once, a cloud POS is the better buy.

👥 Who This Template Is For – and Who It’s Not For

✅ This template is built for:

• Small cafes, coffee shops, bakery cafes and kiosks that take orders at one counter
• Owners who want to know the food cost and margin of every menu item
• Cafes that reorder milk, beans and cups every week and want a par-level warning
• Owners who want monthly profit, staff sales and expenses without paying for software every month

❌ This template is NOT for:

• Teams that need several people entering orders in the same file at the same time – it is a single-user desktop workbook
• Mac or Excel-for-web users; the forms are VBA and need Excel for Windows with macros enabled
• Cafes that need card-terminal payments, online ordering or a delivery-app connection
• Anyone who needs payroll, time clocks or tax-return filing – the Staff Register records rates and sales, not pay runs

⚙️ How to Use the Cafe Management System

1. Download the ZIP, extract it, open the .xlsm file and click Enable Content so the VBA runs.
2. Go to Print & Setup > Settings & Paper Size and enter your cafe details, currency symbol, sales tax rate and printing defaults, then press Apply Currency & Tax.
3. Open Manage Dropdown Lists and set your own tables, menu categories, ingredient categories, staff roles and expense categories.
4. Add your menu items with their recipe cost, your ingredients with par levels, your suppliers and your staff.
5. Press New Order at the counter, add the items, save, and print the guest check and kitchen ticket.
6. When stock arrives press New Purchase Bill; record usage on Ingredient Stock and running costs with New Expense.
7. Open the dashboards and the Profit & Loss. Delete the sample rows when you are ready – the reports refresh themselves.

💼 Real-World Use Cases

A neighbourhood coffee shop with one till. The barista opens New Order, adds two lattes and a croissant for Table 4, saves, prints a 3 inch guest check and sends the kitchen ticket to the bar. At close the owner checks net sales and profit after food cost on the Sales Dashboard.

An owner fixing the menu. The Menu Dashboard shows which items are Stars and which are Dogs. A Dog with a 27% food cost gets a new price on Menu & Costing, and its margin updates on the spot.

A cafe that keeps running out of oat milk. Ingredient Stock flags Oat Milk as Reorder Now against its par level. New Purchase Bill records twelve cartons from the dairy, the Purchase Order prints for the supplier, and the status goes back to In Stock.

❓ Frequently Asked Questions

What does the Cafe Management System track?

Cafe orders, menu items and their costing, ingredients and stock, suppliers, purchase bills, staff and expenses. Four dashboards and a live Profit & Loss statement are built on top of those registers.

Does it print a guest check and a kitchen ticket?

Yes. Guest Check, Kitchen Order Ticket and Purchase Order each print at A4, 4 inch (104 mm) or 3 inch (80 mm). Choose the paper size and whether the button previews, prints or saves a PDF on the Settings sheet, or print any document by number from the Print Centre.

Does selling an item take ingredients out of stock automatically?

No. Purchases add to ingredient stock automatically, and the used quantity is recorded on the Ingredient Stock sheet with Edit / Record Usage. Menu items carry a recipe cost for food cost %, not a full recipe that deducts each ingredient.

Can I change the currency and tax rate?

Yes. The sample cafe uses US dollars and a 6% sales tax. Enter your own currency code, symbol and tax rate on the Settings sheet and press Apply Currency & Tax. Use a whole-number tax rate (for example 6% or 8%); the order basket in this version rounds the tax % to a whole number.

Can I stop staff editing or deleting records?

Yes. Allow Record Update and Allow Record Delete on the Settings sheet switch editing and deleting off, and the data sheets are protected with a password the macros use. There are no separate user logins – it is a single-user desktop workbook.

Does it work on Mac or Excel for the web?

No. The entry forms and the printing engine are VBA, so it needs Microsoft Excel 2016 or later for Windows on the desktop, with macros enabled. It will open elsewhere but the buttons will not run.

Can you customise it for my cafe?

Yes. Write to [email protected] with what you need changed and we will reply with a quote.

👤 About the Author

Built by PK – Microsoft Certified Professional with 15+ years of Excel, Google Sheets and Power BI experience, and founder of NextGenTemplates. Every template is hand-built and tested before release. Follow the tutorials on YouTube: @PKAnExcelExpert, @NextGenTemplates, @NeoTechNavigators.

🔗 Explore Related Templates

💎 Best value: the VBA Management Systems Mega Pack bundles five complete Excel VBA systems for less than buying them separately.

• Restaurant Management System V2.0 in Excel VBA – for a full-service restaurant.
• Coffee Cafe POS Web App – a multi-user, browser-based option for cafes.
• General Store Billing Management System V1.0 in Excel VBA – the same engine for a shop counter.
• Cafe Daily Sales Data Entry System in Excel – a lighter daily sales log.
• Coffee Chains Dashboard in Power BI – for multi-outlet reporting.

📖 Read the full walkthrough on the blog: Cafe Management System in Excel VBA.