How to best design your model to facilitate variance analysis.

sagarkpr

I have a requirement to create a model that can facilitate variance analysis like Compare CY Sales, with PY Sales and show % along with doing the same for CY Sales vs Budget.

What would be the ideal model design? Should I be create versions or should I create line items for CY, PY and Budget in the module and then calculate variance.

Would appreciate any tutorial or article on this?

AjayM

Hi @sagarkpr ,

If it is always about variance between CY and PY (be it Sales, Budget or Sales vs Budget), I recommend lineitems where you can simply calculate variance for every year with respect to its previous year. Another advantage with lineitems is to show both absolute variance and percentage variance in respective line items.

You can go for versions or a list that represents versions, if your requirement is not confined to a small set of KPIs. In other words, if you foresee the need for versions at multiple places within the model, and not just for variance analysis.

Cheers!