Basic Costing Spreadsheets
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.
These spreadsheets all work together like puzzle pieces, with spreadsheet formulas that do the math for you, and allow you to account for your labor as well as the cost of ingredients.
You will need to make a copy to your Google Drive to edit them, and be mindful of the built in formulas. But also, 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
Here’s how they work:
Step 1
Starting with the ingredient index, which helps you to understand the cost of all your ingredients by whatever unit of measurement you use. I do everything by weight, not volume.
For example, I input a 50lb bag of flour, for $20. The cost of the flour is broken down into how much it costs per ounce, pound, each, and gram. In this instance, an error code reads in the -each- column, because this flour isn’t sold by individual items. I input 50 into the pounds column because this item is sold by that quantity, but also 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 ounce, pound, or gram.
Similarly, if you were to input 5 lemons, you would input 5 into the quantity/each column, and the total cost. You will have error messages in the ounce, pound, and gram columns.
If you have an item, such as a 200ct case of Limes, that also weighs 40 pounds, you can input all of this information and have the cost for every unit.
Step 2
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: sourdough starter.
Starter requires 100 grams of bread flour, which we costed in the ingredient index, and 100g of water, which is free. The cost of flour per gram is $.0009, so you input that into the cost/unit column for flour.
This makes 1 sourdough starter, so that is your yield, and takes you 5 minutes to make. Oftentimes, I overlook paying myself, and therefore have built this formula in to estimate the time it takes me for recipes, so that I can incorporate that into my costs. Take your hourly wage of $15 and divide it by 60 minutes, and the resulting decimal number should be input into cell D18.
At the bottom of this spreadsheet, it will give you the total weight of the recipe (which only works if it is all the same unit of measurement) 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, and includes the cost of your labor.
Step 3
WOO so into the thick of it: multi recipe items. For example, cinnamon rolls that have a dough, filling, and frosting, maybe even a preferment. This spreadsheet has multiple sheets that allows you to cost out each individual recipe, and total the cost of each component to give the cost of the whole cinnamon roll.
If you have more than two items/components, simply make a copy of the item 2 sheet as many times as you need.
To figure out the cost of an entire single cinnamon roll, I would take the cost of the component from the total/each row and add it into the total cost column in the “Total Cost” sheet. This will then take the cost of each component, and total it for you.
Step 4
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
