Trend Analysis

[AnnieGr]

Hi

I think I have created my spreadsheet incorrectly as I am trying to create a trend analysis comparing one financial year to the previous year, but also over a period of 12 months (hope that makes sense. I have tried the Insert Line Chart and that's having a fit

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Jan 2017[/TD]
[TD]Jan 2018[/TD]
[TD]Feb 2017[/TD]
[TD]Feb 2018[/TD]
[/TR]
[TR]
[TD]Person 1[/TD]
[TD]£20,000[/TD]
[TD]£25,000[/TD]
[TD]£10,000[/TD]
[TD]£5,000[/TD]
[/TR]
[TR]
[TD]Person 2[/TD]
[TD]£85,000[/TD]
[TD]£15,000[/TD]
[TD]£87,000[/TD]
[TD]£25,000[/TD]
[/TR]
[TR]
[TD]Person 3[/TD]
[TD]£4,500[/TD]
[TD]£25,000[/TD]
[TD]£12,000[/TD]
[TD]£32,000[/TD]
[/TR]
</tbody>[/TABLE]

Person 1 January to February 2017 dipped in sales
Person 1 January 2017 compared with January 2018 was up

Its basically two sets of data a comparison to last year month/month, then over the year of 2017?

I am trying to build an overall view of sales against the sales person and how they are performaing month against month and then year against year, hope that makes sense

 

[Gerald Higgins]

What exactly do you need help with ?
It's not clear (to me) from your OP, what exactly you are having difficulty with.

 

[joeu2004]

I'm afraid I don't have the time now to look at details and offer turnkey suggestions. But some thoughts for your consideration....

1. Do not use the trendline of Line Charts per se. They always use x=1,2,3,.... instead of the actual values of the x-axis. Use the trendline of XY Scatter charts (with lines, if you prefer), if you use trendlines at all.

2. Be wary of using high-degree polynomial trendlines to predict future (or past) data points. A 6-degree polynomial trendline will fit 7 data points exactly. But it is unlikely that the past/future behavior of such a trendline fits the pattern of your real-life data.

3. If you copy-and-paste coefficients from the chart trendline formula, be sure to format the trendline label to display "sufficient" precision in order for the Excel formula to accurately calculate trendline data points. It is difficult to say what is "sufficient". It is unlikely that the default precision is "sufficient". I use Scientific format with 14 decimal places. That displays 15 significant digits for all coefficients, regardless of magnitude. That is the most precision that Excel will format, even though the actual value might have even more precision.

4. Usually, it is better to use LINEST to calculate trendline coefficients to their full precision directly in Excel, instead of copy-and-pasting from the chart trendline label. However, how to use LINEST for that purpose depends on the trendline type that you choose to use. Let us know.

5. Be wary of using any "trendline" formula (including LINEST results) to predict sales or spending patterns. They are typically seasonal in nature. If possible, I use year-over-year monthly data to determine the monthly growth rate, as well as other future goals or knowledge of future changes. However, that does require that I have 24 months of past data to work with, which is not always available.