ExcelInsider Team
Controlling food costs is crucial for running a profitable restaurant or food service business. When deciding food pricing based on menus, weekly performances, and especially the inventories, the food cost percentage can provide you with insight for smarter decisions. This tells you more about the actual food cost, clarifies the profit margin, and spots food wastage. For this reason, people waste tons of money on food cost-calculating software. However, the fact that they miss out is that Excel is one of the best, simplest, and free tools that you can use to track food cost percentage over time.
If you want to know how to use the food cost percentage formula in Excel, follow the steps below-
➤ In your Excel, create two columns for total food cost and total sales.
➤ Select a third cell to record the value of the food cost percentage and give an appropriate header (e.g, Food Cost %).
➤ Use the formula below in the cell,
= B2/C2
where B2 is the cell containing the total cost and C2 is the total sales.
➤ Select the output cell and press Ctrl + 1 or right-click and choose Format Cells to format the value as a percentage.
➤ In the Format Cell window, go to Numbers and choose Category to Percentage.
➤ Click OK to view the result in percentages.
➤ You can use the Fill Handle option or drag the cells to apply the same formula to the other cells.
So, here you will walk through the various methods to perform food cost calculations. We will start with the basic calculations using division rules to dive deeper into table creation and charts. In the meantime, automated methods like VBA will also be explored in detail. With this, you can really learn how to interpret your food cost and boost your margins.
Simple Division Formula to Calculate Food Percentage in Excel
If you are working with small datasets and just starting out your food business, you don’t need to look for hefty methods and complex formulas. One-time methods are enough for faster calculations. The easiest way for you is to use the basic division rule you learn in your Maths class.
Steps:
➤ Create two columns to store the total cost and the total sales. Give it appropriate headers like Food Cost and Sales.
➤ Create another column to store the result percentages. Name it with headers that match your wish (e.g., Food Cost %).
➤ In the cell of the new column, enter the following formula –
=B2/C2
where B2 is the cell of total cost and C2 is for the total cells.
➤ The output of this function comes in decimals; to change it into a percentage, we need to format cells.
➤ Select the cell and press the shortcut Ctrl + 1 to open the Format Cells window. To open this, you can also right-click and choose Format Cells from the menu.
➤ In the window, go to Numbers and select Category to Percentage.
➤ You can change the decimal places as you want or keep it to 2 decimal places..
➤ Click OK to format the cells in percentage.
➤ Drag the cells or use the Fill Handle option to copy the formula and formatting to the other cells.
Note:
➨ Make sure your total sales value is not zero. Dividing by zero value will give #DIV/0! Error.
➨ You can also get the rounded values instead of the decimal values by changing the decimal values from the Format Cells window. Or, you can also use this formula –
=ROUND(B2/C2)
Creating a Structured Table for Tracking Food Cost Percentage
Creating a structured table for regular basic food cost tracking is more efficient and smarter. Tables give you extra flexibility while dynamically adding formulas, stylings, and support filters. This method can be the best approach for restaurants and caterers.
Steps:
➤ Ensure your table has a total food cost and the total sales column.
➤ Create another new column to track the food cost percentage.
➤ Select the table and click on the Insert -> Table option.
➤ The Create Table dialog box appears; review that the table range is correct and check the box My table has headers.
➤ This will change your dataset into a table, usually named Table 1 (which you can change using Table Tools).
➤ In the first cell of the column, use the formula below –
=[@Food Cost (USD)]/@[Sales (USD)]
where Food Cost(USD) is the name of the column of total food cost and Sales (USD) is the column of total sales.
➤The formula is automatically applied to all the cells of the column.
➤ However, the values are in decimal numeric values, which need to be converted to percentages.
➤ Select the column and press Ctrl + 1 to open the Format Cells window. Go to Numbers and select the Category to Percentage.
➤ Set decimal values as per requirement; the default value is 2.
➤ Click OK, and this will apply the formatting to all the cells.
Note:
The table will automatically add the values to the new column when they are added.
Automating Food Cost Percentage with Excel VBA
When handling dozens of rows and large datasets, a single automation can increase productivity. Of course, VBA Macros are the one for that. With VBA, you can create customized formulas to review each column and calculate the food percentage. No need for manual formula input or chart creation.
Steps:
➤ Make a table with the columns total food cost, total sales, and an empty food cost percentage column.
➤ Go to Developers tab -> Visual Basic.
➤ This will launch a VBA editor. In the window, click on the Insert tab and select the Module.
➤ In the blank space, paste the following VBA script for food cost percentage.
Sub CalculateFoodCostPercentage()
Dim lastRow As Long
Dim ws As Worksheet
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row 'Assumes Food Cost is in Column B
Dim i As Long
For i = 2 To lastRow
If ws.Cells(i, 3).Value <> 0 Then 'Assumes Sales is in Column C
ws.Cells(i, 4).Value = ws.Cells(i, 2).Value / ws.Cells(i, 3).Value 'Output in Column D
ws.Cells(i, 4).NumberFormat = "0.00%"
Else
ws.Cells(i, 4).Value = "N/A"
End If
Next i
End Sub 
➤Save the file as a macro-enabled file and close the VBA window.
➤ In the Developer tab, go to the Macros.
➤ You will find the name of the custom VBA formula there, which is CalculateFoodCostPercentage according to the above VBA code snippet.
➤ Select that and click on Run.
➤This will automatically fill the column Food Cost % with the percentage values with proper formatting.
Notes:
The VBA code assumes,
Food Cost -> column B
Sales -> column C
Food Cost % -> column D
For different structures, you can change the columns from these lines (lines 5, 8, and 9)
lastRow = ws.Cells(ws.Rows.Count, “B”).End(xlUp).Row ‘Assumes Food Cost is in Column B
If ws.Cells(i, 3).Value <> 0 Then ‘Assumes Sales is in Column C
ws.Cells(i, 4).Value = ws.Cells(i, 2).Value / ws.Cells(i, 3).Value ‘Output in Column D
Calculating Food Cost Percentage per Menu Item
Not all food service businesses relied on this calculation. Calculating food percentage based on per menu time instead of weekly ones seems more feasible and interpretable. It helps you to see how profitable the items are and how to adjust margin, pricing, and menu offerings based on this. Just a simple modification can do the work.
Steps:
➤ Create a table with columns for individual ingredient costs and sales of that ingredient.
➤ Have an extra column for storing the food cost percentage.
➤ Use the same division formula –
=B2/C2
where B2 is the ingredient cost and C2 is the total sales of that ingredient.
➤ Select the output cells and press Ctrl + 1 to open the Format Cells window.
➤ Select the Percentage Category from the Numbers tab in the Format Cells window.
➤ Adjust the decimal values as per your requirement.
➤ Click OK to format the cell as a percentage.
➤ Drag the cells or use Fill Handle to use the formula in the rest of the cells.
Using Excel Charts to Visualize Food Cost Percentage over Time
Excel is not only the simplest tool for calculating food cost percentages but also the best for visualizing their profit and trends in charts. If you are a manager who wants to look beyond just numeric stats and take a deeper look at trends, you can use Excel Charts.
In this example, we will use the weekly food cost percentage table. Assuming you have already found out the percentage of food cost, this method will show how to transform them into charts.
Steps:
➤ In our table, select the columns you want to make charts. Here, to visualize the percentage cost per week, you need to select the week column and the percentage (e.g, Week and Food Cost %)
➤ Go to Insert tab -> Recommended Charts.
➤ Choose the form of chart or graph that is best for your insights. Often, Column Charts and Line Charts are preferable.
➤ Click OK, and the charts will be generated. You can change the name of the chart to the one you want (e.g., Weekly Food Cost %).
➤ You don’t need extra formatting if you have formatted the resultant column earlier.
Notes:
➨ The Line Chart efficiently visualizes the trend and shows whether the food cost percentage is increasing or declining. On the other hand, the Column Chart helps to compare the percentage across various weeks.
➨ You can also use charts to show the food cost percentage per menu. Just select the menu items instead of the weeks.
Frequently Asked Questions (FAQs)
How do I avoid errors when dragging food cost formulas down a column?
If you find errors in food cost formulas after dragging down a column, you need to check that the cell references are properly set. Ensure they are in either absolute or relative. Also, go through the calculator settings and ensure they are set to Automatic.
Can I link food cost data from multiple sheets or branches into one summary?
You can link food cost data from multiple sheets and create new charts. For this, you need to use options like Data Consolidations or the Power Query approach. Other than these, you can also use VLOOKUP.
Can I automate alerts when the food cost percentage exceeds a threshold in Excel?
To automate alerts when the food cost percentage exceeds a certain threshold, you need to use conditional formatting or automated VBA Macros. For conditional formatting, you can go to the Conditional Formatting tab and select New Rule. Set the rule as Cell Value greater than and give your required value. However, the VBA method is efficient but requires extra formulas and coding.
How can I round the food cost percentage to the nearest whole number or specific decimal place?
Use the ROUND function to round the food cost percentage to the nearest whole number of specific decimals. The syntax of the function is –
ROUND(B2/C2, 2),
where B2/C2 is the formula and the 2 is the number of decimals you want to show. Just omit part 2 and write the formula inside the ROUND function for all numbers. Also, you can change the decimal value or make it a whole number by adjusting the decimal values from the Format Cells options.
Can I calculate food cost percentage based on multiple categories in Excel?
To calculate food cost percentage for multiple categories in Excel, like protein, dairy, and produce, you need to use the SUMIF function. This function will sum the cost based on the categories, which you can divide by total sales afterwards to find the food cost percentage.
Wrapping Up
It does not matter if you are running a small food court or multi-branch restaurant; calculating food cost percentage is non-negligible to see profit. Excel provides a powerful, accessible, and easy solution to track the food cost percentage. It comes with various options like simple formulas, creating Tables, and even automating with VBA. Also, you can use Charts to visualize the trends and interpret them more closely. You can choose and master any methods and see how your food profits, bursting numbers, and crossing all milestones.
