COGS help

first off I’m a new GM, and I was given the position with basically no training. I’ve kind of just learned everything as it happened. Since taking over I’ve dropped labor to about 26%, and I’ve gotten the store to a much more efficient level in terms of staff, and cleanliness. Now with CA minimum wage going up last week we’re repricing the menu. So i’m having to build a COGS spreadsheet, and I’ve never really done one before. I’ve figured out, for the most part, how much everything costs per ounce, and most of what our topping weight is per pizza. The trouble I’m having is building the spreadsheet itself. Is there a generic layout that anyone knows of that I can use to build off? or even some advice anyone can give to help me out. I have excel 2013. but it’s been a good few years since I’ve really used it. Any help would be appreciated. thanks in advance

There are a few food cost videos on You Tube…

Consider joining you will find templates there. I have an older version of excel and am resisting upgrading but it sounds like you could do it yourself
cell C5 (food cost) =$2.50
cell C6 (desired percent) =27.5
cell C7 (formula =C5/C6*100)
this formula results in a selling price of $9.09

Okay I’ll definitely give that a look over today. I appreciate the help

No need to upgrade…a spreadsheet program from 30 years ago will do all the math you require…Or use Google Docs for free…And Google gives you the option to share so you can collaborate with others to get help…

Royster is so right… you will be sorely disappointed if you upgrade. The new versions are a friggin’ mess if you’ve used to Excel from 1997 to 2007. I studied and worked in finance so I became a power user of Excel over those 10 years. I hate the new versions so much that I went back to using 2000.

All I have is the 2013 edition of office so I’m stuck with excel 2013. I’ll just have to deal with it.

The problem is the transition, not necessarily the software. If you learn on 2013 you’ll be good!

Honestly I haven’t used any excel in about 10 years so I’m relearning on 2013 and it doesn’t seem too different from what I remember. Just have to get the feel of things all over again

Well some advice would be to first create a separate sheet that you input all of your case costs into. Have that sheet break every thing down to the cost per ounce. You said you figured out how much everything costs per ounce, but what happens when your prices change? If you make that sheet now you’ll save yourself a lot of time later. You can just grab your order guide, type all of your case prices in, and have the costing sheet automatically update.

Okay so basically have a sheet with unit cost, a new cell with ounces per case, and a third cell that runs a formula to break down cost per ounce?

You all are talking about two different things: 1. Menu Cost and 2. COGS. Both are very important and they are related to each other but they are not the same thing:

Menu Cost: The cost to make each item using the ideal portions. i.e. this pizza has a dough ball that weighs 20oz (use batch cost to reach cost for this one) 8oz of sauce (batch cost again), a portion of 11 oz of mozzarella @ $X.XX per pound, 2.5 oz of pepperoni @ $X.XX per pound etc etc. oen 14" box @.XX. This is very important when working through a price model and is directly tied to portion control training to ensure that the employees are using the correct portions (or that the “ideal” portion reflects what is actually happening. If practice and theory are not joined here when it comes to portions the model is useless for pricing.

COGS: =(Starting inventory + new deliveries of food and supplies) - Ending inventory. That is it. It is not related to the amount of anything that goes on a pizza. It therefor takes account of food used for menu items, waste, spoilage, mistakes, theft etc etc and in the end it is holy writ. Menu cost and ideal portions are meaningless if you blow it on COGS.

Some operators break out food, beverages, condiments, paper, supplies and even do a calculation to deduct donations and apply that cost to marketing. I do not have than many hours in the week. If we use it up it is in my COGS. I divide it by net sales and that is my “number” for food cost. (I do do a second calculation that figures COGS sold as a percentage of sales before coupons. Since we record donations as discounted sales that calculation not only washes out the donations it also makes apples to apples comparisons between “normal” periods and times when we are doing more promotions but I find I do not use this number much as the variation is most often not significant)

We calculate COGS weekly. We do Menu Cost analysis when we print new menus and set prices… i.e. once or twice a year.

In my experience, I need a menu cost of several points lower than the total COGS I am shooting for. I also find that menu cost targets vary. I might want 20% on some things like bread sticks and be happy with 30% on items like wings and salad with minimal prep and even 40% on bottled beverages where I have no prep at all. It is a blended picture and flat line rules do not work.

My target is 29% of net sales. Remember that includes everything from red pepper to toilet paper. If we use it up it is in the number. Typical menu cost for a pizza for us is more like 20-23% on actual selling price (net of coupons) depending on what is on it. We use flat pricing for the number of toppings but we do have some toppings that are 2X or 3X price. For example, Wild Elk sausage is a 3X price topping. Nevertheless a veggie pie is going to come in with a lower food cost than a pie with 2-3 meat toppings. The difference between the 20-23% and the target of 29% is, first of all, items like wings or drinks with higher % cost and then food items given away on coupons (free drinks, free ice cream, discounted salads in dinner combos etc) cleaning supplies, condiments, napkins, waste, crew pies, donations, spoilage etc.

Okay that’s what I initially thought, but when I was looking some things up online they kept pointing to COGS for some reason. Glad the board on this site is so active everyone is extremely helpful

Exactly. This will save you so much time down the road.

Speaking of saving time, have you considered buying software to do this for you? Does your POS system have an inventory system available? Or have you thought about using Foodcost Pro? It’s only $160.