Dynamic Menu Planning with Spreadsheets

by Rick Curtis

Each year the Outdoor Action Program at Princeton University sends out about seventy 6-day backpacking trips, that's over 850 people in the field. Feeding this small army of folks is a big task. Due to the large size of our program and because leaders only return a few days before the trip goes out at the end of the summer, we do a central menu plan rather than have each group develop their own menu. Since we are only out for six days it makes more sense to work from a set menu rather than providing a range of food items and let the group develop a menu on the trail. By purchasing in bulk we save significant money.

Over the years in order to streamline the planning and ordering process I developed an Excel spreadsheet to help me lay out the menu, and, most importantly, make sure that I ordered enough food. The spreadsheet is pretty basic. Below is a sample food item. I'll walk you through how the spreadsheet works and explain the underlying formulas.

Download a Copy of the Food Planner Spreadsheet

You can see that I've created a replica of the spreadsheet complete with column headings and row numbers. I'll use the standard A1..B2 spreadsheet referencing scheme to point out the particular features of the spreadsheet. You can follow along by looking at the sample table laid out below.

The first set of important cells are highlighted in yellow. The are master values in these cells drive much of the spreadsheet. Cell G2 (Groups) is the number of actual trip groups that are going out. Cell F2 (Order) is the number of groups that I am going to order for. This is to give us a little extra food in case another trip is adding at the end of the summer and so we have some extra for some or our fall programs. Cell H2 (>10) is the number of groups that have more than 10 people. Our groups on the Appalachian Trail are 10 while groups in some other areas like the Catskills are 12. Cell I2 (Kosher) is the number of trips we have with observant Jewish Students who keep Kosher.

So take a look at Row 4. Here you see the heading labels for each column. Looking at our example, Heartland Granola in Row 5, let's just work our way across the row. I have duplicated Row 5 twice, once with the numerical values and the second time showing the formulas in each cell.

• A5 - Item Name
• B5 - Amount that each group gets of this item.
• C5 - How the item is packaged
• D5 - How many per case
• E5 - The size of each item
• F5 - This cell takes its value from cell F2 with the formula =$F$2
• G5 - Calculated field for now many of this item is needed. The formula is =B5*F5 or (Amount/group * Number of Groups)
• H5 - Simply a duplication of cell C5 showing the unit - =$C$5
• I5 - How many cases you are going to order. This is a field that you play with. By changing the number of cases you change the outcome further on (watch and you'll see).
• J5 - The Product ID code
• K5 - The price per item. This is a calculated field =M5/D5 or (Case price/ the number of items per case)
• L5 - How the item is priced. In this case E stands for each as in $2.60 for each box.
• M5 - The case price from the wholesaler.
• N5 - The extended price from the formula =I5*M5 or (Cases ordered * Case Price). This price will change as you change the number of cases ordered in I5.
• O5 - How many left over items you will have from the formula =(I5*D5)-G5 or (Cases Ordered* Number/case) - Number Needed for all groups). This will change as you change the number of cases ordered in I5.
• P5 - How the item is packaged
• Q5 - With some items we give groups over 10 some extra. In this case each group over 10 gets 1 extra box of granola. As a result we need to do a second calculation on the number of boxes we will have left over.
• R5 - This is the second calculation for the number of left over boxes after adding in the extra food handed out. It is calculated using the formula =O5-($H$2*Q5) or (The number left over initially - (The Number of Groups over 10 * How many extra of this item each group over 10 gets))
• S5 - Simply a duplication of cell C5 showing the unit =$C$5

FROSH TRIP FOOD LIST

Unit Codes: LB=pound, E=each, C=case, B=Box

By now you must be dizzy looking back and forth between the list and scrolling across the spreadsheet. You have the basic idea. There are a few extra modifications I've made to the spreadsheet that take into account some other factors. You might be wondering since I am using Cell F2 to set the number of groups I am ordering for why I bother with Cell G2, the number of groups. Well that's because for all nonperishable items I use the F2 number, but for perishables like cheese, pita's, etc. I don't want things to go to waste and we don't have the refrigeration facilities to keep the stuff. So with perishable items I use the value of G2 in what would be cell F5 in this example. I do the same thing with Cell I3 for the kosher items I order. You can see how the numbers in column F change when you look at the whole spreadsheet.

There are also a few cells that have some special formulas in them. Mostly this happens with Fresh Produce. Produce is often sold by the pound but I am handing out a specific number of apples, green peppers, etc. per group so I have to calculate the number ordered based on the actual count. Talking to my supplier I find out that a 28 pound box of green peppers is about 75 peppers (75 count). So I alter the formula for the number of extra needed in Cell O to be =(I64*75)-G64 or (Cases Ordered* Count) - Number Needed. The other thing to keep in mind with produce there is a 2%-%5 spoilage rate. That is in a box of 75 count of green peppers there are going to be 2-5% that are so skanky-looking that you wouldn't dare give them out to anyone. The same is true for oranges and to a lesser extent with apples. It doesn't happen much with onions and garlic cloves. The bottom line is know your produce!

So that's the concept. Once I have planned the menu and know the items needed for each group I enter the information into the spreadsheet. What's great about this is if suddenly I increase to 70 real trips up from 68 all I need to do is change Cell G2 to 70 and up Cell F2 to 74 and the entire spreadsheet recalculates. Now I haven't yet changed the number of cases ordered in column I so chances are if I look over at column O for the number of extra items I'll probably see some negative numbers, meaning that I am going to be short. I can simply up the number of cases ordered in column O until I have an overage that I am comfortable with. When I am all set I send the spreadsheet to my supplier for them to process my order. Of course like any spreadsheet this one also calculates the total food cost for budget planning purposes.

This spreadsheet has saved me countless hours of hand figuring over the years and every year we are right on the money with the amount of food we need and our little extra to tide us over for fall trips. I hope that this is helpful for your menu planning.

Download a Copy of the Food Planner Spreadsheet