This tool will help you figure out what your ideal cost should be.
It will highlight any
variences so you can look into solving them.
Instructions:
At the bottom of this worksheet are 6 tabs, with a totals and example page.
1) List your items, then enter the information (cost, sales $, # sold) at the end of each
period (weekly, bi-weekly, monthly).
3) On the totals sheet, the information from the other sheets will autoatically come up and
show a total ideal CoGs. There is a section to enter actual cost for each catagory. This is
determined through proper inventory at the end of each period. A variance will
automatically be calculated based on the actual and ideal cost.
A Negative number in red means that your actual expenses were more than your ideal
expenses.
A positive number in black means that your actual expenses were less than you ideal. (This
isn't always a good thing, so still take note.)
A substantial variance in either direction, in any catagory, usually means there is a problem
with inventory or costs.
Sales Period:
Food Cost Sales $ CoGS%
Margin # Sold Total Cost Total Sales
Food Summary
Total Cost
$0.00
Food Summary
Total Sales CoGS%
$0.00 #DIV/0!
Sales Period:
Bottles /
Cost / Case Cost
Beer - Bottle Case
Servings /
Cost / Keg Cost
Beer - Draft Keg
Sales $ CoGS% Margin # Sold Total Cost Total Sales
Apx. Kegs
Sales $ CoGS% Margin # Sold Total Cost Total Sales Used
Beer by the Bottle Summary
Total Cost Total Sales CoGS% Avg. Cost Avg. Sales
$0.00 $0.00 #DIV/0! #DIV/0! #DIV/0!
Total Number of Bottles Sold
0
Beer Draft Summary
Total Cost Total Sales CoGS% Avg. Cost Avg. Sales
$0.00 $0.00 #DIV/0! #DIV/0! #DIV/0!
Total Number of Glasses Sold
0
Beer Summary
Total Cost Total Sales CoGS%
$0.00 $0.00 #DIV/0!
Sales Period:
Servings /
Cost / Bottle Cost
Wines - Glass Bottle
Servings /
Cost / Case Cost
Wines - Bottle Case
Sales $ CoGS% Margin # Sold Total Cost Total Sales
Sales $ CoGS% Margin # Sold Total Cost Total Sales
Wine by the Glass Summary
Total Cost Total Sales CoGS% Avg. Cost Avg. Sales
$0.00 $0.00 #DIV/0! #DIV/0! #DIV/0!
Total Number of Glasses Sold
0
Wine by the Bottle Summary
Total Cost Total Sales CoGS% Avg. Cost Avg. Sales
$0.00 $0.00 #DIV/0! #DIV/0! #DIV/0!
Total Number of Bottles Sold
0
Wines Summary
Total Cost Total Sales CoGS%
$0.00 $0.00 #DIV/0!
Sales Period:
Liquor Cost Sales $ CoGS%
Margin # Sold Total Cost Total Sales
Liquor Summary
Total Cost
$0.00
Liquor Summary
Total Sales CoGS%
$0.00 #DIV/0!
Sales Period:
Merchandise Cost Sales $ CoGS%
Margin # Sold Total Cost Total Sales
Merchandise Summa
Total Cost
$0.00
Merchandise Summary
Total Sales CoGS%
$0.00 #DIV/0!
Sales Period:
Food Total Cost Total Sales Total CoGS Total Margin
Total Food $0.00 $0.00 #DIV/0! $0.00
Beverage Total Cost Total Sales Total CoGS Total Margin
Wine-Glass $0.00 $0.00 #DIV/0! $0.00
Wine-Bottle $0.00 $0.00 #DIV/0! $0.00
Total Wine $0.00 $0.00 #DIV/0! $0.00
Beer-Bottle $0.00 $0.00 #DIV/0! $0.00
Beer-Draft $0.00 $0.00 #DIV/0! $0.00
Total Beer $0.00 $0.00 #DIV/0! $0.00
Total Liquor $0.00 $0.00 #DIV/0! $0.00
Total Beverage $0.00 $0.00 #DIV/0! $0.00
Merchandise Total Cost Total Sales Total CoGS Total Margin
Total Merchandise $0.00 $0.00 #DIV/0! $0.00
Total Ideal Cost Total Cost Total Sales Total CoGS Total Margin
Total CoGS $0.00 $0.00 #DIV/0! $0.00
Actual Cost Variance
Actual Cost Variance
Actual Cost Variance
Actual Cost Variance
Sales Period:
Jan-14
Bottles /
Beer - Bottle Cost / Case
Case
Cost
Corona $19.86 24 $0.83
Dox XX $18.45 24 $0.77
Bud Light $22.12 24 $0.92
Bud $20.48 24 $0.85
Coors $17.49 24 $0.73
Coors Light $18.75 24 $0.78
Sierra Nevada - Pale Ale $22.36 24 $0.93
Servings /
Beer - Draft Cost / Keg Keg Cost
Blue Moon $126.00 141 $0.89
Lagunita IPA $144.00 141 $1.02
Angry Orchard - Apple Cider $65.00 45 $1.44
Guiness $186.00 141 $1.32
4
Sales $ CoGS% Margin # Sold Total Cost Total Sales
$4.00 20.7% $3.17 75 $62.06 $300.00
$5.00 15.4% $4.23 64 $49.20 $320.00
$3.00 30.7% $2.08 110 $101.38 $330.00
$3.00 28.4% $2.15 48 $40.96 $144.00
$3.00 24.3% $2.27 89 $64.86 $267.00
$3.00 26.0% $2.22 136 $106.25 $408.00
$7.00 13.3% $6.07 87 $81.06 $609.00
Apx. Kegs
Sales $ CoGS% Margin # Sold Total Cost Total Sales Used
$5.00 17.9% $4.11 312 $278.81 $1,560.00 2.2
$7.00 14.6% $5.98 247 $252.26 $1,729.00 1.8
$6.00 24.1% $4.56 187 $270.11 $1,122.00 4.2
$7.00 18.8% $5.68 148 $195.23 $1,036.00 1.0
Wine by the Glass Summary
Total Cost Total Sales CoGS% Avg. Cost Avg. Sales
$505.77 $2,378.00 21.3% $0.83 $3.90
Total Number of Bottles Sold
609
Wine by the Bottle Summary
Total Cost Total Sales CoGS% Avg. Cost Avg. Sales
$996.41 $5,447.00 18.3% $1.11 $6.09
Total Number of Glasses Sold
894
Beer Summary
Total Cost Total Sales CoGS%
$1,502.18 $7,825.00 19.2%
