5 Principles for Excel-Based Emissions Tracking Systems
Last month, we looked at the use of databases to track air emissions data. Databases are great tools, but they are not the only tools. Emissions tracking can be complex, so no one needs additional burden and inefficiency when maintaining compliance. Below are 5 principles that ought to be considered prior to and during the process of creating or updating your facilities Excel-based emissions tracking system. Each principle is accompanied with a “do” and “don’t” that have been commonly observed by SEC during the process of creating and updating these kinds of emissions tracking systems.
#1. Consolidate / Centralize
DON’T maintain separate spreadsheets for different air emissions tracking & reporting activities.
Instead,
DO maintain a single “Master” air emissions tracking & reporting spreadsheet.
This is the most common mistake made in the application of Excel to emissions tracking systems. Excel users often do not take advantage of Excel’s ability to contain a significant amount of data while performing a variety of complex calculations and analyses. The utilization of multiple spreadsheets in the service of a calculating facility-wide emissions necessitates the manual transference of data from one spreadsheet to another. Manual transference of data commonly leads to mistakes and errors in the calculation of facility-wide emissions.
The solution to the common issues of a decentralized emissions tracking system is to consolidate the entire process into a single “Master” emissions tracking spreadsheet. The key to the implementation of this idea is to make sure that raw data, raw data processing, and final emissions results are contained within the same spreadsheet. This ensures that the manual transference of data is limited to the input of raw data into the “Master” spreadsheet. Another area of manual data transference often occurs during regular compliance demonstrations like reporting and recordkeeping. Logs and tables that are used to demonstrate compliance with reporting or recordkeeping requirements (like Semi-Annual Reports (SAR) or Annual Emissions Inventories) should also be included in this “Master” spreadsheet. These logs & tables are guaranteed to have accurate data when they directly reference the primary source for the facilities emission’s calculation. A common downside of consolidation is that a spreadsheet may become unruly and difficult to understand. The principles of “Simplifying” and “Automating” discussed below directly address this downside.
#2. Simplify / Transparentize
DON’T let the fundamental emissions calculation process become obscure and difficult to follow.
Instead,
DO create a space that clearly shows all raw data inputs, key assumptions, specifications, and emission factors used in the emissions calculation process.
After an emissions tracking process has been consolidated into a single spreadsheet, the process must also be made simple and transparent to avoid creating a process that is overwhelming and difficult to understand. This is the reason that SEC has standardized the use of “input decks” for all emissions tracking processes managed or created by the company. At the very least, an “input deck” is a single tab in the emissions tracking spreadsheet that houses all raw data input tables for the entire emissions tracking process. Additionally, the “input deck” may be expanded to include all key assumptions, specifications, and emissions factors used to calculate emissions for the facility. All other tabs within the spreadsheet that perform emissions calculations must always reference the “input deck” directly when raw data, key assumptions, specifications, or emissions factors are needed. If this is done correctly, then the “input deck” will also provide versatility to your emissions calculation. Consider a hypothetical situation in which you switch to a new filter supplier for your baghouses, the only necessary change to your emissions tracking spreadsheet will be to the specifications table for the baghouse filters located in the “input deck.” Without an “input deck,” you would need to search through the entire spreadsheet for individual references to the previous baghouse filters specifications and change individually. By reducing the amount of manual user input to the emissions tracking process, an “input deck” significantly reduces the chances of having incorrect information (old specifications, assumptions, emissions factors, etc.) carry forward into future calculations. In addition to versatility, the use of an “input deck” provides visibility as to how the emissions for a facility are being calculated. While the specific nuance of how a calculation is being performed should be limited to the notes of the specific calculatory tab, the question of whether the appropriate raw data, specifications, or emissions factors are being referenced by calculatory tabs may be easily answered when all the information is located on a single tab.
#3. Automate / Streamline
DON’T settle for manually performing repetitive tasks.
Instead,
DO automate the performance of repetitive tasks using Excel’s Visual Basic for Applications (VBA) coding language.
In 2010, Microsoft upgraded the legacy Visual Basic 6 programming language into what is known today as Visual Basic for Applications or VBA. VBA is a programming language that is built into most Microsoft Office applications. VBA allows users of Excel to “get behind the curtain” of the software and create functions that can personalize and streamline the use of any spreadsheet. For example, SEC has used VBA to create functions in emissions tracking spreadsheets that automatically generate regular reports (like Semiannual Reports or Annual Emissions Inventories) by clicking a button; expand or collapse different groups of tabs for better navigability of the spreadsheet; and automatically import, export, or manipulate data. Most of the VBA functions that SEC creates are initiated by buttons or a control panel on the “input deck” tab of the tracking spreadsheet. However, VBA functions may also run in the background of a spreadsheet and automatically initiate functions based on a variety of events like opening the spreadsheet or updating a particular cell. A more complex application of VBA may allow an Excel user to link their tracking spreadsheet to the source(s) of their raw data. Many facilities manage their data using an Enterprise Resource Planning (ERP) System. Examples of ERP commonly used in the manufacturing space include but are not limited to: Oracle, SAP, NetSuite, QAD, IFS, IQMS, and Acumatica. Excel users at facilities that utilize an ERP have a particular opportunity to automate their emissions tracking process by using VBA to establish communication/automatic data transfer from their facility’s ERP to their Excel-based emission tracking process.
#4. Visualize / Illustrate
DON’T restrict your historical and current year emissions data to a tabular format.
Instead,
DO generate useful graphics using historical and current year emissions data to visually demonstrate compliance and unlock insights for your facility.
Orderly and thorough tabular data is essential for demonstrating your facility’s compliance with its applicable permit limitations and regulatory thresholds; however, tabular data is not always as useful for internal discussions regarding the current state or trajectory of EHS compliance. One way to effectively communicate compliance in this manner is through simple, intuitive, graphical representations of the tabular data. To create such representations of a facility’s emissions data, first, the emissions tracking process must output the emissions data into simple tables that can be utilized for the creation of graphics. Secondly, the emissions tracking process must provide space for historical emissions data. The practice of keeping historical emissions data can be difficult when also applying the principles of “Consolidating” and “Automating” discussed above. Since the consolidation and subsequent automation of an emissions tracking process can increase the size of a spreadsheet significantly, Excel users must consider how to limit the space required for storing multiple years of historical emissions data. The size requirements of historical emissions data may eventually overwhelm an emissions tracking process if all of the data is being calculated by the spreadsheet in ever-expanding calculatory tabs and raw data input tables. One way to avoid this issue is to limit all calculatory tabs and raw data input tables to the size required for a single year. Then, at the end of each year, the data produced by the emissions tracking spreadsheet can be “hard input” into a historical data table within the spreadsheet that only contains the final values of emissions data from previous years. Since this approach does not require expanding calculatory tabs to maintain historical emissions data, the space requirements of the data become negligible. For recordkeeping purposes, best practice when utilizing this approach is to create a copy of the emissions tracking spreadsheet with linked calculations for all previous years. This process may be easily automated by using VBA to automatically save a copy of the complete spreadsheet for the current year, hard input current year emissions data into a historical emissions data table, and revise the emissions tracking spreadsheet such that it is ready to calculate a new year of emissions data.
#5. Harmonize / Normalize
DON’T allow your emissions tracking process to become disjointed from the governing air permit.
Instead,
DO normalize the structure & verbiage of your emissions tracking process to the standard of the governing air permit.
This principle is of fundamental importance to the tracking of emissions; where other principles may be considered to have an indirect impact on a facilities compliance demonstration, this principle directly impacts the quality of compliance demonstration. When creating or updating an emissions tracking process, it is imperative to begin first with the governing air permit. A facility’s air permit ought to set the following standards to be replicated by the emissions tracking process:
- Names of Emissions Sources, Emission Points, and Control Equipment
- Identification Numbers (ID) of Emissions Sources, Emission Points, and Control Equipment
- Emissions Limitations
- Required Information for an Appropriate Compliance Demonstration
- In some cases, specific types of calculations or table/log structures are required for an appropriate compliance demonstration.
An easily discernable through line should run from the air permit through the emissions tracking process and ultimately to the reporting & recordkeeping documentation maintained and submitted by the facility. When implemented correctly, this practice homogenizes the structure of, and verbiage used by the emissions tracking process with that of the air permit which leads to clear compliance with reporting and recordkeeping requirements.
Dylan Moore
dylan@stevensenvironmental.com