Sheets List

Progress Tracking Checklist using Google Sheets

Setting up the checklist for tracking progress in Google Sheets for Power Plant Installation company.

❓ Unmet needs

Every power plant installation process involves a series of steps starting with the Acquisition of Land. The checklist of items for a Land Acquisition process is tracked on paper and has to be manually updated into the system.

Many a time the manual updates are done post the completion of the process and not during the process itself. This makes it hard to track the progress of the project and the responsibility falls upon the field engineers to keep their higher ups informed.

🏹 Objectives

The main objective that was to be solved for Kshema Power was a method to track the status of Land acquisition and to capture data at various steps.

  • Go/No Go: Preliminary stage including site survey
  • Land financials: Securing access to the land with proper documents and permissions
  • Land conversions: Tracking transfer of the land with approvals
  • HOTO: Handover Takeover

⛔ Constraints

For finalizing the solution, a number of the following points had to be kept in mind:

  • Employees were used to entering data in excel sheet.
  • Reports were to be generated and easily accessible for tracking progress and making decisions for next steps.
  • Access to the data entry and monitoring was to be restricted by user roles.
  • An existing ERP solution was already in place and this data should be made accessible to the same.
  • Ease of use of the solution.

🗺 Solution

As we explored solutions for the system, we kept in mind user behavior and their familiarity to make use of tools such as Microsoft Excel. To account for role based multi-user access without data redundancy that comes with offline excel sheets, we considered a cloud based solution with Google Suite of tools, particularly Google Sheets.

We setup the following 3 sheets in Google sheets:

  1. Format: The Format sheet was the main sheet that held the checklist for Land Acquisition. This sheet is the one that is to be duplicated for updating statuses of tasks across stages for each Land location.

    1. It held the following data of for a location:

      1. State
      2. Client Code
      3. Location No.
      4. Location Id
    2. For the checklist, status and documents were collected across the following stages:

      1. Go/No Go
      2. Land financials
      3. Land conversions
      4. HOTO

    Each task of the checklist has a score assigned to it. The score added up to 100% to track progress of each stage. The progress was represented by a bar chart.

  2. Table: The Table sheet held the master tables of the tasks and the scores associated for each task. Once the status for the tasks are updated for each Land sheet, the score will be automatically updated from the values set in this sheet.

    Sheets Master Table - Coffee

  3. Index: Finally, the Index sheet is a generated sheet that holds an overall summary dashboard of the progress across all land locations. It is generated using an App Script and lists the following information for each location.

    1. Client Code
    2. Sheet Name
    3. Sheet Link
    4. Go/No Go
    5. Land financials
    6. Land conversions
    7. Handover/Takeover (HOTO)
    8. Overall progress
    9. Progress chart: The overall progress for each section will be updated in the index sheet in a consolidated manner to view the overall progress of each land acquisition project

📈 Impact

  • The organization now had access to an overview of Land acquisition across projects.
  • Leveraged existing platform and user's familiarity to speed up adoption.
  • Bird’s Eye View: One place to track progress.
  • More than 100 locations and their statuses were tracked in a single Google Sheet.
  • Easier for stakeholders to take decisions.

© 2023 Coffee