Counting customers in a Bar

Topic

New Reply

Hi All, totally new here, and almost new to excel.

I’m Managing a small rural bar and I want to have an easy way to analyze the number of customers per hour and on what hours / days are the most visitors coming to the bar.
This will initially be for my own records.
I’d lime to show the results of the visitor count per hour and per day in a graph of some sort just for easy viewing.

As I’m fairly new to Excel I’d like some help with the setup of the spreadsheet.
The info I will need is
Date (day of the week)
Time (between 12noon and 1pm for example, and each hour during the day)
Number of customers

Thats all I need really but im not sure of the setup I should use for dates and times to be able to show graphs later with the customers per hour on a specific day.

Thanks in advance for any help.

Reply | Quote

Replies

Hi

Welcome to the Lounge as a new poster!

I’ll have a think about this. What Excel version do you have?

zeddy

Reply | Quote

Version 2010 Pro.

Reply | Quote

Webcrafty,

Because I cannot attach a file in a PIM, I will attach it here. Here is a matrix with rows for each day of the year and columns with each hourly period of the day. Fill in the number of patrons for each hour period in the white area. The average number of patrons per specific weekday per specific hour will be averaged YTD. For example, as of March 12, there have been 11 Sundays and a total of 527 patrons for those days between 6PM and 7PM, the average will display at the bottom as 47.9 for that hourly period. The matrix will also sum across the number of patrons that visited for the entire day.

Based on the Avg patrons/hr/day, a graph visualizes the patterns of patron visits to your bar.

HTH,
Maud

46938-bar-chart3

46936-bar-chart2

Reply | Quote

Maud,

Having had these kinds of needs in the past, I usually proceed at first to set up things like you suggested. And then I find that I get burned when my analysis requirements go beyond what I thought of originally.

So I end up redoing the setup as a data base. In this case, I’d have a col for date, another for time/hour (maybe using a 24-hr clock for better sorting by hours), a 3rd col for #customers. If wanting to analyze customers by day of week across a year, another col could be added using a formula to get the day of the week. The analysis could be done by time of day across all days. Etc.

Then the analysis could be done by pivot table or by graph or by formulas. Additional cols could be added for things not envisioned right now (which I have finally learned will always happen).

The total number of rows for 1 year doesn’t seem so much 365×24 (or some smaller # hrs). Doing this might also make the data entry easier (rather than having to read down the correct col and across the correct row to enter the # of customers for that day/hour.

Just a thought while Zeddy is pondering a solution.

Fred

Reply | Quote

Jezzz Maudibe I wasn’t expecting you to go to so much work, this is brilliant.
I’m not altogether what FBERG is referring to but what you have done for me will satisfy my needs.

I know by reading Fberg’s post that there are probably a few tweaks that could be done but I’m grateful for your work.
If Fberg would like to do some more work on the file then that would be great too.

Thanks for all the help guys.

Reply | Quote

How do you know how many customers are in the bar at any one time? Do you count and then write the number down?

cheers, Paul

Reply | Quote

Its a small (ish) rural bar and since the downturn in Ireland the bars are not doing so good. The reason for this spreadsheet is to allow us to see at a glance when our best times are and therefore we will be able to determine when we should be open or closed. A lot of pubs in Ireland only open evenings or some only on certain days.
We hope to eliminate the closures altogether and run promotions and deals for the slow times.

Reply | Quote

Are you counting the right thing ?!

“Spend” might be more meaningful than “customers”

It might (depending on how you record payments) actually be easier to analyse when payments are made, and how much they are. If you are lucky the data will already be captured – till and card payments are usually time-stamped.

After all, you might not want to stay open when the drunks are all snoozing after a heavy evening session, but you might want to be open when a few people pop in for a lunchtime drink but don’t hang around.

“Euros per minute” rather than “bums on stools” ? :rolleyes:

Reply | Quote

Something to be said for monitoring of the number of patrons is the owner can cut overhead by reducing waiters, waitresses, bartenders and barmaids, cooks, etc as well as reduced food supplies tracking the hours of light periods and conversely when the bar is normally crowded.

Those “bums” as you call them are most likely faithful patrons, neighbors, and co-workers to many. So, a little respect for those who like to party hardy, hold down a job, raise a family of 8, and probably are a major contributor to webctrafty’s income.

Reply | Quote

Maudibe, “bum” means something quite different on this side of the pond.

http://dictionary.cambridge.org/dictionary/english/bums-on-seats

Reply | Quote

In this case, I’d have a col for date

Column B, its there

another for time/hour (maybe using a 24-hr clock for better sorting by hours)

Columns D1:AA2, that’s there too

another col could be added using a formula to get the day of the week.

Also in there, column C

Then the analysis could be done by pivot table or by graph or by formulas

Graph is on Chart1 sheet, analysis with formulas (D368:AA374) also included

The analysis could be done by time of day across all days

Columns D:AA Yep, at the bottom

Did you open it before commenting on it?

Maud

Reply | Quote

Maudibe, “bum” means something quite different on this side of the pond.

Perhaps political correctness should be observed as this is a global forum?

Maud,

Pardon my unusual sarcasm. Its been a bad week and it is supposed to snow 18-24 inches tonight (45-60cm for those across the pond)

Reply | Quote

Native English speakers across the pond tend to use inches, non-English speakers use metric. Just to make it more complex the English speakers also use metric sometimes, but like to resist using it for some inexplicable reason.

cheers, Paul

Reply | Quote

Most humble apologies to Fred, Martin, and Brownie for my abruptness. Not my usual nature.

signing off,
Maud

Reply | Quote

My apologies to Maud (one of the nicest people I’ve never met).

In the UK, “Bums on seats” is a colloquialism for “occupancy”.

http://dictionary.cambridge.org/dictionary/english/bums-on-seats

Reply | Quote

Hi Martin

..it also applies to passengers on flights, even in First Class.

Now, getting back to the bar thingy.
It’s taken me so long to ponder I needed a drink.
I think Maud’s solution is top class.
The only thing I would add (see attached file) is a ‘sparklines’ column (see column [AC] ), which is great for ‘snapshot’ analysis. If you haven’t used sparklines (available since Excel2010), I added a link (in the file) which should help.

Now, more importantly, what kind of a bar does Maud go to that is closed at 4:00AM????
In the Caribbean, when I ask when does the bar close, they usually say “November”.
..and no use relying on till receipts for timing/counting customers, as we run a tab and pay at the end, not ‘as-you-go’. Just saying.

zeddy

Reply | Quote

Hi Maud,

I will admit to not opening your file when posting my original comment but by going by your description of what’s in a row and what’s in a col. To me, your response even seemed to support your original description.

But now that I’ve looked at your original file, it is as I thought it was.

What I was talking about was something like Row 1 with the following cols and then entering the data for each day-hour in a separate row (versus a cell in a table as you have):
Date Weekday Hour #Customers
20170314 Tuesday 1200 3
20170314 Wednesday 1300 4

this would easily allow adding another column for amount spent. Maybe even another col for #personnel on duty, any promotions, etc.

My problem in doing any further work is that pivot tables and graphing are my weak points in Excel. But this seems like a “typical” data base kind of application.

Fred

Reply | Quote

Hi Maud,

I hope you aren’t buried in whatever amount of snow/units were being predicted.

I’m in NJ about 2 miles from the Atlantic and the snow turned to rain sometime over night (I got home at 1am when it began snowing – no I wasn’t at a bar, but turned to rain by the time I got up at 8am). The predictions for here were much worse.

So hopefully the predictions for you were worse than the actual snow fall.

Fred

Reply | Quote

I think Maud, Zeddy, RG, and surely others (including myself) would be happy to come there for a bit to help count. We could probably trade our time for a few pints!

Reply | Quote

I hope everyone is OK in New York.
Tropical one day, then snowstorms, what’s going on!
My friends in Manhattan are working from home today.

me, I prefer tropical!

zeddy

Reply | Quote