Since their inception, spreadsheets have been a wonderful tool for all kinds of home and business applications. But they have their limitations. The problem with trying to use a spreadsheet for your open to buy is that there isn’t options for enough variables.
Open to buy is purported to be very simple as illustrated on a web site for a major market merchandise mart. They suggest you take your beginning of the month inventory, subtract your retail sales, add your purchases, subtract your markdowns and that will indicate the end of month inventory. And they are correct, but is that really usable information?
You can put this information on a spreadsheet and save yourself some time, but when you consider that even a small store will have about 20-30 merchandise categories and then you have to consider the flow over 12 months and then you need to deal with turn rates for every department that will vary each month. Finally, the determination of the open to buy amounts must be configured so that any negative open to buy amounts are taken away from preceding positive open to buy amounts, it gets pretty complicated.
In truth, you need an open to buy system that works within each department separately and take into account planned sales and turnover. Sales will fluctuate each month and so will turnover (the annual turn rate is just an average). I haven’t seen a spreadsheet that accounts for turnover and turnover is the key to making money in retail. The more you turn, the more you make!
Trying to do your open to buy (the most important merchandising information you can have) on a spreadsheet is simply false economy. Sure, spreadsheets are very available and many are free, but will you save enough to cover your markdowns when the numbers are not accurate for your store?