hi guys welcome to the template on trend analysis in this template i want to quickly walk you
through the single steps that you need to take in order to customize the template to suit your needs
so as always let's start with a quick look at the different sheets as always we have a mapping
table now the mapping table this time is fairly straightforward what we need to bring in here
are products meaning in this template which has some pre-filled data in it we have four products
in total and about 14 placeholders now what you want to do is just update this section and
it'll translate automatically into the different sheets the version is now quite straightforward
basically what we're doing in the pre-filled version here is we're contrasting our actuals
versus the budget so there's always a budget for the units to be sold and the actual sales in
terms of monetary values in this case it's usd so what you want to do and this will then also
update the headers accordingly let's just update these two sections right here i've also brought in
a conversion factor so if you want to look at your data in thousands or even millions
just update this piece right here now if you leave this at one it'll just show the values as they are
showing them in absolute numbers now months as always are a bit tricky because you want to make
sure that these align with the regional language settings of microsoft excel and your windows
machine because that will allow you to use date formulas to retrieve the numerical values behind
months now this is very important because if we look at year-to-date values we want to be able to
actually specify the different months by means of numbers and i'll show you what i mean by that in
just a second but for the time being let's move on to the next sheet which is bridges for trends
there's not much to say about this sheet just don't change anything about it
because it's fully automated there's nothing you need to do about it this will just
update automatically and it's needed for trend calculations as we'll see in just a minute
now moving on to the next two sheets this is our actual data and what we have in here
are our products the time measures now they are always periodic we're just looking at one month
we have our budget and our actuals now very important about this is just make
sure you do not change the order of the first five columns and then as you update it with your data
just drag down these formulas f and g all the way until the bottom because this will later be needed
to populate the year-to-date columns a similar setup holds true for the units to be sold
however since we're not looking at any year-to-date values by means of calculations
we don't need to specify the month and year in here yeah so that is it for the data sheets let's
now focus on the analysis here on summary what you will find is just the full data
and it's all linked to the mapping table so whatever product you specify in your mapping table
you will find it right in here also notice that there are some groupings and if you close these
you can just hide them essentially now about this sheet there's really nothing you need to do
apart from updating the current month so in this particular case we're looking at the month of july
and if i were to change this to say may then you will see that my headers changed and also
my data down here let's quickly go back to july and let's take a look at what this
sheet actually provides us with now if i select july i will get the month to date data for july
that means i'm really just looking at the month of july as such we have some variance analysis
versus prior year and budget and we also have a year-to-date view now the year-to-date view
provides me with a view on the actual months to date so in the case of year to date july we
will be looking at the months from jan until july included again we have our variance analysis right
here and if we move one step further we'll see the full year view now the fourier view
gives me all of the data available for 2019 and 2020 i have data from january until december
for 2020 in here in our actuals i really only have data until july why do we only have data until
july well because that is as of now the data that we have available i specifically opted for this
kind of scenario because it's very realistic if you think about the business setting you
would be analyzing this on a monthly basis and you don't always have all of the information available
so whilst you may want to disregard the full year column right here because it's only going to give
you the same information that you will find in the year to date it's very important to look
at the year to go and to understand how much money you still need to make in order to meet the budget
for this we actually need to look at the averages right so if we look at the year-to-date average
this will really just take our year-to-date number and divide it by the number of months so in this
case seven the same one is true for our year to go months so the year-to-date number gives us the
numbers that we have completed already now for the year to go we still have five months to go
and for this we actually need to divide it by five so we're taking our budget it's a full year number
we deduct the year-to-date number and we then divide this by five to give us
an idea as to how much we need to make each month in order to meet the budget
now as such of course it's very important to look at the full year trend outlooks sometimes we
may be looking at our year-to-date view plus the so-called three-month rolling rate and i encourage
you to really just look at the formulas right here and understand how they're being calculated
because all the way until the right you'll be finding a lot of different analyses and
calculations but one key thing to bear in mind is that you do not need to change anything about them
some of them just pull actual data now in this particular case we're looking at march 2020 and
if you think about it we have some 12-month trend analysis in here so that's why we need
the bridges to feed our data in here because this is going to give us the last 12 months of data
and then this will be needed to calculate our trends now trends are really just built on a
forecast formula building on the last 12 months of available data we have this for the 12 months
for six months and also for three months however we are only looking at three months more
monthly averages in this particular case so as you see that throughout the sheet you have
plenty of different analyses that can be done i encourage you to look at the formulas but really
there's nothing you need to change about this template apart from updating the current month
the selection right here will also affect the individual analysis sheets right here so let's
just take a look at the chairs for example now in here you get the maximum level of detail
first starting off with units this is coming from the units sold sheet you get your
sales in kusd depending on your conversion factor selection and also an average price calculation
now the units down here will be shown by means of graphs right the same holds true for sales
and you see that the green chart is actually actuals then our gray one is a trend for six
months the blue one is for 12 months light green is the budget and the dark gray is the forecast
now the forecast is again built on historically available data taking into consideration the
trends that we've calculated now overall these analyses over here that you will find
are just really the maximum level of detail that you can find trend analysis
quarter and quarter growth you can look at the performance versus the prior year so really
just spend some time looking at these formulas and learn how you can leverage them for your analysis
now where does all of this data come from there's actually a piece within this template that is
hidden and this is right here so if you open this one up there's a detailed trend analysis behind it
which again you don't need to touch because it's going to update automatically
however what i can share with you is that there's a lot of let's say information that we've used
previously let's say the is for trends again we need to look at the last 12 months of historically
available data to calculate our trends all in all all these calculations will later translate
into the charts on the left hand side so you need this to actually calculate your forecast your six
month and the 12 month trend you have this kind of sheet for all of the different products available
now in our pre-filled data set we're looking at just four products however we can extend this
let's say if you bring in additional products if you just copy over one of these templates so
i'm just selecting the kitchens for example i'm holding down the control button i'm dragging it
over right here at the end now i'm just creating a copy of the template and let's say if i want to
change this from kitchen to well let's say chairs even though we have a chairs sheet already i can
either just type it in or i use the drop down list which refers to the mapping table you see
we have all the placeholders all until the end now let's say we want to update this for chairs
so i'm selecting chairs you'll notice that everything about this is changing automatically
so really this sheet or this template in general gives you the maximum detail in terms of trend
analysis looking at historical performance but also projecting it into the future
i do encourage you to spend some time looking at the formulas really understand what is driving
them and then you can start punching in your own data and start your analysis