Intuitive Costing Spreadsheets — For Bakers!
For my @bakernobakery microbakery operation, I started off with an ingredient index and a recipe costing template, wanting to get a better understanding of how much each ingredient cost, and what I needed to charge to hit an ideal 30% (or less) food cost. This led to a sales log template, which allowed me to track orders, as well as input information from my recipe costing spreadsheets. I also wanted to understand the gross income and net profits of each of my popups, and additionally be able to factor in donations.
The first spreadsheets I released required a lot of data entry, and they’re still available for free through my website, www.bakernobakery.com. These free are a lot more flexible for grams, pounds, and ounces especially for savory chefs who don’t work in grams.
Those all worked together like puzzle pieces that you had to put together yourself, this second iteration is much more intuitive and does the majority of the heavy lifting. You’ll need to enter the ingredients' weight and how much they cost you, and then enter in your recipes. After that, everything should translate for you. I specifically work in grams, and these recipes are built out to be used in grams as well which means it’s ideal for bakeries.
This spreadsheet provides you with 4 recipe categories, 5 recipes per category, 2 components per recipe, 15 ingredients per component, and computes the food cost percentage per item for retail and wholesale, and your average food cost for retail and wholesale.
If you’re proficient in Excel you’ll be able to see the formulas I’m using and expand upon all the recipes to allow for more ingredients or add additional component’s recipes. If you’re not so proficient in excel and are looking for something a bit more custom to fit your recipes specifically, email me at thebaker@bakernobakery.com and we’ll chat about your project.
I used Google Drive to build and edit this spreadsheet template, though it should convert to Excel. Please be mindful of the built in formulas. Always keep an unedited version so you can refer to the preset formulas. And with a backup, you can always remake copies if you accidentally delete or edit a cell, or go back in the history of the google document to undo your mistake.
Click here to open your free templates
Here’s how they work:
Step 1
Cost Ingredients: Ingredient Index, sheet #2
Starting with the ingredient index, Sheet #2, which helps you to understand the cost of all your ingredients by grams. As a professional baker, I break everything by weight in grams, not volume.
Since most things in the United States are sold in pounds and ounces, this spreadsheet allows you to enter the weight of the case or item, then the price you pay per case or item. For example, I input a 50lb bag of flour, for $105. The cost of the flour is broken down into how much it costs per gram. I input 50 into the pounds column because this item is sold by that volume. If you have a case that is in pounds and ounces, you could input 1lb and 5 oz, and the spreadsheet will automatically total the ounces. The spreadsheet then takes the cost of the case/item/bag/etc and divides it by the total oz to give you the cost per gram.
Step 2
Inputting Recipes: Category 1, Sheet #3
Next, you want to take the information from the ingredient index, and apply it to a recipe to understand the cost of that recipe. For example: my concha or tres leches based on the spreadsheets you downloaded, quantities edited so these recipes will not turn out if you try them.
Enter the name of your item, instead of “Item 1” above your recipes. On Sheet #3, replace ‘Concha’ with your item name. This will auto populate the summary sheet.
Enter in the ingredients in ‘Column A’ for your recipe word for word to match the ingredient index, any spelling errors, extra spaces, or capitalization will not allow the formula to find the ingredient cost.
Then enter the amount of ingredients you are using for the recipe in grams in ‘Column B’. This should automatically pull the cost per gram from the Ingredient Index, or sheet #2, to the Unit Cost column (Column D) and give you the total cost (Column E) of that ingredient per recipe. If your item is only one recipe, like a simple cookie dough, not a multiple component item: select and delete/empty all the #N/A cells in the Unit Cost and Total Cost columns for the component recipe you don’t need.
Make sure to enter the yield or count produced from the recipe (in Column B Cell 23).
At the bottom of the recipes, the spreadsheet will give you the total weight of the recipe (which only works if it is all the same unit of measurement, grams) and the total weight of each portion based on the yield you input. It then breaks down the cost of the total recipe, as well as the individual portion. This data then populates the Cost Summary, Sheet #1.
Sheets #3, #4, #5 and so on are their own Categories. Examples for Category titles are Cookies, Cakes, Pastries, Etc.
Step 3
Recipe Food Cost: Cost Summary, Sheet #1
Once you have entered all your recipe details and cleared the cells and recipe slots you don’t need, the information will auto populate the Cost Summary, or Sheet #1, with your total food cost per item.
Cost, or Column B totals the cost of each item by adding together the cost of each item's recipes or components. Then, enter in your retail price for the item in column C, and the wholesale price in column E if that is a sales channel for you too. The spreadsheet will then breakdown the food cost percentage per item, and then to the right, the average food cost for your entire menu for retail and wholesale.
If your average totals still read “#N/A”, make sure you have cleared all unpopulated cells in columns B through F of “#N/A” if you have finished inputting all of your recipes.
Congrats! You did it!
Step 4 (Optional, if you don’t have a POS system)
Now that you know how much each of your items costs you to make in materials and labor, you can price your items accordingly, and also log that information into, you guessed it, another spreadsheet!
Now, this one looks daunting, I know. It will allow you to record the date, the customer, and their order. It will also allow you to input the price of an item, the cost of an item, and based on all of that data: give you the gross and net profits of your popup/event/sales that day. Additionally, in row 2, you can view the totals of all your items, as well as those specific items gross and net totals.
I also included a sales tax option, which if you are an LLC can help you estimate your sales taxes, and understand your net profit more accurately. If you don’t worry about sales tax, just input 0 instead of .053 in the formula in cell AE 12.
There is also an opportunity to log your donations, as well as calculate how much you are donating based on a percentage. I chose 20% for this example based on gross income, but this formula (cell AE16) can be altered to be based on net profits by changing AB12 to AB13.
If you have any questions, feel free to email me: thebaker@bakernobakery.com
