FST 401 Introduction to Food Processing

FST 401 Introduction to Food Processing

Homework: Spreadsheet Practice

The purpose of this assignment is to familiarize you with Excel spreadsheets and the USDA nutrient database. You will set up a spreadsheet to convert a standard “kitchen” recipe to an “industry” recipe which has all ingredients listed by weight. You will also scale-up the recipe to a pilot plant scale and provide scaled-up weight of each ingredient in grams and pounds.

Procedure:

1. Select a recipe with at least 8 ingredients. Indicate the source of the recipe on your spreadsheet.

2. Label the tab at the bottom of your worksheet with the product name. (Right click on the tab and select “rename”.)

3. List the ingredients on you spreadsheet.

4. List the quantity and unit of each ingredient listed in your recipe. Use decimals to express fractions (example ½ cup = 0.5 cup). Put the quantity and the unit in separate columns to facilitate calculation.

5. Convert all “kitchen” units (cups, teaspoons, etc.) to gram weight using the web site below. Show the conversion factor in a column of your spreadsheet. Use Excel to multiply your quantity by your conversion factor.

http://www.nal.usda.gov/fnic/foodcomp/

· -Under “Search the nutrient database” select “Search”. Type in a key word to find a specific ingredient. After selecting the ingredient, the database will show the conversion factors from standard kitchen measurements to gram weights. (Examples: 1 cup of granulated sugar = 200 g; 1 cup of heavy cream = 238 g).

· At the same web page, you will also find conversions from metric to English units under the “Information” section, select “Measurement conversion tables”.

6. Calculate the total weight of the recipe using the Excel “SUM” function.

7. Calculate the weight percentage of each ingredient.

a. You can format the cells in the column to percentage by selecting the cells in the column. Under the “Format” menu, select “Cells”. Select the “Number” tab. Under “Category:”, selection “percentage”. Click OK.

b. Use Excel to calculate the percentage in the first row, then use the “fill handle” to complete the rest. You will need to fix the cell for the denominator by adding a “$” before the row and column in the equation.

8. Calculate the number of grams of each ingredient needed for a 10 lb (4536 g) batch. Use Excel to calculate the weight in the first row by multiplying the percentage by 4536 g. Then use the “fill handle” to complete the rest of the rows.

9. Calculate the number of pounds of each ingredient needed for a 10 lb (4536 g) batch. You can do this one of two ways:

a. Use Excel to calculate the weight in the first row by multiplying the percentage by 10 lbs. Then use the “fill handle” to complete the rest of the rows.

b. Or, use Excel to calculate the weight of the first row by multiplying the gram weight (step 7) by 0.0022 (1 g = 0.0022 lbs). Then use the “fill handle” to complete the rest of the rows.

10. Calculate the total for each column by using the “fill handle” on the cell from step 5 to fill in the remaining cells. Confirm that the total percentage = 100%, the total gram weight is 4536 g, and the total pound weight is 10 lbs.

11. Save the Excel file as “401 HW Your Last Name”. E-mail the file to courtney.25@osu.edu as an attachment. Use “401 HW Your Last Name” as the subject for your e-mail. You will receive a confirmation e-mail within 12 hours. If you do not receive a confirmation, contact the instructors by phone, e-mail or in person.

Required elements of spreadsheet:

Your name

· Name of the product

· Tab at the bottom of the worksheet should be labeled

· Source of the recipe

· List of ingredients (must have at least 8)

· Conversion factors used to calculate gram weight of ingredients.

· Gram weight of all ingredients in the recipe.

· Weight percentage of all ingredients in the recipe.

· Scaled up recipe to a 10 lb (4536 g, 4.536 kg) batch with weights in grams and pounds.

· Totals for the columns containing the gram weight for the ingredients in the recipe, weight percentage, grams for 10 lb batch, and pounds per 10 pound batch.

· Calculations must be done using the functions of Excel (i.e., you may not use a calculator and just type in the numbers.)

Help with Excel:

1. To add the total of a column or row:

Click in the cell in which the sum will go.
Click the “fx” or “=” symbol at the top.
In the “Insert Function” window, select “SUM” and “OK”.
In the “Function Arguments” either type the range (example: B12:F12”) or use the mouse to select the range on the spreadsheet.
Click OK

Click in the cell in which the sum will go.
Type “=SUM(B12:F12)”
Push “Enter” on the keyboard.

2. To repeat a calculation in neighboring cells – no fixed cells:

Click on the cell that has the original calculation.
Put the cursor over the square at the bottom right corner of the cell (the “fill handle”) until the cursor becomes a “+” symbol.
Hold the left mouse button down and drag through the cells in which you want to repeat the calculation.

3. To repeat a calculation in a neighboring cell –one or more fixed cells:

Click on the cell that has the original calculation.
In the formula, type a “$” in front of the row and column designations of the fixed cell. For example, if you want to multiply all of the cells by the number in cell H7, you would change H7 to “$H$7” in the cell with the original calculation.
Put the cursor over the square at the bottom right corner of the cell (the “fill handle”) until the cursor becomes a “+” symbol.
Hold the left mouse button down and drag through the cells in which you want to repeat the calculation.

4. Multiply, add, divide or subtract:

Click on cell where the result will go.
Press “=” on you keyboard.
Select the cell to be used in the calculation
Press the “*, /, +, or –“ symbol on the keyboard
Select another cell or type a number.
When finished, press enter.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Product Example spread sheets