During the month, three fellow restaurant consultants have contacted me about menu engineering and recipe costing with specific questions regarding Excel. Excel charts do not automatically create the popular four quadrant chart with Stars, Plowhorses, Puzzles and Dogs (or any of the other 4 quadrant variations). I have accomplished this task using a few third party utilities. In my analysis, recipe costing is accomplished using software specifically designed for this purpose.
I consider the POS system Product Mix report the source of the key data. Since menu engineering is concerned with selling price fluctuations, the PMIX report has all essential data (number sold and selling price) except the cost to produce each item. Most POS systems have Excel file export capability. If you are adept at the table look-up formula, you can place the exported data exactly where you need it in your model.
Generally, I use good old data entry to update the recipe costs using the numbers from the software. Once I have the number sold, selling price and recipe cost, the sophisticated menu engineering and analysis reports flow from Excel. The four quadrant chart is not the only report produced. You can use sorts and filters to generate a tremendous amount of valuable information.
Anyone who wants an all-in-one solution for inventory, recipe costing, purchasing, menu engineering and requisitions really needs to invest in a proper solution. Excel tables are famous for errors in formulas and there is no audit trail for your purchases (which feed all cost calculations).
Many people have asked me for a recommendation. I do not own a restaurant and I do not have a laboratory in my office with 20 different recipe costing programs loaded and ready to test. In fact, many of the popular software solutions come out with substantial improvements each year. It would be a full-time effort for anyone to honestly provide this type of service.
Many of the popular solutions target a segment and do a terrific job. FoodTrak moved from a restaurant model to a hotel/resort model around 2000. They added transfers and requisitions to the essential restaurant reports already in the program. The reasons I tend to work mostly with FoodTrak: longevity and optional cost methods. They have been around since 1980 and I started my company in 1990. If you need FIFO cost numbers, you won't find a better solution for the investment. There are other programs in the six figures range which have true FIFO but I am assuming very few of my readers would invest $100,000 plus for a solution.
There are many programs capable of decent reporting using the last cost method. I encourage anyone on a tight budget to start with one of these solutions for recipe costing needs.
