Help with Formula to measure performance

Dear All

I appreciate your help with the formula for the following.

I have a table of scores for department per each Month
Period
1 10%
2 25%
3 20%
4 15%
5 30%
6 50%
7 12%
8 22%
9 40%
10 44%
11 56%
12 33%

I need a formula to show me how well this department has performed over the past 12 months , taking into account the fluctuations over time .

Any help with the formula is appreciate it. I don't want to use P12 result -P1 result as it doesn't really show the ups and downs each month

Thanks

Thanks, does this give their improvement/decline over the 12 months from start p1 to endpoint P12?

not sure how to do this. I urgently need a formula that will show me the movement (whether this department has done good or not) from the result in P1 and their performances over the 11 months to what they finished. e.g they started on 10% and finished on 33% but during the 11 months there was some fluctuations so basically how much they have improve (if any) during the 11 months since the start.

I really appreciate your help with this

I hope I am making sense, I have used Average Annual growth formula but not sure if that is correct. I just want to see over the past 11 months how much this department have moved, would something like Calculating growth will do and so what formula. Or any other way.

This is exactly what I needed, can I then do an average of the changes or does that not make sense?

Basically based on the table above can I say the department has improved by 23% compare to P1?

Thanks for your help. I know , im not comfortable with averaging the averages, I was just wondering whether there is another way to show improvement or decline. I was trying to use something like XIRR function or CAGR formula but not sure if this will give me the right information.

Based on your table above will it be right to say the rthe department has improved by 2% growth over the 11 months?

Thanks for the help. I do really appreciate it. I am just using an example data which I need to produce a report for. I have sales figures of various departments and need to show which department over the last twelve months has performed best, based on their monthly trade figures. The example I used might have not been good but I wanted to see is formula to just take the value of P12-P1 would be sufficient. I suppose you are right this is more of a maths and Stats question than excel, but I was hoping if someone know a formula or a function which can do this.

Thanks for your time with this and I really appreciate your help

Thanks sourabhg98 I thin I thatceilk do it as a simple illustration. I appreciate everyone's help.

Agree with what MrShorty mentioned above.

Here's one suggestion on how I'd do it. Disclaimer: there's a lot of ways to interpret data and your intended end-game (the message you want to show) will have a great impact on how you interpret them. Also note that there are many method of doing this and they may result in a different solution.

Say you want to show growth, with the interest of keeping it simple and using what Excel has:

1) Plotting a simple chart would immediately show you what sort of trend you're looking at. I used a line chart because that tells the story of movements through time.
Chart1.jpg
which somewhat shows an overall rising trend.

2) Then you'd want to quantify this to some degree. For this, I used trendline. Click on the chart, at the very top, select Layout, and click on Trendline - Linear. With the chart still selected, under Layout tab, on the far left, there's a drop-down box. Click that and select "XXX-Trendline X", then Format Selection. First window you see, at the bottom, check "Display Equation on chart"
Chart2.jpg
On mine, it shows y = 0.0267x + 0.1241 - which "roughly" means, for every month, you expect a growth of 2.67% growth.

Depending on who you're selling the story to, you don't always have to quantify volatility. Unless you're explaining to very technical people (those who do/rely on quantitative analysis). To explain volatility (in layman's term), without knowing the background of your line of industry, to me it seems like there's a very clear seasonality trend. Firstly, the start of each quarter will show a markedly low performance (especially Month 1, Month 4, and Month 7; Month 9 to some degree). Secondly, looking at each quarter, (P1-P3, P4-P6, P7-P9, P10-P12). Again, from the chart itself, it's quite clear P10-P12 is the top performer. Christmas perhaps? Or something people get during winter?

Dear quekbc thank you so much for your advise. This is absolutely great and has helped a lot. I now know what to do. Thanks again to you and everyone in this forum which has helped me tremendously in the past.