Lab Week 5 – Pricing Analysis

Background

With the food cost for each of your recipes determined, it’s now time to figure out how much we would charge for our cakes, cupcakes, and cookies. Why? How you price your baked goods will determine how much profit (margin) you feel is acceptable for each sale in order to pay your bills, overhead, and salary with some money left over to invest.

Goal

In this assignment, you are going to combine your work from weeks 3 and 4, and edit the work to accommodate new information. Doing this will save us time as the recipes and their raw food costs are already in place, and so is the formatting. The newly formed document will be the starting point for the rest of the assignment.

Using the work you completed in the past two weeks, you will edit the recipe worksheets to add additional columns and calculations to determine the total cost of producing a baked good. The total cost will include the overhead (week three) and ingredient costs (week four). Also, you will calculate the percent of the cost of each ingredient in each recipe. Lastly, you will create Formulas that will calculate the price per individual and total serving of each baked good based on an ideal food cost percentage. *The calculation of menu pricing is based on one method that you could use to determine how much you could charge for your baked goods. This exercise, in no way, constitutes a full examination of menu pricing

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
• Relative and Absolute cell referencing
• Worksheet management
  • Move or copy worksheets
  • Renaming worksheets
  • Grouping worksheets

The completed spreadsheet should look like this:

Creating a Combined Workbook

1. Open the Food Costs Analysis Workbook – Week 5 Lab, and your Overhead Costs Workbook – Week 5 Lab (both found linked in Module 5). These are the starting files for this assignment.

2. Move a Copy of the Overhead Cost Analysis worksheet to the Food Cost Analysis Workbook, placing it between the Food Cost and Measures worksheet and the Vanilla Cake worksheet.
3. Close the Overhead Costs Workbook (do not save changes)
4. Save your combined workbook as “Food Cost & Menu Pricing Analysis.xlsx”

Quick Check!

Editing the Recipe Worksheets

1. Continue working on your file, Food Cost & Menu Pricing Analysis.xlsx

Remember?

Group the Vanilla Cake, Vanilla Cupcake, and Chocolate Chip Cookies worksheets and perform the following formatting edits:

2. Change the Page Layout to Landscape
3. Merge and Center cells A1:H1
4. Merge and Center cells A2:H2 and edit the text to read: Food Cost & Menu Pricing Analysis
5. Edit the Column Width of columns G:H to 13
6. In cells F4 and F18 enter the following text: Overhead Cost and format the text so that Cost appears on the line below Overhead

7. In cells G4 and G18 enter the following text: Total Cost and format the text so that Cost appears on the line below Total
8. In cells H4 and H18 enter the following text: Percent of Cost and format the text so that Of Cost appears on the line below Percent
9. Format the newly created cells to the format found in adjacent cells in rows 4 and 18
10. In cell C16 edit the text to read: Calculated Ingredient Subtotals
11. In cell C21 edit the text to read: Uncalculated Ingredient Subtotals
12. Highlight cell E16 and use the Format Painter to format cells F16:H16 and F21:H21
13. Change the Row Height of row 3 to 40
14. In cell A3 Unmerge the cells and then Merge and Center cells A3:B3 then Left-Align the text
15. Move cells E3:F3 to cells C3:D3
16. In cell E3 enter the following text: Individual Price. Format the text so that “Price” appears on the line below Individual
17. In cell G3 enter the following text: Total Price. Format the text so that “Price” appears on the line below Total
18. Format the text style for E3 and G3 to match cell C3
19. In cell B23 edit the text to read: Total Food Cost
20. In cell C23 delete the Formula
21. In cell D23 edit the text to read: Ideal Food Cost %
22. In cell F23, delete the Formula and change the formatting to a Percentage (%) and enter 22.5 then format the cell to show a percentage with one (1) decimal place
23. Save your workbook before continuing

24. In cell F5, create a Formula using an Absolute Cell Reference that calculates the Overhead Cost of each Calculated Ingredient:

Overhead cost=individual ingredient cost × Est OH % (from Overhead Costs worksheet)

The absolute cell reference should be used for the estimated overhead percentage cell reference.

25. Copy the Formula to cells F6:F15 (make sure to maintain the Total Cell Style formatting)
26. Format the results in Accounting format
27. In cell F19 create a Formula using an Absolute Cell Reference that calculates the Overhead Cost of each Uncalculated Ingredient based on the Estimated Overhead Percentage found on the Overhead Costs worksheet
28. Copy the Formula to cell F20 (make sure to maintain the Total Cell Style formatting)
29. Format the results in Accounting format

30. In cell G5 create a Formula that calculates the Total Cost of each Calculated Ingredient:

31. Copy the Formula to cells G6:G15 (make sure to maintain the Total Cell Style formatting)
32. Format the results in Accounting format
33. In cell G19 create a Formula that calculates the Total Cost of each Calculated Ingredient
34. Copy the Formula to cell G20 (make sure to maintain the Total Cell Style formatting)
35. Format the results in Accounting format

36. Calculate the subtotals for the Overhead Cost and Total Cost columns for the Calculated and Uncalculated Ingredients sections
37. In cell C23 create a Formula to calculate the total cost of the recipe

38. In cell H5, create a Formula using Absolute Cell References to calculate the Percent of Total Cost for each Calculated Ingredient

[latex]Percent\,of\,Cost(for\,Each\,Individual\,Ingredient)=\frac{Total\,Ingredient\,Cost}{Total\,Food\,Cost}[/latex]

39. Format the results as a Percentage with no decimals
40. Center-Align the results
41. Copy the Formula to cells H6:H15 (make sure to maintain the Total Cell Style formatting)
42. In cell H19 create a Formula using Absolute Cell References to calculate the Percent of Total Cost for each Uncalculated Ingredient
43. Format the results as a Percentage with no decimals
44. Center-Align the results
45. Copy the Formula to cell H20 (make sure to maintain the Total Cell Style formatting)

46. In cells H16 and H21 use a Function to calculate the total percentage for each category

47. Center-Align and Bold the results

48. In cell H3 create a Formula that calculates the Total Price of the baked good. This formula should calculate the amount of the ideal food cost percentage to the total food cost to account for the expected profit level:
    • [latex]=\frac{Total\,Food\,Cost}{Ideal\,Food\,Cost}[/latex]

49. Format the results to Accounting, Bold, Georgia Pro 16 point, and Middle-Aligned
50. In cell F3 create a Formula that calculates the Individual Price of the baked good
51. Format the results to Accounting, Bold, Georgia Pro 16 point, and Middle-Aligned
52. Save your workbook and close Microsoft Excel

Your work should look like the example below when you are done with this assignment: