Template for bar stock accounting
I am currently serving as one of the bar managers for the student-run bar at my Oxford college. We needed some new stock-keeping books, so I developed this system after reading online about some best practices. I wanted to share this template I made in case other bar managers need a similar Excel sheet and don't want to reinvent the wheel!
The basic idea is to record a stock inventory at the beginning of a time period, to note any purchases made throughout the period, and then to record stock at the end of the period. The sheet calculates the "Usage Cost" of the goods purchased as well as the expected revenue from the sales. For each period, use a new Excel sheet leaving the basic information (in green) unchanged. Annual totals can then be calculated across sheets in the workbook.
The Excel file contains a template, a completed example (screenshot below), a example of annual totals, and a calculator for adjusting the wholesale price for an item when some of the item is still in stock at a former price.
Let me know if you have any questions about how to use the sheet! I hope it's helpful.