Spreadsheet Sustainability  

Jim Meacham is one of those engineers who live and breathe spreadsheets. Need to know a building's annual predicted carbon emissions? Ask the spreadsheet. Need to know what happens to a building's water consumption if you switch out cooling towers with a geothermal field? Ask the spreadsheet.

Meacham is a mechanical engineer with Irvine, California-based CTG Energetics, and director of the firm's advanced energy services. He uses spreadsheets almost every day, mostly to support what CTG calls its Sustainable Communities Model, which the firm has developed over years of project work in response to increasingly complex master-planning developments that must balance multiple environmental inputs and outputs as part of a more sophisticated approach to sustainable design.

"There was no tool to analyze green-house gas emissions in an integrated way, where you could look at the tradeoffs between the different design decisions," Meacham says. "It's constantly evolving, since every year we have to update the spreadsheets with the latest emissions factors and industry data."

Ask many engineers and consultants who work like Meacham and they will tell you the same story. The dirty secret of so much sustainable design is that it's not based on fancy three-dimensional building- information modeling of complex inputs and outputs, dynamic parametric analyses, or instant-answer results from super-computers-most of it boils down to someone working out the details of energy, water, transport, and waste systems in an increasingly complicated and proprietary Microsoft Excel spreadsheet. The "what-if" scenarios that are integral to sustainable design are often just cells filled in on an endless parade of tabs and links.

Click on image to see more details

Illustration by Julia Hoffmann

This is not to say that the many available and developing software products aren't comprehensive or helpful, but they are often meant to be used at the concept stage of design when details that affect performance and, ultimately, liability for delivering a building aren't as important. Think of Autodesk's Green Building Studio, targeted to early design. Much of the development in complex modeling of sustainability systems-what many call "multi-criteria" modeling or integrated-resource management-has occurred within design firms, like CTG, or in academia.

Spreadsheets and open-source algorithmic software like Grasshopper or proprietary component-based platforms like Simulink/Matlab have allowed individual designers and consultants, driven by project requirements and even personal interests, to invent these new approaches as a way forward for increasingly complicated sustainable projects. While the roots of sustainable design analysis will likely always be in spreadsheets, complex building modeling often occurs in multiple software programs. In turn, these programs are giving way to a glimpse of a future increasingly focused on combining modeling into a single software platform-not a soup, but more of a salad approach. Knowing which approach to apply depends largely on the questions you're asking.

A Spreadsheet Approach

CTG engineers have applied their Sustainable Communities Model to a diverse range of projects, including new city plans and even the design of a new U.S. embassy in Juba, Sudan. The embassy, designed by Einhorn Yaffee Prescott and currently in construction, is targeting LEED certification and illustrates the potential for multi-criteria modeling. Before modeling begins, the first step in most projects is gathering data: annual climate files, square footage of the building organized by space type, acres of landscape, parking spaces, transport trip forecasts, census data, energy- performance data for given building types, and anything else available. "The bulk of the work is getting good data," Meacham says.

For a domestic project, CTG can often rely on historical data provided by local planning departments, filling in gaps with assumptions or information from readily available national and state databases such as the Commercial Buildings Energy Consumption Survey (CBECS) or the California Commercial End-Use Survey (CEUS). They did not have this breadth of information available for their site in Sudan, so they used assumptions and experience from similar projects. All of this information feeds baseline assumptions, or a business-as-usual case from which to measure improvements.

CTG's engineers can then start at the community or building scale to balance sometimes-competing issues like energy and water consumption, greenhouse gas emissions, air pollution, water quality, transport and parking, landscape design, and renewable energy systems. The results are reported in environmental and economic returns with a cost-benefit analysis, and usually illustrated with Sankey diagrams that show the supply and demand for each source. For the Sudanese embassy, Meacham and his colleagues found the approach especially useful when analyzing the self-sufficient water systems that included supply from an on-site water-processing plant and sewage-treatment systems for the embassy's multiple buildings.

Click on image to see more details

For the U.S. embassy in Sudan, CTG Energetics used Sankey diagrams comparing baseline design (3) to the optimized scenario (4), showing the combined energy and water savings. Images: Top, Teri Shikany; 3,4 courtesy CTG Energetics

"All water and waste water had to be processed on-site, so we used the model to capture all of the related interactions from reducing the scale of the waste stream of the water," says Meacham. "It not only reduces the scale and site impact of the waste treatment, but also the energy used to treat that water." Since all site energy was generated from imported oil transported on barges, there were both direct and indirect correlations between water and fuel consumption. "If we reduced the amount of water consumed on-site, we found that would reduce energy consumption by a factor of three, once we accounted for all of the processing and purification that went into providing the water," he says.

These kinds of synergies occur in sustainable design practices routinely, but they can take weeks or months of analysis to fully appreciate when calculated separately. Typically, site-water-balance calculations, transport analyses, and energy modeling are discrete exercises, often performed by different consultants. A combined multi-criteria model may not speed this up excessively, but it makes connections more apparent and flexible, enabling designers to consider multiple solutions to this balancing act without bogging down decisions. "It's a highly quantitative tool, but there are many kinds of qualitative factors that fall on top of it," says Meacham, adding that aesthetic considerations or market studies may overrule a life cycle assessment that concluded a specific system was best.

Meacham thinks flexibility explains why commercial software developers have yet to fully address this market, although he acknowledges there are products that assist in larger planning projects that take a more top-down approach rather than the bottom-up approach espoused by CTG. For Meacham, the benefits of spreadsheets not only include flexibility, where new databases and systems can be integrated into an existing model on the fly, but also data reliability, since equations can be readily tracked for accuracy.

Shifting Between Programs

But spreadsheets should not be confused with more complicated environmental simulation engines, such as the Department of Energy's stalwart, EnergyPlus, for calculating a building's annual energy consumption. CTG uses these programs, feeding them into their spreadsheet models. There are many such software engines available, depending on a project's focus. Engineers at Buro Happold's Los Angeles office combined four software packages as part of their multi-criteria analysis of the natural ventilation strategy for the Transbay Terminal project in San Francisco. Designed by Pelli Clarke Pelli Architects, the project features a 5.4-acre rooftop garden over 600,000 square feet of bus and rail platforms and is in construction until 2017.

To argue for extending skylights in the Transbay Terminal, Buro Happold used radiance daylight modeling (1, 2). Images: Top, courtesy Transbay Joint Powers Authority; 1, 2 courtesy Buro Happold

The goal for Alper Erten and his mechanical engineering colleagues at Buro Happold, was to convince the client and architect that an energy-efficient natural ventilation strategy would maintain acceptable comfort conditions for the project's Grand Hall. On the ground floor of the terminal, it features a glazed exterior wall and a large central skylight that penetrates the roof garden. Naturally ventilating this space, as opposed to providing a dedicated mechanical cooling and heating system, could significantly decrease the project's annual energy demand, but it relies on considering a complex interplay of environmental and programmatic concerns.

Happold’s engineers began by importing the architect's three-dimensional geometry model, which existed in a Rhino format, into the Autodesk Ecotect environmental software, which they then exported into Radiance, the industry's de facto daylighting engine. With Radiance, they could analyze the configuration of skylights and advise on appropriate glazing types for daylighting. They also developed a thermal model using Integrated Environmental Solutions Virtual Environment (IESVE), a proprietary software package in wide use in engineering. IESVE has a Radiance interface that allowed them to consider the thermal advantages of increased daylighting through the use of daylight-dimming systems. "Software like Ecotect is great if you have a simple, boxy office," says Orla Williams, one of Erten’s colleagues at Happold. "But with the complicated geometry of Transbay, we needed to be more accurate and have more confidence in our results." While software like Rhino has allowed architecture to become more complex and blobby, environmental modeling software remains geometrically challenged. Ecotect works well for simple projects or in early phases of design where its capability includes solar, daylighting, and overshadowing analysis. But a natural ventilation strategy depends on more than knowing internal and external heat gains. "We then used IESVE's MacroFlo model to simulate the buoyancy flows between inner zones by measuring temperature and pressure," Erten says. "We can also calculate the flows in external vents to understand differences between openings." They then exported the resultant surface temperatures from IESVE into a software called FloVENT, which is a computational fluid dynamic (CFD) analysis package.

Where MacroFlo is a bulk air flow analysis that demonstrates how air moves between spaces to show that natural ventilation is possible, FloVENT's CFD capability provides a more nuanced and rigorous analysis of the airflow rates, supply and extract locations, and how air interacts with other components in the space. CFD models can indicate air velocities and highlight dead spots where the age of air may exceed a target value, which can lead to uncomfortable conditions for occupants. Finally, once they generated hourly performance data in IESVE for the building's mechanical system, which included a large central plant with chillers, they could then export this data to another software called TRNSYS, short for Transient Energy System Simulation Tool. Overseen by a consortium of universities, led by the University of Wisconsin in Madison, TRNSYS is a component-based energy-modeling software that is particularly suited for simulating alternative equipment in more complicated central plant systems, but it's not that adept at modeling complex architectural geometries. Simulink/Matlab is another component-based proprietary software package used by engineers to model complex building-controls systems, which makes sense given the software's origins as a tool for automotive designers.

For Transbay, the engineers used TRNSYS to analyze the project's proposed geothermal heat pump system, using the IESVE data already embedded with the simulations of the architecture and mechanical system. "With the TRNSYS model, we were interested in comparing energy use and savings, as well as water use and savings in comparison to a cooling tower-based system," says Erten. "To balance heating and cooling, we also needed to look at ground temperatures over 10 years." With a geothermal heat pump, incorrect sizing could cause ground temperatures to reach unviable levels, leading to an inefficient mechanical system and the need for a costly retrofit. Buro Happold's engineers used the TRNSYS model to test parameters like borehole depths and separation sizes, too, finding that their results were approximate to the final design provided by the specialist geothermal consultant. Williams says such findings give them more confidence that their simulation processes are accurate for early design advice.

The Future of Real-Time Modeling

While the energy-systems planning of CTG and the building-systems optimization of Buro Happold represent examples of the upper end of the industry's capability in multi-criteria modeling, the online chatter in architectural modeling forums focuses on connecting these environmental performance processes with formal modeling in programs like Rhino, Autodesk's Revit visualization tool, and Bentley Systems's optimization software, Generative Components. The agenda is less about replacing deep systems interrogation by professionals and more about making the early design phase of a project more robustly attuned to environmental inputs when there is still time to change the architecture in significant ways. Andrew Payne, the founder of Lift Architects and more recently a doctoral student at the Harvard Graduate School of Design, has cowritten a program, Firefly, that links real-time controls information directly into a digital architecture model through the Grasshopper platform. You can download Firefly's beta version for free. In turn, Grasshopper can be used to program parametric models that can directly influence architectural form in Rhino. In theory, Firefly could allow you to take real-time wind data from an online weather station feed and then generate an unlimited number of corresponding facades; an optimization algorithm could parse through these alternatives to find the best fit given a set of predetermined constraints. Given 15-minute intervals between wind readings, you would end up with the 15-minute facade. If it sounds neat, Payne is inclined to agree.

"This is really a prototyping platform to get ideas out quickly," Payne says. He often taps into a website called Pachube (www.pachube.com), which links to any publicly reported sensor and illustrates them on a map of the world. You can find things like electricity consumption, temperature readings, and the output of a solar photovoltaic array, all reported in real time. Although consultants like CTG's Meacham rely on historical data from several years to minimize anomalies and establish reasonable averages, a real-time modeling approach could benefit projects or systems being designed to plug into an existing infrastructure. A simulation of proposed wind turbines could then be embedded in a digital model. The model incorporates the real-time energy consumption of an existing neighborhood to understand the fluctuations in the turbine's interface with the grid over a week of real-time modeling. Given enough computing power, you can see how this simple platform could easily scale up.

"This is not going to beat BIM at being BIM," says Payne. "But it is the first time we've embedded this real-time information into a CAD package." Since the Grasshopper beta version is open source (and free to download), there are several concurrent efforts to Firefly aimed at connecting environmental simulation and data into an algorithmic model to inform architecture. Christoph Reinhart, an associate professor at Harvard's GSD and Payne's doctoral advisor, is developing a program called Diva, which would integrate Radiance and Daysim-two daylighting programs-into Grasshopper to then manipulate a Rhino model. Efforts are also under way to integrate Autodesk's Ecotect program into Grasshopper, although Ecotect has already been integrated with the Generative Components algorithmic software. To the non-aficionado, this software interoperability arms race can quickly become confusing. And there is a tendency for the majority of users at this level of design to prefer open source software or those that remain in the public domain. "The power of Grasshopper lies in the fact people with no computer programming background can get up and running creating very complex things in a matter of hours," says Payne. "The promise is a new way of thinking about design."

What anyone who works in this field will tell you-grand public pronouncements aside-is that a fully integrated software package that takes into account energy, water, daylighting, waste, greenhouse gases, etc., is several years, if not a good decade, away. "There is a lot of time involved in going back and forth between software," says Buro Happold's Williams. And CTG's Meacham emphasizes the need to have tools that can be demonstrated in face-to-face project meetings. "With Excel, we can look at trade-offs in real time so the whole project team can see results and make meaningful decisions that can actually be implemented," he says. So, maybe it's safe to say, regardless of the software packages that eventually come to market, they will likely remain attuned to concept-level design, not the nuts and bolts of hardcore engineering. For that, the lowly spreadsheet stands alone.

Russell Fortmeyer is an engineer, sustainability consultant, and journalist based in Los Angeles.