Soap recipe in Excel (auto calculations)

[Na7asha7aylor]

Hi everyone, hopefully someone here can give me some direction. And apologies, if this is confusing, I am confused lol

I can't seem to figure out how to formulate a soap recipe in excel that is similar to all my other bath and body products in percentages.

Mainly because I would have to calculate based on total batch weight versus the total oils weight.
For example, when I want to make 1000g of lotion, I simply plug in that 1000, and based off my percentages it gives me my total grams for each ingredient.

So, I want to be able to auto calculate my main soap recipe in 500g, 1000g and 2500g, but these are the oil weights, not the batch sizes.

Has anyone managed to figure this out? When I submit my CNF forms (Canada) I just want to quickly know what percentage my goat milk is etc for any size batch that I make...and also to make my soap recipes as precise as my other skin care formulations. Thanks very much!!

 

[Marsi]

To calculate your main recipe (one recipe!), there is a very straightford set of calculations that you can enter into your spreadsheet.

You already know the amount of oils it takes to make your usual batch size.
Calculate the volume of mold that you usually use (using height of the soap in the mold as the height measurement).
(or you could just pour something into the mold and measure the volume directly!)

Calculate the volume of the new mold (using the same method you used to measure the volume of your original mold)

Divide the new mold volume by the original mold volume.
The resulting number gives you a multiplier to use for your individual ingredient weights.

Example ...
If you used this terrible recipe

Batch weight 2000g
100% coconut oil
5% superfat

You would have
2000g coconut oil
707g water
348g lye
Lets assume (example only, not accurate) that this amount of batter makes 4300 millilitres of soap batter

Logically, if you make a quarter sized recipe (500g), then you will end up with a quarter of the batter as well.
You can use this knowlege to put your calculation into your Excel spreadsheet.

To calculate your new batch size ingredient list, take your new batch size and divide it by your original batch size.
If you are working on weights (which you already know how to do), it would be 2000g multiplied by 0.25 to get 500g oil weight (for the quarter batch example).

To do this using volumes, you take your new mold volume and divide it by the original mold volume.
For example, if your new mold holds 6500 millilitres of soap batter, then the calculation would be 6500 millilitres (new mold volume) divided by 4300 millilitres (original mold volume) - the multiplier will be 6500/4300, or 1.51

So to make your new batch size of 6500 millitres, you would get your spreadsheet to calculate
3020g coconut oil (calculation: 2000g multiplied by 1.51)
1067g water (calculation: 707g multiplied by 1.51)
525g lye (calculation: 348g multiplied by 1.51)

(I've included rounding errors for brevity of typing - you will not have these in your spreadsheet calculations)

(Massive changes in volume will bring some other things into play, so if you change from 500grams to 50kilograms, you will notice that this isn't as accurate, but for shifting between 2500g and 500g it will work quite well).

 

[lsg]

Soapmaking Friend and automatically give you the weights for each oil/fat, given the percentages of each and the total amount of oils used.

https://www.soapmakingfriend.com/soap-making-recipe-builder-lye-calculator