One of the most common challenges and hurdles to overcome when taking on any new software, is how to cleanse data from your old system. Don’t let messy data stop you from moving to a more powerful software platform to run your business. This series of blogs will present 5 of the most used groups of Microsoft Excel features for cleansing your data that are easy to use and will save you time.
Part 5 - Data manipulation using =SWITCH
If you have read Parts 1, 2, 3 and 4 of this blog series, hopefully you have an insight into how Microsoft Excel (“Excel”) is an ideal tool for cleaning data. If you are looking at moving to a single business software platform and are currently using accounting software such as MYOB, QuickBooks, Saasu and Xero – all of these allow you to export master data to Excel directly, or at least to a text (.txt) or comma separated values (.csv) which can in turn be opened in Excel.
In this final blog, we want to explore how we can not only tidy up messy data, but also enrich the data before we import it into our new ERP software. Examples of this could be where we wish to add additional data attributes that do not exist in the legacy information. It could also be taking the opportunity to use aggregated financial data such as average debtor days to determine which credit terms should be applied to customers in the new software. Another example would be updating bin location names to reflect a new warehouse layout.
Let’s take the example that we have used Excel to work out the average debtor days (the length of time a customer takes to pay an invoice) by taking the payment date and subtracting the invoice due date from the legacy system’s transaction ledger exported to a csv file. We now want to use that information to populate debtor terms in our new software of “COD’ or ‘30 days’ based on their historic debtor days. All these outcomes can be facilitated by using the = SWITCH formula.
If we imagine a spreadsheet where the Debtor code appeared in cell A1, and the historic Debtor days in B1, we can create a simple grading in cell C1. If we used a guide of less than 7 days being given an A-ranking (30-day terms) we would create the formula:
=IF (A1<7,”7”, “COD”)
This will place the number 7 in cell C1 if our rule is met.
We can then use the formula =SWITCH to populate the desired debtor terms outcome in cell D1. =SWITCH(VALUE(C1),7,"30 DAYS","COD ")
This formula basically looks at the value in Cell C1 and if it is 7 will return 30DAYS into cell D1, if not it will return COD.
Now let’s turn to the possibility of enriching the data. Let us assume we want to add a contact preference attribute to our Debtor (phone, text or email) based on whether they are local, interstate or overseas.
The key is to find an existing piece of data that identifies the attribute we wish to use with SWITCH. In this case it could be as simple as the postcode. If the suburb we were defining as local was Sydney (postcode 2000), then the formula would read
=SWITCH(VALUE(C1),2000,"Phone/Text", ”Email")
In other words, if the postcode is not 2000 (Sydney) then the preferred method of communication should be via email.
With these tools it becomes reasonably easy to clean up data. That means if you wish to move to a more powerful, whole of business software such as SAP Business One, you really don’t have to re-key your data.
An SAP Business One Cloud Starter pack license is available for as little as $140ex GST per user per month. It comes with the ability to import Customers, Suppliers, Items and Price Lists straight from a Microsoft Excel spreadsheet. Sales pipeline, CRM, Cash flow management and Budgeting – these are just some of the areas that a move to using SAP Business One could help with improving how you manage your business.
With what you now know, you could be up and running in a software that not only manages what has happened in your business but gives you insight into what is about to happen in your business - in less time and effort than you might think.
