Here at Fixed Asset Consulting, we have been performing many data conversions lately. Mainly data that has been in the Sage Fixed Assets Depreciation (formerly Sage FAS) and was outsourced.

The biggest problems we are running into during our analysis and process is that those ‘outsourced’ professionals who are using the Sage Fixed Asset System (or any automated fixed asset system) are not entering the asset information in correctly. Here is a list of what we have been seeing (note: not all the data we are analysing and fixing are from the same source/end users):

Bonus depreciation not being utilized consistently. Some assets have it on, others don’t. Same property type, same placed-in-service year, etc.

Bonus depreciation not being used at all even though the Client is claiming in on their tax returns. This means that even though the Tax calculations are outsourced, the Client still has to perform off-line adjustments and calculations on a spreadsheet.

Inconsistent data entry and misuse of critical depreciation fields.

Property Types are incorrectly used. I.e. Buildings and building remodels are set to Personal Property with a 39 year life and a straight-line method. Should have been set to Real Property.

Adjustments were never taken and trued up on the data. This is causing a lot of over and under depreciated assets to occur at the end of the assets life, creating a NBV on a fully depreciated asset.

One line items that represent 3 or sometimes 10 assets. When reconciling this information back to the customers off-line data or internal data, some of those “assets” where disposed of. Or… the sum of the 3 assets don’t equal the total amount of the one asset in the outsourced system.

We could go on and on, however, many times this stems from the end-user not being professional trained on the system, being thrown into fixed asset management role without training, not communicating properly with their clients or just not knowing what they are doing.

Not all fixed asset users or those managing fixed asset data should be created equally. Problem is, many don’t know this is happening until it’s too late. Don’t be one of them.

New Fixed Asset Management Outsourcing Service Launches

Fixed Asset Consulting, LLC (http://www.fixedassetconsulting.com) launches new outsourced service for companies that lack the in-house resources or expertise to navigate complexities of fixed asset management.

EUGENE, OREGON – December 3, 2012 – Fixed Asset Consulting, LLC, a specialized consulting firm led by noted Fixed Asset Management expert Angie Bolton-Lyons, has announced the launch of its new Fixed Asset Outsourcing service. The new offering solves accounting and compliance concerns for companies who lack the in-house resources or expertise to navigate the complexities of Fixed Asset management.

According to Bolton-Lyons, the new fixed asset outsourcing service fills a huge gap in an underserved market. “There aren’t many fixed asset outsourcing options out there and even fewer with the level of experience that we offer. At a recent tax accounting conference, every CFO I spoke with said the same thing about their fixed asset management process: it’s a mess.”

After providing fixed asset consulting and technology solutions for nearly 20 years, Bolton-Lyons notes that for some companies, knowing how to manage fixed assets isn’t enough. She says, “Many of our clients also need someone to take control of and ‘own’ the process itself – reconciling assets, creating depreciation calculations and journal entries, and generating reports for state and federal requirements. That’s where the new outsourcing service comes in.”

The new Fixed Asset Management Outsourcing service begins with cleaning up and organizing existing fixed asset data, followed by:

Generate depreciation schedules and fixed asset registers

Generate reports for book, state, and tax purposes

Create journal entries for general ledger postings and reconcile back to financials

Provide tools and processes for tracking new fixed assets and disposals moving forward

Provide year-end audit support including Form 4562 and other documentation for tax purposes

The most convincing argument for outsourcing fixed asset management goes beyond compliance or convenience. Bolton-Lyons also points out that the ROI is tremendous. “When clients see how much time and money we help them save, they’re convinced. In most cases, we’re able to accelerate deductions and generate more allowable expenses, all while reducing their administrative overhead. They also appreciate the peace of mind that comes from knowing their fixed asset accounting is accurate and compliant with current tax code.”

Fixed asset outsourcing is available on a monthly or quarterly basis and starts as low as $89 per month depending on the number of assets to be managed. Bolton-Lyons says, “We’ve set a low barrier to entry and are providing businesses with an option they may not have considered or even known about. You can simply go on with your daily business activities and still get the benefit of expert fixed asset management at a price that’s extremely affordable.”

Offices located in Eugene and Portland, Oregon as well as San Diego, California. Providing fixed asset management solutions, such as the Sage Fixed Asset product lines, training, data conversions, physical asset inventories, fixed asset management outsourcing services, and a wide array of additional fixed asset consulting services for almost 20 years. Serving small, mid-sized to large Fortune 100 companies all over the United States and other countries when necessary.

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:

What is the percentage breakdown of cost by Vendor?

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

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.

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.

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.

For every fixed asset management service contract signed with Fixed Asset Consulting, LLC (starting December 1, 2012) through December 31, 2013, FAC, LLC will donate 2% of the project total to Stand Up 2 Cancer in your companies honor.

You can help Team FAC / PDM Give Back Attack reach our personal goal of $25,000 by December 31, 2013. Cancer is bigger than all of us… let’s assist our kids, future kids, grandchildren, future grandchildren and other family members have a fighting chance.

Paragon Systems (http://www.FixedAssetExperts.com) has announced their certification for both Winshuttle Query and Winshuttle Transaction software utilities for SAP reporting. Using the Winshuttle utilities, Paragon is able to help larger U.S.-based companies overcome significant shortcomings and inefficiency associated with the native fixed asset management functionality in SAP.

Angie Bolton Lyons, Senior Fixed Asset Consultant and Sage FAS Fixed Asset expert at Paragon, says her company’s interest in Winshuttle began by listening to customer needs. “Many of our larger clients using SAP Fixed Assets were running into significant challenges. When it comes to fixed asset management, accounting in SAP isn’t well-localized for U.S. companies.” Bolton-Lyons says that those clients were forced to use spreadsheets and perform manual calculations outside of SAP in order to manage important processes like quarterly tax updates, reporting, and complex deprecation calculations.

The right tools, she says, make all the difference. “The data doesn’t extract cleanly out of SAP fixed assets if you’re using Excel or some other manual method. But with the Winshuttle utilities, we’re able to get the live data out easily and accurately using a simple three-step process. It’s so much cleaner, faster, and most importantly saves our clients a tremendous amount of time and hassle.”

Paragon consultants use both Winshuttle Query and Transaction utilities to pull accounting data from SAP and transfer it seamlessly into Sage FAS Fixed Assets. Bolton-Lyons says, “Sage FAS Fixed Assets is far more flexible and capable when it comes to enterprise asset management. Once the data is in Sage FAS, our clients can run calculations and get the numbers they need in minutes – not days or months.”

According to Bolton Lyons, the problems with SAP fixed asset accounting (http://fixedassetexperts.com) really aren’t unusual. “Paragon knows fixed asset accounting is a different animal and requires specialized knowledge. To some clients, it may seem counter-intuitive to add more software to an enterprise system like SAP. But Winshuttle Query and Transaction are really just utilities that make their existing investment more valuable and useful.” She adds, “And when they see how much time, hassle, and costs that we eliminate from the fixed asset process, it’s a no-brainer for our larger clients.”

While on site last week performing fixed asset consulting and data work, my client had encountered last-minute auditors. Not just one, but three (3) from different places. Funny thing was, they were keeping their fixed asset listings and their CIP (construction in progress) accounting assets in the same spreadsheet. Boy, was it a long one with filters and pivot tables, etc.

During the first day of my stay, I uploaded their fixed asset records into FAS Asset Accounting, ran some preliminary depreciation to balance back to FY 2010. In a matter of hours I was able to prepare a report in 3 different formats and export out to a PDF for the corporate Controller. At the end we noticed about a $95K difference in depreciation – in their favor!

The next day the Auditors were in and out of the Controllers office asking all about fixed assets and their current schedule and if they could get a listing sorted by asset class / asset type with sub-totals by location. With their current “system” (aka: spreadsheets and SAP fixed assets), they couldn’t get this or make this readily available. However, with Sage FAS Asset Accounting they were able to very quickly.

Thank goodness they had a fixed asset Consultant on deck that week. Next time they have an unexpected / unanticipated audit (they thought they had until December), they will be even more ready with CIP reports as well. So the question is… are YOU and YOUR fixed assets audit ready?

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.