Creating a summary of totals in a recipe counter

**chefpaddy** · 10-20-2012, 01:23 AM

Hi guys, new member and hoping someone could possibly help me out.

---I will just start with a quick blurb about my experience in Excel, what I have done so far and what I hope to achieve---

My experience in MS Excel is to A-Level and European Computer Drivers Licence standard (that's what I gained from school!) which is hard to recall exactly where I got up to, but if prompted, then i'm sure I will pick up any advice and remember functions that people can advise.

I'm a kitchen manager creating some spreadsheets to create a forever-updating averages sheet (drawn from Daily Product Sales Reports) of the meals/sides/options in my pub.

So far, I have created a 8 week long timeline across the top of the columns, labelled Monday to Friday in each week. Going down the page is each of the items as they appear in my reports. At the end of each row is a simple =AVERAGE formula to give me an average per day. On another work sheet, there is a table presenting the total averages for the week on each item on the menu. So far, I have entered two weeks worth of sales and have my averages working fine, and created a separate work sheet giving me an average total of what we sell in a week (eg. 30 burgers one week, 20 the next - Weekly, I sell 50 burgers on average).

What i'd really love to achieve (and really impress some top brass) is somehow have a sort of recipe book (or list) in a work book so that I can list, for example, how many slices of bacon I use in a week, to govern how many packs I need to order.

So, for a very simple example.

regular Burger - 1 beef burger, 1 Burger bun, Xg of chips, Xml of sauce.

chicken burger - 1 chicken breast, 1 burger bun, Xg of chips, Xml of sauce.

As this shows, there are common ingredients in both recipes. So if my totals column read...

Regular Burger - 20
Chicken Burger - 10

Would there be a way of multiplying the total number of each item sold by the quantity of items in the 'recipe'? So that I would be given the totals of needing...

20 Beef burgers
10 Chicken breasts
30 Burger Buns
30 x (x)g chips
30 x (x)ml sauce

Hope there is enough of an example there - and sorry if it's slightly over the top. Any help or advice would be much appreciated - I'm unsure whether I have posted this in the right forum, or whether it should be in a more dedicated board. Please advise if so, and I will remove it and relocate.

Cheers! Paddy

**FDibbins** · 10-20-2012, 01:37 AM

looks like you are well on the way to having a good system there

what you are after sounds pretty do-able. however, in order to help you, can you please upload a sample workbook to show where your data is, layout etc?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**chefpaddy** · 10-21-2012, 09:12 AM

Thank you very much!

I have attached the most recent version of the spreadsheet with sales up to and including Saturday 20th Oct.

First worksheet is the daily sales and daily averages at the end of the rows - I have frozen cell B4 so that users can scroll about and still line up the products to the days. Second is a total of all the daily averages so I can work out how many meals sell on average per week.

Thank you for quick reply! Looking forward to really getting this sorted.

Cheers!
Paddy.

**chefpaddy** · 10-22-2012, 01:53 PM

Had a bit of brain wave with regards to creating this new sheet that I'm hoping to be able to get an idea of the quantities of all the items that we use in the pub, going from averages created in the sheet that is attached to my previous post.

If in one new sheet, running down the 'A' column, I have the individual meals that appear on the two previous sheets, and then across the first row, have all the items (taken from the stock count sheet) that we could possibly use. Then under each of the items that we use, add in the quantity of this item that appear in that meal.

At the very bottom of Column A, I will have a totals column. The formula in here will be...
(recipe total item 1 + average orders of meal)+(recipe total item 2 + average orders of meal) ...
and so on.

Hopefully this gives a better picture of what i'm working towards. I will attach a rough idea in my next post - waiting on an email from work with the stock count sheet to get all the individual items.