A Macro-Based Solution for Addressing Structure Gap Reporting Challenges

SBAP-581829-GMFI-MP-002

February 2024

*****

Background

Currently, the Aircraft Data Group (TED) relies on a manually created Excel report to track the daily status of aircraft structure gaps. This report heavily depends on various Excel functions and formulas. However, this manual process presents several challenges:

Lack of Automation: The current process relies on manual intervention and increases the potential for errors in the reporting process.
Data Exclusion Challenges: As customer requirements evolve and are documented in a formal document such as Engineering Information, there's a growing need to exclude specific structure gap data from the report. The current manual process makes it difficult to accommodate these exclusions efficiently.
Inconsistent Plant Data: Inconsistencies during the removal order creation process often lead to errors in Plant data, resulting in incorrect unit in charge (UIC) assignments.

Proposed Solution

To address these challenges, I propose automating the report generation process using macros. By implementing macros within Excel, we can significantly streamline the following tasks:

Data Cleaning: Clean and standardize the data to ensure accuracy and consistency.
Data Filtering: Apply filters based on customer requirements to exclude specific data points (only formal and valid documents are acceptable).
Report Generation: Generate a customized report that accurately reflects the current status of structure gaps.
Data Visualization: Create visual representations of the data, such as charts and graphs, to facilitate analysis and decision-making.

By automating these tasks, we can expect the following benefits:

Increased Efficiency: Reduce the time and effort required to create daily reports.
Improved Accuracy: Minimize errors caused by manual data entry.
Enhanced Flexibility: Easily accommodate changes in reporting requirements and data sources.
Better Data Quality: Ensure that the data presented in the reports is reliable and consistent.

In essence, the proposed solution aims to create a more efficient, accurate, and flexible reporting process by leveraging the power of macros to automate the manual tasks involved in generating the daily structure gap status report.

Proposed Process Flow

1. Input Raw Data Structure Gap into Report

To update the PICA sheet with the latest data, copy the results from running the Python scripts zrccm2.py and pica.py. Paste these values (as values) into cell E3 of the PICA sheet, overwriting any existing data from row 3 onwards. Please ensure that the header rows (rows 1 and 2) remain unchanged.

2. Input Exclude Data Status (If Any)

To record any data that needs to be excluded from the report, enter the relevant details into the EXCLUDE sheet. Please classify each exclusion as either related to the aircraft (AC), equipment (EQUIP), or plant data correction (PLANT). Please also ensure that the exclusion is based on a valid and formal reference.

Explanation:

AC — to exclude all structure gaps within a specific registration.
EQUIP — to exclude structure gaps related to equipment that has been removed from a particular aircraft registration, based on a valid official reference (e.g. EI 10000210737/000/00 Deactivation/Removal unserviceable Galley Inserts on B737-800NG).
PLANT — to update the Plant PPC, Problem Identification, Corrective Action, and PIC data according to the correct data.

3. (Automated) Update Columns: No, Maintenance Status, Customer, and Date Running

By pressing Ctrl+Shift+G, you can trigger a macro that automatically fills in the necessary information for each structure gap, including a unique number, maintenance status, airline affiliation (Garuda Indonesia or Citilink), and the date the macro was run. This process will also ensure that the information marked for exclusion is combined and incorporated into the current day's data.

4. (Automated) Merge the Latest Data with Historical Data

To combine today's data with the existing historical record, simply press Ctrl+Shift+M. The macro automatically updates the table, ensuring that only the most recent seven days of structure gap data are retained.

5. (Automated) Visualize Merged Data into Graph

To visualize the updated data, press Ctrl+Shift+F. This will trigger the macro to refresh the "ALL A/C Online SGAP Open (Top 15)" and "ALL A/C SGAP Open (Top 15)" charts, providing you with the latest insights.

Explanation:

Chart1ON — a chart showing the total highest structure gap data from 15 PICs for all online aircraft.
Chart2ALL — a chart displaying the total highest structure gap data from 15 PICs for all aircraft registrations, both online and offline (maintenance).

6. Review Exclude Status Relevance

Press Ctrl+Shift+I to review the relevance of the excluded data. Any structure gap that has been closed will be marked in orange in column E for easy identification. This step is only applicable to the EQUIP and PLANT levels.

7. Structure Gap Report is Ready

The Structure Gap Report is ready to be distributed to the respective units in charge (UIC).

A Macro-Based Solution for Addressing Structure Gap Reporting Challenges