Need to determine an ideal ratio

[sbrien]

Hello all,

I'm haven't played with stats in a while and now I'm trying to determine the ideal traffic/labor hour in order to maximize revenue/labor hour. My data looks like this:

Excel 2010

	A	B	C	D	E	F
1	Date	Traffic	Revenue	Hours	Traffic/hr	Revenue/hr
2	7/1/2012	144	$ 2,072.16	15.45	9.320388	$ 134.12
3	7/2/2012	163	$ 7,395.98	36.85	4.423338	$ 200.71
4	7/3/2012	142	$ 3,802.03	33.52	4.236277	$ 113.43
5	7/4/2012	94	$ 3,020.40	22.58	4.162976	$ 133.76
6	7/5/2012	144	$ 4,843.29	31.82	4.525456	$ 152.21
7	7/6/2012	174	$ 8,885.81	29.13	5.973223	$ 305.04
8	7/7/2012	177	$ 3,349.87	32.96	5.370146	$ 101.63
9	7/8/2012	116	$ 7,811.72	24.28	4.777595	$ 321.73
10	7/9/2012	160	$ 5,116.48	30.33	5.275305	$ 168.69
11	7/10/2012	136	$ 1,617.67	30.52	4.456094	$ 53.00
12	7/11/2012	153	$ 6,816.25	39.5	3.873418	$ 172.56
13	7/12/2012	122	$ 1,972.75	28.03	4.352479	$ 70.38
14	7/13/2012	203	$ 7,310.88	27.27	7.444078	$ 268.09
15	7/14/2012	165	$ 6,239.02	29.18	5.654558	$ 213.81
16	7/15/2012	126	$ 2,652.57	23.46	5.370844	$ 113.07
17	7/16/2012	195	$ 10,033.34	31.75	6.141732	$ 316.01
18	7/17/2012	147	$ 4,593.56	23.14	6.352636	$ 198.51
19	7/18/2012	156	$ 5,319.05	44.69	3.490714	$ 119.02
20	7/19/2012	130	$ 1,801.60	33.91	3.833677	$ 53.13
21	7/20/2012	220	$ 5,415.52	28.21	7.798653	$ 191.97
22	7/21/2012	146	$ 12,727.49	23	6.347826	$ 553.37
23	7/22/2012	110	$ 8,519.18	14.93	7.367716	$ 570.61
24	7/23/2012	166	$ 5,006.82	45.19	3.673379	$ 110.79
25	7/24/2012	166	$ 8,823.67	36.13	4.59452	$ 244.22
26	7/25/2012	152	$ 4,722.04	46.63	3.259704	$ 101.27
27	7/26/2012	140	$ 4,136.81	28.61	4.893394	$ 144.59
28	7/27/2012	162	$ 3,711.42	30.77	5.264868	$ 120.62
29	7/28/2012	162	$ 4,133.91	22.66	7.149162	$ 182.43
30	7/29/2012	137	$ 1,657.63	28.77	4.761905	$ 57.62
31	7/30/2012	127	$ 4,590.15	28.32	4.484463	$ 162.08
32	7/31/2012	100	$ 1,319.93	21.36	4.681648	$ 61.79

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

I don't know if Excel or Analyst Toolpak has the firepower for this or if I'll need additional tools. I appreciate any direction you can give me on this.

Thanks,
Sam

 

[Jerry Sullivan]

Hi Sam,

I'm not an expert at statistics either, but since you haven't had any replies after 36 hours, I thought I'd try to help.

Below is a Scatter Chart showing the data with a trend line that attempts to fit a polynomial equation.

Hopefully this gives you a better visual understanding of your data.

If nothing else, it gives your thread a bump that might catch the attention of someone more knowledgeable on the topic.

 

[energman58]

I think you need to be a bit more precise in what you are trying to do. Is this a queue problem (how many staff do I need) in which case you need to say what happens to the traffic you cant handle and how traffic turns into revenue based on the staff hours.

If what you are asking is how do I maximise profits then that is probably a linear programming problem. In which case Solver (a toy LP that is given away with Excel Analysis ToolPack) may be man enough for the job if your problem is really simple but once you get to most real world problems you need either to buy a better version of Solver or one of its rivals (What'sBest is my favourite) which are fairly pricy or use one of the open source solvers that are kicking around the web - I have used lpsolve which someone has written a dll version of which allows you to use it with VBA and seems to work very well but as it is open source you need to put in a fair bit of effort to use it effectively

HTH

 

[sbrien]

Thanks Jerry. Do you know if there is a function to obtain a "best fit" polynomial equation given a a set of data? Although the data is somewhat random, I've got about 2000 stores I'd like to run this for and plotting a graph for each one isn't feasible...

-Sam

 

[sbrien]

HTH,

Thanks for the insight. I don't think my bosses are interested in a sophisticated answer that accounts for all the what ifs. A best guess scenario using a reasonable trend seems to be sufficient. Thanks also for the insights into various Solver alternatives. I'll take a look, though the learning curve likely won't fit with the quick turnaround on this project.

Cheers,
Sam

 

[Jerry Sullivan]

Sam, The more data that you have, the more likely you'll be able to see a meaningful correlation for just the two factors charted.
So doing a similar analysis will data from all 2000 stores will have some benefit, though it won't control for factors that differ between the stores. The 3 outlying points (above $500/hr and traffic greater than 9/hr), are skewing the trend in this sample.

My understanding is that applying a trend line with the polynomial option attempts to find the "best fit" for the order number selected.
I don't know of an automated way in Excel to look at a set of data and determine which trend/regression type is the best fit.

It makes sense that your stores will have a point of diminishing returns in which increases in traffic will not yield proportional increases in revenue for a store, so polynomial could be a better representation of the trend than linear.

As noted, I don't have any expertise on this topic and I'd be happy to learn more on this topic from someone who does.