Any excel experts around?

[Originalsauce]

Tiny bit of help please?

Imagine I have a 24hr shop. And people come in at all different times of the day.

Some of them buy things but some don’t.

I think that more people buy things at 10pm, but on my chart of ‘sales’ we have a peak at 4pm. How can I show that we might make more sales at 4pm but relative to how many people come in the shop we actually make more sales per footfall at 10pm?

I currently have all the sales plotted out and I have the data on average footfall per hour.

If you can make sense of this and help I would really appreciate it!

Can you calculate sales/customer through the door in 60 min or 30 min blocks.
Then plot that against time.

Is it not just the sales per the hour in question divided by the footfall for that hour?

Then youd be expecting that the sales per person between 10-11pm is higher than at 4-5pm?

Calculate average purchases per hour (would you want this in £, or in number of people who make a purchase?) You already have average footfall.

Columns of: time; average footfall; average purchases next to each other.
Then a scatter graph with time on the x axis. Plot both the average footfall, and the average purchases against time on the same graph.

This should visually show that the relationship of purchases to footfall is not entirely linear.

Or, you could take your average footfall and average purchases, and make another column that calculates either how much the average visitor to the shop spends (£purchase per hour divided by footfall).
Or that calculates what proportion of the visitors make a purchase (number of buyers divided by footfall. This will obviously be less than 1).
Then plot that column against time.

Does that make sense?

Yoid probably have to play around with it a bit, try different things to get what you want.

I would start with a scatter graph, with lines between the data points. But try other chart types too. I think I'm just biased towards scatter graphs because they are my go to!

I have never had to analyse retail data though!

What are you trying to 'prove' is greater Money spent at 10pm or number of sales at 10pm

For example it might be
4pm, 10 sales totalling £1
10pm 2 sales totalling £20.

If you're looking at number of sales, 4pm looks better.

If you're looking at total spend 10pm looks better.

Of your looking at money spent per sale on average, then 10pm looks better (£10 p/sale)

Why is footfall relevant in your scenario?

Or make a bar graph, with each bar representing a certain time unit (per hour for eg) and the total height of each bar representing total footfall. Each bar could then be split into "buyers" and "browsers".

To do this, make a column next to average footfall of average number of people who make a purchase (do you have this? Would be different to average number of goods sold as some people might but more than one thing!) Then average "browsers" = average footfall minus average buyers.

Plot as a bar chart with the times of day along the x axis.
If your assumptions are correct, you will sauce a bigger bar at 4pm. The bar at 10pm will be smaller, but the "buyers" will make up a larger proportion of the 10pm bar.

See not sauce!

Sorry it’s not an actual retail situation, I didn’t want to post the real scenario here in case my boss saw me and guessed I was seeking help with my project

In essence I’m trying to prove that whilst “sales” is highest at 4pm we also have more footfall at 4pm. Sales at 10pm are higher in comparison with footfall.

So we sold 200 units at 4pm against 1000 customers (A fifth of all customers bought something)

BUT

We sold 50 units at 10pm against 200 customers (A quarter of all customers bought something)

I’m usually quite good at excel but I’ve lost my mind with this today!

Thank you so much for all the suggestions I’m going to go and have another play now

Op if you want any help I can try and set up a spread sheet for you with formulas in.

I will have a go.

Pm me if you want time to try.

Are you counting actual footfall - so people in the door, or shoppers?
Have you not got average basket size -items and value?

Also are you could tong the value of the purchases

When do you need it done by?

If you are normally good at excel, but your brain is letting you down then I suggest taking a break from it.
I always find that when stuck on a tricky problem, doing something else (especially if its active) helps. Inspiration is most likely to strike me when I'm on a walk or a run!

So unless you're on a tight define, you might find that if you sleep on it, you will crack it in the morning.

Do you work for SAGE btw?
😉

Why don't you simply calculate the per person spend for a given hour. So have in 1st column the hours 2nd number of people "footfall" and 3rd total spend. In 4th column put =C2/B2 to give the spend per person. Copy the formula down and graph against the hour.

Are your "units' equal because in your retail analogy you would need to factor in money spent as well as number of items sold?

If the units are equal, I would plot a graph to show footfall with time, units sold with time and units sold per person (ie footfall) with time.

Just divide the first thing by the second thing. That will give you the sales per footfall, or whatever.

Is it a website?
Calculate conversion rate by hour?
Orders / visits

You could also use a ratio? So within the hour timeslot, the number of people who entered and bought against the number of people who entered and didn't buy. This only works if you have a count of 'sale' instances within the hour though, not just the total value of sales within the hour. But if you know that 200/1000 people bought at 4pm and 50/200 people bought at 10pm, your ratio would be 1:4 for the first slot and 1:3 for the second slot. Would that work?

Lolz I don’t work for SAGE

Anyhoo, yes a break is absolutely what I need! Thank you all, will definitely PM if I don’t crack it by the morning

My take on this would be what outcome would proving x create?

What do you want to prove?

Once you know what answer you want then we can work out the stats to prove your point 😂😂😂😂😂😂😂😂