Fixed Asset Reconciliation Queries: Building an IT Inventory Template

Guest post by  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.

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.


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.

Fixed Asset Inventory and Disaster Recovery

Insuring your fixed assets is useless if you aren’t prepared to substantiate your cliams when your worst nightmares come true.  Does your recovery plan include YOUR valuable fixed assets?

Del Papa Distributing is celebrating 100 years distributing fine beer and beverages across 17 counties in Texas. The company sells more than 10 million cases each year from three distribution centers and employs more than 340 individuals. To ensure its numerous assets are efficiently tracked and accurately accounted for, Del Papa Distributing relies on Sage FAS Fixed Assets software and Paragon Systems. Recently, the company sought to better control its assets through a barcode system, and contacted Paragon Systems, a Sage business partner specializing in the implementation of asset management and asset inventory applications.

Maximize Your Investment

“We have a large number of assets throughout the company and tracking them by barcode makes good business sense,” says Crystal Bryan, director of accounting services for Del Papa Distributing.

“Barcoding speeds the count of assets and the simple presence of an asset tag on a piece of equipment lets people know an asset is being tracked,” Bryan explains. “We looked at several other asset tracking systems, but selected FAS Asset Inventory for its capabilities and also because it integrates with our existing asset accounting software. The integration helps us to maximize our overall investment in technology.

How did we (Paragon Systems) help Del Papa get back on track?  What should you be thinking of?  Read the full article…. Fixed Asset Inventory and Disaster Recovery

Top 5 Ways To Find Money Through Fixed Assets

Wow… today the stock market crashed so big it has probably got us all thinking… where do we go from here? Which also gets me thinking — how can businesses find some cash flow so they have some “rainy day funds” (or for operating expenses) in their back pockets?  Then it dawned on me… if they manage their fixed assets properly and received detailed cost segregation studies or had a proper 3115 study done, then shoot, EVERYONE could have extra cash flow.

If you think about it, there really are many ways to capture extra cash flow through fixed assets.  From very small efforts to large.

Top 5 Ways Fixed Assets Can Capture or Re-capture Cash Flow

  1. Cost Segregation Studies – extra Tax Deductions with properly classified fixed assets.  Money is in the DETAILS, not in bulked entries everyone!  Make sure you capture your 100% bonus depreciation for 2011 – it will go away soon.
  2.  Rev Proc 2007-16 Study – 3115 assets; allows taxpayers to change their method of accounting and claim the allowable depreciation (or amortization) amount they never claimed (i.e. bonus depreciation, etc.).
  3. Physical Fixed Asset Inventories – cost savings all across the board with Property Taxes, Insurance Premiums, Financial savings impact and more.  Have you ever done one of these before?
  4. Asset Appraisals – is it really worth TODAY what it once was?  Probably not.
  5. Automated Depreciation System – if you are still stuck in spreadsheet land for calculations, believe me when I say, YES you ARE missing additional expense and bonus that you are entitled to.  I see it every time I open someones spreadsheet!  Doesn’t matter the size of the organization or spreadsheet, there is ALWAYS calculation error, sometimes in the millions.

Which industries would benefit from these services / studies?  Just about all industries, well, maybe government and non-profit wouldn’t benefit from all five, but certainly from a couple.  Industries that would uncover a ton (always in the thousands – sometimes millions) from one or all five: hospitality, data centers, banks, manufacturing, retail, healthcare to name a few.

Now I know why I love waking up every day to go to work for the past 14 years (and growing)… because everything I (and my associates) do each and everyday help people and their businesses grow.  Who doesn’t like that?  Probably the same people who don’t like furry fuzzy kittens.



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.

Physical Fixed Asset Inventory / Audit: Quick Start

In the past few months, I’ve received about one or two calls a week with questions pertaining to conducting physical inventories.  Which gets me thinking… Oh yeah, it is about that time a year again.  Internal audits of companies fixed assets! 

About this time every year is often when companies start putting deadlines on themselves for gathering their information.  Many times it is sparked by internal audit controls, SOX testing, reorganizing, or better yet, “the auditors just left and said we need to do a better job controlling our fixed assets“.

After having several conversations with all these different companies; their situations running in a variety of flavors… I get a little worried that perhaps, they are still going to venture off in the wrong path.  The one of destruction not success.

 Things to ponder and prepare yourself for BEFORE running willy nilly amongst your assets:

  1. First thing to ask yourself – Is everyone on board internally with this project?  By everyone I mean, departments other than Accounting?  Remember to think of your compadre who work there too.  Example: IT Department, Facilities, Tax Department versus Finance (crazy, but we run into this miss-communication factor quite often).
  2. Second part – where is your data now and what’s it look like?  Are you pulling your fixed asset data from a spreadsheet, home-grown system, IT’s list/system, Facilities listings?  You need to know what data you have and what it looks like so you can better determine what course of action you will be performing with your inventory.
  3. Third piece of the puzzle – AFTER you review your data and data sources, you need to determine if it’s best for you to perform either a Dynamic Inventory or a Baseline InventoryThis is the BIG decision people!  You make the wrong choice here and you could be wasting a lot of precious time and end up taking f-o-r-e-v-e-r on your project.  Or even worse, you may end up doing it all over again!
  4. Moving forward – Labels / Tags… what kind, how many, what are they going to look like?  Order them and provide yourselves with a couple of weeks turn around.  It’s all about scheduling.
  5. Tag em’ – by now you should have already determined what you are tagging, where your putting the tag and who is tagging.  Also, you should have already decided by now WHAT information you will be gathering.  Seize the day and attack!
  6. The BIG Bear of Inventory – reconciliation time folks!  Keep in mind and be prepared that this CAN and WILL take some work.  Especially if your company is a first timer (meaning – you’ve never performed a physical asset inventory – ever).  However, this is where the fruit of your exhausting labor comes into play.  You get to find out what you have, what you don’t have (ghosts… BOO), where it is and who is responsible for it. 
  7. Last but certainly NOT least – keep up the good work my friend!  Now that you’ve cleaned up your data, keep it clean.  Now is the time to determine when you will be performing the next one to ensure that your data stays accurate.

Whew, I feel better getting that off my chest.  I know what you are thinking and I agree, that does seem like a lot to think about and plan before running like a crazy person through the field of assets.   Knowing about fixed assets is one thing… Trying to find them and clean them up is another! 

P.S. did you know that Paragon Systems offers an array of physical asset inventory services and consulting?  Yeppers… ala carte or full load.  Email me.

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.

Love Your Assets – Tag Em’

Do you truly love your fixed assets?  How about giving them a Valentine today and get serious about conducting a physical inventory!  Sounds romantic doesn’t it.

Ask yourself this question, has your business or organization ever conducted a fixed asset inventory audit?  Is your answer no?  This is all too common.  What everyone doesn’t understand is that knowing what you have and what you don’t have is a really big deal and greatly impacts your budget amongst other benefits.

Not only does conducting an inventory audit ensure your accuracy of asset information on your books, it also sets a best practice of managing your assets.   Before you even think about starting your inventory, you need to make sure you have everything planned out and ready to be executed BEFORE you (or anyone off the accounting street) hit the floors with a clip board and some inventory tags.  This will turn out to be a disaster!  A few things to think about prior to auditing:

  1. Company-wide Participation — Make sure everyone and all departments are on board with this project.  If not, this project will fail before it even gets started.
  2. Asset Tagging — What are you going to tag?  Where will you place the tag? How many tags will you need, will they be customized?  What about the starting sequence?  The best one… what TYPE of tag are we going to implement?
  3. Data Collection — What information will be obtained when we are out there?  Where in the heck will all this information go both in the field and out of the field?
  4. Reconciliation — Now that you’ve collected your asset data, make sure you match to your original records and CLEAN THEM UP.
  5. On-going Management — Now what’s your plan for the future?  Don’t take all that time to clean just so you can get dirty again.

Now that you have shown some love to your fixed assets — nothing says you really care like an automated full circle fixed asset management solution!  No, spreadsheets don’t count here – nor do clipboards.  What should you choose?  Hahahaaaa… well, Sage FAS Track Pack with industry leading bar code readers of course!  Email me a valentines asking how you can get started showing the love.

From my assets to yours, Happy Valentine’s Day!

Blog at