Instructions

Before you begin, remember that you only need to enter values in the White Cells. All other cells will c

Wherever you see the following symbol in a cell, simply hover over the cell for instructions on how The first step is to Enter the VAT Rate which is applicable in cell G10 below

VAT/Tax Rate = 21.00

Page by Page Instructions Result Sheet
1 2 3 4 5

Insert the name of your establishment in the top of this sheet Insert the Start Date and End Date of the Stock Period on the Result Page Under the Opening Stock column, if this is your first stocktake, then enter your opening stock count from your openin If this is not your first stocktake, then copy the closing stock figures from the last stocktake here. Enter the total Gross Revenue received into cell G10. This includes all cash, cheque and credit card revenue.

Purchases
1 2 3 4 5

Delete all information in the White cells as it is there for display purposes only Enter the correct month Enter the name of each supplier at the top of each column Once you have amassed all incoices and delivery dockets for the stock period, enter one delivery docket amount opp Remember to enter the values EXCLUDING vat.

Data Entry Sheet

1 2 3 4 5 Delete all information in the White cells as it is there for display purposes only Enter the product descrption and unit of measure for each item in the bar and store. Next enter the unit cost (always exc vat) for each item. The unit cost price calculator to the right of the sheet will help Enter your count of how many of each item are on hand during the stocktake. Remember that all entries into this sheet are cost prices which exclude VAT

Labour Cost Sheet

1 2 3 Delete all information in the White cells as it is there for display purposes only Follow steps 1,2 and 3 in that order. Everything else will calculate automatically

ctions

he White Cells. All other cells will calculate automatically.

er the cell for instructions on how to use it.

Enter your VAT/Tax rate here

your opening stock count from your opening count he last stocktake here. sh, cheque and credit card revenue.

eriod, enter one delivery docket amount opposite the correct date.

calculator to the right of the sheet will help you calculate the unit cost

1-Jun

to Data Entry

28-Jul

Results & Key Performance Indicators 85000.00 70247.93 3000.00 50307.00 4746.25 48560.76 21687.18 Net Sales Actual Beverage Gross Profit % Labour Cost % Beverage & Labour Cost %

Gross Beverage Sales Net Sales Opening Stock Purchases Closing Stock Cost of Sales Gross Profit

70247.93 30.87 17.40 86.52

Gross Profit %
Purchases
0.0% 0.0% 0.0% 19.6% 0.0%

30.87% Overall Analysis

Supplier 1

0.0%

0.0% 0.0%

Supplier 2 Supplier 3 Supplier 4

Beverage Cost
26%

0.5%
5.9%

Supplier 5 Supplier 6

Labour Cost
15% 59%

6.4%

67.6%

Supplier 7 Supplier 8 Supplier 9 Supplier 10 Supplier 11 Supplier 12

Gross Profit

Purchases by Supplier
Month
Date

August
Supplier 1
34,000.00

Supplier 2
3,200.00

Supplier 3
2,988.00

Supplier 4
243.00

Supplier 5
9,876.00

Supplier 6

Supplier 7

Supplier 8

Supplier 9

Supplier 10

Supplier 11

Supplier 12

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Totals

Total 50,307.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

34,000.00

3,200.00

2,988.00

243.00

9,876.00

0.00

0.00

0.00

0.00

0.00

0.00

0.00

50,307.00

Supplier 1 Supplier 2 Supplier 3 Supplier 4 Supplier 5 Supplier 6 Supplier 7 Supplier 8 Supplier 9 Supplier 10 Supplier 11

34,000.00 3,200.00 2,988.00 243.00 9,876.00 0.00 0.00 0.00 0.00 0.00 0.00

Supplier 12

0.00

Beverage Stocktaking Sheets

Draught Beers
Product Guinness Heineken Smithwicks Carlsberg Budweiser other other other other other other other other other Unit Keg Keg Keg Keg Keg Count 2.3 2 4 7.4 1.9 Unit Price 149.76 149.76 149.76 149.76 149.76 Extension 344.45 299.52 599.04 1108.22 284.54 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Total

2635.78

Bottled Beers
Product Guinness Heineken other other other other other other other other other other other other other other other other other other other other other other other other Unit btl btl Count 2.3 2 Unit Price 1.43 1.43 Extension 3.29 2.86 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Total

6.15

Spirits
Product Smirnoff Hennessy XO other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other other Unit 700ml 700ml Count 4.8 2 Unit Price 16 154 Extension 76.8 308 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Total

384.80

Minerals
Product Coca Cola Diet Coke other other other other other other other other other other other other other other other other other other other other other other other other Unit btl btl Count 648 456 Unit Price 1.43 1.43 Extension 926.64 652.08 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Total

1578.72

Wines
Product Chateau Barkeeper Chateuneuf du Barkeeper other other other other other other other other other other other other other other other other other other other other other other other Unit Btl Btl Count 4.8 2 Unit Price 16 32 Extension 76.8 64 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Total

other other other other other other other other other other other other other other other other other other other other other other other other other

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

140.80

Grand Total Value of Stock on Hand

4746.25

Labour Cost Worksheet

Verify that the date range on the right is correct as this will be used to calculate the total labour cost for the period

Start Date End Date Total days:

1-Jun 28-Jul 57

2
Total hours
(enter hours here)

Enter the total number of hours for each hourly pay scale below Gross
(enter hourly rate here)

36 45 34

8.00 8.50 9.00 9.50 10.00 10.50 11.00 11.50 12.00 12.50 13.00 13.50 14.00 14.50 15.00 15.50 16.00

Total Cost 0.00 288.00 382.50 306.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 976.50

Total Wage Cost hourly: salaried: 976.50 11,243.84 12,220.34

Total Hourly Wage Cost

3
Salary
John Mary

Enter the salaries for each salaried employee below. The period cost will be automatically calculated

Gross Yearly
38000 34000

Period Wage Cost 5934.25 5309.59 0.00

Salary Rate 1

Salary Rate 2 Salary Rate 3 Salary Rate 4 Salary Rate 5 Salary Rate 6 Salary Rate 7 Salary Rate 8 Salary Rate 9 Salary Rate 10 Salary Rate 11 Salary Rate 12 Salary Rate 13 Salary Rate 14 Salary Rate 15
Total Cost/Period

0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 11243.84