If you are looking for the formula to calculate your month end inventory food cost click on this link to go there: Food Cost Calculation Formula . This page details the Excel Food Cost Calculation Tool for tracking your monthly inventory and is available to Premium Members for download.
Food Cost Calculation Tool
The Food Cost Calculation File is an excellent inventory management forms tool which helps analyze your month to month inventory. It is an Excel file and has a separate tab (Jan. – Dec.) to track your food cost for the whole year. Each month has the info and format listed below. It has room for up to 23 storerooms/storage areas. As you enter your ending inventory for each storeroom it automatically adds them together to calculate your ending inventory. The ‘Comparison To Last Month’ box compares the changes in value for each storeroom so you can quickly see major changes (potential problems) in each area. It also compares the difference from last month for your Beginning Inventory, Ending Inventory, Transfers, Purchases, Sales, and the difference in points on your Food Cost. Decreases in any of these values appears in red.
The only areas you need to fill in are the areas in blue. All the other areas are calculated for you.
In the ‘Key Markers’ area you will find automatic calculations for your Average Inventory, the amount of Inventory Used, your Inventory Turns, the percentage of change from last month in Ending Inventory, Purchases and Sales. And there is a separate area to track your Transfers In, Transfers Out, Marketing Credit/Expense, Comps, and Unrecorded Sales.
If you have multiple venues/units then use a separate Food Cost Calculation File for each venue and name them after each venue.
System Requirements: Excel 2000 or better, Windows 98 or better.
Click on the image below to see a larger version of what a typical sheet looks like.
Food Cost Calculation File’s features
The features of the Food Cost Calculation File are highlighted and explained below. Click for larger image.
Tracking Food Cost and Monthly Variances
The Monthly Variances area is on each month’s sheet and is for tracking various credits/debits affecting your food cost. You can use this area to simply draw totals from other excel files. Or you can lengthen the columns so as to add every transaction in this area. Click image below to see what the whole page looks like.
Premium Members can download the End of Month Foodcost Sheet
(get membership info)
Microsoft Excel required (not included)
Suggested Reading
See Our Tutorial Library!
Related Pages Index
- Break Schedule for Cooks Restaurant Employees
- Kitchen Opening & Closing Duties
- Kitchen Pre Shift Meeting Form
- Kitchen Training Checklist for Cooks
- Produce Ordering Guide for Restaurants
- Restaurant COVID 19 Sanitation Checklist
- Restaurant Kitchen Order Guide Template Excel
- Restaurant Labor Cost Excel Template
- Restaurant Maintenance Checklist – Kitchen Maintenance Log
- How to Create Forms with Excel
- Kitchen Temperature Log Sheets
- Restaurant Kitchen Schedule Template Excel
- Commercial Kitchen Cleaning Schedule
- Beef Butchering Yield Form
- Cook Evaluation Form
- Fish Filleting Log and Butchering Chart
- Food Cost Calculation Form
- Kitchen Station Task List
- Recipe Evaluation Form
- Prep Sheets
- Recipe Templates
- Using Excel for Event Planning
Books by Chefs Resources!
Handcrafted Cards
wpDiscuz
Hi there, for some reason the download link will not show in my any browsers chrome nor firefox. Please help me I would love test out this useful tool.
@disqus_xmKCPmj8Ug:disqus I have fixed the download button so it appears again. Sorry for the delay.
Awesome!! Thank you very much
Hey I dont know if I’m looking in the right area, the beginning inventory link, and in January is missing? Please advise.
Yes, on the January tab the Beginning Inv. cell is blue so you need to just enter the value for your beginning inventory (which is December’s ending inventory). If you want to link it to an existing Excel sheet for December, open that file. Then go back to the January tab on this sheet, click in the Beginning Inv cell where the link goes, and create the formula by pressing the “=” sign and then go back to the December file and click on the cell with the ending inventory. Then press “Enter” and you’re done!
Is there any way to change the default currency?
Yes. This page gives directions.
https://www.chefs-resources.com/culinary-videos/kitchen-management-videos-for-chefs/change-currency-in-excel/
Great 1
hi david, I’m great fan of your formulas and spreadsheets. It will really help on my café which is coming very soon in here in aussie Melbourne part.I’ve watch also some of your video’s on youtube. Just want to ask about the excel on Food cost calculation tool. since my café will be opening soon how can I start from march inventory if I don’t have yet my ending inventory co’z I will be just startin’ Do I have to start on storeroom count or just on beginning nventory.I was little bet confused. what if I start this coming 1st… Read more »
In this scenario you’re beginning inventory would be whatever you have in all your storerooms/coolers the night before you open. Probably the easiest way would be to inventory all the food the day before you open and then purchases which arrive on the 1st would be part of purchases for March inventory. If you are doing any training on/after the 1st which includes food then try to keep track of that cost (if it’s a lot of training) so you can keep track of how much you spent for training and can back that out of your numbers in April… Read more »