Planning Prep with Excel Prep Charts

How do you like that Prep Chart picture above???Looks pretty good, right?

Wrong.

It mixes all sorts of different tasks requiring different skills and different equipment together.

It does not project sales accurately with daily forecasted levels.

It does not track on hand inventory and daily prep for an entire week.??

There is no place to enter what is on hand, and there should be two places for that…one for on hand in the line coolers and another for on hand in the walk-in cooler.

It does not distinguish between “Make At” and “Build To” levels.

It is no worse than anything else you will find in most restaurants or anywhere online…even on sites that charge you money for “teaching” you how to organize food preparation.??

We can do much better.

Before you try to incorporate the detailed information below into your prep charts and pars, you should read two other articles in this series:?Kitchen Design Drives Sales and Profit?and?The Keys to a Productive Prep Kitchen.

If you follow the advice in those articles, you will set up your prep kitchen divided into stations by skills and equipment and divide the tasks to lay out in your prep charts following that system.

Here is a very brief review.

Job Division:??Jobs should be divided up by complexity and by equipment usage, not by ingredient type.??Largely we divide up by hot versus cold prep.???Since the mixer is most often used for baking, it is usually assigned to the hot prepper.??In a two-station prep kitchen we tend to assign “sink,” “table-top,” “meat slicer” and cutting/portioning prep to less experienced cooks at lower pay, and range, oven, mixer prep to more experienced cooks at higher pay.??

????????????Line prep is?only?work that requires line equipment such as fryers (nacho chips or tortilla cups, blanched French fries), or must be done more than once a day due to freshness issues (such as lemon wedges, chopped parsley, sliced green onions…. i.e., fresh line garnishes.)

????????????Within these divisions, try to keep all meat cutting within one time frame and all vegetable and salad cutting at a separate time, preferably earlier.??Sanitize station, tools and cutting boards extremely well between these types of prep.

????????????The prep is to be done in the order that is listed on the chart.??As each item is done, it is marked over with a highlighting pen so that we can track progress.??A “9 am” line should be delineated across each preppers list showing our mid-morning “destination” point to ensure we are on track to be ready for lunch service.

????????????The prep charts are posted on clipboards in individual stations with the last 3 weeks charts behind the current week should we need to review usage history.

????????????The hot prepper should start oven prep first, so that we are never in a position of waiting for something to bake or roast while there is no other remaining work.??Generally, start lower temperature items first, as it is faster for the oven to heat up than cool off between jobs. The other priorities to be considered are products that are needed for early service in the day (e.g., Croissants or bake-off breads.)

????????????Try to set up your prep schedule to use one piece of equipment at a time repetitively so that it will get one thorough cleaning at the end of that work.??Schedule the most difficult to clean food items at the end, e.g. sliced cheese last on a meat slicer.

Prep pars: Responsibility-Day Sous Chef and Prep Coach reviewed by Chef and GM routinely

Consult your projected sales mix.??Many items will be simple “build to” pars.??Set these by simply multiplying the total expected sales of the items for the week by the % daily sales for each day.

For example, if you are projecting 80 orders of dry ribs for the week and Tuesday’s sales are 11% of the weekly sales, your Tuesday par is 9 portions.??As stated below, though, dry ribs hold just as well portioned as they do thawed in bulk, so you would pad the amount by 25% and make your par 12 portions.

Once you are open, you can just use the highest daily sales of the previous four weeks.??Most can be “padded” to ensure no run out.??For example, portions of dry ribs or chicken wings hold just as well in portions as they do in bulk, so it is recommended to build levels high, padding 25% over expected daily sales.??Many fresh produce items, on the other hand, should be kept to a minimal level and we should expect to re-prep them after dinner.??Burger tops are a good example.??The Tomato and lettuce combination can hold poorly, so levels should be set low.??However, extra slices of tomato can be prepped and held stacked upright quite nicely, to allow for easier reprep after dinner.??Salad mixes are another example.??Prep minimal quantities and be prepared to reprep before dinner, especially on sunny hot days in the spring.??

The first thing the night sous chef should do upon starting the shift is to inventory the daily prepped “build-to” items and ensure there are enough for dinner, based on the % of business done by the night shift.??If dinner is 2/3rds of the business on Fridays, s/ he should expect to find 2/3rds of the prep par still left on hand, and the day shift is responsible to reprep that, if necessary, before leaving.

The “make at” pars are for items that hold well for several days or more.??The idea is to prep enough to last for the maximum holding time when you are down to enough for your busiest day’s sales.??Let’s say you make a tomato sauce that is used in a pasta and on a pizza.??For a pasta you use 12 fl oz and for a pizza 3 fl oz.???Let’s say your busy day is a Friday and you generally go through 8 of the pasta dishes and 16 of the pizzas.??Your total usage is then 12 fl oz X 8 and 3 fl oz X 16: 144 fl oz.???Assuming your storage containers are marked in liters, your “make at” level is then 4 liters (rounded down very slightly from 144 fl oz.)??This way you will never run out of product and have to make it on the fly (and use it before it's had a chance to blend flavours overnight.)

How much to make???If you are using 144 fl oz on Friday, which is putatively 20% of your sales, then your weekly usage is 720 fl oz.??Let’s say the maximum holding time is 4 days, then you want to make 720 fl oz divided by 7 times 4: 411 fl oz.??Most tomato sauce recipes will yield about 120 fl oz (100 fl oz tin of tomatoes plus onions and seasonings) so you will want to make 3.5 recipes.??

Your par is 3.5R @ 4 L.

You really need to do all this figuring with a calculator by hand and understand it, and then you are ready for the more advanced Excel Spread Sheet Prep Charts demonstrated below!

The retained charts on the preppers’ clipboards track four weeks of prep and we can adjust pars from the sales mixes posted daily and from simply tracking on-hand levels from day to day and week to week.??It will provide a great snapshot of the prep business and should be reviewed and updated by the general manager, chef, and day sous-chef every Monday morning after inventory.????Watch out for the patterns of carryover to prepped goods, looking for a 1:10 ratio in sensitive products, and a 1:4 ratio in non-sensitive items.??Holding times for bulk products should be entered on the chart beside the items, and then compared to the pattern of prep of those items and adjusted accordingly.

There should be a rigorous monthly comparison of the prep charts and order guides to the sales mix and to product movement per the computerized food and beverage tracking system.???Keep your product ingredient pars tight and your prep levels relatively generous and you can keep a low inventory and very fresh food without?ever?running out of product for the customer.

????????????There is a tendency for the Line Setter to do incomplete inventories and charts, since s/he has many small tasks to perform.??This is a mistake!??These items are our fresh garnishes and are critical for the customer’s experience.??The very first sign of inattention to par levels and accurate prep chart use will likely be very tired looking lemon wedges on the line.

Here are the two formulae used to build bulk “Number of Recipes to Make” at “Inventory levels that will cover your busiest day.”???These bulk items improve by holding overnight, so you want to have enough on hand to ensure that always happens.?

Numbers of recipes to make formula:

(1000s of $ Sales for the week) X (portions sold per $1000 sales) / (# times made per week) / (portions per recipe)

Make at busiest day level formula:

(Busiest days sales in $1000's) X (portions sold per $1000 sales) X (portion size in counting unit)

Here is the formula for “build to” levels.??

(Number of portions sold per $1000 of the item) X ($1000’s of dollars expected that day.)

Let’s start with our “Hot Prep” chart to demonstrate the “Make At” formula.??Here is a look at the whole chart. You can see that all Hot Prep levels are “Make a certain number of recipes” at “one busy day’s stock on hand.”??Usually, you are making the product somewhere between once a week to three times a week, so you should only have to make about 25% of the items on any one day.

Let’s look at how we used the formulae to calculate the?Recipes to Make?level and the?At Unit Ct?level.??We must have recipes written as below with the correct yields and times per week for the recipe to be produced.??Normally, I multiply out the recipe by 2, 3, 4, and 5 times on the recipe sheet so the prepper does not have to do that for various sales levels.??Note above that we have daily anticipated sales in 1000’s of dollars, and two places to count inventory on hand daily to the left of the space to inscribe the amount to prep.

Exotic Mushroom Soup?

Part A

125 g butter

500 g ?” diced shallots

40 g Kosher Salt

35 g creamed Garlic

15 g Coarse Pepper

1 ? T SupHerb Thyme

1 T SupHerb Rosemary

Sweat until shallots translucent

Part B

333 g each Cremini, Shiitake, Oyster Mushrooms sliced ?”

85 g flour?

85 g butter

Add and cook 10 minutes over low heat.

Remove from heat.??Puree and return to pot.

Part C

3 K water

125 g Savory Vegetable Broth concentrate??

1360 g whipping cream

255 g marsala

45 g Lemon Juice?

45 g Balsamic Vinegar

15 g Porcini mushroom powder?

15 g Worcestershire??

3/4 t Tabasco

3 bay leaf

Mix in thoroughly.??Simmer 10 minutes, lid on.??

Part D

333 g each ?” sliced Cremini, Shiitake, whole Oyster Mushrooms?

85 g butter

Sweat. Mix in.?

Refrigerate up to 5 days.??Prep 1.5 times per week.??

Yield 216 fl oz per single recipe, 21 portions.

Serve 10 fl oz (.295 L) with ? t 1/8th” chopped chives.

Historically, we sell 160 portions of this soup in a $70,000 food sales week or 2.286 portions per $1000 sales. The “Make number of recipes” formula gives us:

(The?sum of cells C2:I2?gives us?70?1000s of $ Sales for the week) X (2.286?portions sold per $1000 sales) / (1.5?times the soup is made per week) / (21?portions per recipe)

70 X 2.286 /1.5 / 21= 5.08 Recipes “to make” and we have set the chart to no decimal points to round it down to five.??

Here is how that looks in the “formula bar” of the chart.??It is at the very top in white on black.??We’ve added the predicted daily sales in $1000’s of dollars together, multiplied by the portions sold per $1000 sales divided by how often we make the product weekly and by the portions per recipe.??Simple.??

Let’s have a look at the “Make At” level, called “At Unit Ct” on the sheet.?

(Cell?G2?is the busiest days sales in $1000's) X (2.286?portions sold per $1000 sales) X (.295 L?portion size in counting unit)

14 X 2.286 X .295 L = 9.441 Liters.??The chart rounds it down to 9 Liters on hand.??When we get that low, we make 5 Recipes.

If we want to update the chart, we track popularity per $1000 sales every period and change that number as necessary.??We project daily sales for the week early every Monday morning and just enter those numbers in the 7 boxes provided.

?I developed this for a very busy restaurant in a convention center that also serviced cruise ships.??So, we could have sales bouncing up and down every day of every week like a yo-yo depending on the convention and cruise ship bookings.??This made life easy!

Let’s look at our Portion Prep Chart for this business (done by the Dishwasher from 7 am to 11 am…inexpensive labor.)??Note that this prepper is almost entirely “build to” levels of portions.??Sometimes we just portion everything that someone more skilled has sliced or diced or cooked.??Sometimes we are “making at” very simple recipes like mixed cheeses.

All we must do is place the sales per $1000 of each item historically on that items row to the right in the “hidden” part of the chart.?

And here is one completed formula from all those listed to the right in the “hidden” part of the chart.??The formula is in white on black at the top in the “formula bar.”

We are looking at the top item, Wing Portions, Row 3.

Cell C2?(the $1000’s of dollars sales predicted for Monday that week) times?Cell X3?(which is the portions sold per $1000 sales historically.)

6 X 7 = 42.??The portions of Wings to “build to” on this Monday is 42.???

If the popularity of Wings goes up, we increase the number under?Column X,?and we always predict daily sales for the week every Monday morning before the prep cooks start their inventory

And if that is not enough spreadsheets for you (!), here are the other 3 prep charts from this very successful restaurant. These $70,000 per week volumes are from early spring, by summer these prep charts doubled in volume.?

All we had to do was increase the sales numbers in Cells C2 to I2 and check the popularity per 1000 dollars sales of each item. The salads and appetizer popularity went up as they always do in the summer.

And you just got a very good soup recipe at the cost of reading this involved article!