Technology
Microsoft Excel spreadsheets for ice cream recipes
During 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 £9.99 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. 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 £9.99 to obtain the spreadsheet in Pack 2.
| |
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 £19.99 to obtain the spreadsheets in Pack 3.
Figure 2. Graphic illustrating typical spreadsheet in 'Pack 3' designed to undertake mix calculations using two 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 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.
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; 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).
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 £49.99 to obtain these customised spreadsheets.
| |
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.
These spreadsheets are similar in functionality to those in pack 5 but some additional features have been added e.g. mix costs can 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 £39.99 to obtain these customised spreadsheets.
| |
Pack 7. Complete set of all the calculators contained in packs 1 to 6
Are you interested in studying all the spreadsheets in packs 1 to 6 and saving more than £100? 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 £49.99 to obtain the complete set of ice cream mix spreadsheets.
| |
