Written by Cody Dougherty, Tax Supervisor, Global Tax Management, and Sage Fixed Assets Expert

Alteryx is the talk of the town across all companies when it comes to repetitive, time-consuming data manipulation processes. Alteryx Designer is a drag-and-drop programming tool that allows you to take a data set and completely transform it into a consumable format. It is a program that lends itself to the monthly/quarterly/annually recurring time-consuming work of updating Sage Fixed Assets for current period activity.

Considerations before beginning your Alteryx Workflow

The first step is to consider the source of your data. Will your data come from an ERP system, other fixed asset software, or an Excel spreadsheet? This is important so you can understand how to set up your workflow. In a best-case scenario, you will want a data source free of any human manipulation. These needed manipulations can always be built into a workflow.

Taking the data one step further, you will need to understand what the source data represents. Is the data a transactional-based extract, or is the data showing a set of transactions for a specific period? In a transactional-based extract, the data will represent the fixed assets at a particular point in time, generally in a parent/child asset format. This means there will be a parent asset that contains all the asset-specific details (e.g., location, company, department). Then child assets will represent the transactions for a particular period. There may be codes provided for each transaction that depict what a specific line item represents. These are extremely common in an SAP environment. More often, you will have data representing the fixed asset transactions for a period. For example, you may be provided data for current period additions, transfers, and retirements. In both formats, Alteryx has tools that can significantly reduce the time needed to manipulate, import, and reconcile the fixed asset transactions.   

One of the most critical aspects of building an Alteryx workflow is understanding the common fixed-asset transactions across your company. From an addition perspective, you may have what is commonly referred to as ‘trailing costs,’ which are essentially cost adjustments to prior period assets. These can be handled in a few different ways: adjusting prior period balances or placing these cost adjustments in the current period activity. Another common transaction would be the splitting of prior-period assets. Assets are often placed into service when the full details of the asset are not currently known. Once the details are known, which could occur in future periods, the assets could be split into multiple assets. This is important to know because there may be a need to adjust your current system assets to reflect the bifurcation of the original asset. 

These considerations are only a few of the many possible situations where Alteryx may be able to greatly reduce the amount of time you spend manually manipulating your fixed asset records. After understanding what is unique to your company, you can move on to building your Alteryx workflow.

Building your Alteryx Workflow

The beautiful part about Alteryx is that there are thousands of ways to do the same thing, which is all driven by the user’s thought process. Due to this, I will explain the general aspects of the three key areas of a workflow: data inputs, manipulations, and data outputs.

The data inputs are the starting point of an Alteryx workflow. In an ideal scenario, the first input should be a fixed asset data file free of any prior manipulations. This will ensure that the data stays consistent on a period-by-period basis. When configuring your data set, be sure to check your data for the Sage critical depreciation elements. These are the data elements Sage requires to include upon importing any asset information. After configuring the initial input, you will get into the more Sage-specific data inputs. I refer to the second data input as the “Sage Language” input. This contains the necessary details on an asset level to ensure a successful input into the system. These include the property type, asset life, depreciation method, bonus percentage, and declining balance percentage. Generally, these can be joined to the original fixed asset data using an asset class or other asset type identification. The final data input is one that I only recommend for experienced Sage Fixed Assets users, which is the SQL Server database tables. This allows you to connect, via Microsoft SQL Server Quick Connect, to the informational tables which store the Sage Fixed Asset data. When properly connecting and configuring the connection to the tables, it will allow you to seamlessly reference the data currently in the system and reconcile any current period activity. After properly importing the data inputs, it is time to move on to the data manipulation steps.

 The second step of the workflow would be manipulating the data to ensure a smooth importing process into the Sage Fixed Assets software. This will require detailed knowledge in two different areas, the incoming data side, and the Sage Fixed Assets side. From the incoming data perspective, you will need to consider the points that were brought up previously under “Considerations before beginning your Alteryx Workflow”. For example, what are the common transactions across your company? Do you need to account for trailing costs or the bifurcation of prior period assets? Knowing the answers to these questions will help you set up the proper manipulation of your data. From the Sage Fixed Assets side, there are some common issues with importing assets that you may need to address. These include 0 cost assets, cumulative MACRS and bonus depreciation, and assets placed into service and disposed of in the same year. Properly addressing these pain points can save you when it comes to importing the assets.      

The final step in the workflow process for Sage Fixed Assets is the data output. This can be as simple as producing an addition import to the system or as complex as reading the SQL tables to reconcile the data already processed. If the previous considerations were accounted for and the data inputs were properly imported into Alteryx, any sort of output is possible. As a best practice, it is recommended the group the outputs into two categories: current period activity and reconciling the activity. Doing it this way ensures that the data is first processed before it is reconciled.

Conclusion

With the ever-changing nature of tax depreciation, it is imperative to simplify the data preparation aspect as much as possible. Luckily, Alteryx has shown to be an effective middleware to aid in this process.

• Learn more about GTM’s Tax Depreciation Services or to speak with someone who can guide you through the process
• Read the Global Tax Management Sage Spotlight
• Connect with Cody on Sage City