Cascade Alliance

Technique Tuesday: Tracking Data Using Excel

Some may have noticed most recently we’ve been starting to highlight our members successes and sharing their content. For the next few weeks, Technique Tuesday will be written by a guest who is none other than Rebecca Currie, Finance Director at The Scrap Exchange in Durham, North Carolina. She has written a three part series on tracking data by using excel.  This informational/tutorial will be useful for those who don’t use a point of sale system but would like some sort of way to track their businesses data.

From Rebecca Currie:

We have had many discussions over the years about using a Point of Sale (POS) system, or some other more automated data tracking system for tracking sales information. We would end up in a loop about what features we needed or didn’t need, what it would cost, how easy or hard it would be to manage.

Finally I had a conversation with our retail folks about what the main benefits of a POS would be in terms of data tracking, and I realized that we should be able to get what they said they needed from our existing Excel workbook that we had been using for many years to track our bank deposits and the data we needed to enter into our bookkeeping system (QuickBooks desktop version).

We have two electronic cash registers (SAM4s) that are networked to each other so they consolidate the Z-tape for both registers at the end of the day.

We manually enter the data from the register tape into an Excel workbook. This system is decidedly low-tech. But if you stay on top of it—which you should, because you should be looking at all of these numbers on a regular basis—it is easy and takes not much time at all.

So the purpose of this post is to tell you about how we use Excel to give us a variety of actionable information about our sales.

Our workbook has a separate tab for each month (e.g.,  a worksheet for JAN, FEB, MAR, etc.). Each day of the month is a row; each register category is a column.

We have a subtotal column that gives the store gross sales (before discounts) and then two columns for totals — one column shows the total from the register tape (which, as noted, is a consolidated tape that combines data from both of our registers, and includes all of our income streams—creative arts programming, fundraising, and retails sales—anything that’s rung into the register) and one column shows the total amount of the day’s deposit, which consists of two credit card machines and the cash deposit that goes to the bank.

I added conditional formatting to the two totals so that if they are not equal, they show up in red.

This makes it immediately obvious when something has been entered incorrectly, or if there is a discrepancy with the deposit numbers that were recorded, in which case we can go back to the paperwork and try to figure out what happened.

Here is the formula I use for the conditional formatting:

In the example above, the first total is in column AM and the second total is in column AR, and my cursor is on row 4. So when I look at the conditional formatting rule, it says that it should use a formula to determine which cells to format, and the formula is that  if AM does not equal AR (=AM4 <> AR4) , then format the text with the color red. (You set the formatting by clicking on the “Format…” button.)

You can set that in one field then copy it down the column. I set it on both columns so they both appear red, which makes it easier to see that it’s a problem with the matching, you can easily see that the two numbers that are in red are not equal to each other.

In addition to the worksheets for each month, we have a worksheet called Totals which shows us the daily average — all retail register categories plus discounts plus any over/under.

 

At the bottom of the column you can see the total sales for the month, the daily average (which is a running total for the month—you can see at any point in time what is the current daily average for the month), and the average transaction amount, which comes from the store sales total for the month divided by the number of register transactions total for the month.

Because our sales on Saturdays are so much higher than on other days, we highlight those cells in yellow to make it easy to scan and see how the Saturdays have been. In addition, we have an event that happens every Third Friday (our monthly gallery opening), so we highlight Third Friday in green.

The numbers on the Totals worksheet are calculations that come from the month sheets, so there’s no data entry involved. This is what the background looks like:

Note: To see the formulas in your worksheet instead of the data, on a Windows computer, press CTRL-~. Press it again to go back to the data; it’s a toggle.

(This may seem to be in the weeds but I want to point out that one of the reasons we put CLOSED in for days the store isn’t open and we have no sales is so that the average is calculated based on days we are open rather than all days in the month. If you put a 0 in that field, you will get a different average than if you have nothing or text.  See below for the difference in average depending on whether you include text, a blank cell, or a zero.

We want to know what the average is for days we actually sold things, not for every day of the week regardless of whether or not we were open.

If there’s anything especially noteworthy about a day, we include it in a comment, which is indicated by a red triangle in the top right corner of a cell.

 

Stay tuned for Part II coming next week Tuesday!