Managing the tax depreciation process is a complex endeavor that involves the collection and calculation of data points taken from a number of different sources. All too often, this process is complicated even further by outdated manual processes.
Alteryx is changing how companies manage repetitive, time-consuming data manipulation processes. Alteryx Designer is the drag-and-drop programming tool that allows you to take a data set and completely transform it into a consumable format for any need a company may have. 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.
Before Beginning Your Alteryx Workflow
Consider the Source of Your Data
The first step when using Alteryx to streamline your tax depreciation process is to consider the source of your data. Will your data be coming from an ERP system, other fixed asset software, or an Excel spreadsheet? This is important so you can understand how you should set up your workflow. In a best-case scenario, you will want a data source that is free of any human manipulation. These needed manipulations can always be built into a workflow.
Understand What the Source Data Represents
Taking the data one step further, you will need to understand what the source data represents. Is the data a transactional-based extract, or does it show 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 which contains all the asset specific details (e.g., location, company, department,) and child assets that 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 that represents 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 greatly reduce the time needed to manipulate, import, and reconcile the fixed asset transactions.
Understand the Transactions That are Common to Your Company
One of the most important aspects when building an Alteryx workflow is to understand the fixed asset transactions that are common 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, such as 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 up 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 in which 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 onto building your Alteryx workflow.
Building Your Alteryx Workflow with Sage Fixed Assets
One of the best aspects about Alteryx is that there are thousands of ways to do the same thing, all driven by the thought process of the user. The world is your oyster, depending on your approach. To break it down practically, let’s look at three key steps of a workflow: data inputs, data wrangling, and data outputs.
Step 1: Data Inputs
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 that Sage requires to be included upon import of 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 the 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 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 onto the data wrangling steps.
Step 2: Data Wrangling
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.
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 manipulations 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.
Step 3: Data Outputs
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. When doing it this way, it will ensure that the data is first processed before it is reconciled.
With the ever-changing nature of tax depreciation, it is imperative to simplify the data preparation aspect as much as possible. Alteryx has shown to be an effective middleware to aid in this process.