Scatter Plot Help

[Byrnsie]

Hi there,

Hoping someone can help with this, I'll try to keep it as brief as possible.

I have c.10k lines of a data extract from a CRM - each line is a sales 'opportunity', with data like company, value, product, sales stage, pipeline age etc.

I am trying to put together a chart to aid in forecasting based on pipeline age vs sales stage. I'd like to be able to paint a picture which says, with a 90%+ degree of certainty that sales opportunities over a certain age are unlikely to be won and should be closed off on the CRM system as dead opportunities.

I have snapshots from our CRM for the last 12 months so can put this together based on historicals.

How do I go about this? I've tried using a scatter plot with sales stage on the x-axis and age on the y-axis, but it's all over the place and not readable. From my read of the data, there should be a bell-curve shape on a scatter plot.

Any tips, suggestions, insights etc are welcome.

Thanks in advance,
Byrnsie.

[Vinodsralian]

Anytime adding entire data on the graph would not be of help. I feel you need to average it to Months/Weeks before you actually plot.

[Byrnsie]

Thanks Vinodsralian - beside the data dump, I've created a VLOOKUP beside each opportunity which gives pipeline age in months & weeks (using a '=TODAY()-"Creation Date" formula)

Chart is still all over the place...I've seen it done in a previous job but I can't remember how it was done.

[MrShorty]

IMO, the first step in understanding your query is determining if Excel is plotting the data correctly or not. When you say the data is all over the place, is it because Excel is plotting incorrect data, or is it plotting the data correctly and the raw data have a lot of noise in them? Is it a charting question or a question of how best to smooth the data?

[Byrnsie]

Hi McShorty - the more I think about it, the more it looks you might be right re smoothing the data.

How best should I go about that?

[MrShorty]

That's probably not necessarily an Excel question, since this question would apply if we were doing this in C or Java or Python or <insert favorite programming language here>. There are many different strategies and algorithms for smoothing data, from very simple to very complex. The choice of smoothing algorithm is often not a trivial or simple choice.

Probably the simplest smoothing algorithm is a "moving average". In Excel, this can easily be implemented using the =AVERAGE() function. Enter the average function in an adjacent column with an appropriate range, then copy the function down. The chart trendline feature also includes a "moving average" option.

Another common algorithm is to use regression (linear regression is easiest). In Excel, linear regression is best accomplished using the LINEST() function https://support.office.com/en-us/art...rs=en-US&ad=US You can either perform a single regression using the entire data set, or you can do a "piecewise regression" where you perform the regression on several subsets of the overall data set.

Off the top of the my head, those are the two easiest smoothing algorithms to implement in Excel. There are, of course, many other possible algorithms and variations. Care must be exercised in your choice of smoothing algorithm, especially since this is intended for forecasting/extrapolation, to make it reasonably accurate beyond the scope of the original data.