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.

ORGANIZATION:
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).

COMPLEX CALCULATIONS:
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.

CUSTOM REPORTS:
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.

Food Cost Control and Excel

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

Answer:
I can recommend 2 or 3 solutions. It depends on your long term objectives. Please email me at joe@joedunbar.com 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.]

Question on Food Cost Basics

Joe,

My name is Steve and I am starting a new job as a Sous Chef but haven't learned anything about food costs or labor costs and I am afraid to not be able to be a team player on this important situation, that I am final realizing that its part of my job. Can you please teach me how to do all of this or point me in the right direction. I have read your basics on food cost but still I am having trouble understanding it all. Thank you for your time.

Sincerely
Steve

The goal of food purchasing is to place the food in inventory as close to actual production as possible. People who are pros know delivery dates and times, par stock levels, minimum drops, shelf life, and most important, the forecast for the order period.

The food cost formula is really a report card on how well you forecasted for the orders. Most food service companies have several backup suppliers they can use if they experience sales far beyond the forecast. Typically, these last minute purchases are made at cash and carry stores with much higher prices.

The heart of the food cost formula is purchases. You simply divide net purchases by sales. If you forecast your sales carefully, you will make better purchase decisions.

As a learning tool, you may want to take a look at 4 or 5 weeks of invoices to see past purchases. You can discuss spoilage and waste with the chef. Ordering is most important before busy periods. You need to have adequate supply without creating excessive waste. If you keep score, you will rapidly improve.