Creating a comprehensive Excel spreadsheet template for managing restaurant controllable costs is a great way to track and control expenses effectively. Controllable costs in a restaurant typically include food and beverage costs, labor costs, and other variable expenses like utilities, marketing, and supplies. Here’s a detailed guide to help you set up your spreadsheet:
1. Structure of the Spreadsheet
Your spreadsheet should be organized into multiple sheets or tabs, each dedicated to different aspects of your controllable costs. Here’s an outline of the structure:
- Summary Sheet
- Food Costs
- Beverage Costs
- Labor Costs
- Utilities
- Supplies
- Marketing
- Miscellaneous Expenses
2. Summary Sheet
The Summary Sheet will aggregate data from all other sheets to give you a comprehensive view of your total controllable costs.
Columns:
- Category
- Budget
- Actual Cost
- Variance (Actual – Budget)
- Percentage of Total Cost
Sample Data:
| Category | Budget | Actual Cost | Variance | Percentage of Total Cost |
|---|---|---|---|---|
| Food Costs | $5,000 | $4,800 | -$200 | 40% |
| Beverage Costs | $2,000 | $2,100 | $100 | 18% |
| Labor Costs | $6,000 | $5,900 | -$100 | 49% |
| Utilities | $1,000 | $1,050 | $50 | 9% |
| Supplies | $500 | $450 | -$50 | 4% |
| Marketing | $300 | $350 | $50 | 3% |
| Miscellaneous Expenses | $200 | $250 | $50 | 2% |
| Total | $15,000 | $14,900 | -$100 | 100% |
3. Food Costs
Track all expenses related to food, including purchases and waste.
Columns:
- Date
- Item
- Supplier
- Quantity
- Unit Price
- Total Cost
- Notes
Sample Data:
| Date | Item | Supplier | Quantity | Unit Price | Total Cost | Notes |
|---|---|---|---|---|---|---|
| 2024-07-01 | Chicken | Supplier A | 100 lbs | $2.50 | $250 | Weekly purchase |
| 2024-07-03 | Vegetables | Supplier B | 50 lbs | $1.00 | $50 | Organic produce |
| 2024-07-05 | Beef | Supplier C | 80 lbs | $3.00 | $240 | Special order |
4. Beverage Costs
Track all expenses related to beverages, including alcohol and non-alcoholic drinks.
Columns:
- Date
- Item
- Supplier
- Quantity
- Unit Price
- Total Cost
- Notes
Sample Data:
| Date | Item | Supplier | Quantity | Unit Price | Total Cost | Notes |
|---|---|---|---|---|---|---|
| 2024-07-01 | Wine | Supplier X | 20 bottles | $10.00 | $200 | Red wine |
| 2024-07-03 | Beer | Supplier Y | 100 cans | $1.00 | $100 | Local brew |
| 2024-07-05 | Soda | Supplier Z | 200 cans | $0.50 | $100 | Soft drinks |
5. Labor Costs
Track wages, salaries, and other labor-related expenses.
Columns:
- Date
- Employee Name
- Hours Worked
- Hourly Rate
- Total Cost
- Notes
Sample Data:
| Date | Employee Name | Hours Worked | Hourly Rate | Total Cost | Notes |
|---|---|---|---|---|---|
| 2024-07-01 | John Doe | 40 | $15.00 | $600 | Weekly wages |
| 2024-07-01 | Jane Smith | 40 | $15.00 | $600 | Weekly wages |
| 2024-07-01 | Jack Brown | 20 | $20.00 | $400 | Part-time chef |
6. Utilities
Track expenses for electricity, water, gas, and other utilities.
Columns:
- Date
- Utility Type
- Provider
- Total Cost
- Notes
Sample Data:
| Date | Utility Type | Provider | Total Cost | Notes |
|---|---|---|---|---|
| 2024-07-01 | Electricity | Power Corp | $300 | Monthly bill |
| 2024-07-01 | Water | Water Works | $100 | Monthly bill |
| 2024-07-01 | Gas | Gas Company | $150 | Monthly bill |
7. Supplies
Track expenses for non-food supplies like cleaning materials, paper products, etc.
Columns:
- Date
- Item
- Supplier
- Quantity
- Unit Price
- Total Cost
- Notes
Sample Data:
| Date | Item | Supplier | Quantity | Unit Price | Total Cost | Notes |
|---|---|---|---|---|---|---|
| 2024-07-01 | Napkins | Supplier P | 1000 | $0.05 | $50 | Monthly supply |
| 2024-07-03 | Detergent | Supplier Q | 10 | $10.00 | $100 | Cleaning supplies |
8. Marketing
Track expenses for advertising, promotions, and other marketing activities.
Columns:
- Date
- Campaign
- Medium
- Cost
- Notes
Sample Data:
| Date | Campaign | Medium | Cost | Notes |
|---|---|---|---|---|
| 2024-07-01 | Summer Promo | $100 | Social media ads | |
| 2024-07-03 | Flyer Dist. | Local | $50 | Print flyers |
9. Miscellaneous Expenses
Track any other variable expenses that don’t fit into the above categories.
Columns:
- Date
- Description
- Cost
- Notes
Sample Data:
| Date | Description | Cost | Notes |
|---|---|---|---|
| 2024-07-01 | Repair | $150 | Kitchen equipment |
| 2024-07-03 | Staff Event | $100 | Team building |
3. Setting Up Formulas
Variance Calculation: For each category in the Summary Sheet: Variance = Actual Cost - Budget
Percentage of Total Cost: Percentage of Total Cost = (Actual Cost / SUM(Actual Costs)) * 100
4. Data Validation and Conditional Formatting
Data Validation:
- Ensure data entered in date columns is in date format.
- Ensure quantities and costs are numeric.
Conditional Formatting:
- Highlight cells in the Variance column where the value is positive (over budget) in red.
- Highlight cells where the value is negative (under budget) in green.
5. Automating with Excel Functions
Use Excel functions to automate calculations and ensure accuracy:
SUMIFandSUMIFSto aggregate costs by category and date.VLOOKUPorINDEX-MATCHto fetch data from other sheets for summary calculations.IFERRORto handle errors gracefully in formulas.
6. Regular Updates and Review
Ensure the spreadsheet is updated regularly, ideally daily or weekly, to maintain accurate tracking of expenses. Schedule periodic reviews (e.g., monthly or quarterly) to assess performance against the budget and make necessary adjustments.
7. Template Design and Aesthetics
Make the template user-friendly with clear headings, color-coding for different categories, and instructions for users. This will help ensure consistency and ease of use.
Regularly updating and maintaining this spreadsheet will help you keep your controllable costs in check and improve your restaurant’s profitability.
