Should You Replace or Integrate Your Spreadsheets? - Replay

    

Should You Replace or Integrate Your Spreadsheets_Many companies struggle with the question: should you replace or integrate your spreadsheets? In this blog, we will recap the discussion from our recent webinar, and share with you our knowledge on the topic. There are many areas internally that you're likely tracking in a spreadsheet or even working with a customer or vendor where you have spreadsheets that could possibly be integrated. We will discuss the surface level of integrating and replacing your spreadsheets and get you thinking about what you could have in your ERP Solution rather than an outside of the system; for example an AP Invoice integrated to save time and manual data entry.

Common Spreadsheets replaced with an ERP Module

Inventory Listing

Often companies implement the Financial Modules (GL, AR and AP) of an ERP solution but let inventory live outside of the system, with tracking displayed on a spreadsheet. This regularly includes the tracking of inventory at a warehouse level, giving insight into where products are located within the warehouse. Below is an example of inventory listing in a spreadsheet, however we will further explain how the common spreadsheet of inventory listings can be migrated into your ERP solution to take away potential for human error and limit manual entry.

Inventory Listing in ERP

Inventory listing in ERP can be very detailed and easily looked at. When your inventory control is held outside of an ERP solution in a spreadsheet there is a larger room for error, and it involves much more work. For example, let's say that you're in the service industry and you are going out to a job, possibly using the financial modules to bill your customer for a fixed fee servicing that equipment. In the back end you have an Excel spreadsheet that lists all of the different inventory items that are required on that service job and those inventory items are being held outside of the system. The entire process would be much more simple if it was easily accessible within one solution. 

Here is an idea of all the different areas within inventory control that can set up based on items within ERP rather than a spreadsheet. Throughout this blog we will be showcasing Sage 300 and Microsoft Dynamics 365 Business Central.


Bill of Material (BOM)

Another area that we see often outside of the solution alongside inventory listing is Bill of Material (BOM). If you are not utilizing the bank module within an ERP solution, it is common to have other operational modules left out. Below is an example of a bill of materials spreadsheet, which involves a large amount of manual process.

Bill of Materials in ERP

There are various ways ERP works with BOM depending on the solution you are using. Some solutions, like Sage 300 have a tree of components that are applicable to make a bill of material, where the information is being pulled from a master item. The master item is created and the components will have already been set up in the database; with the item record, pricing, units of measure etc. This allows you to simply add the master item and collect the components needed, rather than dealing with a spreadsheet which involves copying and pasting - hoping you don't forget material along the way; there is a lot of risk involved with keeping BOM within a spreadsheet.

 

Bank Reconciliation

Bank Reconciliation is another statement that often lives outside of an ERP solution and within a spreadsheet. Even if you use the financial modules there are often banks that are not included. Spreadsheets also lack insights into various aspects of a transaction.

Example of a Bank Reconciliation Spreadsheet.

Bank Reconciliations in ERP 

Spreadsheets do not show all of the transactions that run through your ERP and do not include trails such as payments and receipts from your customers.  This leads you again to track that all manually instead let the solution do it for you. You've already done 90% of the work in terms of doing those withdrawals and deposits, those checks and those receipts, so take advantage of the bank reconciliation in the ERP. Once you complete one it becomes very simple task that can be completed on a monthly basis. Within an ERP solution it also ensures that everything is balanced, and if it does not balance you can't just forge a change as you could when it comes to an Excel spreadsheet.


These solutions both handle a bank reconciliation and achieve the exact same thing; just with a different approach. The idea here is that most ERPs have a bank reconciliation. We highly suggest removing it out of an Excel spreadsheet and getting it into the ERP, allowing the ERP do the majority of your work versus having to have a resource take care of that on a weekly or monthly basis.

Common Imports 

  • Payroll Imports
  • Accounts Payable Invoice
  • Accountants Entries
  • Sales Orders

How we would complete these common imports out of the box. 

 

Business Intelligence and Power BI

Business Intelligence and Power Bi give visuals in the different areas of your business and it can be leveraged for things like Financial Statements, Sales Dashboard, Financial Trend Analysis and more. Data appears in the form of graphics and charts versus just seeing numbers; making it easier to understand and more flexible.  If you're using Sage 300 currently, you already have access to business intelligence, however it likely needs to be connected.

 

 

In Summary

There are various tasks being completed within Excel that could be brought into a ERP Solution helping you to utilize time, resources, and productivity. Excel spreadsheets are a great tool, and we do not suggest getting rid of them altogether, however, repeatable processes are much more efficient and accurate when in an ERP Solution. There is also a number of opportunities to gain further insight into processes, transactions, customer value/retention and more. 

If you want to learn more about ERP contact us today! 

View the recording: Should You Replace or Integrate Your Spreadsheets?

Read More: How Automating Spreadsheets can Reduce Human Error

Jennifer Holman

About The Author

Jennifer Holman

Jennifer brings areas of expertise within the ERP space. Certified in Microsoft and Sage Business Solutions she is involved in Implementations big and small. With a previous role as an ERP consultant, she has experience working with clients to overcome pain points and problems. Jennifer now spends her time as a pre-sales presenter and a Project Manager for Microsoft Implementations.