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:

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

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.

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.