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

Evie: Looking like we're about ready. Hi everyone, welcome to our webinar. Thank you for joining us this morning. My name is Evie. I am the Customer Success Manager here at Solution 7. We have disabled the chat, um, for questions today, but if you do want to enter a question, you can do so in our Q&A box down at the bottom of your screen. Um, any questions that get submitted, we will try and answer in the Q&A portion of the session.

Today's webinar is a repeat of our Ask the Expert sessions. Without further ado, I'd like to introduce you to our founder and CTO, Simon Miles.

Simon: Thanks, Evie, and good morning everyone. Welcome to today's session. Um, as Evie said, this is a repeat of a session we've done a couple of times now, um, where we're going to walk you through, um, a specific use case of Solution 7. Um, in this case, we're going to be looking at how you can integrate a budgeting cycle with Microsoft Teams and Microsoft SharePoints. Um, it's an interesting session, um, a bit like working with, um, children and animals. Um, in the media, demoing live software never, never, ever really goes to plan, so we've had a couple of hiccups, um, each of the times we've been through it. I'm hoping to get through this session now that we're doing it a third time without hitting any problems.

So just to confirm, um, what the session is going to look like, I'm going to be working with Excel, going to be working with SharePoint, Microsoft Teams, pulling all that together using Solution 7 to work you through almost like an end-to-end budgeting cycle, um, that takes around 30 minutes, hopefully, to leave enough time then for a Q&A session at the end. And as Evie said, please feel free to throw any questions into the Q&A box, and we'll hopefully get around to your questions, um, towards the end of today's call. Uh, just to say, this session is being recorded, um, and, um, when the recording is available, we will send it to all registered attendees, um, probably now… what's today? Today's Thursday, so probably early next week.

So let's look at what our budgeting and planning options are within NetSuite. Um, you have a few options available. Um, you can work natively with the NetSuite budget input screen. Um, there you can literally just key data into NetSuite. In fact, let me bring up the budget input screen here, so you can work directly with, um, this particular screen where you can select different criteria, and you can enter budgets, and you can actually ask any user to enter budget information in NetSuite. They will need a NetSuite login, and you just point them in the direction of the budget screen and ask them to enter the data.

A couple of other options, um, include, um, what a lot… what we see a lot of our customers do, which is building, um, budgets out in spreadsheets and then uploading them using the NetSuite CSV input routines. So you have to build your workbook, and then you have to take those workbooks and shape them into the form that NetSuite needs for the CSV input, and then you can easily upload them into NetSuite. Um, you've got other tools like, you know, enterprise planning tools like NetSuite's own NSPB or Adaptive Planning. Um, I would typically call those enterprise planning tools because they are… they are large, process-oriented tools. They… they are fairly large tools. They typically require a project to implement them, but they're very scalable, and you can roll them out to the entire, um, entire enterprise. On a smaller scale, um, you can use Solution 7. Um, we integrate with NetSuite's Budget Engine, and, um, Solution 7 is actually a great tool with our features and functions to build a, a pretty cool budgeting cycle similar to the Sprint to the CSV upload, but rather than having to do a lot of the steps that you would do, um, by hand, we can automate a lot of those processes, and you can build a relatively sophisticated budgeting and forecasting process without going to the expense and the implementation cost of a tool like NSPB or Adaptive Planning.

What does a budgeting process typically look like with Solution 7? Well, like everything, we're essentially building templates. Whether they're reports, whether they're budget templates, you can build templates. We can publish those templates for you, and in the demo that I'm going to give you, we're going to publish those templates via Microsoft Teams. Um, once your users have interacted with the templates that you've built, we can then take the data and push it back into NetSuite without having to sort of reformat the data. You literally just map the data, and then you tell Solution 7, "Hey, grab this data and push it up into NetSuite." So it creates a relatively simple process that you control, with great-looking templates and all the benefits of using Solution 7, but with the simplicity of the integration we've created in Microsoft Excel.

So what does that budget cycle look like in terms of detail? Well, there's quite a few steps. I did say this demo might go wrong at some point, so we've got quite a few steps to go through. I'm just going to walk you through the different steps that we're going to… I'm going to take to complete the cycle. The first thing we're going to do is we're going to create a channel in Microsoft Teams that's going to give us a shared area in Teams, or if you work with SharePoint, in SharePoint, that we can start to consolidate the data. I'm going to build out a master template using our list and functions capability, and that template will actually be the data entry form that your users will interact with. We can then map that template back onto NetSuite so that Solution 7 knows how to pick up the data from the template and to push it up into NetSuite. We have a feature in Solution 7 called automation that will allow us to grab that template and actually push it out to the business. We're then going to start to lean on Teams and the OneDrive sync engine to actually take the data from our machine and actually physically push it up onto Teams and SharePoints. I'll then show you what those look like, what those templates look like through Excel Online, so you're not limited to users having to work with a Microsoft Excel physically installed on their machine. If you want, though, your users to interact via Excel Online, they're just regular spreadsheets that we're pushing out. Um, once we've been through that process again, the OneDrive engine will sync those files back down onto our machine, and we can use the Solution 7 budget upload routine to actually take those numbers and push them into NetSuite. Finally, we're going to view the consolidated budget in NetSuite.

So I've got a simple report that I'm going to show you, and this is a pre-built report where the numbers will start to flow through into the budget section, and then we'll have a quick look at the drill-down to see what the detail looks like. And I'm hoping that will then complete this sort of end-to-end budget cycle for you. Um, for any new customers… oh, sorry, sorry, for anyone not currently using Solution 7, um, just to say, super easy to install. It's only a one-hour implementation. You will literally be installed, up and running, and building reports or building budget packs with Solution 7 within that hour. And again, our offer to everybody is we want you to take a free trial. We want you to try the software before you have to commit to it, so we encourage customers to, um, to take the trial, to play with it, and to, um, really try it out for themselves before committing, right?

I will come back to the Q&A at the end of my demo. So let me jump into my demo, and I'm going to start off in Microsoft Teams. So this is our Microsoft Teams instance. As you can see, these are all the different Teams we have set up within the organization. And within the finance team, I'm going to create a channel, and I'm going to create a channel, which is like a subsection of a team that we're going to use to manage the budget templates, and that we're going to… that we're going to create. So let's add a channel to our finance team, and I'm going to call it Budgets 2024. I'm going to make it a private channel. This is actually our live Teams instance, so just so I don't annoy everybody else, I'm going to create that as a private channel just for this session. Hit the create button. Teams added the channel. I'm going to skip adding new members. And you can see here we've now got a channel in which we can start our budgeting process. Associated with every channel in Teams, you get a set of files, and I can sync those files onto my machine, so effectively I can create a folder on my machine that will reflect that structure that we have in Teams.

In addition to doing that, you'll also see we have this opening SharePoint button, and you'll see when I click that and I bring the window over from my other screen, we actually get an area in SharePoint that our… that our users can go to if they don't have Teams installed. So they can just fire up a browser, go to the SharePoint site, and they'll be able to pick up their files from that area. Let me just show you that same folder on my machine, but let me just bring that over. This is that folder on my machine. You can see that in my Solution 7 section, I now have the finance team, and I have the Budgets 2024 folder.

So let's start thinking about what our template needs to look like. So what I'm going to do for you is I'm actually going to build a template that looks something like this. So let's just load that up into Excel, and as with all multi-monitor setups, everything appears on the wrong screen, so let me pull that over. So, to start with, I'm going to build out this particular… this particular template. So this is going to be our data capture template that we're going to use to, um, to grab the data from our users, or give them a form that they can enter the data, grab the data from our users, and actually push it up into NetSuite. I'll show you another template as well. This is going to be the template that we're going to use to actually see the data once we've, um, once we've uploaded it into NetSuite. So we've got a simple P&L, um, and when we look at the 2024 budget, we're going to see those numbers start to flow through into this column here.

Let's actually go in and look at how we would go about building a template. So I'm going to start with a new workbook, and I'm going to build out the template that I just showed you there. So this isn't… this is a one-world instance of NetSuite. So let's have a look at the ribbon up here and see how we can start to pull our data through. Um, I'm going to start off by using a pop-up, and because this is a one-world instance of NetSuite, I'm going to start off by inserting a pop-up of subsidiaries. Now, the one thing I'd like to say to all users is just recognize how familiar this is. This is just regular Excel. This… this dialog here… this is actually a dialogue that we've built, but it's designed just to look like native NetSuite, uh, Native XL. So all I have to do is click on OK. We're going to go off to, great, NetSuite, grab the subsidiaries. That gives us the ability to choose which subsidiary we want to insert into the sheet, and I can choose any one of my subsidiaries directly from NetSuite. I also need to choose what budget I want to run this against, so again, I'm going to use another pop-up, and I'm going to insert a pop-up of budget categories. Let's have a look and see what budget categories have we got. You can see here I've got Budget Forecast, Q2 Forecast, and a Test Budget. Actually, let's create a brand-new budget within NetSuite and let's pull that through. So again, let's pull up a copy of NetSuite, and let's add a new budget category into NetSuite. So here, from the budget input screen, I can hit my plus button, and I'm going to call this, for the purpose of the demo, let's just call it our… let's call it our demo budget. Now, for anyone who doesn't know what the global tick here means, um, that determines, in a one-world instance of NetSuite, that determines what currency you want to enter the budget values in as. So if you tick Global, all numbers that you enter into NetSuite must be entered at the Global Currency level. So if it was a Sterling company with a U.S. subsidiary and a Euro subsidiary, and you ticked Global, all the numbers that you input for all subsidiaries would need to be in Sterling.

Um, if you didn't tick that and you left it as effectively the local budget, then the numbers that you enter at the top level would be in US dollar, it would be in Sterling, and then for the US dollar subsidiary, you would enter in US dollars. For the Euro subsidiary, you would enter in Euros. So this just determines the currency, or the currency values, that you're going to enter.

Let's save that. So we now have a new budget category in NetSuite. Let's jump back into Excel and pull that through. So I can hit the refresh button here, and we should get a new budget category appear, and there's our new demo budget. We're going to run this budget for 2024, so I'll just enter that.

Next, I'm going to insert the chart of accounts. So the easiest way, I think, to get the chart of accounting, you could type it in if you want to, literally just key in all the individual codes. I'm actually going to grab them from NetSuite. So again, up here on the ribbon, we've got a list type called a column list, and I'm going to insert a list of accounts by number.

And why… if you're not letting me do that, I'll… because I'm edited to sell, that's why. Let me try that again. Here, we need to choose the subsidiary. Here, we need to choose the account. So, because I want this to run across the entire business, I'm going to set the subsidiary to be our Consolidated subsidiary, and I'm going to enter a range of account codes. Now, again, I could enter a single code here. I could use wildcard, so if I type in four followed by an asterisk, that would give me all of my accounts that start with a digit 4.

We've also got a notation called the array syntax. This is actually a notation that Excel understands. This is a common way of selecting multiple values, and you simply enter a list of values surrounded by curly brackets. So here, for example, if I type in Four Star and Five Star in curly brackets, that's going to give me all of the accounts that start with a digit 4, and all of the accounts that start with a digit 5. So in this case, it's our income codes, and it's either our cost of sale codes or our expense codes.

Now we also need to build out some periods that we can use to capture the data, and I always find the simplest way to build that is just use formulas. So three simple formulas we can use: Excel's DATE function to give us January, we can use Excel's TEXT function, that we can take that January calculation and, if we format it with three M's and four Y's, we can take that January date and convert it into a period. And this is the period format used by NetSuite.

Finally, another formula we're going to use: EDATE, EDAY, EOMONTH. They kind of do the same thing. EDATE will allow us to do basic date arithmetics. We can take that date and offset it by one, and now I can start to copy and paste my formulas to build out all of my financial periods. And from here, I can start to insert subtotals, and I can start to build out my template.

Now, rather than me going through and actually building all of this out, what I'm actually going to do is I'm going to close that and let's refer back to one I built earlier. So this is the template I showed you right at the beginning. So this is that same template, but all formatted nicely and ready to send out to our end users.

Now, to also say, if you want to use Solution 7 formulas to pre-populate the report with numbers, there's nothing stopping you using our balance functions, our budget functions, to actually pre-populate this spreadsheet with maybe historic numbers. Let's say, for example, you want to take the balance on that account for last year and apply… I don't know, what inflation is these days… a thousand percent uplift. Um, you could add values, you can reduce values, we're just using Excel formulas here. You could even have an additional column where maybe you want to set an uplift as a percentage, and you actually feed the uplift percentage through a formula to give you a first cut of a budget or a first cut of a forecast automatically.

In this scenario, I'm going to be doing zero-based, um, zero-based, um, planning. So I'm sending out empty spreadsheets, and I'm basically saying to my users, give me the information that, um, that you know about, to provide me with the budget numbers that we can then push into NetSuite to form part of the budget.

Now, before I go any further with this particular example, just to say, we're kind of doing a top-down approach to budgeting. If you wanted to do more of a bottom-up approach to budgeting, where you actually enter the detail rather than entering the summary information, um, we actually worked with a customer in South Jersey, and they very kindly said we can share this spreadsheet with users. So if you want to do more of a bottom-up approach to planning, we also have, um, sample templates that we can share with you, where you actually get your users to enter the detail and the individual amounts that they're expecting for each of the different periods, and these are different codes that they may want to apply values to. Maybe they want to put a commentary into the report… uh, sorry, a commentary into the budget pack. So this will give you a more bottom-up style of planning, and this, again, Solution 7, when I show you the budget import side of it, we can pick this up and post it into, um, NetSuite automatically.

So, regardless of whether you want to do top-down, regardless of whether you want to do, um, bottom-up, um, all of these are supported with the way we talk to NetSuite.

Let me jump back into my original template. So again, this is the top… this is a top-down approach. We are just providing high-level numbers. Let's look at how we can actually take those numbers and push them out to our end users.

So how are we going to do that? Well, we have a feature in Solution 7 called Automation, and it's this automation process that we're going to use to, um, create these… these sample templates. Think of automation as a bit like mail merge in Excel, in Microsoft Word. So I'm sure you've built a letter or an email template where you have a database of names, and then you can publish that template out. Our automation engine works pretty much the same way. Um, the important thing is, the first thing you have to do is you have to tell Solution 7 what cells in your workbook are the dynamic cells. So these are what we call the automation cells.

So I'm simply going to add a cell, and I'm going to click on the subsidiary. So I'm effectively creating one budget per subsidiary. I click on OK, and I click on OK again. Solution 7 will say, "Hey, you've inserted a pop-up list here. Would you like me to use that pop-up list to drive the automation?" Let's say yes, and we get a really great automation sheet with all of the different possible values.

Now I don't want to run a budget for our Consolidated entity, so I'm simply going to hide that row, and I'm not going to run it for our Eliminations entity, so let's hide that row as well. I now need to decide how I want to group the workbooks, and what I'm going to do is I'm going to take some of these worksheets, sorry, and I'm going to put them into a book called Americas. So I'm going to put Inc., Canada, Mexico, and USA in the Americas book. Uh, sorry, for Europe and the UK, I'm going to put those into a book called Europe.

And as you can see here, we've actually got the ability to enter an email address as well. So if I was publishing these out via email, just sending them to people's inbox, I can provide an email address, but because we're using SharePoint, I'm going to leave that email address column blank.

Now, for anyone that's used the automation feature, you probably don't know, but there's a hidden cell here that allows you to choose additional functionality associated with that row. So here I can create something that we're going to call a token. Now tokens are used in the automation process where we can't just use regular Excel formulas, so you create what's called a token, and then you can embed your tokens. So I'm going to use the tokens to name the book, sorry, to name the sheets within the book.

So we've got our Inc., we've got our Canada. Unfortunately, Excel is going to fill the rest in for me. We've got our Europe, Mexico, UK, and USA. So what we're going to do there is we're going to use this token to rename the master template as part of the automation process.

So we're good to go. Let's actually run that process through. So I'm just going to double-check the book here. We're running it for our Consolidated entities. We're old test, okay, we need to change that and switch that over to our demo budget. We're running it for 2024, and we've got the shape set up correctly.

One other thing we need to do as well before I forget is we also need to, what we call, map the workbook. Now this is the final piece of the jigsaw in terms of setting up our template.

So what does mapping the workbook mean, and what does that look like? So what we're doing here is we're mapping the sheet and effectively telling Solution 7 where all the component parts are that we can then pick up and post into NetSuite as a budget. So we need to know things like, is our subsidiary, these are the financial periods, these are the accounts, and you'll notice that we're actually able to skip that blank row. So this blank row 12, Solution 7 will just skip that. It sees that, it doesn't see that as a value that needs pushing into NetSuite. So that's how we can include our subtotals.

We need to choose the budget category that's going to go up to our demo budget. Let's have a look and see what else we've got: class, department, location. Okay, these are all the standard dimensions in NetSuite. I'm not going to upload to any of those. And just also say that if you have custom segments set up in your NetSuite instance, you can also upload to custom segments. Um, if you want to do that, you will need to define your custom segments with the GL impact option ticked, and you will need to enable budget import on that custom segment as well. If you'd like to be shown how to do that, drop an email through to support. We can show you how to set that up. It's super easy.

Now that we've mapped that, let's actually push those spreadsheets out. So I'm going to save copies of the sheets using the automation definition. So using that database that we've just defined, we're going to push out the master template. And for the sheet name, I'm simply going to enter curly bracket one, curly bracket. This is our token syntax, so this is where we're using the token embedded into the sheet name to push out a named sheet. Let's click on OK and let that run.

So, if I set this up correctly, that should now be going into our Teams folder. So you can see here, in the background, we're just working our way through the Automation, and we should start to see some, um, books appear in the automation folder. So there's our… there's our new books that have appeared, and you can see here that the automation has completed.

Now, the real hero behind this entire process is actually the OneDrive sync engine. So you can see here, this is the OneDrive sync engine doing its thing. It's now picking up those files off of our machine and pushing them up onto Microsoft Teams, up onto Microsoft SharePoint. So this is the Teams folder. You remember, we set that up right at the beginning of this particular demo. You can see there's the master template that I just copied in, and these are the two sheets that we created as part of the… also the two books we created as part of the automation process.

Let's have a look at the Americas book. I can simply click on it, open it, and here you can see it opens nicely in Teams, and I can even interact with it. So I can go through and say, right, let's… let's add some values. So let's put a 100, 100 pounds, euros, dollars, whatever there. Let's put 100 here, and let's just… let's just throw some numbers into this particular sheet. Notice here, we're looking at Inc. Do you remember we used the token to rename the master template? So we've got sheets of Inc., Canada, Mexico, USA.

Let's have a look at Canada. Let's throw some numbers into Canada. This would be 100 Canadian, or maybe a hundred Sterling if it was a global budget. So I'm just gonna put some random numbers into a few cells. I won't go and put anything into USA and Mexico, but you can see here, this is the Mexico sheet, this is the USA sheet. And again, a really nice feature of automation is you can build sheets on a one-to-one basis. So if you want one book with one sheet for one user, you can. If you want multiple sheets for multiple users, you can. You build out the output shape as you want to see it.

Let's close that. Now, for the Europe book, let's have a look at that through SharePoint. So I'm going to open that in SharePoint, and this is the SharePoint folder. So again, there's the master template we threw in, there's the Americas book, there's the Europe book. Let's open up the Europe book. Again, it's the same experience, and we can go through and start typing in some numbers for Europe.

Now, we get asked a lot: can you use Solution 7 to create authorization processes? So can you have a draft budget? Can you create an accepted budget? Well, there's a really great way of doing that within SharePoint, so let me just show you that quickly.

So let's go… let's go back into… let's go back into SharePoint, and I'll just show you how to configure SharePoint so you can enable document authorization. I'm not actually going to switch it on, I'll just show you where the settings are. So within our SharePoint folder, if you go up here to the settings cog, and you go to library settings, and from there you go to more library settings… Microsoft have done a good job of burying this… we can then go into this option here called versioning, and you'll see there's a couple of useful options in here, one of which is Require Content Approval.

Now, what that means is that whenever anybody edits one of those workbooks, it will mark that spreadsheet as unapproved. As the, um, as the person running the budget cycle, you can then look and review those spreadsheets, and you can approve individual spreadsheets. If another user goes back in and edits a spreadsheet, that will reset that approval status. Now, this won't stop you. This won't prevent you from being able to upload it into NetSuite, but what it at least means is that you've got a nice list that you can work down to see which of the budgets have been approved, or which of the workbooks have been approved, and which of the workbooks remain unapproved.

There's another nice feature down here called Check-In and Check-Out, and what Check-In and Check-Out requires is that if you enable that feature, a user is required to check out a template to themselves before they can start working on it, which means you don't then get two people accidentally editing the same file. And with the, um, with the approval switched on, you check out a workbook, you work on it, you check it back in, and then a central user can then approve that workbook. You can see the approval status before you then push the numbers up into NetSuite.

So, say I'm not going to go through that in this session, but it's a great way of introducing some element of control and workflow into the process. So we've created our templates, we've published them to our users, our users have entered some numbers. Let's look at how we can take those numbers and push them back into NetSuite.

So jumping back into Excel… so jumping back into Teams… let's open up one of those workbooks, actually in, um, actually in Excel itself. In fact, no, I'm not gonna go into Teams. I'm going to pull that off my file system. So let's open it up in the file system, and I'm going to browse. And if we scroll down into our folder, again, the OneDrive sync engine is sat there in the background, pushing and pulling files backwards and forwards. So if it's done its stuff correctly, we should now have up-to-date copies of the files on our machine.

So let's open up the Americas book, and let's go into the back into the Inc. tab. You can see there, these are the numbers that our users entered onto the web. Those have now been synced back onto my machine. So there's the Inc. numbers, there's the Canada numbers. Let's have a look at the Europe numbers. Again, we now have those numbers back on our machine as well.

Let's have a look at how we can take those numbers and push them up into NetSuite. So we use the option here earlier to map the workbook. Now, the important thing here is we mapped the master template. So you'll see here, when I go and actually look at that mapping, that mapping has flowed through onto every copy of that worksheet. So we don't have to remap the worksheets; those mappings already exist within each of our templates.

So take those numbers and push them up into NetSuite. It's really simple: just go to the Budgets and Forecasts menu, and for each workbook, upload it to NetSuite. And again, just see how easy this is. We don't have to take it and convert it into a CSV file and kind of get the right shape and push it through, map it through the CSV engine. We literally just take the workbook in the form that it's already in, hit the Upload button, we get a confirmation screen to say, "Look, you're uploading it to an account, you're using this role, is that correct?" Be careful when you do this for the first couple of times: maybe push it into a test budget, or maybe upload it onto a Sandbox account before updating live.

Let's click on OK. We go off, validate the workbook, check the mapping, grab the numbers, connect to NetSuite, and we upload them into NetSuite. Let's do the same for the… let's do the same for the other workbook. So we've got numbers in Inc., and we've got numbers in Canada. We didn't put anything in Mexico. No. So let's upload those.

So again, Solution 7, upload to NetSuite, Inc., Canada, upload. Okay, again, we connect to NetSuite, validate the workbook, and push the numbers up through the NetSuite API. And there you can see we've successfully uploaded records into NetSuite.

Now, let's have a look at those numbers through NetSuite. So again, I'm going to use the NetSuite Budget input screen to actually view those numbers. So we're looking at… let's look at the Canada numbers. We're looking at Canada, we're looking at them for 2024. We're in the budget, the demo budget, and if I restrict this to existing, that will show us the numbers that have been successfully uploaded. And you can see here, without having to key anything into NetSuite, we've grabbed those templates and we've pushed them up into NetSuite.

So there's our Canada budget. Let's have a look at our Europe budget. There we go, and those are the numbers we pushed up for Europe. Now, it would be better to be able to view those in a much, um, in a much more consolidated form rather than looking at the individual numbers within the Budget input screen. So you remember I said at the beginning of this session I was going to use a report to actually do that.

So let's go into one of our P&L reports and actually see those numbers flow through. So we're looking at 2023. Let's just change that over to 2024, and my pop-up buttons are not appearing. Let me type that in. So we're looking at Jan 2024, and the budget we're interested in is not the original budget, it's going to be called the demo budget. So let's pull that through. So you'll see, because 2024 hasn't happened, the actual columns are all blank, but can you see here, these are our budget numbers coming through. And you'll remember that we entered everything as a hundred dollars, a hundred euros, um, 100 pounds. So those have all been converted into the correct currency, um, by Solution 7, and we can actually look at those individual numbers just by drilling down.

So the final, final piece of the jigsaw is, as you start to consolidate the numbers through, if you want to see them in more detail, you can very easily get the underlying numbers through our drill-down feature. A lot of customers forget that this drill-down exists. So we're looking at a figure of 388 somethings in this case. If it's a Sterling top Co., then it's going to be in pounds, and I can drill down by subsidiary, by count, by class, or I can go straight to the budget detail. Let's have a look at that number by subsidiary. You can see there that we've broken out the different subsidiaries. Again, we're showing them in the top-most currency, so these are converted into Sterling. Let's take that top number, that Inc. number, and then that's actually drill down there and look at… drill down into that and look at the budget detail.

And you can see these are the individual budget values that have been pushed into NetSuite. So if the budgets aren't quite looking right, drill into them, find your way around them, figure out where the problems are, edit the templates, push them up into NetSuite. Again, when it comes to running a revised forecast, there's nothing stopping you as well, just reusing the existing templates.

So, unlike, say, the CSV import, where you've got to take that data and kind of convert it into a different shape every time, there's absolutely nothing stopping you. If you ever want to revise your numbers, these workbooks are all still perfectly good enough for going in, editing, revising the numbers, and then just uploading to a different budget category in NetSuite, to a different set of numbers. So you can have the original budget, you can revise the budget, you could have a Q1, a Q2, a Q3 forecast, all working off the same original spreadsheets.

So, just to review what we've been through there… that… that's the demo portion of this session.

Um, just to review what we went over there, so again, we created a budget in Teams. So we created a channel in Teams, um, we created a master template, we published the master template using the automation engine. That was then pushed out to a… shipped to a folder on my machine that was synced with Teams. Our end users were able to go in and edit those documents. Again, the sync engine… I can't… I can't rave enough about it. It grabs those sheets for us, pushes them down onto our machine. We can open them up in Excel. Having mapped those workbooks, we can then take them and push them up into NetSuite, and then we just use a report to, um, to view the consolidated budget.

So again, we're not trying to… we're not trying to be an Adaptive Planning, we're not trying to be, you know, an entire full Enterprise Planning and forecasting tool, but it's got to be better doing it this way than trying to manipulate spreadsheets, um, manually, and we can get close to that Enterprise Planning capability just using, um, simple Excel spreadsheets.

Foreign. So, as I say, that's the end of the, um, the demo session. Let me jump back into my slide. I think Evie… hopefully you should have some questions, and we can jump onto the Q&A section.

Evie: Absolutely, thanks for the demo, Simon. Um, all right, the Q&A portion. Uh, Simon, I have the first question. Is this session being recorded.

Simon: So this is our most common question. Yes, it's being recorded, and yes, it will get sent out probably early next week.

Evie: Fantastic. All right, um, so I have another question in the Q&A box. Um, somebody has asked, how do the workbooks go back into being a consolidated sheet? And so, if you'd like to go over how we can consolidate our budgets again…

Simon: Oh, okay. So the… the budgets, the consolidation side, is just using regular Solution 7 formulas. So let me take… let me take the original template for you that we didn't actually push out to the end users. We just used it as a template that we then split up. I'm gonna… I'm gonna hijack this workbook as a way of pulling numbers back in. So you'll remember that we uploaded to the demo budget for 2024. Um, here you can see I've got Consolidated selected, so let's actually go in and just use a formula to pull the consolidated budget through.

So, um, when we built the template, I showed you how to use a pop-up, I showed you how to use a column here, we're going to use what we call our functions, and you'll see that we've got a series of budget functions. The budget functions allow us to pull budget values from NetSuite. I'm going to use this one here called the… what I like to call the A-Bud function. Notice all our functions start NSGL; they're just NetSuite GL type functions. A-Bud, as you can see when I hover over it, is an Excel formula that's going to return a GL balance by the account number.

So let's use that to actually pull that consolidated balance back. You'll see the function has a series of parameters that we need to provide. So the question was, can we look at it from a consolidated perspective? Yes, absolutely. Let's reference our consolidated cell for the budget category. You'll remember we uploaded to our demo budget category, and for the account, let's here look at our 4000 code, and for the period, we're going to reference Jan 2024. Notice all my dollars have been entered so that I can just copy and paste the formula around the workbook. I click on OK, and that's how we get the numbers back out of NetSuite.

So you can see here, if I copy that across and copy it down, I'll only… I'll only do the income section. I won't bother with any of the costs, but there you can see these are our numbers that have been pushed up into NetSuite, now being returned back out of NetSuite using our budget functions. And again, don't forget, when you do pull them out of NetSuite, every formula supports drill-down. And in this case, let's drill down again by subsidiary. Like we did earlier, we can drill down and actually get into the budget that's been pushed into NetSuite.

Evie: Fantastic. Okay, and another question on the consolidation as well. Can you consolidate before uploading into NetSuite?

Simon: Yeah, that's a really good question, actually. So yes, of course, you can. It's Excel. The purpose of what we're trying to do here is actually to not consolidate outside of NetSuite. We're trying to consolidate inside of NetSuite.

Let's talk briefly about the downsides of consolidating outside of NetSuite. Let's pretend you've got six or seven workbooks. If you then consolidate those numbers into another workbook, what can happen is, when someone edits those workbooks, if you delete a workbook, if you move a workbook, if you rename a workbook, if you rename a folder, what can happen is all those links just break. And then getting to the bottom of why all those links are broken can be a real pain.

So what I personally recommend is, when you do your consolidation, push everything up into NetSuite. It's a database; it's what it's good at. Now, you don't have to push your numbers directly into the live budget. You can create effectively a work-in-progress budget, and you can consolidate in a work-in-progress budget. So again, if we loop back around our cycle again, get the sheets out to your users, get the users editing them, get those numbers pushed up into NetSuite, super easy to do, upload them into a work-in-progress budget, and then when you actually want to create the real budget, you've got two options: you can either upload those budgets again into the live budget, or within NetSuite, there's a copy budgets feature.

So here, Financial, Copy Budgets, and what you can actually do is you could take your work-in-progress budget and then just use the copy budgets feature to take those work-in-progress budgets and copy the numbers into the live budget. So to answer your question, yes, absolutely, you can consolidate data outside of NetSuite, outside of NetSuite in Excel using your own knowledge of Excel and joining workbooks together. Personally, I think get everything into NetSuite because it's easy to push it in, and it's super easy to pull it back out again. I hope that answers your question.

Evie: Great thanks, Simon. We have a question from Francis. Um, could you please walk through the various function options?

Simon: I walk through… ah, okay, so that's a really good… that's a really good question. Um, normally that would take an hour on its own, so I will try and give you a bit of a consolidated walkthrough. So these are our functions. The simplest thing to do, if you want to work out what each of them do, is to hover over them. It will tell you this function returns an account balance by the account number. This function returns an account balance by the account name. This function returns the account balance by the account… come on, Excel… there we go… returns the account balance by the account type.

So as I say, the simplest thing to do is just hover over the formulas, and they will tell you what they do. Um, just go into them in a bit more detail, you'll see as you go into each of these different categories, there's pretty much three formulas in each of the different categories. I tend to refer to them as the A-Bal, N-Bal, T-Bal function, or the A-Bud, N-Buds, T-Bud function.

So as I said earlier, A-Bal is going to return an account balance by the account number. Now, let's say for example you wanted to build a variance report. So let me pull up that variance report I showed you earlier. Let's say you wanted to build a variance report. In this column here, we've got our actuals. How do we populate our budgets? Well, typically, what I say to customers is that for every A-Bal function you use, also, for every A-Bud function you use, there's an equivalent Bud function. So if you were using the A-Bal to return a balance for an individual row on a sheet, you would typically use the A-Bud function to return the equivalent budget.

And you'll see here, if I look in the individual formulas here, you can see we're using the A-Bal function here, you can see we're using the A-Bud function. And the simplest way to edit a formula, once you've inserted it into the workbook, is… try, I wouldn't bother trying to edit it by hand. It just gets confusing. What I recommend is you use what we tend to call the little FX button, which is this one here. If you originally insert function at the button, what it will do is it will take you into the Insert Function dialog, and it will help you through all of the different parameters that you need to follow.

Now, in terms of what the different functions and the parameters do, again, the simplest way to figure that out is just to play and just to insert formulas into the workbook. Again, you can reference the sheet, or you can type values in, or you can use the lookup button to grab values from NetSuite. So, for example, if we wanted a consolidation balance for all of the four codes, the asterisk is the wild card for Jan 2024. There's a simple formula to do that that will pull in a balance that's a single period. If you want a range of periods, you enter the from period and the two periods. So let's say I wanted December 2024 as the two period. Again, I can click on OK. Let me, in fact, let me change that to 2023, so we get some numbers out. We don't have anything posted to 2024.

So here we're looking at all the four codes and the aggregated balance from January through to December. There we go, we've got a number. This time, again, the two period is optional. If you leave the two period out, you just get a single period balance. Um, in terms of the additional parameters, if you scroll down… a lot of people don't see the scroll bar here… if you scroll down for these original balance functions, you can filter by class, by department, by location. And if you're using OneWorld, you can decide what currency you want to see. So that's based on the parent company. So if I was looking at a USD company and it had a Sterling parent, I could use the lookup button to grab the parent company and show the child, um, balances in the currency of the parent. And you can kind of flip-flop between the two.

Same for the budgets. Exactly the same formulas, except an additional parameter: subsidiary budget category. So these… all the different budgets. So let's go back into that demo budget that we uploaded. The account range, I'll do four-star again, and again we'll do Jan 2024 because we uploaded to 2024, two through to December 2024. And it's the same parameters underneath, so last, apartment, location, and so on. And there's our demo budget that we loaded a few minutes ago.

Um, moving on from the original balances and budgets, why have we separated these out? These were the first set of functions we built with Solution 7 for NetSuite. The advanced balance functions have come along more recently, and they give you more control over the data that you want to return. Now, they interact with NetSuite in a slightly different way, so they don't always perform as well as the original balance and budget functions, but you do get additional functionality.

Notice these: Ending AP-Bal, NP-Bal, TP-Bal. What does the additional, additional P mean? Well, this is a period balance function. Because against those, we've also got the equivalent functions by date. Now, what does the date give you? It gives you the ability to actually filter at a more… at a deeper level, at a more granular level than period reporting. So, for example, if you want to do weekly reporting, the by-date functions actually allow you to go down to individual dates. So daily postings, weekly postings, um, can all get picked up, and those are actually picked up by the document date What we would call in the UK, the tax point date. So we're not using the financial period, now we're using date. So, for example, if you wanted to pick up the week before Christmas this year against the week before Christmas last year to do seasonal analysis, these date functions are great. And again, notice it's the same three functions: A-Bal and D-Bal, T-D-Val.

I'll show you what the period balance, the AP-Bal function, looks like. Again, it's the same interface, again it's substitute three, again it's a range of accounts, period, and the difference is we've got what we call our option value pairs. So where for the original formulas we had class, department, location, here we've got option one, value one, option two, value two. Think of these as like filters and functions, and you can do things like filter by class, department, location, project, prospect, sales rep, transaction type.

So, for example, if you wanted to look at bank postings and you wanted to pick up the bank side of a bank posting, you could filter on the transaction type. If you wanted to look at the VAC code on invoices, again you can apply those sorts of filters to get different balances out. And the other one here is what we call the result basis, and if you're using NetSuite OneWorld, you've got some really neat ways of bending the calculations that we perform for you.

So you can do things like switch it from the actual consolidated rate, so the native NetSuite calculation, to things like the average rate, the current rate, the historic rate, or maybe you want to use the budget rate. So you want to compare, um, what do our actuals look like against the actual rate compared to our actuals run against the budget rate. So you can look at FX impact on your numbers. We can choose the account currency, for example. If you want to look at a bank balance, you can look at a bank code and say I want it in the account currency. So you'll see the bank balance rather than the reporting balance. And you can pull out things like the quantity.

Simplest thing to do there is, when we send out this recording, I've gone through a lot of information, watch the recording back, um, but more importantly, just have a play, have a play with the different formulas, and see which formulas will work for you. Again, I hope that answers your question, and again, that's a one-hour demo condensed down to about five minutes.

Evie: Thanks, Simon, that's great.

Simon: Before we go, should we keep going? Evie, I'm happy to just keep working through questions. So if we've reached the top of the hour, if anyone wants to dive out, um, you know, thanks for coming along, but we'll keep going with some questions and keep the session open for a bit longer.

Evie: Absolutely, that works. All right, so we had a few questions submitted prior to the session, okay, one of which is, how do we go about retrieving the live daily spot exchange rates from NetSuite to Solution 7?

Simon: Ah, what a good question. Don't get that one very often. Uh, it's a formula. So if you go into the function dialog and go down to currencies, we've got two currency formulas available. We've got the consolidated rate, and we've got the currency rate.

Now, what on earth is the difference between those two? The consolidated rate is what NetSuite uses within a OneWorld instance to do intercompany consolidation. So the consolidation rate is effectively the monthly rate from one company to another company. Let's have a look at that and see how that works. You can see we've got the from subsidiary, the to subsidiary, the period we're interested in, and what we call a rate type. So let's have a look at that. Let's go from UK to Inc, and let's have a look at that again for Jan 2024. And I think the rate type is optional, but let's see. Oh no, let's fill it in. Let's see, we've got available… oh yes, we can look at the average, the average budget, the current, the current budget, the historical, the historic budget. So let's have a look at the average, and given this is a bit unscripted, hopefully we'll get a number back and not a zero. There we go. And you can see that between Funny Came UK and Honeycomb Inc for January 2024, the average rate is 1.33. We're carrying almost… um, let's have a look at the daily rate.

So this is the currency rate. So this is used as the FX rate used against any documents that you raise. Now slightly different, this is the from currency, the to currency, and the effective date. Um, when you're looking at the FX screen in NetSuite, they refer to it as the source and the base, which we found a bit confusing. So we've tried to mold all that language together. Effectively, we're going from this currency to that currency at this point in time. So let's look up a currency. And again, this has been unscripted, so hopefully we'll get something. Let's go from GBP to USD. Uh, the effective date is optional, and it's going to default to today. So let's click OK, and there you go. You can see we get a number. Oh, interesting, it's the same number there. So this is effectively the spot rate. So this is the rate that's used for when you raise an invoice, and it puts the document currency in. And the consolidated rate is the OneWorld intercompany exchange rate. I hope that answers that question.

Evie: Fantastic. All right, so the question that was submitted prior to the webinar is, there a way that we can refresh just a certain cell rather than the whole sheet?

Simon: Oh yeah, good question. Super easy. That one, you just simply go into the sheet, choose the cells that you want to refresh, go to the refresh menu, and say refresh cells. And it will only refresh that portion of the workbook.

Now, one word of caution. When you open Excel for the first time, you can't guarantee that Excel will go, oh, you've refreshed those, therefore I'm going to take control and refresh the entire sheet for you. One factor, while Excel has been open for a little bit of time, it calms down. But when you open it for the first time, even though you're only refreshing one cell, sometimes Excel will go, actually, I'm going to refresh the entire book, the entire sheet for you. So simple to do, but sometimes Excel will override you.

Evie: Great. And wasn't sure if we went over this a little bit earlier, but we have had a question about showing consolidated balances in GBP if the parent entity is USD.

Simon: Yeah, absolutely, I'll show you that one again. Then, so let's go back into that book that I built here. So, uh, we're using the budget formula. Um, let's limit this to… now I've got to remember where I pumped some values into, um, and I don't understand why my pop-ups have stopped working. Um, so this is going to be HH. H, let's try Europe. No, that's all. Let me go into a new sheet. Okay, HH dots Europe, nope. Let me find a new sheet for you.

So we're going to go in, and we're going to enter formula, and we go budgets, and we're going to use A-Bud, and we're interested in the Europe budget. And you'll remember we uploaded that to demo, and I'll pull it in for all of the four codes from Jan 2024 through to December 2024. That should give us a figure. 200, there we go. Now that is going to be in this case 200 Euros, because we're looking at Europe. If I go back into that formula, scroll down, and touch the parent currency, and hit the lookup button, you can see that we can now choose any of our subsidiaries.

Now, for the original formulas, this is not for the advanced functions, but for the original formulas, you can go up the hierarchy. So if I choose Inc and click OK, you'll see the number changes, and it goes from 200 to 222, and that's because we've now applied the exchange rate between Europe and Inc. If Inc is Sterling and Europe is Euros, we're then seeing 222 pounds for 200 Euros.

Now, if we're using the advanced balance functions, slightly different approach. Let me code up the basics for you again. We should get our 200 out. Zero, while we're getting zero, Jan 24, Dec 24. Oh, because I'm looking at balances. Silly me. I want to look at Buds. Let me just correct that for you. Laughs. So it helps to use the right formula. So here we're looking at the demo budget consolidated. Let's change that now to be Europe. So we should get the 200 out of our 200.

Now, the way you do the different currencies, all the different companies, you do that for the advanced balance functions. You do that through an option, and the option is subsidiary context. So we've tried to marry up the language better with what you see within a NetSuite report. So here we're going to choose a subsidiary context, and for the value… and this is where Solution 7 is really neat with these new functions, I can actually convert from any company's currency to any other company's currency.

So whereas NetSuite will typically only roll up, and our original formulas, the… not the advanced, but the original balance and budget functions will only roll up, with the advanced we can go up, down, and across the hierarchy. So, for example, we're looking at Europe, and let's say, for example, we wanted it in the context of Mexico. I can click OK, and I click OK again, and we can now see that our 200 Euros is 4,280 Mexican peso.

Now, there is no direct relationship between our Euro company and our Mexico company, so what we've actually done is we've created a blended rate of Mexico up to the root and Europe up to the root. So we know what the peso is in Sterling, we know what the Euro is in Sterling, because we know both of those rates. We can create a blended rate to go from Euro over to Mexican peso. So super clever with our new advanced functions.

Evie: Thanks, Simon. Do we have time for another one?

Simon: I'll go and shoot. Let's do one more.

Evie: Okay, sounds good. Um, so I had a couple of questions about pivot tables. Yes, uh, one from Chris. He has asked, um, the… sorry, just trying to, uh, put this in a consolidated way. Um, he's got… seems like he's got multiple pivot tables in one sheet, and as he progresses through the year, uh, the pivot tables grow, and they extend to where the next pivot table is in the sheet, and it can crash the refresh. Um, is there a way that new lines can be automatically inserted into Excel to stop the pivot table extending to the one below?

Simon: Simple answer to that, sadly, is we're at the mercy of Excel. It's… it's just how Excel's pivot table engine works. Um, the simple way to do it… this is the problem with any sort of dynamic report where they grow and shrink. Put them side by side rather than one above the other might help, and put them on different sheets and maybe consolidate them onto a top cheek so they've got space to grow and shrink.

Um, we don't do anything. We just literally take data and push it into the pivot table engine, and we're then at the mercy of Excel's pivot table engine. So sadly, the answer to that is no. Um, you'll have the same problem if you use a tool like Power BI or Power Pivot. Um, we're just at the mercy of the way the engine works. But I'm a bit concerned by… by the… by when you say it crashes. So, um, we'll take that one away and actually test it to make sure that it doesn't go horribly wrong. You should just get a simple warning to say that one is interfering with the other. There's not a lot we can do about that, unfortunately.

Evie: Okay, um, and another question, um, on drill downs. Is there a way that we can add or edit the columns for a drill-down worksheet?

Simon: Yeah, great, great question. Um, so as everybody knows, you can drill down to the transaction detail. When you're in the transaction detail, you see what's on the line. Um, at the moment, come to us, raise a ticket with us, and we will add the extra columns that you need. Um, the ability for a user to choose those extra columns is currently in development.

So in the short term, just email support@solution7.co.uk. Let me just bring up the Q&A slide to see if… see if the email's on there. There we go. Yeah, there you go, support@solution7.co.uk. Drop us an email, and if you want the drill-down tweak, we can add those columns for you. But watch this space. It is currently in development.

Evie: All right, um, so that comes to the end of the Q&A. Uh, sorry if we didn't get to answer your question during the session, guys, and we'll help to answer these in a future session. If your question is urgent, please reach out to us at support@solution7.co.uk. You can see that on Simon's screen here. Just as a final reminder, the recording will be edited by our clever marketing people, and then once the recording is ready, we will send a link out to you. Thank you for joining today, and we look forward to seeing you at our next session.