Prepsheets in Excel

July 22, 2021 7 min read

Richard

Prepsheets

Before Prepsheets.com The Caterers built a Prepsheets in Excel. This was a complete system including: recipe costs, allergens, calories and portion sizes.

Unfortunately it couldn’t grow as they expanded to multiple locations and more people needed to access and edit the recipes. However if you’re just starting out it might work for you! This system gives you a list of all your ingredients which you can link to your recipes. These recipes help you become competitive, compliant and consistent with your dishes.

Download the Prepsheets template



Download the Prepsheets Template



Setting up the Ingredients Index

The first step is to make the list of all your ingredients. You can base this off your Approved Purchase List, invoices, supplier price books, etc.

In the template you downloaded go to the Ingredients Master sheet. There will around 150 common ingredients already in here.

Next, let’s add some ingredients. For each ingredient you’ll need to fill in it’s name, supplier, list weight, and list price. The template will then calculate the Price per unit for you.

If you know the calories or allergens you can also add those. For each allergen that the ingredient has change the 0 to a 1 in that allergen’s column

By doing this now, our recipes will be able to pull in this information later on.

Writing a Recipe

We’re now ready to start writing in recipes. The first step is to duplicate the Recipe Name sheet by right-clicking the sheet. This will give you a blank recipe template to fill in.

You can ahead and fill in the following cells

  1. Category
  2. Item
  3. No. of Guests

Next up, we’ll add some ingredients. Be careful when editing cells as there are formula everywhere.

  1. Go to your index and find the ingredient you want
  2. Copy that ingredient’s exact name
  3. Paste the ingredient name into the first ingredient row under Item
  4. Put the ingredient’s quantity into Prepped Qty per Portion
    1. Put the unit of this quantity – kg, g, u (for units) – into the adjacent unit column

The system copies over Prepped Qty per Portion and unit to Order Quantity Per Portion and it’s adjacent unit. You can delete the formulas and change these values however.

For example: Say you needed 100g of peeled carrots. You might want to to order then 110g of carrots.

Finally the system calculates the amount required in it’s base unit to get Total Amounts Required and multiples this by the number of guests to get the quantity you’ll need to order which is put in the Order column.

In our example above each portion requires 0.1kg of carrots and we’re preparing for 11 people so we need 1.1kg in total.

🎉 Congrats, you’ve added your first ingredient. Do the same for all the other ingredients.

Once you’re done highlight all the rows without ingredients (e.g. column C has no content), right click and click Clear Contents.

Info

Once you’ve finished you can see the recipe information

In the top right is the cost:

and right underneath is the total calories:

at the bottom is the portion size and the numbers of any allergens:

Finishing up

That’s it. Just duplicate the inital Recipe Name for each recipe you have and now you have your own recipe system in Excel.

Of course there is a fair bit of work here and maintaince isn’t exactly easy so we think you’d be better off just using Prepsheets.com which let’s you just focus on what makes you money. Preparing delicious meals.

As always if you have any questions or need help with anything you can or send us an email at: info@prepsheets.com