Lab Week 4 – Food Cost Analysis

Background

After concluding last week our estimated sales, overhead costs, and our estimated overhead percentage, we now need to price out how much it will cost us to produce our cakes, cupcakes, and cookies. This information will be vital in understanding how much we are going to spend in creating each of our recipes and further help with the decision to join the Longmeadow Baking Company.

Goal

In this assignment, you are going to evaluate a list of vendors and ingredients to get the best price for your cakes, cupcakes, and cookies. You will enter this information into a table to determine the price per ounce and unit.

Then you will enter the ingredient prices into your three recipes (provided) and calculate how much it costs to create each baked good and the cost of an individual serving. This information will be used to determine how much you should charge for your baked goods.

You will demonstrate the following Excel skills with this exercise:

• Applying formatting (column width, row height, fill, font, alignment, number, styles)
• Format painter
• Basic formula creation
• Copy/paste formula
• Basic functions (COUNT)
• Excel Tables
• Filtering
• Relative and Absolute cell referencing
• Worksheet management
  • Renaming worksheets
  • Grouping worksheets

The completed spreadsheet will:

Evaluating vendors and pricing

1. Download (from your Canvas module) and open the starting workbook “Food Cost Analysis Workbook.xlsx”
2. Save your workbook.

3. On the Vendor Data Set worksheet, select all the data in columns A:C
4. Convert the selected data into a Table, using Orange, Table Style Medium 3, with Headers included (Styles Group on the Home Tab)
5. Rename the Vendor Data Set worksheet to “Vendor and Ingredient Costs”
6. You need to find the minimum price for each product type.  One way to do this is to Filter the data by Product Type (one product type at a time) and then filter the data by Unit Price, smallest to largest (see example below: be sure to filter by Product Type first, then filter the data by Unit Price).

For this case, the minimum Unit Price for “Baking Powder” is in cell C21.

7. On the Food Cost & Measures worksheet:
   1. Create a Relative Cell Reference to the corresponding ingredient’s minimum unit Cost cell (Column C).
   2. Let’s create the first one together for the Calculated Ingredient, Flour.
      1. On the “Vendor and Ingredient Cost” sheet, filter the Product Type (column A) to Flour only.  Then filter Unit Price (column C) smallest to largest.
      2. On the “Food Costs and Measures” worksheet, activate cell C4
      3. Enter an = sign in cell C4
      4. Select the Vendor and Ingredient Costs worksheet tab
      5. Click on the cell that contains the lowest cost for Flour to select it
      6. Then hit Enter
      7. Our relative cell reference has been added

8. REPEAT step 7 above for each of the remaining Ingredients.  Filter the table on the “Vendor and Ingredient Costs” sheet first, then create the formula on the “Food Cost and Measures” sheet.

9. You determine the cost for packaging is $367.26 per 1000 units. Enter this information in the correct location.
10. In cell E4 enter a Formula that calculates the Cost per Ounce by Dividing “Cost” by “Lbs to Ounces calculation” and Copy the Formula to cells E5:E14 (make sure to keep cell border formatting intact)

11. In cell E16, enter a Formula that calculates the Cost per Unit and Copy the Formula to cell E17 (make sure to keep cell border formatting intact).  Your formula is different from the formulas you made in step 10 because the items in the “Uncalculated Ingredients” category are based on unit qty unit and not by ounce.  Therefore, use the “Lbs or Quantity” (in column B) instead of “Lbs to Ounces Calculation” (in column D)

12. In cell A19 enter the following text: Count of Calculated Ingredients
13. In cell C19 use the COUNT Function to calculate the number of individual/unique Calculated Ingredients that you must purchase for your recipes
14. In cell A20 enter the following text: Count of Uncalculated Ingredients
15. In cell C20 use the COUNT Function to calculate the number of individual/unique Uncalculated Ingredients that you must purchase for your recipes

16. You are done with the first half of this week’s assignment.   Save your workbook before moving on to the second half of this week’s assignment.

Calculating the Cost of Baked Goods

1. Continue using your “Food Cost Workbook.xlsx” file.
2. Save the workbook.

3. On the Recipes worksheet, select the second recipe (cells A21:F38) and Cut and Paste it to a New worksheet in cell A1 and Rename the worksheet to: “Vanilla Cupcakes”
4. On the Recipes worksheet, select the third recipe (cells A41:F58) and Cut and Paste it to a New worksheet in cell A1 and Rename the worksheet to: Chocolate Chip Cookies
5. Rename the Recipes worksheet to: Vanilla Cake

Quick Check…

6. Insert two (2) rows above the Uncalculated Ingredients (A16).  Repeat this for the other two recipe sheets (“Vanilla cupcakes” and “Chocolate Chip Cookies”).
7. Navigate back to the “Vanilla Cake” sheet.
8. Formulas are needed to calculate the individual ingredient costs specific to the recipe.  Create the first formula to calculate the cost of Flour for Vanilla Cake.  You have to multiply two values to calculate how much flour costs for the vanilla cake (the amount of flour needed x the cost of flour.) The number of ounces of flour required is in cell D5 of the “Vanilla Cake” recipe sheet and the “Cost per Ounce” for flour is on the “Food Cost and Measures” sheet in cell E4.
   1. Create a formula in cell E5 on “Vanilla Cake” sheet that multiplies “Ounce Conversion” in cell D5 times the flour “Cost per Ounce” which is on the “Food Cost and Measures” sheet.

Double-Check Your Formula!

9. Now that you’ve verified the formula is correct, copy the formula to cells E6:E15 (the other Calculated ingredients.)
10. The next formula to create is for the Uncalculated ingredients (Eggs and Packaging).  Because these items are based on quantity needed and not based on ounces, the formula needs to multiply the ingredient quantity x the “Food Cost and Measures” value for the item. In cell E19 create a Formula that calculates the ingredient cost using relative cell references (the same process as you did for the Calculated items, but use quantity X Cost per item price on the Food Cost and Measures worksheet.
    1. Copy the Formula to cell E20

The remaining formulas to create are subtotal costs for the Calculated Ingredients, subtotals for the Uncalculated Ingredients, Total cost of the recipe, and the Single serving Cost:

11. In cell E16 use a Function to calculate the subtotal of the Calculated ingredients.
12. In cell E21 use a Function to calculate the subtotal of the Uncalculated ingredients.

13. In cell F23, create a Formula to determine the Total Serving Cost (based on calculated and uncalculated ingredient costs)
14. In cell C23 create a Formula to calculate the Single Serving Cost (based on the Total Serving Cost and the number of servings the recipe makes (value is in row 3):

[latex]\frac{Total Serving Cost}{Serving For Recipe}[/latex]

Cells E16, E21, C23, and F23 need to have descriptive labels.

15. Merge and Center cells B16:D16, enter the following text: Calculated Ingredient Subtotal
    1. Right-Align and Bold the text
16. Merge and Center cells B21:D21, then enter the following text: Uncalculated Ingredient Subtotal
17. Merge and Center cells A23:B23 and enter the following text: Single Serving Cost
    1. Change the font to Georgia Pro 16 Point and Right-Align the text
18. Merge and Center cells D23:E23 and enter the following text: Total Serving Cost
    1. Change the font to Georgia Pro 16 Point and Right-Align the text

19. Move cell B3 to E3
20. Move cell C3 to F3
21. Merge and Center A3:D3, then Left Align.

Double-Check Your Formulas…

Using Formats for a Professional Appearance:

You probably can tell that your spreadsheet doesn’t look professional.  You’ve adjusted some of the formats already, but now need to make the sheet look the best that it can.  You already have a lot of practice applying formatting, so…

22. Use your preferred method to update the “Vanilla Cake” sheet with the following format changes:

• Change Row Heights:
  • Row height of rows 1 and 2 = 40
  • Row height of rows 3 and 4 = 30
  • Row height of row 18 = 30
• Change column widths:
  • Column width of column A = 18
  • Column width of columns B:F = 13
• Merge and Center:
  • A1:F1
  • A2:F2
• cell A1 = Georgia Pro, 26, Bold, Italic “White” font
  • Fill color same as previously used (#FF3399)
• cell A2 = Georgia Pro 26 point font, Bold
• Cells A3:F3 = Georgia Pro 16 point, and Middle-Align the text in the cell
• cells A4:F4
  • Wrap Text
  • Bold text
  • Add a Bottom Border
  • Center-Align the text
• cells A18:F18 = same formats as row 4
  • Wrap Text
  • Bold text
  • Add a Bottom Border
  • Center-Align the text
• cells B5:D15 and cells B19:D20
  • Center-Align the data in cells B5:D15 and cells B19:D20
• cells A16:F16 and A21:F21
  • Add the Total Cell Style to cells A16:F16 and A21:F21
• All costs should be Accounting format (individual ingredient costs, subtotal costs, total serving cost, and single serving cost)

Now that you’ve perfected the “Vanilla Cake” worksheet, you can update the other two recipe worksheets by copying the formulas and formats:

23. First, move cells B3:C3 to E3:F3 on each of the remaining recipe worksheets (“Vanilla cupcake” and “Chocolate Chip Cookie”) …also verify that you added the extra rows from step 6 above.
24. Navigate back to “Vanilla Cake” sheet, and copy row 16, and Paste to each recipe worksheets (“Vanilla cupcake” and “Chocolate Chip Cookie”)
25. Navigate back to “Vanilla Cake” sheet and Repeat for row 21 (copy/paste) and row 23 (copy/paste).  Don’t worry about the calculations being correct at this point.  We’ll fix that next.

There are still some formatting changes to update on each recipe sheet.  Of course you can repeat all of the formatting updates that you made in step 22.  But did you know you can use the format painter feature to apply the formatting from the “Vanilla Cake” sheet to the “Vanilla Cupcake” and “Chocolate Chip Cookie” sheet?

26. Navigate to “Vanilla Cake” worksheet, and select all of the cells (A1:F23), then select the format painter icon on the Home ribbon.
27. Select the “Vanilla Cupcake” worksheet tab, and then cell A1 and hit ENTER.