• No results found

Go From Spreadsheets to Asset Management in 40 Minutes


Academic year: 2021

Share "Go From Spreadsheets to Asset Management in 40 Minutes"


Loading.... (view fulltext now)

Full text


Go From Spreadsheets to Asset

Management in 40 Minutes

January, 2013

E-ISG Asset

Intelligence, LLC

3500 Boston Street Suite 316 Baltimore, MD 21224 Phone: 866.845.2416 Website: www.e-isg.com




This guide will provide you with the information and examples to organize your asset

management and legacy information into excel spreadsheets to import your data into eQuip! or Visual Asset Manager with minimal work and maximum accuracy. Following this guide, you will be able to organize these Excel sheets so that data can be rapidly imported using the import/update wizard in eQuip! and Visual Asset Manager. This wizard allows you to create multiple worksheets, use formulas in your worksheet to minimize typing, and provide data to use as lookups so that you can quickly build template worksheets that tie into your workflow so that data in eQuip! is only entered once for the lifetime of that data, and managed in one place. Following these examples will provide you with work saving tips that help you leverage the benefits of an industry standard asset management system.

Organization of an Asset Management System

The basic organization of the Asset Management System is a general division based on the Site of the Enterprise. The site is a building, campus, county, etc., denoting a common denominator, ownership, or control of the equipment. In eQuip! or Visual Asset Manager, the first data entry in the system is the site name, site code and description of the controlling Site. The Site Code is a short (up to 10 characters) code that will be used to create unique classification codes or barcodes (should none be given). The site


Zone of Responsiblity Server Desktop Desktop Laptop Location Location Location Person Who Controls Equipment Equipment Assigned to Location Person Assigned To Location


Zone of Responsiblity Server Desktop Desktop Laptop Location Location Location Person Who Controls Equipment Equipment Assigned to Location Person Assigned To Location Site



name should be meaningful as this will be a common delimiter of all data.

Additionally, the asset data can be classified further. For example, assets are controlled by departments, or can be subdivided by zone of control classifications such as lease names, or outside groups. In eQuip! or Visual Asset Manager, these types of classifications are called Zones. Assets are found in locations, and are used by responsible owners. In organizing your enterprise asset system, these classifications are important and need to be noted and used when organizing your legacy asset data or creating a new inventory. Finally, eQuip! and visual asset manager classify assets by a category system that is defined either by our predefined equipment frameworks, or can be defined by your organization.

Locations in eQuip! and Visual Asset Manager can also be classified by a muli-level category system defined by the framework or by your organization In the Emergency Management Framework as an example, locations are classified by the National Incident Management System’s (NIMS) Discipline, and for each Discipline, further categories of location are the NIMS category of Discipline. In the ITIL framework, the Location can be classified by Department, equipment rack, or IT Owner.

Finally, locations can be placed on a floor plan, giving a visual indication of where assets are located. If locations are scattered remotely, these locations can be placed on a Google Map as a map annotation.

In order to achieve the goal of classifying and organizing assets into an enterprise asset management system, the following capabilities can be utilized in eQuip! and Visual Asset Manager:

1. Import wizard to create, or read excel templates.

2. Use of floor plan drawings and Google Maps classified by Site and the use of floor plans to be placed on parent floor plans to indicate a hierarchy or larger zone to smaller, more detailed plans. An example of this hierarchy of floor plans is a datacenter map pointing to a number of drawings indicating equipment racks.

3. Selection of Zones with the ability to place those zones on a Google Map or Floor Plan. 4. Placement of equipment directly on the floor plan to show commonly used assets such as

printers, copy machines, or fax machines.

5. Use of plug ins to add additional features and functions for data editing, facilities planning or asset checkin/checkout functions.

6. Create dynamic messages to provide indications of assets that are needing attention or are coming due for retirement.

As you will see, eQuip! and Visual Asset Manager provides complete support for automating the business process of Enterprise Asset Management, and provides the metrics to show how efficient this process is for your organization.

Initial Setup of Visual Asset Manager or eQuip!

Although there are many options to use when doing an initial setup of eQuip! and Visual Asset Manager, we will cover the essential options. If you have initially installed Visual Asset Manager, or signed up for eQuip! and have chosen a predefined framework of IT Asset Management, Facilities



Management, or Emergency Management, then the category system, fields used to store framework specific data, and some key performance indicators are pre-defined, so configuration of categories and fields will not be needed. When installing the Basic framework, no categories are defined, and tabs and fields in the asset detail records are not defined with the exception of the basic summary information and inventory information about the asset.

To edit Options in Visual Asset Manager or eQuip, select the Admin menu and then select Edit Options:

The three options that you will need to change can be found in the later part of the options list:

The Google Map API key needs to be set in order to use the Google Maps for your domain if using Visual Asset Manager. For eQuip, this API key is preset. There is no charge from Google for applying for and obtaining a MAP API key.


4 To create your API key:

1. Visit the APIs Console at https://code.google.com/apis/console and log in with your Google Account.

2. Click the Services link from the left-hand menu. 3. Activate the Google Maps API service.

4. Click the API Access link from the left-hand menu. Your API key is available from the API Access page, in the Simple API Access section. Maps API applications use the Key for browser apps.

Copy and paste the API key into the options value field of the Google API key option.

The second option is to set the barcode generation setting. If you want the Visual Asset Manager or eQuip! importer to generate barcodes when there is no preexisting barcode, by setting the Generate Barcode option to “True” and the Barcode Value to a predefined start number, the barcode value will increment the predefined number by one, and save the value in the setting, for further use. The generated barcode will be the Site Code of the existing site where you are importing the asset, and the current predefined value. This guarantees a unique barcode that can be used as a placeholder, or a permanent asset tag.

The final option to set is the SMTP server name, SMTP user name, and SMTP password so that you can have reports and dynamic messages emailed to you or your other user’s account.

Sites, Maps an Floor Plans

To add or edit a site, select the Admin Menu, Manage Sites option:

This option will enable the Site Editor where you can create the sites that will differentiate your asset data. Before doing any data importing, these sites will need to be created and the data import spreadsheets will require the Site Name you enter.



Once you have created the list of sites, you can add the maps or floor plans to be listed under each site. Click on the icon to watch the short instructional video on adding sites, or adding maps and floor plans.

Creating and Using Spreadsheet for Visual Asset Manager Importing

We have created a data import strategy to minimize work in getting legacy data or new inventory into eQuip! or Visual Asset Manager. Once the site has been entered into Visual Asset Manager or eQuip!, the actions of creating and editing a data import spreadsheet has been greatly simplified. It is NOT necessary to first import locations, and then import people, before you import your asset data. We have created a template approach that lets you enter in a basic subset of information, and then as the need arises, you can export data into excel sheets to do bulk data updating.



As you can see, the columns listed in Red are those that Visual Asset Manager and eQuip! require. All other fields are optional (unless specified when adding fields, or modifying import rules by mandating other fields.

The data import rules work to make your job of importing legacy information easy. If the Asset Category does not exist in Visual Asset Manager or eQuip!, it is added on the fly. If a location name and location barcode combination does not exist in the database, the importer will add this location to the set of locations. Similarly, if a vendor, manufacturer, Asset Condition Value, Asset Usage Value does not exist in the condition, usage, or vendor table, those values are added. It is NOT necessary to prepopulate those tables during setup. Additionally, linking assets together has never been easier. Items that are tied together are tied by a linked barcode. In this use/case, it is necessary to create a barcode in the parent asset (the asset that will be used to link to other items), and then to specify that barcode in the

LinkedAssetBarcode field. The result of the data import is an entry in the parent (and linked) asset to

show the assets linked to each other, and allow them to grouped together in a variety of ways. People who are listed in the import table are looked up, and if not present in the people table, are added on the fly and linked to the asset where their name is listed.

Adding data on the fly makes it easy to get data into the system, but there is a dual-edge sword and it is easy to add data that is misspelled or contains typos. We have built in support for using formulas and lookups used in excel to lookup a site name, and based on the site name, select the location name of the locations at that site. Based on the selection of the location name, a location barcode can be

automatically entered into the spreadsheet. A judicious use of lookups an formulas reduces data entry for each asset to selection of a category (which adds a root category, parent category and asset category name, and in some cases can add an asset name and asset description from a larger lookup table. With our Emergency Management Framework, which uses FEMA’s Authorized Equipment List (AEL) as a category system, looking up the AEL code provides 6 required fields just by entering one code. Looking



up a site, and then looking up the location, enters 3 fields when selecting items from a dropdown list on the excel list. With judicial use of dropdowns and lookups, data entry can be significantly reduced to as much as 10 fields per asset.

Lookup table for category Lookup table for site and location.

Data Validation List Lookup table creation

Formula looking up data from data rows and lookup table specified on another page in the worksheet

The Import Wizard can read data from multiple worksheets when specified, provided that the work sheet has identical columns. You can specify the worksheets that have data during the download and read of the spreadsheet. You can separate legacy data on worksheets from different sites, different funding and different zones of responsibility.

Data Entry Lookup Rules

eQuip! and Visual Asset Manager’s data importer have a number of import rules, and

understanding how these rules work, can be used to create excel sheets used as part of the asset lifecycle during asset ordering, receiving, and asset disposal.


8 Data Import Rules

Field Look up Rule Required Fields Comments

Required Fields

Location Location Added when location name and location barcode is specified

Location Name Location Barcode

If location barcode only is in import data, and location does not exist, the row is rejected

People Person Added when Person Name is used

Person Name

Asset Category Root Category specified and Parent Category specified will locate the asset

category in the hierarchy of the parent. Asset Category can be specified as a Parent Category to add additional levels.

Asset Category The Category lookup uses the parent or root category to constrain the search to

determine if the category specified in the row exists. If the root category is specified in the row, the lookup searches for the root first, and then will add the category if not found as a child of the root category. When parent category is also specified, the root category is looked up first, and then the parent is looked up and the asset category is added as a child of the parent if not found.

Zone Zone Name If the zone exists, the asset row is linked to the zone.

Plan Plan Name If the plan name exists, the row is linked to the floor plan.

If the plan does not exist, the row is rejected. Dropdown Field Value must exist in the

set of dropdown values

Value in set of dropdown values

If value does not exist in the list the row is rejected

Value List Field Value is compared if exists, and added if does not exist

Value list is added to list. Common use is to add to vendor or manufacturer list.



Using the Importer to Update Asset Data

The data import wizard not only is used to insert new assets, but can be used to bulk update assets listed in an excel sheet. The entire list of assets can be generated from the data import wizard:

By creating a template of assets already imported, bulk changes can be accomplished by changing the field values or adding additional data when needed such as maintenance and warrantee dates and information. However data in excel templates can be generated by using the search screens. Here you can narrow down a search for items that are needing bulk updating by searching, and then exporting the results into excel. This data is exported with all required ID’s to allow data updating when imported back into the import wizard as an update job.

Data exported directly from the search screens can be edited and resubmitted to the data importer. One of the key uses of the search screen is to search for and then export the results to excel. The exported



results can be submitted back to the import wizard as bulk changes. Working within excel is comforting to most equipment managers, and this updating can be a logical part of a workflow.

When submitting the spreadsheet to the importer, immediately after downloading you need to select the worksheet that contains your input data. In the example below, the screen shows not only the worksheet tabs that contain data (labeled “Asset Template”) but the sheets that contain the lookup data used to support the template. It is not necessary to select the lookup worksheets

After selecting the worksheet (or worksheets that you wish to import using the multiple select feature of the worksheet list) you can preview the worksheet by selecting the dropdown box which will contain all the worksheets you have selected for importing. You can view each sample sheet by selecting the worksheet name on the dropdown:



Moving to the next screen in the import wizard takes you to the screen that lets you map the spreadsheet to the data fields. If you are using a generated template from the import wizard, or generated spreadsheet from the search screens, most of the data fields will already be mapped. When you add your custom fields, you may map those fields to data in the asset table. If you have standardized on your template, Visual Asset Manager and eQuip! remembers the template mapping and reuses the template map for each template:

Once fields are mapped, you can export this mapping so that you can create automation from other software that creates spreadsheets. In the Visual Asset Manager installation, the importer folder can be mapped to an FTP folder, and the spreadsheets along with their map can be copied to that folder via FTP, and the importer will directly copy them into VAM. Right click your mouse on the data fields at the left part of the screen, to view a data dictionary entry indicating the name of the field, the data type and size of the data, and the tab and display name for the data element. If you need to write the same field to multiple data elements, simply specify the field name for the spreadsheet again.

One of the key strengths of the import wizard is to read excel sheets with calculated formulas and to import the values based on the calculation into the data of the asset. This is very useful for importing pre-calculated book values, financial values, leasing residuals, and other financial information.

Visual Asset Manager and eQuip! is tightly integrated with excel spreadsheets. The key cost savings for the use of eQuip! and Visual Asset Manager is that data going into the asset system is entered in one place, at one time, for the life of that data.




Organizing the Enterprise Asset Management system using eQuip or Visual Asset Manager has never been easier. After creating your site delineation, the remainder of the data can be imported from only one spreadsheet. Keeping track of data, doing mass updates of that data, and making data entry a logical part of the asset acquisition process keeps the asset system up-to-date.


About E-ISG Asset Intelligence – E-ISG Asset Intelligence provides cost effective software solutions to manage equipment for emergency management agencies. In addition to the standard features for managing the lifecycle fiscal and physical information of equipment, it also features specifically designed for IT managers. It organizes equipment based on an ITIL framework. To Sign up for a free version of eQuip! or to get pricing on our subscription click here to register.


Related documents

Strategi komunikasi dalam kaitan dengan partisipasi perempuan dan keterwakilan mereka di lembaga legislatif bisa dikelompokkan menjadi dua, yaitu strategi komunikasi politik

Berdasarkan seri percobaan yang dilakukan terungkap bahwa bahan aktif crude hidrokuinon bersifat antibakterial dan immunostimulan yang meningkatkan nilai parameter

Power Supplies for constant current HI-POWER LEDs 350/700mA Alimentatori per LED in corrente costante HI-POWER

Through interviews with faculty, staff, teachers, students, and parents of children at Fairway Elementary School a model was developed through the lens of Maslow’s Hierarchy

After upgrading to MAPS service 4.5.x, in cases where certain tables in the MAPS database contained missing or invalid dates, the eLauncher would either hang without loading

One substitute teacher lists the agenda prepared by the teacher and tells the class, “If there is time after we get the required work done, I have brought some fun activities

Agent of Supervising Physician “A physician assistant is considered the agent of the supervising physician with regard to all duties delegated to the physician assistant and

accomplished these activities will lead to the following changes in 7-10 years Tangibles: •Funding Intangibles: •People (social capital) Assembling of the Business