This isn't so much a "how do i excel" question. Rather I'm trying to figure out how best to (or if it's even feasible) display this data set I have on one chart. See my attached example.
So basically what we have is 3 values gathered on a monthly basis...
"# of leads" is pretty self-explanatory -- if our tracking software registers that we got 30 phone calls in a certain month from a specified source, then we have 30 leads for the month
"Closed Won" is a profit value. If we create 10 quotes for a calculated value of $500 profit each, and then the customer ends up purchasing that order as quoted, we have $5,000 of "Closed Won" profit
"Closed Lost" is the profit value of a quote that ultimately ends up being rejected by the customer. If we quote a guy for an order that would have produced $500 in profit and he ends up saying no thanks, then that's $500 in the "Closed Lost" column for the month
The thing is, it's easy to create a nice chart showing how "Closed Won" and "Closed Lost" interact over time. It's also easy to show how '# of Leads' flows up and down from month to month. What my boss asked me for - what I don't know how to convey - is a chart that shows all 3 values on one neat chart showing how Closed Won/Lost values may be effected by a growing or descending # of Leads per month. Obviously Chart 1 is worthless, and Chart 2 which uses an axis with a log scale isn't much better. The best method I have thought of would be to multiply the # of Leads by a certain value so that it appears close to the overall values of Closed Won/Lost and the fluctuations are still proportionately accurate, but I'm not in love with this solution.
So if anyone has any great ideas I would appreciate your input. It doesn't have to be a line chart - could be a bar graph or anything else as well. Also if you think it's worthless trying to chart numbers in the tens with numbers in the tens of thousands, feel free to speak up!
Bookmarks