Okay, so in this video, I'm going to be demoing the Arixcel add-in, which is mainly used as a navigational add-in but can also be used as a formula map add-in. What I've got here is a financial model, and I'll just briefly explain: these blue worksheets are outputs, yellows are inputs, and greens are calculations.
What I've got here is a gym model, so we have five gyms, which are one, two, three, four, and five, and then it has some central costs. These are actually consolidated, so they're added together in this Consol worksheet, which therefore provides a view of the whole business. Now, if I wanted to change the assumptions in my workbook or in this model, then I'd enter them here. So this might be price or how many people join the gym. Those assumptions are passed to the calculation sheets where they're calculated together, so it might be the number of members multiplied by price creates revenue, and then that's output within Revenue here, etc. So we can see a P&L for each gym, and then that's added to see the P&L for the whole business.
Now, what I'm going to do here is look at this P&L, and I might find some kind of cost, so for instance, we have direct wages here. Now, if I go to the right, I'm going to find a month I want to drill down on, so this is November '19. We've got costs of 17,645. This is summing the same cell across all these different worksheets. Normally, I'd have to investigate that figure by clicking into each of these worksheets, finding that cell, seeing what the figure is, and then drilling down on that.
What I can do here is press Ctrl+Q, and Arixcel Explorer opens. I can see my formula here, and if I press Ctrl+Q again, I expand the formula, and I can see all the different cells that this formula is referencing in this model. So, I can see my 17,645 is made up of 10,082 and 7,562. If I press down, I've just navigated to the first one and then the second and then the other worksheets which are zero. You can see how quick it is to navigate through your model.
Furthermore, if I want to investigate this figure because this was the largest figure of the 17,000, I can press Ctrl+Q again, and we have a second Arixcel Explorer open. So now, this is an Index function; if I press Ctrl+Q again, it breaks this out. The result of the Index function is the 10,000. This is the array of references, this is the row number, which is this 1 here, and then this is another cell which we're kind of multiplying by. We're interested in really this 10,000 here, and if I press Ctrl+Q again, I can drill down further. Now, we have an IF function, and if I go down to here, we've got a direct wages figure for gym one. Again, I can press Ctrl+Q, open a new Arixcel Explorer, and basically, you keep on drilling down until you find where this is coming from.
In the end, this formula is actually summing these cells, and so I've drilled my way through from the Consol, through to the gym worksheet, specifically worksheet one, and then through the calculation sheets and then back to the original inputs where I could potentially change this input and change the result I have at the top of the model.
Now, what's also great about this is that I can very easily find my way back. So, I'm in this Arixcel Explorer here, and I can trace my way back. If I press up there and press Esc, it closes that window. I can press up and then press Esc, and then I'm actually back to my original Consol sheet where I investigated the original number. What's good about that is, again, I've navigated very quickly, but I haven't had to remember where I started when I did that whole process. I can just quickly go back to these dialog boxes and press Esc; they'll close, and then we can go back to the original one and navigate to the original cell.
Now, similarly, if I go to one of the input sheets, let's just take, for instance, the input Revenue sheet, I've got an input here or an assumption, and I might want to know where that goes in the model. If I press Ctrl+Shift+Q, it brings up a similar box, but we're here interested in the dependents. So, this is the cells which depend on this. Again, I can cycle through these, and I can see it goes to this cell. I can press Ctrl+Shift+Q again and trace it in the other direction. So that's another bit of functionality of this; the first functionality I showed you with just Ctrl+Q is what this tool is more often used as.
And then, finally, I want to show you the formula map functionality. If I've got this spreadsheet here, all these formulas are basically the same. This is slightly different, though; this is a sum of everything below, whereas these are summing across the worksheets. Now, if I go to Arixcel and I go to this toggle formula map, all of a sudden, the worksheet's very colourful. I'm just going to zoom out slightly there, and I don't really understand what these colours mean. If I go to Arixcel and I can click map colours, and this gives me a little key.
So this orange, just taking this block of formula here, orange is a unique formula, so this is a formula we've entered. The blue is a formula copied across, so this basically means I've created a formula here, and I've copied it across; these are essentially all the same formula. Pink here is still a unique formula, but it's referencing another worksheet, so this is where we're summing across several worksheets, but then again, across here, we've got blue, so all these are the same as the preceding cell. I've created a formula here and copied it that way. I've also copied it down, so these green ones mean the formula is copied down; this has been copied to the right and has been copied down. This beige colour means the formula has been copied across and down, so essentially, this is the same as what's above and what's to the left, and so all this is, therefore, the same formula.
Now, why might we want to know that? I'm going to show you. If I turn off this colour, if I put a “5" here accidentally but I didn't realize I've done it, firstly, we've tripped our error switches, so we know there's an error in there, which is great, but we might not be aware of where it is. If we actually look at the spreadsheet, it'd be very hard to tell that there's a "5" hard-coded here. If I go to Arixcel and I toggle the formula map, all of a sudden, you can see this is not consistent; this colour is not consistent. And so if I bring up my map colours again, it says yellow is a number, so we've got a hard-coded figure here, and so we know that's where our error is. If we don't like these colours, what's great is we can actually customise them to what works. We can set these map colours, for example, maybe we want this as a kind of red or something like that. Now, if I just go back here and then I copy this formula here, I'm going to turn off the map colours and then I'm going to reapply them, and we can see we've got rid of our problem. So that's actually a really useful tool to find or spot errors in our model.
In summary, this Excel add-in is a powerful tool to help you navigate and understand your financial models more efficiently. By using the navigation features, you can quickly trace the origins of figures and formulas, and with the formula map functionality, you can easily spot errors and inconsistencies in your model. The customisable colours make it even more user-friendly, allowing you to tailor it to your preferences. Overall, this add-in can save you time and effort when working with complex financial models.
