Cost tracking (Beware if your scared of numbers)

[wfowlks]

So, as I am advancing in my brewing I have BeerSmith, and I am updating all the prices of the products to match my LHBS. I was disappointed however, that BeerSmith does not provide a cost analysis tool, for equipment, electricity or propane, or to see the marginal cost of my equipment decrease over time.

Let me just state, I got into home brewing as a hobby, I like cooking and I have found home brewing to be relaxing... not just because of the alcohol.

But as a software engineer (and someone who has a fixation with numbers) and someone who has a hobby of economics etc, I was wondering if anyone had an excel sheet or a program that factored all this in before I made one myself.

Because I would just like to see what has served me well over time, because I am looking to start a microbrewery in the next 5-8 years or so, and I just wanted to get a baseline for my costs on a personal scale because then at a microbrew my projections wont be worse than my small batch home brew due to economies of scale.

Thanks for the input in advanced.

 

[Ryush806]

Are you wanting to factor in capital depreciation and all that other good stuff?

Also, are you continuing to buy more and more equipment? Haha I'm sure I would have broken even a long time ago if I could just STOP buying stuff!

 

[wfowlks]

I do buy new equipment on occasion. Like I just bought Mason jars for growing and storing yeast. But yes, I also wanted to factor in depreciation and etc. I am starting to make a sheet at the moment, and I will upload it once I'm done for review unless someone else points me to a tool that I can use before I finish.

 

[stevehardt]

If you do find such a tool or spreadsheet, please consider sharing it. I've also been curious about the economies of homebrewing. I'm at the point where I'm very happy with my equipment set up, and was wondering when I"ll hit the breakeven point...

 

[wfowlks]

I'm working on a primitive spreadsheet now, and I'm hoping to be able to post it tomorrow, and get some feedback and maybe update/modify it, to get the best possible sheet

 

[Ryush806]

I'll be happy to take a look at it. I'm engineer not an accountant so I'm probably not the most qualified but hopefully you'd consider me at least qualified.

 

[wfowlks]

Ok, so here is my excel sheet so far. I have done the numbers as I think they should be (remember I'm not an accountant).

https://docs.google.com/open?id=0B6F5yLl4sQDvSndhVUxLS2V4c1U

It is a link to Google Docs. You guys should be able to download a copy and modify it, however I made it so that you can't make changes to the golden copy. If thats not the case and you cant download it please let me know and I'll fix it.

Some notes about the graphs:
I have done a few of the charts with hidden cells. That was just to make them nice. The formulas are in the cells, and you can write in any of the hidden cells. I left ample space for adding items. But some of the formulas have constants so be careful when modifying the formulas.

Also to calculate the savings, I thought that Sam Adams Boston Lager was a fairly reasonably priced beer for comparison. You will need to change that value for your area.

Feedback is welcome, tell me if I messed something up, or if anyone can think of any improvements I will be happy to incorporate them and give you credit!



To download from Google Doc's: Under the title in the upper left hand corner, click on the file. Then all the way at the bottom of the file menu is a download option.

 

[helibrewer]

Nice...I'm too scared to add up my equipment costs though Plus I share my equipment for winemaking...BeerSmith should at least help with the variable costs, you could probably add Propane to Misc. in BeerSmith to to track it....even electricity on a per batch basis for the Keezer...but that starts to feel too professional to me...LOL I don't want to worry about cost.

 

[Ryush806]

A few questions for you:

1) What are you defining as fixed cost? It looks like your doing cost of equipment divided by number of batches made. What is the reason for tracking that?

2) How did you come up with your formula for propane cost?

I don't know if you plan on adding it later, but you should really have another sheet with the financial justification if you're planning on going pro eventually. Basically you need to see if the initial capital investment pays off versus keeping the money in an interest bearing account.

 

[wfowlks]

1. The fixed costs are the cost of equipment or in other words a "durable good". And barring some freak accident, you will be able to use whatever item it is until the foreseeable end of time.

The reason for dividing the equipment cost across the batches is because that is the Average Total Cost, or the cost of all durable goods needed for operation spread across each batch.

It is also a key component in figuring out the supply and demand. As the output increases and the fixed costs decrease and reach their minimum. The cost to produce one more batch of beer (marginal cost) is equal to the average cost (fixed + variable). And in other words you have justified the cost of your equipment or fixed costs, or at least recovered them.

2) I have a regulator on my propane tank, and I just got a new propane tank before my last batch (a double batch, 10 gallons), and the first time I got the tank i did a single batch (5 gallons). When I did the double batch, when I was done, 1/3 of the 15# propane tank was used. And with the single batch afterwards 1/4 of the 15# propane tank was used.

I had the propane tank because my landlord left it, and it costs 19.99 to exchange it at the local store (Blue Rhino). So, there were 2 parts of the equation. So the formula is an IF statement to check the number of batches, then it applies the given formula, based on the propane refill cost in the variable cost sheet.

Let X be the batch size

IF(X == 1) { cost = $19.99 / 4 }
ELSE { cost = $19.99 / 3 }

I could probably improve it by doing:

costPerGallon = (refillCost / tankLbs) * numLbsUsed

I'm not really sure what you mean by financial justification, are you talking in terms of the whole venture, or of buying a piece of equipment or something completely different all together?

 

[Ryush806]

The justification I'm talking about is somewhat similar to what I think you're trying to do with the fixed cost spread across all the batches. You could do it very simply by taking the total cost of your equipment and subtracting your savings each batch until you break even. After the break even point, the rest is "profit".

You can track it very simply like that if it's just for personal curiosity, but if you're going for owning your own business someday you have to get very deep into the money side of it all. It's entirely possible that even if you sell the beer for a good bit more than you made it, you won't ever make enough money to pay off your start up loan and the business would fail. You have to factor in taxes, capital depreciation, etc in the business world as well. I don't want to pee in your cheerios too much, but there's a huge amount of financials you have to be aware of to make yourself a successful business owner. I just want to make sure you're aware of it all.

All that being said, since you don't own a brewery (yet ), a lot of the complicated stuff could be done away with for now. You could look at your equipment purchases and see if the savings you get would earn more interest (assuming you invested them) than if you simply invested the equipment cost in the first place (and didn't buy the equipment at all). Haha that might not be anything at all like you were originally intending with this spreadsheet. As a chemical engineer trying to justify new equipment to improve processes as my job, this is how I tend to look at everything. I'll never be able to justify my hobby this way...but that's why it's a hobby for me. I don't want to worry about the money side of things at the moment.

 

[wfowlks]

Lol yeah,

This was just more for interest right now, pseudo business, just to see how it would go. Because the prices I have now for grain etc, would only go down since I would be buying bulk and currently I am buying per batch at the LHBS.

There are a lot more costs involved for full scale micro, however it could be done on a budget. Especially with the recent growth of craft beer. The local beer store, sells craft 5 gallon kegs and the cheapest one starts at 80, and they range up to about $140. I talked with the owner and they actually move quite a few of them, and of all price ranges.

But times change as with the beer market, no longer are the days of "one bud fits all".

And this was mainly just to see my "break even", and to justify the more expensive brews, like ones with fruit etc, and to show that in the long run there are savings, and quite a bit of them.

 

[Schmutzie]

As Ryush806 intimated, looking at the cost vs savings of your hobby is going to be quite a bit different than examining the viability of a new (for profit) business.

In examining your costs, there is a distinction between average and marginal - marginal is the cost of the next batch and should only include the costs that will be incurred by brewing that batch. From the perspective of marginal cost (i.e. looking forward to the next batch), the $1,400 or you already spent on equipment is essentially a sunk cost, that money is gone whether or not you ever brew again!

If you are looking to examine your 'fixed' purchases try to think in terms of average cost for the life of the equipment (i.e. projected number of batches) - after two batches, realizing that you have spend over $700 per batch on equipment could be depressing! Spending $1,400 on equipment that should last 100 batches is only $14 per batch use this info before the purchase to justify the expense (to yourself, SWMBO, or whomever) Having said that, knowing the average cost per batch you have done to date is interesting but unless you plan quitting the hobby now it is somewhat meaningless.

If you really want to complicate the mater, you could included the value of your time spent brewing as well as some monetary value to the 'utility' (enjoyment) you get from brewing - but that is getting very pedantic for a hobby.

From a commercial perspective, you'll want to compare the present value of all future net cash inflows to the initial cost of starting the project. (As Ryush806 mentioned above there is a ton of costs/expenses that must be factored in.)

I like your spreadsheet a lot, for my personalization of it I'll use the price of BMC beers as my comparison - my beers aren't that good enough to compare to craft brew yet!

-Schmutzie

 

[Ryush806]

So what I infer from Schmutzie is that in order to justify you hobby you need to...

BREW AS MUCH AS POSSIBLE!

Or at least as much as SWMBO will allow...

 

[ludomonster]

If you're doing this analysis from a hobby perspective, why would you consider depreciation? You don't get a tax break. The only this I could see you using depreciation for would be to estimate the return you would get by selling the equipment. I would think that things like kettles wouldn't depreciate much unless they are damaged. Also, you itemize tubing as a single cost. However, you probably repace your tubing periodically. This should be considered a recurring cost. There are other things that work like this: cleaners, sanitizers, finings, gaskets, brushes, bags, CO2. You'll also have costs that occur from time to time but not at regular intervals like hydrometers, buckets.

Finally, as someone who likes charts, I suggest you include a cumulative cost chart. But also, if you're really into charts, you should adjust for time value of money.

 

[wfowlks]

Good call on the cumulative cost chart. I will have to find the rest of my receipts etc. so that I can populate the dates and do a scatter plot for cumulative cost

 

[AmandaK]

I have a spreadsheet that tracks my equipment cost & ingredient cost and plot them in a nice table of "Cost per Sixer". It's an easy way to see how my beers compare with what I could buy in the store on a cost level.

I don't do deprecation, because it's my hobby, and I don't get tax breaks. Right now, I'm looking at ~$12/sixer for my last batch of Tripel Threat. And my CAP was around $10 a sixer. Not bad!

 

[landshark]

I have a spreadsheet that tracks my equipment cost & ingredient cost and plot them in a nice table of "Cost per Sixer". It's an easy way to see how my beers compare with what I could buy in the store on a cost level.

I don't do deprecation, because it's my hobby, and I don't get tax breaks. Right now, I'm looking at ~$12/sixer for my last batch of Tripel Threat. And my CAP was around $10 a sixer. Not bad!

If I ever told my SWMBO my last batch was $12 a sixer, my brewing operation would be shut down within the hour. I'll stick with simple ales and wheats.

 

[wfowlks]

I have a spreadsheet that tracks my equipment cost & ingredient cost and plot them in a nice table of "Cost per Sixer". It's an easy way to see how my beers compare with what I could buy in the store on a cost level.

I don't do deprecation, because it's my hobby, and I don't get tax breaks. Right now, I'm looking at ~$12/sixer for my last batch of Tripel Threat. And my CAP was around $10 a sixer. Not bad!

Does the $12 include the equipment?

 

[Schmutzie]

So what I infer from Schmutzie is that in order to justify you hobby you need to...

BREW AS MUCH AS POSSIBLE!

Or at least as much as SWMBO will allow...

To SWMBO "But Honey, we will be losing money if I don't brew this weekend!!"

 

[wfowlks]

Exactly

 

[Schmutzie]

Exactly

Hey wfowlks, any chance you were up watching the Sox/O's game?? As much as the extra innings loss pains me, it is great to see Baltimore winning this year - outside of Fenway, Camden Yards is my favorite park.

 

[wfowlks]

No, I didn't. I should let you know I just moved to Baltimore from MA, in January, so if I liked baseball, the Red Sox would be my team, but there is just to many games for me. Football is the only sport I follow currently, and its Patriots all the way... Someone slashed my tires this Friday night, I think because of the Patriots stickers on my car.

On another side note, I have not yet been to Camden yards. Its like $7 for a ticket. But I have been to lazy to go.