NetSuite Reporting in Excel with Solution 7

Hello, my name is Simon Miles, and I'm the CTO of solution 7. solution 7 fundamentally is about creating great-looking Financial reports. what do I mean by that? well, we're talking about the types of reports that you need to build every day, every week, every month, maybe as part of your month-end process, maybe as part of your year-end process. it's the reports how you want to see them. so it's your look, it's your feel, it's your colors, logos, and so on. it's the numbers, how you want to see them, it's how your stakeholders want to see them. in this example here, we're showing a report as a simple variance report. so we've got actual versus budget for the period. alongside that, we're showing year-to-date actual versus budget again, and we're doing comparisons with things like the full year and last year's data. we can also do reports like rolling forecast reports, because again, we can pull data from any part of the GL and present it how you want to see it. so in this case, we're showing actuals and budgets. so rather than having to, um, rerun a forecast process partway through the year, we can simply load in actuals to date and budgets into the future to give us a very, very quick and easy, um, rolling forecast position.

what types of reports are we talking about? well, typically they're your month-end reports, so Consolidated pnls, balance sheet, cash flow, board reports. maybe it's for statutory or regulatory reporting, so your tenq, your 10K, 1099s, trustee reporting. maybe it's as part of building your annual report, so where you're needing to pull that data from the GL and present it again. it's very simple using solution 7 for variance reporting, like actual versus budget, budget over forecast. maybe you need to present forecast over revised forecast, month-on-month, quarter-on-quarter, or even year-on-year reporting. it's very simple. moving away from the GL, maybe you're looking at accounts receivable or accounts payable. again, very, very simple using, um, using solution 7 multi-dimensional analysis, so by class, by Department, by location, or possibly you're using a custom segment. non-posting documents are just as simple as reporting from the GL. so if you're looking at sales orders or purchase orders, again, they're very easy to pull onto a report. and, um, moving into more advanced types of reporting, like one world or multi-book, or maybe you're a partnership business where you need to do part-ownership analysis, again, that's very, very, very simple because we're just simply building reports using Excel in a form that you want to see them.

we can also do easy financial planning. what do I mean by that? well, we're not trying to recreate the wheel, we're not trying to be a pbcs or an Adaptive planning, we're just simply allowing you to automatically publish spreadsheets out to The Wider business. so rather than having to build and manage lots of templates, you just create one master template, and solution 7 will publish those out to The Wider business for you. then, a simple case of gathering those back in and uploading them into NetSuite. so you simply build your templates in Excel, and we will publish them up into NetSuite for you without the need to export them into a CSV file.

let's have a look at solution 7 in action. I'm going to build a simple report for you. as I say, I'm not trying to show you how advanced solution 7 is at this stage, I'm just going to build a very, very simple report to show you how quick and easy it is to get up and run with the software and to build out your first report. you can see in this sample we've got our chart of accounts down the left-hand side, our financial periods across the top, and then we've got company information and the year in the top-left-hand corner, and that will become useful later on, as we can then switch between company and switch between Financial year.

let's start with a blank canvas. I'm going to introduce you to two concepts within solution 7 that you need to learn to build out a financial report. the first of those is something that we call lists. we've got three different types of lists: we've got column lists, row lists, and popup lists. you can see here I'm going to insert a popup of subsidiaries. to do that, I simply choose the item from the menu, and you can see we get presented with something called the list argument screen. now, for anyone that knows Excel, you'll recognize this screen, and this is a screen that's actually simply based on the built-in Excel function arguments dialogue. again, the important thing here is we're not trying to teach you anything new, we're just simply trying to leverage your Excel skills so that you can use those to build your financial reports.

in this case, I just need to click the okay button, and that will insert a popup of subsidiaries. and here's the popup in action. I just choose the button, and there's the popup screen, and I can choose any one of my subsidiaries out of NetSuite. we've also got something called a column list. now here, I'm going to insert a list of accounts by number. I'm also going to pause at this stage, because you'll notice as we review down the list of, um, available types that we can insert from NetSuite, you'll recognize it's all just using NetSuite's own language, so things like account types, accounts by name, budget categories, classes, customers, and so on. these are just Concepts that exist within NetSuite.

I'm going to insert a list of accounts by number, and again, we recognize that list argument screen. this time, I'm going to populate it, and to choose the subsidiary, you'll see here I'm going to use this lookup button, and for there, I'm going to simply choose our consolidation subsidiary. for the account, I'm going to type in four, followed by an asterisk, and what that's going to ask solution 7 to do is to grab from NetSuite all of the accounts that start with the digit four. I simply click on okay, and again, those get returned into our Excel template.

now I'm going to run this report for 2017, and to build out my columns, I need to use three Excel formulas. I'm not going to use solution 7 functionality to do this, I'm simply going to use standard Excel. and again, this also demonstrates how easy it is to leverage your existing knowledge of Excel, and how you could combine that with solution 7 functionality to build out the reports that you want to see. I'm going to start by using Excel date function. what that will do, by referencing cell C4, which is our date, you'll see it gives us January of 2017. I can then use Excel's text function to format it, and finally, I'm use Excel's date function, and that allows us to do some basic data arithmetic. it simply adds one month to our January heading to give us February, and you'll notice with a little bit of copy and paste, I can just drag all our formulas across the column. and there's our January to December column headings.

next, I'm going to insert some values into the report, and I'm going to pull these directly from NetSuite's GL. now, to do that, I need to introduce you to another concept, and these are what we call our functions. these functions, imagine they're a bit like the SUMIF function or the VLOOKUP function, but rather than aggregating data that already exists within Excel, we're simply pulling data from NetSuite. let's have a look at this function here that we call the NSGL Bal function. it starts with NS, because it's a NetSuite function, GL because we're interrogating the GL, Bal, well, we're simply going to return a general ledger account balance by an account number, hence the name NSGL Bal. I need to populate the subsidiary, so let's populate the subsidiary by simply selecting cell C3. that's going to be our Consolidated subsidiary. next, we need to pull through the account number, and again, I'm going to pull that from the sheet by selecting cell B6, or R4000 code. and finally, I'm going to select January 2017 as our period, and that's going to come from cell D4. you'll also notice all the dollar references, and what that will allow us to do is simply copy and paste the formula around the workbook, so that Excel is going to do all the heavy lifting, to, um, to allow us to populate each of the columns rather than us having to rekey the formula one at a time. so I simply click on okay.

firstly, we get presented with an error because at this point solution 7 goes off to NetSuite to grab the balance. as soon as that balance becomes available, it just drops in as the result of the formula. and you can see here that for January 2017, our sales balance is a credit of $1.8 million US. it's a credit because it's income. so for the purpose of presenting in the report, we need to reverse the sign, and to do that, you can see here I just pop a minus sign into the formula. that will then convert that into a positive number, and it's that positive formula that we can now copy and paste across the workbook, and again, allow Excel to simply recalculate what those numbers look like.

in terms of finishing the report, I can pop in a sales total, and again, I just key the amount in. I can type in a total over here for each of the, um, columns. I can simply use Excel SUM function to add down the sheet, and I can use Excel SUM function to add across the sheet. and then finally, I'm going to apply some basic formatting. so let's apply some lines, um, some underlines to the, um, top and total row. again, for the total row on the right-hand side, I like to hide the first row of, um, date formulas because I think it just makes the report look a bit tidier. I also like to turn the grid lines off, and I'm also going to throw on a couple of graphs and charts.

and again, if you want to show your data graphically, you can very, very easily integrate NetSuite's data with Excel's graphing and charting capabilities because we're simply pulling data from NetSuite into the workbook, and then it's just down to your knowledge of Excel and how you build out those graphical representations of your data. so it's very easy to go from just a basic report to a report that looks much, much more like a dashboard by applying Excel's own graphing and charting capability.

if I then want to change the numbers in the report, again, that's very simple too. so, for example, if I want to switch companies, maybe I want to switch from my Consolidated entity to look at my Mexican entity, I can just simply use popup, and by changing the value in that cell, Excel will simply recalculate the values that do in front of you. and obviously, solution 7 then goes and grabs all of the numbers from Mexico, and again, you'll see the numbers just update in front of you. the same is true for the date, because all of these formulas go through these, um, month headings, and all these month headings are driven from the date here. I can simply change the date, let's roll the date backwards by a year, and again, you can see the numbers simply update in front of you.

now, when it comes to month-end, part of the challenge is understanding the numbers that are being presented. often, you'll get asked questions by the business, or maybe you'll question your own reports. uh, you'll be questioning your own reports, why does this number say what it's saying? to help with that, we've got a full drill down and drill back capability. what that allows us to do is to take any number in our report and actually drill into the underlying detail. I can click on any cell, I can right-click on that cell, and you'll see we have this drill down option at the bottom of the right-click menu. from there, I can choose to drill down directly to the underlying transaction detail, or I can actually look at the data almost like different layers of an onion. so I can drill into the numbers and effectively peel those layers back as I'm going. I can drill down by subsidiary, by account, account class, department, or location.

let's drill down initially by subsidiary. so we've got a $2.5 million balance here. let's break that out into the individual legal entities that are contributing towards that income figure, and as you can see here, we've created a new sheet on the workbook, and that's now broken down. that $2.5 million figure is now broken down by our Europe, Mexico, and our USA subsidiary. we can drill into those numbers as well. as I say, it's a bit like peeling back layers of an onion. so let's right-click on this 1.4 million USA dollar figure, and let's actually drill into that and look at it by location. so again, I can open that 1.4 million number out, and we can see that's broken down by a number of our San Francisco locations and our Boston location. let's drill even further. let's have a look at the San Francisco location. this time, let's drill into it by class, and then finally, I'm going to drill into our accessories number, and I'm going to drill all the way down to the underlying transaction detail.

you'll notice, for each drill down, we've simply got a brand new sheet on the workbook, and now that we're down at the transaction detail, we can see all the individual postings, the debits, the credits. these could be your sales invoices, your purchase invoices, or just a list of journals. but you'll notice with the one that I've highlighted there, we can also have a set of document references that we can use to drill back into NetSuite. so if you've got one of the line items that are being questioned, you can very easily go from your drill down in Excel, you can simply drill back into NetSuite. you can log in, and that will take you straight back to the source document. when you then scroll down, you can look, you can find the offending line item in that document, and then you've got a number of choices: how do I put the correction through? do I maybe issue a credit note against an invoice? maybe I need to post a correcting journal? or maybe I can simply edit the document that's in front of you. the important thing here is any correction that you do need to make in NetSuite will be instantly reflected in your financial reports built with solution 7. to update them, you simply go to the refresh menu, you tell solution 7 to refresh the sheet or the workbook, and again, the numbers simply refresh in front of you. so you're not having to wait for any interfaces to run, you're not having to wait for a cube to be updated. the data is immediately available just by hitting the refresh button.

so we've looked at financial reporting. let's now look at financial planning. this is a diagram I showed you earlier. again, this is a very, very simple financial planning process. it's effectively a three-step process. what we're looking to do is to build a template that's going to extract data from NetSuite. we've got a publishing process that allows us to take that template and publish it around the business, and then it's just a, um, a collation exercise of pulling that data back together and uploading it back into NetSuite.

so what does part one of that process look like? let's look at the data collection side of it. you simply build a template in Excel. again, these are your workbooks. you build them out how you want to see them. in this case, you'll see here we've built a column of actuals that are being pulled through into column E, and then G through to R, or our January through to December headings, and we're simply using Excel formulas to spread the income and to apply a simple uplift.

now, it's important to note at this stage that these templates can be as simple or as complicated as you want to make them. if you want to pull KPIs through, maybe for example you want to model based on headcount. headcount could be held in NetSuite as a statistical account. you can very easily pull those numbers through or just get the user to enter those numbers, and again, using simple formulas, we can model that, um, budget or that forecast within, within Excel. you'll notice for my example here that my San Francisco, um, my San Francisco element of the template is a location that I'm using to allow me to run my template by the different locations within the USA. so essentially, what I'm doing is I'm running a budgeting process by the different locations. I'm also going to model those locations on what we call an automation sheet. now, the point of the automation sheet is it allows us to determine how we want to publish our worksheets. so you'll notice that we've got each of our different locations, um, each of our different locations set here, and as the software, as solution 7, processes the automation sheet, it will work its way down each of the different locations, essentially like a mail merge process in Microsoft Word, and it will bag up each of the different worksheets into different workbooks.

so you can see here, our San Francisco sheets are going to go into a workbook called San Francisco. our Boston sheets are going to go into a workbook called Boston. they're all going to get emailed to me. and then we've also created some tokens, and I'll come on to tokens in a second. now, when it comes to actually pushing the sheets out the door, you simply click on the email option up here. that then allows you to prepare a worded email. so it's really nice that you can actually say, hey guys, the, um, templates are ready for whichever year you're working on. um, please review them, please update them, and please send them back. and you'll see that curly bracket notation that we've got there. that's one of the tokens that we're using to personalize the email that we're sending out.

so you can see we've got a token here, um, to personalize the email. we're also using a token here to personalize the names of the output sheets that we're generating. so again, we use that token concept to allow us to create formulas to personalize elements of this process that doesn't actually allow us to use embedded native formulas, um, in the actual place we want to use them. so I simply click on okay. the software will then go through and refresh the workbook and email the workbook based on each of the entries defined in the automation tab. so here it's recalculating for San Francisco. here it's recalculating for Boston. and then finally, there's our output email. so the software has now sent an email out to me because I'm defined as the budget holder. if we now switch roles, you'll see that the email comes into my inbox. so it's nothing more than a straightforward email. you can see there we've got two attachments to the email. we've got our Boston workbook. we've got our San Francisco workbook. let's open up the Boston workbook and have a look at it. you can see here there are two sheets in the Boston workbook, our Boston sheet and our B- sheet. so the thing there is that we've only had to maintain one master template, and the software has generated these two additional templates from that master template.

let's also have a look at San Francisco. again, we've created four templates here from that master template. so again, we're not having to manually create and manually maintain lots of worksheets. we're letting solution 7 do all the heavy lifting. in terms of, we maintain the master templates, and we let the software generate all those copies of the master templates, so we don't have to worry about them.

now, I said it was a three-part process. let's have a look at part two of the process, which is what we call our spreadsheet mapping process. what we're able to do here is we're able to take a workbook. in this case, we're taking the templates that we've already generated, and we're going to map those onto NetSuite. so rather than having to physically convert the data into an import shape, we're simply going to tell solution 7 the shape of the workbook that we've created. you can see here, cell G4, that contains our subsidiary. our periods are in cells G7 to R7. the accounts, that's our 4000 to 4100 codes. in sales, B9 to B14. here's our budget category. and again, it's important to note that if you're running NetSuite Advanced Financials, you can update as many budget categories as you choose. so if you want to contain different versions of the budget or different versions of the forecast, it's very, very easy to do. and then finally, here we've got our location mapped. so in this case, we're going to be budgeting by location.

so the final part of the process is how do we actually get those numbers back and into NetSuite? well, again, it's a very simple process. having mapped the workbook, it's nothing more complicated than choosing this upload option in Excel. we simply select each of the sheets that we want to upload, we hit the upload button. first thing that happens is we connect to NetSuite. we grab all the relevant data out of NetSuite, and we validate the workbook just to make sure that it's a sensible layout that can be imported. and then we process the template and push it up into NetSuite. finally, we create an upload summary so you can just confirm that all the data was uploaded successfully. maybe you need to keep that and show it to a manager or show it to an auditor. and here you can see we've uploaded the data directly into NetSuite's budget module. so again, this, um, data is not going to live outside of NetSuite. it's only outside of NetSuite whilst it's in your spreadsheets. once you've uploaded it through the budget upload process, it's now sitting directly within NetSuite. the benefit of that being, you can then use our financial reporting capability to very easily combine those budget numbers with your actuals to create those great-looking financial reports that I showed you at the beginning of this session.

so in summary, what is solution 7? well, it's about great-looking financial reports. it's about that reporting that you need to do at month end, your P&Ls, your balance sheets, and so on. it's all about dynamic calculations within Excel, directly from your live NetSuite data, with drill down, with drill back capabilities, with graphing and charting integration, and the ability to publish to Excel spreadsheets or PDFs via that automation process. from a financial planning capability, it's no more than creating scalable planning processes around simple Excel templates to create dynamic budgets and forecasts from historic data, allowing, um, and that data can be both NetSuite data and non-NetSuite data, you to allow you to build out, model, and WHIFs using your own knowledge of Excel to automatically publish workbooks out to the business, and then gather those, um, workbooks back in and publish them up into NetSuite so that you can consolidate them directly within NetSuite without having to string lots of spreadsheets together to check the amounts before you upload them into NetSuite.

if you'd like to find out more, solution 7 is available on SuiteApp.com. just simply search for solution 7. alternatively, you can email me. my email address is simon.miles@solution7.co.uk, or you can find us on the web at www.solution7.co.uk