I made some spreadsheets for formulating products such as lotions, shampoo, conditioner, sugar scrubs, basically anything but soap. This is not a lye calculator and there are other spreadsheets out there to calculate the cost of soap. This spreadsheet allows you to tweak the percentages of a formula, not the amounts directly, and see the cost per ingredient and per batch.
The first is a batch auto-calculator. The instructions are in the spreadsheet itself, below the calculator part. Basically what it does, is you enter the amount of product you want to make and the percent of each ingredient, and it will calculate the amount you need of each. You can use this one to track test batches and keep notes of what changes you tried and how well it worked. Having to calculate amounts by hand and trying to keep a stack of sticky notes organized was just not working for me. At all.
It will also calculate your costs, both per ingredient and for the total batch. This lets you see how cost effective your recipe tweaks are. You can use grams or ounces. It will calculate the cost per item, if you are making multiple equal portions (e.g., a number of bottles of lotion that are equal size). It does not currently add in the cost of packaging, but if there's a demand for it I can probably add that in, somewhere.
The second calculator will calculate your cost per gram or per ounce (at least, it should - I am not good at math, which is why I need spreadsheets to do it for me
). Instructions are to the right of the calculator. You enter the ingredient name, what you paid for it, and how much of it you purchased. Please note there are separate columns for weight and volume. If you want it to calculate the price per gram, enter the amount you bought in grams. If you want it to calculate the price per ounce (by weight), enter how many ounces you bought. If you purchased it by the fluid ounce or liter/milliliter, there is a column for volume measurements plus a column for the specific gravity. The vendor will usually have that information, or a quick internet search should find it. If you can't find the specific gravity, you can do an internet search for "what does 8 fluid ounces of [ingredient] weigh in grams" or however much you bought. Then put the number the search comes up with in the weight column instead of the volume column. That search doesn't always work for less common ingredients so contacting the vendor for the specific gravity (if it's not on their website) is more reliable.
I apologize for the verbosity of the instructions. I was trying to anticipate any possible questions regarding how to use the spreadsheets. If anyone can suggest a way to streamline the instructions, please tell me.
The format is Excel. I made it using Google Sheets but you can use whatever program you want, and I figured Excel would be a pretty universal format. The reason I prefer Google Sheets is because I can see the most recent version of any given formula on my phone away from the computer. Just something to consider.
To use the spreadsheet, you will need to download it or make a copy for yourself. When you click the link, you'll see a button at the top that says "Open with:". Click that and then click "Google Sheets". Click on "File" (in the upper left, just below the spreadsheet name) and see the available options. You will not be able to enter anything or make changes to the copy I am sharing until you make your own copy to fill in.
If you want a copy on your computer in the Excel format, instead of clicking on "Open with:", click the download icon in the top right toolbar instead.
I hope someone finds this useful. If you have questions or suggestions, please let me know.
Formulating spreadsheet: https://drive.google.com/file/d/1GFXQk4bV69cV7l8SM1FOfpWFYEbM3nv3/view?usp=sharing
Ingredient cost calculator: https://drive.google.com/file/d/1O3JF70bQ3ztuu3vKR6oEzCdMcsaLodRr/view?usp=sharing
The first is a batch auto-calculator. The instructions are in the spreadsheet itself, below the calculator part. Basically what it does, is you enter the amount of product you want to make and the percent of each ingredient, and it will calculate the amount you need of each. You can use this one to track test batches and keep notes of what changes you tried and how well it worked. Having to calculate amounts by hand and trying to keep a stack of sticky notes organized was just not working for me. At all.
It will also calculate your costs, both per ingredient and for the total batch. This lets you see how cost effective your recipe tweaks are. You can use grams or ounces. It will calculate the cost per item, if you are making multiple equal portions (e.g., a number of bottles of lotion that are equal size). It does not currently add in the cost of packaging, but if there's a demand for it I can probably add that in, somewhere.
The second calculator will calculate your cost per gram or per ounce (at least, it should - I am not good at math, which is why I need spreadsheets to do it for me
). Instructions are to the right of the calculator. You enter the ingredient name, what you paid for it, and how much of it you purchased. Please note there are separate columns for weight and volume. If you want it to calculate the price per gram, enter the amount you bought in grams. If you want it to calculate the price per ounce (by weight), enter how many ounces you bought. If you purchased it by the fluid ounce or liter/milliliter, there is a column for volume measurements plus a column for the specific gravity. The vendor will usually have that information, or a quick internet search should find it. If you can't find the specific gravity, you can do an internet search for "what does 8 fluid ounces of [ingredient] weigh in grams" or however much you bought. Then put the number the search comes up with in the weight column instead of the volume column. That search doesn't always work for less common ingredients so contacting the vendor for the specific gravity (if it's not on their website) is more reliable.I apologize for the verbosity of the instructions. I was trying to anticipate any possible questions regarding how to use the spreadsheets. If anyone can suggest a way to streamline the instructions, please tell me.
The format is Excel. I made it using Google Sheets but you can use whatever program you want, and I figured Excel would be a pretty universal format. The reason I prefer Google Sheets is because I can see the most recent version of any given formula on my phone away from the computer. Just something to consider.
To use the spreadsheet, you will need to download it or make a copy for yourself. When you click the link, you'll see a button at the top that says "Open with:". Click that and then click "Google Sheets". Click on "File" (in the upper left, just below the spreadsheet name) and see the available options. You will not be able to enter anything or make changes to the copy I am sharing until you make your own copy to fill in.
If you want a copy on your computer in the Excel format, instead of clicking on "Open with:", click the download icon in the top right toolbar instead.
I hope someone finds this useful. If you have questions or suggestions, please let me know.
Formulating spreadsheet: https://drive.google.com/file/d/1GFXQk4bV69cV7l8SM1FOfpWFYEbM3nv3/view?usp=sharing
Ingredient cost calculator: https://drive.google.com/file/d/1O3JF70bQ3ztuu3vKR6oEzCdMcsaLodRr/view?usp=sharing
