Managing your restaurant or food service kitchen requires keen attention to numerous details, with food cost control being one of two significant KPIs.
It’s critical to have a system for keeping track of price updates now more than ever. The pandemic ushered in hyper-sensitive volatility, and I, for one, was burned more than a few times making price assumptions. Now I have an easy solution. In this context, Excel’s conditional formatting is a fantastic feature that can be applied to numerous parts of your food cost spreadsheet.
Conditional formatting is a commonly used function in Excel and Sheets. For food costing it should be used to help keep your attention on price changes (or lack of) and dates for freshness, pricing and coming in below a threshold marker.
Understanding Conditional Formatting in Excel
Conditional formatting in Excel allows you to automatically apply formatting—such as colour changes—to cells based on their values. This dynamic feature can visually signal when it’s time to reevaluate certain inventory costs, significantly enhancing your ability to maintain accurate pricing and manage food costs effectively.
Begin by creating a spreadsheet listing your inventory items. Include columns for the item name, last price updated date (or first time entry), current price, and any other relevant details as shown in the sample inventory list below. This setup will serve as the foundation for applying conditional formatting rules.
As you can see from the image below, there is conditional formatting for older dates showing some items having not been updated for 2 years! Yikes! That’s a long time, but maybe here those products were not purchased recently or possibly not used anymore.
To implement conditional formatting that reflects the age of your inventory prices, follow these steps:
- Select the Date Column: Click the column header containing the last price update dates to select all the cells in that column.
- Access Conditional Formatting: Navigate to the ‘Home’ tab, find the ‘Styles’ group, and click on ‘Conditional Formatting’. This will open a dropdown menu with various formatting options.
- Create a New Rule for 3-Month Mark: Choose ‘New Rule’, then select ‘Use a formula to determine which cells to format’. Enter the formula =AND(TODAY()-$B2>90, TODAY()-$B2<=180) in the formula box, assuming your dates are in column B and your data starts in row 2. Adjust the column reference as needed for your spreadsheet. This formula checks if the date is more than 3 months but not more than 6 months old.
- Choose Formatting Options: Click ‘Format’, select the ‘Fill’ tab, and choose an orange color. Click ‘OK’ to apply this formatting rule.
- Create a Second Rule for 6-Month Mark: Repeat the process to create another rule, this time with the formula =TODAY()-$B2>180. This formula identifies dates older than 6 months. Choose a red color for the fill to differentiate it from the 3-month mark.
- Finalizing Rules: After setting up both rules, ensure they are correctly ordered in the Conditional Formatting Rules Manager. The 6-month rule should be above the 3-month rule to prioritize its formatting.
Conditional Formatting for Effective Food Cost Management
With these conditional formatting rules applied, your Excel spreadsheet will now visually alert you to inventory prices needing review. Prices last updated more than 3 months ago will be highlighted in orange, and those exceeding 6 months will turn red, serving as a clear, actionable signal to reassess and update as necessary.
“This simple tactic keeps you on your game because every time you log into your food cost calculator the conditional formatting is staring right back at you!” Chef Marty
This approach not only streamlines the process of managing food costs but also embeds a proactive review mechanism within your inventory management practices. By leveraging Excel’s conditional formatting, you can ensure your pricing strategy remains responsive to market changes and cost variations, ultimately supporting the financial health and success of your food business.
