A data conversion is the process of taking data stored in one database and moving it to another. Each database's structure is different, making a conversion a lot like a snowflake—no two are ever alike. One thing is sure—data conversions can be daunting.
We recently sat down with Steve Cohen, Director of Sage Expert Services (SES), to get some time-saving tips on successfully converting data. Steve and his team are experts (as the name implies) at application implementation and management—providing several white glove services to Sage customers. Steve and his team are available if your business would benefit from these services. If you prefer a DIY approach to data conversion, Steve shares some great tips below to make the most of your time and resources when converting data from an existing fixed asset solution to Sage Fixed Assets (SFA).
Steve, in your experience, what’s the top reason people get overwhelmed and risk having a poor experience with data conversion?
Hands down, it’s a lack of planning. If you haven’t taken time to think through the steps, you can run into challenges early in the process that can be very overwhelming. Rushing to get your data exported, converted, and imported can cost more time and resources than if you had planned it out.
What tips can you share to make the process as smooth as possible?
Remember that a successful data conversion requires some upfront planning on your part. Companies often reach out to my team after they’ve rushed the process—exporting their data to Excel and jumping right into the import. Without proper planning, this can result in hundreds or even thousands of error messages (there can be multiple errors for each asset) because they’re pulling in lots of data without sufficient forethought. Correcting all the errors before the import can be overwhelming—adding extra time and stress to the process. Don’t let this be you! Planning your strategy out doesn’t mean you won’t get any error messages or run into any problems—but Steve will show you how to avoid many of them and resolve the rest quickly.
Tip 1—Take the time to plan
First, you need to understand what SFA needs from your current fixed assets application. The SFA User Guide has two sections in the Appendix (C and D) that will guide you. Appendix C (Custom Import Helper) includes a list of all importable fields available in SFA and Appendix D lists the field specifications. You’ll know exactly which fields to export from your existing fixed asset application and what format each field needs.
Tip 2—Ask yourself some key questions
Once export fields are identified, it’s time to prepare your import. Start by asking yourself three questions:
- Do you want to convert all assets?
- How about only converting active assets (assets that are not disposed of)?
- What about assets transferred between locations? For instance, if an asset was transferred from location “A” to location “B”, should you include it as two assets in your data file to convert?
Active assets are those that are not disposed of and can be fully depreciated. It’s still an asset you own, even if fully depreciated. Steve typically recommends converting all active assets plus current-year disposals (you’ll want to include these to ensure your books are balanced and included in any physical asset inventories). After all, do you really need to convert assets that were disposed of years ago? Why take up space in your database for assets you no longer have?
When transferring assets between locations, it’s important to understand that SFA doesn’t import historical transactions. The asset that would be included in the data file and imported into SFA is the current version of the asset. In the example above, it would be the asset assigned to location “B”. Once the asset is in SFA and you transfer it to another location, SFA will track history and transfer activity from that point forward.
Finally, it’s time to begin your export! Steve advises first exporting your data into an Excel file where it’s simple to modify and format your data using the field specifications in the SFA User Guide.
Tip 3—Avoid future data issues
Create clear field names in your import file to avoid significant issues later
Any misunderstanding about what the data in a particular field represents will cause significant problems down the road. Steve says to trust him on this—and we do!
Make sure the first row in your spreadsheet contains the column name. Sometimes, column names are automatically added from the export process as the actual name of the field in the database. Change the column name to something easy to recognize and understand. Make it clear. (i.e., “YTD Accumulated Depreciation,” “Acquisition value,” etc.). Go through each field in your import file to ensure it is formatted to the correct specification detailed in the SFA User Guide.
Get ready to import!
For brevity’s sake, Steve didn’t review all the fields available to import into SFA. Instead, he highlights the SFA-required fields. These are the fields required to calculate depreciation—you couldn’t even calculate depreciation manually without it (please see the Field Specification section, Appendix C in the SFA User Guide for more details).
Property Type.
This represents the property types as defined by the IRS. Cut and paste or search and replace in Excel to change your property type codes to the applicable Sage property type code as shown in the field specification list in the user guide.
Some fixed asset applications don’t have a property type field. In this case, you should be able to easily identify the correct property type based on another field—GL account number, asset class code, etc. Add the SFA property type code based on whatever criteria is available.
Placed-in-service date.
This is the date on which the asset was placed in service—the date that depreciation starts. Not necessarily the assets acquisition date (there’s a separate user field for that).
Acquisition value.
This is the cost of the asset.
Depreciation Method.
Using cut and paste or search and replace in Excel, change your depreciation method to the applicable Sage depreciation method Type Code as shown in the field specification list in the user guide.
Estimated Life.
Reformat your estimated life field to the format required by SFA (YYMM).
SFA allows you to track asset depreciation across multiple books. (Tax, Internal, State, etc.) If you are using multiple books in SFA, you’ll want separate fields (described above) for each book—like Tax Depreciation Method, Internal Deprecation Method, etc.) If one field applies to all books, you only need that one field in your import file. (For instance, if the placed-in-service date field applies to all books, you only need to have that field once in the import file).
Once the import file is ready, follow the SFA Import Helper steps to map the fields in your import file to the related field in SFA.
Before clicking the “Import” button, validate the data across the SFA rule base. Use the button in the Import Helper to validate the data. A report is generated showing errors and warnings. Errors must be fixed, or the asset will not import. A warning alerts you of anything unusual, but the asset can still be imported.
Tip 4—Breathe, and use this time-saving advice
There can be multiple errors per asset, per book. As I mentioned, this could result in hundreds or thousands of error messages. Don’t panic. You’re not going to have to correct each error individually. Scroll through the error list and look for patterns. For example, you may see an error message on hundreds of assets that states the depreciation method is invalid. There is no need to locate each asset and fix the depreciation method. Go to the Excel spreadsheet, filter all records with that invalid method, and make the correction. This will fix all those errors in one shot. Continue looking at the error message patterns and make your fixes by filtering your spreadsheet and making mass changes.
When completed, validate the data again from within the Import Helper. The error list will be considerably shorter each time you make a fix until there are no remaining errors. Following the steps in the previous paragraph, look for patterns and correct those errors in the import file.
Validate the import file again. You can click the import button and bring the data into SFA if there are no errors.
I like to check my import file for the most common errors before validating it for the first time. This ensures a minimal number of errors when running the first validation.
Here are the most common errors I check for before validating my data the first time:
- Life-to-date accumulated depreciation cannot be more than the depreciable basis.
- Year-to-date depreciation cannot be more than the life-to-date depreciation.
- Make sure the amount of any 168K bonus depreciation is not included in the accumulated depreciation amount (SFA tracks that separately).
Steve, any final tips for anyone converting their existing fixed assets data to Sage Fixed Assets?
I could continue providing data conversion advice for another 20 pages, but I hope our readers get the point with the tips I’ve shared.
To sum it up:
- Take the time upfront to do some planning.
- Review the importable field list and the field specifications first.
- Make the necessary changes to the data and look for common errors before importing.
Spending the time upfront will ensure the job is done right the first time. Following these steps will save you considerable time in the long run.
How about one final tip?
Tip 5—You don’t have to go it alone
The data conversion process can be easy or challenging depending on the format exported from the existing system and the number of companies, books, and assets. If you need help due to time, resources, or lack of expertise, the Sage Customer Service team can assist with any questions and point you in the right direction. Alternatively, Steve and his team of experts can manage your data conversion. Please reach out to your Sage Fixed Assets Account Manager at [phone] for an SES project price quote.
Download the Sage Expert Services Datasheet to learn more.