Tracking inventory lets a business owner know how much stock he has on hand, how much he needs to order and, most importantly, how much of that product is being sold. When compared with sales figures, inventory can also provide information about loss, and provide clues as to where that loss is occurring. Track beer inventory through the use of spreadsheet software.

Step 1.

Set up the inventory-tracking software. Click on the "Start" link on the computer and open the spreadsheet program. Click on "New" and choose "Inventories" from the column on the left-hand side. Click on the template for "Inventory/Cost Analysis" to open a new spreadsheet.

Step 2.

Count, by hand, each item you have on hand and notate it. Break down beer inventory by volume. For example, a case of beer has twenty-four 12-ounce bottles. Notate that as 288 -- the total ounces in a case. A regular keg is 15-1/2 gallons, so that equals 1984 ounces. A pony keg -- which is a half-size keg -- has 7-3/4 gallons or 992 ounces.

Step 3.

Enter the inventory data as directed by the spreadsheet. Across the horizontal top line, enter the name of each brand of beer that you stock.

Step 4.

List the inventory-analysis information in the vertical column. The information should include the number of units in inventory at the beginning of the period, the units available for sale, the units sold, and the number of units at the end of the period

Step 5.

Enter the value of the inventory for the cost-of-goods-sold analysis below the inventory-analysis section. This information should include the beginning inventory, additional purchases, the cost of goods available for sale. Subtract from these the ending inventory, which will give you the total cost of goods sold.

Step 6.

Add the inventory-costing-value information below the cost-analysis section. Categories should include the cost per unit at the beginning of the period and the cost per unit at end of the period, which will give you the variance. This will also total the weighted-average cost for you, which is the total cost of goods available for sale weighted against the total units for sale. Ignore the ending inventory breakdown section at the bottom, as that is for production inventory.


Conduct inventory checks weekly, bi-weekly or monthly, depending on the size of your operation. This will verify that your inventory numbers are correct and will keep track of any losses through breakage or theft.