Creating a cost of goods sold (COGS) spreadsheet for a café involves a detailed understanding of your costs and how they contribute to the overall expenses of running your business. Here’s a step-by-step guide to creating a comprehensive COGS spreadsheet for your café.
Step 1: Understand Cost of Goods Sold (COGS)
COGS refers to the direct costs attributable to the production of the goods sold in a company. This includes the cost of the materials and labor directly used to create the product. For a café, this typically means the cost of coffee beans, milk, syrups, pastries, and any other consumable items sold.
Step 2: Gather Your Data
Before creating your spreadsheet, gather all the necessary data:
- Invoices from suppliers for all ingredients and products.
- Labor costs specifically tied to the preparation of the products.
- Overhead costs such as utilities, rent, and equipment depreciation, though these are generally not included in COGS but are crucial for overall cost analysis.
Step 3: Organize Your Data
Your spreadsheet should have columns for each type of data. Here’s a suggested structure for the columns:
- Item Name: Name of the product or ingredient.
- Unit of Measure: How the item is measured (e.g., pounds, gallons, each).
- Quantity Purchased: The total amount of the item purchased.
- Cost per Unit: The cost for each unit of the item.
- Total Cost: Quantity Purchased * Cost per Unit.
- Usage: How much of the item is used per period (week/month).
- Cost of Goods Sold: Calculated based on usage and cost per unit.
Step 4: Create the Spreadsheet
Using a spreadsheet application like Microsoft Excel or Google Sheets, create the following columns:
A. Item Information
- Item Name: List each item you sell or use in your café.
- Unit of Measure: Specify the unit of measure for each item (e.g., pounds, ounces, gallons).
B. Purchase Information
- Quantity Purchased: Record the quantity of each item purchased.
- Cost per Unit: Enter the cost per unit of each item.
- Total Cost: Calculate the total cost for each item by multiplying the quantity purchased by the cost per unit.
C. Usage Information
- Quantity Used: Track the quantity of each item used during the specific period.
- Cost of Goods Sold: Calculate the COGS for each item by multiplying the quantity used by the cost per unit.
Example Layout:
| Item Name | Unit of Measure | Quantity Purchased | Cost per Unit | Total Cost | Quantity Used | COGS |
|---|---|---|---|---|---|---|
| Coffee Beans | Pounds | 100 | $10.00 | $1,000.00 | 50 | $500.00 |
| Milk | Gallons | 200 | $3.00 | $600.00 | 150 | $450.00 |
| Sugar | Pounds | 50 | $1.50 | $75.00 | 20 | $30.00 |
| Syrups | Bottles | 30 | $5.00 | $150.00 | 10 | $50.00 |
Step 5: Summarize Monthly or Quarterly Data
Summarize the data on a monthly or quarterly basis to get a clear picture of your expenses over time. This can help in identifying trends, managing inventory, and making informed decisions about pricing and purchasing.
Example Summary:
| Month | Total COGS |
|---|---|
| January | $1,000.00 |
| February | $1,200.00 |
| March | $950.00 |
Step 6: Analyze the Data
Analyze the summarized data to understand the following:
- Highest costs: Identify which items contribute the most to your COGS.
- Trends: Look for trends in your COGS over time to predict future costs.
- Efficiency: Determine if there are ways to reduce costs or improve efficiency.
Step 7: Adjust Pricing and Purchasing
Based on your analysis, adjust your pricing or purchasing strategies:
- Pricing: Ensure your menu prices cover your COGS and provide a profit margin.
- Purchasing: Optimize purchasing quantities to avoid overstocking or shortages.
Example Analysis:
- Highest Cost Items: Coffee beans and milk.
- Trend: COGS increases in winter due to higher coffee sales.
- Adjustment: Consider bulk purchasing discounts for coffee beans.
Step 8: Regular Review and Update
Regularly review and update your spreadsheet to reflect changes in prices, suppliers, and menu items. This ensures your data remains accurate and useful for decision-making.
Additional Tips:
- Use Software: Consider using specialized software for inventory and COGS tracking to automate calculations and generate reports.
- Track Waste: Include a column for tracking waste to identify potential areas for cost savings.
- Monitor Supplier Prices: Regularly check and negotiate supplier prices to ensure you’re getting the best deals.
- Incorporate Overhead: While not typically included in COGS, including a section for overhead costs can provide a more comprehensive view of your café’s finances.
By following these steps, you can create a detailed and effective COGS spreadsheet for your café, helping you manage costs, optimize pricing, and ultimately improve profitability.
Detailed Walkthrough with Examples
To further illustrate the process, let’s go through each step in more detail with examples.
Step 1: Understand Cost of Goods Sold (COGS)
COGS includes all direct costs associated with the production of goods. For a café, this means:
- Ingredients: Coffee beans, milk, sugar, syrups, pastries, etc.
- Packaging: Cups, lids, napkins, etc.
- Direct Labor: Wages of baristas and kitchen staff involved in preparing the items.
Indirect costs like rent, utilities, and administrative salaries are considered overhead and are not included in COGS but are essential for overall financial health analysis.
Step 2: Gather Your Data
Start by collecting all invoices, receipts, and records of purchases for the ingredients and items used in your café. Ensure you have detailed records showing quantities and costs.
For example:
- Coffee Beans Invoice: 100 pounds at $10 per pound.
- Milk Invoice: 200 gallons at $3 per gallon.
- Sugar Invoice: 50 pounds at $1.50 per pound.
Step 3: Organize Your Data
Create a structure in your spreadsheet to capture this data. Here’s a more detailed breakdown of the columns:
- Item Name: Coffee Beans, Milk, Sugar, Syrups, Pastries, etc.
- Unit of Measure: Pounds, Gallons, Bottles, Each.
- Quantity Purchased: The total amount of each item bought.
- Cost per Unit: Price per pound, gallon, bottle, or each.
- Total Cost: Total expenditure on each item.
- Quantity Used: How much of each item is used in a specific period.
- Cost of Goods Sold: Cost corresponding to the quantity used.
Step 4: Create the Spreadsheet
Open Excel or Google Sheets and set up the columns as discussed. Enter the data for each item.
Example Data Entry:
| Item Name | Unit of Measure | Quantity Purchased | Cost per Unit | Total Cost | Quantity Used | COGS |
|---|---|---|---|---|---|---|
| Coffee Beans | Pounds | 100 | $10.00 | $1,000.00 | 50 | $500.00 |
| Milk | Gallons | 200 | $3.00 | $600.00 | 150 | $450.00 |
| Sugar | Pounds | 50 | $1.50 | $75.00 | 20 | $30.00 |
| Syrups | Bottles | 30 | $5.00 | $150.00 | 10 | $50.00 |
Step 5: Summarize Monthly or Quarterly Data
Add a summary section in your spreadsheet to calculate total COGS for each month or quarter. This helps in tracking trends and making informed decisions.
Example Summary:
| Month | Total COGS |
|---|---|
| January | $1,000.00 |
| February | $1,200.00 |
| March | $950.00 |
Step 6: Analyze the Data
Analyze your COGS data to find areas where you can save costs or improve efficiency. Look for patterns or unexpected spikes in costs.
Example Analysis:
- Highest Cost Items: Coffee beans are the most significant expense, followed by milk.
- Trends: Notice if there are seasonal fluctuations, such as higher costs in colder months due to increased coffee consumption.
- Efficiencies: If sugar wastage is high, consider portion control methods or alternative suppliers for better pricing.
Step 7: Adjust Pricing and Purchasing
Based on your analysis, make adjustments to your menu pricing to ensure you cover your costs and achieve desired profit margins. Also, refine your purchasing strategy to optimize inventory levels and reduce waste.
Example Adjustments:
- Pricing: If your COGS for a latte is $2.50, ensure the sale price covers this cost plus a profit margin. You might price it at $5.00.
- Purchasing: If coffee beans have bulk purchase discounts, consider ordering larger quantities if storage permits.
Step 8: Regular Review and Update
Keep your spreadsheet updated with the latest purchase and usage data. Regularly review it to ensure your café’s financial health is on track.
Regular Review Checklist:
- Update Costs: Enter new invoices and update prices as they change.
- Track Usage: Regularly record the quantities of items used.
- Review Trends: Look at your summary data monthly to catch any significant changes early.
Additional Tips:
- Use Software: Tools like QuickBooks or specialized café management software can automate many of these processes, saving time and reducing errors.
- Monitor Waste: Track waste to identify inefficiencies. Add a column for waste to your spreadsheet.
- Negotiate with Suppliers: Regularly check for better prices or bulk discounts with your suppliers.
- Overhead Costs: While not part of COGS, tracking overhead costs in a separate section helps in understanding total business expenses.
By diligently following these steps, you’ll have a robust COGS spreadsheet that helps you manage your café’s costs, improve profitability, and make informed business decisions.
