Organizations have all kinds of assets they need to manage. Large assets, like fixtures, furniture, and equipment (FFE) are used by everyone in the organization. This could be kitchen appliances, chairs, desks, shelving, bookcases, and more. Managing all FFE is important for the organization so that they can easily planning the replacements or reparations and make a good estimation of the expected costs. This template allows you to track the assets from several angles. This can be by category, subcategory , staff member, or an entire department. Regardless of what you choose, you’re able to easily determine the total costs for the assets along with their salvage value.

Assets

This table holds all the information about assets and contains formulas to automatically calculate the depreciation costs and salvage values. This table is connected to all of the other tables in the database.

Fields

• ID. The name of the asset followed by the last 4 digits of Barcode to ensure that it is unique.
• Name. The name of the asset.
• Barcode. A unique number for each asset in the table.
• QR Code. A link to api.qrserver.com. It generates a 100x100 QR code for the asset based on the Barcode.
• Invoice. This file field sql.sqlallows to upload the invoice for the asset in JPG or PDF format.
• Subcategory. This linked field links to the Subcategories table. Each asset is assigned to exactly one subcategory. This allows to generate an overview of which categories an subcategories use the most expensive assets.
• Category. This lookup field shows the name of the category that belong to the Subcategory field.
• Purchase date. The date that the asset was purchased.
• Purchase cost in dollars . The original price of the asset.
• Depreciation period in months. The amount of months that you expect that the asset can be used before it needs to be replaced.
• Replacement date. This formula field calculates the date when the asset is fully deprecated. It is calculated based on the Purchase date and Depreciation period in months.
• Montly depreciation. This formula field calculates the monthly depreciation for an asset based on the Purchase cost and Depreciation period in months.
• Salvage value. This formula field calculates the salvage value for an asset based on the current date, the Replacement date, the Purchase cost in dollars and Purchase date. If an asset is fully deprecated, the value of this field will be set to zero instead of a negative number.
• Is depreciated. This formula field shows a checkbox when the replacement date has been exceeded and the asset has become fully deprecated.
• Condition. This single select field indicates the current condition of the asset. This field can be set independent from any salvage value or replacement date. It is an indication that an asset should be replaced sooner than expected.
• Use level. This single select field shows at what level the asset is used. Some assets are used by the entire organisation, others by a certain department or by a single staff member.
• Used by staff. This linked field to the Staff table shows which staff member is responsible for the asset. This field only has a value if the Use level is set to Staff member.
• Used by department. This linked field to the Departments table shows which departement is responsible for the asset. This field only has a value if the Use level is set to Department.
• Can be replaced by. This linked field to the Replacements table shows the alternative assets that can replace the current asset.
• Price difference of replacement. This lookup field shows the difference in price between the purchase price of the current asset and the purchase price of the alternative.

Views

• All assets. Displays all assets sorted in chronological order of Purchase date.
• Assets to be replaced this year. Displays all assets with a Replacement date in the current year or a Condition that is set to poor.
• Assets to be replaced this month. Displays all assets with a Replacement date in the current month or a Condition that is set to poor.
• Assets with a small depreciation period (<1 year). Displays all assets with a Depreciation period in months that is lower than 12 months.
• High-cost assets ( > $1000). Displays all assets with a purchase price higher than $1000.
• Low-cost assets ( < $100). Displays all assets with a purchase price lower than $100.
• Assets without invoice. Displays all assets that do not have an invoice or any other document.
• Assets without barcode. Displays all assets that do not have a barcode.
• By use level. Displays all assets in a kanban view, stacked by use level.
• Add new asset. Displays a form that can be used to add new assets. You only need to offer the basic information. The formula fields are calculated automatically just as you would enter a new asset directly to the table.

Categories

All assets belong to a single category. These categories are stored in a separate table instead of a single select field so that you can see the total asset costs and salvage values for each category.

Fields

• Name. The name of the category.
• Description. A short description of the category so that it is clear what type of assets belong to the category.
• List of subcategories. This linked field shows all the Subcategories of the category.
• Asset costs by subcategory. This lookup field shows the total asset costs for each subcategory. This field is necessary to calculate the Total asset cost by category and can be hidden if desired.
• Salvage value by subcategory. This lookup field shows the total salvage value for each subcategory. This field is necessary to calculate the Total salvage value by category and can be hidden if desired.
• Total asset cost by category. This formula field calculates the total asset cost for the category. This indicates which categories are the most expensive.
• Total salvage value by category. This formula field calculates the total salvage for the category.

Views

• All categories. Displays all categories sorted in alphabetical order.
• High-cost categories (> $5000). Displays the categories that has a total asset cost of more than $5000.
• Low-cost categories (< $2000). Displays the categories that has a total asset cost of less than $2000.

Subcategories

Each category is divided into multiple subcategories. A subcategory is always attached to exactly one category. Just like with the Categories table, it has an overview of the total asset costs and salvage value for each subcategory.

Fields

• Name. The name of the subcategory
• Description. A short description of the subcategory so that it is clear what type of assets belong to the subcategory.
• Category. This linked field connects the subcategory with one record from the Categories table.
• Category assets. This linked field contains a list of all the Assets that belong to the subcategory. This field is used to calculate the fields Total asset cost by subcategory and Total salvage value by subcategory with a formula.
• Total assets cost by subcategory. This formula field calculates the total cost for all assets in a subcategory
• Total salvage value by subcategory. This formula field calculates the total salvage value for all assets in a subcategory.

Views

• All subcategories. Displays all subcategories sorted in alphabetical order.

Staff

This table offers basic information about each staff member together with the assets they are responsible for. The table has fields that calculate the total assets cost for a staff member and the total salvage value. This allows to see a detailed overview, just like with the Categories and Subcategories table.

Fields

• Name. The name of the staff member.
• Picture. This file field contains a square profile image of the staff member.
• Phone. The phone number of the staff member.
• Email. The e-mail address of the staff member.
• Job role. The job title of the staff member.
• Staff member department. This linked field connects the staff member to a single Department.
• Staff assets. This linked field shows all the Assets that are assigned to the staff member. This field is used to calculate the Total asset cost by staff member and the Total salvage value by staff member.
• Total asset cost by staff member. This formula field calculates the total cost for all assets of a staff member.
• Total salvage value by staff member. This formula field calculates the total salvage value for all assets of a staff member.

Views

• All staff members. Displays all staff member sorted in descending order of Total asset cost by staff member.
• Gallery: All staff members. Displays all staff member with their Picture in descending order of Total asset cost by staff member.

Departments

Each staff member belongs to a single department. The Assets table indicates if an asset is used by a single staff member, the entire organization or a single department. This table shows the total cost and salvage value for assets that are assigned to a department.

Fields

• Name. The name of the department.
• Staff members. This linked field lists all the Staff members of the department.
• Number of staff member. This formula field count the amount of staff members in a department. This is used to calculate the Average asset cost by staff member.
• Department assets. This linked field lists all the Assets that are assigned to a single department. Assets that are assigned to a single staff member are not mentioned here.
• Total assets costs by departement. This formula field calculates the total cost for all assets of a department.
• Total salvage value by department. This formula field calculates the total salvage value for all assets of a department.

Views

• All departments. Displays all departments sorted in descending order of Average asset cost by staff member.
• Large departments (> 5 staff members). Displays all departments with 5 staff members or more.
• Small departments (< 3 staff members). Displays all departments with 3 staff members or less.

Replacements

This table offers alternatives to replace current assets. There can be multiple alternatives to replace an asset and there is a formula that calculates the difference between the purchase price of the current asses and the price of the new asset.

Fields

• Name. The name of the alternative asset.
• Replaces asset. This linked field contains one or more Assets that can be replaced by the alternative.
• Shop. This url field contains a link to a webshop where the item can be purchased.
• Price. The price of the alternative asset.
• Status. A single select field indicating the current status of the potential replacement.
• Price difference. This formula field calculates the price difference between the purchase price of the original asset and the price of the alternative asset.
• Total price difference. This formula is the sum of the field Price difference. If an alternative can replace multiple assets, it will make the sum of all individual price differences. Otherwise, it contains the same value as Price difference.
• Reasoning. A short motivation why the alternative would be a good option.

Views

• All replacements. Displays all alternative assets to replace the current ones sorted descending by the purchase Price.
• Cheaper replacements. Displays the alternative assets that are cheaper than the current ones sorted descending on the Total price difference.
• More expensive replacements. Displays the alternative assets that are more expensive than the current ones sorted descending on the Total price difference.
• By status. Displays all alternative assets in a kanban view stacked by status.