Ask the Expert Webinar: NetSuite Financial Reporting in Excel with Solution 7 by Zone
TRANSCRIPT
Introduction and welcome
Hi, everyone. Welcome to our webinar. Thank you for joining us this morning. My name is Evie, and I'm the customer success manager here at Solution 7. We have disabled the chat for questions today, but if you want to enter a question, you can do so in our Q&A section at the bottom of your screen.
Without further ado, I'd like to introduce you to our founder and CTO, SimonMiles.
Overview of the webinar
Good morning, everyone. Welcome to today's session. As Evie said, this is a repeat of a session we've done a couple of times now, where we'll walk you through a specific use case of Solution 7. Today, we're focusing on how you can integrate a budgeting cycle with Microsoft Teams and Microsoft SharePoint.
To confirm, the session will involve working with Excel, SharePoint, and Microsoft Teams, and pulling all that together using Solution 7 to demonstrate an end-to-end budgeting cycle. The demo will take around 30 minutes, leaving time for a Q&A session at the end. Please feel free to throw any questions into the Q&A box, and we'll get to them at the end of the call.
Budgeting options in NetSuite
Let's look at our budgeting and planning options within NetSuite. You can work natively with the NetSuite budget input screen, where you can key data directly into NetSuite. Here's the budget input screen where you can select different criteria and enter budgets, allowing any user with a NetSuite login to enter budget information.
Many customers build budgets in spreadsheets and then upload them using the NetSuite CSV input. You build your workbook, shape it into the form NetSuite needs for its CSV input, and then upload it into NetSuite. There are also enterprise planning tools like NetSuite's own NSPB or Adaptive Planning, which are large, process-oriented tools that require a project to implement but are very scalable.
Solution 7 offers a great tool with features and functions to build a sophisticated budgeting and forecasting process, automating many steps that would otherwise be done manually. This allows you to avoid the expense and implementation cost of tools like NSPB or Adaptive Planning.
Solution 7 budgeting process
What does a budgeting process typically look like with Solution 7? We're essentially building templates, whether they are reports or budget templates. In the demo, we'll publish these templates via Microsoft Teams. Users can interact with the templates, and Solution 7 will map the data and push it back into NetSuite without needing to reformat the data. This creates a simple, controlled process with great-looking templates and seamless integration with Microsoft Excel.
Creating templates in Microsoft Teams
First, we create a channel in Microsoft Teams, providing a shared area in Teams or SharePoint for consolidating data. We build a master template using our list and functions capability, which will be the data entry form for users. We map the template back to NetSuite so that Solution 7 knows how to pick up the data and push it into NetSuite.
Using the OneDrive sync engine, we push the data from our machine to Teams and SharePoint. I'll show you the templates through Excel Online, allowing users to interact via Excel Online if they prefer. Once the process is complete, the OneDrive engine syncs the files back to our machine, and we use the Solution 7 budget upload routine to push the numbers into NetSuite.
Building and mapping templates
I'll demonstrate building a template. This template will be our data capture form, which we'll use to gather data from users and push it into NetSuite. I will also show another template to see the data once uploaded into NetSuite.We'll create a new workbook and build out the template, using functions like pop-ups for subsidiaries and budget categories, and inserting a list of accounts by number.
Automation and publishing of templates
We use Solution 7's automation feature to create sample templates. Automation is like mail merge in Microsoft Word, allowing you to publish a template to multiple users. We define dynamic cells in the workbook, drive automation using pop-up lists, and group workbooks for different regions.
After setting up the templates, we use the OneDrive sync engine to push the files to Teams and SharePoint. Users can then interact with the templates and enter data. We use the Solution 7 budget upload routine to push the entered data into NetSuite.
Uploading budgets to NetSuite
We'll open one of the workbooks in Excel to view the data entered by users. The mappings we defined earlier will apply to each workbook, so we don't need to remap them. We then upload the data to NetSuite using the Solution 7 budget upload routine.
Consolidating budgets in NetSuite
To view the uploaded data, we'll use the NetSuite budget input screen. We'll also use a pre-built report to see the numbers flow into the budget section and drill down to see the details.
Creating and using functions
We have various functions available in Solution 7 for retrieving account balances, budget values, and more. These functions allow for advanced reporting and analysis, including filtering by class, department, location, and using different currencies.
Demo session recap
To recap, we created a budget in Teams, built a master template, published the template using automation, synced the files with Teams, had users enter data, and uploaded the data to NetSuite. We then viewed the consolidated budget using a report in NetSuite.
Q&A
Q: How do the workbooks go back into being a consolidated sheet? Could you go over how we can consolidate our budgets again?
A: The consolidation side is just using regular Solution 7 formulas. Let me take the original template we used and hijack it to pull numbers back in. Remember, we uploaded the demo budget for 2024. Here you can see I've got consolidated selected. Let's use a formula to pull the consolidated budget through.
We're going to use our functions, and you'll see we've got a series of budget functions. The budget functions allow us to pull budget values from NetSuite. Let's use the ABud function to pull the consolidated balance back. You'll see the function has a series of parameters that we need to provide. The question was, can we look at it from a consolidated perspective? Yes, absolutely. Let's reference our consolidated cell for the budget category. We uploaded it to our demo budget category, and for the account, we'll use our 4,000 code. For the period, we'll reference January 2024.
Notice all my dollar signs are entered so I can copy and paste the formula around the workbook. Copy it across and down, and there you see our numbers pushed up into NetSuite are now returned using our budget functions. Don't forget, that you can drill down to see the underlying numbers through our drill feature. We created a budget in Teams, built a master template, published the template using the automation engine, and synced it with Teams. Users edited the documents, and the sync engine pushed the files down to our machine. We opened them in Excel, mapped the workbooks, and uploaded them to NetSuite.Finally, we used a report to view the consolidated budget. This method simplifies the budgeting process using simple Excel spreadsheets.
Q: How do we retrieve live daily spot exchange rates from NetSuite to Solution 7?
A: It's a formula. In the function dialogue under currencies, we have two currency formulas available: the consolidated rate and the currency rate. The consolidated rate is the monthly rate used for intercompany consolidation in NetSuite OneWorld, and the currency rate is the spot rate used for documents. Here's how they work.
The consolidated rate requires the from subsidiary, to subsidiary, period, and rate type. For example, going from the UK to Inc. for January 2024 using the average rate will return the exchange rate. The currency rate formula requires the from currency, to currency, and effective date. For example, from GBP to USD on today's date.
Q: Is it possible to refresh a specific cell rather than the whole sheet?
A: Yes, it's simple. Select the cells you want to refresh, go to the refreshment, and select "refresh cells." However, sometimes Excel may refresh the entire sheet.
Q: When I have multiple pivot tables in one sheet extending into each other as they grow, can new lines be automatically inserted into Excel to prevent this?
A: Unfortunately, we're at the mercy of Excel's pivot table engine. One solution is to place pivot tables side by side rather than one above the other on different sheets. This way, they have space to grow and shrink without interfering with each other.
Q: Is there a way to add or edit columns for a drill-down worksheet?
A: Yes, currently, users can't choose additional columns, but it's in development. In the short term, email support at support@solution7.co.uk, and we can add the extra columns for you.
Conclusion and final remarks
Thank you for joining us today. We hope to see you at our next session. If your question wasn't answered, please reach out to us at support@solution7.co.uk.
