Ask the Expert | NetSuite Financial Reporting in Excel with Solution 7 | Oct 2024

Transcript

Evie: Hello everyone, thank you for joining. I am Evie, I'm the Customer Success Manager here at Solution 7, and uh, we're going to do another Ask the Expert webinar today. Simon is going to show us, um, an update on the migration plan from the legacy data source to NetSuite 2, and then we will move on to the Q&A. If you have a question during the webinar, please post your question in the Zoom Q&A box at the bottom.

Alright Simon, what have you got for us today?

Simon: Hey, good morning everybody, good morning actually good afternoon everybody. Um, welcome to this month's session. So yeah, as Evie said, I'm going to be, um, as first part of this session, giving you another update on the NetSuite 2 migration just to say where we've got to, um, give you a feel for what the migration is going to look like in terms of how it will actually work inside the software and, um, with a fair wind, um, give you a quick demo of where we're at with the migration.

Um, which, for the amount of work that's gone into it, looks very unspectacular, but I think the more unspectacular it looks, the better. The, um, sorry the more unspectacular it looks, the better the job the developers have done.

Okay great, so let's dive into the first part of this session before we do the Q&A. Just as a quick reminder, um, we're working to three different plans regarding this NetSuite 2 migration. We've got an engineering plan in place, a communication plan in place, and a customer migration plan. I'm just going to go through each of those and let you know what's updated and what's changed.

Um, in terms of where we are on the engineering plan   everything here that's crossed out has been completed. So, um, we feature-completed NetSuite 2 several, um, several months ago now. Um, uh, we've improved the user experience, um, we've worked on mappings between legacy and NetSuite 2, and there's enough mappings in place to get the baseline software, um, um, working, um, and we have backwards compatibility with the functions.

What does that all mean? It means that the NetSuite 2 version works and we have customers using it, and all new Solution 7 customers will sit on the NetSuite 2 data source   they won't be on the legacy data source.

Um, what's in red is what we're currently working on, and that's all around what the migration experience will look like for existing customers. I've revised the phases from the previous session that I did, and I've tried to consolidate it down to a fewer set of phases. So we're now treating, um, phase one as complete, we're now treating phase two as the migration functionality within the software, and then we're treating phase three as looking at additional mappings that go above and beyond the mappings that have been completed to get the baseline functionality off the ground, and how we're going to handle missing columns.

Um, there is an issue between legacy and NetSuite 2 where some columns just haven't been migrated by NetSuite yet. Um, so while we're waiting for those to happen, we need to manage those missing columns. Um, I'm sure we're going to be asked what those missing columns are. Um, I don't, you know, at the moment, um, but what we will do within the software is we will, um, uh, try and highlight anything that's missing as part of the migration process. I don't think it's going to be anything important, so don't worry that, you know, a major balance column is going to be missing and you can't run your entire financials pack   it'll be a more obscure column that either NetSuite haven't thought about or we just haven't found it within the, within the NetSuite schema yet.

The phase two migration of lists   that's what I'm going to be doing my quick demo on, um, just to show you where we're at in terms of functionality.

Um, from a communication plan perspective, nothing's really changed. Um, we haven't, apart from these sessions   in fact, I should probably add these sessions to the communication plan because they do form part of it. Um, in terms of reaching out to customers, um, nothing's really moved on yet. We're trying to, um, nail down the functionality before we start reaching out to customers to let them know that now is the time to migrate and what to do. So, so, um, for now the best thing to do is sit tight and just watch these webinars, and um, if you want to have a go at downloading the latest version and playing with it, you're very welcome to.

From a customer migration plan   so what does migrating existing customers look like? Um, I'm actually working personally with the developers to review the customizations that have been built for people, um, and to try and automate migrating those customizations. So, um, if you have a customization, um, you'll know who you are because we'll have spoken to you in the past. If you have a customization, um, uh, one of the support team will have built that using, um, what we call internally just the customization builder.

Um, we've migrated the customization builder to NetSuite 2, but what we now need to look at is physically migrating customizations from the shape of NetSuite 1 or the legacy data source to the new NetSuite 2 data source. So that's actively happening now. And again, um, this is where we then dovetail into the communication plan.

Um, once that migration has been better automated   because we don't fully know yet what that automation is going to look like   um, we're going to contact customers and distribute the new customizations out to customers as part of that big migration of taking everyone from legacy to, um, NetSuite 2.

Uh, all of the NetSuite 2 functionality is going to be made available in version 4.12, so we are evolving version 4.12 around NetSuite 2. 4.12 was the first version to support NetSuite 2.

Um, feel free to download it, it's available from the website. Not everything I'm going to show you in this call, in this webinar, is available yet. I was actually hoping that everything I was going to show you today would be available to download, but unfortunately we missed one of the deadlines, so we're going to push everything I'm showing you now back to next month   so that by the time we do the webinar next month, I'm going to jump on a call and say, "All of this is available for download and you can, um, pull it down and start the migration as you see fit."

Um, so some of the migration functionality is in there, um, but unfortunately the piece that I'm going to show you today isn't. So if you'd like to have a go at downloading 4.12, playing with 4.12, um, your functions will work   so where you've used, for example, the ABAL or the ABUD functions, those will all work.

Um, what you won't be able to do is automatically migrate your lists in the version that you can download now, so you'd actually have to manually migrate your lists over to 4.12 to get those working. Feel free to have a go at it; alternatively, wait till next month and hopefully I'll have more to say then.

Before I actually show what I mean by migrating of lists, I just wanted to spend a few minutes explaining the challenge that we've had with the new NetSuite data source. What I'm showing you here is an example of one of the tables that has changed inside of NetSuite.

Now what do I mean by a table? Well, think of a table as an object that stores all of your data. One of those objects is the accounts object, and that's used to hold your chart of accounts.

You can see I've listed out here what it looks like in legacy, and you can see here what it looks like to us as developers through the NetSuite 2 data source.

And the challenge that we've got is that NetSuite not only have changed the name of the table or changed the name of the object, but they've also changed all the column names as well. So essentially, when we're talking to NetSuite at a code level, it's almost like we've gone from speaking English to speaking French or German   it's just a brand new language that we've had to learn and we've had to code into the system so that it doesn't just throw errors.

You can see here what was called "Accounts" in the legacy data source is just simply now called "Account." Now whilst that might sound something, might sound very simple   just by dropping the "s" breaks all of our code   it's that fundamental.

You can see here as well that the account number field So when you type in your chart of accounts, and you give your, um, your GL code and account number, through the Legacy data source it was exposed to us as something called account number. You can see in NetSuite 2 now it's abbreviated to ACC number. Again, any code that we've developed in the past is now broken, so that needs, so that needs resolving.

What does that, what do I mean by code? Well, Solution 7 talks to, um, NetWe using a language called SQL or SQL. Um, for the Legacy data source, we could issue a command which basically said:

“Select account number, comma description from accounts where account number like,” and this is a wildcard for all of the four codes.

And what this piece of code would do for us would be to return from NetSuite all of the account numbers and their associated descriptions where the account number starts with a four. That now needs to be converted to code that says:

“Select ACC number from account singular where account number like yada yada ya.”

And the challenge there is not only do we have to change the internal code, which we've already done   which is why for existing customers the software just works   so all of this new code is in 4.12. The challenge we have is that for lists within, um, Legacy spreadsheets, we have these field names physically embedded into the workbook.

So when you insert a list, we know you've inserted a list of accounts, and we know that you've chosen the account number and the description. And so that had to be physically stored in the workbook so that when you refresh the list, we know what to go back to NetSuite and, and   H, sorry   we know what to ask of NetSuite in terms of “we've asked for this information before, please can you provide it again.”

And obviously that is now out of date when we shift to the NetSuite 2 data source. So that's where the, that's where the real pain is being caused from a, um, from a Legacy to NetSuite 2 conversion process.

How have we overcome that? Well, not only have we fixed the code internally, but we've also had to create a series of mappings. So we've had to say that the accounts object now maps onto the account singular object. The account number field maps onto ACC number. Class ID   you know you've got the concept of class, department, location   in Legacy it's called class ID, in the new data source it's just called class.

And the hardest one, um, that we've had to get our heads around are custom fields and custom segments, because you can see here custom segments used to give you a clue what they were related to. So if you had a custom segment called account, actually it would be called account in the Legacy data source. Now it's just called CEG something.

So again, we've had to set up all the mappings for that. And again, part of the migration challenge we've got with customers that have customizations is we've got to generate all those, um, translation files so that we can hopefully just lift your Legacy workbooks and map them onto NetSuite 2.

So to sum all that up, it has been quite a large, uh, quite a sizable task taking all of Legacy, figuring out all of the differences, and working out how to migrate people, along with finding bugs in the NetSuite 2 data source that we've had to escalate to NetSuite to get those fixed as well.

What does that look like from a demo perspective? Well, I have a really unspectacular demo for you, and I'm really quite pleased that it's an unspectacular demo, because it means that the developers have done a really good job, um, M, uh, putting all of this migration code in place.

You'll see here I've got two config files   one for Legacy and one for NetSuite 2. Um, I'm probably going to go into that in more detail in the next webinar, in terms of how you can flick between versions of, um, Legacy and NetSuite 2, um, because I think that's going to be more appropriate once the software is available.

But this is how I'm just flicking between the two different, um, data sources. Let me activate, um, running under Legacy   there we go. And I'm going to go back to the example that I showed you in the SQL, where I'm going to insert a list of accounts by number.

So it's not quite running the code that I showed you, but it's, it's close enough for this example. So here I'm going to insert a list of accounts, and let's do all of the fours and all of the fives so we get a nice long, so we get a nice long list up here. So that's all of the fours and all of the five codes, and let's just pop some formulas next to them.

So I'll insert some balances quickly, so there's a simple set of numbers. Let's save that   so I'm going to call that Book One, and let's switch over to NetSuite 2.

So this is effectively a NetSuite One workbook which I'm going to migrate to NetSuite 2. So you'll see when you're activating NetSuite 2, you don't really see anything different, and we tried really hard to make the user experience as seamless as possible. Once you're over on NetSuite 2, it should work pretty much the same way as the Legacy data source.

Let's load up that workbook, and there are two things that, um, we'll see when we try to, um, refresh the workbook or refresh the list. But first of all, let's try refreshing the sheet, and you'll see you get a warning:

“There are lists or tables in this workbook which cannot be refreshed. See the List and Table Manager.”

So you see if I click on OK, we're going to refresh, we're going to refresh the formulas, and you'll notice from a NetSuite, uh, from a Legacy to NetSuite 2 perspective, there's actually nothing to do from a formula perspective, unless you've got customizations.

You can see there those just refreshed as normal, so that refreshed against the NetSuite 2 data source. But what the software cannot do at this stage is refresh to the list. So how do we go about fixing that?

Well, you see if I right-click on the list, you'll see that here, rather than being able to refresh it, now the right-click menu is also pointing me at the List and Table Manager. So the List and Table Manager is the key to this process.

Let me just show you the refresh sheet again   you'll see again that refers to the List and Table Manager. Now you may have never used the List and Table Manager, but that's somewhere that you can go into up here on the ribbon, where you can actually see all of the list definitions that you've got set up in your workbook.

Now what do I mean by a list? Well, a list is actually not only your lists that you insert, but also I think the pivot table show up in here as well, which is why it's called the List and Table Manager.

So if you've inserted any pivot tables, those also have the same issue in terms of translating the old Legacy names into the new NetSuite 2 names. And it's in here that all of the work has been done to convert your workbooks, because these lists cannot be refreshed because they've got the old names in them.

You'll see you get a warning: “One or more lists are not compatible with NetSuite2.com and have been disabled.”

What you do have is a nice migrate button, and this is where I said the process, although really challenging, is really unspectacular. Just hit the migrate button   it gives you a warning:

“List and tables cannot be undone, so please make sure you have a backup copy of your workbook.”

Again, I'll probably go into this in more detail next time. Uh, the one thing I would say here is where it says to make sure you have a backup of your workbook, um, just be aware that if you've got autosave switched on, this will be a live upgrade, and you will have upgraded the workbook, and it will automatically get saved back into, um, Teams or SharePoint because autosave is turned on.

So your backup would be going into SharePoint and relying on SharePoint's backup copies that it creates. Before personally, I would recommend taking the entire contents of the folder, pull it NetSuite 2, and then try upgrading the NetSuite 2 version so that you keep the Legacy version of your workbooks.

I'm going to say yes to that. You'll see we're getting “In Progress” up here as we go through, analyze all the mappings, analyze your list, try and work out where all the differences are, and there you go   done.

I told you it was unspectacular. So that is that list now upgraded. So you'll see here if I go in and hit the refresh button, I can now refresh the sheet as before. Click OK, we can update the list, we can update the, um, formulas, and we now have a NetSuite 2 working workbook.

One word of caution: once you've upgraded   sorry, I mustn't use the word upgrade   once you've migrated your workbook from Legacy to NetSuite 2, anyone using the Legacy data source will not be able to refresh the workbook So, you do need to plan this carefully so that you, um, migrate copies of the workbooks so you know that they will migrate successfully, and then, between all of us, we need to carefully plan the rollout of a NetSuite 2. Um, not only the 4.12 version but also a NetSuite 2 config file to take your version of Solution 7 and point it at the NetSuite 2 data source. Because, again, if you load this workbook that’s now been, um, updated as a NetSuite 2 workbook and you try and update it in, um, Next 1 or, sorry, Legacy, um, that will cause you a problem. So, it’s kind of a one-way process but taking lots of checkpoints and backups along the way.

So, I hope that sums up where we’re at in terms of we’re trying really hard to make it as painless as possible. We really want to be in a position where you just load your workbook, hit a button, it upgrades the workbook, and you’re done. Um, we don’t expect it to be quite so simple; obviously, we haven’t seen everybody’s workbooks yet, um, to understand where all the edge cases are, but hopefully that gives you a really good idea as to where we’re at and the progress we’re making.

And just to give you a reminder on timeframe, um, NetSuite have given us a deadline of, um, I think it’s Q1 or Q2 2026. So, in my head, I’ve got a date of around April 2026 that customers must have completed their migration from NetSuite 1 to NetSuite 2. So, whilst we have over 12 months, let’s not get, um, let’s not get too complacent, and, um, because you know what’s going to happen   that time is going to go super quickly.

And that’s our update on NetSuite 2. Should we dive into the Q&A? Um, Evie,

Evie: Let’s do it. Thanks, Simon. I know you said it looks unspectacular, but I’m quite excited.

Simon: Oh, good.

Evie: All right, well, we do have one question in the Q&A that relates to NetSuite 2 and the migration, so I’m going to ask that one first. Okay, and then we’ll go through the pre-submitted questions that you guys sent in.

All right, so, uh, Kristen has asked if we download 4.12, will our current workbooks not work until the mapping is done?

Simon: Uh, no. So, so that’s probably a good thing to go over now. So, 4.12 is 100% compatible with 4.11. Um, it’s all at the config file and the data source level. So, um, uh, you’ll see here that we’ve got what’s called a data provider, and you’ll see when you upgrade to NetSuite 2, you’ll have two data providers available in here. You’ll see, because I’m working in a development copy, I’ve got lots and lots and lots and lots, and I’ve got some Legacy.com and some NetSuite 2.

So, it’s not 4.11–4.12 you need to worry about, it’s flicking between data providers that will cause you a problem. Um, your 4.11 workbooks, um, if they have lists in them, won’t be compatible with   with, uh, sorry   your Legacy workbooks (I need to avoid using the version number). Your Legacy workbooks won’t be compatible with NetSuite 2, um, if you have lists in them. The formulas will refresh, but you won’t be able to refresh the lists.

Something to also add to the 4.11 to 4.12 migration, um, there was a requirement pushed onto us by NetSuite, um, that we must support token-based authentication. That has also been implemented in 4.12, and you will need to enable, um, token-based authentication within NetSuite, um, otherwise you will experience errors. So, there’s, there’s two issues to be aware of from a 4.12 Legacy NetSuite 2 migration. One is the migration of your workbooks, which is a data source issue. The other is a 4.11–4.12 issue, which is to do with token-based authentication.

Evie, is there anything that you wanted to add about, um, the token-based authentication? I know you’ve dealt with quite a number of issues in and around that.

Evie: Um, yeah, so one of the things is we need to push the integration bundle kind of update, um, to NetSuite. So, even if you add the permissions for token-based authentication, you still might see a message pop up in Solution 7 purely because you have an out-of-date bundle. Um, so if that’s the case, shoot us an email at support, and we can get that update pushed to your account.

Simon: Yeah, and the other thing I would say, we do have a stock email that, should you bump into this error, there’s a stock email that we can send you that explains how to go through the token-based authentication process and how to enable that on your account. So, um, that’s generally the first port of call, is that right, Evie? We’ll send over the email; hopefully that answers most of the questions, and then we can always jump on a call if that doesn’t fix things.

Evie: Yes, yeah, absolutely. All right, um, so another question from the same person: will you be sending out instructions on how to create the configuration file for NetSuite 2?

Simon: Uh, yeah, so that will be something I will address probably at the next webinar. Um, we don’t have it feels like it’s going to be something that, if I do a, a, um, a session on how to create the config file, how to switch between the two as a, as a, you know, a drawn-out session, then we can just send that recording out to people. So, the plan is once the, um, NetSuite 2 migration functionality that I’ve just shown you is available for download, we’ll then run that webinar, and, um, you can have that as a resource instead of having to read a document.

Evie: Perfect. All right, uh, so Jeremy has asked, um, if your file does not contain any lists or tables, it should just work with NetSuite 2, right? Disregard fields?

Simon: What do you think? Disregarding custom fields?

Evie: Yeah, unless you have custom fields, that’s right. So, formula...  

Simon: Yeah, good point, because we   so the reason that custom fields and custom segments don’t work is because NetSuite have completely changed the naming convention for custom fields and custom segments, and your, or any adapters that we sent you   so any customizations that we sent you   will need to be updated before you can use NetSuite 2. But in terms of if you’ve got no customizations, if it’s a vanilla install and you’ve got no lists and no tables, it should just work, and Evie’s reputation is on the line if it doesn’t.

Evie: I’ll take that.

Simon: There you go.

Evie: All right, uh, so I think one last question on NetSuite 2: is there any documentation with steps to test NetSuite 2 and configure Solution 7 in the environment?

Simon: Um, again, let’s, um, let’s talk about that in the next webinar, um, because that’s, that’s kind of tied in with the whole creating a new config file. Um, we can talk about, we can talk about creating a config file, what a config file is, how to switch between the two. Um, just trying to think if we want to go through an install. You see, what’s, what’s interesting is that, so from a, from a NetSuite perspective, NetSuite 2 is already available, so there’s no test environment that you have to set up. The test environment is more of a 4.12 test environment, and you can upgrade to 4.12 now.

So, um, in terms of creating an environment, let’s just tackle that issue now. It’s no different to testing new versions of Solution 7. It’s no different to testing, um, like a Microsoft Word upgrade. If you want to create a virtual machine, if you want to do it in a sandbox environment, you’ll need to create that environment to do that testing first. Um, the, the other   the remainder of the testing is actually the config file and switching between NetSuite 1 and NetSuite 2, and as I said, I’ll, I’ll, I’ll go through that in more detail at the next webinar once we, once that new version is available.

What I didn’t want to do was to give a demo on how to switch between them and then start generating a load of questions about, “I’m not seeing this functionality that you demoed on the session.” So, let’s wait for that functionality to become available for download, and then we’ll show you how to use it and, and set up those, um, different connections.

Evie: Absolutely, and to kind of add to what Simon said there, um, I’ve taken a few people through sort of migrating from Legacy to NetSuite 2. Don’t feel like you have to get everybody to migrate at the same time. If you can have one or two people max kind of testing the reports, making sure that they work in NetSuite 2, um, that would be great rather than getting everybody to move over at the same time.

Simon: But, but again, just be aware, any lists that you’ve created and any pivot tables that you’ve created, once they’ve been migrated, you can’t go back to the Legacy data source. So, whilst you can do some and not the others, that’s only where it’s just using the formulas so where you don’t have any lists in your workbooks.

Evie: Yeah, okay, um, oh, one more, sorry, on, um, customization. So, will the update to the customization adapters be sent automatically, or will those need to be requested?

Simon: Haven’t figured that out yet. Watch this space. Um, we’ll probably send them out. Um, we think that’s going to be the most painful part of this process, so, um, bear with us on that. We will communicate it out once we’ve worked out the path of least resistance in terms of just creating them and getting them out and getting them installed. Um, we don’t know what that process is going to look like yet, but yeah, you won’t, you shouldn’t have to request them, because we, we have a list of everyone that we sent them to, and we should be able to   we, yeah, we have a list of everyone we sent them to that’s associated with a record in CRM, and we have email addresses of people, so we should be able to let you know Um, obviously, as the deadline gets closer, if we haven't notified you, then reach out to us, but again, as part of these webinars, we will let people know, as part of our communication plan, what's going out, when it's going out, and what to do if you don't receive an email.

Evie: Great, all right, so I'm going to go through the sort of pre-submitted questions first before we move on to the rest of the Q&A questions.

All right, so we've had a question about budgets, Simon, so how do we pull budgets from NetSuite, and how do we upload budgets to NetSuite?

Simon: Okay, yeah, that's a, we haven't talked about budgets for a while. Let me, let me revert this back to NetSuite One. Okay, so the question is, how do we upload budgets into NetSuite, and how can we report back those budgets?

Um, um, the key to that is this menu here, or this ribbon item here, what we call our budgets and forecasts menu. Solution 7 has a mapping tool in it, so you can take a workbook and essentially map it onto NetSuite. I'll show you that in an example in a second, and then we have the ability to pick up that mapped workbook and upload the data back into NetSuite. So let me load up one of my existing demos. Okay, let's delete that sheet.

So here's a basic report. Now, from our perspective, a report and a budget template, they're the same thing. It's just a workbook with numbers in it. So you can, if your financial reports are good enough for collecting numbers, and you want to send them out and ask people to overkey the values, you can. If you want to build brand new workbooks, budgeting again, you can. The purpose of our budgeting functionality, or our budgeting story, should I say, it's all based around what we call automation and the upload.

Um, I'm not going to go into automation in this particular session in too detail. Um, we do have a webinar that's been previously recorded, and Evie would, let's send out the link to that recording, um, where I go through automation, and I go through the upload process in more detail, and I essentially walk you through an entire end-to-end budgeting process, um, using Teams as a, like, a storage mechanism for the different workbooks and so on.

Um, let's focus, because this is the specific question, let's focus on how we upload, um, budgets back into NetSuite. If you weren't using Solution 7, you'd have to take this workbook, you'd have to convert it into a CSV file, and then you could upload the CSV file back onto NetSuite. The challenge there is that if you start, if anyone starts changing any of these numbers, for whatever reason, you've got to recreate that CSV file, and you've got to re-upload it.

Um, as I said a second ago, we have a mapping engine and an upload engine. Let's have a look at the mapping engine. You can see here that this workbook has already been mapped into, um, the required fields for, um, posting the budgets back. So, um, we've mapped the subsidiary, that's a hidden cell, B1. Um, we've got the periods mapped, so January through to December, and you'll see here that those are marked as our column headers, so the software knows that those are the topmost point of the dataset that we're going to upload.

We've got the GL codes down the left-hand side, and those are marked as our row headers. So again, the software knows that those are the leftmost, um, fields we're going to be uploading. And then here, you can see we're going to choose a budget category. Now, just to give you an idea of what I mean by a budget category, um, if you've got, um, NetSuite Advanced Financials, you have the ability to upload multiple budgets into NetSuite, so you're restricted to just a set of actuals and a set of budgets. Sorry, a set of actuals and a budget. You can have a set of actuals, and you could have as many defined budgets as you choose.

So you could have a demo budget, you could have a work-in-progress budget, you could have a Q1, Q2, Q3 budget, which you could use as forecasts, you could have a forecast budget. It's entirely up to you how you name your different budgets, um, so that you can do actual versus budget, budget versus forecast, um, Q1, Q2, Q3, and all the movements. And if you think about it, with those different versions of the budget, you can use those for modeling different what-if scenarios, and you can have all of those what-ifs loaded back into NetSuite.

Now that we've mapped it, so we've got our subsidiaries, period, accounts, and the budget category, I have all the information I need to be able to actually push the numbers back up. So when Solution 7 processes this spreadsheet, it'll pick up the budget category, it'll pick up the hidden subsidiary name here. Let me show you what's hidden. So let me just unhide that for you. There you go, there's the hidden company name that we're going to be uploading to, and that's just based on the fact that we need to drop the word "consolidated." Let me rehide that.

Um, so we've got the, um, the budget we're going to upload to, we've got the hidden company we're going to upload to, all the subsidiary, we've got the GL code, we've got the account, and essentially all the software is doing is triangulating in the workbook where all of the different codes and values are going to come from. So uploading is as simple as saying "upload into Next Suite," choose all of the sheets we want to upload, whether we want to upload values in hidden cells, click upload.

We just confirm that you're uploading to the correct account and using the correct role. If you wanted to upload to, um, your sandbox, this is the point where we go, "You are uploading to your sandbox, and you do recognize what you're uploading to," just in case you decide to, um, blow away all the budgets by mistake. Let's click on OK. We connect to NetSuite, we validate the chart, we validate the periods, we validate the company, we basically do an entire validation on the workbook, and then we just pick out all of the individual values for you and push them up into NetSuite through its API.

So this is a fairly simple upload. There's the fact that it either worked or failed, and as I say, you can take this basic principle of mapping workbooks and uploading, um, individual workbooks, and if you build an, um, build an automation process around it, um, you can even, um, do an entire, um, end-to-end budgeting cycle just using Excel, Solution 7, and maybe a bit of SharePoint or Google Maps to, um, fill in some of the, um, some of the distribution elements, um, to get those workbooks out to your users.

Have a look and see what we just uploaded. So where did we upload it to? Oh, let me just grab that workbook again. So we uploaded it to, where are we? Uh, HH Inc 2023 demo budget. Let's go and find that. So we want HH Inc, we did it to 2023, and we uploaded it to the demo budget, and there you go, there are the numbers in NetSuite.

How do we now pull those numbers back out again? Uh, let's, well, typically what you would do is you would use one of our balance functions. You can see here this report was built using the AAL functions. Let me just give you an example of doing the same but using, um, a BUD function. So I'm going to pull out the 4000 code. I'll do it for, again, Jan 2023.

And again, just to fill in the gaps for anyone, we have these functions, A BAL, N BAL, T BAL. They all start NSGL because we're pulling from, from NetSuite's GL functionality. A BAL returns an account balance by the account number. N BAL returns the account balance by the name of the account. T BAL does it on the account type. As I say to everyone, there's only three formulas you need to remember or three functions you need to remember, although you'll see if I click here, there's function after function after function after function, but you'll see they all end in either A BAL, N BAL, T BAL, A B BUD, N B BUD, T B BUD, A P BAL, N P BAL, T P BAL. Essentially, it's the same formula just doing something slightly different each time.

So that particular spreadsheet was built using the A BAL function, so I'm going to use the A BUD function to pull that budget number back out. It's just a regular old Excel formula. Let's pull in the subsidiary, let's pull in the budget, and I think we uploaded it to the demo budget, so let's pull in the demo budget for the account. I'm going to reference our 4000 code for the period. It was, let's go Jan 2023. Click OK. Fingers crossed, we should get a number out. There we go.

And because that was just taking it from here, 3494. Oh, I was expecting that number to be 30494. Hm, it's not. Don't know why that's different. There must be a good reason behind it, but there we go. We've pulled out that number, and in fact, we can figure out where that value's come from because we can drill down to the detail. So let's try and understand why that's come out slightly different. Let's drill down by class and just see if that's a red herring. We might have some old numbers in there. No, that's come out correctly.

Let's drill down to the transaction detail, to the budget detail, and see if that's where the issue is. Otherwise, I'm honestly a bit stumped. There you go. Oh, okay, okay, I see. Looks like I previously uploaded a value to Honeycomb USA. There's our 2.1. Let's have a quick look at the income sheet. Yeah, 2.46. And you can see here that, um, where are we? Drill down detail. You can see here that I must have previously uploaded to USA. There we go. We've had a drill-down demo at the same time, Evie.

Um, you can see there that we've got a number of different numbers that have been uploaded in the past, and I just happen to be picking up, um, Honeycomb USA, and that will be because, yeah, I went down the consolidated route, whereas if I'd just gone down the HH Inc route, we should get the 2.1. There you go. We haven't hit a bug.

How does that look? Anything else to add to that, Evie, in terms of uploading budgets and pulling budgets back out again?

Evie: Think that's great. I love the drill-down demo too.

Simon: There we go.

Evie: Awesome. Okay, um, so moving from budgets to balances now, uh, we have somebody who is trying to run a formula for two different accounts to get a consolidated figure of those two accounts. Can you choose two accounts within a range?

Simon: Yes, you can. So the question is, can we reference multiple account codes at the same time? I think we even have a, remind me, Evie, do we have a document that we can send out that explains how to do different types of aggregating values?

Evie: We do.

Simon: I think there is. All right, let me, let me show you several different ways of doing aggregation. So let's insert, oh no, not accounting books. Let's insert the chart of accounts again. Why can't I click on that? There we go. Let's insert the chart of accounts. The key is, in one of the examples, is using wildcards. So let me show you different ways of aggregating multiple GL codes together. So we'll go back to our friend, the A BAL function, and let's just pull those basic balances through. And you know what? I'm going to sum it as well because that's going to be quite handy having the sum in there. Right, so there we have a simple report with some balances at the detail level So the question is again, how can we do aggregated balances at a high level? I'm going to pull that formula out sideways and start showing you different ways of aggregating multiple values together. One way is just to reference multiple cells, so rather than referencing C4, let's reference C4 and C5, and there you can see that we've got a balance of 2.7, and that should be, if we look down here, the aggregation of those two fields, those two cells, 2.78, two sorry, 2.72 eight, so you can see there that's the aggregation of 4,000 and 402. So you can just reference multiple cells. Now, typically you wouldn't reference them vertically. You could have this, the for the cells listed out sideways, maybe off to the left of the sheet, and just list them out.

Another way is to use, um, is to use an array. So, um, in fact, you know what I'm going to do, I'm actually g, I'm going to do what I just said. I'm actually going to list out 402 in that cell there, and let's reference that instead, because on the next row, I'm going to use an array. So let's do that. So we're going to reference those two there. On this row, I'm going to use an array, so I'm going to type in the array syntax, which is curly brackets 4,000 comma 40002, and what have I done wrong there? Oh, I've used the wrong bracket, there we go, there we go, and you can see there that by using that syntax on a single line in a single cell, we've actually put two GL codes, 4,000, 40002, and obviously the number here has updated. So that's just using an array.

Next, I'm going to use a wild card. So say, for example, I wanted to do four star. This would aggregate all of the four codes for me, and just to obviously remember here that these, these headings no longer make any sense because we've aggregated multiple GL codes together, so you'd probably want to type in some sort of, um, some sort of heading that was more appropriate to what you were trying to show on that particular line. I'll just leave them blank. So here we're using wild cards. This is now a wild card total, and here I can even use a combination of wild cards and array, and you can see there that total now is an aggregation of all of the four codes and all of the five codes.

There's one other way that, um, you can do, um, wild car, uh, you can do aggregation, aggregating multiple totals. In fact, there's two that I'm going to show you, um, one of which is using the name of the account rather than using the account number, and that means we've got to go in here and switch formula from the Abal to the Mbal formula. So you'll see if I hit the FX button, oh, and I've done, have I done that on the right row? Oh no, I have done that right row. Um, here you can see that the Nbal function, the account parameter is a, um, is an account name rather than the account number, and because I've got GL codes with the word sales in them, or I could even do sales star, or I could even do sales colon star depending on how I want to navigate the hierarchy, because the name field also supports the built-in hierarchies in NetSuite. You can see that as I play with the different values, I get different val, different, um, balances appear. So in this particular scenario, we're saying give me all of the child accounts of the account called sales. Here I'm just saying give me the account called sales, and here I'm saying give me the account called sales with anything else after it in the name or in the hierarchy, and you'll see as I go through each of those, I get a different value appear.

Now the final way of doing aggregation is to use, um, is to use a custom segment, and I didn't prepare for this, so let me just see if we've got any custom segments available. I'm actually going to throw those away. Let me just have a look in the advanced balances and just see if I've got a custom segment available. So you can report based on custom segments using the advanced balance functions, and here we use the option value pair and option one. I've got a feeling I don't have any configured. We might have to, we might have to roll this forwards into another demo. Yeah, if you had a custom segment configured, you'd see the custom segment here, and you'll remember, you may remember the question earlier about customizations and custom segments and how those are going to be upgraded to support a custom segment. You'll need to speak to the support team, and they will enable your custom segments for you, um, as part of a support ticket.

Once that's happened, you'll see your custom seg name in here. You can also do a custom segment value. You can see here it's none because I don't have any configured, um, but you can, you can then aggregate at the custom segment level. Now maybe we should set up another, um, maybe in the next session or the session after, I'll do a, I'll do a more in-depth demo on how to use custom segments, but the real power behind, um, using custom segments for doing aggregation is, um, you can actually take individual GL codes and within NetSuite assign them to a custom segment. So you could say you could create an income custom segment, and you could go code 4,000, 40002, 40004, 40006. All of those appear inside that custom segment, which means that the mapping that you're creating between the custom segment and the grouping that you've got within your workbooks, all of that can be managed inside of NetSuite rather than it only existing inside of your workbooks.

There's another, um, great ben benefit in that if you've got a, a big multi-company setup, you can actually set all those mappings up across all of your different subsidiaries. So whilst your subsidiaries’ individual chart of accounts don't naturally flow into each other, you can use that custom segment as a way of creating a consistent, um, customization across all of your subsidiaries, and you can then roll them up into those consistent categories again, with all of that mapping information being stored inside NetSuite and not the logic within, within your workbook. So Eve, let's, um, let's, let's try and do a session on custom segments again and show people how we can use custom segments to do that different level of aggregation.

Evie: Okay, sounds good to me. I've got a question from Mark going back to the budget upload. I think it's a good question to bring up here. Um, when you upload a foreign, when you upload a budget to a foreign subsidiary and the budget was created in that currency, is there a way to easily determine the exchange rate is used?

Simon: Oh, okay, so let's talk briefly about budgets and foreign currency. Um, so foreign currency is not, is not a function of the budget itself, it's actually a reporting issue in that NetSuite stores the budget in whatever sorry, think of the budget as nothing more than a set of values. So this is 2.46 million something. NetSuite actually doesn't care what it is. It's when it comes to reporting it back out that the NetSuite and Solution 7 interpret this number as something in a currency. So where's that currency determined? Well, it's determined by the budget category type, and you can see here that this particular budget category has been set up using a local type, and what local means is that the, is that the currency for reporting purposes, the currency will be the reporting currency of that subsidiary, and because that's a US dollar subsidiary, you can see here that it's a, it's expecting these numbers to be uploaded in US dollars.

Now you can see here this is a read-on field, so where's that set up? And that's set up when you create a budget. Now you can create a new budget either going into here and hitting the plus button or, and I've got to remember where this is, I believe it's under accounting lists. So set up accounting, accounting lists, and a budget category is just a form of accounting list. So if I go in here and hit the new button, you can see here that by default it's chosen a budget category accounting list, and it's in here that I can tick whether this budget is a global budget or a local budget. So you remember for the one that I just showed you, it was set to local. Slightly confusing this, in that you don't have a dropdown to flick to local and global. Um, when you set the budget category up, you choose it to either be global or not.

When you set it to be a global budget, NetSuite will expect the numbers that you upload to be in the currency of the topco in your hierarchy, so that will determine the global budget for the entire, um, NetSuite instance. Sorry, when I say the global budget for the entire NetSuite instance, I mean the, the global budget for this budget category for your NetSuite instance. So we've, we've actually seen customers have a budget called global and have a separate budget where they upload the individual, um, local budget account amounts, and then you can use that as a way of doing, you know, FX type analysis.

But again, the important thing to state is that when you upload the numbers, NetSuite just sees them as numbers from a budget perspective. It's only when they're reported back out that NetSuite and Solution 7 use this flag to determine whether to translate those numbers or not. So when you upload the numbers, if it's a global budget, you upload them in the topco currency. When it's not a global budget, so it becomes a local budget, you upload them in the currency of that subsidiary, and then when we report those numbers back out, we then do the translation, so that if you've got a global budget and you're at the subsidiary level, we need to convert that into the local currency. If you've got a local currency budget and you're reporting at the global level, then we will convert it into the global currency, um, for you, and we'll do all the FX, um, all the FX conversion for you so you don't have to worry about it.

So again, just to reiterate, numbers are just numbers, and it's the reporting side of it when the, um, when the different budget types kick in and determines how we do the currency conversion. I hope that answers the question.

Evie: Thanks, Simon, that certainly made it clearer for me as well. Um, all right, guys, I think that's about all we have time for today. Um, thank you for all your great questions, and thank you, Simon, for answering them as always.

Simon: And sorry again if we didn't get round to your questions. We, we tried to get through as many as possible, um, and you know, if, if there's a specific question that hasn't been answered, just drop us an email and we can always jump on a call or we'll try and roll it forward to the next, um, to the next session.

Evie: Yeah, absolutely. I was just going to say that, um, do you want to pull up the support email on the screen so that if anyone has any questions, you know, feel free to just shoot them over to our support team and either I or Maria will get back to you guys. Awesome. Uh, we hope you enjoyed the session today, um, and you know, additionally, if you have any comments or feedback, uh, feel free to shoot those over as well. We do appreciate hearing what you guys think, um, and we'll send the recording out to you guys in a couple of days as well once the team have had time to get it into a good shape. All right, so thank you for joining, and we'll see you in the next one.

Simon: Thanks, everybody. Bye-bye.