Fat INS Cost per Specific Other Iodine Value INS ComputationNaOH KOH Oz/Unit Gravity Names Low High Dr. Bob's INS Est. INS

Almond, Sweet 0.1360 0.1908 97 $ 0.24 0.949 93 105 97 92Apricot Kernel 0.1350 0.1894 91 $ 0.21 0.940 92 108 91 90Arachis 0.1360 0.1908 99 $ 0.06 0.914 peanut 93 93 99 98Avocado 0.1330 0.1866 99 $ 0.30 0.915 82 90 99 101Avocado Butter 0.1339 0.1878 101 $ 0.63 0.904 80 95 101Babassu, brazil nut 0.1760 0.2469 230 0.897 10 20 230 232Bay 0.1420 0.1992 126 $ 34.52 0.880 Laurel 68 80 126Beef Hoof 0.1359 0.1906 124 0.917 neat's foot 72 72 124 119Beeswax 0.0690 0.0968 84 $ 0.38 0.966 9 11 84 87Borage 0.1357 0.1904 51 $ 2.53 0.923 130 150 51Brazil Nut 0.1750 0.2455 230 0.897 10 20 230 231Butterfat, Cow 0.1619 0.2271 191 0.911 36 36 191 192Butterfat, Goat 0.1672 0.2346 204 0.910 .91-.94 25 37 204Camelina 0.1333 0.1870 37 $ 0.64 140 160 37Camellia 0.1362 0.1911 109 $ 0.32 0.915 78 88 109Candelilla Wax 0.0381 0.0534 36 $ 0.37 0.984 15 20 36Canola 0.1324 0.1857 56 $ 0.04 0.918 110 126 56 68Carnauba Wax 0.0611 0.0857 75 $ 0.19 0.997 9 14 75Castor 0.1286 0.1804 95 $ 0.55 0.965 80 90 95 96Chaulmoogra 0.1470 0.2062 72 0.948 130 140 72Chicken Fat 0.1389 0.1949 130 0.924 66 72 130 126Chinese Bean 0.1350 0.1894 61 $ 0.02 0.927 soybean 124 132 61 62Citric Acid 0.5710 0.8010 0 $ 0.16 1.665Cocoa Butter 0.1370 0.1922 157 $ 0.23 0.968 33 44 157 154Coconut 0.1900 0.2665 258 $ 0.07 0.924 10 10 258 257Coconut Cream 0.0000 0Coconut Milk 0.0000 0Cod-liver 0.1326 0.1860 29 0.928 165 165 29 22Colza 0.1240 0.1740 56 $ 0.00 0.915 rapeseed 105 120 56 62Corn 0.1360 0.1908 69 $ 0.04 0.922 122 123 69 69Cottonseed 0.1386 0.1944 89 $ 0.15 0.917 103 111 89 88Depot fat 0.1397 0.1960 129 0.918 Human Fat 68 68 129Earthnut 0.1360 0.1908 99 $ 0.06 0.914 peanut 93 93 99 98Emu 0.1359 0.1906 128 $ 1.38 40 80 128 131Evening Primrose 0.1357 0.1904 43 $ 0.59 0.914 138 158 43Flaxseed 0.1357 0.1904 -6 $ 0.51 0.915 linseed 205 205 -6 -15Goose Fat 0.1369 0.1921 130 0.929 58 67 130 130Grapeseed 0.1265 0.1775 66 $ 0.56 0.927 94 135 66 63Hazelnut 0.1356 0.1902 104 $ 0.68 0.919 87 87 104Hemp Seed 0.1345 0.1887 39 $ 0.45 0.927 145 161 39 36Illipe Butter 0.1385 0.1943 146 $ 0.70 43 55 146Java Cotton 0.1461 0.2050 108 kapok 85 110 108Jojoba (Wax) 0.0690 0.0968 11 $ 0.93 1.000 80 85 11 15Kapok 0.1461 0.2050 108 85 110 108Karite Butter (Shea) 0.1280 0.1796 116 $ 0.34 55 71 116 117Katchung 0.1360 0.1908 99 $ 0.06 0.914 peanut 93 93 99 98Kokum Butter 0.1357 0.1904 155 $ 0.72 32 40 155Kukui Nut 0.1350 0.1894 24 $ 1.18 160 175 24 22Lactic Acid 0.4440 0.6229 0Lanolin 0.0741 0.1040 83 0.938 17 29 83 81Lard 0.1380 0.1936 139 $ 0.04 0.925 Pig Fat 59 59 139 135Linseed 0.1357 0.1904 -6 $ 0.51 0.915 188 195 -6 -2Macadamia 0.1390 0.1950 119 $ 0.26 73 79 119Maize 0.1360 0.1908 69 $ 0.04 0.922 corn 122 123 69 69Mango 0.1280 0.1796 120 55 65 120Mango Butter 0.1371 0.1923 146 $ 0.51 43 50 146Manoi 0.1900 0.2665 258 $ 2.24 0.924 Coconut,refined 10 10 258 257Meadowfoam 0.1207 0.1693 78 $ 1.41 92 92 78Mink 0.1400 0.1964 142 45 65 142Mustard 0.1241 0.1741 56 $ 0.00 0.915 rapeseed 105 120 56 62Neat's foot 0.1359 0.1906 124 0.917 72 72 124 119Neem 0.1387 0.1946 124 $ 0.85 0.917 84 94 124 106Nutmeg Butter 0.1160 0.1627 101 0.972 60 65 101Olive 0.1340 0.1880 109 $ 0.12 0.918 79 95 109 101Ostrich 0.1390 0.1950 128 90 103 128 99Palm 0.1410 0.1978 145 $ 0.07 0.915 45 57 145 147Palm Butter 0.1560 0.2188 183 0.870 10 18 183 205Palm Kernel 0.1790 0.2511 183 $ 0.09 0.870 10 18 183 238Palm, Stearic 0.1410 0.1978 157 37 45 157Peach Kernel 0.1370 0.1922 96 92 100 96 97Peanut 0.1360 0.1908 99 $ 0.06 0.914 93 93 99 98Perilla 0.1369 0.1921 -4 0.930 .930-.937 185 206 -4Pistachio Nut 0.1328 0.1863 92 $ 0.84 0.913 .913-.919 93 96 92Poppyseed 0.1383 0.1940 55 0.925 140 140 55Pumpkinseed 0.1431 0.2007 76 0.924 121 130 76Ramic 0.1240 0.1740 56 $ 0.00 0.915 rapeseed 105 120 56 62Rape 0.1240 0.1740 56 $ 0.00 0.915 rapeseed 105 120 56 62Rapeseed 0.1240 0.1740 56 $ 0.00 0.915 105 120 56 62Rice Bran 0.1280 0.1796 70 $ 0.46 0.918 110 110 70Ricinus 0.1286 0.1804 95 $ 0.19 0.965 castor 80 90 95 96Rose Hip Seed 0.1378 0.1933 7 $ 1.37 0.920 187 187 7Safflower 0.1370 0.1922 47 $ 0.09 0.928 145 145 47 48Sal Butter 0.1306 0.1832 145 $ 0.73 35 43 145Sesame Seed 0.1330 0.1866 81 $ 0.33 0.921 103 117 81 77Shea Butter 0.1280 0.1796 116 $ 0.31 55 71 116 117Shortening (veg.) 0.1360 0.1908 115 $ 0.05 0.920 90 95 115 99Soybean 0.1350 0.1894 61 $ 0.02 0.927 124 132 61 62Stearic Acid 0.1412 0.1981 280 0.941 2 2 280 197Stearin 0.1347 0.1890 280 2 2 280 187Sunflower Seed 0.1340 0.1880 63 $ 0.09 0.923 126 126 63 63Sweet Oil 0.1340 0.1880 109 $ 0.10 0.918 olive 81 81 109 107Tallow, bear 0.1390 0.1950 Bear GreaseTallow, beef 0.1405 0.1971 147 $ 0.13 0.942 Tallow 43 50 147 151Tallow, chinese vegetable 0.1385 0.1943 186 0.920 8 9 186Tallow, deer 0.1379 0.1935 166 0.965 .96-.97 26 36 166 163Tallow, goat 0.1383 0.1940 156 156Tallow, rabbit 0.1424 0.1998 115 0.935 70 100 115Tallow, seal 0.1362 0.1910 50 0.920 130 152 50Tallow, sheep 0.1383 0.1940 156 0.945 40 50 156 150Tamanu 0.1357 0.1904 83 $ 19.50 0.910 108 108 83Theobroma 0.1370 0.1922 157 $ 0.23 0.968 cocoa butter 33 44 157 154Tung 0.1377 0.1932 26 0.932 163 173 26Walnut 0.1353 0.1898 45 $ 0.16 0.928 140 150 45

Wheatgerm 0.1310 0.1838 58 $ 0.36 0.925 115 140 58 57

EO, Anise $ 0.59

EO, Cinnamon (leaf) $ 0.82

EO, Cedarwood (Virginia) $ 1.13

EO, Eucalyptus $ 0.51

EO, Lavender, 40/40 $ 2.13

EO, Lemon $ 1.52

EO, Lime $ 1.73

EO, Orange $ 0.32

EO, Peppermint $ 1.75

EO, Tea Tree $ 1.24

FO, Chocolate $ 2.57 FO, Cucumber Melon $ 1.50 FO, Magnolia $ 2.00 FO, Plumeria $ 1.50 FO, Rose Petals $ 1.35 FO,Sea Fresh $ 2.10 FO,Ocean Fresh $ 13.03 Box, Pill 3.0000 2.0895 $ 0.70 Pigment (candle) $ 0.65 Glycerin $ 0.41 1.264 GlycerineGlycerin $ 0.14 1.264 Glycerine

0

Sodium Hydroxide Potassium Hydroxide

.910-.929

Miscellaneous Tables

Type Of Lye formula offset Cost/Oz PriceSodium Hydroxide NaOH 2 0.56000 28Potassium Hydroxide KOH 3 0.25000 0.25

Conversions Conv factor Fluid units unit,small unit,Spelled unit, BigMetric 28.3750 ml gm Grams KilosStandard 0.0352 Oz Oz Ounces Pounds

Measurement System Standard

Decimal Places 1

Convert to System Convert to Unit To Std In. to cmConversion Factors 0.03524 16 1.00000 0.39370

Cu Inch to Oz cm to gramsVol Conversion Factor 0.5773852813853 0.9950

Mold Type offsetRectangular 3Cylindrical 4

Usage rate for EOs, it's usually 0.5 oz/lb for EOs like lavender, geranium, tea tree, or other medium-strong ones. (Best recommendation is for 2% of total weight of recipe) 0.3 oz/lb for cinnamon leaf, clove, peppermint, spices, or possible irritants, and up to 0.7 oz/lb for citrus.

Weight in lbs501

0.5463541667 (adjustment for true volume of oils rather than just weight as used in the conversion factor for 'vol to wt cnv'v was .6818

intraConv FacVol to Wt cnv Len Units Length1000 0.9950 cm 2.54

16 0.5774 In 0.393701^was .3780use instead: 133.376 oz water divided into 231 cubic inchesThis is the volume conversion for standard TOTAL

Usage rate for EOs, it's usually 0.5 oz/lb for EOs like lavender, geranium, tea tree, or other medium-strong ones. (Best recommendation is for 2% of total weight of recipe) 0.3 oz/lb for cinnamon leaf, clove, peppermint, spices, or possible irritants, and up to 0.7 oz/lb for citrus.

(adjustment for true volume of oils rather than just weight as used in the conversion factor for 'vol to wt cnv'

Recipe Name:

Measurements: Standard Recipe Fat/Water ratio: 3

Estimated INS Value: 183 Type of Lye: Sodium Hydroxide

Est Bar Hardness: hard

Oil NaOH

Oil Weight (Oz) Vol (Oz) % Pounds, Ounces INS SAP Weight (Oz) Super-fatting Oil Weight (Oz)

Coconut 32.0 34.6 33% 2 lb, 0 oz 258 0.190 6.08

Palm 32.0 35.0 33% 2 lb, 0 oz 145 0.141 4.51

Tallow, beef 32.0 34.0 33% 2 lb, 0 oz 147 0.141 4.50

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

Total: 96.0 103.6 100% 6 lb, 0 oz 183 15.09 Total: 0.0

Lye discount: 5.0% DWCP %: 40% Total Effective Lye Discount: 5.2%

Resulting Water/Lye factor: 1.50

Total Ounces Pounds Ounces Notes:

Oils 96.0 6 0.0

Other Ingredients 0.0 0 0.0

Lye 14.3 0 14.3

Water 21.5 1.0 5.5

Total (16.2% water) 131.8 139.3 8.0 3.8

After Cure (11.4% water) 124.6 7 12.6

Weight after yield & cure 109.0

per Cost Price Profit

Bar Size (Oz) 4.0 Bar $0.61 $ 2.45 $ 1.84

Number of Bars 31 Batch $16.54 $ 66.16 $ 49.62

After 5% overhead loss 27 Oz 0.140 $ 0.61 $ 0.47

Predicted Yield 95% Margin 4 Fragrances: 3 oz (Based on 0.5 oz ppo oils)bar price rounded up to the nearest 5 cents

Recipe Name:

Scale Factor: 1.00

Oil Weight (Oz) Vol (Oz) % of Oils Pounds, Ounces SAP Oz NaOH Super-fatting Oil Weight in Oz

Coconut 32.0 34.6 33% 2 lb, 0 oz 0.1900 6.08

Palm 32.0 35.0 33% 2 lb, 0 oz 0.1410 4.51

Tallow, beef 32.0 34.0 33% 2 lb, 0 oz 0.1405 4.50

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

Total: 96.0 103.6 100% 6 lb, 0 oz 15.09 Total: 0

Lye discount: 5.0% DWCP %: 40.0% Total Effective Lye Discount: 5.2%

Resulting Water/Lye factor: 1.50

Total Ounces Pounds Ounces Notes:

Oils 96.0 6 0.0

Other Ingredients 0.0 0 0.0

Lye 14.3 0 14.3

Water 21.5 1.0 5.5

Total 131.8 8 3.8

After Cure (11.4% water) 124.6 7 12.6

Cost

Bar Size(Oz) 4.0 Bar $0.61

Number of Bars 31 Batch $16.54

Number of Bars 0 Fragrances: 3 oz (Based on 0.5 oz ppo oils)

Bar Size(Oz) (before cure) #DIV/0!

Bar Size(Oz) (post cure) #DIV/0!

Recipe Name: Length ( in In) Width/Dia Depth

Mold Dimensions Rectangular mold 16 8 1.5

Scale Factor: 0.80 type: Rectangular Cylindrical mold 3 14

192 3

Oil Weight (Oz) Vol (Oz) % of Oils Pounds, Ounces SAP Oz NaOH Super-fatting Oil Weight in Oz

Coconut 25.5 27.6 33% 1 lb, 9.5 oz 0.190 4.84

Palm 25.5 27.8 33% 1 lb, 9.5 oz 0.141 3.59

Tallow, beef 25.5 27.0 33% 1 lb, 9.5 oz 0.141 3.58

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

lb, oz

Total: 76.4 82.4 100% 4 lb, 12.4 oz 12.00 Total: 0

Lye discount: 5.0% DWCP %: 40.0% Total Effective Lye Discount: 5.0%

Resulting Water/Lye factor: 1.50

Total Ounces Pounds Ounces Notes:

Oils 76.4 4 12.4

Other Ingredients 0.0 0 0.0

Lye 11.4 0 11.4

Water 17.1 1.0 1.1

Total 104.9 110.9 6 8.9

After Cure (11.4% water) 99.1 6 3.1

Cost

Bar Size(Oz) 4.0 Bar $0.61

Number of Bars 24 Batch $13.16

Number of Bars 0 Fragrances: 2.3 oz (Based on 0.5 oz ppo oils)

Bar Size(Oz) (before cure) #DIV/0!

Bar Size(Oz) (post cure) #DIV/0!

Hi, just a few notes about this template, how to use the workbooks created from it, and the methods used for certain operations. There's a short FAQ at the very end of this document. Notes in blue were added by Kathleen Koch January 2005.

Installation------------(These instructions are based on installing the template for use by versions as early as MSExcel97, on a machine running MSWindows95/98/ME/NT/XP.)

The best place to keep this template is in your Templates folder. Your Templates folder should be in the folder in which you installed Microsoft Office or Microsoft Excel. For example: on my machine the Templates folder is 'C:\Program Files\Microsoft Office\Templates'.

Basic usage-----------Once you have saved the template to your Templates folder, here's how to create new workbooks from it:1) Start Excel2) Click 'New' on the 'File' menu, which opens up the 'New' dialog box.3) On the 'General' tab of the 'New' dialog, pick 'soapsheet.xlt'.4) Click the OK button.5) A new workbook called soapsheet1 should appear in Excel.

There are only two worksheets in this workbook that you need to be concerned with when you play with your soap recipes. They are the sheets labeled "Recipe Worksheet" (the one you're on by default when you create a new workbook from the template) and "Scaled Version". The fields that may be changed on the Recipe worksheet are:

- Recipe Name- fields under the "Oil" heading- fields under the "Super-fatting Oil" heading- fields under the "Weight in Oz" headings for oils and super-fatting oils- Lye discount- DWCP % (Experienced Soapmakers only)- Bar Size (Oz)- Oil to Water ratio- Water (if water is overridden, then auto calc will cease for water field)(With the addition of the DWCP % field, this field is disabled)- Notes

Entering a recipe is as easy as picking oils from the drop-down list attached to the Oil fields and entering the amount of each you want to use. Then, if you want, enter super-fatting oils the same way, enter a lye discount, and perhaps change the bar size. That's it. Everything else will be calculated for you.

The Scaled Version sheet allows you to take the recipe entered on the Recipe sheet and scale it to whatever batch size you want. It shows everything from the Recipe sheet except INS values, and includes the additional field "Scale Factor". Scale Factor is the only field that may be changed on the Scaled Version sheet, and is used as follows: to scale a recipe down to one half of its original size, enter a Scale Factor of .5. To double a recipe, enter a Scale Factor of 2. Enter whatever factor you want, 2 and .5 are just examples. You get the idea.

The AutoScaled sheet allows you to enter a mold size and get the recipe scaled to your mold.

All fields titled in dark blue are for enterable values. For easier viewing of these areas, they have been highlighted in yellow.

Fluid Measurements------------------This spreadsheet will automatically compute the number of fluid ounces (or milliliters) from the oil weights entered, assuming the specific gravity is available for that oil.

Be aware, the same care must be taken measuring using liquid measurements.If you normally measure weights to within a tenth of an ounce and want to measure by volume, then the same precision is necessary (to the tenth of a fluid ounce). The gradations of a normal measuring cup would not be sufficiently accurate.

It is important to note that natural materials, such as oils and fats varygreatly, depending on source and refinement methods. As such, the specificgravity of your batch may differ from the table values. This will result in inaccurate amounts even with accurate measuring. Make certain you have at lease 3% superfatting in your recipes, to ensure an inaccuracy doesn't result in lye heavy soap. Measurement System------------------The measurement system (metric vs standard) can be changed by clicking the "convert measurements" button. This can be done at anytime and will convertall values from one system to the other. This allows you to enter metric orstandard (english) recipes then converting to your desired system. Conversionis done via an Excel macro, so if you do not enable macros (some people don'tfor fear of viruses) then conversion will not work. Conversion is the only thing using the macros, so if you don't convert measurements, disabling the macros will have no effect on your use of this spreadsheet.

SAP Values----------The SAP values used in calculating the lye are looked up from the SAP Values worksheet. If you disagree with any of the values, please feel free to change them. You may also change the names of any of the oils, just make sure you change any references on the Recipe worksheet from the old name to the new name. You may also add new oils, as long as you don't change the format of the SAP Values worksheet (this is very important, as the lookup from the Recipe worksheet depends upon the oil names and SAP values being in specific columns, and within a certain range of rows). If you do add new oils, you can either insert them into the list where you want or put them at the end; order is not important, just don't go past row 200 or so. Note too that the oils in the drop down list on the Recipe sheet appear in the same order as on the SAP Values sheet, so if you don't insert new oils in alphabetical order, they won't appear that way in the list either. To save any changes/additions you make to the SAP Values worksheet, so that any new workbooks you create will include them, see "Changing the template" below. Any additions need to include a price, even zero,to prevent unexpected and inaccurate results from costing and change measurementsinteraction.

Costing-------The spreadsheet will attempt to computer the cost of the soap you make by batch and bar. It does this based on the Cost/Oz column in 'SAP Values' Oil table.This is an example of how I would add a value: I bought a gallon of Coconut oilfor $9.90, so I entered '=9.90/128' into the cell E24 (in Coconut oil row of the Oil table). The 9.90 is the price and the 128 is the ounces per gallon. In truth the oil probably would actually weigh about 118 ounces, but this was a quick and dirty estimate of cost. If you do this for a living, then you might want to factor in specific gravity to convert liquid measurements to weights. eg: '=9.9/(128*F24)'. F24, in this case, is the specific gravity of Coconut oil.

For costing, add any non-oil ingredient (except Lye) to the bottom of theOil table. There is no need to put in any values except the name and price.Add specific gravity would help in computing volume measurements (useful with glycerin), but wouldn't be used for most fragrances.

The price for Lye is set up the same way, in the 'Tables' Type_of_Lye table,in the 'E' column. Eg: For a $3.79, 18 Oz bottle of Lye, click cell E103, type '=3.79/18'.

Many of the price values enter were based on prices I looked up on the web. Some maydiffer dramatically for you, depending on the amount and source of ingredients you buy. If you want accuracy, modify the prices based on your purchases.

IMPORTANT: If pricing values are missing for an Oil you use, then that cost willnot be reflected in the reported cost. No warning will appear.

INS Values of Recipes---------------------The concept of INS values is borrowed from Dr Robert McDaniel's book, "Essentially Soap." He in turn borrowed it from an unattributed source. In any event, what he says is that an oil's INS value is based on its SAP value and iodine value, and that a soap's INS is the weighted average of the INS values of its constituent oils. What does that mean to you? Probably not much. The important thing is that (as related by Dr Bob) the 'ideal' INS value to shoot for when formulating a soap recipe is 160. Now remember: 160 is only the ideal. Most recipes, even really excellent ones, won't be at an INS of 160. The majority of the ones I've looked at are in the mid 140's or low 150's. The INS value's real utility is as a sort of rough gauge of how well your recipe is balanced. In other words, if your recipe's INS is much above or way below 160, you might want to take another look at things.

INS Values of Individual Oils-----------------------------Many of the INS values for individual oils are borrowed from Dr Bob. Specifically, the sheet calculates its own INS values based on the best SAP and iodine values I could find, but where Dr Bob had a value, I defer to him. You may note that a few of the oils listed have no INS. That's because Dr Bob didn't list them, and I couldn't find iodine value numbers for them either. You can still use those oils in your recipes, but they will be ignored in the INS calculation for the recipe as a whole.

Canola versus Rapeseed oil--------------------------All canola oil is rapeseed oil, but not all rapeseed oil is canola. The reason for this is that rapeseed oil normally has an erucic acid content of up to fifty percent, while in order for an oil to be called canola, it may have an erucic acid content of no more than two percent. In other words, canola is low erucic acid rapeseed oil. What does this mean to you? Well, the erucic acid of regular rapeseed is largely replaced by oleic and linoleic acids in canola, which CHANGES THE SAP VALUE. I have yet to see a saponification chart, either published or on the web, which takes this into account. Every one of them treats rape and canola interchangeably, and lists the SAP of regular rape, even though the availability of regular rapeseed oil to the average North American consumer is limited at best. Is this dangerous? Since the SAP of rape is lower than that of canola, no, not really. It just means you're building an additional lye discount into any soap that uses canola. Which isn't much of a problem unless your recipe is already at the extreme of superfatting, or you're trying to make transparent soap. Bottom line: use canola for canola, and any of the other names (ramic/rape/rapeseed) for regular rapeseed oil.

Lye calculation and rounding----------------------------Because most people don't have a way to accurately measure amounts smaller than .1 ounces (well, I don't), the calculated amounts for lye and water are rounded DOWN to the nearest .1 ounce. If you must round, rounding down is the only safe way to go for lye, and unless you are making very small batches, a difference of less than one tenth of an ounce should have very little effect. Note that you may still enter amounts of oil to any level of precision you want, you just won't ever get more than one decimal place of precision for the lye and water.

Water calculation-----------------The amount of water required is calculated based on the rule of thumb of one pound of water for every three pounds of oils. There are other rules of thumb out there, e.g. six ounces of water per pound of oils, ala the MMS calculator, but I prefer the 1:3 rule.

To change the Oil to Water ratio enter the number of pounds of oil per pound ofwater in the oil into the water ratio entry field (upper left hand of Recipe worksheet).

The water value can be overridden (typed in manually), the rest of the calculationswill adjust, but this will prevent automatic calculation of water on thissheet. If you always create a new worksheet from the template for each newrecipe, it will only effect the recipe you manually overrode.

DWCP % (Experienced Soapmakers only)-------------------DWCP %: For Discounted Water Cold Process, enter a value from 33% to 50% (the maximum percentage range for disounting water; below 32 results in a "wet" batch and above 50 results in unreacted sodium hydroxide.)

The water calculation of "Recipe Fat/Water ratio:" is disabled when using the DWCP %.

After Cure calculation----------------------The After Cure weight is an estimate of the total yield of soap after most of the water has cured out of your bars. It is based on the rule of thumb that when your soap is done curing it will contain about 4% water, by weight.The actual water percent will be calculated and appear in the After Cure line.

Default values--------------Every new soapsheet workbook will have a default lye discount of 0%, bar size of 4 ounces, Oil-Water ratio of 3, scale factor of 1, and a oil & lye pricing info. Any of these may be changed. To save your changes for future workbooks, see "Changing the template" below.

Changing the template---------------------Here's how to change the template:

1) Create a new soapsheet workbook.2) Make your changes/additions to SAP Values, bar size, lye discount, and/or scale factor.3) From the File menu, click Save As.4) In the 'Save as type' drop-down list box on the 'Save As' dialog, choose 'Template (*.xlt)'. This choice should automatically change the 'Save in' directory to your Templates directory.5) In the 'File name' box of the 'Save As' dialog enter 'soapsheet' (the original name), or whatever name you would like to use for the new version of the template.6) If you are replacing the template you started with in step one, you will be prompted whether or not to overwrite the original. Click 'Yes'.7) You're done.

Hi, just a few notes about this template, how to use the workbooks created from it, and the methods used for certain operations. There's a short FAQ at the very end of this document. Notes in blue were added by Kathleen Koch January 2005.

Installation------------(These instructions are based on installing the template for use by versions as early as MSExcel97, on a machine running MSWindows95/98/ME/NT/XP.)

The best place to keep this template is in your Templates folder. Your Templates folder should be in the folder in which you installed Microsoft Office or Microsoft Excel. For example: on my machine the Templates folder is 'C:\Program Files\Microsoft Office\Templates'.

Basic usage-----------Once you have saved the template to your Templates folder, here's how to create new workbooks from it:1) Start Excel2) Click 'New' on the 'File' menu, which opens up the 'New' dialog box.3) On the 'General' tab of the 'New' dialog, pick 'soapsheet.xlt'.4) Click the OK button.5) A new workbook called soapsheet1 should appear in Excel.

There are only two worksheets in this workbook that you need to be concerned with when you play with your soap recipes. They are the sheets labeled "Recipe Worksheet" (the one you're on by default when you create a new workbook from the template) and "Scaled Version". The fields that may be changed on the Recipe worksheet are:

- Recipe Name- fields under the "Oil" heading- fields under the "Super-fatting Oil" heading- fields under the "Weight in Oz" headings for oils and super-fatting oils- Lye discount- DWCP % (Experienced Soapmakers only)- Bar Size (Oz)- Oil to Water ratio- Water (if water is overridden, then auto calc will cease for water field)(With the addition of the DWCP % field, this field is disabled)- Notes

Entering a recipe is as easy as picking oils from the drop-down list attached to the Oil fields and entering the amount of each you want to use. Then, if you want, enter super-fatting oils the same way, enter a lye discount, and perhaps change the bar size. That's it. Everything else will be calculated for you.

The Scaled Version sheet allows you to take the recipe entered on the Recipe sheet and scale it to whatever batch size you want. It shows everything from the Recipe sheet except INS values, and includes the additional field "Scale Factor". Scale Factor is the only field that may be changed on the Scaled Version sheet, and is used as follows: to scale a recipe down to one half of its original size, enter a Scale Factor of .5. To double a recipe, enter a Scale Factor of 2. Enter whatever factor you want, 2 and .5 are just examples. You get the idea.

The AutoScaled sheet allows you to enter a mold size and get the recipe scaled to your mold.

All fields titled in dark blue are for enterable values. For easier viewing of these areas, they have been highlighted in yellow.

Fluid Measurements------------------This spreadsheet will automatically compute the number of fluid ounces (or milliliters) from the oil weights entered, assuming the specific gravity is available for that oil.

Be aware, the same care must be taken measuring using liquid measurements.If you normally measure weights to within a tenth of an ounce and want to measure by volume, then the same precision is necessary (to the tenth of a fluid ounce). The gradations of a normal measuring cup would not be sufficiently accurate.

It is important to note that natural materials, such as oils and fats varygreatly, depending on source and refinement methods. As such, the specificgravity of your batch may differ from the table values. This will result in inaccurate amounts even with accurate measuring. Make certain you have at lease 3% superfatting in your recipes, to ensure an inaccuracy doesn't result in lye heavy soap. Measurement System------------------The measurement system (metric vs standard) can be changed by clicking the "convert measurements" button. This can be done at anytime and will convertall values from one system to the other. This allows you to enter metric orstandard (english) recipes then converting to your desired system. Conversionis done via an Excel macro, so if you do not enable macros (some people don'tfor fear of viruses) then conversion will not work. Conversion is the only thing using the macros, so if you don't convert measurements, disabling the macros will have no effect on your use of this spreadsheet.

SAP Values----------The SAP values used in calculating the lye are looked up from the SAP Values worksheet. If you disagree with any of the values, please feel free to change them. You may also change the names of any of the oils, just make sure you change any references on the Recipe worksheet from the old name to the new name. You may also add new oils, as long as you don't change the format of the SAP Values worksheet (this is very important, as the lookup from the Recipe worksheet depends upon the oil names and SAP values being in specific columns, and within a certain range of rows). If you do add new oils, you can either insert them into the list where you want or put them at the end; order is not important, just don't go past row 200 or so. Note too that the oils in the drop down list on the Recipe sheet appear in the same order as on the SAP Values sheet, so if you don't insert new oils in alphabetical order, they won't appear that way in the list either. To save any changes/additions you make to the SAP Values worksheet, so that any new workbooks you create will include them, see "Changing the template" below. Any additions need to include a price, even zero,to prevent unexpected and inaccurate results from costing and change measurementsinteraction.

Costing-------The spreadsheet will attempt to computer the cost of the soap you make by batch and bar. It does this based on the Cost/Oz column in 'SAP Values' Oil table.This is an example of how I would add a value: I bought a gallon of Coconut oilfor $9.90, so I entered '=9.90/128' into the cell E24 (in Coconut oil row of the Oil table). The 9.90 is the price and the 128 is the ounces per gallon. In truth the oil probably would actually weigh about 118 ounces, but this was a quick and dirty estimate of cost. If you do this for a living, then you might want to factor in specific gravity to convert liquid measurements to weights. eg: '=9.9/(128*F24)'. F24, in this case, is the specific gravity of Coconut oil.

For costing, add any non-oil ingredient (except Lye) to the bottom of theOil table. There is no need to put in any values except the name and price.Add specific gravity would help in computing volume measurements (useful with glycerin), but wouldn't be used for most fragrances.

The price for Lye is set up the same way, in the 'Tables' Type_of_Lye table,in the 'E' column. Eg: For a $3.79, 18 Oz bottle of Lye, click cell E103, type '=3.79/18'.

Many of the price values enter were based on prices I looked up on the web. Some maydiffer dramatically for you, depending on the amount and source of ingredients you buy. If you want accuracy, modify the prices based on your purchases.

IMPORTANT: If pricing values are missing for an Oil you use, then that cost willnot be reflected in the reported cost. No warning will appear.

INS Values of Recipes---------------------The concept of INS values is borrowed from Dr Robert McDaniel's book, "Essentially Soap." He in turn borrowed it from an unattributed source. In any event, what he says is that an oil's INS value is based on its SAP value and iodine value, and that a soap's INS is the weighted average of the INS values of its constituent oils. What does that mean to you? Probably not much. The important thing is that (as related by Dr Bob) the 'ideal' INS value to shoot for when formulating a soap recipe is 160. Now remember: 160 is only the ideal. Most recipes, even really excellent ones, won't be at an INS of 160. The majority of the ones I've looked at are in the mid 140's or low 150's. The INS value's real utility is as a sort of rough gauge of how well your recipe is balanced. In other words, if your recipe's INS is much above or way below 160, you might want to take another look at things.

INS Values of Individual Oils-----------------------------Many of the INS values for individual oils are borrowed from Dr Bob. Specifically, the sheet calculates its own INS values based on the best SAP and iodine values I could find, but where Dr Bob had a value, I defer to him. You may note that a few of the oils listed have no INS. That's because Dr Bob didn't list them, and I couldn't find iodine value numbers for them either. You can still use those oils in your recipes, but they will be ignored in the INS calculation for the recipe as a whole.

Canola versus Rapeseed oil--------------------------All canola oil is rapeseed oil, but not all rapeseed oil is canola. The reason for this is that rapeseed oil normally has an erucic acid content of up to fifty percent, while in order for an oil to be called canola, it may have an erucic acid content of no more than two percent. In other words, canola is low erucic acid rapeseed oil. What does this mean to you? Well, the erucic acid of regular rapeseed is largely replaced by oleic and linoleic acids in canola, which CHANGES THE SAP VALUE. I have yet to see a saponification chart, either published or on the web, which takes this into account. Every one of them treats rape and canola interchangeably, and lists the SAP of regular rape, even though the availability of regular rapeseed oil to the average North American consumer is limited at best. Is this dangerous? Since the SAP of rape is lower than that of canola, no, not really. It just means you're building an additional lye discount into any soap that uses canola. Which isn't much of a problem unless your recipe is already at the extreme of superfatting, or you're trying to make transparent soap. Bottom line: use canola for canola, and any of the other names (ramic/rape/rapeseed) for regular rapeseed oil.

Lye calculation and rounding----------------------------Because most people don't have a way to accurately measure amounts smaller than .1 ounces (well, I don't), the calculated amounts for lye and water are rounded DOWN to the nearest .1 ounce. If you must round, rounding down is the only safe way to go for lye, and unless you are making very small batches, a difference of less than one tenth of an ounce should have very little effect. Note that you may still enter amounts of oil to any level of precision you want, you just won't ever get more than one decimal place of precision for the lye and water.

Water calculation-----------------The amount of water required is calculated based on the rule of thumb of one pound of water for every three pounds of oils. There are other rules of thumb out there, e.g. six ounces of water per pound of oils, ala the MMS calculator, but I prefer the 1:3 rule.

To change the Oil to Water ratio enter the number of pounds of oil per pound ofwater in the oil into the water ratio entry field (upper left hand of Recipe worksheet).

The water value can be overridden (typed in manually), the rest of the calculationswill adjust, but this will prevent automatic calculation of water on thissheet. If you always create a new worksheet from the template for each newrecipe, it will only effect the recipe you manually overrode.

DWCP % (Experienced Soapmakers only)-------------------DWCP %: For Discounted Water Cold Process, enter a value from 33% to 50% (the maximum percentage range for disounting water; below 32 results in a "wet" batch and above 50 results in unreacted sodium hydroxide.)

The water calculation of "Recipe Fat/Water ratio:" is disabled when using the DWCP %.

After Cure calculation----------------------The After Cure weight is an estimate of the total yield of soap after most of the water has cured out of your bars. It is based on the rule of thumb that when your soap is done curing it will contain about 4% water, by weight.The actual water percent will be calculated and appear in the After Cure line.

Default values--------------Every new soapsheet workbook will have a default lye discount of 0%, bar size of 4 ounces, Oil-Water ratio of 3, scale factor of 1, and a oil & lye pricing info. Any of these may be changed. To save your changes for future workbooks, see "Changing the template" below.

Changing the template---------------------Here's how to change the template:

1) Create a new soapsheet workbook.2) Make your changes/additions to SAP Values, bar size, lye discount, and/or scale factor.3) From the File menu, click Save As.4) In the 'Save as type' drop-down list box on the 'Save As' dialog, choose 'Template (*.xlt)'. This choice should automatically change the 'Save in' directory to your Templates directory.5) In the 'File name' box of the 'Save As' dialog enter 'soapsheet' (the original name), or whatever name you would like to use for the new version of the template.6) If you are replacing the template you started with in step one, you will be prompted whether or not to overwrite the original. Click 'Yes'.7) You're done.

Hi, just a few notes about this template, how to use the workbooks created from it, and the methods used for certain operations. There's a short FAQ at the very end of this document. Notes in blue were added by Kathleen Koch January 2005.

Installation------------(These instructions are based on installing the template for use by versions as early as MSExcel97, on a machine running MSWindows95/98/ME/NT/XP.)

The best place to keep this template is in your Templates folder. Your Templates folder should be in the folder in which you installed Microsoft Office or Microsoft Excel. For example: on my machine the Templates folder is 'C:\Program Files\Microsoft Office\Templates'.

Basic usage-----------Once you have saved the template to your Templates folder, here's how to create new workbooks from it:1) Start Excel2) Click 'New' on the 'File' menu, which opens up the 'New' dialog box.3) On the 'General' tab of the 'New' dialog, pick 'soapsheet.xlt'.4) Click the OK button.5) A new workbook called soapsheet1 should appear in Excel.

There are only two worksheets in this workbook that you need to be concerned with when you play with your soap recipes. They are the sheets labeled "Recipe Worksheet" (the one you're on by default when you create a new workbook from the template) and "Scaled Version". The fields that may be changed on the Recipe worksheet are:

- Recipe Name- fields under the "Oil" heading- fields under the "Super-fatting Oil" heading- fields under the "Weight in Oz" headings for oils and super-fatting oils- Lye discount- DWCP % (Experienced Soapmakers only)- Bar Size (Oz)- Oil to Water ratio- Water (if water is overridden, then auto calc will cease for water field)(With the addition of the DWCP % field, this field is disabled)- Notes

Entering a recipe is as easy as picking oils from the drop-down list attached to the Oil fields and entering the amount of each you want to use. Then, if you want, enter super-fatting oils the same way, enter a lye discount, and perhaps change the bar size. That's it. Everything else will be calculated for you.

The Scaled Version sheet allows you to take the recipe entered on the Recipe sheet and scale it to whatever batch size you want. It shows everything from the Recipe sheet except INS values, and includes the additional field "Scale Factor". Scale Factor is the only field that may be changed on the Scaled Version sheet, and is used as follows: to scale a recipe down to one half of its original size, enter a Scale Factor of .5. To double a recipe, enter a Scale Factor of 2. Enter whatever factor you want, 2 and .5 are just examples. You get the idea.

The AutoScaled sheet allows you to enter a mold size and get the recipe scaled to your mold.

All fields titled in dark blue are for enterable values. For easier viewing of these areas, they have been highlighted in yellow.

Fluid Measurements------------------This spreadsheet will automatically compute the number of fluid ounces (or milliliters) from the oil weights entered, assuming the specific gravity is available for that oil.

Be aware, the same care must be taken measuring using liquid measurements.If you normally measure weights to within a tenth of an ounce and want to measure by volume, then the same precision is necessary (to the tenth of a fluid ounce). The gradations of a normal measuring cup would not be sufficiently accurate.

It is important to note that natural materials, such as oils and fats varygreatly, depending on source and refinement methods. As such, the specificgravity of your batch may differ from the table values. This will result in inaccurate amounts even with accurate measuring. Make certain you have at lease 3% superfatting in your recipes, to ensure an inaccuracy doesn't result in lye heavy soap. Measurement System------------------The measurement system (metric vs standard) can be changed by clicking the "convert measurements" button. This can be done at anytime and will convertall values from one system to the other. This allows you to enter metric orstandard (english) recipes then converting to your desired system. Conversionis done via an Excel macro, so if you do not enable macros (some people don'tfor fear of viruses) then conversion will not work. Conversion is the only thing using the macros, so if you don't convert measurements, disabling the macros will have no effect on your use of this spreadsheet.

SAP Values----------The SAP values used in calculating the lye are looked up from the SAP Values worksheet. If you disagree with any of the values, please feel free to change them. You may also change the names of any of the oils, just make sure you change any references on the Recipe worksheet from the old name to the new name. You may also add new oils, as long as you don't change the format of the SAP Values worksheet (this is very important, as the lookup from the Recipe worksheet depends upon the oil names and SAP values being in specific columns, and within a certain range of rows). If you do add new oils, you can either insert them into the list where you want or put them at the end; order is not important, just don't go past row 200 or so. Note too that the oils in the drop down list on the Recipe sheet appear in the same order as on the SAP Values sheet, so if you don't insert new oils in alphabetical order, they won't appear that way in the list either. To save any changes/additions you make to the SAP Values worksheet, so that any new workbooks you create will include them, see "Changing the template" below. Any additions need to include a price, even zero,to prevent unexpected and inaccurate results from costing and change measurementsinteraction.

Costing-------The spreadsheet will attempt to computer the cost of the soap you make by batch and bar. It does this based on the Cost/Oz column in 'SAP Values' Oil table.This is an example of how I would add a value: I bought a gallon of Coconut oilfor $9.90, so I entered '=9.90/128' into the cell E24 (in Coconut oil row of the Oil table). The 9.90 is the price and the 128 is the ounces per gallon. In truth the oil probably would actually weigh about 118 ounces, but this was a quick and dirty estimate of cost. If you do this for a living, then you might want to factor in specific gravity to convert liquid measurements to weights. eg: '=9.9/(128*F24)'. F24, in this case, is the specific gravity of Coconut oil.

For costing, add any non-oil ingredient (except Lye) to the bottom of theOil table. There is no need to put in any values except the name and price.Add specific gravity would help in computing volume measurements (useful with glycerin), but wouldn't be used for most fragrances.

The price for Lye is set up the same way, in the 'Tables' Type_of_Lye table,in the 'E' column. Eg: For a $3.79, 18 Oz bottle of Lye, click cell E103, type '=3.79/18'.

Many of the price values enter were based on prices I looked up on the web. Some maydiffer dramatically for you, depending on the amount and source of ingredients you buy. If you want accuracy, modify the prices based on your purchases.

IMPORTANT: If pricing values are missing for an Oil you use, then that cost willnot be reflected in the reported cost. No warning will appear.

INS Values of Recipes---------------------The concept of INS values is borrowed from Dr Robert McDaniel's book, "Essentially Soap." He in turn borrowed it from an unattributed source. In any event, what he says is that an oil's INS value is based on its SAP value and iodine value, and that a soap's INS is the weighted average of the INS values of its constituent oils. What does that mean to you? Probably not much. The important thing is that (as related by Dr Bob) the 'ideal' INS value to shoot for when formulating a soap recipe is 160. Now remember: 160 is only the ideal. Most recipes, even really excellent ones, won't be at an INS of 160. The majority of the ones I've looked at are in the mid 140's or low 150's. The INS value's real utility is as a sort of rough gauge of how well your recipe is balanced. In other words, if your recipe's INS is much above or way below 160, you might want to take another look at things.

INS Values of Individual Oils-----------------------------Many of the INS values for individual oils are borrowed from Dr Bob. Specifically, the sheet calculates its own INS values based on the best SAP and iodine values I could find, but where Dr Bob had a value, I defer to him. You may note that a few of the oils listed have no INS. That's because Dr Bob didn't list them, and I couldn't find iodine value numbers for them either. You can still use those oils in your recipes, but they will be ignored in the INS calculation for the recipe as a whole.

Canola versus Rapeseed oil--------------------------All canola oil is rapeseed oil, but not all rapeseed oil is canola. The reason for this is that rapeseed oil normally has an erucic acid content of up to fifty percent, while in order for an oil to be called canola, it may have an erucic acid content of no more than two percent. In other words, canola is low erucic acid rapeseed oil. What does this mean to you? Well, the erucic acid of regular rapeseed is largely replaced by oleic and linoleic acids in canola, which CHANGES THE SAP VALUE. I have yet to see a saponification chart, either published or on the web, which takes this into account. Every one of them treats rape and canola interchangeably, and lists the SAP of regular rape, even though the availability of regular rapeseed oil to the average North American consumer is limited at best. Is this dangerous? Since the SAP of rape is lower than that of canola, no, not really. It just means you're building an additional lye discount into any soap that uses canola. Which isn't much of a problem unless your recipe is already at the extreme of superfatting, or you're trying to make transparent soap. Bottom line: use canola for canola, and any of the other names (ramic/rape/rapeseed) for regular rapeseed oil.

Lye calculation and rounding----------------------------Because most people don't have a way to accurately measure amounts smaller than .1 ounces (well, I don't), the calculated amounts for lye and water are rounded DOWN to the nearest .1 ounce. If you must round, rounding down is the only safe way to go for lye, and unless you are making very small batches, a difference of less than one tenth of an ounce should have very little effect. Note that you may still enter amounts of oil to any level of precision you want, you just won't ever get more than one decimal place of precision for the lye and water.

Water calculation-----------------The amount of water required is calculated based on the rule of thumb of one pound of water for every three pounds of oils. There are other rules of thumb out there, e.g. six ounces of water per pound of oils, ala the MMS calculator, but I prefer the 1:3 rule.

To change the Oil to Water ratio enter the number of pounds of oil per pound ofwater in the oil into the water ratio entry field (upper left hand of Recipe worksheet).

The water value can be overridden (typed in manually), the rest of the calculationswill adjust, but this will prevent automatic calculation of water on thissheet. If you always create a new worksheet from the template for each newrecipe, it will only effect the recipe you manually overrode.

DWCP % (Experienced Soapmakers only)-------------------DWCP %: For Discounted Water Cold Process, enter a value from 33% to 50% (the maximum percentage range for disounting water; below 32 results in a "wet" batch and above 50 results in unreacted sodium hydroxide.)

The water calculation of "Recipe Fat/Water ratio:" is disabled when using the DWCP %.

After Cure calculation----------------------The After Cure weight is an estimate of the total yield of soap after most of the water has cured out of your bars. It is based on the rule of thumb that when your soap is done curing it will contain about 4% water, by weight.The actual water percent will be calculated and appear in the After Cure line.

Default values--------------Every new soapsheet workbook will have a default lye discount of 0%, bar size of 4 ounces, Oil-Water ratio of 3, scale factor of 1, and a oil & lye pricing info. Any of these may be changed. To save your changes for future workbooks, see "Changing the template" below.

Changing the template---------------------Here's how to change the template:

1) Create a new soapsheet workbook.2) Make your changes/additions to SAP Values, bar size, lye discount, and/or scale factor.3) From the File menu, click Save As.4) In the 'Save as type' drop-down list box on the 'Save As' dialog, choose 'Template (*.xlt)'. This choice should automatically change the 'Save in' directory to your Templates directory.5) In the 'File name' box of the 'Save As' dialog enter 'soapsheet' (the original name), or whatever name you would like to use for the new version of the template.6) If you are replacing the template you started with in step one, you will be prompted whether or not to overwrite the original. Click 'Yes'.7) You're done.

Hi, just a few notes about this template, how to use the workbooks created from it, and the methods used for certain operations. There's a short FAQ at the very end of this document. Notes in blue were added by Kathleen Koch January 2005.

Installation------------(These instructions are based on installing the template for use by versions as early as MSExcel97, on a machine running MSWindows95/98/ME/NT/XP.)

The best place to keep this template is in your Templates folder. Your Templates folder should be in the folder in which you installed Microsoft Office or Microsoft Excel. For example: on my machine the Templates folder is 'C:\Program Files\Microsoft Office\Templates'.

Basic usage-----------Once you have saved the template to your Templates folder, here's how to create new workbooks from it:1) Start Excel2) Click 'New' on the 'File' menu, which opens up the 'New' dialog box.3) On the 'General' tab of the 'New' dialog, pick 'soapsheet.xlt'.4) Click the OK button.5) A new workbook called soapsheet1 should appear in Excel.

There are only two worksheets in this workbook that you need to be concerned with when you play with your soap recipes. They are the sheets labeled "Recipe Worksheet" (the one you're on by default when you create a new workbook from the template) and "Scaled Version". The fields that may be changed on the Recipe worksheet are:

- Recipe Name- fields under the "Oil" heading- fields under the "Super-fatting Oil" heading- fields under the "Weight in Oz" headings for oils and super-fatting oils- Lye discount- DWCP % (Experienced Soapmakers only)- Bar Size (Oz)- Oil to Water ratio- Water (if water is overridden, then auto calc will cease for water field)(With the addition of the DWCP % field, this field is disabled)- Notes

Entering a recipe is as easy as picking oils from the drop-down list attached to the Oil fields and entering the amount of each you want to use. Then, if you want, enter super-fatting oils the same way, enter a lye discount, and perhaps change the bar size. That's it. Everything else will be calculated for you.

The Scaled Version sheet allows you to take the recipe entered on the Recipe sheet and scale it to whatever batch size you want. It shows everything from the Recipe sheet except INS values, and includes the additional field "Scale Factor". Scale Factor is the only field that may be changed on the Scaled Version sheet, and is used as follows: to scale a recipe down to one half of its original size, enter a Scale Factor of .5. To double a recipe, enter a Scale Factor of 2. Enter whatever factor you want, 2 and .5 are just examples. You get the idea.

The AutoScaled sheet allows you to enter a mold size and get the recipe scaled to your mold.

All fields titled in dark blue are for enterable values. For easier viewing of these areas, they have been highlighted in yellow.

Fluid Measurements------------------This spreadsheet will automatically compute the number of fluid ounces (or milliliters) from the oil weights entered, assuming the specific gravity is available for that oil.

Be aware, the same care must be taken measuring using liquid measurements.If you normally measure weights to within a tenth of an ounce and want to measure by volume, then the same precision is necessary (to the tenth of a fluid ounce). The gradations of a normal measuring cup would not be sufficiently accurate.

It is important to note that natural materials, such as oils and fats varygreatly, depending on source and refinement methods. As such, the specificgravity of your batch may differ from the table values. This will result in inaccurate amounts even with accurate measuring. Make certain you have at lease 3% superfatting in your recipes, to ensure an inaccuracy doesn't result in lye heavy soap. Measurement System------------------The measurement system (metric vs standard) can be changed by clicking the "convert measurements" button. This can be done at anytime and will convertall values from one system to the other. This allows you to enter metric orstandard (english) recipes then converting to your desired system. Conversionis done via an Excel macro, so if you do not enable macros (some people don'tfor fear of viruses) then conversion will not work. Conversion is the only thing using the macros, so if you don't convert measurements, disabling the macros will have no effect on your use of this spreadsheet.

SAP Values----------The SAP values used in calculating the lye are looked up from the SAP Values worksheet. If you disagree with any of the values, please feel free to change them. You may also change the names of any of the oils, just make sure you change any references on the Recipe worksheet from the old name to the new name. You may also add new oils, as long as you don't change the format of the SAP Values worksheet (this is very important, as the lookup from the Recipe worksheet depends upon the oil names and SAP values being in specific columns, and within a certain range of rows). If you do add new oils, you can either insert them into the list where you want or put them at the end; order is not important, just don't go past row 200 or so. Note too that the oils in the drop down list on the Recipe sheet appear in the same order as on the SAP Values sheet, so if you don't insert new oils in alphabetical order, they won't appear that way in the list either. To save any changes/additions you make to the SAP Values worksheet, so that any new workbooks you create will include them, see "Changing the template" below. Any additions need to include a price, even zero,to prevent unexpected and inaccurate results from costing and change measurementsinteraction.

Costing-------The spreadsheet will attempt to computer the cost of the soap you make by batch and bar. It does this based on the Cost/Oz column in 'SAP Values' Oil table.This is an example of how I would add a value: I bought a gallon of Coconut oilfor $9.90, so I entered '=9.90/128' into the cell E24 (in Coconut oil row of the Oil table). The 9.90 is the price and the 128 is the ounces per gallon. In truth the oil probably would actually weigh about 118 ounces, but this was a quick and dirty estimate of cost. If you do this for a living, then you might want to factor in specific gravity to convert liquid measurements to weights. eg: '=9.9/(128*F24)'. F24, in this case, is the specific gravity of Coconut oil.

For costing, add any non-oil ingredient (except Lye) to the bottom of theOil table. There is no need to put in any values except the name and price.Add specific gravity would help in computing volume measurements (useful with glycerin), but wouldn't be used for most fragrances.

The price for Lye is set up the same way, in the 'Tables' Type_of_Lye table,in the 'E' column. Eg: For a $3.79, 18 Oz bottle of Lye, click cell E103, type '=3.79/18'.

Many of the price values enter were based on prices I looked up on the web. Some maydiffer dramatically for you, depending on the amount and source of ingredients you buy. If you want accuracy, modify the prices based on your purchases.

IMPORTANT: If pricing values are missing for an Oil you use, then that cost willnot be reflected in the reported cost. No warning will appear.

INS Values of Recipes---------------------The concept of INS values is borrowed from Dr Robert McDaniel's book, "Essentially Soap." He in turn borrowed it from an unattributed source. In any event, what he says is that an oil's INS value is based on its SAP value and iodine value, and that a soap's INS is the weighted average of the INS values of its constituent oils. What does that mean to you? Probably not much. The important thing is that (as related by Dr Bob) the 'ideal' INS value to shoot for when formulating a soap recipe is 160. Now remember: 160 is only the ideal. Most recipes, even really excellent ones, won't be at an INS of 160. The majority of the ones I've looked at are in the mid 140's or low 150's. The INS value's real utility is as a sort of rough gauge of how well your recipe is balanced. In other words, if your recipe's INS is much above or way below 160, you might want to take another look at things.

INS Values of Individual Oils-----------------------------Many of the INS values for individual oils are borrowed from Dr Bob. Specifically, the sheet calculates its own INS values based on the best SAP and iodine values I could find, but where Dr Bob had a value, I defer to him. You may note that a few of the oils listed have no INS. That's because Dr Bob didn't list them, and I couldn't find iodine value numbers for them either. You can still use those oils in your recipes, but they will be ignored in the INS calculation for the recipe as a whole.

Canola versus Rapeseed oil--------------------------All canola oil is rapeseed oil, but not all rapeseed oil is canola. The reason for this is that rapeseed oil normally has an erucic acid content of up to fifty percent, while in order for an oil to be called canola, it may have an erucic acid content of no more than two percent. In other words, canola is low erucic acid rapeseed oil. What does this mean to you? Well, the erucic acid of regular rapeseed is largely replaced by oleic and linoleic acids in canola, which CHANGES THE SAP VALUE. I have yet to see a saponification chart, either published or on the web, which takes this into account. Every one of them treats rape and canola interchangeably, and lists the SAP of regular rape, even though the availability of regular rapeseed oil to the average North American consumer is limited at best. Is this dangerous? Since the SAP of rape is lower than that of canola, no, not really. It just means you're building an additional lye discount into any soap that uses canola. Which isn't much of a problem unless your recipe is already at the extreme of superfatting, or you're trying to make transparent soap. Bottom line: use canola for canola, and any of the other names (ramic/rape/rapeseed) for regular rapeseed oil.

Lye calculation and rounding----------------------------Because most people don't have a way to accurately measure amounts smaller than .1 ounces (well, I don't), the calculated amounts for lye and water are rounded DOWN to the nearest .1 ounce. If you must round, rounding down is the only safe way to go for lye, and unless you are making very small batches, a difference of less than one tenth of an ounce should have very little effect. Note that you may still enter amounts of oil to any level of precision you want, you just won't ever get more than one decimal place of precision for the lye and water.

Water calculation-----------------The amount of water required is calculated based on the rule of thumb of one pound of water for every three pounds of oils. There are other rules of thumb out there, e.g. six ounces of water per pound of oils, ala the MMS calculator, but I prefer the 1:3 rule.

To change the Oil to Water ratio enter the number of pounds of oil per pound ofwater in the oil into the water ratio entry field (upper left hand of Recipe worksheet).

The water value can be overridden (typed in manually), the rest of the calculationswill adjust, but this will prevent automatic calculation of water on thissheet. If you always create a new worksheet from the template for each newrecipe, it will only effect the recipe you manually overrode.

DWCP % (Experienced Soapmakers only)-------------------DWCP %: For Discounted Water Cold Process, enter a value from 33% to 50% (the maximum percentage range for disounting water; below 32 results in a "wet" batch and above 50 results in unreacted sodium hydroxide.)

The water calculation of "Recipe Fat/Water ratio:" is disabled when using the DWCP %.

After Cure calculation----------------------The After Cure weight is an estimate of the total yield of soap after most of the water has cured out of your bars. It is based on the rule of thumb that when your soap is done curing it will contain about 4% water, by weight.The actual water percent will be calculated and appear in the After Cure line.

Default values--------------Every new soapsheet workbook will have a default lye discount of 0%, bar size of 4 ounces, Oil-Water ratio of 3, scale factor of 1, and a oil & lye pricing info. Any of these may be changed. To save your changes for future workbooks, see "Changing the template" below.

Changing the template---------------------Here's how to change the template:

1) Create a new soapsheet workbook.2) Make your changes/additions to SAP Values, bar size, lye discount, and/or scale factor.3) From the File menu, click Save As.4) In the 'Save as type' drop-down list box on the 'Save As' dialog, choose 'Template (*.xlt)'. This choice should automatically change the 'Save in' directory to your Templates directory.5) In the 'File name' box of the 'Save As' dialog enter 'soapsheet' (the original name), or whatever name you would like to use for the new version of the template.6) If you are replacing the template you started with in step one, you will be prompted whether or not to overwrite the original. Click 'Yes'.7) You're done.

Hi, just a few notes about this template, how to use the workbooks created from it, and the methods used for certain operations. There's a short FAQ at the very end of this document. Notes in blue were added by Kathleen Koch January 2005.

Installation------------(These instructions are based on installing the template for use by versions as early as MSExcel97, on a machine running MSWindows95/98/ME/NT/XP.)

The best place to keep this template is in your Templates folder. Your Templates folder should be in the folder in which you installed Microsoft Office or Microsoft Excel. For example: on my machine the Templates folder is 'C:\Program Files\Microsoft Office\Templates'.

Basic usage-----------Once you have saved the template to your Templates folder, here's how to create new workbooks from it:1) Start Excel2) Click 'New' on the 'File' menu, which opens up the 'New' dialog box.3) On the 'General' tab of the 'New' dialog, pick 'soapsheet.xlt'.4) Click the OK button.5) A new workbook called soapsheet1 should appear in Excel.

There are only two worksheets in this workbook that you need to be concerned with when you play with your soap recipes. They are the sheets labeled "Recipe Worksheet" (the one you're on by default when you create a new workbook from the template) and "Scaled Version". The fields that may be changed on the Recipe worksheet are:

- Recipe Name- fields under the "Oil" heading- fields under the "Super-fatting Oil" heading- fields under the "Weight in Oz" headings for oils and super-fatting oils- Lye discount- DWCP % (Experienced Soapmakers only)- Bar Size (Oz)- Oil to Water ratio- Water (if water is overridden, then auto calc will cease for water field)(With the addition of the DWCP % field, this field is disabled)- Notes

Entering a recipe is as easy as picking oils from the drop-down list attached to the Oil fields and entering the amount of each you want to use. Then, if you want, enter super-fatting oils the same way, enter a lye discount, and perhaps change the bar size. That's it. Everything else will be calculated for you.

The Scaled Version sheet allows you to take the recipe entered on the Recipe sheet and scale it to whatever batch size you want. It shows everything from the Recipe sheet except INS values, and includes the additional field "Scale Factor". Scale Factor is the only field that may be changed on the Scaled Version sheet, and is used as follows: to scale a recipe down to one half of its original size, enter a Scale Factor of .5. To double a recipe, enter a Scale Factor of 2. Enter whatever factor you want, 2 and .5 are just examples. You get the idea.

The AutoScaled sheet allows you to enter a mold size and get the recipe scaled to your mold.

All fields titled in dark blue are for enterable values. For easier viewing of these areas, they have been highlighted in yellow.

Fluid Measurements------------------This spreadsheet will automatically compute the number of fluid ounces (or milliliters) from the oil weights entered, assuming the specific gravity is available for that oil.

Be aware, the same care must be taken measuring using liquid measurements.If you normally measure weights to within a tenth of an ounce and want to measure by volume, then the same precision is necessary (to the tenth of a fluid ounce). The gradations of a normal measuring cup would not be sufficiently accurate.

It is important to note that natural materials, such as oils and fats varygreatly, depending on source and refinement methods. As such, the specificgravity of your batch may differ from the table values. This will result in inaccurate amounts even with accurate measuring. Make certain you have at lease 3% superfatting in your recipes, to ensure an inaccuracy doesn't result in lye heavy soap. Measurement System------------------The measurement system (metric vs standard) can be changed by clicking the "convert measurements" button. This can be done at anytime and will convertall values from one system to the other. This allows you to enter metric orstandard (english) recipes then converting to your desired system. Conversionis done via an Excel macro, so if you do not enable macros (some people don'tfor fear of viruses) then conversion will not work. Conversion is the only thing using the macros, so if you don't convert measurements, disabling the macros will have no effect on your use of this spreadsheet.

SAP Values----------The SAP values used in calculating the lye are looked up from the SAP Values worksheet. If you disagree with any of the values, please feel free to change them. You may also change the names of any of the oils, just make sure you change any references on the Recipe worksheet from the old name to the new name. You may also add new oils, as long as you don't change the format of the SAP Values worksheet (this is very important, as the lookup from the Recipe worksheet depends upon the oil names and SAP values being in specific columns, and within a certain range of rows). If you do add new oils, you can either insert them into the list where you want or put them at the end; order is not important, just don't go past row 200 or so. Note too that the oils in the drop down list on the Recipe sheet appear in the same order as on the SAP Values sheet, so if you don't insert new oils in alphabetical order, they won't appear that way in the list either. To save any changes/additions you make to the SAP Values worksheet, so that any new workbooks you create will include them, see "Changing the template" below. Any additions need to include a price, even zero,to prevent unexpected and inaccurate results from costing and change measurementsinteraction.

Costing-------The spreadsheet will attempt to computer the cost of the soap you make by batch and bar. It does this based on the Cost/Oz column in 'SAP Values' Oil table.This is an example of how I would add a value: I bought a gallon of Coconut oilfor $9.90, so I entered '=9.90/128' into the cell E24 (in Coconut oil row of the Oil table). The 9.90 is the price and the 128 is the ounces per gallon. In truth the oil probably would actually weigh about 118 ounces, but this was a quick and dirty estimate of cost. If you do this for a living, then you might want to factor in specific gravity to convert liquid measurements to weights. eg: '=9.9/(128*F24)'. F24, in this case, is the specific gravity of Coconut oil.

For costing, add any non-oil ingredient (except Lye) to the bottom of theOil table. There is no need to put in any values except the name and price.Add specific gravity would help in computing volume measurements (useful with glycerin), but wouldn't be used for most fragrances.

The price for Lye is set up the same way, in the 'Tables' Type_of_Lye table,in the 'E' column. Eg: For a $3.79, 18 Oz bottle of Lye, click cell E103, type '=3.79/18'.

Many of the price values enter were based on prices I looked up on the web. Some maydiffer dramatically for you, depending on the amount and source of ingredients you buy. If you want accuracy, modify the prices based on your purchases.

IMPORTANT: If pricing values are missing for an Oil you use, then that cost willnot be reflected in the reported cost. No warning will appear.

INS Values of Recipes---------------------The concept of INS values is borrowed from Dr Robert McDaniel's book, "Essentially Soap." He in turn borrowed it from an unattributed source. In any event, what he says is that an oil's INS value is based on its SAP value and iodine value, and that a soap's INS is the weighted average of the INS values of its constituent oils. What does that mean to you? Probably not much. The important thing is that (as related by Dr Bob) the 'ideal' INS value to shoot for when formulating a soap recipe is 160. Now remember: 160 is only the ideal. Most recipes, even really excellent ones, won't be at an INS of 160. The majority of the ones I've looked at are in the mid 140's or low 150's. The INS value's real utility is as a sort of rough gauge of how well your recipe is balanced. In other words, if your recipe's INS is much above or way below 160, you might want to take another look at things.

INS Values of Individual Oils-----------------------------Many of the INS values for individual oils are borrowed from Dr Bob. Specifically, the sheet calculates its own INS values based on the best SAP and iodine values I could find, but where Dr Bob had a value, I defer to him. You may note that a few of the oils listed have no INS. That's because Dr Bob didn't list them, and I couldn't find iodine value numbers for them either. You can still use those oils in your recipes, but they will be ignored in the INS calculation for the recipe as a whole.

Canola versus Rapeseed oil--------------------------All canola oil is rapeseed oil, but not all rapeseed oil is canola. The reason for this is that rapeseed oil normally has an erucic acid content of up to fifty percent, while in order for an oil to be called canola, it may have an erucic acid content of no more than two percent. In other words, canola is low erucic acid rapeseed oil. What does this mean to you? Well, the erucic acid of regular rapeseed is largely replaced by oleic and linoleic acids in canola, which CHANGES THE SAP VALUE. I have yet to see a saponification chart, either published or on the web, which takes this into account. Every one of them treats rape and canola interchangeably, and lists the SAP of regular rape, even though the availability of regular rapeseed oil to the average North American consumer is limited at best. Is this dangerous? Since the SAP of rape is lower than that of canola, no, not really. It just means you're building an additional lye discount into any soap that uses canola. Which isn't much of a problem unless your recipe is already at the extreme of superfatting, or you're trying to make transparent soap. Bottom line: use canola for canola, and any of the other names (ramic/rape/rapeseed) for regular rapeseed oil.

Lye calculation and rounding----------------------------Because most people don't have a way to accurately measure amounts smaller than .1 ounces (well, I don't), the calculated amounts for lye and water are rounded DOWN to the nearest .1 ounce. If you must round, rounding down is the only safe way to go for lye, and unless you are making very small batches, a difference of less than one tenth of an ounce should have very little effect. Note that you may still enter amounts of oil to any level of precision you want, you just won't ever get more than one decimal place of precision for the lye and water.

Water calculation-----------------The amount of water required is calculated based on the rule of thumb of one pound of water for every three pounds of oils. There are other rules of thumb out there, e.g. six ounces of water per pound of oils, ala the MMS calculator, but I prefer the 1:3 rule.

To change the Oil to Water ratio enter the number of pounds of oil per pound ofwater in the oil into the water ratio entry field (upper left hand of Recipe worksheet).

The water value can be overridden (typed in manually), the rest of the calculationswill adjust, but this will prevent automatic calculation of water on thissheet. If you always create a new worksheet from the template for each newrecipe, it will only effect the recipe you manually overrode.

DWCP % (Experienced Soapmakers only)-------------------DWCP %: For Discounted Water Cold Process, enter a value from 33% to 50% (the maximum percentage range for disounting water; below 32 results in a "wet" batch and above 50 results in unreacted sodium hydroxide.)

The water calculation of "Recipe Fat/Water ratio:" is disabled when using the DWCP %.

After Cure calculation----------------------The After Cure weight is an estimate of the total yield of soap after most of the water has cured out of your bars. It is based on the rule of thumb that when your soap is done curing it will contain about 4% water, by weight.The actual water percent will be calculated and appear in the After Cure line.

Default values--------------Every new soapsheet workbook will have a default lye discount of 0%, bar size of 4 ounces, Oil-Water ratio of 3, scale factor of 1, and a oil & lye pricing info. Any of these may be changed. To save your changes for future workbooks, see "Changing the template" below.

Changing the template---------------------Here's how to change the template:

1) Create a new soapsheet workbook.2) Make your changes/additions to SAP Values, bar size, lye discount, and/or scale factor.3) From the File menu, click Save As.4) In the 'Save as type' drop-down list box on the 'Save As' dialog, choose 'Template (*.xlt)'. This choice should automatically change the 'Save in' directory to your Templates directory.5) In the 'File name' box of the 'Save As' dialog enter 'soapsheet' (the original name), or whatever name you would like to use for the new version of the template.6) If you are replacing the template you started with in step one, you will be prompted whether or not to overwrite the original. Click 'Yes'.7) You're done.

FAQ's-----

1) What the heck is a '.xlt' file?

The .xlt, versus .xls, indicates that it is an Excel spreadsheet _template_, not actually a spreadsheet. Every time you open it, it will create a new workbook in Excel that you will be asked to name when you save (i.e. it's hitting the `New' button to create a new workbook, but instead of being blank, it's got all of this stuff from the template in it already). Normally templates are kept in the Templates directory under the directory where Microsoft Office was installed. For me, under WindowsNT 4.0, that defaults to c:\program files\microsoft office\templates. Once the template is installed in the templates directory, picking File>New off of Excel's menu will pop up a dialog for choosing a template or wizard for the new workbook. Just pick 'soapsheet.xlt' from the General tab, and you are good to go.

2) Why is the total effective lye discount greater than my lye discount, even though I specified no superfatting oils?

3) Why is the total effective lye discount different for the scaled version of my recipe?

The answer to these two questions has to do with the fact that lye amounts are rounded down to the nearest tenth of an ounce, as detailed in "Lye calculation and rounding" above. To answer the first question, by way of example: if you specify a lye discount of 5%, which puts the lye amount at, for example, 7.39 ounces, the number 7.39 will be rounded down to 7.3, giving you a total effective lye discount of almost 6.2%. That's a 1.2% higher discount, all because you lost .09 ounces of lye to rounding. Are you with me so far? Now on to question two... The reason why the total effective lye discount may be a different value on the scaled version worksheet than the recipe worksheet is that the amount being rounded off of the lye amount will almost certainly change, and it will be a different proportion to the lye amount than in the unscaled version. For example: say I have a recipe with a 5% lye discount, which puts the lye amount at 8.66 ounces, which rounds down to 8.6, giving me a total effective lye discount of around 5.7%. Then I go to the scaled version worksheet and double the batch size. Now my 5% discount puts the lye amount at 17.32 ounces, which rounds down to 17.3, giving me a total effective lye discount of only about 5.2%. This effect can work the opposite way too, with the total effective lye discount going up when you change the batch size. It is even more pronounced when you scale down to smaller batch sizes, because even a small amount rounded off is a proportionately larger change than in a large batch. I hope that makes sense to everyone. There's no way around this without taking a smaller lye discount than what you originally entered. Using metric measures will drop the roundoff difference, making the entered and effective lye discounts closer.

Using Metric measurements will show a smaller than expected difference in effective lye discount. It is assumed that by measuring in grams higher accuracy will be attained (a gram is equal to 1/20 of an ounce).

Auto Scaling

An auto scaled worksheet is provided so you can just enter a recipe you like then go to the auto scaled sheet and enter the dimensions of the mold (andthe mold type you want to use). The correctly scaled recipe will be produced to the size / type of mold you enter. It will also produce a scaling factor,for those interested. When using DWCP%, the computed weight of the oils will increase as the water is decreased to ensure the mold is filled to the specified dimensions.

If you have a specific mold you always use, then putting it's dimensions intothe worksheet template, then saving in the template directory should save time when scaling the recipe.

The volume to weight conversion factors are located on the 'Tables' worksheetas "Volume Conversion Factor" and correspond to a 4/3 ratio of total weight to oil weight times the avg oils specific gravity, then inverted. This will multiply times the volume (in Fl Oz or milliters) to yeild the weight of oil (in Oz or gms). The ratio of the computed weight to the weight on the 'Recipe Worksheet' is displayed and used to compute the displayed ingredientsamounts.

Source of Information---------------------

Information, such as missing Iodine values and Specific Gravities comes from the 38'th and 50'th Revisions of The Chemical Rubber Company Handbook of Chemistry and Physics. The CRC handbooks has been considered the most authoritative reference source for Chemistry or Physics to be found in a single book.

FAQ's-----

1) What the heck is a '.xlt' file?

The .xlt, versus .xls, indicates that it is an Excel spreadsheet _template_, not actually a spreadsheet. Every time you open it, it will create a new workbook in Excel that you will be asked to name when you save (i.e. it's hitting the `New' button to create a new workbook, but instead of being blank, it's got all of this stuff from the template in it already). Normally templates are kept in the Templates directory under the directory where Microsoft Office was installed. For me, under WindowsNT 4.0, that defaults to c:\program files\microsoft office\templates. Once the template is installed in the templates directory, picking File>New off of Excel's menu will pop up a dialog for choosing a template or wizard for the new workbook. Just pick 'soapsheet.xlt' from the General tab, and you are good to go.

2) Why is the total effective lye discount greater than my lye discount, even though I specified no superfatting oils?

3) Why is the total effective lye discount different for the scaled version of my recipe?

The answer to these two questions has to do with the fact that lye amounts are rounded down to the nearest tenth of an ounce, as detailed in "Lye calculation and rounding" above. To answer the first question, by way of example: if you specify a lye discount of 5%, which puts the lye amount at, for example, 7.39 ounces, the number 7.39 will be rounded down to 7.3, giving you a total effective lye discount of almost 6.2%. That's a 1.2% higher discount, all because you lost .09 ounces of lye to rounding. Are you with me so far? Now on to question two... The reason why the total effective lye discount may be a different value on the scaled version worksheet than the recipe worksheet is that the amount being rounded off of the lye amount will almost certainly change, and it will be a different proportion to the lye amount than in the unscaled version. For example: say I have a recipe with a 5% lye discount, which puts the lye amount at 8.66 ounces, which rounds down to 8.6, giving me a total effective lye discount of around 5.7%. Then I go to the scaled version worksheet and double the batch size. Now my 5% discount puts the lye amount at 17.32 ounces, which rounds down to 17.3, giving me a total effective lye discount of only about 5.2%. This effect can work the opposite way too, with the total effective lye discount going up when you change the batch size. It is even more pronounced when you scale down to smaller batch sizes, because even a small amount rounded off is a proportionately larger change than in a large batch. I hope that makes sense to everyone. There's no way around this without taking a smaller lye discount than what you originally entered. Using metric measures will drop the roundoff difference, making the entered and effective lye discounts closer.

Using Metric measurements will show a smaller than expected difference in effective lye discount. It is assumed that by measuring in grams higher accuracy will be attained (a gram is equal to 1/20 of an ounce).

Auto Scaling

An auto scaled worksheet is provided so you can just enter a recipe you like then go to the auto scaled sheet and enter the dimensions of the mold (andthe mold type you want to use). The correctly scaled recipe will be produced to the size / type of mold you enter. It will also produce a scaling factor,for those interested. When using DWCP%, the computed weight of the oils will increase as the water is decreased to ensure the mold is filled to the specified dimensions.

If you have a specific mold you always use, then putting it's dimensions intothe worksheet template, then saving in the template directory should save time when scaling the recipe.

The volume to weight conversion factors are located on the 'Tables' worksheetas "Volume Conversion Factor" and correspond to a 4/3 ratio of total weight to oil weight times the avg oils specific gravity, then inverted. This will multiply times the volume (in Fl Oz or milliters) to yeild the weight of oil (in Oz or gms). The ratio of the computed weight to the weight on the 'Recipe Worksheet' is displayed and used to compute the displayed ingredientsamounts.

FAQ's-----

1) What the heck is a '.xlt' file?

The .xlt, versus .xls, indicates that it is an Excel spreadsheet _template_, not actually a spreadsheet. Every time you open it, it will create a new workbook in Excel that you will be asked to name when you save (i.e. it's hitting the `New' button to create a new workbook, but instead of being blank, it's got all of this stuff from the template in it already). Normally templates are kept in the Templates directory under the directory where Microsoft Office was installed. For me, under WindowsNT 4.0, that defaults to c:\program files\microsoft office\templates. Once the template is installed in the templates directory, picking File>New off of Excel's menu will pop up a dialog for choosing a template or wizard for the new workbook. Just pick 'soapsheet.xlt' from the General tab, and you are good to go.

2) Why is the total effective lye discount greater than my lye discount, even though I specified no superfatting oils?

3) Why is the total effective lye discount different for the scaled version of my recipe?

The answer to these two questions has to do with the fact that lye amounts are rounded down to the nearest tenth of an ounce, as detailed in "Lye calculation and rounding" above. To answer the first question, by way of example: if you specify a lye discount of 5%, which puts the lye amount at, for example, 7.39 ounces, the number 7.39 will be rounded down to 7.3, giving you a total effective lye discount of almost 6.2%. That's a 1.2% higher discount, all because you lost .09 ounces of lye to rounding. Are you with me so far? Now on to question two... The reason why the total effective lye discount may be a different value on the scaled version worksheet than the recipe worksheet is that the amount being rounded off of the lye amount will almost certainly change, and it will be a different proportion to the lye amount than in the unscaled version. For example: say I have a recipe with a 5% lye discount, which puts the lye amount at 8.66 ounces, which rounds down to 8.6, giving me a total effective lye discount of around 5.7%. Then I go to the scaled version worksheet and double the batch size. Now my 5% discount puts the lye amount at 17.32 ounces, which rounds down to 17.3, giving me a total effective lye discount of only about 5.2%. This effect can work the opposite way too, with the total effective lye discount going up when you change the batch size. It is even more pronounced when you scale down to smaller batch sizes, because even a small amount rounded off is a proportionately larger change than in a large batch. I hope that makes sense to everyone. There's no way around this without taking a smaller lye discount than what you originally entered. Using metric measures will drop the roundoff difference, making the entered and effective lye discounts closer.

Using Metric measurements will show a smaller than expected difference in effective lye discount. It is assumed that by measuring in grams higher accuracy will be attained (a gram is equal to 1/20 of an ounce).

Auto Scaling

An auto scaled worksheet is provided so you can just enter a recipe you like then go to the auto scaled sheet and enter the dimensions of the mold (andthe mold type you want to use). The correctly scaled recipe will be produced to the size / type of mold you enter. It will also produce a scaling factor,for those interested. When using DWCP%, the computed weight of the oils will increase as the water is decreased to ensure the mold is filled to the specified dimensions.

If you have a specific mold you always use, then putting it's dimensions intothe worksheet template, then saving in the template directory should save time when scaling the recipe.

The volume to weight conversion factors are located on the 'Tables' worksheetas "Volume Conversion Factor" and correspond to a 4/3 ratio of total weight to oil weight times the avg oils specific gravity, then inverted. This will multiply times the volume (in Fl Oz or milliters) to yeild the weight of oil (in Oz or gms). The ratio of the computed weight to the weight on the 'Recipe Worksheet' is displayed and used to compute the displayed ingredientsamounts.

Hi, just a few notes about this template, how to use the workbooks created from it, and the methods used for certain operations. There's a short FAQ at the very end of this document. Notes in blue were added by Kathleen Koch January 2005.

Installation------------(These instructions are based on installing the template for use by versions as early as MSExcel97, on a machine running MSWindows95/98/ME/NT/XP.)

The best place to keep this template is in your Templates folder. Your Templates folder should be in the folder in which you installed Microsoft Office or Microsoft Excel. For example: on my machine the Templates folder is 'C:\Program Files\Microsoft Office\Templates'.

Basic usage-----------Once you have saved the template to your Templates folder, here's how to create new workbooks from it:1) Start Excel2) Click 'New' on the 'File' menu, which opens up the 'New' dialog box.3) On the 'General' tab of the 'New' dialog, pick 'soapsheet.xlt'.4) Click the OK button.5) A new workbook called soapsheet1 should appear in Excel.

There are only two worksheets in this workbook that you need to be concerned with when you play with your soap recipes. They are the sheets labeled "Recipe Worksheet" (the one you're on by default when you create a new workbook from the template) and "Scaled Version". The fields that may be changed on the Recipe worksheet are:

- Recipe Name- fields under the "Oil" heading- fields under the "Super-fatting Oil" heading- fields under the "Weight in Oz" headings for oils and super-fatting oils- Lye discount- DWCP % (Experienced Soapmakers only)- Bar Size (Oz)- Oil to Water ratio- Water (if water is overridden, then auto calc will cease for water field)(With the addition of the DWCP % field, this field is disabled)- Notes

Entering a recipe is as easy as picking oils from the drop-down list attached to the Oil fields and entering the amount of each you want to use. Then, if you want, enter super-fatting oils the same way, enter a lye discount, and perhaps change the bar size. That's it. Everything else will be calculated for you.

The Scaled Version sheet allows you to take the recipe entered on the Recipe sheet and scale it to whatever batch size you want. It shows everything from the Recipe sheet except INS values, and includes the additional field "Scale Factor". Scale Factor is the only field that may be changed on the Scaled Version sheet, and is used as follows: to scale a recipe down to one half of its original size, enter a Scale Factor of .5. To double a recipe, enter a Scale Factor of 2. Enter whatever factor you want, 2 and .5 are just examples. You get the idea.

The AutoScaled sheet allows you to enter a mold size and get the recipe scaled to your mold.

All fields titled in dark blue are for enterable values. For easier viewing of these areas, they have been highlighted in yellow.

Fluid Measurements------------------This spreadsheet will automatically compute the number of fluid ounces (or milliliters) from the oil weights entered, assuming the specific gravity is available for that oil.

Be aware, the same care must be taken measuring using liquid measurements.If you normally measure weights to within a tenth of an ounce and want to measure by volume, then the same precision is necessary (to the tenth of a fluid ounce). The gradations of a normal measuring cup would not be sufficiently accurate.

It is important to note that natural materials, such as oils and fats varygreatly, depending on source and refinement methods. As such, the specificgravity of your batch may differ from the table values. This will result in inaccurate amounts even with accurate measuring. Make certain you have at lease 3% superfatting in your recipes, to ensure an inaccuracy doesn't result in lye heavy soap. Measurement System------------------The measurement system (metric vs standard) can be changed by clicking the "convert measurements" button. This can be done at anytime and will convertall values from one system to the other. This allows you to enter metric orstandard (english) recipes then converting to your desired system. Conversionis done via an Excel macro, so if you do not enable macros (some people don'tfor fear of viruses) then conversion will not work. Conversion is the only thing using the macros, so if you don't convert measurements, disabling the macros will have no effect on your use of this spreadsheet.

SAP Values----------The SAP values used in calculating the lye are looked up from the SAP Values worksheet. If you disagree with any of the values, please feel free to change them. You may also change the names of any of the oils, just make sure you change any references on the Recipe worksheet from the old name to the new name. You may also add new oils, as long as you don't change the format of the SAP Values worksheet (this is very important, as the lookup from the Recipe worksheet depends upon the oil names and SAP values being in specific columns, and within a certain range of rows). If you do add new oils, you can either insert them into the list where you want or put them at the end; order is not important, just don't go past row 200 or so. Note too that the oils in the drop down list on the Recipe sheet appear in the same order as on the SAP Values sheet, so if you don't insert new oils in alphabetical order, they won't appear that way in the list either. To save any changes/additions you make to the SAP Values worksheet, so that any new workbooks you create will include them, see "Changing the template" below. Any additions need to include a price, even zero,to prevent unexpected and inaccurate results from costing and change measurementsinteraction.

Costing-------The spreadsheet will attempt to computer the cost of the soap you make by batch and bar. It does this based on the Cost/Oz column in 'SAP Values' Oil table.This is an example of how I would add a value: I bought a gallon of Coconut oilfor $9.90, so I entered '=9.90/128' into the cell E24 (in Coconut oil row of the Oil table). The 9.90 is the price and the 128 is the ounces per gallon. In truth the oil probably would actually weigh about 118 ounces, but this was a quick and dirty estimate of cost. If you do this for a living, then you might want to factor in specific gravity to convert liquid measurements to weights. eg: '=9.9/(128*F24)'. F24, in this case, is the specific gravity of Coconut oil.

For costing, add any non-oil ingredient (except Lye) to the bottom of theOil table. There is no need to put in any values except the name and price.Add specific gravity would help in computing volume measurements (useful with glycerin), but wouldn't be used for most fragrances.

The price for Lye is set up the same way, in the 'Tables' Type_of_Lye table,in the 'E' column. Eg: For a $3.79, 18 Oz bottle of Lye, click cell E103, type '=3.79/18'.

Many of the price values enter were based on prices I looked up on the web. Some maydiffer dramatically for you, depending on the amount and source of ingredients you buy. If you want accuracy, modify the prices based on your purchases.

IMPORTANT: If pricing values are missing for an Oil you use, then that cost willnot be reflected in the reported cost. No warning will appear.

INS Values of Recipes---------------------The concept of INS values is borrowed from Dr Robert McDaniel's book, "Essentially Soap." He in turn borrowed it from an unattributed source. In any event, what he says is that an oil's INS value is based on its SAP value and iodine value, and that a soap's INS is the weighted average of the INS values of its constituent oils. What does that mean to you? Probably not much. The important thing is that (as related by Dr Bob) the 'ideal' INS value to shoot for when formulating a soap recipe is 160. Now remember: 160 is only the ideal. Most recipes, even really excellent ones, won't be at an INS of 160. The majority of the ones I've looked at are in the mid 140's or low 150's. The INS value's real utility is as a sort of rough gauge of how well your recipe is balanced. In other words, if your recipe's INS is much above or way below 160, you might want to take another look at things.

INS Values of Individual Oils-----------------------------Many of the INS values for individual oils are borrowed from Dr Bob. Specifically, the sheet calculates its own INS values based on the best SAP and iodine values I could find, but where Dr Bob had a value, I defer to him. You may note that a few of the oils listed have no INS. That's because Dr Bob didn't list them, and I couldn't find iodine value numbers for them either. You can still use those oils in your recipes, but they will be ignored in the INS calculation for the recipe as a whole.

Canola versus Rapeseed oil--------------------------All canola oil is rapeseed oil, but not all rapeseed oil is canola. The reason for this is that rapeseed oil normally has an erucic acid content of up to fifty percent, while in order for an oil to be called canola, it may have an erucic acid content of no more than two percent. In other words, canola is low erucic acid rapeseed oil. What does this mean to you? Well, the erucic acid of regular rapeseed is largely replaced by oleic and linoleic acids in canola, which CHANGES THE SAP VALUE. I have yet to see a saponification chart, either published or on the web, which takes this into account. Every one of them treats rape and canola interchangeably, and lists the SAP of regular rape, even though the availability of regular rapeseed oil to the average North American consumer is limited at best. Is this dangerous? Since the SAP of rape is lower than that of canola, no, not really. It just means you're building an additional lye discount into any soap that uses canola. Which isn't much of a problem unless your recipe is already at the extreme of superfatting, or you're trying to make transparent soap. Bottom line: use canola for canola, and any of the other names (ramic/rape/rapeseed) for regular rapeseed oil.

Lye calculation and rounding----------------------------Because most people don't have a way to accurately measure amounts smaller than .1 ounces (well, I don't), the calculated amounts for lye and water are rounded DOWN to the nearest .1 ounce. If you must round, rounding down is the only safe way to go for lye, and unless you are making very small batches, a difference of less than one tenth of an ounce should have very little effect. Note that you may still enter amounts of oil to any level of precision you want, you just won't ever get more than one decimal place of precision for the lye and water.

Water calculation-----------------The amount of water required is calculated based on the rule of thumb of one pound of water for every three pounds of oils. There are other rules of thumb out there, e.g. six ounces of water per pound of oils, ala the MMS calculator, but I prefer the 1:3 rule.

To change the Oil to Water ratio enter the number of pounds of oil per pound ofwater in the oil into the water ratio entry field (upper left hand of Recipe worksheet).

The water value can be overridden (typed in manually), the rest of the calculationswill adjust, but this will prevent automatic calculation of water on thissheet. If you always create a new worksheet from the template for each newrecipe, it will only effect the recipe you manually overrode.

DWCP % (Experienced Soapmakers only)-------------------DWCP %: For Discounted Water Cold Process, enter a value from 33% to 50% (the maximum percentage range for disounting water; below 32 results in a "wet" batch and above 50 results in unreacted sodium hydroxide.)

The water calculation of "Recipe Fat/Water ratio:" is disabled when using the DWCP %.

After Cure calculation----------------------The After Cure weight is an estimate of the total yield of soap after most of the water has cured out of your bars. It is based on the rule of thumb that when your soap is done curing it will contain about 4% water, by weight.The actual water percent will be calculated and appear in the After Cure line.

Default values--------------Every new soapsheet workbook will have a default lye discount of 0%, bar size of 4 ounces, Oil-Water ratio of 3, scale factor of 1, and a oil & lye pricing info. Any of these may be changed. To save your changes for future workbooks, see "Changing the template" below.

Changing the template---------------------Here's how to change the template:

1) Create a new soapsheet workbook.2) Make your changes/additions to SAP Values, bar size, lye discount, and/or scale factor.3) From the File menu, click Save As.4) In the 'Save as type' drop-down list box on the 'Save As' dialog, choose 'Template (*.xlt)'. This choice should automatically change the 'Save in' directory to your Templates directory.5) In the 'File name' box of the 'Save As' dialog enter 'soapsheet' (the original name), or whatever name you would like to use for the new version of the template.6) If you are replacing the template you started with in step one, you will be prompted whether or not to overwrite the original. Click 'Yes'.7) You're done.

Hi, just a few notes about this template, how to use the workbooks created from it, and the methods used for certain operations. There's a short FAQ at the very end of this document. Notes in blue were added by Kathleen Koch January 2005.

Installation------------(These instructions are based on installing the template for use by versions as early as MSExcel97, on a machine running MSWindows95/98/ME/NT/XP.)

The best place to keep this template is in your Templates folder. Your Templates folder should be in the folder in which you installed Microsoft Office or Microsoft Excel. For example: on my machine the Templates folder is 'C:\Program Files\Microsoft Office\Templates'.

Basic usage-----------Once you have saved the template to your Templates folder, here's how to create new workbooks from it:1) Start Excel2) Click 'New' on the 'File' menu, which opens up the 'New' dialog box.3) On the 'General' tab of the 'New' dialog, pick 'soapsheet.xlt'.4) Click the OK button.5) A new workbook called soapsheet1 should appear in Excel.

There are only two worksheets in this workbook that you need to be concerned with when you play with your soap recipes. They are the sheets labeled "Recipe Worksheet" (the one you're on by default when you create a new workbook from the template) and "Scaled Version". The fields that may be changed on the Recipe worksheet are:

- Recipe Name- fields under the "Oil" heading- fields under the "Super-fatting Oil" heading- fields under the "Weight in Oz" headings for oils and super-fatting oils- Lye discount- DWCP % (Experienced Soapmakers only)- Bar Size (Oz)- Oil to Water ratio- Water (if water is overridden, then auto calc will cease for water field)(With the addition of the DWCP % field, this field is disabled)- Notes

Entering a recipe is as easy as picking oils from the drop-down list attached to the Oil fields and entering the amount of each you want to use. Then, if you want, enter super-fatting oils the same way, enter a lye discount, and perhaps change the bar size. That's it. Everything else will be calculated for you.

The Scaled Version sheet allows you to take the recipe entered on the Recipe sheet and scale it to whatever batch size you want. It shows everything from the Recipe sheet except INS values, and includes the additional field "Scale Factor". Scale Factor is the only field that may be changed on the Scaled Version sheet, and is used as follows: to scale a recipe down to one half of its original size, enter a Scale Factor of .5. To double a recipe, enter a Scale Factor of 2. Enter whatever factor you want, 2 and .5 are just examples. You get the idea.

The AutoScaled sheet allows you to enter a mold size and get the recipe scaled to your mold.

All fields titled in dark blue are for enterable values. For easier viewing of these areas, they have been highlighted in yellow.

Fluid Measurements------------------This spreadsheet will automatically compute the number of fluid ounces (or milliliters) from the oil weights entered, assuming the specific gravity is available for that oil.

Be aware, the same care must be taken measuring using liquid measurements.If you normally measure weights to within a tenth of an ounce and want to measure by volume, then the same precision is necessary (to the tenth of a fluid ounce). The gradations of a normal measuring cup would not be sufficiently accurate.

It is important to note that natural materials, such as oils and fats varygreatly, depending on source and refinement methods. As such, the specificgravity of your batch may differ from the table values. This will result in inaccurate amounts even with accurate measuring. Make certain you have at lease 3% superfatting in your recipes, to ensure an inaccuracy doesn't result in lye heavy soap. Measurement System------------------The measurement system (metric vs standard) can be changed by clicking the "convert measurements" button. This can be done at anytime and will convertall values from one system to the other. This allows you to enter metric orstandard (english) recipes then converting to your desired system. Conversionis done via an Excel macro, so if you do not enable macros (some people don'tfor fear of viruses) then conversion will not work. Conversion is the only thing using the macros, so if you don't convert measurements, disabling the macros will have no effect on your use of this spreadsheet.

SAP Values----------The SAP values used in calculating the lye are looked up from the SAP Values worksheet. If you disagree with any of the values, please feel free to change them. You may also change the names of any of the oils, just make sure you change any references on the Recipe worksheet from the old name to the new name. You may also add new oils, as long as you don't change the format of the SAP Values worksheet (this is very important, as the lookup from the Recipe worksheet depends upon the oil names and SAP values being in specific columns, and within a certain range of rows). If you do add new oils, you can either insert them into the list where you want or put them at the end; order is not important, just don't go past row 200 or so. Note too that the oils in the drop down list on the Recipe sheet appear in the same order as on the SAP Values sheet, so if you don't insert new oils in alphabetical order, they won't appear that way in the list either. To save any changes/additions you make to the SAP Values worksheet, so that any new workbooks you create will include them, see "Changing the template" below. Any additions need to include a price, even zero,to prevent unexpected and inaccurate results from costing and change measurementsinteraction.

Costing-------The spreadsheet will attempt to computer the cost of the soap you make by batch and bar. It does this based on the Cost/Oz column in 'SAP Values' Oil table.This is an example of how I would add a value: I bought a gallon of Coconut oilfor $9.90, so I entered '=9.90/128' into the cell E24 (in Coconut oil row of the Oil table). The 9.90 is the price and the 128 is the ounces per gallon. In truth the oil probably would actually weigh about 118 ounces, but this was a quick and dirty estimate of cost. If you do this for a living, then you might want to factor in specific gravity to convert liquid measurements to weights. eg: '=9.9/(128*F24)'. F24, in this case, is the specific gravity of Coconut oil.

For costing, add any non-oil ingredient (except Lye) to the bottom of theOil table. There is no need to put in any values except the name and price.Add specific gravity would help in computing volume measurements (useful with glycerin), but wouldn't be used for most fragrances.

The price for Lye is set up the same way, in the 'Tables' Type_of_Lye table,in the 'E' column. Eg: For a $3.79, 18 Oz bottle of Lye, click cell E103, type '=3.79/18'.

Many of the price values enter were based on prices I looked up on the web. Some maydiffer dramatically for you, depending on the amount and source of ingredients you buy. If you want accuracy, modify the prices based on your purchases.

IMPORTANT: If pricing values are missing for an Oil you use, then that cost willnot be reflected in the reported cost. No warning will appear.

INS Values of Recipes---------------------The concept of INS values is borrowed from Dr Robert McDaniel's book, "Essentially Soap." He in turn borrowed it from an unattributed source. In any event, what he says is that an oil's INS value is based on its SAP value and iodine value, and that a soap's INS is the weighted average of the INS values of its constituent oils. What does that mean to you? Probably not much. The important thing is that (as related by Dr Bob) the 'ideal' INS value to shoot for when formulating a soap recipe is 160. Now remember: 160 is only the ideal. Most recipes, even really excellent ones, won't be at an INS of 160. The majority of the ones I've looked at are in the mid 140's or low 150's. The INS value's real utility is as a sort of rough gauge of how well your recipe is balanced. In other words, if your recipe's INS is much above or way below 160, you might want to take another look at things.

INS Values of Individual Oils-----------------------------Many of the INS values for individual oils are borrowed from Dr Bob. Specifically, the sheet calculates its own INS values based on the best SAP and iodine values I could find, but where Dr Bob had a value, I defer to him. You may note that a few of the oils listed have no INS. That's because Dr Bob didn't list them, and I couldn't find iodine value numbers for them either. You can still use those oils in your recipes, but they will be ignored in the INS calculation for the recipe as a whole.

Canola versus Rapeseed oil--------------------------All canola oil is rapeseed oil, but not all rapeseed oil is canola. The reason for this is that rapeseed oil normally has an erucic acid content of up to fifty percent, while in order for an oil to be called canola, it may have an erucic acid content of no more than two percent. In other words, canola is low erucic acid rapeseed oil. What does this mean to you? Well, the erucic acid of regular rapeseed is largely replaced by oleic and linoleic acids in canola, which CHANGES THE SAP VALUE. I have yet to see a saponification chart, either published or on the web, which takes this into account. Every one of them treats rape and canola interchangeably, and lists the SAP of regular rape, even though the availability of regular rapeseed oil to the average North American consumer is limited at best. Is this dangerous? Since the SAP of rape is lower than that of canola, no, not really. It just means you're building an additional lye discount into any soap that uses canola. Which isn't much of a problem unless your recipe is already at the extreme of superfatting, or you're trying to make transparent soap. Bottom line: use canola for canola, and any of the other names (ramic/rape/rapeseed) for regular rapeseed oil.

Lye calculation and rounding----------------------------Because most people don't have a way to accurately measure amounts smaller than .1 ounces (well, I don't), the calculated amounts for lye and water are rounded DOWN to the nearest .1 ounce. If you must round, rounding down is the only safe way to go for lye, and unless you are making very small batches, a difference of less than one tenth of an ounce should have very little effect. Note that you may still enter amounts of oil to any level of precision you want, you just won't ever get more than one decimal place of precision for the lye and water.

Water calculation-----------------The amount of water required is calculated based on the rule of thumb of one pound of water for every three pounds of oils. There are other rules of thumb out there, e.g. six ounces of water per pound of oils, ala the MMS calculator, but I prefer the 1:3 rule.

To change the Oil to Water ratio enter the number of pounds of oil per pound ofwater in the oil into the water ratio entry field (upper left hand of Recipe worksheet).

The water value can be overridden (typed in manually), the rest of the calculationswill adjust, but this will prevent automatic calculation of water on thissheet. If you always create a new worksheet from the template for each newrecipe, it will only effect the recipe you manually overrode.

DWCP % (Experienced Soapmakers only)-------------------DWCP %: For Discounted Water Cold Process, enter a value from 33% to 50% (the maximum percentage range for disounting water; below 32 results in a "wet" batch and above 50 results in unreacted sodium hydroxide.)

The water calculation of "Recipe Fat/Water ratio:" is disabled when using the DWCP %.

After Cure calculation----------------------The After Cure weight is an estimate of the total yield of soap after most of the water has cured out of your bars. It is based on the rule of thumb that when your soap is done curing it will contain about 4% water, by weight.The actual water percent will be calculated and appear in the After Cure line.

Default values--------------Every new soapsheet workbook will have a default lye discount of 0%, bar size of 4 ounces, Oil-Water ratio of 3, scale factor of 1, and a oil & lye pricing info. Any of these may be changed. To save your changes for future workbooks, see "Changing the template" below.

Changing the template---------------------Here's how to change the template:

1) Create a new soapsheet workbook.2) Make your changes/additions to SAP Values, bar size, lye discount, and/or scale factor.3) From the File menu, click Save As.4) In the 'Save as type' drop-down list box on the 'Save As' dialog, choose 'Template (*.xlt)'. This choice should automatically change the 'Save in' directory to your Templates directory.5) In the 'File name' box of the 'Save As' dialog enter 'soapsheet' (the original name), or whatever name you would like to use for the new version of the template.6) If you are replacing the template you started with in step one, you will be prompted whether or not to overwrite the original. Click 'Yes'.7) You're done.

Hi, just a few notes about this template, how to use the workbooks created from it, and the methods used for certain operations. There's a short FAQ at the very end of this document. Notes in blue were added by Kathleen Koch January 2005.

Installation------------(These instructions are based on installing the template for use by versions as early as MSExcel97, on a machine running MSWindows95/98/ME/NT/XP.)

The best place to keep this template is in your Templates folder. Your Templates folder should be in the folder in which you installed Microsoft Office or Microsoft Excel. For example: on my machine the Templates folder is 'C:\Program Files\Microsoft Office\Templates'.

Basic usage-----------Once you have saved the template to your Templates folder, here's how to create new workbooks from it:1) Start Excel2) Click 'New' on the 'File' menu, which opens up the 'New' dialog box.3) On the 'General' tab of the 'New' dialog, pick 'soapsheet.xlt'.4) Click the OK button.5) A new workbook called soapsheet1 should appear in Excel.

There are only two worksheets in this workbook that you need to be concerned with when you play with your soap recipes. They are the sheets labeled "Recipe Worksheet" (the one you're on by default when you create a new workbook from the template) and "Scaled Version". The fields that may be changed on the Recipe worksheet are:

- Recipe Name- fields under the "Oil" heading- fields under the "Super-fatting Oil" heading- fields under the "Weight in Oz" headings for oils and super-fatting oils- Lye discount- DWCP % (Experienced Soapmakers only)- Bar Size (Oz)- Oil to Water ratio- Water (if water is overridden, then auto calc will cease for water field)(With the addition of the DWCP % field, this field is disabled)- Notes

Entering a recipe is as easy as picking oils from the drop-down list attached to the Oil fields and entering the amount of each you want to use. Then, if you want, enter super-fatting oils the same way, enter a lye discount, and perhaps change the bar size. That's it. Everything else will be calculated for you.

The Scaled Version sheet allows you to take the recipe entered on the Recipe sheet and scale it to whatever batch size you want. It shows everything from the Recipe sheet except INS values, and includes the additional field "Scale Factor". Scale Factor is the only field that may be changed on the Scaled Version sheet, and is used as follows: to scale a recipe down to one half of its original size, enter a Scale Factor of .5. To double a recipe, enter a Scale Factor of 2. Enter whatever factor you want, 2 and .5 are just examples. You get the idea.

The AutoScaled sheet allows you to enter a mold size and get the recipe scaled to your mold.

All fields titled in dark blue are for enterable values. For easier viewing of these areas, they have been highlighted in yellow.

Fluid Measurements------------------This spreadsheet will automatically compute the number of fluid ounces (or milliliters) from the oil weights entered, assuming the specific gravity is available for that oil.

Be aware, the same care must be taken measuring using liquid measurements.If you normally measure weights to within a tenth of an ounce and want to measure by volume, then the same precision is necessary (to the tenth of a fluid ounce). The gradations of a normal measuring cup would not be sufficiently accurate.

It is important to note that natural materials, such as oils and fats varygreatly, depending on source and refinement methods. As such, the specificgravity of your batch may differ from the table values. This will result in inaccurate amounts even with accurate measuring. Make certain you have at lease 3% superfatting in your recipes, to ensure an inaccuracy doesn't result in lye heavy soap. Measurement System------------------The measurement system (metric vs standard) can be changed by clicking the "convert measurements" button. This can be done at anytime and will convertall values from one system to the other. This allows you to enter metric orstandard (english) recipes then converting to your desired system. Conversionis done via an Excel macro, so if you do not enable macros (some people don'tfor fear of viruses) then conversion will not work. Conversion is the only thing using the macros, so if you don't convert measurements, disabling the macros will have no effect on your use of this spreadsheet.

SAP Values----------The SAP values used in calculating the lye are looked up from the SAP Values worksheet. If you disagree with any of the values, please feel free to change them. You may also change the names of any of the oils, just make sure you change any references on the Recipe worksheet from the old name to the new name. You may also add new oils, as long as you don't change the format of the SAP Values worksheet (this is very important, as the lookup from the Recipe worksheet depends upon the oil names and SAP values being in specific columns, and within a certain range of rows). If you do add new oils, you can either insert them into the list where you want or put them at the end; order is not important, just don't go past row 200 or so. Note too that the oils in the drop down list on the Recipe sheet appear in the same order as on the SAP Values sheet, so if you don't insert new oils in alphabetical order, they won't appear that way in the list either. To save any changes/additions you make to the SAP Values worksheet, so that any new workbooks you create will include them, see "Changing the template" below. Any additions need to include a price, even zero,to prevent unexpected and inaccurate results from costing and change measurementsinteraction.

Costing-------The spreadsheet will attempt to computer the cost of the soap you make by batch and bar. It does this based on the Cost/Oz column in 'SAP Values' Oil table.This is an example of how I would add a value: I bought a gallon of Coconut oilfor $9.90, so I entered '=9.90/128' into the cell E24 (in Coconut oil row of the Oil table). The 9.90 is the price and the 128 is the ounces per gallon. In truth the oil probably would actually weigh about 118 ounces, but this was a quick and dirty estimate of cost. If you do this for a living, then you might want to factor in specific gravity to convert liquid measurements to weights. eg: '=9.9/(128*F24)'. F24, in this case, is the specific gravity of Coconut oil.

For costing, add any non-oil ingredient (except Lye) to the bottom of theOil table. There is no need to put in any values except the name and price.Add specific gravity would help in computing volume measurements (useful with glycerin), but wouldn't be used for most fragrances.

The price for Lye is set up the same way, in the 'Tables' Type_of_Lye table,in the 'E' column. Eg: For a $3.79, 18 Oz bottle of Lye, click cell E103, type '=3.79/18'.

Many of the price values enter were based on prices I looked up on the web. Some maydiffer dramatically for you, depending on the amount and source of ingredients you buy. If you want accuracy, modify the prices based on your purchases.

IMPORTANT: If pricing values are missing for an Oil you use, then that cost willnot be reflected in the reported cost. No warning will appear.

INS Values of Recipes---------------------The concept of INS values is borrowed from Dr Robert McDaniel's book, "Essentially Soap." He in turn borrowed it from an unattributed source. In any event, what he says is that an oil's INS value is based on its SAP value and iodine value, and that a soap's INS is the weighted average of the INS values of its constituent oils. What does that mean to you? Probably not much. The important thing is that (as related by Dr Bob) the 'ideal' INS value to shoot for when formulating a soap recipe is 160. Now remember: 160 is only the ideal. Most recipes, even really excellent ones, won't be at an INS of 160. The majority of the ones I've looked at are in the mid 140's or low 150's. The INS value's real utility is as a sort of rough gauge of how well your recipe is balanced. In other words, if your recipe's INS is much above or way below 160, you might want to take another look at things.

INS Values of Individual Oils-----------------------------Many of the INS values for individual oils are borrowed from Dr Bob. Specifically, the sheet calculates its own INS values based on the best SAP and iodine values I could find, but where Dr Bob had a value, I defer to him. You may note that a few of the oils listed have no INS. That's because Dr Bob didn't list them, and I couldn't find iodine value numbers for them either. You can still use those oils in your recipes, but they will be ignored in the INS calculation for the recipe as a whole.

Canola versus Rapeseed oil--------------------------All canola oil is rapeseed oil, but not all rapeseed oil is canola. The reason for this is that rapeseed oil normally has an erucic acid content of up to fifty percent, while in order for an oil to be called canola, it may have an erucic acid content of no more than two percent. In other words, canola is low erucic acid rapeseed oil. What does this mean to you? Well, the erucic acid of regular rapeseed is largely replaced by oleic and linoleic acids in canola, which CHANGES THE SAP VALUE. I have yet to see a saponification chart, either published or on the web, which takes this into account. Every one of them treats rape and canola interchangeably, and lists the SAP of regular rape, even though the availability of regular rapeseed oil to the average North American consumer is limited at best. Is this dangerous? Since the SAP of rape is lower than that of canola, no, not really. It just means you're building an additional lye discount into any soap that uses canola. Which isn't much of a problem unless your recipe is already at the extreme of superfatting, or you're trying to make transparent soap. Bottom line: use canola for canola, and any of the other names (ramic/rape/rapeseed) for regular rapeseed oil.

Lye calculation and rounding----------------------------Because most people don't have a way to accurately measure amounts smaller than .1 ounces (well, I don't), the calculated amounts for lye and water are rounded DOWN to the nearest .1 ounce. If you must round, rounding down is the only safe way to go for lye, and unless you are making very small batches, a difference of less than one tenth of an ounce should have very little effect. Note that you may still enter amounts of oil to any level of precision you want, you just won't ever get more than one decimal place of precision for the lye and water.

Water calculation-----------------The amount of water required is calculated based on the rule of thumb of one pound of water for every three pounds of oils. There are other rules of thumb out there, e.g. six ounces of water per pound of oils, ala the MMS calculator, but I prefer the 1:3 rule.

To change the Oil to Water ratio enter the number of pounds of oil per pound ofwater in the oil into the water ratio entry field (upper left hand of Recipe worksheet).

The water value can be overridden (typed in manually), the rest of the calculationswill adjust, but this will prevent automatic calculation of water on thissheet. If you always create a new worksheet from the template for each newrecipe, it will only effect the recipe you manually overrode.

DWCP % (Experienced Soapmakers only)-------------------DWCP %: For Discounted Water Cold Process, enter a value from 33% to 50% (the maximum percentage range for disounting water; below 32 results in a "wet" batch and above 50 results in unreacted sodium hydroxide.)

The water calculation of "Recipe Fat/Water ratio:" is disabled when using the DWCP %.

After Cure calculation----------------------The After Cure weight is an estimate of the total yield of soap after most of the water has cured out of your bars. It is based on the rule of thumb that when your soap is done curing it will contain about 4% water, by weight.The actual water percent will be calculated and appear in the After Cure line.

Default values--------------Every new soapsheet workbook will have a default lye discount of 0%, bar size of 4 ounces, Oil-Water ratio of 3, scale factor of 1, and a oil & lye pricing info. Any of these may be changed. To save your changes for future workbooks, see "Changing the template" below.

Changing the template---------------------Here's how to change the template:

1) Create a new soapsheet workbook.2) Make your changes/additions to SAP Values, bar size, lye discount, and/or scale factor.3) From the File menu, click Save As.4) In the 'Save as type' drop-down list box on the 'Save As' dialog, choose 'Template (*.xlt)'. This choice should automatically change the 'Save in' directory to your Templates directory.5) In the 'File name' box of the 'Save As' dialog enter 'soapsheet' (the original name), or whatever name you would like to use for the new version of the template.6) If you are replacing the template you started with in step one, you will be prompted whether or not to overwrite the original. Click 'Yes'.7) You're done.

Hi, just a few notes about this template, how to use the workbooks created from it, and the methods used for certain operations. There's a short FAQ at the very end of this document. Notes in blue were added by Kathleen Koch January 2005.

Installation------------(These instructions are based on installing the template for use by versions as early as MSExcel97, on a machine running MSWindows95/98/ME/NT/XP.)

The best place to keep this template is in your Templates folder. Your Templates folder should be in the folder in which you installed Microsoft Office or Microsoft Excel. For example: on my machine the Templates folder is 'C:\Program Files\Microsoft Office\Templates'.

Basic usage-----------Once you have saved the template to your Templates folder, here's how to create new workbooks from it:1) Start Excel2) Click 'New' on the 'File' menu, which opens up the 'New' dialog box.3) On the 'General' tab of the 'New' dialog, pick 'soapsheet.xlt'.4) Click the OK button.5) A new workbook called soapsheet1 should appear in Excel.

There are only two worksheets in this workbook that you need to be concerned with when you play with your soap recipes. They are the sheets labeled "Recipe Worksheet" (the one you're on by default when you create a new workbook from the template) and "Scaled Version". The fields that may be changed on the Recipe worksheet are:

- Recipe Name- fields under the "Oil" heading- fields under the "Super-fatting Oil" heading- fields under the "Weight in Oz" headings for oils and super-fatting oils- Lye discount- DWCP % (Experienced Soapmakers only)- Bar Size (Oz)- Oil to Water ratio- Water (if water is overridden, then auto calc will cease for water field)(With the addition of the DWCP % field, this field is disabled)- Notes

Entering a recipe is as easy as picking oils from the drop-down list attached to the Oil fields and entering the amount of each you want to use. Then, if you want, enter super-fatting oils the same way, enter a lye discount, and perhaps change the bar size. That's it. Everything else will be calculated for you.

The Scaled Version sheet allows you to take the recipe entered on the Recipe sheet and scale it to whatever batch size you want. It shows everything from the Recipe sheet except INS values, and includes the additional field "Scale Factor". Scale Factor is the only field that may be changed on the Scaled Version sheet, and is used as follows: to scale a recipe down to one half of its original size, enter a Scale Factor of .5. To double a recipe, enter a Scale Factor of 2. Enter whatever factor you want, 2 and .5 are just examples. You get the idea.

The AutoScaled sheet allows you to enter a mold size and get the recipe scaled to your mold.

All fields titled in dark blue are for enterable values. For easier viewing of these areas, they have been highlighted in yellow.

Fluid Measurements------------------This spreadsheet will automatically compute the number of fluid ounces (or milliliters) from the oil weights entered, assuming the specific gravity is available for that oil.

Be aware, the same care must be taken measuring using liquid measurements.If you normally measure weights to within a tenth of an ounce and want to measure by volume, then the same precision is necessary (to the tenth of a fluid ounce). The gradations of a normal measuring cup would not be sufficiently accurate.

It is important to note that natural materials, such as oils and fats varygreatly, depending on source and refinement methods. As such, the specificgravity of your batch may differ from the table values. This will result in inaccurate amounts even with accurate measuring. Make certain you have at lease 3% superfatting in your recipes, to ensure an inaccuracy doesn't result in lye heavy soap. Measurement System------------------The measurement system (metric vs standard) can be changed by clicking the "convert measurements" button. This can be done at anytime and will convertall values from one system to the other. This allows you to enter metric orstandard (english) recipes then converting to your desired system. Conversionis done via an Excel macro, so if you do not enable macros (some people don'tfor fear of viruses) then conversion will not work. Conversion is the only thing using the macros, so if you don't convert measurements, disabling the macros will have no effect on your use of this spreadsheet.

SAP Values----------The SAP values used in calculating the lye are looked up from the SAP Values worksheet. If you disagree with any of the values, please feel free to change them. You may also change the names of any of the oils, just make sure you change any references on the Recipe worksheet from the old name to the new name. You may also add new oils, as long as you don't change the format of the SAP Values worksheet (this is very important, as the lookup from the Recipe worksheet depends upon the oil names and SAP values being in specific columns, and within a certain range of rows). If you do add new oils, you can either insert them into the list where you want or put them at the end; order is not important, just don't go past row 200 or so. Note too that the oils in the drop down list on the Recipe sheet appear in the same order as on the SAP Values sheet, so if you don't insert new oils in alphabetical order, they won't appear that way in the list either. To save any changes/additions you make to the SAP Values worksheet, so that any new workbooks you create will include them, see "Changing the template" below. Any additions need to include a price, even zero,to prevent unexpected and inaccurate results from costing and change measurementsinteraction.

Costing-------The spreadsheet will attempt to computer the cost of the soap you make by batch and bar. It does this based on the Cost/Oz column in 'SAP Values' Oil table.This is an example of how I would add a value: I bought a gallon of Coconut oilfor $9.90, so I entered '=9.90/128' into the cell E24 (in Coconut oil row of the Oil table). The 9.90 is the price and the 128 is the ounces per gallon. In truth the oil probably would actually weigh about 118 ounces, but this was a quick and dirty estimate of cost. If you do this for a living, then you might want to factor in specific gravity to convert liquid measurements to weights. eg: '=9.9/(128*F24)'. F24, in this case, is the specific gravity of Coconut oil.

For costing, add any non-oil ingredient (except Lye) to the bottom of theOil table. There is no need to put in any values except the name and price.Add specific gravity would help in computing volume measurements (useful with glycerin), but wouldn't be used for most fragrances.

The price for Lye is set up the same way, in the 'Tables' Type_of_Lye table,in the 'E' column. Eg: For a $3.79, 18 Oz bottle of Lye, click cell E103, type '=3.79/18'.

Many of the price values enter were based on prices I looked up on the web. Some maydiffer dramatically for you, depending on the amount and source of ingredients you buy. If you want accuracy, modify the prices based on your purchases.

IMPORTANT: If pricing values are missing for an Oil you use, then that cost willnot be reflected in the reported cost. No warning will appear.

INS Values of Recipes---------------------The concept of INS values is borrowed from Dr Robert McDaniel's book, "Essentially Soap." He in turn borrowed it from an unattributed source. In any event, what he says is that an oil's INS value is based on its SAP value and iodine value, and that a soap's INS is the weighted average of the INS values of its constituent oils. What does that mean to you? Probably not much. The important thing is that (as related by Dr Bob) the 'ideal' INS value to shoot for when formulating a soap recipe is 160. Now remember: 160 is only the ideal. Most recipes, even really excellent ones, won't be at an INS of 160. The majority of the ones I've looked at are in the mid 140's or low 150's. The INS value's real utility is as a sort of rough gauge of how well your recipe is balanced. In other words, if your recipe's INS is much above or way below 160, you might want to take another look at things.

INS Values of Individual Oils-----------------------------Many of the INS values for individual oils are borrowed from Dr Bob. Specifically, the sheet calculates its own INS values based on the best SAP and iodine values I could find, but where Dr Bob had a value, I defer to him. You may note that a few of the oils listed have no INS. That's because Dr Bob didn't list them, and I couldn't find iodine value numbers for them either. You can still use those oils in your recipes, but they will be ignored in the INS calculation for the recipe as a whole.

Canola versus Rapeseed oil--------------------------All canola oil is rapeseed oil, but not all rapeseed oil is canola. The reason for this is that rapeseed oil normally has an erucic acid content of up to fifty percent, while in order for an oil to be called canola, it may have an erucic acid content of no more than two percent. In other words, canola is low erucic acid rapeseed oil. What does this mean to you? Well, the erucic acid of regular rapeseed is largely replaced by oleic and linoleic acids in canola, which CHANGES THE SAP VALUE. I have yet to see a saponification chart, either published or on the web, which takes this into account. Every one of them treats rape and canola interchangeably, and lists the SAP of regular rape, even though the availability of regular rapeseed oil to the average North American consumer is limited at best. Is this dangerous? Since the SAP of rape is lower than that of canola, no, not really. It just means you're building an additional lye discount into any soap that uses canola. Which isn't much of a problem unless your recipe is already at the extreme of superfatting, or you're trying to make transparent soap. Bottom line: use canola for canola, and any of the other names (ramic/rape/rapeseed) for regular rapeseed oil.

Lye calculation and rounding----------------------------Because most people don't have a way to accurately measure amounts smaller than .1 ounces (well, I don't), the calculated amounts for lye and water are rounded DOWN to the nearest .1 ounce. If you must round, rounding down is the only safe way to go for lye, and unless you are making very small batches, a difference of less than one tenth of an ounce should have very little effect. Note that you may still enter amounts of oil to any level of precision you want, you just won't ever get more than one decimal place of precision for the lye and water.

Water calculation-----------------The amount of water required is calculated based on the rule of thumb of one pound of water for every three pounds of oils. There are other rules of thumb out there, e.g. six ounces of water per pound of oils, ala the MMS calculator, but I prefer the 1:3 rule.

To change the Oil to Water ratio enter the number of pounds of oil per pound ofwater in the oil into the water ratio entry field (upper left hand of Recipe worksheet).

The water value can be overridden (typed in manually), the rest of the calculationswill adjust, but this will prevent automatic calculation of water on thissheet. If you always create a new worksheet from the template for each newrecipe, it will only effect the recipe you manually overrode.

DWCP % (Experienced Soapmakers only)-------------------DWCP %: For Discounted Water Cold Process, enter a value from 33% to 50% (the maximum percentage range for disounting water; below 32 results in a "wet" batch and above 50 results in unreacted sodium hydroxide.)

The water calculation of "Recipe Fat/Water ratio:" is disabled when using the DWCP %.

After Cure calculation----------------------The After Cure weight is an estimate of the total yield of soap after most of the water has cured out of your bars. It is based on the rule of thumb that when your soap is done curing it will contain about 4% water, by weight.The actual water percent will be calculated and appear in the After Cure line.

Default values--------------Every new soapsheet workbook will have a default lye discount of 0%, bar size of 4 ounces, Oil-Water ratio of 3, scale factor of 1, and a oil & lye pricing info. Any of these may be changed. To save your changes for future workbooks, see "Changing the template" below.

Changing the template---------------------Here's how to change the template:

1) Create a new soapsheet workbook.2) Make your changes/additions to SAP Values, bar size, lye discount, and/or scale factor.3) From the File menu, click Save As.4) In the 'Save as type' drop-down list box on the 'Save As' dialog, choose 'Template (*.xlt)'. This choice should automatically change the 'Save in' directory to your Templates directory.5) In the 'File name' box of the 'Save As' dialog enter 'soapsheet' (the original name), or whatever name you would like to use for the new version of the template.6) If you are replacing the template you started with in step one, you will be prompted whether or not to overwrite the original. Click 'Yes'.7) You're done.

Hi, just a few notes about this template, how to use the workbooks created from it, and the methods used for certain operations. There's a short FAQ at the very end of this document. Notes in blue were added by Kathleen Koch January 2005.

Installation------------(These instructions are based on installing the template for use by versions as early as MSExcel97, on a machine running MSWindows95/98/ME/NT/XP.)

The best place to keep this template is in your Templates folder. Your Templates folder should be in the folder in which you installed Microsoft Office or Microsoft Excel. For example: on my machine the Templates folder is 'C:\Program Files\Microsoft Office\Templates'.

Basic usage-----------Once you have saved the template to your Templates folder, here's how to create new workbooks from it:1) Start Excel2) Click 'New' on the 'File' menu, which opens up the 'New' dialog box.3) On the 'General' tab of the 'New' dialog, pick 'soapsheet.xlt'.4) Click the OK button.5) A new workbook called soapsheet1 should appear in Excel.

There are only two worksheets in this workbook that you need to be concerned with when you play with your soap recipes. They are the sheets labeled "Recipe Worksheet" (the one you're on by default when you create a new workbook from the template) and "Scaled Version". The fields that may be changed on the Recipe worksheet are:

- Recipe Name- fields under the "Oil" heading- fields under the "Super-fatting Oil" heading- fields under the "Weight in Oz" headings for oils and super-fatting oils- Lye discount- DWCP % (Experienced Soapmakers only)- Bar Size (Oz)- Oil to Water ratio- Water (if water is overridden, then auto calc will cease for water field)(With the addition of the DWCP % field, this field is disabled)- Notes

Entering a recipe is as easy as picking oils from the drop-down list attached to the Oil fields and entering the amount of each you want to use. Then, if you want, enter super-fatting oils the same way, enter a lye discount, and perhaps change the bar size. That's it. Everything else will be calculated for you.

The Scaled Version sheet allows you to take the recipe entered on the Recipe sheet and scale it to whatever batch size you want. It shows everything from the Recipe sheet except INS values, and includes the additional field "Scale Factor". Scale Factor is the only field that may be changed on the Scaled Version sheet, and is used as follows: to scale a recipe down to one half of its original size, enter a Scale Factor of .5. To double a recipe, enter a Scale Factor of 2. Enter whatever factor you want, 2 and .5 are just examples. You get the idea.

The AutoScaled sheet allows you to enter a mold size and get the recipe scaled to your mold.

All fields titled in dark blue are for enterable values. For easier viewing of these areas, they have been highlighted in yellow.

Fluid Measurements------------------This spreadsheet will automatically compute the number of fluid ounces (or milliliters) from the oil weights entered, assuming the specific gravity is available for that oil.

Be aware, the same care must be taken measuring using liquid measurements.If you normally measure weights to within a tenth of an ounce and want to measure by volume, then the same precision is necessary (to the tenth of a fluid ounce). The gradations of a normal measuring cup would not be sufficiently accurate.

It is important to note that natural materials, such as oils and fats varygreatly, depending on source and refinement methods. As such, the specificgravity of your batch may differ from the table values. This will result in inaccurate amounts even with accurate measuring. Make certain you have at lease 3% superfatting in your recipes, to ensure an inaccuracy doesn't result in lye heavy soap. Measurement System------------------The measurement system (metric vs standard) can be changed by clicking the "convert measurements" button. This can be done at anytime and will convertall values from one system to the other. This allows you to enter metric orstandard (english) recipes then converting to your desired system. Conversionis done via an Excel macro, so if you do not enable macros (some people don'tfor fear of viruses) then conversion will not work. Conversion is the only thing using the macros, so if you don't convert measurements, disabling the macros will have no effect on your use of this spreadsheet.

SAP Values----------The SAP values used in calculating the lye are looked up from the SAP Values worksheet. If you disagree with any of the values, please feel free to change them. You may also change the names of any of the oils, just make sure you change any references on the Recipe worksheet from the old name to the new name. You may also add new oils, as long as you don't change the format of the SAP Values worksheet (this is very important, as the lookup from the Recipe worksheet depends upon the oil names and SAP values being in specific columns, and within a certain range of rows). If you do add new oils, you can either insert them into the list where you want or put them at the end; order is not important, just don't go past row 200 or so. Note too that the oils in the drop down list on the Recipe sheet appear in the same order as on the SAP Values sheet, so if you don't insert new oils in alphabetical order, they won't appear that way in the list either. To save any changes/additions you make to the SAP Values worksheet, so that any new workbooks you create will include them, see "Changing the template" below. Any additions need to include a price, even zero,to prevent unexpected and inaccurate results from costing and change measurementsinteraction.

Costing-------The spreadsheet will attempt to computer the cost of the soap you make by batch and bar. It does this based on the Cost/Oz column in 'SAP Values' Oil table.This is an example of how I would add a value: I bought a gallon of Coconut oilfor $9.90, so I entered '=9.90/128' into the cell E24 (in Coconut oil row of the Oil table). The 9.90 is the price and the 128 is the ounces per gallon. In truth the oil probably would actually weigh about 118 ounces, but this was a quick and dirty estimate of cost. If you do this for a living, then you might want to factor in specific gravity to convert liquid measurements to weights. eg: '=9.9/(128*F24)'. F24, in this case, is the specific gravity of Coconut oil.

For costing, add any non-oil ingredient (except Lye) to the bottom of theOil table. There is no need to put in any values except the name and price.Add specific gravity would help in computing volume measurements (useful with glycerin), but wouldn't be used for most fragrances.

The price for Lye is set up the same way, in the 'Tables' Type_of_Lye table,in the 'E' column. Eg: For a $3.79, 18 Oz bottle of Lye, click cell E103, type '=3.79/18'.

Many of the price values enter were based on prices I looked up on the web. Some maydiffer dramatically for you, depending on the amount and source of ingredients you buy. If you want accuracy, modify the prices based on your purchases.

IMPORTANT: If pricing values are missing for an Oil you use, then that cost willnot be reflected in the reported cost. No warning will appear.

INS Values of Recipes---------------------The concept of INS values is borrowed from Dr Robert McDaniel's book, "Essentially Soap." He in turn borrowed it from an unattributed source. In any event, what he says is that an oil's INS value is based on its SAP value and iodine value, and that a soap's INS is the weighted average of the INS values of its constituent oils. What does that mean to you? Probably not much. The important thing is that (as related by Dr Bob) the 'ideal' INS value to shoot for when formulating a soap recipe is 160. Now remember: 160 is only the ideal. Most recipes, even really excellent ones, won't be at an INS of 160. The majority of the ones I've looked at are in the mid 140's or low 150's. The INS value's real utility is as a sort of rough gauge of how well your recipe is balanced. In other words, if your recipe's INS is much above or way below 160, you might want to take another look at things.

INS Values of Individual Oils-----------------------------Many of the INS values for individual oils are borrowed from Dr Bob. Specifically, the sheet calculates its own INS values based on the best SAP and iodine values I could find, but where Dr Bob had a value, I defer to him. You may note that a few of the oils listed have no INS. That's because Dr Bob didn't list them, and I couldn't find iodine value numbers for them either. You can still use those oils in your recipes, but they will be ignored in the INS calculation for the recipe as a whole.

Canola versus Rapeseed oil--------------------------All canola oil is rapeseed oil, but not all rapeseed oil is canola. The reason for this is that rapeseed oil normally has an erucic acid content of up to fifty percent, while in order for an oil to be called canola, it may have an erucic acid content of no more than two percent. In other words, canola is low erucic acid rapeseed oil. What does this mean to you? Well, the erucic acid of regular rapeseed is largely replaced by oleic and linoleic acids in canola, which CHANGES THE SAP VALUE. I have yet to see a saponification chart, either published or on the web, which takes this into account. Every one of them treats rape and canola interchangeably, and lists the SAP of regular rape, even though the availability of regular rapeseed oil to the average North American consumer is limited at best. Is this dangerous? Since the SAP of rape is lower than that of canola, no, not really. It just means you're building an additional lye discount into any soap that uses canola. Which isn't much of a problem unless your recipe is already at the extreme of superfatting, or you're trying to make transparent soap. Bottom line: use canola for canola, and any of the other names (ramic/rape/rapeseed) for regular rapeseed oil.

Lye calculation and rounding----------------------------Because most people don't have a way to accurately measure amounts smaller than .1 ounces (well, I don't), the calculated amounts for lye and water are rounded DOWN to the nearest .1 ounce. If you must round, rounding down is the only safe way to go for lye, and unless you are making very small batches, a difference of less than one tenth of an ounce should have very little effect. Note that you may still enter amounts of oil to any level of precision you want, you just won't ever get more than one decimal place of precision for the lye and water.

Water calculation-----------------The amount of water required is calculated based on the rule of thumb of one pound of water for every three pounds of oils. There are other rules of thumb out there, e.g. six ounces of water per pound of oils, ala the MMS calculator, but I prefer the 1:3 rule.

To change the Oil to Water ratio enter the number of pounds of oil per pound ofwater in the oil into the water ratio entry field (upper left hand of Recipe worksheet).

The water value can be overridden (typed in manually), the rest of the calculationswill adjust, but this will prevent automatic calculation of water on thissheet. If you always create a new worksheet from the template for each newrecipe, it will only effect the recipe you manually overrode.

DWCP % (Experienced Soapmakers only)-------------------DWCP %: For Discounted Water Cold Process, enter a value from 33% to 50% (the maximum percentage range for disounting water; below 32 results in a "wet" batch and above 50 results in unreacted sodium hydroxide.)

The water calculation of "Recipe Fat/Water ratio:" is disabled when using the DWCP %.

After Cure calculation----------------------The After Cure weight is an estimate of the total yield of soap after most of the water has cured out of your bars. It is based on the rule of thumb that when your soap is done curing it will contain about 4% water, by weight.The actual water percent will be calculated and appear in the After Cure line.

Default values--------------Every new soapsheet workbook will have a default lye discount of 0%, bar size of 4 ounces, Oil-Water ratio of 3, scale factor of 1, and a oil & lye pricing info. Any of these may be changed. To save your changes for future workbooks, see "Changing the template" below.

Changing the template---------------------Here's how to change the template:

1) Create a new soapsheet workbook.2) Make your changes/additions to SAP Values, bar size, lye discount, and/or scale factor.3) From the File menu, click Save As.4) In the 'Save as type' drop-down list box on the 'Save As' dialog, choose 'Template (*.xlt)'. This choice should automatically change the 'Save in' directory to your Templates directory.5) In the 'File name' box of the 'Save As' dialog enter 'soapsheet' (the original name), or whatever name you would like to use for the new version of the template.6) If you are replacing the template you started with in step one, you will be prompted whether or not to overwrite the original. Click 'Yes'.7) You're done.

FAQ's-----

1) What the heck is a '.xlt' file?

The .xlt, versus .xls, indicates that it is an Excel spreadsheet _template_, not actually a spreadsheet. Every time you open it, it will create a new workbook in Excel that you will be asked to name when you save (i.e. it's hitting the `New' button to create a new workbook, but instead of being blank, it's got all of this stuff from the template in it already). Normally templates are kept in the Templates directory under the directory where Microsoft Office was installed. For me, under WindowsNT 4.0, that defaults to c:\program files\microsoft office\templates. Once the template is installed in the templates directory, picking File>New off of Excel's menu will pop up a dialog for choosing a template or wizard for the new workbook. Just pick 'soapsheet.xlt' from the General tab, and you are good to go.

2) Why is the total effective lye discount greater than my lye discount, even though I specified no superfatting oils?

3) Why is the total effective lye discount different for the scaled version of my recipe?

The answer to these two questions has to do with the fact that lye amounts are rounded down to the nearest tenth of an ounce, as detailed in "Lye calculation and rounding" above. To answer the first question, by way of example: if you specify a lye discount of 5%, which puts the lye amount at, for example, 7.39 ounces, the number 7.39 will be rounded down to 7.3, giving you a total effective lye discount of almost 6.2%. That's a 1.2% higher discount, all because you lost .09 ounces of lye to rounding. Are you with me so far? Now on to question two... The reason why the total effective lye discount may be a different value on the scaled version worksheet than the recipe worksheet is that the amount being rounded off of the lye amount will almost certainly change, and it will be a different proportion to the lye amount than in the unscaled version. For example: say I have a recipe with a 5% lye discount, which puts the lye amount at 8.66 ounces, which rounds down to 8.6, giving me a total effective lye discount of around 5.7%. Then I go to the scaled version worksheet and double the batch size. Now my 5% discount puts the lye amount at 17.32 ounces, which rounds down to 17.3, giving me a total effective lye discount of only about 5.2%. This effect can work the opposite way too, with the total effective lye discount going up when you change the batch size. It is even more pronounced when you scale down to smaller batch sizes, because even a small amount rounded off is a proportionately larger change than in a large batch. I hope that makes sense to everyone. There's no way around this without taking a smaller lye discount than what you originally entered. Using metric measures will drop the roundoff difference, making the entered and effective lye discounts closer.

Using Metric measurements will show a smaller than expected difference in effective lye discount. It is assumed that by measuring in grams higher accuracy will be attained (a gram is equal to 1/20 of an ounce).

Auto Scaling

An auto scaled worksheet is provided so you can just enter a recipe you like then go to the auto scaled sheet and enter the dimensions of the mold (andthe mold type you want to use). The correctly scaled recipe will be produced to the size / type of mold you enter. It will also produce a scaling factor,for those interested. When using DWCP%, the computed weight of the oils will increase as the water is decreased to ensure the mold is filled to the specified dimensions.

If you have a specific mold you always use, then putting it's dimensions intothe worksheet template, then saving in the template directory should save time when scaling the recipe.

The volume to weight conversion factors are located on the 'Tables' worksheetas "Volume Conversion Factor" and correspond to a 4/3 ratio of total weight to oil weight times the avg oils specific gravity, then inverted. This will multiply times the volume (in Fl Oz or milliters) to yeild the weight of oil (in Oz or gms). The ratio of the computed weight to the weight on the 'Recipe Worksheet' is displayed and used to compute the displayed ingredientsamounts.

Source of Information---------------------

Information, such as missing Iodine values and Specific Gravities comes from the 38'th and 50'th Revisions of The Chemical Rubber Company Handbook of Chemistry and Physics. The CRC handbooks has been considered the most authoritative reference source for Chemistry or Physics to be found in a single book.

FAQ's-----

1) What the heck is a '.xlt' file?

The .xlt, versus .xls, indicates that it is an Excel spreadsheet _template_, not actually a spreadsheet. Every time you open it, it will create a new workbook in Excel that you will be asked to name when you save (i.e. it's hitting the `New' button to create a new workbook, but instead of being blank, it's got all of this stuff from the template in it already). Normally templates are kept in the Templates directory under the directory where Microsoft Office was installed. For me, under WindowsNT 4.0, that defaults to c:\program files\microsoft office\templates. Once the template is installed in the templates directory, picking File>New off of Excel's menu will pop up a dialog for choosing a template or wizard for the new workbook. Just pick 'soapsheet.xlt' from the General tab, and you are good to go.

2) Why is the total effective lye discount greater than my lye discount, even though I specified no superfatting oils?

3) Why is the total effective lye discount different for the scaled version of my recipe?

The answer to these two questions has to do with the fact that lye amounts are rounded down to the nearest tenth of an ounce, as detailed in "Lye calculation and rounding" above. To answer the first question, by way of example: if you specify a lye discount of 5%, which puts the lye amount at, for example, 7.39 ounces, the number 7.39 will be rounded down to 7.3, giving you a total effective lye discount of almost 6.2%. That's a 1.2% higher discount, all because you lost .09 ounces of lye to rounding. Are you with me so far? Now on to question two... The reason why the total effective lye discount may be a different value on the scaled version worksheet than the recipe worksheet is that the amount being rounded off of the lye amount will almost certainly change, and it will be a different proportion to the lye amount than in the unscaled version. For example: say I have a recipe with a 5% lye discount, which puts the lye amount at 8.66 ounces, which rounds down to 8.6, giving me a total effective lye discount of around 5.7%. Then I go to the scaled version worksheet and double the batch size. Now my 5% discount puts the lye amount at 17.32 ounces, which rounds down to 17.3, giving me a total effective lye discount of only about 5.2%. This effect can work the opposite way too, with the total effective lye discount going up when you change the batch size. It is even more pronounced when you scale down to smaller batch sizes, because even a small amount rounded off is a proportionately larger change than in a large batch. I hope that makes sense to everyone. There's no way around this without taking a smaller lye discount than what you originally entered. Using metric measures will drop the roundoff difference, making the entered and effective lye discounts closer.

Using Metric measurements will show a smaller than expected difference in effective lye discount. It is assumed that by measuring in grams higher accuracy will be attained (a gram is equal to 1/20 of an ounce).

Auto Scaling

An auto scaled worksheet is provided so you can just enter a recipe you like then go to the auto scaled sheet and enter the dimensions of the mold (andthe mold type you want to use). The correctly scaled recipe will be produced to the size / type of mold you enter. It will also produce a scaling factor,for those interested. When using DWCP%, the computed weight of the oils will increase as the water is decreased to ensure the mold is filled to the specified dimensions.

If you have a specific mold you always use, then putting it's dimensions intothe worksheet template, then saving in the template directory should save time when scaling the recipe.

The volume to weight conversion factors are located on the 'Tables' worksheetas "Volume Conversion Factor" and correspond to a 4/3 ratio of total weight to oil weight times the avg oils specific gravity, then inverted. This will multiply times the volume (in Fl Oz or milliters) to yeild the weight of oil (in Oz or gms). The ratio of the computed weight to the weight on the 'Recipe Worksheet' is displayed and used to compute the displayed ingredientsamounts.

FAQ's-----

1) What the heck is a '.xlt' file?

The .xlt, versus .xls, indicates that it is an Excel spreadsheet _template_, not actually a spreadsheet. Every time you open it, it will create a new workbook in Excel that you will be asked to name when you save (i.e. it's hitting the `New' button to create a new workbook, but instead of being blank, it's got all of this stuff from the template in it already). Normally templates are kept in the Templates directory under the directory where Microsoft Office was installed. For me, under WindowsNT 4.0, that defaults to c:\program files\microsoft office\templates. Once the template is installed in the templates directory, picking File>New off of Excel's menu will pop up a dialog for choosing a template or wizard for the new workbook. Just pick 'soapsheet.xlt' from the General tab, and you are good to go.

2) Why is the total effective lye discount greater than my lye discount, even though I specified no superfatting oils?

3) Why is the total effective lye discount different for the scaled version of my recipe?

The answer to these two questions has to do with the fact that lye amounts are rounded down to the nearest tenth of an ounce, as detailed in "Lye calculation and rounding" above. To answer the first question, by way of example: if you specify a lye discount of 5%, which puts the lye amount at, for example, 7.39 ounces, the number 7.39 will be rounded down to 7.3, giving you a total effective lye discount of almost 6.2%. That's a 1.2% higher discount, all because you lost .09 ounces of lye to rounding. Are you with me so far? Now on to question two... The reason why the total effective lye discount may be a different value on the scaled version worksheet than the recipe worksheet is that the amount being rounded off of the lye amount will almost certainly change, and it will be a different proportion to the lye amount than in the unscaled version. For example: say I have a recipe with a 5% lye discount, which puts the lye amount at 8.66 ounces, which rounds down to 8.6, giving me a total effective lye discount of around 5.7%. Then I go to the scaled version worksheet and double the batch size. Now my 5% discount puts the lye amount at 17.32 ounces, which rounds down to 17.3, giving me a total effective lye discount of only about 5.2%. This effect can work the opposite way too, with the total effective lye discount going up when you change the batch size. It is even more pronounced when you scale down to smaller batch sizes, because even a small amount rounded off is a proportionately larger change than in a large batch. I hope that makes sense to everyone. There's no way around this without taking a smaller lye discount than what you originally entered. Using metric measures will drop the roundoff difference, making the entered and effective lye discounts closer.

Using Metric measurements will show a smaller than expected difference in effective lye discount. It is assumed that by measuring in grams higher accuracy will be attained (a gram is equal to 1/20 of an ounce).

Auto Scaling

An auto scaled worksheet is provided so you can just enter a recipe you like then go to the auto scaled sheet and enter the dimensions of the mold (andthe mold type you want to use). The correctly scaled recipe will be produced to the size / type of mold you enter. It will also produce a scaling factor,for those interested. When using DWCP%, the computed weight of the oils will increase as the water is decreased to ensure the mold is filled to the specified dimensions.

If you have a specific mold you always use, then putting it's dimensions intothe worksheet template, then saving in the template directory should save time when scaling the recipe.

The volume to weight conversion factors are located on the 'Tables' worksheetas "Volume Conversion Factor" and correspond to a 4/3 ratio of total weight to oil weight times the avg oils specific gravity, then inverted. This will multiply times the volume (in Fl Oz or milliters) to yeild the weight of oil (in Oz or gms). The ratio of the computed weight to the weight on the 'Recipe Worksheet' is displayed and used to compute the displayed ingredientsamounts.

Soapsheet.xlt

A soap recipe spreadsheet template, written for Excel97.

Author: Chris Mathes (navig8rs@hotmail.com)Version: 1.3

I wrote soapsheet.xlt because doing lye calculations by hand is cumbersome, time consuming, error prone, and not conducive to tweaking or otherwise playing with recipes. Additionally, I was dissatisfied with the inconvenience and lack of utility of the available on-line lye calculators, so I decided to put

together my own tool that was portable, easy to use, and did what I wanted. I sincerely hope you like it.

I welcome any feedback you have at the email address above. If you would like to be notified when a new version of soapsheet.xlt is released, or when the new stand-alone application version is done (many, many new features), send me an email and I will put you on the list. I should also mention that the new,

as yet unnamed application will also be free, and freely redistributable.

Notice:

This template is an original work of Chris Mathes, but may be freely redistributed under the following conditions:

1) This notice must remain attached and unchanged. 2) You may not charge people for soapsheet.xlt, other than to recoup no more than your costs for physical distribution (e.g. media and/or postage).

3) Blah, blah, blah, blah. Now go make some soap!

Happy Soaping,

Chris

Soapsheet.xlt

A soap recipe spreadsheet template, written for Excel97.

Author: Chris Mathes (navig8rs@hotmail.com)Version: 1.3

I wrote soapsheet.xlt because doing lye calculations by hand is cumbersome, time consuming, error prone, and not conducive to tweaking or otherwise playing with recipes. Additionally, I was dissatisfied with the inconvenience and lack of utility of the available on-line lye calculators, so I decided to put

together my own tool that was portable, easy to use, and did what I wanted. I sincerely hope you like it.

I welcome any feedback you have at the email address above. If you would like to be notified when a new version of soapsheet.xlt is released, or when the new stand-alone application version is done (many, many new features), send me an email and I will put you on the list. I should also mention that the new,

as yet unnamed application will also be free, and freely redistributable.

Notice:

This template is an original work of Chris Mathes, but may be freely redistributed under the following conditions:

1) This notice must remain attached and unchanged. 2) You may not charge people for soapsheet.xlt, other than to recoup no more than your costs for physical distribution (e.g. media and/or postage).

3) Blah, blah, blah, blah. Now go make some soap!

Happy Soaping,

Chris