The Cost for using Spreadsheets for Engineering Applications

18 Feb

When talking with lead engineers using homegrown Excel spreadsheets for various engineering calculations the following insights were used to help them determine the true cost of their methods.

The primary reason for developing their own spreadsheets was used because the entire office used MS Office including MS Excel. They were concerned about the cost of PIPE-FLO or a similar CMA application for fluid piping systems. It must be remembered that every process has a cost, the key is identifying the total cost of the workflow.

When asking the lead engineers how their Excel spreadsheets were working the following workflow costs were identified. I will identify the concerns that were identified along with examples of how it impacted the workflow. Many of these concerns are outlined in a Pumps & Systems article.

Development

It must be remembered that Excel is a program language, and if it is being used for production work it becomes an application. For an application, the developer must know the underlying calculations to be performed along with the source of the required input data, intermediate calculations and how the final results will be used by others.

For an EPC, the cost of developing a spreadsheet is directly billed to the customer resulting in a source of revenue. An owner developing a spreadsheet results in a direct cost.
The data entry units are a concern bases on the variety of customary engineering units in use. Many engineering schools teach using metric values, but many industries use standard US units.
Many input values are entered from standards or external sources. For example, the inside pipe diameter is a function of the pipe material, wall thickness or schedule, and customer preferences. An incorrect value of inside diameter will have a major impact on the calculated results.
Many intermediate results must be calculated effecting the final results, the ability to follow the calculations is required to ensure the integrity of the final results.
The calculated results typically fall within industry values, the spread sheet should recognize when the results fall outside typical norms.
The calculations should be documents in a standardized report.

A Texas Based engineering spent over 9 man months of engineering time to develop an Excel based spreadsheet having these and other features needed to meet their needs. The chief engineer stressed the need for what they had partially developed and asked how long it would take to customize PIPE-FLO to meet their specific needs. I stated that PIPE-FLO has all their customer requirements built into the program using design files and engineering data tables. After a review of PIPE-FLO the three engineered developing their spreadsheet said they had all of their feature requirements and then some.

Training

Since multiple people are using the application for production work, the training aspect must be considered. Since the spreadsheet is developed internally all new user must learn how to use the program. The major sticking points were the data flow, source of the input data, and the terminology using in the application. If the results were not compared with industry norms the user must be aware of the norms to determine the results are within reason. Also understating the method of solution is an important aspect in gaining user confidence. The lack of online help also resulted in slower acceptance of the inhouse application.

Support

Often the developer of the Excel application is the primary source of technical support for new and existing users. Since the developer is usually an experienced engineer with company insights this is expensive used of a valued resource. As the user base increases the developer is often called upon to add new features to the existing application. This often results in feature creep which increased all the costs associated with the application.

Version Control

Since Excel is an easy to use application and commonly used it is easy to modify the internal workings of the application. As a result, version control becomes a concern, if someone has a new idea or a short cut in performing the calculations. Often the Excel applications are developed by someone in a specific office, if the application is employed to many offices version control takes on greater importance.

Review and Approval

It is common practice for each calculation to be reviewed and approved by another engineer. The review is often conducted by senior engineers aware of the pitfalls and short comings of these calculations. One common concern is the results are out of the expected ranges that can be caused by entering a user input in the wrong units.

Design Control

Another cause for concern is using an item of equipment not approved in the client’s specification documents. For example, the allowable pipe sizes, schedules, valves and fittings are specified in the defined pipe class, entering data for a non-approved device will affect the calculated results. Since the calculated results are often used in sequent calculations in the design process it is important to determine the results accurately reflect the expected design.

Using the Calculated Results

Many of the results performed by the Excel application are used in subsequent design tasks. Additionally, many design values entered in the previous design tasks is also used in the subsequent design task. If that calculation is done with another Excel application the data is typically re-entered into the new spreadsheet. If the subsequent design task is performed using a CMA (Computer Modeling & Analysis) application the existing design information must be entered to the new application. Reentering data increases the possibility of errors involved with converting to new units or transcription

Lack of a Total Picture

Performing calculations in multiple applications Excel application does not easily present a clear and total picture of how all the items work together as a system. Using PIPE-FLO as a CMA application with the ability to integrate the all the items into “Believable to Me” results for the entire system.