Asset Tracking Templates: Free Spreadsheets, Register Columns and an Audit Checklist

Free asset tracking templates to download

The first three templates are files that download straight away, with no form and no signup. The last three are column lists and steps further down this page.

The sign out sheet, the crib log and the column lists below share one column, asset_id. Give each item a short permanent code, such as DRL-014, and never reuse it. A sign out row, a crib loan and an audit line then all point at the same item.

How to set up an asset tracking template in Excel or Google Sheets

Import the CSV instead of double-clicking it, then add dropdowns for the columns that take fixed words. An asset tracking spreadsheet set up this way keeps every serial intact, and its filters count correctly.

• Import, do not double-click. In Excel, use Data, then From Text/CSV, and choose Transform Data. Remove the # rows at the top, then use the first row as headers. Set every ID and serial column to Text, or Excel strips leading zeros and turns long numbers into scientific notation (Microsoft: keeping leading zeros). In Google Sheets, use File, then Import, untick the option that converts text to numbers and dates, then delete the # rows.
• Add dropdowns for fixed words. Condition on the sign out sheet takes four words: new, good, worn and damaged. Status in the register takes five: in_service, in_repair, lost, sold and retired. In Excel, use Data, then Data Validation, and allow a List (Microsoft: create a drop-down list). In Google Sheets, use Insert, then Dropdown (Google: create an in-cell dropdown list).
• Keep the header row, and freeze it. Other software can then match the columns by name. The View menu in Excel and Google Sheets freezes the top row, so it stays in view. A filter, also under Data, lets anyone sort and count by column.

Dropdowns keep one spelling per word. A filter for "damaged" misses a row where someone typed "damged".

Asset register template: the columns to start with

An asset register template starts with asset_id, description, serial, purchase date and cost, home location and status. It holds one row per item you own and changes slowly. The sign out sheet and the crib log record what happens each day.

The full list is below. The purchase, depreciation and disposal columns follow the IRS list of what asset records should show (IRS Publication 583).

• asset_id. Your own short permanent code, such as DRL-014. Every other template uses it.
• description. What people call the item out loud, such as "hammer drill".
• category. Your own groups, such as power tools or site lighting, so you can filter and count.
• make_model and serial. After a theft, police and insurers need the serial. Format serial as Text.
• purchase_date, cost and improvements. When you acquired the item, what you paid, and what later improvements cost.
• depreciation_to_date. The deductions taken so far, including any Section 179 deduction.
• home_location. The yard, crib, truck or room the item belongs to. An audit counts one home_location at a time.
• assigned_to. A person or crew, for items that stay with one owner, such as a laptop or a meter.
• status. One of in_service, in_repair, lost, sold or retired, picked from a dropdown.
• disposal_date, disposal_method, sale_price and sale_costs. When and how the item left, what it sold for, and what the sale cost.
• calibration_due. For gauges and test equipment that must be checked on a schedule.
• last_audited. The date of the last count that found it. An old date shows where to look next.
• notes. Warranty details or anything else, last.

Never delete a row when an item is sold or lost. The IRS says to keep property records until the period of limitations expires for the year you dispose of the item. A closed row with a disposal date keeps that record.

Serials matter after a theft too. The National Equipment Register counts inaccurate or missing ownership records among the causes of low recovery. It advises keeping a list of equipment serial numbers to give police and insurers (NER and NICB theft report, 2016).

Equipment inventory template: count and grant columns

An equipment inventory template adds count columns to the register: counted_on, counted_by, location_found, condition and use. Equipment bought with a federal award also needs funding_source, title_holder and federal_share (2 CFR 200.313).

That rule lists what the property records of grant-funded equipment must hold. Most of it is already in the register: a description, a serial or other ID, the acquisition date, the cost and disposal data. The rest is the funding source with the award number (FAIN), the title holder, the federal share, and the location, use and condition.

• counted_on and counted_by. The date of the count and who did it. The latest date also fills last_audited in the register.
• location_found. Where the count found the item. A gap from home_location shows an item that has drifted.
• condition. The condition at the count, picked from the same four words as the sign out sheet.
• use. The job, project or program the item serves right now.
• funding_source, title_holder and federal_share. For equipment bought with a federal award. Put the award number (FAIN) in funding_source, and the federal share of the cost as a percentage.

You can keep these as extra columns on the register, or as a count sheet per area that you copy back. Either works if every row carries the asset_id.

Who has each item: the sign out sheet and the crib log

The equipment sign out sheet and the tool crib checkout log say who has each item now. The register only says what you own.

The sign out sheet has eleven columns, from asset_id and taken_by to due_back and condition_in. It suits a shared room, truck or kit shelf. The equipment sign out sheet guide explains each column and a weekly returns check.

The crib log has one row per movement across a counter, and no row is edited once written. A return is its own row that names the loan it closes. The tool crib checkout system guide explains its fourteen fields and the reports they feed.

How to audit assets against the register

Count one area at a time against the register, on a cycle you can keep. For equipment bought with a federal award, 2 CFR 200.313 sets a floor. It asks for a physical count, reconciled with the property records, at least once every two years.

1. Filter the register to one home_location, sorted by asset_id. Print it, or open it on a phone.
2. Walk the area and tick each item you find. Pick its condition from the same four words as the sign out sheet.
3. Write down anything you find that is not on the list. Add it to the register with a new asset_id.
4. For each item you did not find, check the sign out sheet and the crib log for an open row. An open row means it is out, not lost.
5. For the rest, look up the last reported location of any item that carries a tracking tag. Search there first.
6. Mark anything still missing as lost, rather than deleting the row. Set last_audited on every item you found.

Keep a list of the items each audit could not find. If the same ones appear twice, count their areas more often and tag those items first.

Keep the spreadsheet, and add where each item is now

Every template records what someone wrote down. A tag on the item records where it is now. The two answer different questions, so keep both.

A crowdsourced tag is small and needs no wiring or data plan. Any phone that passes reports where it heard the tag. These networks are large (Apple counts over a billion iPhone, iPad and Mac devices, and Google over a billion Android devices). Its battery lasts up to six years, depending on its size, so it stays on the item between audits.

Name each tag with the item's asset_id, and the tag lines up with your asset tracking spreadsheet on one column. An open row, a missing audit line and the tag's last report then sit side by side. The data export and API guide shows the export.

Geofence import template (CSV and Excel)

A geofence is a boundary drawn around a site. When a tagged item crosses it, the crossing is recorded and an alert can be sent. The geofence template sets up many sites at once, one row per site. The Excel version has the same columns, with an Instructions sheet beside them.

Only fence_name, tags, latitude and longitude need a value. The tags column takes tag names separated by semicolons, or the word all. Tags named by asset_id make it quick to fill. Other columns set the fence size, the alert emails, the map color and a reminder for an item that stays outside.

When you finish in Excel, save the Geofences sheet as a CSV file and import that file. The geofence guide shows how to size a fence for a yard or a jobsite.

What to expect from tags next to your templates

Next to your templates, a tag adds a dated location for each item. Any phone that passes reports it, and that shapes how often it updates.

• Updates come from passing phones. A busy shop or a staffed site updates all day. A quiet yard at night updates less often. Each report carries a time, so read it before you drive out.
• Building-level position. Expect the right site, yard or building, not the right shelf. The asset_id on the item does the rest.
• Where, not who. A tag shows where an item is. The sign out sheet and the crib log show who took it and in what condition. Keep them as the custody record.