Early Lessons, Part 6a: Calculate the Cost of Homemade: Ingredient costs

For the previous posts in this series click Part 1, Part 2,  Part 3Part 4, and Part 5.  This post contains affiliate links.  If you make a qualifying purchase after clicking a link, we may receive a percentage of the purchase price.

When I was growing up, one underlying assumption of saving money on groceries is that it was less expensive to make food yourself than it was to buy processed foods.  Frugal writers often issue the imperative that to save money you must make your own, without offering any justification for this statement. It’s the kind of wisdom that seems like it must be true, so let’s just all agree that it is.  It’s also the kind of wisdom that must be examined, if our goal is to choose the lowest cost option.

There are three factors to creating your own foods: cost of ingredients, cost of electricity or gas for your cooking appliances, and the cost of time.  The Complete Tightwad Gazette by Amy Dacyczyn (Amazon affiliate link), gave me the tools for calculating the first two of these costs.  I am not going to discuss the cost of running appliances in this series.

Today’s post is about accounting for the cost of ingredients.  In tomorrow’s post, I’ll discuss how to use the cost of ingredients to calculate the cost of a recipe and compare that to similar prepared foods.  Monday’s post is about factoring your time into all of the strategies discussed in this series.  Tuesday’s post will wrap up this series by discussing some of the strategies I’ve developed through using these tools.

Calculating Your Cost Per Recipe

If you’ve been reading this series, you’ve probably figured out by now that I love math.  If you’ve known me a while, or if you’ve read my other blog, especially the post on Crafting CPH (Cost Per Hour), you long ago figured out that I love math.  Either way, by now you know enough about me to know that when I came across Amy Dacyczyn’s article on Calculating C.P.M. (Cost Per Muffin), I was immediately hooked and happy as a math geek with a page full of numbers gets.

Calculating the cost of a recipe is a straightforward process.  Figure out how much a unit of each ingredient costs, multiply the amount of the ingredient used in the recipe by the unit cost of the ingredient, add up the cost of all the ingredients used in the recipe, and divide that cost by the number of servings the recipe produces.  Compare the cost of a homemade serving to the cost of an already prepared serving.  In this endeavor,  your time spent calculating can be minimal because a spreadsheet is your friend.

Calculate Unit Cost

Q: If we’ve been diligently following the steps laid out in this series of columns, haven’t we already done this?

A: Maybe.  Probably not.

What is a unit?

The issue here comes down to the question we asked ourselves before: What is a unit?  When you are shopping the quickest way to make comparisons between different brands of raw ingredients is usually the weight of the package, so your units will be in ounces or grams.  When you are cooking, you can measure the ingredients by volume, Imperial weight (ounces and pounds), or metric weight (grams and kilos).

If you are like the majority of people in the United States, you use volume to measure your ingredients when cooking or baking and recipes, whether in books or online, intended for distribution in the United States rarely include anything other than a volume measurement.  Outside the United States, it is more likely that recipes are measured in metric weight.

I personally use volume for cooking.  Sometimes that means with a measuring cup and sometimes it means eyeballing it.  When I’m baking, however, I use metric weight to measure ingredients.  (I’ll save the detour on why for a future post).

Some ingredients might be measured in various ways, depending on the recipe.  For example, if I’m making a white sauce, I measure butter by the tablespoon.  If I’m making a cake, I weigh the butter in grams.

Equivalencies

If you need to find a volume measurement, look at the nutrition and serving size label on your product.  On most products, the serving size is a volume measurement.  Unfortunately, that volume measurement may not use the same units as your recipe and you will have to figure out how the two relate to each other.

If you have a smart phone, there are apps for this.  But here’s some equivalents for you anyway:

Volume

  • 1 Tablespoon = 3 teaspoons
  • 1 Cup = 16 Tablespoons = 8 fluid ounces
  • 1 Pint = 2 Cups
  • 1 Quart = 4 Cups
  • 1 Gallon = 4 Quarts = 16 Gallons
  • 1 Fluid Ounce = 29.5735 milliliters
  • 1 Liter = 1000 milliliters

Weight

  • 1 Pound = 16 Ounces
  • 4 Ounces = 112 grams
  • 1 Kilo = 1000 grams =2.2 pounds

The Basic Formula

To calculate the cost of ingredient, divide the price by the number of units.  Some ingredients may only require a single calculation because you only measure them in one way.  If you measure an ingredient in different units, depending on the recipe, you will need to make a calculation for each form of measurement.  I might measure sugar by the cup, tablespoon, teaspoon, pound, ounce, kilogram, or gram, depending on the recipe.  Each of these different measurements is the same calculation: price paid for the package divided by the number of units of that size in the package.

A Spreadsheet is Your Friend

I recently started a spreadsheet to calculate the cost of making gluten free items.  The remainder of this blog post is the technical documentation explaining how I set up that spreadsheet, so you can use that information to set up a spreadsheet also.  It is a little bit of work to set up the spreadsheet, but once it is set up in the way described here, maintenance is easy!

Notes on the Sample Spreadsheet

I’ve included excerpts in this post, but you can view the entire spreadsheet on Google Docs.

The recipes included on this spreadsheet all come from the Gluten Free on a Shoestring blog.  The author of that blog developed her own gluten free flour recipes and uses that flour as the basis for all of her other recipes.  The first six  recipe columns of my spreadsheet calculate the cost of the various flour blends.  The flour blends are also listed as ingredients since they will be used in making other recipes.

This spreadsheet does not yet reflect an accurate total for either the waffles or the chocolate chip cookies included on the sheet.  Since this is a new spreadsheet, since I had not been carefully tracking the price of various ingredients, and since I used pantry ingredients like brown sugar, which were purchased some time ago at an unknown cost, those ingredients are not yet on the sheet.  Next time I buy those ingredients I will update the spreadsheet with that information.

Enter Your Ingredients

This is all the ingredients I’ve entered into my spreadsheet so far.  I manually enter the information in the columns labeled Ingredient and Units.  For the Price and Total columns, I manually entered the number, but many of these entries are cell references or formulas rather than static numbers.  The Price / Unit column is a formula.

Ingredient Column

I entered ingredients while looking at recipes and have not sorted them in any fashion.  Eventually, I will sort them in alphabetical order.  As I’m looking at this now, I realize that I’m probably going to want to change the names so that an alphabetical sort makes sense.

When the spreadsheet only has a handful of ingredients, the name I use isn’t a big deal.  If I have 100 or more entries, I don’t want to be able to find a particular entry quickly.  For example, if I sort alphabetically right now, Superfine Brown Rice Flour is going to be in S.  I’m not going to look for it there.  I’m going to want to change it to Flour, Superfine Brown Rice or Flour, Rice, Brown, Superfine or some variation thereof.  The important thing is that it’s first and foremost Flour, so that should be the first word!

Price, Total, and Units Columns

The price is the actual amount you paid for the product.  The Total is the number of units in the product.  The Units is the name of the units used.

Every formula is only as accurate as the data it uses.  If our price or total entries are incorrect, the Price / Unit generated by the spreadsheet is just a random number.  Therefore,  we have to think carefully about how to make entries in the Price and Total Columns.

On my spreadsheet, the information in the Price and Total Columns is a combination of manual entries, cell references, and formulas.  In this section, I’ll describe the options and when to use each one.

A Single Entry

If the ingredient only needs one entry, all you need to do is pick the relevant level of units and manually fill in the information across the row.  Your Total Units can either be the weight of the product, as it is for every ingredient currently on my spreadsheet, or it can be a volume measurement obtained from the serving size label on the product.

Whether you use a volume or weight measurement, your single entry should be something that makes sense for the way you use the product.  If the serving size on the label does not make sense for the way you use the product, your units may be a formula.

For example, this bottle of lemon juice uses 1 teaspoon as a serving size and has 280 teaspoons per bottle.

If I will only ever use the lemon juice 1 Tablespoon at a time, I can put the formula =280 / 3 (total teaspoons in bottle divided by 3 teaspoons in one tablespoon) into the Total column and never think about it again — unless I buy a bottle with a different number of teaspoons.

Multiple Entries

If you need multiple entries for a single ingredient because you may measure it in different ways when cooking, that’s when things get more complicated.

When I need multiple entries, the first entry I make for that ingredient is exactly as I purchased it.  For example, the first entry here for sugar is $11.49 for a 25 pound sack.  I bought this sugar at Costco and this entry reflects exactly the size of that single bag of sugar.  All of the other entries include at least one cell reference or formula in the Price or Total column.

The reason I set my sheet up this way is because I want the ongoing maintenance of this system to be easy.  I do not mind taking some time to set it up, but I do not want to manually calculate or enter 6 different values for sugar every time I buy a new bag.

The combination of manual entry, cell references, and formulas in the spreadsheet is calibrated so that if I buy another 25 pound bag, all I need to do is change the price on the $11.49 / 25 pound line.  All of the remaining entries for sugar will update automatically.  If I buy a 10 pound bag of sugar rather than a 25 pound bag of sugar, all I need to do is enter the new price and weight on the $11.49 / 25 pound line and all of the remaining entries for sugar will update automatically.

In order to get the correct combination of manual entry, cell references, and formulas for a particular ingredient, it matters if you start from a larger unit and work down (the sugar example) or if you start from a smaller unit and work up (the lemon juice example).  Let’s look at how to do the formulas in each instance.

Starting with Larger Units

When starting with Larger Units, your Price column will always contain a cell reference, either to the Price cell of the first entry or to the Price / Unit cell of the next largest relevant measurement.  The Total column will either be a formula or a static number, entered manually.  If the Total column includes a formula, that formula will include a cell reference to the Total cell of the first entry.

I want price / unit for all three measurement options — volume, Imperial weight, and metric weight — because sugar is a product that I might use in a variety recipes with differing measurement systems.  Therefore, my first equations are the ones needed to convert between the different measurement systems.  Once I have a starting point for each measurement system, my next set of equations provide the price for the smaller measurements within that system.

If I wanted to go straight from $11.49 / 25 pounds of the sugar package as purchased to a price / teaspoon, one way or another, I have to do the following steps:

  1. Calculate the price per pound ($11.49 / 25)
  2. Calculate the price per cup ($11.49 / 25 * 2.25)
  3. Calculate the price per tablespoon ($11.49 / 25 * 2.25 / 16)
  4. Calculate the price per teaspoon ($11.49 / 25 * 2.25 / 16 / 3)

Clearly, this is an iterative process.  I manually enter numbers when they are encountered for the first time.  After that, I use cell references to refer to the already completed calculations.  For the pounds to teaspoons calculations, that looks like this:

Calculate the price per pound: Manually enter $11.49 in Price column and 25 in Total column

Calculate the price per cup: This is a conversion row because I am switching from one measurement system to another.  In conversion rows, the Price cell is a reference to the price cell of the first entry (=B26) because the price of the entire package is the same, regardless of how you measure it!  When I manually update the price in the first row, the cell reference means this cell will automatically update.

In conversion rows, the Total entry is a formula that references the measurement of the first entry.  I might buy 5, 10, or 25 pound bags of sugar based on what the unit pricing in the store tells me is a better deal.  By referring back to Totals column in the first entry rather than using a static number, I can change only the first entry and all the other cells will update accordingly.

To convert from weight to volume, you can either look at the serving sizes, look up your product on the internet, or measure 1 cup of the product and weigh it.  A quick Google search tells me that 1 pound of sugar = 2.25 cups.  The full equation for the Total cell for the Cups row is: =C26 * 2.25.

Calculate the price per tablespoon.  Now I am reducing from one measurement to another in the same measurement system.  I know there are 16 tablespoons in 1 cup, so to find the price per tablespoon, I need to divide the price per cup by 16.  The Price entry is a cell address for the Price / Unit in the Cups row (=E27).  The Totals entry is a manually entered static number: 16 (tablespoons per cup).

Calculate the price per teaspoon.  Again, I am reducing from one measurement to another in the same measurement system.  I know there are 3 teaspoons in one tablespoon.  To find the price per teaspoon, I need to divide the price per tablespoon by 3.  The Price entry is the cell address for the Price / Unit in the tablespoons row (=E28).  The Totals entry is a manually entered static number: 3 (teaspoons per tablespoon).

The process for getting prices for metric system measurements is the same.

  1. Calculate the price per pound ($11.49 / 25)
  2. Calculate the price per kilogram ($11.49 / 25 / 2.2)
  3. Calculate the price per gram ($11.49 / 25 / 2.2 / 1000)

Calculate the price per kilogram. This is a conversion row.  Therefore, the Price cell is a reference to the price cell of the first entry (=B26).  We are converting between two different systems for measuring weight and we already know the conversion factor is 2.2 pounds to a kilogram.  The formula for the Totals column is: =C26 / 2.2

Calculate the price per gram.  Now we are reducing from one measurement to another in the same measurement.  We know there are 1000 grams in a kilogram, so to find the price per gram, I need to divide the price per kilogram by 1000.  The Price entry is the cell address for the Price / Unit in the kilograms row (=E30).  The Totals entry is a manually entered static number: 1000 (grams per kilogram).

Starting with Smaller Units

If we start with smaller units, we are going in reverse order, and this changes where we need to manually enter static numbers, cell references, and formulas.  Let’s use the bottle of lemon juice as an example.  I do not remember how much I paid for the bottle, but let’s just say it was $2.00.

The series of calculations we need to do is as follows:

  1. Calculate the Price per teaspoon ($2.00 / 280)
  2. Calculate the Price per tablespoon ($2.00 / 280 * 3)
  3. Calculate the Price per Cup ($2.00 / 280 * 3 * 16)

We have two different options for Price and Total entries when working from the smallest units up to larger units.  In option 1, we use a formula in the price column and manually enter the static number 1 in the Total column.

Calculate the price per teaspoon.  This is our first entry, so we manually enter $2.00 in the Price column and $2.93 in the Totals column.

Calculate the price per tablespoon.  The Price is a formula in which we multiply the price per teaspoon by the number of teaspoons in a tablespoon.  We use a cell address reference for the price per teaspoon, which we calculated in the preceding row.  In this case, our formula is: =E34 * 3.  Since our Price formula reflects the conversion from teaspoons to tablespoons, we manually enter a static 1 in the Totals cell.

Calculate the price per cup.  The Price is a formula in which we multiply the price per tablespoon by the number of tablespoons in a cup.  We use a cell address reference for the price per tablespoon, which we calculated in the preceding row.  In this case, our formula is: =E35 * 3.  Since our Price formula reflects the conversion from tablespoons to cups, we manually enter a static 1 in the Totals cell.

In option 2, we use a cell reference in the Price column.  That cell reference refers back to the Price / Unit for the next smallest unit.  In the Totals cell, we enter a static number reflecting the number of smaller units in the larger unit.

Conversions between measuring systems can be handled three ways when starting from the smallest units and working your way up.  (1) You can convert to smallest units across all measuring systems and work your way up in all of them.  (2) You can work your way up in your first measuring system, use the largest units to convert to the other measuring systems and work your way down from there.  (3) You can use packaging information to getting starting points for each of the measuring systems (the front of the bottle of lemon juice tells me the number of fluid ounces and liters in the bottle) and manually enter a starting point for each.  If you choose the third option, you would have to make manual adjustments to the starting point in each measuring system, if you purchase a different size of that product in the future.

Price / Unit

The Price / Unit column is a formula.  If you start from the larger units and work your way down or you used option 1 when working from the smallest units on up, the formula is the number in the Price column divided by the number in the Total column.

If you started from the smallest units and worked up and you choose option 2, the formula in the Price column is the number in the Price column multiplied by the number in the Total column.

I personally prefer to use option 1 when working from the smallest units on up because I like to have the Price / Unit formula stay the same for every cell in that column.  In the Price and Totals column, my formulas are never going to be consistent because ingredients have a variety of units.

 Conclusion

Now that we have ingredients entered and we know how much they cost us using various units of measurements, it is time calculate how much a particular recipe costs to make and compare that to the prepared products available in the grocery store.  Time to add some columns to the spreadsheet!

Click here for Part 6b: Calculating the Cost of Homemade: Recipe Costs

3 Replies to “Early Lessons, Part 6a: Calculate the Cost of Homemade: Ingredient costs”

  1. I have two comments today, on different aspects of this topic. First – relative cost of homemade vs. store-bought. I used to make homebrew beer. It was fun, and at the time was the best way to get quality beer with flavor. But on reflection, I’m sure the cost per unit of beer was much higher for my homebrew than it would be for an equivalent store-bought beer. Calculating the cost of the base ingredients (malt, hops, yeast and water), plus the cost of the natural gas to boil, plus the cost of the water to cool it all and clean up afterwards would be scary, so I never did. Once you add in the cost of my time (about 6 hours start to finish to make 5 gallons), I eventually decided to go buy a 6-pack at the store when I was in the mood 🙂

  2. Comment 2 – on measuring units, scaling and spreadsheets. Once upon a time, my wife Kathy was traveling on business and had a restaurant meal she really liked. She asked the chef for the recipe, which he gladly gave her. But it was for hotel quantities of food! Any recipe that starts with “5 pounds of chicken soup base” (like bouillon cubes, only better) will likely make more than the two of us could consume in a lifetime. Out comes the spreadsheet and units conversion tables. Now I’ve got the recipe for one of her favorite meals down to 12 oz. chicken breast, 3 oz. dried mushrooms, 2 T chicken soup base, 1/4 cup balsamic vinegar, etc… Came out great.
    Chicken with balsamic mushroom sauce over angel hair pasta.

Comments are closed.