Articles - Brew Ninja

Cost Tracking Spreadsheet for Beer Production at Your Brewery

Written by Bronwyn Lloyd | Sep 20, 2024 5:00:00 PM

Using a Spreadsheet to Track Cost of Goods Sold on Each Beer Your Brewery Brews

The Importance of Understanding the Cost of Goods Sold on Each Beer You Produce

Calculating and understanding the cost of beer production is crucial for any craft brewery. For craft breweries with tight margins, understanding COGs is especially important for boosting profitability and keeping operations running smoothly.

Understanding the cost to produce each beer you brew can help you to:

  • Properly price your beer
  • Manage your cash flow
  • Make better business plans and decisions

Typically cost of goods sold include direct costs like ingredients and packaging supplies, but some brewers also choose to include indirect costs like overhead rate and labor costs. 

Calculating COGs for Your Brewery in a Spreadsheet

What is the Cost of Goods Sold on Each Beer You Produce?

There are a few different ways to go about your cost tracking. First you'll need to decide what you want to calculate COGs for. 

  • Do you simply what to understand what each recipe costs to produce on average?
  • Are you looking to measure average costs per batch, brew over brew to track opportunities for improvement in your brewery production?
  • Or is your accountant looking to calculate the actual cost of every finished product you produce?

Regardless of what you are looking for, the information you'll need is pretty much the same. You'll need costs of all raw materials going into your beer, from malt, to hops, to yeast, and even any additional  ingredients that might be added later on in the brewing process. 

Start off with a simple spreadsheet with a separate sheet for each recipe. Outline what ingredients are needed for a typical batch of each recipe, with costs per unit included. Tally up all of your material costs for each recipe in a new column, and calculate the grand total at the bottom, this will be your average cost for a standard brew of this recipe. Since yeast can often be re-pitched, many people calculate roughly how many times they expect their yeast to be re-pitched and calculate an average cost from there. 

Understanding COGs of Your Finished Goods Inventory

Once you've calculated your recipe costs you can start converting that information into costs for each of your packaged goods, adding in the cost of any packaging materials used. Typically keg equipment costs aren't included at this stage since kegs are usually re-usable long term, but you will want to tie in the cost of any cans, bottles, etc. that you are using. You'll also want to track any wasted materials at this stage - did you have low fills or crushed cans? If so, record those quantities to capture more accurate COGs. 

If you work with an external canning company, you might not necessarily have a cost per can, but you likely know the cost of the average canning run and could work from there.  

Identifying Trends, Managing Cash Flow, and Improving Profitability

If you wanted to take it a step further you could allocate overhead and labour costs to your recipes too, but often times this is handled elsewhere in your accounting workflow. Once you know roughly what it costs you to produce each beer, you can start tracking costs batch over batch. To do this you would need to track exactly what ingredients were actually used in each brew. Over time you should be able to identify trends noticing what an efficient batch costs, versus one where you ran into issues. 

Now that you know the true cost of each craft beverage you produce, you can accurately calculate the value of your brewery's finished beer. This empowers you to create a robust and competitive pricing strategy, manage your cashflow more effectively, and make better decisions for your business overall - cheers to that!

 

Although cost of goods sold can be calculated in spreadsheets, or even using pen and paper, a brewery management software could make this process much easier. With Brew Ninja, cost of goods sold calculations become automated and are tracked on every brew, plus all the associated journal entries are created automatically in the background. Interested in learning more? Book a demo with our team!