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

Evie: Hi everyone, welcome to another Ask the Expert session. Thank you for joining. My name is Evie, and I am the Customer Success Manager here at Solution 7. During these sessions, we'd like to give you guys a chance to ask questions and have them answered by our resident expert, Simon. We really want these sessions to be driven by you, so please blow up the Q&A box. It'll be open throughout the session today. We often receive far more questions than we can answer, but we will try to answer as many as we can during today's session.

Um, all right, so let's have a look at the questions. The first question is from me. It's Solution 7. Um, so Simon, will this session be recorded?

Simon: Uh, yeah, absolutely, Evie. And just to say hello, everyone. Um, yeah, this session is going to be recorded, and we will bag up the recording at the end of this session, um, tidy it up, and hopefully get it out to everyone either the back end of this week or early next week.

Evie: All right, to kick us off, uh, we do have a few questions that you guys have submitted, so thank you for that. Um, number one: would Solution 7 work to produce consolidated reporting across multiple NetSuite instances?

Simon: Oh gosh, that's a good question. Um, um, the answer to that, I guess, is yes and no. Let me delve into what I mean by that. So, um, I'm sure everybody knows if you have a OneWorld instance of NetSuite, you have the ability to report by subsidiaries. So we can do consolidated reporting at the subsidiary level. Let me insert let's have a look at this demo company and let's have a look at the list of available subsidiaries that we have in this particular company. So, in this particular instance, we have a consolidated entity, we have a parent US, we have an eliminations company, and then we have our individual, um, countries.

So, um, the first, um, comment about consolidation is yes, absolutely, NetSuite OneWorld can do consolidation. Um, and we lean on the NetSuite OneWorld functionality to allow users to, um, to consolidate within a single instance of NetSuite.

Now, um, for anyone who has, um, dug, um, deeply into our configuration screen, this is where we determine which instance of NetSuite we're talking to. Now, in theory in theory you are able to set up multiple connections. And I say in theory because currently in Solution 7 for NetSuite, this is not supported. So if you do set up an additional connection, we don't currently allow you to talk to it.

So, within our base framework, yes, we have the ability to connect to multiple backend systems. Um, again, we don't currently have that available in, um, the current version of NetSuite. If it, if it's something that there is demand for and customers start to ask us for, it's certainly something that we could add to the roadmap. Um, I don't know how it would work from a licensing perspective, but if you could, um, please feel free to drop an email through to support or into the Q&A box if it's, if it's a feature that would be useful and, and connecting to multiple instances simultaneously, um, would be of value. Again, I don't know how licensing would work because I think you are limited to one instance of NetSuite per Solution 7 license, but again, we could I'm sure we could figure all that out in a future release.

If you did want to consolidate across different companies, um, how I would probably do that at the moment is let's pull in, um, let's pull in a simple example report. But here's a simple example report connecting to HH Inc. So I would have to know that this particular sheet, um, could work with, um, one particular instance of NetSuite. So let me just refresh that for you.

Now, if you wanted to connect to a separate NetSuite instance to be able to then consolidate the numbers, what you can take advantage of is our locking feature here. Now, you've got to remember that all NetSuite formulas that we provide are live formulas back into NetSuite. So if you were to attempt to connect to a NetSuite instance that wasn't compatible with the way you built your formula, you will get errors.

But what you can do is you can apply this locking mechanism to lock the formulas and to prevent them from being recalculated. So, for example, let's lock the sheet and click OK, and you'll see what we've done here is we've actually re-encoded the formulas in such a way where Excel will never recalculate them. This sort of IF statement prevents it from going down this half of the formula, which means because it will never recalculate, you'll never get any errors.

Now, if I were to make a copy of this sheet, effectively what I could do is build up a sheet that's connected to another, another instance of NetSuite. Oh, didn't think I'd be able to get quite enough words in there. So that would, that would be connected to another NetSuite instance that would have its own formulas for doing any consolidation work you wanted within that particular instance of NetSuite.

And then what I would do is, using our configuration file, I would have you can see here I've got lots and lots of configuration files set up for every different instance of NetSuite we need to connect to. Um, you can set up a connection file. It contains all the details that we need to know to connect to an instance of NetSuite. You could have one connection file for one instance, another connection file for another instance, switch between those two instances, and then as you're in the instance that you want to refresh, I'll give you a sneak peek into a feature that's been added I think to the next build. I don't think it's currently available. I know it's in my build here, but it's in a future build.

When I go in and refresh a sheet, I now have the option as part of the refresh mechanism to include locked formulas. So I can refresh this sheet, include locked formulas for the current instance of NetSuite that I'm connected to, hit OK, we'll refresh that particular sheet, leaving alone the previous sheet so that it doesn't go into an error state.

So this can still, um, have formulas that are valid for one instance. This can contain formulas that are valid for a second instance, which means that within Excel I can now aggregate these two sheets together to create a consolidated view across the different companies.

Um, again, be aware, I do believe our licensing does tie you down to one instance of NetSuite. I'm not, I'd have to double-check that though. So, um, you know, talk to us if this is something that you need to do. Again, we can dig into that for you and find out how that would work from a licensing perspective. But effectively, yes, you can connect to different instances of NetSuite and currently aggregate them, um, through different sheets.

Um, what I would also say, um, this locking mechanism, um, another reason that we've added this locking mechanism here is for performance reasons. Um, I'm sure a number of users have built very, very large workbooks, and I'm sure you've been there before where you load up a workbook and then you got to wait four or five minutes while all the formulas refresh in that workbook. That can get annoying.

Um, the way we've, the way we're looking to help work around that problem is, again, taking advantage of this locked formulas approach, the idea being that because this is a locked sheet, again, this cannot recalculate. So when I load up the workbook, it's not going to automatically trigger a refresh. What you can now do with this locking mechanism is you can actually select individual cells and just refresh those. You can select the sheet that you want to refresh, or you can select the entire workbook. And you'll see when I do that in each case, you get the "Include Locked Formulas" tick box appear. Again, I don't know if that's available in the current download or not. Um, we, we'll double-check that and have a look. I think it might be reserved for our next release.

But again, if you, if you're in that situation where you load up a workbook and you kind of get a bit fed up having to wait for those formulas to refresh, that's a great way of just calming Excel down and calming your workbooks down so they only refresh when you want them to, rather than when Excel chooses to refresh them. Hope that answers that question.

Evie: Thanks, Simon. All right, our next question: how can we retrieve amounts for NetSuite parent accounts that include the sum of the amounts of the children for the parent GL?

Simon: Oh, okay. Yeah, let's do that. So personally, I like to use account numbers rather than account names. Um, and there's a reason for that. You know, you, you have a, you have a chart of accounts. Oh, that was silly, wasn't it? Let me try that again. You have a chart of accounts to dictate the shape. Why can't I type a four? There we go. I'm going to pull in all the four codes here.

By the way, I've just simply inserted a list of accounts by number. So you, you have a, you have a chart of accounts to try and reflect the shape of the business that you're trying to represent inside of, um, inside of, um, NetSuite. Um, I believe the way that in our chart of accounts here, the way these codes are set up Um, I believe that the 402 through to 408 codes are actually children of the parent codes. Now, there's nothing stopping you just simply using our ABAL function, which is going to return an account balance by an account number. There's absolutely nothing stopping you pulling a formula back, or pulling a value back, sorry, where you simply choose the account you want, so the parent and the child.

So I'll show you that here. So if I pull my four codes in and let's say I wanted to pull in Sales, Merchandise, Service, Clearance, Warranty, I can just hardcode those directly into the formula. That would, that won't automatically how can I best describe this that will pick up the child accounts because I've asked Solution 7 to pick up the child accounts.

And why have we got an error? Oh, because I haven't put a period in. Let's do Jan 2023. That's one way of doing it. If you want to reference those account codes and you want to just select cells off the sheet, you can do that as well. So again, you're not limited to just hardcoding those values. You can just select the range of cells. That's going to give me a range of account codes 4,000 through to 408.

Um, you can also do it by typing them in onto a single row. Um, that curly bracket notation is a notation that we call an array. Here, we've hardcoded the array physically into the formula. Here, I'm simply going to reference a cell that contains that array syntax. So let's just do that. So, you know, you can easily do it through the account code.

Um, the reason I mentioned names earlier is you can also achieve the same thing by using account names, and it's, it's through the, the account name that's this option here. I'm going to insert a column of accounts by name. It's the account name that NetSuite uses to encode that parent-child hierarchy. Let me find some codes that we can work with. Let's find Income there's Income there.

So you'll see when I insert these, there, can you see we've got the parent-child hierarchy embedded into the account name itself? So Clearance, Merchandise, Service, and so on. That colon there represents the parent-child break. So what that allows me to do is to use the NBAL function, which you can see here, is going to return a GL account balance by the account name rather than the account number. So you can use that to actually navigate the hierarchy.

So again, let me, um, code up the formula for you. So I'm going to do consolidated for the account. I'm going to use Sales in fact, I'll type it in. Let's do Sales. And I'm going to add an asterisk onto the end of the word Sales. So in this scenario, it's going to pick up our Sales code. The asterisk, being a wildcard, is going to pick up our Sales Clearance, Sales Merchandise, Sales Service, Sales Warranty.

Now, if you only wanted to pick up the, um, child account and you didn't want to include the parent, you could do that structure, which is saying pick up all the accounts that start with Sales, then the exclamation mark, then the, um, colon, then the asterisk. That won't include the parent code; that would just pick up the child codes.

Now, one word of warning here is if you've got the word in this scenario, if you've got the word Sales at the beginning of multiple account codes, it will aggregate codes into the balance that you may not want to pull through. So again, you just got to be cautious there in terms of how you code this up.

Uh, one neat way of actually getting to the bottom of what those values are you can just use the drill down. In fact, if we get a number back, let's have a look at the drill down. Um, so now I'm going to use Sales and the asterisk, so pick up my Sales code and all the children, all the child accounts of the Sales code. I'm going to do that for, again, 2023. Click OK. Hopefully, we get a number back, and this is a great use of the drill down. I'm going to, simp, let's format that number. I'm right, going to right-click on that number, drill down, and if we drill down on it by account, you'll actually see the amounts that make up that 3.3.

There we go. And there, you can see we've pulled the Sales, the Sales Clearance, Merchandise, and so on. But again, interesting here can you see how we've accidentally picked up Sales Discounts and Sales Tax Payable? Now, how can we deal with that problem? So let's deal with that problem quickly.

So how I would now deal with that problem is I'm going to go back into my formula again, and I'm actually going to code this up as an array, and I'm going to say pick me up the code Sales and also pick me up all of the remember, we can use the colon all of the children of that particular Sales code. Again, click OK. Hopefully, we get a different number back. Yep, 3.48 this time. And when I right-click and drill down on that by account, there you can see we've only picked up the Sales code because that was the first entry in the array that we asked for, and then with the colon, we said pick up all the child accounts.

So there's a really neat way of, um, navigating the hierarchy using the account names.

Now, one final word of warning when it comes to building reports this way: just be aware that these names, unlike account numbers, which are pretty much set in stone which is why I like the account number because it's a code and codes generally don't change because this is simply just a name, there's nothing stopping someone with the right access rights going into NetSuite and changing the name inadvertently without realizing that those names are actually quite critical for running some of your financial reports.

So it's really important that if you are going to use the name and you are going to use the hierarchy, that you lock that functionality down, or you make people very, very well aware that if they do go in and change any of the account names, they might break some of the reports that you've built out in Excel.

Evie: Thanks. All right, we have a question about combining financial data and statistical data. How would we do that?

Simon: Oh, okay. Yeah, let's do that. So financial data is easy. We just oh, I'm already in that report, aren't I? Let me just tidy up, tidy up my mess here. Let's go back, let's, uh, clean that up.

So here we've got an income statement that's financial data. Um, how could we integrate that with some statistical accounts? Well, let's insert a column. Although I have to confess, I don't know if we have any statistical accounts set up in this, um, particular instance of NetSuite. So let's, um, let's figure that out as we go. Nothing stopping you, um, using our statistical functions.

Now, if you're using the most up-to-date version of Solution 7, what you'll find is that the statistical fun, the statistical, um, the statistical accounts can be accessed using the Advanced Balances, and we have the ability to report advanced balances by statistical accounts, or statistic that's a mouthful statistical accounts by date.

Now, what's the difference? Again, going back to our naming convention as I work my way down, you can see that we've really only got three functions available. We've got ABAL, NBAL, TBAL, ABUD, NBUD, TBUD, AP BAL, NP BAL, TP BAL. Let me just explain the difference with those.

ABAL returns a GL account balance by the account number, and these are one of our older original functions that we shipped with, um, Solution 7. NBAL does the same but does it by the account name, and we've just been through that as part of the hierarchy example. TBAL does it by the account type.

And for every category that you go into, you've got a BUD, NBD, TBUD, AP BAL, NP BAL, TP BAL, and so on.

Now, when you go into the Advanced Balances, what does the P stand for? That's going to do it by a period, so by a financial period, which is why it's AP BAL, NP BAL, TP BAL, where we're reporting by date. So by transaction date, it's a D instead of a P: AD BAL, ND BAL, TD BAL.

And just to go against everything I've said, where we say there's three functions in every category, when we get to statistical, we've only got two. Now, why have we only got two? Well, statistical is a type of account; it's a statistical account. Therefore, we don't need the option to do it by type because there is only one type of statistical account.

Again, we've got, instead of GL, the ST function. So NetSuite Statistical AP BAL the account period balance, where those statistical accounts have been posted to periods. Again, we can do it by the name of the statistical account, and again, we can do it by the dates: AD BAL, ND BAL.

Now let's go in and see if we can pull a balance, and this is where I don't know if we've got any data in for statistical accounts.

So, let's have a play. Let's have a look. I bet this is going to come up empty, or it's going to pull up the original chart of accounts. Yeah, it's going to pull up the full chart of accounts. Uh, let's have a look. Do we have a statistical account or anything that looks like one? No. Okay, let's have a snoop in NetSuite and see what we can find.

Um, let's have a quick, let's have a quick look and see how we turn on statistical accounts. Um, so anyone who doesn't make use of statistical accounts within their NetSuite instance, um, think of them as just a way of storing non-financial data. Um, examples that we've seen are headcount, uh, maybe departmental headcounts, maybe location headcounts, maybe square footage if you're a retail environment. Um, um, if you're selling, if you're selling things by, um, by, by some sort of quantity metric, or some other metric that's not, not financial. I'll say quantity is probably a bad idea because you can add quantity to transactions. Um, um, I don't know, electricity, kilowatt hours, I don't know. Um, you can store those against a statistical account.

Now, how do we do that? So the first thing we need to do is make sure the statistical accounts are turned on. So let's go into Setup, Company, Enable Features. Now, I believe statistical accounts are part of Advanced Financials, so let's actually see if they're turned on in here. Can I see it anywhere? Multi-customer, multi-projects, classes, departments. Let me just do a quick search, Control F that? No. Let's have a look under Accounting, see if it's there. There we go, statistical accounts.

So you can see in this particular instance of NetSuite, it's not even turned on. So let's turn that on and save it, and let's go into our chart of accounts and see if we can create a statistical account. Chart of and let's insert a new account. We can't have any accounts in there because we haven't actually turned the feature on yet, so let's insert something.

Now, the trick here is going to be the account type here. So let's just double-check that statistical is available to us. There we go, we've got statistical accounts available to us now. So let's call it Headcount. Oh, no, don't want to do that. Let's call it 99999999 that'll do for the account number. Let's call it Headcount. I won't make it a sub-account of anything. Let's call it… let's call it a type's creator of type Statistical.

Now, you remember I said this is for non-financial data, so we can do unit types. So I don't know, each, maybe it's FTE if you wanted to store people. Um, all the other functionalities there, for, um, that, you can set up any other account within NetSuite. Let's have a look at that and see what that looks like when we hit Save. I guess it's going to be right way down the bottom. Uh, no, it's not there. Let me see where statistical accounts appear. Create a statistical schedule, import statistical journal, statistical journal entries. Uh, I was expecting that to appear in my chart of accounts in here somewhere, so I don't know why that's not appeared. Can I see it anywhere? Nope.

Don't worry, let's, let's try posting a statistical journal, shall we, and see if we can do that. So let's go in there. We are going to make a statistical journal entry, and it's going to be each into ink, and do I get choose the account? There we go, Headcount, and the amount is, let's say, a thousand, something's in there, whatever that might be. And I'm guessing it needs to balance. Maybe it doesn't. Let's try saving it. I'm guessing, being a statistical account, it doesn't actually need to balance. This has gone into November 2023. Let's see if we can now pull that value out. Okay, there we go. So that's confirmed.

So let's go back in here. I'll be very impressed if this works first time. So let's go into here, let's go into the function button, let's go into statistical accounts. We're going to use AP BAL because we know the period that we just posted that balance to. I'm going to choose Consolidated as the subsidiary. Uh, for the account, I'll choose my… that's interesting, it's not appeared in there. Why is that not appeared? Ah, refresh, try that.

So we do, again, just as a, as a side comment here, we do cache a lot of information whilst the software is running and also between sessions as well. So if you do see a list and it doesn't look up-to-date, you can always hit the refresh button to pull the latest data set through. There's our new Headcount account code. Let's click on OK. The period I seem to remember it was November 2023. Let's click on OK, and fingers crossed we get a thousand something through. Phew, there we go.

So you can see how super easy it is to add statistical accounts into your NetSuite to start recording statistical information into your NetSuite. And you can record that either as a statistical journal, or you can actually budget against statistical accounts as well. So you could actually set up different budgets, different budget records in NetSuite and store your statistical amounts against those budgets.

So if you've got some sort of versioning across the period, maybe, maybe you're forecasting headcount and you want somewhere to put the results of a calculation, you can put those into your, um, into your different budget categories: um, a budget, a forecast, a reforecast, and so on. Um, and then you've got the ability to store different versions of the number against the same period, against the same statistical account code, and then you can see how easy it is to pull those through and onto your reports.

So just to say that again, the best way to pull those statistical accounts: use the Advanced Balances, statistical, and in this case, I've used the SNSS AP BAL function, the Statistical Account Period Balance function.

Evie: Great, thanks, Simon. All right, so next reporting question: how can we grab reports from NetSuite via Solution 7 by customer and vendor?

Simon: Okay, so grab reports from NetSuite by customer or by vendor. Um, we don't integrate directly with NetSuite's reporting engine; we just talk directly to the database. So if you want to report, um, if you want to do reports by vendor or by customer, um, the best way to do that, I think, is to use our integration with Excel's pivot table engine.

And in this scenario, I'm going to use what we call a flattened pivot. Now, just to explain the difference between a regular pivot table and a flattened pivot: um, if you're not running the latest version of our software, I don't, again, I don't remember when we started to use this language, um, you might see detail and summary. Um, the reason we've switched away from detail and summary to pivot table and flattened pivot table is we're trying to use more Excel language than, than language that we've made up.

So we've, um, a pivot table is just a regular Excel pivot table. Um, a flattened pivot table is essentially just a pivot table but with all of the pivoting functionality turned off. So you see it as a flattened-out transaction list. Um, it's used I think, I think Power Query uses that language. So, um, we felt it was a better, a better way to describe, um, what inserting this type of pivot table is.

Now, again, it's really important we're using Excel's pivot table engine here just as a layout mechanism because it's easier just to say, hey Excel, you lay this out for us, we'll grab the data, but you throw it into the sheet for us. Um, flattened pivot table, let's insert a list of posted transactions. So, um, as you know, we're a very transaction-oriented reporting engine. Um, so the starting point for everything we do is some form of transactions. So here, it's going to be posted transactions.

Um, let's have a look at, um, customer-type transactions. So we'll look at the income side, um, of the GL. Notice as I start to fill out the details that I want to pull back from NetSuite, it's the same process as if you were inserting a formula. So absolutely nothing complicated about this at all.

For the accounts, um, let's go to that four-range again that we used, um, in earlier examples. So here I'm going to be looking at income postings by customer. So this will be, um, effectively broken down by customer, and each of the income rows, that's hit them either via a journal, um, for something like deferred income, for example, or maybe they've just been invoiced directly, um, direct, or maybe you've raised invoices directly against those income codes.

Let's run it from Jan through to December. Going to click on OK, and let's see what we get back. There we go. So we've got a load of cash sales coming back, got a load of invoices coming back. Now, you can see that here we've just used a very, very basic pivot table to represent that data. But let's say we wanted to tidy up that data and, and show it in a slightly different form. Maybe we want to group it, for example, by customer.

With any of our pivot tables, we're thinking now about the source data and the data we want to pull through. If I right-click, I can go into this Edit option, or I can go into the Choose Columns option here. And just to say, if you go into the Edit option, you can change the parameters that you're pulling the data back through with. So if you want to filter the data differently, you've also got that route back into the Choose Columns screen, which is why I wanted to show it to you through the Edit screen. This will take you to the same screen that I'm just about to show you.

If I right-click and go to Choose Columns here, I've got full access to the GL. So these are the transaction lines that have been posted. You'll see if I go into more, we've also got access to all of the other tables that are related to those transactions.

So, because this is the invoice, because this is the transaction detail, we can hop up to grab anything off the transaction itself. If you want to look forwards or backwards through the flow of your transactions, you can also get to linked transactions. So say you're looking at sales invoices and you want to get back to a purchase order, that would probably be a linked transaction previous from the sales invoice back to maybe a purchase order. Maybe you're going from an invoice to a payment. There, you could use NetSuite, for example, because we've started off on the income code and we're going to the next part of the process, which would be the payment. So you can jump backwards and forwards there.

Um, and you can also see we've got access to the customer record. Um, I like to use this thing called Entity or Related Entity. Um, what that is, it's effectively the third party that is related to this transaction. So if we were looking at an, an expense report, it would be the vendor.

If we were looking at payroll journals, it would be the employee, because we're looking at income. The related entity is going to be the customer, so we don't need to specifically go into the customer, um, option here. We can just go down the related entity path. Let's pull out the entity name, just literally drag and drop. If I double-click, I have the ability to tweak how the data is grouped and sorted sorry, how it's grouped. Sorry, um, so I can say, for example, for the cust, for the entity, for the customer, whether I want to group and show the detail or group and hide the detail. And for any other field that's lower down, I can choose an aggregate, for example, of the amount here at the bottom, where I'm choosing to sum that amount.

So if I group, let's do Group and Hide the Detail. So if I group and hide the entity name, summing on the amount, we should get one line or one group per customer, with the detail hidden and with a total for each amount. So let's click on OK and have a look and see how that comes through. There we go. Have to just resize the sheet a bit. There we go. So let's pull those numbers out.

So here we've got something that's not posted to anything, but here you can see the customers have started to flow through. These are their income amounts because we pulled them from the income line. Notice they've all come through negative because we're looking at income. I'll show you how to flip the sign on that in a second. Um, because we chose to group and hide the detail, I can actually go into each customer and expand the detail. It's a really great way of being able to look at customer data and, and do whatever form of analysis you want to on your customer data, vendor data, whatever.

Let's just deal with that minus sign. The simplest way to fix that is, if I go back into Choose Columns, something that a lot of people haven't spotted is you actually have the ability to write your own formulas. So if I pull down a formula and drop it in here, I can actually say that I want that formula to be equal to the amount. Now, what syntax do I use for this? Well, here I just simply, inside curly brackets, type in the name of the field that I see here. So I know that if I want to reference the amount, I just simply put it in curly brackets and the word Amount. And if I want to reverse the sign, I could probably just type in a minus at the beginning, or I can do times minus one.

And let's call this, um, Amount. I'm going to call it Amount Reversed just for this example. Click on OK. I'm also going to, I'm going to sum that as well. So let's add a sum to that column. Click on OK. There we go. And you can see that in this scenario we've got the negative amount coming through and the positive. And I'm guessing if I go back into Choose Columns and take out the original amount column, this is either going to generate an error, or it should just get rid of the column for us. Let's see. There we go. So we've got those values coming through for each customer, and we've reversed the income amounts to show as positive balances using that formula.Hope that answered the question.

Evie: Awesome, thanks, Simon. While we're in the pivot tables area, do have a couple of questions about some fields that we can find in the Choose Columns. I've got one about finding the memo field off of the transaction header rather than the line.

Simon: Oh, let's have a look. So finding the memo on the transaction header, let's see if we can find it. So by default, we're in the transaction detail. Let's scroll down, go to More Information, go to the transaction. There it is. Drag it up. Click OK. And I bet you we've got nothing posted into it. Let's have a look. Yeah, we've got nothing actually posted into ours. I guess just 'cause it's demo data. Oh, there we go. We got some value coming through there. So those have been pulled off of the header memo. Let's compare that to the… oh, there's the line item memo there. So let's actually compare the two side by side. Yeah, there you go. So in the invoice header, the memo's been populated, and in the invoice detail line items, the memo has been populated with a different value. So yes, absolutely, you can get to the invoice header memo field.

Evie: Fantastic. Okay, we have a question about showing the original value of a transaction in the transaction currency.

Simon: Showing the original value. Okay, well, let's come at that first of all from the pivot table perspective. So let's go into Choose Columns again. Now, the amount that we've pulled through, by default, is the amount in the reporting, is in the context of this particular report. So let me dive out and just explain the currency side of it.

So because here we've said HH Inc Consolidated, um, all of the amounts are being converted into US dollars. So these are consistently giving us USD amounts for this particular query, and that's down to the context of the subsidiary. If we go into Choose Columns, we should have access to the foreign amount. So that will be the amount on the, um, that would be the document amount, or, sorry, yeah, the currency amount for the document. So for example, if it's a US dollar invoice, it'll be the USD amount. If it's a GBP invoice, it would be the GBP amount.

Now, I'm guessing if we go down here, we should have access somewhere to the currency. Do we have access to the currency up here? Nope. Let's see, maybe it's off the transaction record. Let's scroll all the way down. Notice how I can just keep going, going, and going until I find… There you go. There's the currency. So I can actually go into the currency, and I can pull the currency name through. So this is going to tell me the currency of the document and the amount that was posted in that currency, as well as the consolidation, um, currency amount. Let's click on OK and see what that does for us. There we go.

So let's see what this was brought back. Uh, probably fine. We don't have much in the way of currency. So there's the sum of the foreign amount. Do we have any foreign C…? It's all collapsed down, isn't it? Let me, let me, let me open this up because we'll be able to see the data a bit better. So let me go in and change that from a Hide Detail to a Show Detail. There we go. Let's collapse some of this back down. So there's our USD, or USA in this case. Do we have anything in anything other than US dollars? There we go. We got some peso documents coming through. So we've got the… we've got the peso amount.

What have we got to the right of that? Some amount reversed. I'm looking for values that are different here because they're coming through the same value, which doesn't look quite right. I would have expected those to have come through as different values, but anyway, that… that's how you can pull the… um, that's how you can pull the… let me get rid of that formula and pull the original amount back through. That's how you can pull through the different, um, currency amounts at the document level.

That's interesting. It was because we were using a formula that it was getting it wrong. So that's something we're going to need to investigate. But you can see there we've got 3 million peso, which is converted to whatever that number is in US dollars, $192,000. So there we've got the side-by-side amounts.

Let's do that through a formula. So let's go in here, and let's insert a formula. Now, I don't know my data particularly well, so we're going to have to… we have to do a bit of searching to find a balance here. Um, you get to the foreign currency, um, or, sorry, the… the… being able to look at the document currency is implemented through the Advanced Balances function. So I'm going to use the AP BAL function to return a balance. Again, let's use the subsidiary to filter the company context. Let's use our friend, the four-star range. Let's do Jan 2023 through to December 2023. Um, and with the Advanced Balances, what we have is a series of option-value pairs, and that allows us to choose… that allows us to choose, um, um, how we want to either override the filter or override the way the calculation is performed under the covers.

So, for example, here I can pick the transaction currency, for example, and I can say, give me all of the transactions across my Consolidated company, give me all of the transactions that are in Mexican pesos, for example. So that will add up all those amounts in Mexican peso documents. It will add them up and give us a Mexican peso balance. So unlike the pivot table, which kind of switches between a bit of USD, a bit of peso, a bit of Canadian, a bit of Euro, and so on, here we have to specifically say, give me a balance in a document currency for this particular currency. So, for example, there we've got MXN. Here we might have GBP. Here we might have EUR. And I can go in there and tweak my formula and ask the balances in the different currencies. So that's in peso, that's in GBP, and that's in Euro.

So just be cautious. Don't go adding them up because you'll get some strange number that doesn't make any sense to anybody.

Evie: Thanks, Simon. All right, so we've got a few questions in the Q&A box about doing AR and AP aging reports.

Simon: Should we have a quick play with AR and AP? So AR and AP are, again, implemented through the pivot table. This is turning into a bit of a pivot table session, isn't it? So let's have a play with, again, the flattened pivot table, um, aging here. This is going to pull back, um, um, open transactions that have been aged, and we can do that on an AAT basis as well. So let's run that through.

Let's pull an example through. So again, we're going to do Consolidated. Let's choose, for the account type, let's choose… let's choose receivables. Now, if you have a… if you have a larger instance of NetSuite where you've got multiple receivable codes, you can subfilter those codes out. So maybe you've got two or three different receivable codes, you may want to just isolate one or two. You can see here, in this example, we've only got the one CEL receivable code, so I'm actually going to ignore that parameter.

Um, down here we can now say… and by the way, we're trying to mimic NetSuite's internal aging report here in terms of the parameters that we give you. So aging based on, by default, is the due date, but it can either be on the due date or the transaction date, depending on when, where, how you want the transactions to be allocated into the different aging buckets. As of, again, defaults to today, but that means that you can age as of a given point in time. Aged as of is the date from which to age the transactions, from which to… oh, that's the point in time that you want to use as the aging point, i.e., not necessarily today. Maybe you want to age them from the beginning of the month, so what were they as at the beginning of the month in terms of using the first of the month for the aging point in time.

And then we've got the, um, standard dimensions of class, department, and location. So let's run that through and see what it looks like. There we go. Again, very basic view. By default, let's go through and tidy that up. As I've shown a couple of times, we can go into Choose Columns. We can go and grab, for example, the related entity or the customer, because this is, um, based on customers. So let's choose… I'll choose the entity name again. I'm going to double-click on the entity, and I'm going to hide the detail. I click on OK. You'll see it start to come together as more of an ARAP-type report.

So there's each of our customers, there's the amount outstanding. Let's do some more Choose Columns work. So let's go in Choose Columns, let's add some aging buckets. Sherice, so, um, aging buckets… do you notice how these are all just formulas? So what we can actually say is… well, let's add Not Due 1 to 30, 31 to 60, 61 to 90, I don't know, 91+, and I'll move the total down to the end. So you can drag and drop or just double-click, as I did there. Notice, if I double-click on a formula or use the properties button here, if I double-click on a formula on a column… sorry, notice that because this is a formula, I can… I can actually edit and create my own custom aging buckets. So if you don't like the aging buckets that we've provided, just edit this formula to create your own custom buckets. Let's click on OK. Let's click on OK again and see what that looks like.

There we go. So we started to see some numbers come through, and actually that's not a bad spread of numbers. So you can see there we've got a customer here, Better Buy, with a number of invoices that span different ages. We've got some hashes there. Let's… let's tidy this up and turn it into something really cool. So let's freeze the panes, which I think is that button there. Let's get those column widths sorted. There we go. So I've now got the ability to scroll up and down. There's my Better Buys flowing through with my totals on the right-hand side. There's the… there's the total for Better Buy, and we're on to the next customer or vendor, depending on the type of report.

So super easy to build a, um, detailed aging report. If you want to, you could pull that through. If you wanted to do a summary analysis, where you actually pivot your data, maybe you want to do, "What's my aging looking like by department code?" For example, you're not interested in the customer detail, you could use a standard pivot table where you put the department code as the top dimension and, um, maybe something else as the side dimension, maybe the GL code or the customer name. And then you can actually expand that out. So rather than it being in individual aging buckets, you can actually split those out by department or something cool.

Evie: Yeah, it's good. Somebody else put in the Q&A box asking if we would do a demo or webinar on pivot tables, so that works out for them. All right, so on the aging, Simon, uh, we have a couple of people asking about aging for inventory as well a stock aging table.

Simon: That's a really good idea. Uh, a stock aging table. Now, I'm not sure what that actually means, so if you could… if you could add some… whoever asked the question, if you could add some context to that. Are we talking about… are we talking about sort of the age that we've held inventory? So are we talking about things like LIFO and FIFO and how long we've held on to things, or are we talking about looking at items that we've sold to customers and whether those… whether those debts have been cleared for those items? Because I know we can do that just by going into Choose Columns. And if we've opened up the inventory table, which will be off the transaction line, we should have access to the item, item unit price… where's the item… we want the item name, which must be off here somewhere… there's the item.

So maybe that's an option. Maybe we could group it by item. Is that what we're looking for? Not necessarily sure I understand exactly what we're trying to show, so maybe that's an answer to your question. And here I'm showing it by dollar amount. Maybe you'd want to show it by quantity. Be useful to understand the context of the question: whether we're talking about how we've held the stock or how we've actually sold the stock.

Evie: Yeah, so we have answers there. It'll be the length of time the item has stayed in the inventory stock how long the items are being held.

Simon: Yeah, we don't currently have access to that, I'm afraid. Um, it's a good question, and we'll make a note of it, and we'll have to add it to a very, very, very long list of enhancements. So yeah, that's not something I think we have access to easily. Unfortunately, one for the roadmap.

Evie: All right, so got a question on seeing system information via Solution 7, for example, pulling all the accounts with a created date or last modified date or user modified date.

Simon: Oh, okay, that's an interesting one. I don't think I've been asked for that one before. Um, the easiest way to insert static data is there's a column, a row, or a pop-up. Um, at some point I'm going to get these integrated into the pivot table, because it would actually be really nice to use our flattened pivot table. So you insert it as a table rather than just as one of our lists. But you can go into Insert a list of… in this case, let's do accounts by number. Let's choose the full chart of accounts across all subsidiaries. Let's do all account codes. Let's go into our friend, the Choose Column screen, and let's see what we've got available: uh, last modified… do we not have a created date? Opening balances, debit, is credit, hierarchy level… these are all the fields that have been exposed so far, but it might be that that was all we had available at that point in time. Yeah, class, department, location.

So again, we need to make a note to investigate if there are other fields available to us through OBBC that we can open up additional fields on the chart of accounts, because this, at the time, is all we would have had access to when this feature was first added. So last modified date is debit if summary. Let's just throw a few things on and see what it looks like: restricted, department, type, name, type sequence.

So yes, it's available, or yes, additional information is available. We might need to go back and just scratch our heads on what we've made available through the account list. This account list was really originally written just to allow you to insert your chart of accounts for building a report. But yeah, AB… it's a… it's a great thought that if you want to use it to actually look at account codes and, you know, kind of do an audit on your account codes, this would be a really nice way of, um, getting into the, um, the additional fields that are on the account table. So that might be one, again, for the enhancement list. It's a relatively simple enhancement. We just need to expand out our data model as to, and, you know, what we show within NetSuite. Uh, sorry, show from NetSuite.

Evie: Absolutely. It's always interesting to see what people want to pull in.

Simon: Absolutely, yeah. Absolutely. But again, that Choose Column screen is your best friend. So if you want to go exploring across any of the objects that you can insert, just hit Choose Columns and just see what's available, and try pulling it through.

Evie: All right, another question on item, uh, details. Um, can we pull revenue and expense at the item level?

Simon: Can we pull revenue and expense at the item level? So again, going back into the pivot table, if I were to do… if I were to do Revenue by… let's do Revenue by item. So four-star Jan to December. Choose Columns. I think I've kind of built this already, but let's have a look and see.

So we're going into the item table. We're pulling through the item names. Can you see how we've got much more data that we've exposed here? So I'm sure some of that data that we mentioned previously we should be able to expose here: item name, because we're looking at the four-star codes, we're looking at income. So this should give us income between January and December by item.

So that's how we can do it through the pivot table. A lot of zeros coming through, that's interesting. Oh, these are just journals, so we can almost ignore those, I guess. Our data is not too… not too great. There we go. As we go down… there we go. These are the different things that we've sold, different SKUs, and these are the income amounts. And we could also pull the customer name through at the detail level if we wanted to see who we sold it to. I could throw it into a… let's do that actually. Let's throw it into a summary pivot. So let's… let's do a summary pivot this time, or posted again, same details, four-star Jan… let's go into Choose Columns. Let's pull through the item name, and let's also pull through… again, I don't know the data too well, but let's try pulling through the location code. So where's the location code? Gone, there it is, locations. And let's pull through the location name.

So now that we've got those two values available, we can actually use that as dimensions on our pivot table. So let's click on OK. Let's click on OK again. This is what we used to call the summary pivot. It's just a… it's just a regular Excel pivot table. We go, grab the data, drop it into the pivot table engine, and we can say, right, let's use the location in the columns, let's use the item names in the rows, and let's use the amount as the value. That's one relatively easy way of pulling those numbers through.

Let's just try and see if we can do the same using a formula. So I'm going to try Advanced Balances. So let's insert a column of items from the Accounting menu. Now we have no choice here but to pull all items through because the software won't know what, at this stage, we have or haven't posted to. So there's a list of all of our item codes. Let's now try using a formula. I'm going to try the Advanced Balances. Now, the reason I know to use Advanced Balances is ABAL, the original older balance function, is limited to just class, department, location. So I know… I know with the Advanced Balances, we've gone beyond that.

So let's go to Advanced Balances, AP Bal again, same old, same old. Four-star Jan, Jan 2023 to December 2023, and we're going to use our option-value pairs. So for option one, we're going to filter it by item. For value one, we're going to limit it to the item that's been inserted into the sheet. Click OK. And hopefully, if I double-click on the dot, we should get some numbers come back. There we go. Good. We've got some numbers coming back. So again, we're looking at income here, so they've all come back negative. Notice we've got a load of zeros again.

You can lay this report out, for example, where you've got periods across the top. So maybe you've got January 2023 as a heading, and then December… oops, and then, uh, sorry, February 2023, and so on and so on. And I've shown examples in previous Q&As where we use formulas to build out those column headings. I'll just live with… I'll stick with my January to December. Notice we got lots and lots of zeros coming through.

So this is a good, good excuse to show the zero suppression. Let's get rid of those zeros. Let's go… go to the zero suppression menu, hit the suppress button, click OK, and you can see we squash down all those zero rows. So these are income amounts by SKU or by item. And of course, we have to build out a much bigger report than we need because we've got to do it for every possible item. But here we've then squashed the zeros down, um, squashed the zeros down so that, um, so that we're not showing too many, um, too many dead items, or too many items that haven't been posted to.

I'm anxious, Evie, that we're getting to the top, or we've got to the top of the hour. So what should we do? One more question?

Evie: Yeah, let's go for it. All right, so I've got a question on reporting on multi-book. Yeah, how can we extract data for the entire year when part of the year is with one accounting book and the other part is with a different one?

Simon: Wow, they do like to ask difficult questions. Can you… can you go through that one again?

Evie: Yeah, of course. Um, so we've got the full year. Um, half of the year is posted in the primary book, for example. Yeah, and the second half of the year is posted in the secondary book. How come we report on that?

Simon: Good question. So let me try. I'm going to try the… so multi-book, I believe, again, is best implemented in the AP Bal function in the Advanced Balances. Let's try… let's try this as an example, 2023.

So I believe one of the options is the ability to choose the accounting book. There we go. We can choose which accounting book we want to report from. So I can say, for those months, for that accounting book, I want to look at the primary accounting book. H, what a shame. This has only got one accounting book in it. I believe you can actually select multiple accounting books, which means that if you've exclusively posted to one accounting book at the beginning of the year and another accounting book at the end of the year, and there is no overlap, you could select both books for that formula, and you would get a balance out.

Now, if you want to split that, what you may be able to do is have a formula that's just doing the primary book, and another formula that's doing the secondary book. Have this formula reference the primary book, have this formula again using that accounting book option and the name of the book, have that account reference the primary book, that reference the secondary book. You could have different financial periods for the financial period parameter. Let me actually go into the FX St… I'm… we look at it through there. Have different, different financial periods for each different formula. So you pull in one half in one… one half in the other, and then simply, again, I'll… I'll… I'll pretend that's what we've done here, and then simply add the two together.

Um, remember that… remember that Solution 7 is… is just Excel formulas, so I could either do that, or there's absolutely nothing stopping me. If I take that formula and throw it over here, there's absolutely nothing stopping me doing something like this, where I just add two formulas together in a cell, one looking at one book, one looking at another book, one looking at one date range, one looking at another date range, and then allowing Excel to just add those two together in a single cell, and you'll get an amount out.

So, um, I'm not an accounting book expert, I'm not an accountant by background. It feels like that would be a good way to do it. Um, I'd love to hear if that works. Again, again, please throw it into the Q&A if you think that's an approach that will work. Great. If anyone's got any other ideas, that would also be great.

Um, I again, I hope that answers your question.

Evie: Thanks, Simon. Yeah, we really do like hearing your ideas, guys. And if there is anything that you, you know, would love to build in Solution 7, but you're not sure how to go about it, let us know. We can always include that in the session.

Simon: And can I add to that, Evie? I'd also like to say, if… if… if anyone's got any ideas they would like to present themselves, we would be, H, very open to opening this Q&A session up to users actually demonstrating anything that they've done that they're particularly proud of, any ideas that they've come up with that they would like to show anybody else. Um, you know, we would definitely love to open this up to a wider… a wider group and have other people present, and just, you know, show the… the cool things that you can do, um, both in NetSuite and Solution 7.

So if you do have any ideas, please let us know, and we'd love to jump on a call with you and… and learn about those ideas and maybe schedule in a… a Q&A where, um, it's actually a customer-driven and a customer… a customer-run Q&A session. So please, if you've got any ideas on that, please drop us an email, or again, post anything into the Q&A box.

Evie: Okay, um, so that'll bring us to the end of the webinar. Run a little bit over there, but there we go, more value out of that. Um, thank you everyone for submitting your questions. They were all really great questions. Um, I'm sorry if we didn't get to answer some of those during the session today. Um, but we will, um, include them in a future session. Um, so, you know, keep coming to our webinars. We… we're enjoying them. Um, if you would like to organize, um, a separate call to answer your questions, you know, you can also ping an email to our support line. Um, I don't… if we have that on… on the screen somewhere, Simon, could we show that?

Simon: I don't know if I've got it in my side deck. There we go. Support. Perfect. So yeah, please, if you want to email anything offline, just email the support team: support@solution7.co.uk. That's typically monitored by Evie or Maria. Um, brilliant.

Evie: Yeah, so we really want these sessions to be, you know, informal and enjoyable. So I hope you guys enjoyed this as much as we did. And if you have any comments or feedback regarding these sessions, we would love to hear from you as well.

Um, just as a final reminder, the recording will be sent out once it has been, um, all kind of put together with our marketing people, and we will email you guys with that. Thank you for joining again, and we hope to see you guys at the next session.

Simon: Perfect. Thank you. See you next time. Thank you, everybody. Bye-bye.