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.

Baseline or Dynamic Asset Inventory?

When discussing someones upcoming physical asset inventory with them…  “Angie, we are just going to take our outside fixed asset listing of all our capitalized assets from [insert: CPA firm, outside agency, head department, etc.] and use that to FIND and TAG our assets.  We don’t need to do a Baseline because we already have a list.”

Really?  I don’t like to be frank and when I am, I am very diplomatic.  But honestly… the whole reason people come to me (or my other associates at Paragon Systems) is because they DON’T have a CLEAN list – or one they just adopted one.  The whole point is to gather data, reconcile back to accounting records and GET a clean fixed asset list. Why?  To continue to keep it clean so they know what they have and what they don’t.  There are many benefits to a clean and successful inventory.  Ok, getting off my soap box. . .

May I please share with you the reasons why you SHOULD NOT conduct a Dynamic inventory of your fixed assets if you don’t have a GOOD list — or one that hasn’t been yours?

  • List Integrity – the whole reason that someone takes over (brings in-house) their assets is because their ‘outsourced/other’ list isn’t correct.  Right?  Right.
  • Timing – it’s going to take a LOT of time and man-power to look for every needle in your very large haystack. 
    • Individual timing: instead of taking an average of 3 – 5 minutes tagging and collecting data for one asset, it will take about 30 – 60 minutes.
    • Overall project timing: months and months and months of tagging and data collection.  Will you ever really finish?
  • Reconciliation – it is always easier and more efficient to conduct your reconciliation off-line back at your desk than on the fly in the field.  Once you’ve collected your data during a baseline inventory, you can always perform a comparison against your data in Excel.  This assists you with filling two (2) buckets: matched assets and unmatched assets.  You will be able to determine which assets you will need to investigate on (the not matched) much quicker AFTER collecting your data (via Baseline). 

Dynamic – GREAT option for those of you with a fixed asset list.  Upload your data into an automated hand-held bar code scanner (preferred method) and start your inventory.  Update any / all descriptive fields of information while you are in the field.  End result – validating your data in the field.

Baseline – You have no accurate list (or a list at all) or a highly imperfect one and you start tagging your assets to capture all the data about the asset.  Although it might seem obvious, make sure that you pre-define what information you will be collecting on your assets before you go willy nilly.  End result – building your list.

Bringing my point home.  Why spend days, months or even years hunting for a needle that may not even exist at the end of the day?  If you have a list you DON’T trust (or one at all), go Baseline!  Who wants to start over?

More information on the differences between a Baseline and a Dynamic phyiscal inventory.

Anyone Missing Fixed Assets?

Recently myself and another senior fixed asset consultant went on site for a discovery meeting at a prospects who are having issues with controlling their fixed assets.  Some were tagged, some were not.  Those that are/were tagged had different types/formats of tag’s, etc.  They have about six (6) locations throughout the United States and have their fixed asset data in about 8 different Sage FAS companies — each one set up a little different.

Their computer equipment assets that were getting ready to be disposed of.  However, some had tags, some didn’t… not all were recorded to accounting — and if/when they were… they didn’t always have a unique asset id number to use. 

After about a two (2) hour discussion about their upcoming project and what the best approach would be, my associate and I walked out the front door… and a TON of their fixed assets were too!  Guess what, they didn’t know.

Best practices of conducting your own physical asset inventory – free webinar.

Asset Inventory: Tracking Software Licenses

FAQ About Sage FAS Asset Inventory – I see a trend.

I’m getting the question about how to use the Sage FAS Asset Inventory solution to track software licenses within an organization. Although there are many free “sniffer” solutions out there that will go out and obtain each users software licenses, sometimes, that’s not enough.

You can utilize the Sage FAS Asset Inventory solution in two ways:

  1. Create a new ‘company’ called Software License.  You can rename all the fields that are available in Asset Inventory (yes, even the ones that are already named out of the box).  You can name them: operating system; office version; accounting software; etc.  Then you can create drop-down choices in the SmartList (aka: drop down) to incorporate versions, types, etc.  Other fields you can track information on are the obvious: Registration Codes, Serial Numbers, Purchase Dates, Renewal Dates, Warranty Dates.  FAS Asset Inventory also allows you to upload and attached images to your assets/line items.  So if you wanted to scan in your warranty docs, purchase orders, shipping slips, etc. you can!
  2. Implement software tracking INSIDE your current FAS company.  Instead of keeping your software licenses separated, you can implement this same scenario within the same company structure that your Asset Accounting users are utilizing.  The largest BENEFIT of this is to keep ONE record of the workstation in ONE place.  Which drastically improves communication between departments AND the balance sheet.  Which IT Departments may not care too much about, but Accounting sure does!  Difference – you may be limited to the number of user fields.  FAS Asset Inventory has 10 ADDITIONAL fields that do not carry over to the Asset Accounting interface.

The Sage FAS solutions have many different ways you can slice and dice.  So, think outside the box when considering inventory and depreciation solutions — better yet, let me help you: Angie (877) 824-6834.  Let’s chat!

IFRS – Separate Large Fixed Assets Into Individual Assets

Accounting Topic IFRS – Application Challenge

We have been getting a lot of requests for IFRS in our fixed asset management solution.  Our response is plain and simple, no, not yet.  We say, not yet because…. well, even congress can’t decide how business should report, the formatting of the report(s) and other various obstacles prior to June of next year [2011]. 

Daily we receive emails and other content discussions on the IFRS issues and until everyone stops being “overambitious” and IASB and FASB can get along and come to an agreement, how can anyone develop and deploy any software that is “complaint with IFRS”.  Heck, no one really knows what that is.

So… in the meantime, if you are among the lucky who need to prepare themselves for IFRS, here are some tips to get you half way there – or at least part of the way:

  • Separate your “bulked” fixed assets into individual components / line items.
  • Revise your mapping of useful lives to asset codes.
  • Improve your fixed asset tagging system (or implement one) to accommodate impairments and impairment reversals.

Just these three suggestions can get you prepared for IFRS and on the right track for, whatever the accounting world decides. 

Latest article Angie found interesting: Convergence talks accused of over-ambitious targets

Stolen Fixed Assets

Physical Inventory = theft deterrent.

Common Question – How can stop getting our assets stolen, or even better, track our assets so we know if they are stolen?  The answer is always the same, and the answer is easy… CONDUCT A PHYSICAL ASSET INVENTORY of your fixed assets!  For cryin’ out loud.

Recently (March 2010) Paragon Systems conducted a physical inventory of all fixed assets at a large hotel because the hotel client wanted to account for all assets because they were afraid people might steal them when they moved, demolished or had to close down.  Smart.  Just a week after Paragon’s team left, this hotel now (finally) has a complete listing of EVERYTHING in their hotel: furniture, fixtures, computer equipment, office equipment, ART (see my other entry about hotels & resorts and their art), gym, pool area, kitchen equipment, and much more.

Most companies don’t take the time and effort in implementing a full-blown physical inventory of their fixed assets (capitalized or expensed), but I tell ya what… do it once and then you can maintain it going forward.  Like anything else a company implements: CRM systems, Accounting systems, etc.  The heavy lifting is getting it up and running with extremely great (or we strive to make it) work flow.  After it’s implemented, moving forward is a breeze.

My final note: make sure you are keeping all your assets out of the hands of someone else!

Blog at WordPress.com.