During the development of the software for calculating ice cream mix formulations I constructed a range of spreadsheets using the Serum Point method to validate the ice cream mix calculator which is on this site. Following requests for these, I have decided to provide these in return for a donation to support the development of this website.

The spreadsheets are available in four packs. Learn more about the conditions governing use of the spreadsheets prior to making a secure donation.

Pack 1. Single source of MSNF calculators

Pack 1 contains three Microsoft Excel spreadsheets:

1. Calculator for determining ingredients for ice cream mixes using whole milk, butter, sugar, emulsifier and stabil iser.

2. Calculator for determining ingredients for ice cream mixes using butter, skim milk powder, sugar, emulsifier, stabiliser and water.

3. Calculator for determining ingredients for ice cream mixes using anhydrous milk fat, skim milk powder, sugar, emulsifier, stabiliser and water.

With the exception of the first spreadsheet that uses the Pearson Square or Rectangle to calculate quantities of whole milk and butter required to obtain a target, or desired, fat concentration the calculations are straightforward and could easily be done on a basic calculator. However the spreadsheets do allow 'what if' type scenarios to be explored and show how spreadsheets can be configured for ice cream mix calculations. All the cells are unlocked and the 'unhide' command can be used to view the formulas.

A graphic illustrating a typical spreadsheet is shown in figure 1. Read the Frequently asked questions (FAQ) about the Microsoft Excel Ice Cream Mix spreadsheets supplied by the Dairy Science and Food Technology website before making a donation to obtain the spreadsheets in Pack 1.

Figure1. Graphic illustrating typical spreadsheet in 'Pack 1' designed to undertake mix calculations using a single ingredient supplying MSNF

Pack 2. Three source MSNF calculators

Pack 2 contains four Microsoft Excel spreadsheets:

1. Calculator for determining ingredients for ice cream mixes using cream, skim milk powder, sugar, water, emulsifier and stabiliser.

2. Calculator for determining ingredients for ice cream mixes using whole milk, butter, skim milk powder, sugar, emulsifier and stabiliser.

3. Calculator for determining ingredients for ice cream mixes using skim milk, butter, skim milk powder, sugar, emulsifier and stabiliser.

4. Calculator for determining ingredients for ice cream mixes using whole milk, cream, skim milk powder, sugar, emulsifier and stabiliser.

Donors can use the “unhide” command to access hidden cells and explore the approach to mix calculations. I have included summary information to allow users to follow the calculations and provide a link to more detailed information.

A graphic illustrating a typical spreadsheet from "pack 2" is shown in figure 2. Read the Frequently asked questions (FAQ) about the Microsoft Excel Ice Cream Mix spreadsheets supplied by the Dairy Science and Food Technology website before making a donation to obtain the spreadsheets in Pack 2.

width="660" height="470" />

Figure 2. Graphic illustrating typical spreadsheet in 'Pack 2' designed to undertake mix calculations using two ingredients supplying MSNF.

Pack 3. Three source MSNF calculators

Pack 3 contains four Microsoft Excel spreadsheets:

1. Calculator for determining ingredients for ice cream mixes using whole milk, cream, skim milk powder, sugar, emulsifier and stabiliser.

2. Calculator for determining ingredients for ice cream mixes using skim milk, cream, skim milk powder, sugar, emulsifier and stabiliser

3. Calculator for determining ingredients for ice cream mixes using sweetened condensed skim milk, whole milk, sugar, emulsifier and stabiliser.

4. Calculator for determining ingredients for ice cream mixes using whole milk, sweetened condensed whole milk, cream, sugar, emulsifier and stabiliser.

A graphic illustrating a typical spreadsheet from "pack 3" is shown in figure 3. Read the Frequently asked questions (FAQ) about the Microsoft Excel Ice Cream Mix spreadsheets supplied by the Dairy Science and Food Technology website before making a donation to obtain the spreadsheets in Pack 3.

Figure 3. Graphic illustrating typical spreadsheet in 'pack 3' designed to undertake mix calculations using three ingredients supplying MSNF.

Donors can use the “unhide” command to access hidden cells and explore the approach to mix calculations. I have included summary information to allow users to follow the calculations and provide a link to more detailed information.

Spreadsheets 3 and 4 have been included to demonstrate the more complicated ice cream mix calculations e.g. calculating the weights of ingredients, contributing three sources of fat and three sources of MSNF, required to produce an ice cream mix to a target composition. This type of calculation where concentrated milk is used instead of milk powder is generally more prevalent in North America than in the UK and Ireland and students sometimes have difficulties with these calculations. While the article on this site does provide a conceptual approach to undertaking these calculations the step by step guide provided by Professor Goff at http://www.foodsci.uoguelph.ca/dairyedu/iccalc.html provides a particularly clear explanation of how to undertake these calculations. I have adopted his approach in these two spreadsheets. Useful information on how to solve these mix problems is also given in the text book by Marshall and Arbuckle (2000). This is referenced in the article on ice cream mix calculations on this site.

Pack 4. Customised spreadsheets for product development / small batch mix manufacture

Pack 4 contains a customised spreadsheet in two versions, metric and imperial, set up using  whole milk, cream and skim milk powder, very common ingredients in ice cream mixes, adapted for flexibility, and batch processing. One version has been configured to work with batches in kilograms, the other in pounds and ounces. A low resolution screenshot of one of the variants is given in figure 4.1.  For a higher resolution image of the other variant click here.

Figure 4.1. Low resolution screenshot of customised ice cream spreadsheet

Each spreadsheet has been designed to allow users flexibility in ingredient addition, two additional 'blank' ingredients can be added without having to change the spreadsheet formulas (figure 4.2).

The concentration of some ingredients can be set by the user e.g. CSS is normally used at a concentration of 80% solids; users can adjust to higher or lower % solids.

Figure 4.2. Control of ingredient composition in customised spreadsheets

A table indicating the optimal value for MSNF has been added and control limits set at + and -3%; users can easily adjust these limits. Depending on the concentration of MSNF and whether or not it is optimal, two messages are generated. Message 1 will indicate whether the mix is optimal for MSNF and, if not, to note the second message.  Message 2 will indicate whether the mix is within ± 3% of the optimal value and advises that judgment is required or in excess of this limit where the user is advised to reconsider the mix formulation proposed (figure 4.3). The error message functionality  has been added using the logic functions of Microsoft Excel.

Figure 4.3. Optimal value for MSNF is calculated automatically and user is advised of mix problems.

Each spreadsheet has a table for specifying the batch weight. This is useful particularly in product development and when learning how to make ice cream. Users can set the batch size to reflect their scale of operation.  Read the Frequently asked questions (FAQ) about the Microsoft Excel Ice Cream Mix spreadsheets supplied by the Dairy Science and Food Technology website before making a donation to obtain these customised spreadsheets.