Dr Michaell Mullan spreadsheet designer

 

During the development of the Online Dairy Science and Food Technology ice cream mix calculator packs of spreadsheets were developed to validate the calculations. These 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 the 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.

Test out an On Line fully working spreadsheet for producing high protein ice cream mixes by going to https://www.dairyscience.info/protein/protein.htm . Freezing point depression spreadsheets can be downloaded from https://www.dairyscience.info/ice-cream/448-fpd-spreadsheets.html .

 

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 £10 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.


Pay with PayPal, PayPal Credit or any major credit card

   

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. I have also changed the emulsifier heading to egg yolk but this can be changed back to emulsifier if required. 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 £20.00 to obtain the spreadsheet in Pack 2.

 


Pay with PayPal, PayPal Credit or any major credit card

   


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 £30 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.

 

 


Pay with PayPal, PayPal Credit or any major credit card


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 £55.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.


Pay with PayPal, PayPal Credit or any major credit card

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 the University of Guelph in his free Ice Cream Ebook 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.


Pay with PayPal, PayPal Credit or any major credit card

 

 

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 is not provided.

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.

 

Pay with PayPal, PayPal Credit or any major credit card

 

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.

 

 


Pay with PayPal, PayPal Credit or any major credit card

 

 

 

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

Are you interested in studying all the spreadsheets in packs 1 to 8 at a large discount? 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. However unlocked spreadsheets providing an algebraic solution can be provide if requested.

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

 


Pay with PayPal, PayPal Credit or any major credit card
   

 Page was updated on the 28th October 2023.