Equipment Log Spreadsheet

SYSTEM OVERVIEW & PURPOSE

1. Purpose

The Equipment Log Spreadsheet is a production-grade operational framework designed to track asset lifecycles, physical locations, financial valuation, maintenance schedules, and operational status across an enterprise fleet. It functions as a single source of truth for Operations, Finance, and Facility Management teams.

2. Scope

This framework covers physical capital equipment, rolling stock, IT infrastructure assets, heavy machinery, and specialized tools. It spans acquisition through disposal, capturing depreciation, downtime events, and cumulative maintenance expenditure.

3. Data Update Cadence

• Real-time / Event-driven: Status updates (e.g., operational to maintenance), location transfers, and fault logs must be logged immediately upon occurrence.
• Weekly: Maintenance flag audits and work order status reconciliation.
• Monthly: Meter readings (operating hours/odometer updates), depreciation runs, and asset health scoring.
• Annually: Physical inventory audits and asset valuation adjustments.

---

COLUMN DEFINITIONS & VALIDATION RULES TABLE

---

COMPLETE MASTER DATA TABLE

---

FORMULAS & CALCULATION LOGIC

1. Straight-Line Current Book Value

Calculates the depreciated value of the asset based on elapsed time since acquisition, floored at the salvage value. =MAX(Salvage_Value, Purchase_Price - ((Purchase_Price - Salvage_Value) / Useful_Life_Yrs) * (YEAR(TODAY()) - YEAR(Acquisition_Date)))

2. Maintenance Overdue Status Flag

Returns an alert string if the next service date has passed and the asset is not decommissioned. =IF(AND(Next_Service_Due < TODAY(), Current_Status <> "Decommissioned"), "OVERDUE", "Current")

3. Total Fleet Replacement Value

Sums the initial acquisition cost of all active assets in the master log. =SUMIFS(Purchase_Price_Column, Current_Status_Column, "<>Decommissioned")

4. Average Fleet Health Score

Computes the mean health percentage across all operational assets. =AVERAGEIF(Current_Status_Column, "Operational", Health_Score_Column)

5. Dynamic Asset Lookup

Retrieves the current physical location of a specific asset ID. =XLOOKUP(Target_Asset_ID, Asset_ID_Column, Site_Location_Column, "Asset Not Found")

---

SUMMARY KPI DASHBOARD

1. Executive Metrics Block

• Total Active Fleet Valuation: $721,558.34 (Sum of Current Book Value for non-decommissioned assets)
• Active Asset Count: 11 (Total assets minus decommissioned)
• Fleet Operational Availability: 75.0% (Operational assets divided by total active assets)
• Pending Maintenance Items: 2 (Assets with Next_Service_Due < TODAY() or Current_Status = "Under Maintenance")

2. Health Status Flags & Conditional Formatting Rules

• Critical / Out of Service (Red Fill #F8D7DA): Applied when Current_Status = "Out of Service" or Health_Score < 50%.
• Warning / Service Due (Yellow Fill #FFF3CD): Applied when Current_Status = "Under Maintenance" or Next_Service_Due is within 30 days of TODAY().
• Optimal / Operational (Green Fill #D4EDDA): Applied when Current_Status = "Operational" and Health_Score >= 80%.

---

STANDARD OPERATING WORKFLOW

Step 1: Onboarding New Equipment

1. Navigate to the next blank row in the Master Data Table.
2. Generate a unique Asset_ID following the format convention (EQ-[CategoryCode]-[SequentialNumber]).
3. Enter mandatory asset specifications (Asset_Name, Category, Model_Number, Serial_Number, Acquisition_Date, Purchase_Price, Useful_Life_Yrs, Salvage_Value).
4. Set initial Current_Status to Operational and assign the responsible custodian.

Step 2: Logging Operational Updates & Meter Readings

1. Update Operating_Hours at the close of every operational month.
2. If an asset undergoes repair, immediately toggle Current_Status to Under Maintenance and log the date in Last_Service_Date.
3. Upon service completion, update Next_Service_Due based on operational thresholds (e.g., +6 months or +500 operating hours).

Step 3: Performing Periodic Audits

1. Review the Summary KPI Dashboard weekly to identify overdue maintenance flags (OVERDUE).
2. Run monthly depreciation checks to verify that Current_Value calculations match financial ledger accounting.
3. For assets reaching the end of their Useful_Life_Yrs, evaluate physical condition and transition Current_Status to Pending Disposal or Decommissioned as required.