How To Create Budgets With Seasonality Impact Using LuckyTemplates

I’m going to show you something specific and quite advanced on budgeting through LuckyTemplates. We’re going to dive into how to create budgets with consideration on seasonal impact. You may watch the full video of this tutorial at the bottom of this blog.

This has great potential mostly because it paves the way for highly effective analysis. It also enables you to automate some of the insights that you might want to extract in the future. This is great if you don’t have unique budgeting or forecasting data and you need to create it from scratch.

Table of Contents

• Considerations On Budgets and Seasonality Impact
• Creating Product Budgets
• Conclusion

Considerations On Budgets and Seasonality Impact

In creating a budget, you can start off simply by finding the results from last year and then projecting them forward with an additional increase or decrease to those numbers. But based on what I’ve seen, heard of and been involved in, different organisations use a number of budgeting and forecasting mechanisms that are much more complicated.

Through LuckyTemplates, we avoid overcomplicating things like extracting forecasts, budgets and benchmarks. It’s all about weaving some logic into your formulas. 

With this approach, you won’t have to recreate data and reinvent the wheel annually, quarterly, etc.

Another thing about budgets is that usually they’re never uniform. You’re always going to have some sort of seasonality impact, which is fine in a lot of cases. 

You can get that seasonality impact from just looking at historical information. It should show what’s changing in your business. So that’s what we’re going to do with this formula here. 

Creating Product Budgets

In this example, I just want to revert back to the simpler version. If you want to project historical numbers forward, there’s actually a way in LuckyTemplates to do this almost automatically through table functions that we can implement directly inside of LuckyTemplates desktop.

I’m going to really focus on Product Budgets here to show you how to create budgets out of nothing or by using historical information. This will also help you make sure that you’ll have the impact of seasonality as you come up with the numbers.

Let’s start by getting the results based on monthly data. This is ideal for seasonality impact since it can consider elements like Christmas, summer, or promotions that you run over certain time frames.

Aside from going by month, you’ll also be doing it by product. Those two dimensions put together should give you your seasonality impact.

So now, I’m going to create a new table. Remember that the data here will not be from Excel or any database. We’re just recreating a table using a DAX formula.

I’ll start off with the ADDCOLUMNS function.

There are instances where using data from a single column is enough. In this case, I need both the month and the product ID. This is why I’m utilizing the CROSSJOIN function.

Note that we’re using four different table functions in a single formula here. But this is also why this formula is highly effective in getting all the necessary information into tables and formats you can analyze.

Going back to the ADDCOLLUMNS function, you’ll see that I used it to add data for 2018 Budgets.

From there, I used the CALCULATE function to count up how much Total Sales I made in 2017 adding in a growth of 7.5%.

Simply put, for every product across every single month, we are going to forecast or project a budget forward which is going to be the sales from the prior year plus 7.5%. 

Forecasting in LuckyTemplates: Compare Performance vs Forecasts Cumulatively w/DAX
Budgeting Analysis On Best & Worst Performers Using LuckyTemplates

Conclusion

Basically, that’s how you can automatically get some Product Budgets with some seasonality impact. You’re just picking up the sales a year before, but with added consideration on anything that could affect the numbers based on different seasons.

For example, your sales might go up when it’s summer in North America because you’re selling more goods to people on holiday. That impact is going to be showcased in these numbers.

You can even apply this to more complex situations as long as the concept is similar and you’re required to go and average up historical information over various years, quarters, etc. It’s just a matter of substituting a different element instead of just looking at the Total Sales.

Remember that initially, that additional element didn’t originally exist. We just added that in by creating a new table.

From there, it’s easy enough to visualize the data, make comparisons showing the deviations and outliers, find out reasons why there changes, and determine what is causing the impact we’re seeing in our data.

Enjoy this one!