Managing a restaurant or café can be a complex process that involves various tasks, from inventory management and employee scheduling to financial tracking and sales analysis. While advanced software systems like point-of-sale (POS) systems are commonly used for these tasks, spreadsheets offer a flexible, low-cost, and customizable alternative.
In this guide, I will walk you through how to manage key aspects of your restaurant or café using spreadsheets. While spreadsheets cannot offer real-time integration across your business processes like a POS system might, they can still be incredibly effective if designed well. Here’s how to make it work:
1. Inventory Management
Effective inventory management ensures that your restaurant or café runs smoothly without overspending on stock or running out of critical ingredients. You can use a spreadsheet to track inventory levels, manage reordering, and monitor costs.
Step 1: Create Inventory Tracking Sheet
Your inventory sheet should include the following columns:
- Item Name: The name of the item (e.g., coffee beans, flour, sugar).
- Category: Group the items by category (e.g., beverages, dairy, meat).
- Unit of Measure: Specify whether the item is measured by kg, liter, package, etc.
- Stock Quantity: How much of each item you currently have.
- Reorder Point: The quantity at which you should reorder the item.
- Supplier Information: The contact details of the supplier for easy reference.
- Unit Cost: The price per unit of the item.
- Total Cost: Formula:
= Stock Quantity * Unit Cost.
Step 2: Reordering Alerts
Create a simple conditional formatting rule to change the color of the “Stock Quantity” column when it falls below the “Reorder Point”. This way, you can visually identify when it’s time to restock.
Step 3: Monitor Usage
Create a separate sheet for daily inventory usage. Record how much of each item is used every day. Use this to identify trends, which will help you plan your purchases and avoid overstocking.
Benefits:
- Customizable categories based on your business needs.
- Allows you to analyze inventory turnover rates.
- Ensures cost control by giving you an overview of expenses.
2. Sales Tracking and Analysis
Tracking sales is one of the most important elements of managing a restaurant or café. You can use a spreadsheet to monitor daily sales, analyze peak sales periods, and identify which menu items are selling well.
Step 1: Create a Sales Tracker
Your sales tracking sheet should include:
- Date: The specific date for the sales data.
- Product/Item Sold: The name of the menu item.
- Category: Group items into categories (e.g., coffee, desserts, main dishes).
- Units Sold: How many units were sold of each item.
- Price per Unit: The price of each item.
- Total Sales per Item: Formula:
= Units Sold * Price per Unit. - Daily Sales Total: Sum up the “Total Sales per Item” to get your daily sales total.
Step 2: Analyzing Sales Trends
Create a weekly or monthly summary sheet to analyze trends:
- Total Revenue per Week/Month: Aggregate the daily sales totals for weekly/monthly figures.
- Best Selling Items: Identify which items sell the most frequently by creating a pivot table that summarizes units sold by product.
- Sales by Category: Use another pivot table to see how different product categories (e.g., beverages vs. desserts) contribute to your overall sales.
Benefits:
- Simple and scalable for any restaurant size.
- Helps you identify your top-selling items and the most profitable days.
- Can serve as a forecasting tool to predict future sales.
3. Employee Scheduling
Managing staff schedules can be time-consuming, but using a spreadsheet can help you plan shifts, track labor costs, and ensure appropriate staffing levels.
Step 1: Create an Employee Scheduling Sheet
Your employee scheduling sheet should include:
- Employee Name: List each employee.
- Role: Identify each employee’s role (e.g., barista, chef, cashier).
- Shift Date: The specific date of the shift.
- Shift Start Time: When the shift begins.
- Shift End Time: When the shift ends.
- Total Hours Worked: Formula:
= Shift End Time - Shift Start Time. - Hourly Wage: Record the wage for each employee.
- Total Shift Cost: Formula:
= Total Hours Worked * Hourly Wage.
Step 2: Ensure Proper Coverage
Create a summary that shows how many employees are working each shift. This will allow you to easily see if there are any gaps in your staffing plan. You can also color-code the shifts based on role, making it easy to see if you have enough baristas or chefs working at any given time.
Step 3: Track Time-Off Requests
Create a simple calendar section where employees can request time off. Use this to manage availability and avoid scheduling conflicts.
Benefits:
- Reduces the risk of scheduling conflicts.
- Provides an easy way to track labor costs.
- Flexible for last-minute changes and adjustments.
4. Cost Control and Expense Management
Keeping a close eye on your costs is crucial in the foodservice business. From fixed costs like rent to variable costs like ingredients, managing expenses is essential to maintaining profitability.
Step 1: Create an Expense Tracking Sheet
Your expense tracking sheet should include:
- Date: The date the expense was incurred.
- Expense Category: Classify the expense (e.g., rent, utilities, ingredients).
- Expense Description: A brief description (e.g., “Electricity bill for October”).
- Amount: The amount paid.
- Supplier/Service Provider: Who you paid (e.g., landlord, food supplier).
- Recurring or One-Time: Indicate whether the expense is recurring or a one-time payment.
Step 2: Total Monthly Expenses
Summarize your expenses by category at the end of each month. This allows you to see how much you’re spending on food, labor, utilities, etc., and gives you an overview of your total operating costs.
Step 3: Cost-to-Revenue Ratio
To calculate profitability, compare your total monthly expenses with your monthly sales using a simple formula:
- Profitability Ratio: Formula:
= (Total Monthly Sales - Total Monthly Expenses) / Total Monthly Sales.
Benefits:
- Gives you a clear picture of your financial health.
- Helps identify unnecessary expenses.
- Encourages better budgeting practices.
5. Menu Engineering
Menu engineering involves analyzing how profitable and popular each of your menu items is. A well-engineered menu increases revenue by emphasizing high-margin items.
Step 1: Create a Menu Analysis Sheet
In your menu analysis sheet, include:
- Item Name: The name of each menu item.
- Category: Group the items into categories (e.g., appetizers, beverages, mains).
- Cost to Produce: The cost to make each item (ingredients, labor, etc.).
- Sales Price: The price you sell each item for.
- Gross Profit per Item: Formula:
= Sales Price - Cost to Produce. - Units Sold: Total units sold during a specific period (daily, weekly, or monthly).
- Total Revenue per Item: Formula:
= Sales Price * Units Sold. - Total Profit per Item: Formula:
= Gross Profit per Item * Units Sold.
Step 2: Identify Your Stars and Plow Horses
Menu items can be classified into four categories based on profitability and popularity:
- Stars: High profitability, high popularity.
- Plow Horses: Low profitability, high popularity.
- Puzzles: High profitability, low popularity.
- Dogs: Low profitability, low popularity.
Use conditional formatting to automatically flag each item based on these metrics.
Step 3: Menu Adjustments
Once you’ve identified your stars and plow horses, adjust your menu:
- Promote Stars: Increase visibility (e.g., highlight them on your menu).
- Reevaluate Plow Horses: Try to reduce costs or increase prices without affecting customer satisfaction.
Benefits:
- Helps you focus on the most profitable items.
- Allows you to optimize your menu for customer preferences.
- Increases overall profitability.
6. Customer Feedback Tracking
Understanding customer feedback is key to improving your service and product offerings. You can use a spreadsheet to track comments, suggestions, and reviews.
Step 1: Create a Customer Feedback Sheet
Your feedback sheet should include:
- Date: When the feedback was received.
- Source of Feedback: Where the feedback came from (e.g., in-person, online review, social media).
- Customer Name (Optional): If available.
- Feedback Category: Service, food quality, ambiance, etc.
- Comments: The actual feedback provided by the customer.
- Action Taken: What action was taken in response to the feedback (e.g., “Improved service training”).
Step 2: Analyze Trends
At the end of each month, summarize the feedback to look for patterns. For example, if multiple customers comment on slow service, it might be time to retrain staff or adjust staffing levels during peak hours.
Benefits:
- Helps you stay on top of customer satisfaction.
- Allows you to make informed improvements.
- Keeps track of how customer feedback is being addressed.
Conclusion
Spreadsheets offer a flexible and cost-effective solution for managing a variety of tasks within your restaurant or café. While they may lack the real-time integration and automation provided by more sophisticated POS systems, they are an excellent starting point for small to medium-sized businesses looking to control costs, streamline operations, and improve profitability.
By creating and maintaining a well-organized set of spreadsheets, you can manage inventory, track sales, schedule employees, control expenses, engineer a profitable menu, and respond to customer feedback with greater efficiency. If you set up these spreadsheets correctly, they will give you a clear view of how your business is performing and where improvements can be made.
