Friday, May 30, 2008

Seminar in Madison II

Continuing with my series of electronic resources for game store owners, I'm going to discuss the lowly spreadsheet. Some of this is pretty dry, so I'm not sure how much to go over and how much to provide in a handout.

---

The point-of-sale machine can provide lots of reports and raw data, but there are a couple of processes that it does poorly, or it only performs them with an expensive software add-on. The most important of these reports is called "open to buy." It's used to budget your inventory purchases.

Open To BuyThis is the term used to describe how much money you have in your purchasing budget for spending on new inventory. Most point of sale software can't provide this, so you need to make a spreadsheet for your budgeting.

Starting out, I suggest you make a simple "open to buy" spreadsheet. As you get used to the process, you might pull out one department that's doing really well. For example, your Games Workshop sales might dominate your store sales and you might want to track it separately to keep it from eating into your general purchasing budget. A more sophisticated spreadsheet can track purchasing dollars by department, which avoids having your inventory involuntarily shift between departments. I've allowed my store to grow more organically, with a generic open-to-buy procedure, but you may have other plans.

Below is an example of how my Open to Buy spreadsheet works using numbers from my first year in business:

Starting Budget:

$1,000.00

DATE

SALES

COGS

AVAILABLE

PURCHASES

BALANCE

27-Feb

$358.15

$208.59

$1,208.59

$201.00

$1,007.59

28-Feb

$543.69

$255.59

$1,275.96

$1,193.30

$82.66

1-Mar

$334.35

$194.12

$286.49

$-

$286.49

2-Mar

$690.33

$415.53

$722.79

$500.00

$222.79

3-Mar

$597.10

$339.16

$578.91

$901.44

$(322.53)

4-Mar

$453.38

$246.19

$(64.03)

$94.61

$(158.64)

5-Mar

$391.26

$212.69

$64.68

$-

$64.68

Sales. You don't need the sales category, but you can use it to come up with other calculations in your spreadsheet, like your cost of goods percentage. I track my overall sales using this spreadsheet, including year over year comparisons.

As a side note, remember not to count gift certificates as sales when you sell them (only when redeemed), otherwise they get booked twice. Point-of-sale machines don't understand this. My sales category includes daily sales, minus gift certificate sales (their own department).

Cost of Goods. This number should be provided to you on your POS end of day report, also known as the Z-Report. Some store owners use a flat rate of 60%, but I think this is too inaccurate and puts additional money into the purchasing budget that doesn't belong. Your end goal is to have a 60% cost of goods that includes credit card fees, shrinkage, shelf worn items, take homes, etc. However, if you don't have a POS machine, calculating the actual COGS is impractical. If you want to use a flat number, try 54%.

Available. This is how much money you have to spend on new inventory. It's calculated by taking the balance from the day before and adding the cost of goods for today.

Purchases. Here is where you put how much money you've spent on inventory that day.

Balance. This is your actual balance, taking into account all the other variables. You want to keep this to as close to zero as possible. You might want a surplus if you're saving up your money for a big release. You might run a deficit if you've just had some big releases that you couldn't budget for.

The Goal of the open-to-buy process is to carefully track your inventory purchases to avoid extremes. If you buy too much, you'll lose money that could be profit or used elsewhere in your business. If you don't spend enough money, you can starve your store of new inventory and drive away customers. This assumes you're not in a growth phase, expanding your inventory as you move forward. If you are in a growth phase, you can create another field in your spreadsheet where you add in new inventory capital, so you can grow in a measured rate.