Dairy Science and Food Technology

Scientific, information & consultancy services for the food industry

Copyright Protected

Content copyright protected

Science Services

DSFT has been providing science based consultancy services globally since 2002.
  
Click to learn more.

Dr Michaell Mullan spreadsheet designerDuring the development of the Dairy Science and Food Technology ice cream mix calculator for calculating ice cream mix recipes seven packs of spreadsheets were developed and can be downloaded in return for a donation to support the continued development and maintenance of this website. Learn more about the conditions governing use of the spreadsheets prior to making a secure donation.

Because the spreadsheets have been configured to work with particular ingredients e.g. cream and skim milk powder; cream and whole milk powder they (without modification which you are free to do) will only work with the stated ingredients. So please download the correct spreadsheet pack. If in doubt send me an Email or read the additional user instructions.

There are no restrictions on users modifying or customising spreadsheets and some packs contain customised spreadsheets for the most commonly used ingredients e.g. cream, whole milk and skim milk powder. One of the simpler spreadsheets (Pack 2) has been converted to a web page to illustrate how this can be done. Batch sizes can be calculated and "costed", data inputs can easily be validated and alerts can be added e.g. if the MSNF is not optimal or within certain limits or if the fat: sugar ratio exceeds set limits.

To reduce costs some manufactures are using vegetable fat / milk fat blends. One of spreadsheets in Pack 6 enables the quantities of milk fat and vegetable fat required to produce a mix to a desired dairy and vegetable fat ice cream recipe to be calculated. Alerts can also be added if the % vegetable fat exceed a set limit e.g. 40% w/w of the total fat or if the milk fat falls below 60% of the total fat.

 

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 stabiliser.

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.

All the cells can be accessed 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 of £19.99 to obtain the spreadsheets in Pack 1.

Graphic of spreadsheet for calculating ice cream mixes

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


Visa MasterCard Discover American Express

   

Pack 2. Customised butter, skim milk powder calculator for use in product development / small batch trials

Pack 2 contains one Microsoft Excel spreadsheet which has been customised to illustrate how a simple spreadsheet can be modified to enable batch mixes to be formulated and costed:

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

This is a customised version of the spreadsheet shown in figure 1.It has been modified to enable different batch sizes and the cost of mixes to be calculated and can be evaluated On Line. A table indicating the optimal value for MSNF has been added and control limits set; users can easily adjust these limits. Donors can use the “unhide” command to access hidden cells and explore the approach to mix calculations. I have included summary information in the spreadsheet to allow users to follow the calculations and provide a link to more detailed information. 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 of £25.00 to obtain the spreadsheet in Pack 2.

 


Visa MasterCard Discover American Express

   


Pack 3. Two source MSNF calculators

Pack 3 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 3 " 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 of £50 to obtain the spreadsheets in Pack 3.

Microsoft Excel spreadsheet for calculating ice cream mixes

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

 

Visa MasterCard Discover American Express


Pack 4. Three source MSNF calculators

Pack 4 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 4" 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 of £70.00 to obtain the spreadsheets in Pack 4.

Microsoft Excel graphic of an Excel spredsheet for calculating ingredients for ice cream mixes

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


Visa MasterCard Discover American Express

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 5. Customised spreadsheets for product development / small batch mix manufacture using whole milk, cream and skim milk powder as the major ingredients

Whole milk, cream and skim milk powder are amongst the most widely used major ingredients in ice cream mixes and the spreadsheets in pack 5 have been carefully customised to enable major mix calculations including mix costings to be undertaken. Because some manufacturers are now using dairy and vegetable fat blends, a spreadsheet enabling these calculations has also been included.

Pack 5 contains a customised spreadsheet in four versions or options.

Version 1 has been configured to work with batches in kilograms and version 2 enables imperial batch calculations, pounds and ounces to be calculated. These versions do not enable mix costings to be calculated, that facility has been added to versions 3 and 4. A low resolution screenshot of option 1 is given in figure 4.1 ; a higher resolution image of option 2 is available here.

Low resolution image of ice cream mix spreadsheet

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.

Control of ingredient composition in customised spreadsheets

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; 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).

Optimal value for MSNF and error messages

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

Option 1 and 2 spreadsheets as for all the spreadsheets in this pack have 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.

Because cost is of critical importance in ice cream mix development an ingredient cost table has been added to spreadsheet variants 3 and 4. To use this facility users simply replace the values in the spreadsheet with their own values and your local currency to enable batch costs to be calculated. A locked (inactive) web version of option 3 can be viewed On Line.

Manufacturers may need to produce their own dairy / vegetable fat blends and this facility has now been added to the option 4 spreadsheet in this pack. A locked (inactive) web version of option 4 can be viewed On Line. Note this spreadsheet can enable the production of 100 % dairy fat mixes by setting the vegetable fat ingredient input to zero. Because the spreadsheets in pack 5 have been designed to work using whole milk and cream they cannot be used to produce mixes containing no dairy fat!

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 of £80.00 to obtain these customised spreadsheets.

 

Visa MasterCard Discover American Express

Pack 6. Customised spreadsheets for product development / small batch mix manufacture using whole milk powder and cream as the major ingredients

Pack 6 contains two Microsoft Excel spreadsheets:

1. Calculator for determining ingredients for ice cream mixes using cream, whole milk powder, sugar, emulsifier stabiliser and two additional ingredients
2. Calculator for determining ingredients for ice cream mixes using cream, whole milk powder, vegetable fat, sugar, emulsifier, stabiliser and one additional ingredient.


Whole milk or cream powders are not used much in ice cream and gelato manufacture. Nevertheless it is a rich source of both fat and MSNF and is a useful ingredient. Manufacturers sometimes find calculations involving whole milk or cream powders difficult and use of these spreadsheets should avoid these calculation problems. These spreadsheets will also enable mix costs to be calculated. An additional small programme, called a macro (code is password protected), has been used to ensure that the correct MSNF balance is obtained. The macro has potentially wide application across a range of ingredients and enables quite complex mix calculations to be undertaken.

Interest in calculations for determining particular milk fat / vegetable fat blends is increasing and this facility has been added in spreadsheet 2 of this pack. Note! Because this spreadsheets in this pack been designed to work using whole milk powder and cream it cannot be used to produce mixes containing no dairy fat!

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. Note the code for the macro is password protected and can be obtained for an additional small donation to support the site.

Screenshots of both spreadsheets, spreadsheet 1 and spreadsheet 2 can be viewed On Line. 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 of £90.00 to obtain these customised spreadsheets.

Visa MasterCard Discover American Express

 

Pack 7. Customised artisanal gelato / ice cream spreadsheets offering sweetness and hardness control options for small batch mix manufacture using whole milk, cream or butter, skim milk powder, sucrose, dextrose, fructose, a range of glucose powders or syrups, and flavourings


These spread sheets offer a high level of mix control and should be particularly useful to artisanal ice cream gelato makers who wish to produce a range of mixes for products of defined sweetness and hardness. A screenshot of the main calculator is shown in figure 7.1 and a working spread sheet but with limited functionality can be explored here. Note these screen shots are for the spreadsheet configured for cream. 

Artisanal gelato

Figure 7.1a. Screen shot of spreadsheet using cream as concentrated dairy fat source. Note the Set Values worksheet tab.

 

The spreadsheet for butter is similar (figure 7.1b) except that cream has been replaced by butter. 

 

 

   

Figure 7.1b. Screen shot of spreadsheet using butter as concentrated dairy fat source.

 

Inputs

Drop down list• A drop down list enables glucose solids powders or syrups ranging from DE25 to DE62 to be used.
• Dextrose and fructose/invert sugar in addition to sucrose can be added.
• The fat in the milk and in the cream can be varied by the user.
• Cocoa / chocolate, inulin and flavour ingredients can be added
• The concentration of solids in sweeteners, inulin and flavouring can be set in the ‘Values’ workbook. Default values are set at 100%.
• The relative sweetness (RS) and freezing point depression factor (FPDF) of sweeteners, inulin and one flavouring can be varied in the Values workbook. Default values use generally accepted values.

Control of sweetness and hardness

• POD,
• RS,
• PAC
• FPDF

of mixes are automatically calculated.

Fault control and suitability of mixes for producing ice cream suitable for hard freezing and storage

The maximum MSNF for the mix is calculated and some advice on the suitability of the mix for producing ice cream that can be hard frozen and stored for many months is given.

Advice on the hardness of ice cream or gelato produced using a particular mix at -12°C and -18°C

Depending on the PAC values of the mix users will receive advice on the hardness of ice cream or gelato served after holding at -12°C and -18°C

Set batch size

Users can also select batch size. Two options one in grams and the other in kilograms have been provided.

Modify the default sweetness, relative freezing point depression factor and total solids of sweeteners

A values worksheet has been provided (Figure 7.3). This lists the POD, RS, PAC and FPDF values of all the sweeteners, inulin and the flavour used. The % solids of each sweetener, except for lactose, can be varied by the user.

Values work book

Figure 7.3. Values work book

 

The facility to vary sweetness and freezing point depression factors should be particularly helpful to product developers. These values are batch dependant for glucose powder and syrups. The relative sweetness values of sugars is dependent on mix acidity, sweetener concentration and a range of other factors so it is helpful to have a facility to adjust sweetness values for particular flavours of gelato / ice cream.

Caution

Before using the spread sheets users should set the solids values of the sweeteners and the flavour to meet the values of their ingredients. The default setting for all values is 100% solids.

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 of £100.00 to obtain these customised spreadsheets.

 

Visa MasterCard Discover American Express

 

 

New Pack 8.  Excel freezing point depression curve software for ice cream or gelato

New 

 

Ice cream and gelato development technologists use freezing point depression curves of ice cream or gelato mixes to develop recipes that can be scooped at serving temperatures and are resistant to heat shock.

Two spreadsheet packages for calculating freezing point depression curves can be downloaded from this page. The packages are similar except that package 1 is unlocked and also contains an Excel workbook listing sucrose concentrations and the corresponding freezing point depressions (FPDs).

The formulas used to calculate FPD are locked in package 2 and there is no sucrose FPD calibration curve.

Details of the spreadsheet for calculating the freezing point of gelato/ice cream mixes are shown in figure 8.

Excel work book for constructing freezing point curves for ice cream

Figure 8. Spreadsheet for calculating the freezing point of gelato/ice cream mixes.

Users can investigate the effects of sucrose, high fructose corn syrup, monosaccharides (dextrose, fructose), maltodextrins, glucose syrups, high maltose corn syrups, MSNF, whey solids and the initial moisture concentration of mixes on FPD.

Ideally whey solids should be entered separately from MSNF. If so, their contribution to MSNF should not be included in the figure for MSNF.

Options have been provided to investigate the use of maltodextrins ranging from DE5 to DE18 (figure 8.1).

 Selection options for maltodextrin

Figure 8.1. Options for maltodextrin inclusion.

Glucose solids ranging in DE from 10 to 62 can also be used (figure 8.2).

 Selection options for DE glucose syrupss

Figure 8.2.  Options for using glucose syrups

 A facility to investigate the use of high maltose corn syrup of DE42 and DE50 has also been added (figure 8.3).

 High maltose corn syrup selection options 
Figure 8.3. Options for high maltose corn syrup inclusion.

 The spreadsheet also provides users with two warning messages if the sucrose equivalent concentration in the frozen mix at a particular temperature exceeds 180 g per 100 g water. Pickering (1891) only investigated the effects of sucrose concentrations up to 180 g of sugar per 100 g of water so caution is required if users attempt to work with higher sucrose equivalent concentrations. Additionally cells giving the high values are coloured yellow or red depending on the magnitude of the calibration discrepancy.

 Download Excel freezing point curve spreadsheet packages for ice cream or gelato

Package 1

Two unlocked Excel spreadsheets, cost £100, one for calculating the freezing point depression curve for an ice cream or gelato mix and the other for calculating the regression equation relating sucrose concentration to freezing point depression can be downloaded. The regression equation uses sucrose equivalent values of mix components to determine FPD. Note the freezing point depression curve is not required for the calculator to work, the regression equation from the curve is already integrated into the calculator and can be accessed in the unlocked spreadsheet cells.

Note this spreadsheet package is now available as a free download to users who purchased the complete set of ice cream mix spreadsheets (pack 8). Users who have purchased pack 8 prior to the 30th August can obtain this spreadsheet package by donating £15 at the donation page. The package will be sent to you normally within 24 hours.

Please 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 of £100.00 to obtain these spreadsheets. 

 


Visa MasterCard Discover American Express

Package 2

One locked Excel spreadsheet, cost £30, for calculating the freezing point depression curve for an ice cream or gelato mix.

Note this spreadsheet is now available (from the 30th August 2015) as a free download to users signing up to any of the subscriptions for  the Dairy Science and Food Technology ice cream mix calculator. Current subscribers can download the package using the Resource tab on the Calculator. Users who have taken out a subscription prior to 30th August, and which is not currently active, can obtain this spreadsheet package by donating £5 at the donation page. The package will be sent to you normally within 24 hours.

Please 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 of £30.00 to obtain these spreadsheets. This spreadsheet is similar to the package above except that the formulae used to perform the calculations cannot be accessed and the calibration curve used to relate sucrose concentration to FPD has not been provided. The spreadsheet uses the regression equation to do the calculation but users cannot access the equation because the cells are locked.

  


Visa MasterCard Discover American Express

 

 

Pack 9. Complete set of all the calculators contained in packs 1 to 8

Are you interested in studying all the spreadsheets in packs 1 to 8 and saving more than £200? Then donate to obtain the full set of ice cream mix spreadsheets. These should enable most of the calculations encountered in ice cream mix formulations to be explored. Note the code for the macro used in the two spreadsheets in Pack 6 is not included.

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 of £300.00 to obtain the complete set of ice cream mix spreadsheets.

 


Visa MasterCard Discover American Express

   

We use cookies to improve our website and your experience when using it. To find out more about the cookies we use, see our Privacy and Cookie Policy.

'Learn more about managing cookies'