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 arrays:
Welcome to the Excel Tips Podcast. My name is Neale Blackwood. And in this edition we're going to review a report that is using dynamic arrays to create an automated month and year-to-date report. Now this is the second in a three-part series. The first part covered creating a reforecast report where we had actuals added to budget over the year to give a reforecasted full year.
This report that we're covering in this edition is a more standard report where we have a month compared to budget, a year-to-date compared to budget. There's also the actuals compared to the same month last year and the same year-to-date period last year.
You need the subscription version of Excel to work with dynamic arrays. Dynamic arrays were released in 2020. And since then, they've developed with the use of more functions. So extra functions are being added to provide more possibilities in the solutions that you can use dynamic arrays for. The beauty of dynamic arrays is you can create a single formula that will populate a range. Now that could be a vertical range, a horizontal range, and even a two-dimensional range. These ranges are called spill ranges. Dynamic arrays offer lots of opportunities to simplify your reporting.
So this is probably one of the most advanced reports that I've actually shared.
The video and the article cover the report in detail, and I recommend that you watch the video. You might need to watch it a couple of times because it is fairly advanced. The article also covers a few other things. The idea behind the report is that the reports are automated so that once you drop in the new data, the report automatically updates for the next month. Any account numbers that you add to your chart of accounts are automatically included in the report. That's demonstrated in the companion video. The video and article are available NTHEBLACK website. You can also download the companion file as well, so you can see all the reports and see all of the formulas.
Range names for formatted tables:
One of the things I did with this series of reports is to use range names to refer to formatted tables. Now, formatted tables are a really powerful feature in Excel and they use something called a structured reference, which is basically the formatted table's name followed by the column name in square brackets. Unfortunately, that leads to fairly long formulas. And so what you can do is create a range name that refers to the column within the formatted table, and that range name can be a lot shorter. And so when you use the range names, you get the advantage of referring to a formatted table, which automatically expands as data is added to it. You can use a shorter range name in the formula, which in general keeps the formula shorter. And again, in general, the shorter the formula, the easier it is to read.
Budget layout and structure setup:
The budget layout in the file has a typical budget structure where we have the accounts going down the page and the months going across the page. Two extra columns were added to this standard structure to extract out the current month and the current year-to-date. So the current month was fairly easily extracted using the index function, whereas the current year-to-date was a little bit more tricky. And that actually used the offset function. So again, if you want to see how this offset function worked, you can check that out in the video.
The offset function:
The offset function sort of got a bad rep a while ago because it is a function that is what's called volatile, which sounds really dangerous, but all that means is that the function calculates every time Excel calculates. So that can slow excel down. The thing is that our computers are so fast now that you don't really notice the difference. So offsets had a little bit of a new lease of life with dynamic arrays because it can add some flexibility to creating reports and references that expand as the year progresses. So the offset function was able to create a two-dimensional range for the budget that automatically expands as the year progresses. And then I was able to use the buy row function to extract out of that two-dimensional range to work out the total for each row.
The filter function:
Another function that was really useful in the reports was the filter function. So the filter function was able to take the listing of all of the chart of accounts and only bring in the codes that were relating to a particular category. So the report had three different categories, revenues, direct costs, and indirect costs. And so this automated the report in that just adding a new account to the chart of accounts table automatically populates the report via this filter function.
The filter function also was enclosed in a sort function, and that made it possible to basically add the codes in any sequence into that chart of accounts and the sort would then sort the filtered range in ascending order.
So it just made it a little bit more flexible and also just a bit more robust. In the report, once the filter had generated the list of account numbers, the X lookup function was used to extract the description and the sign. So one of the problems with the data that we were working with is that the revenue numbers were all credits. They're all minuses. But obviously in our report, we needed to show them as a positive value. And so there was a sign column in the GL chart of accounts that allowed us to convert the negative revenue values into a positive for the report. And that sign column actually was then used in the variance calculation as well, which I'll explain a bit later.
SUM IF S function:
I used SUMIFS. So SUMIFS is a function that allows you to do criteria based summing. So there was three criteria for the state, the month, and the account, and that was extracting data from the actuals. In terms of the budget, it was a little bit simpler because the budget was only working with a single financial year, and so we just needed to add up either the month column or the year-to-date column in the budget sheet. But again, the SUMIFS function is flexible enough to handle that.
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:
Using the wildcard character:
One of the things that this report had that the previous report didn't is the ability to report by state. It also used a wild card character. So on the report sheet there was a dropdown that had the states listed, and at the end was the wild card character, the asterisk (*). Some people call it the star. Now, the beauty with this wild card character is that can report on all of the codes. So you can report by individual states and you can report in total. Now, the downside of the asterisk as a wild card is that it only works with text-based fields. In our case, the state only had text entries, so it worked okay. But it does offer the flexibility to let the user choose a state to report on or to report on all of the states.
Variance calculations:
So if we go back and think about the variance calculation, so the problem you have with a variance calculation is that the calculation for revenue is different to the calculation for costs. So obviously, if the revenue is above budget, that's a good thing. But if the costs are above budget, that's a bad thing. Now, normally you use an IF function to handle that difference, but we actually had something that made it a little bit easier in that all of the revenue items had a -1 as the sign. So that enabled us to convert the negatives to positives. And all of the costs had a 1 as its sign. And so the formula that we used was able to use that sign to work with the difference between actuals and budgets to give us a single formula that worked for both revenue and costs.
The ABS function:
And the last aspect of the report that was useful was the use of the ABS function. Now, ABS stands for absolute, which is a mathematical idea. It's basically measuring the distance from zero, which you wouldn't think would actually mean much, but what it does is it'll take any negative number and convert it into a positive number. And so that way we can use that to create the base when we're calculating a percentage difference. One of the issues you can get when you're working, say with costs, is that you might have a cost that could be negative.
Now, something like Work in Progress Movement. Now having a negative in the budget would then affect how the variance percentage is calculated. But if you use the ABS function on that budget base, then that converts it into a positive number, and that usually makes it a better calculation and a more accurate calculation when you're calculating the percentage difference.
Additional resources:
So as I mentioned, it's probably worthwhile jumping INTHEBLACK website, checking out the video, downloading the file, and seeing what's possible now with dynamic arrays and reporting in Excel. Stay tuned for part 3, which we'll look at using dynamic arrays in building dashboards.
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 in this episode. And we hope you can join us next time for another episode of Excel Tips.
