Recipe Costing (with Conversions)

99
65
Joined Jun 28, 2017
Hi everyone, I’m sharing an Excel workbook I built for costing out recipes. It’s not perfect, but it’s free and it has a few nice little bells and whistles that I couldn’t find elsewhere. Basically, you can put in how much you paid for a 3 lb. box of salt on one page, and then when you cost your recipe it tells you how much that one tablespoon of salt you used for that recipe cost…. but for all the things in all your recipes using whatever type of measurement you want as input and then converting it automatically (US Customary to Metric and volume to weight if possible) to whatever type of measurement you want as output and updating your costing in all your recipes if you change a price on your master purchase list. The volume to weight conversions are customizable, but currently based on the ones on the King Arthur flour website.

If you have questions about the workbook, please post them in this thread and I will answer them as best I can. I’d love to hear suggestions for improvement and/or feature requests if you have them. I am particularly interested in your opinions on how to incorporate dealing with AP/EP conversions (like, where within the process of using the workbook would it be most useful to input this info).

Below you’ll find a brief explanation of how the workbook works - although it’s really much easier to use than to read about :). Just remember please that this is a workbook I made for myself with my own needs (recipe costing for small scale baking projects) in mind and only deals directly with ingredients as purchased, not labor/overhead/packaging/soft goods/edible portion calculations etc. In addition, I just made a few changes to the sheet - so keep common sense by your side, since it might have glitches. Also, I’m using the Raleway font in the sheet and I’m not sure how that will appear on other people’s computers.

Enjoy or ignore as you please!

I don't really want to deal with emailing people individually, so the download is on a back page of my website: http://www.zcc.kitchen/industry-resources

ABOUT/INSTRUCTIONS:

The workbook is made up of four worksheets: “Constants” and ”Volume Weight Conversion Factors” contain tables of conversion factors that fuel the automatic calculations of the workbook. “List of Purchase Prices” contains a list into which you enter your ingredients and their prices. “Recipe Template” is, as the name suggests, a template page that you copy when you want to cost out a new recipe.

1. “Constants” - This page has several tables holding conversion factors for volume to volume and weight to weight conversions. The information stored on this page (which I got from Google’s conversion engine) fuels the automatic conversion functions of the workbook. Leave this page alone.

2. “Volume Weight Conversion Factors” - This page has a dynamic list of ingredients and their volume to weight conversion factor using a standard of cup or each for the volume and grams for the weight. The information stored on this page also fuels the conversion functions of the workbook. You can use it as-is, add your own items to it, and/or change the conversions to reflect your own averages. I am including a “Conversion Factor Calculator” to make inputing your own averages in the correct format a bit easier. The conversions already listed are all derived from the “Ingredient Weight Chart” on the King Arthur Flour website (http://www.kingarthurflour.com/learn/ingredient-weight-chart.html). Input in the blank fields and sort your list using the buttons in the bottom right hand corner of each header. Use the toggle handle at the far right bottom corner to make space for more items.

3. “List Of Purchase Prices” - This page is where you store information about the ingredients you use and the prices you pay for them. I have left it blank except for a few examples of items so that you can play with the workbook before bothering to input your own information. Use the toggle handle at the far right bottom corner to make space for more items. If you just type your stuff below it won’t interact correctly with the recipe template. You enter the name of the item, the purveyor (optional), the purchase size, the purchase unit of measurement, the price you paid for the item, and the item category. Purchase unit of measurement and item category are both drop down lists that are controlled via data validation linking them to the conversion charts listed above. The worksheet then uses lookup formulas to spit out the cost of your item in all of the different units of measurement that you might want to use in your recipe - both by weight and by volume and in US Customary and Metric formats. Of course, if you choose “*other - no volume:weight conversion available” as the category for an item, the cost will not be converted across the volume weight divide. If you change the purchase price of an item, every recipe that uses that item as an ingredient will have it’s cost updated. If you change the name or purveyor of an item, your recipes will not be able to find the price of that ingredient. Again, input in the blank fields and leave the calculated fields alone.


4. “Recipe Template” - This page is a blank template that you copy when you want to cost out a new recipe. It is fairly self explanatory. You select each ingredient from a drop down menu that is linked via dynamic data validation to the list of ingredients you have already provided. You enter the amount you are using and the unit of measurement (also data validated). You do not need to worry about using the same unit of measurement that you used for the ingredient’s purchase amount - the workbook takes care of the calculations. It has room to compare two recipes side by side with three portioning options. I typically use this function to answer questions like, “should I go to Sam’s Club or to Costco to buy ingredients for this event since I don’t want to go to both” or “can I upgrade to fancy butter in this recipe if I reduce the portion size.” I also added in a graphic representation of individual ingredient cost as percentage of total to help me focus my bargain hunting on the ingredients that most impact the overall cost of the recipe.
 
Last edited:
Top Bottom