hi guys and welcome to the template on variance analysis in this quick video i want to walk you
through the steps that you need to take in order to customize the template so that it suits your
needs so first let's take a look at the different sheets that we have we have actually two support
sheets mapping and data next we have the sheets that actually show us what we want to analyze
the executive summary the sales performance as well as the monthly evolution so let's take
a quick look at each of the sheets and i'll show you what you need to do on each of them
starting off with the mapping now as described in the first introduction sheet you have a month
to date as well as a year to date view just leave them there do not delete them you can specify the
year of your data so in my sample i'm actually using data coming from 2018 and 2019 but you can
of course change that to suit your needs all of the inputs here will later translate into various
drop down lists next you want to start off with the months now here you need to make sure that
this actually reflects your regional language settings in my case it is set to american english
but of course depending on your language settings this may change just make sure that this ties in
now in a variance analysis we're always comparing two different versions in my sample
we're comparing budget versus actuals so essentially we're setting a target
or a budget for the year and then for each and every month we're contrasting
that against the actuals i'm also putting in a conversion factor so depending on how large
the numbers are that you're working with you may want to divide them by 1000 in this case
because that just ensures legibility now in the last step here in column e you want to bring in
the different products that you're selling because this will again later translate into different
drop-down lists now i would encourage you to only change the green cells because totals
and the full year will always be needed so now let's go on to the next sheet data
this is where i put all of my sample data you see the year in the version aligns with the mapping
period product and sales that is actually the data that i want to display in different charts
and tables now the last column column f is just a formula it essentially picks up the period here in
column c and translates it into a numerical value for example january will always be 1.
now change the period product and sales and if needed also adjust the year-end version
because all of the inputs here combined with the mapping will later translate
into the three analysis sheets so let's take a quick look at the executive summary
the executive summary allows you to analyze and visually display your data
if we zoom in on one of them
we're looking at all products in june 2019 and i can change this whether i want to look
at the monthly date or year to date you see this will update automatically as well as the title
moving on to other charts essentially this is the same kind of view but each and every
time you will have another drop down you can also specify the product that you want to use
you can specify the year and each and every time as you see
the graph will adjust automatically now going on to the sales performance
what we can select here are different versions again so we can contrast the actuals of september
the budget of september
and then the last step let's say we want to look at actuals in september again now of course
this will show zero because we're looking at exactly the same data but if i now were to
change this to let's say october you see that my data updates automatically now in our sample we
are only looking at four products in total but of course we have groupings right here
that allow you to expand this all the way so as you can see here these are already pre-filled
while they're still blank they're still referring to a cell here on the mapping sheet
so let's take a quick look at e11
e11 is right here in my products column so whatever you update here will then later update
over here as well of course if you don't need them you can just hide them it doesn't matter
and if you were to extend that range even further let's say you need additional rows
to reflect all of your data i suggest you simply insert additional rows you copy down all of the
formulas and you're all set so make sure that every single time you update data here in column
a you're also accurately reflecting the references to the mapping sheet it must be in an ascending
order in other words it should reflect whatever you've put in here now the last one is actually
the monthly evolution so in the monthly evolution i'm able to select again a year and a version so
i'm just saying show me 2019 and now i get the breakdown by product and the different months
i see some trend lines over here so you can see that there's a bit of seasonality in the data
but then of course you also see all of this translated down here at the bottom
because this neatly shows you the contrast between your actuals
and your budget essentially the two versions that you're looking at
down here you even see the variances in percent and again this will update automatically every
single time now let's see what happens if i change this parameter over here
rather than looking at 2018 i will look at 2019 then again my table here will change
so the contrast i'm making is i'm actually comparing this table versus this one
so the nice thing about this is i can contrast actuals 2019 versus the budget 2019 and all of
this will initially translate into my chart down here and even show me the percentage variances
so anything that is green means that our actuals are actually higher than the budget
anything that is red shows we're below budget as you can also see right here
so guys this is it with the variance analysis i find it super useful i use it in
all different kinds of situations and i hope that this template will be of use to you