USA Excel Question - Comparing Current Period to Prior 12 months


Joined
Dec 6, 2019
Messages
11
Reaction score
0
Country
United States
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!
 
Ad

Advertisements

Fidget

VIP Member
Joined
Jan 6, 2013
Messages
692
Reaction score
118
Country
United Kingdom
=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:

1599884734330.png


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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top