Fixed Asset Reconciliation Queries: Building an IT Inventory Template

Guest post by www.assetgurus.com:  First in IT Asset Inventory and Audit.

This tutorial outlines 3 useful and extendible queries that can be run against the General Ledger, including a step-by-step guide as to how to enhance your General Ledger’s capabilities with outside data, including fixed asset reconciliation to the Fixed Asset Register.

This tutorial and attached example Access database covers the creation of 3 queries to answer the following 3 questions about the General Ledger:

  1. What is the percentage breakdown of cost by Vendor?
  2. What is the cost-breakdown of each IT Asset-Type within a given set of types?
  3. What is the cost-breakdown of each IT asset type grouped by Cost-Center ?

General Ledger

In its most basic form, the General Ledger is a list of the purchases your firm makes.

Here is an example:

Grab this attached Access file (unzip first) and take a look at the tables and queries as you make your way through this tutorial.

ExampleGeneralLedgerWithQueries.zip

In this post, we’re considering a small but growing business, from the point of view of their General Ledger, looking to perform a fixed asset reconciliation and build an IT inventory template.  Above, we see their first Purchase Order of 4 items, to set up a single employee with key IT assets to generate and sustain business-function.  We will watch this business grow by way of adding new POs to the General Ledger, and simultaneously extract important data from it.

Specifically, we will create 3 reports (in the technical parlance: queries) concerning this data.

Constructing the Queries

1.   What is the percentage breakdown of cost by Vendor?

In useful terms for a busy CEO or CTO, to whom does money flow to for procurements and services the business needs to function.

Example:

Currently, there is a single payee for the firm with all procurement cash flowing to that manufacturer.  As we grow the General Ledger, we will fill the above with data automatically.  This is an example of an automated report, or query, we can run against the Journal to get useful, actable information.  However, this is the simplest of the 5 reports we will describe.

2.   What is the cost-breakdown of each IT Asset-Type within a given set of types?

>Our General Ledger does not include Asset-Type as a column, so we will have to extend the columns of the Ledger with a look-up table, which will look like so:

There are 2 scenarios that we can put a simple table like this to work to gather information about assets in an organization

Scenario 1 is the easy case, where we can send this table to the vendor as the PO is issued, and the vendor can deliver a spreadsheet with their physical info recorded.  A best-case scenario looks like this:

HP sends this table along with 2 new laptops:

If we could get this data from the vendor, we could import into our expanded General Ledger (now has an Asset-Type field).  In the example Access database, this expanded Journal with the second PO is called [General Ledger with Type Column].  We don’t have anywhere to store the Pre-Applied Barcode yet, but we will make use of the extra field that the vendor could provide us with later.

Let’s consider Scenario 2.  If we can’t get any type information for our General Ledger, we’re going to have to manually enter it.  Let’s talk strategies for manually identifying Asset -Type in the General Ledger:

For the existing case, with 6 items, it would be easy to go through and simply assign the correct Asset-Type to each row. 

  • When we scale up the General Ledger to 100 or 1000 or 10,000 rows, we need a more efficient method of grouping rows. This is when we want to use bulk filtering to try and isolate different asset types. 

  • Once the ‘Laptop’ word is filtered for, it will show only descriptions containing the word ‘Laptop’.  Then, simply apply the correct Type-Code.  Rather than enter data for each row, use Find and Replace to optimize your workflow.  Tip:  Access doesn’t like ‘replacing’ into an empty field, so you may need to have a default value to replace when you filter for blocks of assets.
  • Eventually, you will run out of neat description keywords to filter by, and you’ll end up with the Ledger corner-cases.  Depending on how your description is populated, you may also have reams of line-items that are not in-scope for the report you’re trying to create.  Put another way, you may not have a Type-Code for line-items like hard drives or individual sticks of RAM.  These could have an out-of-scope Type-Code applied to them, like M000. 
  • There are numerous other strategies to speed up manual matching of General Ledger rows to look-up tables, and entire blog-posts could focus just on these techniques.  In general, focus on speed and efficiency to complete the typing, and use grouping via filter or Find and Replace to make your life easier.  Back up your progress regularly, especially if you’re using Find and Replace.

Once we have completed the matching of General Ledger rows with Type-Code, we can run our query to roll up the Purchase Price of the assets totalled by Asset-Type.

In its simplest for, this query looks like this:

Another neat thing we can do in Access is look at the Asset-Types lookup table and expand each Type-Code to get the matching rows in the General Ledger.  Note that the LedgerID acts as the General Ledger’s primary key, or unique row identifier. 

3.   What is the cost-breakdown of each IT asset type grouped by Cost-Center ?

For the last query we’re going to explore, we’re going to kick it up a notch and pull asset information from beyond the General Ledger so that we can incorporate real-time location information about the asset in its deployed environment.   Specifically, we are going to find out what Cost-Center each asset was assigned to (in this case, choosing between: IT Helpdesk, Factory Operations, Software Development, Sales, and Marketing) by performing a fixed asset reconciliation to the General Ledger.

To access current asset information, like where it is and to whom it is assigned (including what department/Cost-Center), we need to have our General Ledger connect with the Fixed-Asset Register.  We’ll need a unique field that both these tables share, so we need to go back to our vendor import spreadsheet from above and include the Pre-Applied Barcode in a new, modified version of our General Ledger.

In the attached Access example, the tables are:

  • General Ledger with Type Column and Barcode (with 3 additional DELL laptops)
  • Fixed Asset Register

This template for a Fixed Asset Register is extremely simple, with only a Barcode field and the Cost-Center.  Normally you would also include physical attributes like serial number or location detail.  For our purposes, we’re only concerned about the department the device is assigned to, so we’re going to ignore these other columns. 

The Fixed Asset Register looks like this:

Fixed Asset Register

Our new query which will show us Type Totals grouped first by Cost-Center, then by Type, looks like this:

Cost Breakdown By CostCenter and Type Example

IT Inventory Template

Now that we have build a method for connecting up the Fixed Asset Register to the General Ledger, we have created much of the IT Inventory Template necessary to physically collect information from the enterprises’ facilities for the purpose of validating the General Ledger, via fixed asset reconciliation.  It’s important for a firm to have a well-defined IT inventory template with a data-refresh at least every other year.  Preferably, the Asset Repository (and thus, the Fixed Asset Register), would be updated every year or rolled through in part every month.

Mastery of the information flowing in and out of the General Ledger gives the Asset Manager surprising visibility into the asset-landscape to power executive decision-making, procurement, cost-benefit analyses, and productivity-maximization. 

 While the SQL statements that power our 3 queries above are beyond the scope of this document, please download and play with the attached Access database.  Click on the SQL icon at the lower-right of the screen when you’re viewing one of the queries in the database and see the SQL code that generates the query tables.

SQL Access Icon Example

If you want to understand the connective tissue of each statement, try Googling the keywords and syntax in the simplest query, and work your way up from there. If you have SQL gurus or DBAs on staff, have them use this example database as a template for building these queries (and more) to act on your General Ledger.

Post your questions and comments here.  Remember, this tutorial only scratches the surface of what is possible with your fixed asset data.

Rolling Out Large Fixed Asset Management Projects

It’s that time of the year again when companies (large and small) are just starting to think about their year-end processes (or just finished with them and their Auditors) when fixed assets come into play.  Property tax season is now coming upon us all and shortly thereafter… tax filing.  

When getting ready to prepare for this type of fixed asset management project, people tend to have a mini (or large) freak out.  I want to remind you all that you don’t have to bite off more than you (and your budget) can chew.  Have you ever thought about breaking your project out into prioritized phases?  Below is an example of the kind of a approach you can take to complete your full fixed asset management project.

Real World Example

Background: Company XYZ is a large fortune 500, multi-location, publicly traded company.  They have well over 50,000 assets within the U.S. 
Current Issues they are facing:

  1. No automated system to calculate their TAX, State and AMT depreciation calculations = hand calculated (spreadsheet formulas) and delayed filing and delayed provisions. (P.S. they are using an ERP solution)
  2. Inaccurate fixed asset listings – due to never performing a physical asset inventory or reconciliation = leads to inaccurate:
    1. Property Taxes and delayed filing
    2. Improper insurance coverage
    3. Financial reporting
    4. SOX 404 Compliance risk
  3. Multiple locations that need an inventory with bar code tagging = thoughts of many, many dollars run through their minds.
  4. Lack of policy/procedures = lack of maintaining accurate fixed asset records.

Recommendations to perform an ACCURATE and MANAGEABLE fixed asset project.

Priorities for this Client were to get their federal Tax, State and AMT calculations automated.  We based their project off of this high priority. 

  1. PHASE ONE: Implement an automated fixed asset accounting solution (Sage FAS 500 Asset Accounting was used).  Finalize this project implementation, work through the data and get it live for use.  Ran their 4562’s, 4797’s, etc.  Receive an immediate return on the software and implementation costs.
  2. PHASE TWO: Start to roll out the physical asset inventory project  through a pilot program.  Starting small — prioritized the PILOT by number of anticipated fixed assets and activity.  Finalize and reconcile this data to observe the findings and obtain an immediate cost recovery on project.
  3. PHASE THREE: Perform the physical inventory on the other locations that were necessary – again, based on priority.  Reconcile results – find and capture the return on investment by property tax savings and insurance premiums. 
  4. PHASE FOUR: Implementation of an automated fixed asset inventory solution that supports the use of bar code readers / technology (Sage FAS 500 Asset Inventory was the solution of choice).  Now the Client can maintain the clean database and keep it maintained with annual physical inventory audits, etc.
  5. PHASE FIVE: Consulting on on-going use / maintenance of the fixed asset solutions and complete management.  Set in place policies/procedures and train staff on the workflow and systems where necessary.

Working with a Phase approach to fixed asset management works to keep it manageable.  Not only is it manageable for your staff and your business… it’s also manageable for your budget, as each phase pays for itself at the end.  Every step of success opens doors for more.

Sage Summit 2011 Fixed Asset Management Workshops

Sage authorized consultants from Paragon will team with current Sage FAS customers in three breakout sessions to discuss best practices in fixed asset accounting and management at the Sage Summit 2011 conference.

Chicago, IL – July 5, 2011 – Paragon Systems (www.FixedAssetExperts.com), a nationwide provider of Sage FAS Fixed Asset Management software, has announced participation in the Sage Summit 2011 conference as an exhibitor as well as a workshop host.  In addition to their display in Booth 733, Paragon will also present three breakout sessions discussing the advantages of fixed asset accounting and management with Sage FAS Software.  The conference will be held at the Gaylord National Hotel and Conference Center in Washington, DC.  July 10 – 15, 2011.  Sage Summit 2011 represents a new format that combines both Sage Authorized Business Partners and Sage customers at the same event.

FULL PRESS RELEASE ]

Nice Assets – Wait, What Are Fixed Assets?

I would like to start this article off with a little story.  First of all, I fly a lot to go and do what it is I do – I mean everywhere (love United frequent flier status – you are welcome for the plug) often chatting it up with my flight compadre next to me.  Nine times out of ten we exchange our titles and ask each other what we do for a living.  My conversation often goes like this:

Me: “I sure hope we take off on-time”

Compadre: “Me to, I’m on my way [insert home or work]”

Me: “What do you do?”

Compadre: “Oh I’m a [insert anything but a Fixed Asset Consultant].  How about you?”

Me: “I’m a Fixed Asset Consultant.”

Compadre – with a smirk: “fixed assets hu?  Hahahhaaaaa!”
(depending on the personality of this person I either carry on the funny nuances of the words fixed assets or I move on)

Me: “Yes, I play on those words as much as I can. You’re funny.”

Compadre: “All joking aside, really, what is a fixed asset?”

Which leads me to discover that after 13 years of implementing fixed asset management solutions and writing workflow procedures for over 300+ companies (of all sizes), people still don’t know what a fixed asset really is or why they should care.  Sometimes, even those who take care of fixed assets.

So, what is a fixed asset? A fixed asset is really just a tangible asset like: land, buildings, office equipment, computer equipment, furniture & fixtures, vehicles and machinery / manufacturing equipment.  Every company or organization has them.  Then, they depreciate and based on what type of asset it is (personal or real), they depreciate on different schedules and methods.  Tax has different rules and regulations than GAAP.  If you are non-profit or government, tax is taken out of the equation.

Why are they important you ask? Don’t get me started.  Fixed assets help your business grow or assist in creating your product or service.  Mis-managing fixed assets brings a whole slue of problems like overpaying on insurance premiums or even worse… TAXES, you often end up with a higher total cost of ownership, RISK of non-compliance, missing out on income tax deductions and more (internal theft also comes to mind).

In a nutshell, if you don’t know what a fixed asset is and you don’t manage them properly you could be missing out on deductions for your business to keep growing!  These days, no one wants to go out of business or lay anyone off.  Pay attention to what you have – you could be doing your business a disservice!

I’m off to purchase another ticket to my next United destination to dedicate myself to more fixed assets!  Later.

100% Bonus Depreciation: Construction Clarification

In reference to the new 100% bonus depreciation rules, the law and the joint committee report seem to allow 100% bonus on all additions of self-constructed property as long as construction did not begin and a binding contract was not entered into prior to January 1, 2008.  Some commentators have come out with their informal view that the intent of Congress was to only allow 100% bonus on self-constructed property if construction began after September 8, 2010.  Under this interpretation, 50% bonus would apply to asset additions if construction began after December 31, 2007 and before September 9, 2010.  

The IRS has promised to issue guidance on this issue, and it is scheduled to be released some time later this month.  We will do our best to keep you posted.

Implementing FAS with SAP ERP: Live Updates

We get a lot of questions about how FAS (and if) integrates or works with ERP systems.  Since all ERP systems do not have a true “fixed asset management solution”, nor do they admit to having one – many businesses look to other solutions, especially for tracking and running automated depreciation for federal Tax and State books.  While ERP can track and depreciate internal / GAAP book just fine (straight-line is easy to code into a system) – they fail on keeping up on the never-ending depreciation updates of federal Tax and State.  This is where Sage FAS Asset Accounting shines!

Here at Paragon Systems, we often implement the Sage FAS solution(s) along side an ERP solution – such as PeopleSoft, Oracle and SAP.  We figured that since we do this often enough we should share the progress of one of our latest projects.

Currently, we just started implementing Sage FAS 500 Asset Accounting at one of our larger clients.  They are utilizing SAP for all of their worldwide financials.  They (as many do) have been struggling with the lack of automated depreciation for federal Tax, AMT and all State calculations — especially with all the bonus and Section 179 changes.  It’s a nightmare to get all the new bonus rules, property type changes, Section 179 limitation updates, etc. into their SAP fixed asset module.  Not only is a time sensitive issue, it’s also VERY costly (which makes FAS 500 pay for itself  immediately).  Their tax department was definitely spending too much time on manually calculating both tax and state calculations in mass amount of spreadsheets.

We (well, I, Angie Bolton-Lyons) will keep you updated as our progress evolves.  Client name will NOT be provided due to obvious reasons.

Today – we installed our extraction utility (remotely) and are on our way to getting the first round of test data out.  Success!

Sage FAS v2010.1: Bonus and 179 Info

Most frequently asked question – by a long shot:

I am using Sage FAS version 2010.1 and have noticed that it is not updated for bonus on 2010 acquisitions nor is it updated on the new Section 179 limitations.  Will this be in the next version?  When can we expect Sage FAS v2011.1?

Angie’s answer:

Currently, you CAN track bonus depreciation on all 2010 qualifying assets.  Sage FAS was pro-active with version 2010.1 (current) and kept it in the system for 2010 assets – however, FAS will not default to the bonus method based on the Property Type and Placed-in-Service date (at this time).  Instead, you need to SELECT the method of MA.  Once you select this method, FAS will prompt you with a WARNING – of which states (in short) that “Bonus for 2010 Assets is currently pending legislation….”  click yes / ok to bypass this message.

As for updates to the Section 179 provisions, this is schedule and already in place for version 2011.1.  Typically Sage FAS starts to ship the year-end version releases in mid-December.  However, with the highly controversal tax bill [just passed Senate 12/15/10] that includes additional updates to bonus, section 179, energy property and more, Sage is waiting for the outcome of this so these new provisions can be in the system and out to end-users ASAP. 

New target date for Sage FAS update of v2011.1 is slated for mid-January 2011.

Blog at WordPress.com.