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

Evie: Welcome to our first unscripted ask expert session. My name is Evie. I am the customer success manager here at Solution 7. We're welcoming back Simon, our CTO, to go through the session with us. This is a bit of an experiment, so bear with us, and hopefully it all goes well. If it doesn't, we can blame Simon for his bright ideas. We want these sessions to be driven by you guys, so if you do have any questions, you know, please submit them in the Q&A box down in Zoom. We've disabled the chat, but if you do want to submit questions, please do so in that Q&A box. 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. If there's any we can't answer, we will try and answer them in a future session.

To get started, we do have a few questions carried over from our previous webinar. The first question is from Evie at Solution 7. Oh, that's me. Question: Simon, will this session be recorded?

Simon: Absolutely. This session will be recorded, and probably now, sometime next week, a copy of the recording will be sent out to everyone that registered. What a good question to kick us off with, Evie

Evie: Thank you very much. All right, so question two we have from Kieran at TSO, submitted on our last webinar: is it possible to use custom n nominal account fields to create a summary P&L format?

Simon: Oh, yes. Um, let me, let me walk you through what you can do. Um, and given this is an unscripted session, it's almost like we planned this first question. Um, so, so the question is, what we're looking to try and do is, um, deliver a report summarized at a level that goes beyond the chart of accounts. So you have individual account codes, you can use wildcards to group those account codes together, but there are other ways of grouping account codes together to give different levels of, of summarization. Um, to do that, um, I'm actually going to suggest one option is to use custom field, uh, custom segments.

So here's, here's a demo NetSuite instance that we've actually created a couple of custom segments in. Um, for anyone who's never used custom segments before, um, don't be afraid of them. They're super easy to create. They're super easy to delete. So, so, you know, you can't really do any damage within NetSuite by creating segments. Um, obviously, by deleting segments, you may delete somebody else's that they've created, but, you know, they're, they're relatively innocent, um, uh, technology within NetSuite. Custom segments can be attached to a number of things: transactions, departments, locations, so any of the core records, any of the, um, satellite records.

Given that we want to try and use custom segments as a way of summarizing data, um, we've created a brand-new custom segment in our test instance of NetSuite called management accounts grouping. So, to do that, I literally went customization, list, records, fields, custom segments. Now, I will admit, I'm logged in as an administrator, so you may not see the same menu structure that I see here. Um, obviously, you can customize the menus in NetSuite, but you need to say, go into customize, some, some customization menu that you've been given access to: list, records, fields, custom segments. Uh, you then hit the new button, and from there, you can create a custom segment. As I said, I've already created this particular custom segment, and, um, you literally just give it a name, select a type. I've left this all as default, I think. Um, I've created it as a list record type. What that means is each custom segment is its own record, and that record can be attached through a dropdown field to the GL code or to the account code.

Um, if you mark the custom segment as GL impact, it will appear on transactions, and it can appear on a budget. So you can use that custom segment as a way of analyzing budgets and analyzing, um, transactions. Um, be aware that once you've ticked that box and saved it, it cannot be unticked. If you forget to tick it and create your custom segment and save it, you can no longer tick it. It's a real shame, but you have to get your custom seg right up front. Um, a custom segment in this case can have a set of values, and you'll see here that adding new values, I've, I've created them numerically so that I can sort them. You don't have to do that, but you can see I've created a balance sheet, an income, a COGS, expenses, and other. Um, these different custom segments can be created at any level, or, level, sorry, any, any level of summarization or any level of granularity that you want to create. So you could use them as individual, you know, against IND individual GL code. You could group lots of GL codes together to go into a single custom segment.

I'm going to save that, and now that I've got that in NetSuite, let's go through and have a look at how I can consume that in Solution 7. Now, the one thing I will say is if you want to use a custom segment, you do need to come and talk to us first, tell us the custom segment that you've created, you need to tell us where you've created it in NetSuite, and then we provide you with a customization file that brings that customization, that custom segment, to life. You won't see it without talking to us first. Um, if it's a complex custom segment, we may need to jump on a call with you. We may need to access your NetSuite so we can see how it's been built. But effectively, we can bring that custom segment to life for you.

Let's build a really, really simple report that leverages that custom segment. I'm using a OneWorld instance of NetSuite, so I always start my reports by inserting subsidiaries. So let's do that, and I'm going to insert a column, column list of our custom segments. So can you see here, I called it management accounts grouping? So that's actually appeared as a new menu in my list inside Excel. So let's insert that. In fact, I'm going to insert it underneath the subsidiary. So we're going to do a column of management account groupings. I'm going to leave all this blank because I just want to pull everything back. Again, we go off to NetSuite as always, grab the data, drop it in, and can you see there? There's that new category that I pulled in.

Now, this, this particular, um, this particular Q&A, um, or particular answer is not going to cover how to build a report from scratch. That's covered in other sessions. So I'm going to build a very, very simple set of balances, just looking at January 2023, and I'm just literally going to type in Period heading, and I'm then going to enter a formula. So let's do that. So we go into our functions. Now, to work with custom segments, you need to use these Advanced balance functions.

Now, to explain the difference between the standard balance functions and the advanced balance functions: well, fundamentally, the NetSuite balances, NetSuite budget functions, are the older functions that we supported when we first migrated our software onto NetSuite. The advanced balances have been around for about, I think, 18 months now. Um, they work differently internally. The balances talk to pre-aggregated data inside NetSuite. The advanced functions, um, talk to unaggregated data in NetSuite, so they don't run quite as fast as the original balance functions, but they do have lots more functionality.

For this particular example, I'm going to use the AP Bal function, and as always, we get the standard function arguments dialogue appear. Now, again, remember what we're trying to do here. We're trying to summarize at the GL code level. So I'm going to choose the subsidiary, and I'm going to lock my references into a single cell. Now, the, the great thing here is for the account, I can just enter an asterisk. So I can just say, look, every account, because actually the account analysis is going to be done later on in the formula. For the period, I'm going to select January 2023. And with the advanced balances, what you get is a series of option-value pairs. So unlike the regular NetSuite balance functions, where you see class, department, location, and so on, with our advanced balances, you see option one, value one, option two, value two, and so on.

So let's use option one to filter by the custom segment. So you can see that I've hit the lookup button, I scroll down, I choose my management accounts grouping, which is my custom segment in NetSuite, and then for the value, I'm going to reference the sheet. So let's just reference that, now again, lock in my references, click OK, double-click, format the numbers, and align them. Now you can see there, we've got a whole load of zeros come back. Why is that? We haven't actually taken the custom segment and applied it to any of the GL codes yet.

So the reason that we're getting this negative zero here is actually the whole of NetSuite has been aggregated, and we're left with a very minor rounding error. So let's now take some of those, um, custom segments and apply them to a GL code. So I'm going to go into charted accounts, and I'm going to scroll down, down, and let's add some P&L codes. So let's start in this 402 to 408 section. So we can edit the code. Once you create a custom segment, you get a dropdown appear, and I can go into that dropdown, and I can say, right, you are an income code. Now, again, I'm mirroring functionality that already exists in NetSuite. This is similar to the account type, but remember a custom segment can be anything you want it to be. So although I'm mirroring the baked-in account types, you can customize your segments and create segments however you want to analyze your data.

Now, this isn't the quickest process. I need to go in and assign each of my GL codes. So I've done my merchandise. I'll do my service code. Oh, I've hit the wrong button. I need to hit the edit button. Try again. So again, I can just go through and say, right, you are an income code. So we've got a couple of, couple of codes allocated there. Let's do a cost of sales code. So I think that's my fives. Let's go in here, and we, we'll put 51. Oh, and I've clicked the wrong button again. Let's go into the edit button. Let's go into 51, and we'll say that you are cost of sales. 52, again, your cost of sales. And I'll do a couple of expense codes. Don't worry, I'm not going to do the entire chart of accounts, or we'll be here for quite a while. So let's do a few of these. So 6010, I don't actually know if we've got any data in some of these account codes, but we'll allocate them anyway and see what comes through. That's going to go through as an expense code.

Let's find another six code, uh, 610. Let's go with amortization expenses and see what happens, so let's allocate that one as well. Now, if we go back into Solution 7, remember, the way we built this is we're using our AP function, our AP Bal function. We're looking at all account codes, but we're filtering the management accounts grouping based on this column here, so each of these formulas reference those. So let's now hit the refresh button, and with a fair wind, we should get some numbers appear. There we go. And those that we didn't actually allocate, so expenses there weren't any. Other, there weren't any balance sheet. I didn't actually allocate the balance sheet code, so we've not got anything through there. But if you ever want to, um, see what account codes have been mapped and make up that balance, the simplest thing to do is to simply take advantage of our drill-down feature, and I can drill down by the account code, and that will actually open up that 965 balance and show it by the different management accounts group, or by the different G code that were allocated to that management account grouping. I hope that answers your question.

Evie: Fantastic, thanks, Simon. Okay, so we have a third question from Mark at Knoxbox: how do we set, set up reports to automatically add new line items when new accounts are created?

Simon: Oh, okay, good. In fact, that question almost ties in with this question. So there's a few different ways you can do this. So one way is to use wildcards. So if I, if I were to enter a wildcard here, let's say, for example, I enter four star as my formula, and I were to enter a balance function to actually reference that four code. So I'm going to go back to the original AP Bal function. Let's just, let's just pull the subsidiary, let's pull the account from here, let's pull the period from here, click okay.

So this is one way of doing it. Um, here we've said aggregate all of the GL codes that start with a digit 4, because we said I want all of the GL codes that start with a digit 4. Any new GL codes that I add into NetSuite and start posting to will automatically get picked up by the software. So typically, what you want to do if you're using this way of analyzing your data is you almost want to blanket-fill the chart of accounts. So, for example, if I do five star underneath, I cannot create a gap between oh, why is that gone? Bang. Yeah, so I've got my, my dollar in the wrong place. Let me just fix that problem.

So you can see here that I've said all of the fours are going to go here, all of the fives are going to go here. I've left, no, I've left no possibility for creating a gap. It's either going to end up in the fours, or the fives, or the sixes, or the sevens, and so on. So, as I add new G codes, I know for a fact they are automatically going to appear. Um, if we want to use a custom segment way of doing it, again, any new GL codes are not, um, are not, are not allocated to a custom segment automatically, and that's where this non-category comes in. So any brand-new GL codes, you will start to see balances appear for an unanalyzed account code.

So what you can actually do is take that balance and either have it on another sheet, or maybe at the bottom of the workbook, and almost have that like as a cross-check digit to make sure that zero, to make sure that you've taken any new GL code and you've allocated it correctly within your, within your custom segment.

The third way of doing it is when you build a report. So let me insert my popup of subsidiaries again. When you build your report, if you insert a column list of accounts by number, so rather than, rather than keying the accounts in, you let Solution 7 do it for you. So I'm going to pull in all of my four codes, followed by all of my five codes, click on okay. While I'm doing that, I'll also, again, type in a formula, and let's put in one of our balance functions again. Oh, wrong button. Let me do this one here.

So I'm going to reference the subsidiary, I'm going to reference the account, and I'm going to reference the period. So effectively, here's a, here's a report, here's a report with some numbers in it. The question is, what happens if, um, if I add a new GL code? So let's, let's sum up the income section. So this is going to be our total income, and that's got an amount of, let's, is the SU function. That's got an amount of 3.4 million. I think this is a dollar account, so let's just set that up correctly. There we go, and put some borders in. So I'm trying to replicate that number to spot if anything's missing.

So one easy way of doing it is actually to, at the, again, off the bottom of the sheet, if I type in four star and I copy one of those balances down, you can see here that I get a total of 3.48 million. Again, that matches the sum total, and if I take one, one away from the other, in fact, let me just turn those into positive balances. They, they're coming through as credits, so we're getting negative entries come through. Let me just turn that into a positive balance. There we go. So I can say equals that plus that. You see, I get a balance of zero, and if I were to do that all the way across my totals, effectively, what I've built is a self-reconciling report.

So I can always see how much of my individual totals are, compared to, um, what the totals are that are being calculated. And, of course, you've always got the option to check that number against NetSuite, and what we see a lot of the time is people actually create a control sheet where we add up all of the zeros, and if they add up all the way through back to the top and are zero, you know that your report is self-reconciled.

Iling, uh, let's actually go in and delete a row. So let's delete that 404 code, and you can see now that we've got a balance of 3.09 million. Now, what we don't know is we don't know if this section is complete because the total is correct, but our check total down the bottom here is telling us that, according to Solution 7, the balance on all of the four codes is 3.48 million, and we're out by 392,000. So we instantly know we've got a problem.

Now, you'll remember when we inserted this list, we inserted it as a list here. You can see I can actually edit the list and see the original set of values that I inserted. Another neat thing I can do is I can simply refresh that list. If I refresh that list, we go off to NetSuite, grab the chart of accounts, compare it to what you've inserted. Let's imagine where I deleted that account code, we've actually added it as a new account this month, so that 404 has been inserted back into the correct place. And, by the way, Solution 7 tries to work out, based on the shape of your chart of accounts, where to put it. What it won't do is re-engineer your list for you and put it back to how it should look. We try and figure out roughly where in the chart of accounts any new values need to go. So you can absolutely move accounts around, and we will try very hard not to break your structure.

So we've inserted that 404 code. You remember we're out by 392,000. We now have an income balance of 3.48 million. We have a reconciling tot of zero. All is good in the world.

Evie: Thanks, Simon. Um, so looking at the Q&A box, do have quite a few questions. So thanks for that, guys. I'm just going to go through and pick up as many as we can. Now, I do have a question from Jeremy at Ready Capital. Um, is there a way to import the mapping in NetSuite for custom segments?

Simon: Is there a way to import the mapping in NetSuite for custom segments? I, I'm not sure I understand the question. Oh, is that saying, okay, is that asking which accounts are attached to which custom segment? Let me look. You should, I, if, let's, let's make the assumption that what we're trying to do there is pull all the custom segments through with their associated account codes. Clearly, I proved one way was to drill down by account, but that's actually not the question. I think what you're looking for is you are looking for a column of, and if we can't do it, I'll raise this as an issue. We're inserting a list of management account groupings. We go into choose columns. H, that's interesting. So we don't have the accounts that they are linked to. So the way that our list functionality works is you should be able to go in there and choose which account you map to. So could you possibly do it if we tweak the software? Yes, and we'll take that away as an enhancement. Can you do it in the current version? Sadly, no, apart from going into the drill down, apart from going into the drill down and drilling down by account to see what sits under that balance, and that will tell you what you've mapped. But that's a great suggestion, by the way. So we'll certainly take that away and have a look at trying to add it. Um, when you try inserting a list of, um, custom segments, because it won't be too hard to add a reference through to the account table or whatever it's attached to.

Evie: Fantastic. Do have quite a few requests in here assignment about how to add custom segments to Solution 7.

Simon: Uh, it's really simple. Um, send an email to support. We, we don't have a better way of doing it at the moment. Send an email to support, um, hook up with the team, um, and we add them for you. Um, we are on a roadmap where you will be able to go into a screen in NetSuite to configure your custom segments that you want to expose to Solution 7, and we have the technology in place to do that. But currently, we have to take that offline and provide you back with something, and I'm guessing we're going to get inundated with emails after this call. So, um, please also be patient. We, we can, we can only turn them around so quickly, but we can absolutely support them. Just send an email here. Let me show you the, um, let me show you the support email address. Send an email to support@solution7.co.uk. That will either be picked up by E or Maria. Um, Evie is actually on vacation the next week or so, so probably Maria. Um, but yeah, drop us an email, and, um, we can add that for you. But just be aware, off the back of this call, we're probably going to expect several hundred requests.

Evie: Brilliant. Okay, so looking at the Q&A box, we have a question from David: is there a way to pull the average balance over a period using Solution 7.

Simon: That's an interesting one, so I guess now I'm no mathematician, but I'm guessing you are looking for the mean average because I don't think we would be able to get any other average. Well, I suppose there's a few ways of doing it actually, so you could just use Excel to do it. So there's nothing stopping you going equals average, and there's nothing like being average for a demo. Um, we could, uh, say equal average. Oh, come on, give me a formula, A-V-E-R-A-G-E. Enter average. Here we go, equals average, choose all the numbers you want, hit enter. They, uh, oh, average plus average. There we go.

So there's one way of doing it, just pull the balances and hit average. The other way to do it would be to create a formula. Let's say, for example, we're going from January through to December 2023, and we wanted to create a year-to-date balance or between the two. Um, so let's just do that. Let's convert this period balance for December into a year-to-date balance. So that's where the from and the two period kick in. So the from period needs the reference back to January. Try that again, January. And the two period needs to be December, and you'd probably want to do some sort of mathematical calculation to count the number of months. So I know it's 12, but there must be a way in Excel of saying how many months are between those two dates. I'm going to hard code 12. How I would do it is let Solution 7 aggregate the total and then just divide it by, and that will give you the average balance. So effectively, create a year-to-date balance. So the year-to-date balance is taking advantage of both the from and the two period. So create a year-to-date balance, count the number of months between those balances, and divide the total. I hope that answers your question. I'm, as I say, I'm not a math expert.

Evie: Thanks, Simon. David has actually followed up and said the question is more geared around avoiding having to run daily balances for their accounts. Um, so how would we find the daily cash balance or daily average cash balance?

Simon: Uh, the, the, the daily… so I can show you how to do the daily balance. Um, you go into the advanced functions, and you do it by date rather than by period. So all of our functions by default work by period. In the advanced balances, we can work by date, and these work on a date range rather than a period, and they work off of the transaction date rather than the financial period. So if you could imagine an accounting system or an ERP system where you didn't have a financial period, you only had the physical date of the document, these functions work off the physical date of the document, and you'd need to run it to get a balance sheet balance. You'd need to run it from some time a long time ago, first of the 2000 to today, which is in UK date format. Apologies if this messes with people's heads, it is in UK date format. It is the 19th of the 9th 2023.

One word of warning: this is having to aggregate a lot of transactions. If you have a very large NetSuite instance, it's having to go back to the beginning of time to roll this all forwards to create a balance. So what you can do here is effectively that's from the begin of time to September. There's nothing stopping me having it running from the previous day, the previous day, the previous day, and creating those balances, or creating an opening balance and then pulling through the individual daily balances just by entering a date. So, for example, if I were to just enter I'm going to get zero because we haven't got anything posted to this month. But if I were to enter Z 19 09 2023 as a single date, that will pull the movement for that day regardless of whether it's P&L or balance sheet. So if it's P&L, it's going to be income. If it's balance sheet, like a bank account, it'll be the movement on the bank account for that day. And as you see, we get zero because we don't have any transactions posted for that day. But I'm hoping, I'm hoping, does that get us closer to your answer?

Evie: Thanks, Simon. Perfect. Okay, a couple of questions about how to exclude accounts from an account range. For example, if we define all accounts that begin with four for sales, how do we exclude an account or a couple of accounts within that range?

Simon: I love, I love this. I love this question because when we explain it to people, everyone goes, oh yeah, because it's, it's, it's super easy to do. There's, there's two different, there's two different pieces of functionality here that we need to be aware of, one of which is when we insert a list of accounts down the sheet. The other is when we want to exclude them from a formula if we want to do it from accounts in a column, for example.

So I'm going to insert a list of accounts by number. So here, for example, I'm going to say I want all of the, I want to, I want to put in all of the four codes and all of the five codes, and I want to exclude 42, for example. So when you're inserting a list, you just use that syntax. Uh, this is an array. I don't remember whether I said this earlier. This is a syntax that we call an array, so you effectively surround everything with curly brackets, which means, which allows you to repeat the value. So four star, five star, excluding 40002. I click on okay. You can see there that the 402 code is missing. Oh, and by the way, I've realized why my machine didn't finish there. I'm running the 32-bit version of Excel. Um, if you're running Excel, don't run the 32-bit version of Excel. You run out of memory very easily, and I think that's what happened when I was trying to run that.

So here we've got the 402 code being excluded from a list. So very simple, you just edit the list, and if you want to add codes or remove codes, you can have arrays of codes. Let's do, what, we got another one, 48? Let's try that one. Click on okay. Oh no, wrong, wrong bracket, or curly bracket. There we go. So I've now said fours and fives excluding 42, 48. You should see 48 will disappear. Bing, there it goes. Now that's how you do it using lists.

How do we do it using functions? You'll like this because it's super easy to do. So you pull a balance. Let's do all of the four codes for Jan 2023. So let's get a balance through, and again, I don't know my data that well. I don't know what the number is going to look like. Let's copy that down and do an exclude here. Literally take the formula. We're looking at a credit balance here, so we're going to take away another credit balance, and we're going to take away the 40002 balance. So to exclude, you literally just put two formulas into, um, into your formula bar: one to pull those that you want to include, minus or plus, depending on whether you're looking at debits and credits and how you want to play with the sign, plus or minus another balance. Hit enter, and you can see there we've got a different balance. So that's all of our four codes excluding the 402 code. I hope that answers the question, and that's the right, that's the answer you're looking for. So super easy to do, just put multiple formulas in the formula bar.

Evie: Thanks, Simon. Another question about automation, from Paul Goodar. Um, if we map a worksheet with the budget tool, would the automation tool pick up that mapping?

Simon: Absolutely, yeah. So this, this is at the heart of our, um, our financial planning story. People, all people often think that Solution S is a full-blown, um, financial planning engine. Um, what we allow people to do is to write reports using budgets, using actuals. Um, we have an interface that allows you to push budgets back into NetSuite.

So the question is, how can I map and then make sure those mappings stay in my automated sheets? Um, what we're referring to there is this option up here: budgets and forecasts, and what we call mapping of workbooks. So what I can do is I can map the workbook so that I can upload the numbers back into NetSuite. So, for example, if I built a demo workbook, and we've covered this in other sessions, by the way, so, um, if you've watched a previous session, we go through this. Um, if you're just wondering if there's a way we can send out a video link, maybe we'll try and send out a video link to that particular session as well, so that people can see this in more detail.

Um, you map things like the subsidiary, you map the financial periods, you map the GL codes, and the reason we're doing these mappings is CSV imports into NetSuite need the file in a certain shape. And what we're trying to say is actually, know, just tell us where things exist in the workbook: things like the budget category, things like the location. Now, the question is, when I do this mapping and I run automation, does this mapping stick? Yes, absolutely it does. In fact, you want to do this mapping before you run any automation if you plan to use your workbooks as a way of running a financial planning cycle, because you absolutely want your workbooks to contain those mappings so that when you receive them back, all you have to do is upload them into NetSuite.

So how does the process work? You build your template however you want to publish budgets. You push your budgets out using a you push your budget templates out using Solution 7. That will create the different shapes for you. Remember, don't use 32-bit version or you end up with what happened to me. Push your workbooks out. The mappings will go out automatically. When you receive those, map those mapped workbooks back in with the actual numbers. We've got this upload feature here under budgets and forecast. You choose the sheet that you want to upload, you hit the upload button, and then what we do is we unpick that mapping to essentially just work out where everything is. So, for example, we know where the budget category is, we know where the, um, location or the department is, we know where the G code is, we know where the financial period is, and you just set up your mappings.

We'll pick those up, and we'll push those budgets back into NetSuite, and then when you want, you can pick up those numbers using our reporting capability. So you push all your numbers into NetSuite, and then you use our budget functions to, um, to pull those budgets back out of NetSuite. Now, as a, as a, as a quick bonus, I want to just share with you, um, you're not limited to uploading workbooks where you've got periods across the top and codes down the left-hand side. Um, we did work with a, a food bank in South Jersey to build out, build out a couple of other templates. So, so here's one where we've got, um, periods across the top, but we've actually got individual codes in the green section here that you can pick which code you want. Oh no, not that. You can pick which account code you want to set the budget up against. You can actually type in amounts in detail. You can type in what they are. You can add a commentary, you know, that sort of thing. These are just TA Excel tables, and what we do here is we summarize those values up into the green row here, and then it's just regular mapping in Excel, uh, in Solution 7, to pick up for the account that column there, which has the GL code in it, the account number in it. So effectively, what we're doing is we're bypassing the detail, but we're using Excel to aggregate the detail up into the green rows. This is actually a not-for-profit, um, spreadsheet, and then we can literally take those and upload them into NetSuite.

So although we can't push the detail amounts into NetSuite with the commentary, we can hold those in our budget folder for this year to say what was budgeted, and we can push those numbers, um, up into NetSuite in a summary form. And we've got a couple of other workbooks that we worked on with the food bank, and if anyone wants to copy those, just drop us an email, and I'm sure we can, um, I'm sure we can share a link to them.

Evie: Great, thanks, Simon.

Simon: No problem.

Evie: We have a couple of questions on returning balance sheet amounts. Um, so there's a chance we can go through how to pull through a balance sheet amount and then also determining the FX rate to, to that amount if, um, companies are using multicurrency.

Simon: Yeah, good question. So super easy. I'll show you some simple formulas. P&L, I know my four codes on my P&L codes. If I do it for a single period, I get the balance for that period. So that's the movement on the P&L for that period. If I go in and do the same, I'll do the same for P&L. If I do four star, and I run it for Jan 2023 through to December 2023, that's giving me the year-to-date balance again on that P&L code. So you can do things like year-to-date, quarter-to-date, and you can do that on individual, individual period. You can do it on multiple periods; it really doesn't matter.

Uh, to do balance sheet, there's one extra step to think about, which is, uh, utilize in the from and to period to aggregate all of the transactions. So this time I'm going to choose a balance sheet account. So this is going to be our assets account, and the trick here is you need to go back to the beginning of time because, sadly, NetSuite does not hold balance sheet balances. So you have to go back to the beginning of time. And then let's say we wanted to run the balance sheet balance at Jan 2023. I click on okay, and because I've gone from the beginning of time, it's going to roll up the balance force automatically. And how have I… how am I getting an error there? Why is that coming up? Oh, because I put a single quote there. There we go. So that's the balance sheet balance for all of our one codes. I hope that answers the question.

Evie: Thanks, Simon. How can we look at the exchange rate?

Simon: Uh, oh, the exchange rate amounts. Yeah, yeah, I missed that. So, um, in the function, um, in the functions dropdown, we've got this NetSuite currencies. Uh, we've got the consolidated FX rate and the currency FX rate. And we always get asked what's the difference between the two. NetSuite refers to the consolidated rate as the intercompany rate, so this is going from one company to another. The currency rate is the currency rate within a subsidiary that's used to value a document. So if I were to post into a GBP account a US dollar invoice, that would pick up the currency rate.

So for this particular question, we're looking at the consolidated rate, and you'll see that you choose the from subsidiary, the to subsidiary, the period, and the rate type. So we're looking at balance sheet here. So for balance sheet, we're looking at the current rate, or the… the current or the average. Again, I'm no… I'm not an expert NetSuite expert. Let's go with the current rate for now. So if we're looking at a balance, a bank account, for example, we're interested in what the current balance is. Um, so consolidated… oh no, sorry, we're going from… we're going from, let's go from UK to Inc. So the way NetSuite works is consolidation works up the tree. We choose the period that we're interested in. Let's again do Jan 2023, and we choose the rate type, and you have a choice of three rate types within NetSuite: you have the average, the current, and the historical. And then you have separate rates for budgets and actual. So you've got three different rate types multiplied by two: one for budget, one for actual.

If we look at the… let's go for the current rate. Click okay. Click okay. That's telling us that from UK to Inc, the current rate for January 2023 is 1.33 something to something US dollar to sterling, that be sterling to US dollar. I can only assume that number is correct, and you obviously can build that into your workbooks so that if you've got columns across the top, you can have the individual exchange rates coming through, or you can apply different formulas to look at different, um, different exchange rate values. Again, I hope that answers your question.

Evie: Thanks, Simon. We have a question from Michael, um, asking if we can test a budget upload in the NetSuite sandbox first to ensure it's imported correctly.

Simon: 100%, yes. 100%. There's actually two different ways of testing a budget upload. Um, let's… let's pretend we've got a budget here: 4,000, 5,000, oh, 500, 5,000, 6,000, and we'll stick a… we stick a 100 bucks against each of them. And we'll go for my favorite, Jan 2023. When you map the budget again, you'd go, here's the subsidiary, sale, here's the period. It will guess the shape because it's only one cell. I'm going to have to tell it that's the column header. Then you can reference the accounts. Then we reference the budget category, which will be a cell over here somewhere. So Solution 7 now knows the shape. That shape is stored in the workbook. Now you can switch between production accounts and sandbox accounts by using the configure button. And often what we recommend is you create a config file: one for your production NetSuite instance, and another config file for your sandbox instance. So the simplest way to do that is if you've got a config file pointing at production, make a copy of it, reference it, and then edit the data in here to point to your NetSuite, uh, to your sandbox instance. If you don't know how to do that, I think our documentation, um, our documentation will walk you through how to do that. So again, we'll try and include a link to that in the, um, email we send out. Um, so that allows you to switch between production and sandbox. Uh, if I activate against sandbox and I upload the budget, that will go into the sandbox. If I'm using a production config file, when I upload the budget, that will go into production. If you don't want to upload into sandbox because, I don't know, you may not have a sandbox, or, you know, you can't be bothered to switch between the two, another way of doing it is just create a brand-new budget category in NetSuite, call it a play budget or a test budget or a whip budget. Um, you can have… if you've got Advanced Financials, you can have as many budgets in NetSuite as you want, and all you simply need to do is upload to a different budget category, so you don't overwrite the original. So there's another way of kind of treating it as a work-in-progress budget without having to push it into sandbox first. Again, I hope that answers your question.

Evie: Thanks. Coming to the top of the hour, probably have time for maybe one more question. Let me just have a look in our Q&A box. Okay, so we got a question on using Excel formulas along with Solution 7 formulas. Um, on the named ranges, if we use named range feature in Excel to aggregate accounts, asking does it create a strong latency in response?

Simon: No, absolutely not. So, um, um, for those people that, um, don't know what a named range is, formulas, name manager, new. I'm going to create a, a named range called income, and I'm going to reference… and I'm going to reference all of our income codes. When I insert my formula… in fact, this used to be the way we recommended setting up groupings before custom segments existed. Um, again, I can insert my formula, the account. Now I've got to figure this out. Do I just do income? Is that how I pull it through, or do I have to do some special… income Jan 2023. Click okay. There you go, and you got balance three. So it references all of the accounts that are part of the income named range. Uh, you should notice no degradation in performance at all. And again, going back to our very first question to do with custom segments, that's another great way of setting up, um, um, high-level aggregations. The downside of doing it this way is they sit within the workbook rather than sitting within NetSuite. So by setting up a custom segment, everybody has access. If you only set it up in Excel, it's limited to just this workbook, which makes sharing that concept with other users and other workbooks more difficult. So our preferred way of doing it is to use a custom segment. But yeah, that is equally valid, and yeah, there should be no impact on performance.

Evie: Fantastic. All right, um, so I think that's all the questions we have time for here today.

Simon: I feel really bad because I can see we've got 34 open questions. So look, I'm really sorry if we didn't get round to your question. We are trying to run these more regularly, so, um, please hold on to your question if you want to ask it in the next session. Um, we will go through and pick some out and see if we can carry those forwards as well. So again, I do apologize if we don't have time to get to your specific questions. Really sorry.

Evie: Yeah. And guys, if you do have any, you know, urgent burning questions, please do reach out to our support email. I… you want to show that on the screen? Um, and we can answer those.

Simon: Um, an Evie… what's the most important question?

Evie: Will this… will this question… will this, um, session be recorded?

Simon: Absolutely. This session is being recorded. So there, if there's anything we've been through that you want to watch back… and my, my PowerPoint has now gone strange. So not only did my Excel go strange, my PowerPoint's gone strange, so I'll have to put that up there. There we go.

So if you do have any questions, you know, email… email the support team. We'll, we'll try and get back to you as we can. If we didn't cover your question, I do apologize. We will try and cover them off in another call. If you thought this session was useful, can you let us know? Because we'd like to run these regularly. We feel that we can actually take these sessions and turn them into little video snippets so we can, you know, help build up people's training, um, training material, you know, have a nice web, um, demo website where you can go in and look at all these different questions and watch them back. And if you did find it useful, you know, um, please let us know, because, um, they do take quite a while to, to, um, to prepare these sessions, although slightly less if I don't have to prepare a big demo. But yeah, um, you know, please do let us know.

Evie: Yeah, absolutely. Um, we've had quite a few requests of the pre-recordings to the previous webinar we have done as well, so we can talk to our clever marketing team about how to get those sent out.

Simon: Yeah, I don't know how to do that, so if we, if we, if we screw up, it's not my fault.

Evie: All right, thanks guys, really appreciate everybody joining today. Like I say, if you would like to shoot us an email with any questions that you would like answering, please do so to our support inbox. Um, we will send the recording over in the next few days. Um, watch out in your inbox for that, and thank you again for joining, and we hope to see you at the next seminar.

Simon: Thanks, everyone.