top of page

The Pros and Cons of Using a Spreadsheet to Create an Industrial Water Balance

Should I use a spreadsheet for my water balance?


Add a Title

Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Add a Title

Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.


Add a Title

Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.


Sustainability goals, water scarcity, and ESG drivers are putting pressure on you as an industrial water engineer to not only understand the dynamics of water systems, chemistries and waste on your site, but to be continually reducing your freshwater consumption through increased reclaim and reduction efforts; and this all needs to be accomplished while staying within the boundaries of your environmental permit. If you are like most industrial users, you have a clever spreadsheet that shows a high-level site water balance, and if you’re lucky, your spreadsheet even includes some chemistry calculations. The need for a site-wide flow and chemistry balance is clear and there are many parties in the organization that need the data generated by the balance. The corporate team needs to understand the water management accounting for the Corporate Social Responsibility (CSR) report, the operations team needs to continually optimize the site to conserve water and chemicals, and project teams needs to understand the capacity limitations of the systems in order to support growth. As sites increase reclaim percentages, managing the variables gets complicated quickly.Home-grown spreadsheets have their place. For simple sites, a spreadsheet can be the easy way to represent the site water systems and represent how systems are related and how water moves through the site. You already know how to use spreadsheets; you have a license for the program and feel in control of the inputs and outputs. You are in charge and can customize your balance as changes occur. However, there are some challenges and risks with using a spreadsheet for this important task.

comparing spreadsheets and water management application

Time, errors, and single point of failure

Building even a relatively simple flow and chemistry model can be a time-consuming process. If you want to build in any advanced features to support dynamic decision-making such as an interactive site map or incorporating model flexibility (e.g. reclaim prioritization analysis, sensitivity analysis, etc.), the time increases exponentially. Organizing the vast amount of inputs necessary for a site-wide model can difficult task on its own. On top of that, if an error occurs in the inputs, calculations, or key assumptions of the model, finding the error(s) in the calculations is a challenge. We often see one-of-a-kind spreadsheets that are miscalculating key flowrates and chemistries and the owner never knew there was a problem.

The next question is who builds, owns, and updates the model? If your organization is like most others, there is one person who is the “modeling guru” that owns the data and the spreadsheet. We have a client that had a PhD chemical engineering wizard build a nifty and complex spreadsheet model with data from hundreds of flow meters. The problem is that when he left the company, the extremely intelligent engineer that inherited the model is still not sure years later of all the intricacies and interdependencies. Assumptions and tricks are hidden in calcs buried in the cells. Model transfer is difficult and changes in staff represent risk to the organization.

Iterations to reach steady state

When reclaim systems are involved, there is often a need to perform multiple iterations of the model to achieve a steady state for flow and chemistry. Depending on the complexity and system interdependency of the reclaim configuration, it can be daunting to nearly impossible task to create an Excel model capable of accurately calculating these steady state values. It is not uncommon for a model to require dozens of iterations or more before chemistry is relatively balanced for all water systems. While trying to account for the necessary iterations in the spreadsheet program, you’re required to come up with a solution that avoids circular references.

Assuming that a solution has been developed for simple iterations in the spreadsheet program, once you begin to have reclaim with multiple dependencies and systems both receiving water and sending water, it gets overly complex in a spreadsheet quickly. Unless you have very specific checks on every iteration with every system, you could be losing or gaining flow or chemistry in the model balance and not even know it.

Change management and change control

In most industries, being in control of the data and ensuring that changes are managed properly is critical. With a server-based Excel model that has many interested parties, how do you know that the model you are using is the latest version? How do users access the data without risking making unintended changes? How do you effectively control access to the sensitive information controlled in the model? Excel is a difficult tool to use for both change management and access control.

Collaboration across multiple sites is difficult

Model formats and approaches can vary from site to site, model owner to model owner. Having spreadsheets that provide accurate, apples-to-apples comparisons of site is a challenge with a spreadsheet.

Is there a better way?


  • a water model with an easy to build map, in software that has been specifically built to automatically calculate flow and chemistry through every stream for the even the most complex facilities

  • a tool where the graphics are intuitive and the visuals help you understand what the flows are and where they are going

  • features that limit the need for manual entry of data

  • a user-friendly interface that allows a user to set reclaim priorities by dragging and dropping

  • industry standard and custom reports that can automatically generate necessary data and confirm that the model is fully balanced

  • the ability to automatically upload both chemistry data from a lab analysis and regularly changing utility data

  • a one-stop-shop data repository for all the water management and key metrics across the entire fleet of facilities with consistent definitions for the reclaim accounting

All of these capabilities exist in the form of the FTD Solutions Water Management Application (WMA). For more information check it out here or reach out to us for a demo.

water management applicaiton facility map
Portion of typical industrial facility in the Water Management Application

Related Projects and Posts


Risk Centered Asset Management

I'm a paragraph. To update me, go to the Data Manager. The Data Manager is where you store and collect data for your site.

bottom of page