My spreadsheets for formulating/cost/testing

Soapmaking Forum

Help Support Soapmaking Forum:

This site may earn a commission from merchant affiliate links, including eBay, Amazon, and others.

Quanta

Well-Known Member
Joined
May 12, 2020
Messages
469
Reaction score
765
Location
USA
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
 
@Quanta you lovely human, thank you so much for this. I was literally in the process of creating my own spreadsheets last week and this is such a godsend to fine tune them and get pointers. Yours looks much MUCH better than mine and I really appreciate the detailed instructions on the bottom.
 
I added a spot to record pH on the formulating spreadsheet. Under the formula name, in the same cell (C1), I put "pH of this version:" I don't know why I didn't put it on there before.
If you downloaded my sheet, you shouldn't have to redownload it. You can just look at the master copy, and manually add the change to the copy you made for yourself.
 
I saw these before but wasn't in a spot to really look at them or download them. Now that I have... THANK YOU! I recently made a simple one based on HB&Me's spreadsheet instructions, but yours is so much more detailed and helpful. You are indeed a lovely and generous human. 🤗
 
Just a heads up...

Someone sent me a request through Google to share the spreadsheet. For future reference, as far as I know, the spreadsheet doesn't have to be shared in order to be copied. If you have your own (unshared) copy you can change it to suit your needs, and make a new copy for each product you want to use it for.
 
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:

Ingredient cost calculator:


I'm late to this party, but wanted to say thank you nonetheless. Awesome resource! Much gratitude to you for sharing! ❤️
 
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:

Ingredient cost calculator:

These are very cool - thank you for sharing!!
 
Back
Top