Ask the Expert | NetSuite Financial Reporting in Excel with Solution 7 | Aug 2023

Evie: Hi everyone, I am NetSuite, the customer success manager here at Solution 7. Thanks for joining another Ask the Expert session. We have our usual resident expert, Simon Miles, to give us a bit of a demo and answer your questions, so please, if you have any questions throughout the session, throw those in the Q&A box in Zoom, just at the bottom of the screen. All right, I that's enough for me. Take it away, Simon.

Simon: Hey everybody. Welcome to, um, this month's session. Um, this month's, um, webinar. Um, I thought I'd give you a, um, an update on, um, this month, where we are with the, uh, NextWeek 2, um, migration work. Don't have too much to say this month about it, but we are making some progress. Um, and then I thought we could dive into the Q&A. Um, just as a reminder, um, NetSuite have brought out a new data source, um, that they've called NetSuite 2. Um, around that new data source, we have three migration plans that we're currently working on. Um, an engineering plan, which is based around our 4.12 release; uh, a communication plan, how we intend to tell customers, um, about the migration; and the migration plan itself in terms of how we, as a group, can move from existing NetSuite 1-day data source and get your spreadsheets over to, um, the NetSuite 2 data source. Um, not really much to say on the engineering side. Um, what with vacations, um, there's been some engineering work, but really nothing to talk about from when we spoke last time. So, um, we're currently working on the ability to upgrade lists within the software. Nothing really to show at this stage, um, but, um, as I say, that, that is coming. We are partway through the engineering process. Um, as stuff, um, becomes available, as code becomes available to demonstrate, we will certainly share it with you, um, as part of these sessions, but nothing really to say there.

Um, on the communication plan, as I said last time, we have identified different groups of customers, and we do plan to communicate out to customers. Um, the only real movement that's worth talking about is on the migration plan itself, and that's around customizations. Um, we've been looking at all of the customization work that's been done around custom fields and custom segments and looking at individual customers' customizations and trying to consolidate them into a form where we can do some sort of mass upgrade on those customizations. Um, so we've consolidated all of the individual customizations into something that will allow us to try and bulk update, um, the customizations, which we can then roll into part of the communication and the migration plan to actually get those customers, at those, um, customizations, out to customers for testing.

So, as I say, not really much to say on the NetSuite 2 side this month. Um, hopefully, we'll have a bit more, um, a bit more to go on next month, and, um, as we move further into the fall. Um, as a reminder, if you want to download a version of Solution 7 that's already working with NetSuite 2, um, it's available on the website now. Um, it's badged up as the latest version; it's the 4.12 release, so feel free to go ahead and try and download it. Um, again, be aware that workbooks built in NetSuite, um, the NetSuite 1 data source, are not necessarily compatible with the NetSuite 2 data source, so there will be some manual work you'll need to do on your workbooks. Um, if you have a customization and you want to move, come talk to us, and we'll help you we'll try and help you with your customization and get that over as well. Um, so you'll be done on more of a, you know, a manual channel than sort of this automated channel that I was talking about earlier, where we're trying to consolidate the workbooks. That's really it on the NetSuite 2 side. Um, short and sweet in terms of what I want to talk about. Um, I thought we could get into the Q&A and, um, have a play with Solution 7, so, NetSuite, back over to you.

Evie: Awesome, thanks, Simon. All right, the Q&A. I have a question, Simon. Will this session be recorded?

Simon: This session will be recorded and will be made available, um, when the marketing team have had a chance to chop up the video and prepare an email and send it out. And also, just to say, the Q&A box is open, so if you do have any additional questions to the ones NetSuite and I are going to talk the ones NetSuite and I are going to talk about now feel free to throw them into the Q&A box.

Evie: Absolutely. Absolutely. All right, so I'm first going to go through some of the questions that you guys submitted prior to the session, and after that, we will look at any outstanding questions in the Q&A box. All right, so the first one is from one of our current users and relates to NetSuite 2: How do we confirm that we are properly configured to support and transition to NetSuite 2?

Simon: Ah, good question. So, um, as I said, have a go at downloading the version of the software that's on our website. You'll need to go into here let me show you you'll need to go into the configuration dialogue, which is this option here. You'll need to go into the configuration dialogue, um, and I would recommend actually making a copy of your config file. So, for anyone who doesn't know, your configuration settings are stored in these .docon files. You can just copy these files; they're just XML files under the covers. Um, make a copy of your configuration file, point Solution 7 at the new configuration file, and then you'll need to change your data provider. Well, this isn't a very good example because I've got hundreds of data providers in mind, but you'll see something in there labeled NetSuite 2. So switch over to the NetSuite 2 data source, re-enter your username and password, and then you should be good to go.

Um, the best way to ensure you're configured correctly is just reach out to support, and I'm sure NetSuite and the team will take a look at your machine and just confirm that you're set up correctly.

Evie: Yeah, absolutely. Um, just a note as well: you guys will need to be running the latest version to be able to do that. So yeah, like Simon said, if you need a hand, if you want us to talk it through with you, just shoot us an email to support, and we'll show the support email as well on the screen, if not now, at some point through the day. Okay, um, I on that that leads very well into the next question. Um, how do we update the version?

Simon: How do we update the version? I'm assuming that question relates to how do we upgrade Solution 7.

Evie: Yes.

Simon: Yeah. So, um, just download the latest version from the website. So jump on to let me bring up my web browser jump on to, jump on to Solution 7, go to the downloads page, go to the downloads page, and just download either the latest version or the previous release. If you want to go to the latest version, just download the latest version.

Um, Solution 7 is a traditional client-server install, so, um, what I mean by that is there's a physical install you need to put onto your machine. Um, so not everyone goes through this download process. Some people use tools like Intune to do mass updates, so you can actually use, um, automated tools to push the software out to your estate. Um, but if you've only got one or two PCs, yeah, just download it manually and, um, just run the installer, and it should just upgrade the version that you're running. Um, you'll know which version you're running by going into the about box. So if I go up here, there's the about box, and you can see here Solution 7 Financial Analysis, and you can see I'm running 4.12, and it's a build date of today, and that's because I'm running a development build. But yeah, you can see whether you're running an old version through the about box, and you can see what the, um, see what the version is, and then download the appropriate version from there.

We do get asked quite regularly, um, whether we let customers know about new builds being made available. Um, generally, we don't, and we do that intentionally because we don't want customers to download and install the latest bug release, um, just in case that causes a problem that we don't expect. You can imagine with thousands of users, um, as a relatively small team, if everybody found a bug all at once, we'd get swamped. So, um, we try and hold back letting people know about bug fixes, although at any point in time, you can jump onto the website and you can have a look at the release notes, and I'll show you those in a second. Um, but yeah, we don't really let people know as bug releases come out, again, just in case we release something that, um, causes us a bigger issue.

Um, during these Ask the Expert webinars, um, we are letting people know when new versions, major versions, are available, and then we can caveat that with how, um, whether it's a Beta release, whether it's a full release, how long it's been available, and so on and so forth. I mentioned release notes. If you want to view the release notes, again, go to the downloads page, scroll all the way down to the bottom, there's the release notes there. Just open up the release notes, take a peek, and see if there's anything in there that might be relevant to any problems that you've been having. Um, you'll see the builds come thick and fast. Um, we actually try and work to a weekly bug release cycle. So if a, um, if a bug is, um, discovered and reported to us, we will endeavor I say endeavor to fix it by the following Wednesday, but it depends on at what point in time.

Um, it's reported, so it'll either be the immediate Wednesday following the reported bug, or the Wednesday after that. We will try and, you know, non-critical bugs out as quickly as possible. Anything that requires more effort on our side to resolve, um, may take longer than that Wednesday cycle, but, um, we do try and turn around bugs very, very quickly, and, being a very complex product, um, there are plenty of edge cases in there that more and more customers are finding. So, you'll see this is quite a long list of issues that have been reported and fixed, but we do turn around fixes fairly quickly. Thanks, Simon.

Evie: All right, so another question from the same user. Um, how do we pull actuals and budgets into pivots?

Simon: Okay, um, let's have a look at that. So, pulling actuals and budgets into a pivot you can't. You can't, because of the nature of actuals and budgets. You can't pull them into a single pivot. Um, the reason being NetSuite physically stores budgets in one area of the system and stores actuals in another area of the system. That's actually one of the reasons why actual versus budget reporting is quite hard within NetSuite. So, using our list and functions approach, it's really trivial, because you can just have a column of actuals and a column of budgets, and all of your formulas are lined up to pull those through.

Um, if you want to pull actuals, you'd have to pull actuals in as a, um, let's do it as a flattened pivot. Let's do it as a regular pivot. So, you can pull in actuals just using a regular pivot table. Let's do four-star J 2023 to December 23. We really should update our dataset, E. Right, so here's our here's our actuals coming through. Um, accounts and amounts. Uh, what am I doing? Put the amounts there. So there you go, there's some actuals coming through. If you want budgets coming through, they would have to come through into a separate pivot table. There, unfortunately, there just is no easy way of pulling budgets through into the same table as the actual.

So, let's just do the same again, and fingers crossed we've got some budgets in the system. Here we go. So, again, the amount, the account. So, you could have a table of actuals and a table of budgets. If you then wanted to combine them further, you'd then have to pull the data out of the pivot table and use some sort of pivot table. Um, I think is there a GETPIVOTDATA function in Excel that would allow you to then combine those together? But unfortunately, as I say, the nature of the way NetSuite stores the actuals and the budgets at the transactional level, um, when you're using a pivot table, unfortunately, you can only pull them into separate tables. But again, even that's relatively simple to do. For budgets, just use the budget pivot table option. For actuals, use transactions posted or the non-posting option. Hope that answers your question.

Evie: Thanks, Simon. Uh, so the next question, um, do we have any seminars for beginners? I can answer this one, Simon. So we often arrange one-on-one sessions for beginners, or, you know, new users of Solution 7. Um, if you'd like to arrange a session, you know, go ahead and email the support team, and we can, you know, arrange a one-on-one for whenever kind of suits you. Um, and this, you know, also goes for anyone who has questions about specific reporting scenarios, install issues, technical issues. We've had a few questions about all of these. Um, so if you do have a question about specific reports or tech issues that you're having, um, please email us at support@solution7.co.uk, and we can jump on a call to help.

All right, so the next question, um, Simon, is one for you and is one that we found on one of our YouTube videos. Um, how do we pull internal IDs from NetSuite into Excel.

Simon: Oh, okay. So, internal IDs are codes, internal codes to NetSuite that are used, um, at a very low database level. Generally, from a database perspective, they're what are known as the keys to the table things that individually identify records. Um, generally, database keys are not very user-friendly, which is historically why we've tried to hide them. Um, but you can gain access to, um, record IDs, or these record keys, um, through Solution 7. If we look at, um, I use the pivot table to show you where they're stored.

So, um, let's have a look. In fact, no, I'm not going to use a pivot table. I'm going to use a list. So let's pull in a list of departments, for example. When I pull in a list of departments, choose the subsidiary sorry, I say choose a subsidiary. That's in our latest version. We don't have that in this version that I'm looking at. I go into choose columns. From there, you can see here we've got the department ID. So that's what is also known as the internal ID. Um, the reason we don't refer to them as just internal ID is: can you imagine all of these different database objects and everything having something called internal ID? It would get very confusing. So, um, for departments, it's Department ID. For locations, it's Location ID, and you can simply pull them through by pulling the ID into the table, click OK, click OK.

And so for each of the different department codes, these are the internal IDs associated with them. Now, you can also get to the IDs through functions. So, in addition to being able to pull them through using the table functionality, we also have what we call our lookup functions. These are less well-known, but they're super handy in terms of being able to look up some of the information associated with a record. So, you can see here we've got accounts, classes, departments, items, locations, and so on. Let's pull the NS Department function, look up the subsidiary. And now the way these particular functions work is they work on a filtering basis, so we can choose which field we want to filter the record on.

So, I'm going to use the name to look up the ID. So when we pulled this table through, we pulled ID followed by full name. So let's go back the other way. So we're going to use the full name, and we're going to filter to return the ID. So let's just go through that again. So, for this particular function signature, we've got the subsidiary coming through, we've chosen to filter on the full name of the department. The value for this row is going to be cell D4, which is our admin code. And then finally, we said we want to return the ID. So, fingers crossed, we should get a one. And then, when I drag that formula down, we get all of the IDs repeated back out.

So really, there's you've got two options. You can go in through the list functionality, or you can effectively reverse look up the IDs using the lookup functions.

Evie: Thanks, Simon. Okay, so our next question also kind of relates to internal IDs. How can we set up Solution 7 formulas to not break when names of departments or other unique items change in NetSuite?

Simon: Oh, okay, so how do we prevent how do we prevent functions from breaking when we change things like Department codes? It's an interesting question. We tried really hard within the software to hide the IDs, which means you're working with friendly names, department codes, location codes, and so on. The advantage of that is they're super easy to work with. The downside to that is that department names can change, and if department names can change, you end up in a situation where you may have inserted a list of departments, you've inserted a formula let's choose the regular balance function and we filtered that on the department, for example.

So, let's imagine that Sales, the name changed, and it became a different code, so I'll stick a 2 on the end. You can see that now goes to a zero. And what could happen in real life is that somebody changes the department code within NetSuite. You don't update your reports because you've not updated your reports, you've then got the issue that the coding that you've used in the report no longer matches NetSuite, and you end up getting zeros coming through. One way to overcome that problem: when you insert things like Department codes, use a list. You can always refresh your list. When you refresh the list, we will pick up the new name from NetSuite for you, so you don't have to.

Um, if you type in the name of the department, it's not linked to anything, so the software doesn't know to go back to NetSuite to grab the new department code. Whereas if you insert a list, and then you've got the refresh option, um, we can update that for you. Um, specifically, if you really do want to work with IDs, um, let me show you a way we can do that. So I'm going to right-click and go into the choose columns option, and I'm going to pull the department ID through. And again, this could be Department ID followed by name, however you want to do it.

So here we've got the department code, the department name. I'm actually now going to go back into choose columns, and I'm going to lose the department name column. Let's get rid of that. Click OK. There we go. And I'm going to go in, and rather than using the column from the choose column screen, I'm going to go back to that function that I described to you earlier the lookup, the department lookup function and I'm going to look up the department name based on the ID. So I'm going to filter on the department ID. The value that I'm going to use is going to come from here for that particular row. And the field that I'm going to return is going to be the full name. Can you see there that that's the default of that parameter? So I don't actually need to provide that parameter. Let's click on OK, copy that down. So we've now got the ID coming from the list, we've got the department name being looked up dynamically.

So I don't need to refresh the list this time because these IDs never change; they can never change. Um, and then when I reinsert this function up here, when I reinsert the function now, when I choose the department, I'm again going to reference the department name here. That department name is now dynamic. So when I copy it down, we get the balance values come through whenever these change within NetSuite. All I simply have to do is do a regular refresh. I don't need to refresh the list because the IDs, or the internal IDs, have not changed. I simply click on OK, and you'll see not only do the numbers the balances go to hash, but also these lookup functions go to hash, because they get looked up first. So we resolve the IDs to the name, and then having resolved the IDs to the name, we can now resolve the balances from the names themselves. And again, it doesn't matter if the name has changed. So, very, very simple we're using a static ID to dynamically look up the name, to then dynamically look up the balance. I hope that answers that question.

Evie: Thanks, Simon. Yeah, I don't think we've looked at the LookUp functions before, and they are super helpful. All right, so we've had a couple of questions about calculating CTA. Um, again, I'm going to start off by answering this one. Um, I think these are probably better handled on a support call as well, um, as we need to help you find the data in your NetSuite. But if you do want to have a go yourself, we do have a cheat sheet that we can share with you.

Simon: That was the spreadsheet I had open earlier when I should have closed it. Here we go. So, let me just explain what this is. Um, we have a few cheat sheets, again we'll send out a link to those. Um, what these cheat sheets try to explain to people are some of the concepts within Solution 7, and that we can use for pulling data from NetSuite. Um, CTA cumulative translation adjustment from a non-acc accountant perspective, because I’ve never claimed to be an accountant, is effectively the leftover balance for, um, what I understand is unders and overs from FX differences. Um, it's part it's one of three balance sheet calculations that are not physically stored by NetSuite.

So, an example of CTA: say you've got a Euro company, a Sterling company, and a Dollar company. Um, when you aggregate those three companies together, um, you're going to end up with exchange, um, unders and overs, and I believe those are mopped up through the CTA. CTA is calculated dynamically by NetSuite; it's not physically stored in NetSuite. So, if you were to interrogate your CTA balance, it won't be what NetSuite shows you in one of the financial reports. And if I'm honest with you, we are still learning about the CTA calculation, um, because every now and again someone says, “Well, you've given us this cheat sheet, it doesn't the balance doesn't match.” Um, like the question says, “Where do we find the transaction action that we also need to pull in to complete the CTA calculation?” So that's why the starting point is this spreadsheet. But then, if we can't find the data between us, we then jump on a call and figure it out.

Um, the other two calculations that are stored in this, or that are referenced in this cheat sheet, are retained earnings and net income. Again, those are not physically stored within NetSuite; they are just calculated on the fly. And what the workbook tries to explain really, the best way to look at this is: you work backwards. These are the three formulas that you need to understand to calculate retained earnings, net income, and CTA. So, we've tried to document there a way that we see you can pull those balances through. And then, working backwards, you can see the parameters that are needed to service those formulas.

So, you've got a couple of supporting formulas for calculating financial periods: the start of the year, and the current period sorry, the end period of the previous year. And then, going back a stage further, we've got the different parameters that you can feed into these functions to play with the calculation to make sure that these are coming out with the correct balances. Again, we've had the spreadsheet for several years now, and it is still evolving. Um, every now and again, um, we find a new feature in NetSuite that adds into the different balance sheet calculations, and we tweak the balance sheet calculations cheat sheet to update those findings. Um, again, we'll send out a link to that in the follow-up email at the end of this session.

Evie: Thanks, Simon. This cheat sheet has gotten me out of quite a few tight spots, so I appreciate it. All right, so next question is about getting transactions from custom segments where a segment has blank or null values, and the non-option doesn't work.

Simon: So, returning transaction values where the non-option doesn't work. Let's talk briefly about the non-option first. Um, when you insert a list, again, let's go back to let's do locations this time when you insert a list, we always include what we call the non-option. Um, within NetSuite you'll see it is “No Department” or “No Location.” Um, for us, it was just easier to use one word across all of the different list types. Um, this is effectively a placeholder that can be used where transactions have not been referenced to a department or to a custom segment, for example.

Um, so let's just show you how that works. So, if I go Function Balances Abal, just do the same old formula that I showed you earlier, J 2023 December 2023 Depart 1 Location, reference the location. So there you can see there you'll see the locations coming through, and the non should have a balance when it comes through. There we go. You can see we've got a balance for the non. This is exactly the same as custom segments. I just don't have any custom segments set up in here to show you how a custom segment will work, but the non is the same for custom segments and for the baked-in segments.

When you insert a pivot table I'll do it in a new sheet when you insert a pivot table, let's use a flattened pivot. When you insert a pivot table and reference a location or department, rather than seeing dash none dash, I think you just see a blank entry. And I'm guessing that's because we never got around to mapping the blank entry over to the word “none.” That in Jan 2023 December 2023 roll that down. We're not going to filter on the location, but what we will do is let's pull the location through the choose column. So we're going to use the choose column screen to pull the location code in. Where's Location Full Name? There we go. Let's throw it just after the G code. Again, click OK, click OK. But here we're pulling a series of sales transactions or would help if I could spell series of sales transactions and pulling the location code through.

Now, typically, when you want to filter data in the pivot table, you've got two ways of doing it. You can either filter it on the NetSuite side through the query that we send to NetSuite, or you can filter it once you pulled it back into Excel. Now, the challenge that you've got with the blanks is and I think this is what the question is referencing if I go into the edit option and I go to choose columns and I try and filter on the location name, you'll see here that I can do equal, not equal, less than, greater than, less than or equal to, greater than or equal to. There's no “non” or blank, and I tested earlier today doing not equal to star. Unfortunately, that doesn't work. So I couldn't find a way of being able to do a filter in such a way where, when we send it to NetSuite, we only get the blanks or the non back.

You can, however, do the filter client-side, so do it within Excel. That does mean you need to pull all of the transactions back. And you can see here that we've got a mixture of Boston, San Francisco, and blank entries for our location. Let me just freeze the row because it will make it easier to follow. Where's my freeze panes gone? Uh, it's going to be P. But yeah, here we go. So we've got a mixture of different locations. So you can either apply a filter here on the location name and then just use “give me only the blanks,” and you can see there, as I scroll down, there are just all the blank entries. These would be non if you were doing it through a function.

Um, although I think the coolest way of filtering a pivot table is to use a slicer. So let's insert a slicer based on locations, shift the slicer over here, and now we can actually use the slicer to do that same filtering. This is effectively dynamically updating the filter that I did through that button. So here we can just use the slicer to filter through the different location codes, and again, I have the option to choose a blank entry. Probably worth us investigating whether we can turn those blanks into non and actually make it consistent between pivot tables and functions. So we'll certainly take that one away and have a word with the developers about it. I just don't think it's as simple doing it through the pivot table engine as it is through doing it through the functions, but it's certainly something we could have a go at investigating.

How does that look, Evie?

Evie: That looks great. Yeah, I'm looking forward to the guys chopping this one into a little video. We like pivot tables

Simon: We do they're great.

Evie: All right, so next question is around custom fields. Um, our support team can help add any custom fields that you have into Solution 7. Just again, contact support with the details of the customizations you would like to see in the software. Um, you know how we always get questions about if you guys can kind of handle that process on your own? And one day, absolutely, it's on the roadmap. Roadmap. So you guys can manage that process and add all the custom fields you want. Uh, but in the moment, our team will need to kind of add those for you. But the good thing is, we do it all the time, and it's fairly quick and easy for us to do. So just, yeah, shoot us an email, and we can discuss those with you.

Simon: And, obviously, the priority for the roadmap at the moment is NetSuite 2. So, um, although it is on the roadmap and we are doing investigation work, in fact, as part of the NetSuite 2 work, we're hoping we might be able to make quite a significant step forward in terms of allowing customers to manage their own customizations. Um, so fingers crossed, with a fair wind, as we progress through the NetSuite 2 functionality, we will have something that will allow customers or be a move towards customers managing their own customizations.

Evie: Awesome. All right, um, so last couple of questions from the pre-submitted section: is there a way to get Solution 7 to run the financials faster?

Simon: H-speed is speed's an interesting one. Um, generally, generally customers don't have an issue with speed. It's all down to size of the workbook, um, volume of formulas in the workbook, um, size of your NetSuite database, um, complexity of, of, um, the formulas. There are some basic rules that you can stick to to help Solution 7 calculate your workbooks more, more quickly.

Um, let's go through a couple of, um, basic ones. Um, let's just build out, um, let's just build that have we got something built already? I can, I can use an example. Yeah, let's use this one. So, um, I don't, let's use this one. We've got some balances in there. So, um, here we've got some formulas here. We've got a list Al sorry, here we've got a list.

Um, one of the ways to help speed up refreshing a workbook is to make sure that if you go into options, that this refresh mode is set to manual. Um, if you've got lots of automatic refreshing of, um, lists, when you hit the refresh button up here, Solution 7 has no choice but to also refresh all of the lists. The lists are refreshed one at a time, and then finally the formulas update. So it takes a couple of minutes to refresh the lists; you have to wait while those lists refresh, um, before the formulas can update. So that's one way to help with performance.

Um, another way is to try and be as consistent as possible in how you construct your formulas. Um, so for example, if you had a, if you had a, um, a formula where, let's insert a formula up here. So let's shift that down. Let's grab that formula there and paste it here, and then I'm going to remove the department code. Let's just remove the department code there.

So, in this scenario, where we've got a series of formulas that are broken out by department, this is the department code here. And then we've got another formula where that department code doesn't exist. Solution 7, in this scenario, has no choice but to hit NetSuite twice: once to get the dimension the, to get the department dimension; the other is to get the formula without the department.

Now, when you've only got two formulas, it's not so bad because we try and run them in parallel, but again, there is some overhead on both, um, Excel and NetSuite. If you've got a very, very big workbook so imagine if we had a thousand rows and 20 columns there are thousands of, um, cells that need to be calculated, and potentially, if we had 20 columns, that would be 40 formulas that would need to be evaluated against NetSuite.

Um, one easy way around that is actually to use is to take advantage of a wildcard. So here, for example, if I reconstruct that, um, what did I say that was? It was department codes, wasn't it? It wasn't it? Yeah, Comm. So let's just double-check that, make sure I get it right. So yeah, that's the department code that we're going to, um, effectively, uh, wildcard out of the formula.

If I go in here and choose the department by using the asterisk, what we're saying to Solution 7 is I still want to use the department dimension, but I just want all departments to be aggregated together into this formula. What that means is, because Solution 7 knows it needs to get these by department, it can then hijack that attempt at querying NetSuite to service that formula. So it's a way of being able to reduce the number of hits on the NetSuite back end.

So here, this would actually only hit NetSuite once, whereas if that didn't reference a department code, it was just empty, that would hit NetSuite twice. And although, again, as I say, in a small workbook, it's not a big impact, when you get to very, very large workbooks very large implementations it can, you can see, slow down.

Now, there is another way of helping to speed up, um, refreshing, and that's only refresh worksheets that need to be refreshed for the time you're working on them. So what I mean by that is, when you load up load up Excel to start work I'm sure you've been there where you then have to wait while the whole thing recalculates. If you take advantage of our locking functionality so let's lock the sheet here you can actually lock the sheet in such a way where we recode all of the formulas so that they won't automatically recalculate. And you can see here that if I hit the refresh button and click OK, nothing happens.

What that means is you've now got control over being able to recalculate individual cells. If I do refresh let's choose this one here if I do, uh, refresh cell and say include locked formulas, I can actually just recalculate that one cell, knowing that all of the other cells are locked, and therefore Excel's not going to try and update those cells.

There are a couple of benefits. Number one: when you load the workbook, Excel doesn't try and kick off an automatic calculation to recalculate the entire workbook. Number two: as you're working on your workbooks, um, only the formulas that you ask Solution 7 to refresh will actually get, um, updated. So you can see there, if I, at the sheet level, all of those are locked. Oh, did I unlock it? Oh, no, I think include unlocked there we go. So all of those will go through and refresh.

The other benefit of using the locked formulas is that you create a static copy of your workbook. And then the final benefit is, um, you can have an effectively locked-down workbook that you can then use automation to publish out to your user base. So those, these numbers are not going to recalculate while you're doing your everyday work on the workbook. But when you hit the save, or the email, for example, using automation I'm not going to demo automation in this particular session but when you use automation to publish your workbooks out, you can also publish them with, um, static formulas in this locked state, so that your users don't suffer that, um, recalculation.

And then the other benefit of that is your users don't actually need a Solution 7 license to open those workbooks because they're just hardcoded formulas, which means that when they open the workbook, Excel just sees them as, um, because of the way we construct it, it just sees it as a number. And because it's just a number, it doesn't need Solution 7 to go through and do that calculation to update the workbook. So, um, you can effectively do royalty-free distribution, or license-free distribution, of your workbooks.

So I hope that addresses some of your concerns around speed. Again, if you're having serious speed issues, please do, um, ping an email through to support, and NetSuite or Maria, or one of the team, can jump on a call with you, and we can have a look through your workbook and maybe try some ideas out and see where we think the, um, performance bottlenecks are.

Evie: Thanks, Simon. That actually answered one of the Q&A questions as well. We love efficiency. All right, so last question, uh, from the pre-submitted, um, questions: is there a manual we can refer to for performing the functions we need in Solution 7? Um, so yes, absolutely. We can provide links to our PDF guides that are on the website. Or if you prefer to watch videos, you can check out our YouTube channel, and there's some helpful kind of how-to videos on there as well.

Simon: So we can send out we can include a link to this section of the site, and there is a function reference guide here. It all, it does, is list out the function definition. So it doesn't tell you how to use the functions. It's like an Excel manual. You know, if you look up an Excel function, it doesn't tell you how to use it; it just gives you basics on what it is and what it can do for you. You've then got to take that and put it into your reports and use it how you need to use it. But hopefully, again, with some of the demo videos that we've got available in these sessions, we can help build that knowledge.

Evie: Absolutely. Looks like we'll have to update those as well. They're referencing 2022 years.

Simon: are they? Oops. Oh, well.

Evie: All right, so I'm going to move on to the Q&A box with quite a few questions throughout the session. Uh, so our first question is regarding building a trial balance through the software. Um, when I run the TB through the functions on Solution 7, it appears to only be pulling the period activity. Uh, for example, January 2024. How do I run a total from the previous periods?

Simon: Oky-dokie, right. So how do we, how do we pull through let me jump into Excel. One second. Get a new sheet up. Okay, okay. So how do we pull through balances as opposed to period movement? Um, let me I'm not going to build a full trial balance, but let me build a let me just throw in all of the accounts. So the magic there is the from and the two period. Um, if you let me bring up the formula, if you only enter and we're talking these two cells, these two parameters here the from period and two period, if you only enter the from period, you will only get the movement for the month. That's great for cash flow when you're looking at bank accounts, for example. Um, it's great for looking at the movement in the P&L so trading month on month, quarter on quarter, that sort of thing.

If you want to do, um, balance sheet reporting, what you'll need to do is uh, what we've got in the balance sheet. Let's do one star. Oh no, I've got my accounts here already, haven't I? Let's do that one. Uh, what you need to do is for the from period, you need to go back to the beginning of time. So I'm just going to go back to FY 2001. And for the two period, let's pull in 2020. I'll go to the end of 23, shall I? So let's find December 2023.

So, by specifying the from period and the two period, again, NetSuite does not store these balances, so we have to calculate them on the fly. So we have to roll those balances up for you from the beginning of time. So, again, if I click OK and double-click, you can see that these now, rather than being the movement on the account for the month of December 23, it's actually the balance. It's actually the balance as at the end of 23. And I think it works I think because the P&L resets down to zero, I think that also works for the P&L as well.

But obviously, for the P&L, this is going to be a probably a year-to-date figure for the P&L because the year because the balance the P&L is cleared down at the end of the year. So, um, for the balance sheet, you'll get the current balance sheet balance. For the P&L, you'll get the, um, the period-end balance in the P&L. I hope that makes sense.

Evie: Thanks, Simon. Um, the next question is about suppressing zeros. Um, so how can zero values P be suppressed on reports? Oh, what a good Q. I like this question.

Simon: So let's copy, let's just play with this. Be with me a second. I'm just going to butcher this to three. Enter. I'm G to make the I'm going to make all of these values the same. Can I double-click there? Go. Copied that out. Right. So, the question is, how can we suppress zeros? So, here we have a simple report, not particularly meaningful we just got some repeated numbers. But the important thing is we got some rows that have zero. So, how can we, how can we suppress the zeros?

So, you'll see here we've got a zero suppression section to the ribbon. Now, effectively, the way you do it is you define the area of the book or the sheet that contains zeros. So, can you see there we've defined what we call a suppression range? Now, the easiest way to do this is actually just to select the range and hit the suppress button.

But the reason I wanted to show you that definition is the first time you hit the suppress button, it will say to you: there is no suppression range defined. Would you like me to create one for you? So that set, that little example where I went in and showed you how to define the suppression range this is doing that for us automatically. I click on OK. It creates a definition: the zero suppression range. And can you see that the zeros have now all disappeared?

And you can see that we can unsuppress and resuppress. If I go back into Define Suppression, you can see it's remembered the range that, when we said yes, that was the range that was selected. Now, why do we store a suppression range rather than just always suppressing the current set of cells that you've got selected? Well, it means that you can be anywhere in the workbook, and the unsuppress and the resuppress still works. So you don't always have to go select resuppress; it will just remember the suppression range that you created the first time.

If you want to clear it, you can just go in and clear the suppression range. Click on OK, and you're back having no suppression range. So, again, the next time you choose suppress, it will ask you the question.

Now, as an alternative, again, to going through and setting the suppression range through here, we have this Quick Set option, and that allows you to choose the range and do Set Suppression Range. And again, you'll see that that's pulled that through into that suppression range, um, setting. You'll also see we have this Exclude Range. What's that used for?

Um, typically, that's handy where you've got dates going across the top. So, if I've got January and then I've got February going across the top, and so on, the problem that you've got there is Excel sees these as numbers, and so it messes around with the, um, suppression engine. So, the best way to, um, make sure that the zero suppression doesn't pick those top rows up is actually to create what we call an Exclude Range.

So, you'll see now, when I've got, um, when I go into the Define Suppression screen, we've got a suppression range and an exclude range. So the software now knows that it's working its way down these four columns, but because rows one to three are defined in the exclude range, it will ignore those rows.

What we were finding is sometimes, um, there are use cases where people want to hide those dates. And what we were finding without the exclude range is that those were always coming back, and that got annoying. So, by using the exclude range, you can hide your values, and then they're not brought back by the software, um, mistakenly.

So, just to go over that again, we've created a suppression range and an exclude range. You can set them automatically using the suppress button the first time you go in, or you can use the Quick Set option to set them, and also to clear them. That makes sense?

Evie: It certainly does. It does for me anyway. All right, so the next question is about wildcards. Can you explain the difference between the star wildcard versus the question mark wildcard, and when to use one versus the other?

Simon: Yeah, that's an interesting question. So, wildcards, um, probably best if I explain how to use wildcards for a list. And let's use account numbers to do that. So, there are two wildcards that we support: the asterisk the asterisk and the question mark. Um, so, for example, if I do four star on a list, you'll see that I get a list of accounts that start with a digit four. There we go. So we've got one, two, three, four, five, six codes that start with a digit four.

You'll see if I go in and I edit that list and I change it to four question mark like finger trouble there, I bumbled that one and typed the wrong value in try that again. If I go in and use the question mark, you'll see that the currently empty list will stay empty list. And that's because the question mark whereas the asterisk says four followed by anything, it's a wildcard of any number of characters. The question mark is a specific placeholder.

So, to do the equivalent with my chart of accounts, I would have to enter four followed by question mark, question mark, question mark that's followed by any digit, any digit, any digit. Again, the star, the asterisk, will mask over that and just say followed by anything. So you'll see, when I do four question mark, question mark, question mark, and click OK, you'll see that we get our chart of accounts back.

Now, how do people typically use the question marks? What we found in the past is, for some setups, what I would consider more older-style setups, where you've got things like departments physically encoded into the GL code, um, you know, do you remember old systems where you'd have a 12-digit GL code? You'd have a company followed by a, um, department code, followed by the genuine account code, all of which were four digits each. So you ended up with a 12-character, um, GL code.

Um, in that type of scenario, it was really helpful to be able to mask off parts of the GL. So, for example, it's not going to work here, but in an example where you had a 12-digit code, you may say that I want to work with Company1, which would be my company code, followed by any Department code 1, 2, 3, 4, followed by all of my four codes.

So, where you've got a very complex GL code, the question marks were super helpful because if you just did 001 star, 4 star, it would pick up all sorts of codes, whereas you can literally say I want to mask off, in this scenario, Company1, any Department code, because I know I've got a four-digit Department code followed by my natural GL code starting with a four.

We've also seen it where, um, you may have some analysis tacked onto the end of your GL code. Maybe you do a spot one, spot two, spot three, spot four types. And in that scenario, it might be quite useful, where, say, for example, you had a three-digit GL code followed by a DOT, followed by a random single character, whatever that might be. Again, that's a way of doing a single placeholder character, so it would have to be a single digit as opposed to any number of digits.

Um, oh, and there is one other scenario I've just thought of. We do have customers that use, for example, 4,000 to be their sales code, but then they might have, um, all of their subcodes. So 4,000, 10, 20, 30, 40, 50. Rather than doing them as, um, 4,000 for sales and then underneath those 4,10, 4020, we've actually seen customers set it up where you go 4,001 on the end, 4,020 on the end. You can take advantage of that with the question marks because you've got one, two, three, four, five characters after the four. So if I did one, two, three, four, five characters after the four using the question mark, that would exclude from that the 4,000 code, which may not be relevant for that particular report.

But if that was just a grouping construct, that would actually then disappear from the list that's being returned. That's a great way of saying I specifically want these individual characters in these places, and then Solution 7 will, um, service those up accordingly.

Again, I hope that makes sense.

Evie: Thanks. So, we've just reached the top of the hour. The session always flies by us so quickly.

Simon: They do, and we never get through all of the questions, unfortunately. So, I can see there's lots of questions in the Q&A box. So, um, again, anything that we find in there, we'll try to roll forward to the next session. So, apologies if we haven't answered your question.

Evie: Yeah, you can always email us as well, guys, if your question can't wait till next month. Um, so last one I've been asked for a few times, kind of doing my normal day-to-day: will we be attending Suite World?

Simon: Oh yes, quick plug. Suite World is only two weeks away now might even be less than that. So, Suite World is a couple of weeks away. Um, for anyone who's not going or for anyone who doesn't know about it, it's in Las Vegas, 9th to 12th September. Um, I will be there walking around in my black and orange shirt. Um, Zone, who we are now part of, do have a booth in the expo hall. So, um, if you want to come and say hello, um, please drop by the Zone booth. I will either be there, or I will be working my way around the expo hall, or I will see you in a bar somewhere. But, um, yes, I will be at Suite World, and yes, please do drop by the, um, Zone booth and say hi.

Evie: All right, guys, well, thank you for all your great questions, and as always, thank you, Simon, for answering them. Um, as we said earlier, if we haven't been able to answer your question, you know, please do send a request to support, and someone on the team will reach out. We hope you enjoyed today's session. If you have any comments or feedback regarding the webinars, we would love to hear from you. And, as a final reminder, the recording will be sent out to you in a couple of days, so watch out for that. Thanks again for joining, and we hope to see you at the next one.

Simon: Thanks, everyone. Bye-bye.