Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals brought to you by CPA Australia.
Neale Blackwood CPA:
About the report template and using dynamic array
Welcome to the Excel Tips podcast. My name is Neale Blackwood, and in this episode we're going to look at how we can automate a dashboard using Excel's new dynamic arrays. This is the third in a series of three podcasts covering automation of reporting in Excel using dynamic arrays. The first looked at a revised 12 month report, the second one at a more typical month and year-to-date report. And in this episode, we're going to look at some of the things you need to consider in creating a dashboard using Excel's dynamic arrays.
About dynamic arrays
Now, dynamic arrays are a new way of calculating in Excel. Basically, the formula can spill across. And by spilling, I mean the formula is in the top left cell of a range and it will populate the other cells in the range with values. But those cells, those other cells, do not have any formulas in them. They are empty. But the formula spills to populate them. And that can spill down, it can spill across, and it can spill two dimensions.
Dynamic charts and dashboards using dynamic arrays
This podcast is going to focus on the structures and the layouts you can use that make creating the dashboard easier. The advantage with using dynamic arrays for dashboards is that you can create dynamic charts. So as extra data is added to your file, the charts can automatically extend to include that new data. Now, I do highly recommend checking out INTHEBLACK article on this and also the companion video, and there's a companion file. The companion video goes into a lot of detail about all of the formulas that we used. The article covers creating the charts. And in this podcast, I just wanted to cover sort of everything else in terms of just general structures. I won't be going into any detail on formulas or functions, but just generally talking about the structures that you need that work best with dashboards.
Variance charts and budget comparisons
So there's nine charts created. There's three sections, and each section has three charts in it. Basically, the first section looked at creating a variance chart. So a variance chart is a really good way to analyse actual and budget data. Instead of plotting the actuals and the budget together, what you do is you plot the variance. So there's a column chart for the variance. So above the line is a positive favourable variance. Below the line is a negative unfavourable variance. And there is also a line chart included. And the line chart covers the year-to-date variance. So in one chart, you can have both the monthly result as well as the year-to-date result at that month. And as I mentioned, it's really good for budget comparisons. The dashboard itself had the ability to compare against budget or prior year.
The second section of the dashboard was a 13-month rolling chart. The advantage with using a 13-month chart is that the same period last year is plotted as well as the 12 months since then. So there's 13 points on the chart, so you can see where you came from last year and how you've performed in a whole year up until the current month.
The last section of the dashboard was just a straightforward column chart with a trend line on there. So that allowed you to select a number of months and see the last 6, 9, 12 months listed with a trend line added to the column chart. The three measures that were plotted on the dashboard were revenue, profit, and profit percentage.
Building the tables and chats
Now, when you're creating a dashboard, I highly recommend you use a workings section. Having a working sheet can make the model a little bit more transparent. When you have workings, you can build up structures in the sheet to make it easier to follow the data flow. You can set up the calculations so that you might have the detailed and then the summary. And in that way, you can see where the data is coming from, which helps the user. It's also tends to allow you to create shorter formulas. In general, the shorter the formula, the easier it is to understand. And in this case, we had an actuals and a budget sheet. So the actuals were listed in a data layout, so it was a vertical sort of data layout, whereas the budget was a typical budget layout where you had the account information on the left and the months of the year going across. Now, whilst that's easy to review, it's actually hard to use to report on, but you'll see in the companion video how I get around that issue.
Jacqueline Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show, INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.
Neale Blackwood CPA:
So in the working sheet, we summarise all of the information at account level, and that's for the actuals and the budget. And so there's a detailed section, and then there was a summary section. It's actually the summary section that we're going to use for the chart. I also recommend having a dedicated sheet for the chart data. So in that sheet, that's where you build the tables that are going to drive the charts.
There's a chart data sheet in the companion file, and that's where we create all of the formulas that ultimately drive the final charts. The beauty with doing it this way is that you can keep all of the chart data together in one place. I also build the charts in the chart data sheet and then copy them to the dashboard. That way you've got a copy of the chart with the data as well as on the dashboard. So if the dashboard gets corrupted at any point, you can then just do another copy that fix the dashboard.
The beauty with dynamic arrays in terms of creating dynamic charts is that you now have the ability to build a spill range that can then be the data source for a chart. And when that happens, that automatically makes the chart dynamic. So as extra data is added, so in the companion video, we add February's data. We start off with January data, and then we add in the extra data for February. And in just adding that data to the actuals data set, the charts automatically updats. So all of the dashboard automatically updates to now work with the February data.
Now, there's two functions that work really well in building this spill range that becomes the data source for the dynamic chart. They are VStack, which stands for Vertical Stack and HStack, which stands for Horizontal Stack. So VStack is the easiest to understand because you're basically just adding one table underneath another, so you are stacking them. It's like you are appending them if you work with data.
In terms of HStack, that's basically joining columns together. And so the solution for this file joined up a number of rows to create a listing of all the values as well as the date at the top. So the VStack did that, and then the HStack added the label columns on the left-hand side. So a fairly short formula built of the two-dimensional range that became the data source for our dynamic charts. Two of the sections on the dashboard were dynamic. So the variance analysis, that was dynamic. And also where you could choose how many months to report on, that was dynamic. So the section in the middle, which was the thirteen-month report, wasn't really dynamic in the way that it's always reporting 13 data points. What was dynamic is that the months that were being reported on does vary based on whatever the latest actuals were.
So dynamic arrays offer lots of opportunities for reporting in Excel. They are a different way of calculating. So it does take a little while to get your head around. You do require a bit of practise, bit of trial and error, bit of making mistakes. In terms of the companion video, you might need to watch that a couple of times to get a good understanding of how all of the formulas and functions fit together and how they all spill to the same number of columns and how they summarise all of the data in both the budget and the actuals. So dynamic arrays are definitely a skill worth learning. And if you check out these three podcasts as well as the three companion videos and the three companion files, that will give you a good introduction to building reports using dynamic arrays. Thanks for listening. I hope you enjoyed the podcast.
Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks.
To find out more about our other podcasts and CPA Australia, check out the show notes for this episode. And we hope you can join us next time for another episode of Excel Tips.
