USA Excel Question - Comparing Current Period to Prior 12 months

[FooserX]

I know this is an excel question, but it's also accounting related, and I can't seem to find anything on the web yet for what I'm trying to do.

We have 200 accounts to analyze. Most are small, but because of that...comparing current month to prior month isn't always what's needed.

I'm trying to determine if there is a way to compare current month balance to the average of the prior 12 months...to determine if it's reasonable, or if it needs to be analyzed.

I'm thinking Standard Deviation should come into play here, but I'm not quite sure how the formula works.

Does the current month fall within the normal standard deviation of the prior 12 months?

Does this make sense?

TIA!

 

[Fidget]

=STDEV.P(range) will give you the standard deviation for month end balances over the last 12 months, but you'd need to do it for individual accounts. A pivot table would do it all in one go if the dataset includes all the accounts and balances at each month end for the last twelve months. Then you could just add the STDEV.P formula.

I'd probably just do it based on a comparison of the last 12 months average against the current balance rather than full on standard deviation, and just use conditional formatting to highlight a +/- % increase or decrease against the average, so you'd have something like this:

That way you can change the % value in cell B1 and the highlighting will change accordingly. You can, of course, tailor the conditional formatting to suit what you want.

 

[FooserX]

Thank you so much, Fidget