Phone: (413) 727-8897 email:

Friday, August 27, 2010

Menu Engineering Using Excel

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.

Sunday, August 22, 2010

Putting Excel to Use in Food Cost Control

I mentioned my love of Excel in last week's post about software. Excel is ideal for 3 aspects of the food cost control environment: organization, complex calculations, and custom reports.

Most of the food cost control software assumes you have your entire database conceptualized and you simply need to tap enough keys to create the ideal starting point.

Newbies will find plenty of challenges ahead.

Inventory locations, vendors, item categories and inventory sequence are database requirements for phase one. You initially build the item database to aid you in purchase order and invoice entry (the areas requiring 80% of the time once you go live). At the same time, inventory count sheets need to flow from the item database (along with any batch recipe production items).

Excel is a great tool for getting organized. Build columns for the item description, primary vendor, vendor codes, bar codes, category (produce, meat, fish, etc.), storage method (frozen, refrigerated, dry), primary inventory location (where you look to decide when and how much to order), unit of measure data with conversion factors to allow purchases, storage and recipe usage, par levels, alternate item codes (the vendor code for the item you would purchase if there was a stock out).

Food cost control involves using scalable recipes to forecast production requirements, purchase requirements, and line setup requirements. In addition, plate recipes help you forecast profits, calculate usage variances, and price menu items. The top software will provide plenty of excellent information. Excel can provide you with customer profiles, sensitivity analysis (what-if?), and many other advanced concepts.

I use a data mining tool to create custom reports for my clients. Typically, the client will send me their favorite report and ask a question: "Can we get all this exactly the way it is AND add a column on the end which....?"

The data mining software creates the calculation field missing in the source report. Mining software always offers several output options including text, CSV, PDF and Excel formats. I often choose Excel since the analyst can take the analysis further if necessary. Excel offers all of the formats mentioned for the final presentation.

Tuesday, August 17, 2010

Food Cost Control and Excel

I'm looking into buying a menu costing program/software are there any you recommend?

I can recommend 2 or 3 solutions. It depends on your long term objectives. Please email me at so we can pick your ideal solution.

Response from Erik:
From what i have come across, Excel should be the go-to costing program and you should use your own data.

Good point Erik!

I can't think of food cost control without Excel in the picture. The only issue I have is the term "Excel user" has a tremendous variation.

There are people who could model the Big Bang using Excel and other people who haven't discovered the SUM formula (use + sign).

Importing history into Excel is a pain if the files change (for example, a new menu item) for anyone who hasn't mastered Excel's terrific data functions.

Once the power users get everything done conceptually, many migrate the model over to Access so database updates go smoother.

Somewhere along this curve, the benefit of purchasing a software tool specifically designed to handle recipe costing, menu analysis and cost control turns positive.

Many of my clients had terrific Excel based recipe costing models which helped us in building a solution. They often find additional savings of 10% (i.e. if their current Excel control food cost was 30% they would find the new food cost would drop to 27%).

Overall, I believe Excel is the logical starting point for anyone who is serious about getting on top of their food cost. My advanced menu analysis tool - The Menu Map - is 100% Excel based. Since the model focuses on only 3 data points (selling price, food cost and number sold) at the core, Excel is ideal.

Once you want to filter purchase data, Excel loses the power a serious food cost controller needs for investigating problems.

For example, FoodTrak lets you filter invoices by date range, vendor, report group, invoice number, and specific items. There are exception reports to bring many issues to your attention and the terrific data entry alerts show major price variations in real time.

Resorts may need transfer cost reports. This report is a major pain in Excel. Cost updates from purchase data provide freshly costed transfer reports by profit center. Of course, many operations do not have this need. The answer really depends on the operation.

[This post is from comments on the Basic Recipe Costing post.]

Restaurant Data Pros

web counter