When capital budget planning, the educated guess is a great asset, but a great spreadsheet that can be quickly referenced to answer questions has its value. "Estimating controls migration is difficult and the number of unknowns is difficult especially when budgeting for projects a year or two in the future," said Midhu Varghese, section manager for control engineering and validation at AbbVie's biopharmaceuticals facility in the north of suburbs of Chicago. "It was a challenge to quantify areas of risk for migration capital budget planning," Varghese said.
AbbVie found the use of pivot tables generated from the plant I/O database provided a tool to quantify risk, refine estimates and reduce capital costs. Using the pivot table, AbbVie reduced the capital request by $100K and could actually expand the scope of the project, adding four more PCs and effectively saving $600k.
How Good Is Your Number?
AbbVie, founded in 2013 when Abbott Laboratories separated into two companies, is a $20B pharmaceutical company with facilities in over 170 countries and 25,000 employees worldwide. It needed to modernize older RS3, Provox and PLC legacy platforms in the chemical pilot plant and chemical manufacturing area that were originally started up in the 1980s and 1990s.
"Our controls migrations are especially difficult to quantify," said Varghese. "In some cases we are replacing something over 20 years old that many people have touched, making it difficult to quantify software configuration cost. In our case, the plant cannot be shutdown. It was necessary to answer the question, 'What could and should be migrated and in what order?' This was complicated by physical IO spread across many controllers."
"Changes were easy to make and immediate answers could be given to management with high confidence in the estimate." AbbVie's Midhu Varghese on the company's use of flexible Excel pivot tables to estimate migration project costs given various scenarios.
Brute force methods yielded a ball park, within 150% estimate, but Varghese needed a more accurate estimate when management asked for options. As the project became closer to reality, increasingly better numbers were needed, but complexity, project phasing and uncertain commissioning effort complicated the estimate.
Building a Better Spreadsheet
Being able to analyze all the data in your worksheet can help make better business decisions, but sometimes it's hard to know where to start, especially when you have a lot of data. "Keep the end in mind," recommended Varghese. "Then Excel can help you by recommending and automatically creating pivot tables, which are a great way to summarize, analyze, explore and present your data."
Compared to the time it would take you to build an equivalent report manually, pivot tables are incredibly fast, said Varghese. "If you have well-organized source data, you can create a pivot table less than a minute," she said. "To create the pivot table, start with the thing you trust the most—the database export. I also add columns for asset codes for accounting purposes, optional items, capital or expense reference and phase with point count as a reference."
The pivot tables Varghese created made evaluation of I/O, modules and risk points possible. She could quickly refine an estimate over a gated time frame from within 150% to +/-10%. "Using the pivotal table, I quoted the 3,500 I/O points in 16 controllers for the pilot plant migration," said Varghese. "Changes were easy to make and immediate answers could be given to management with high confidence in the estimate."
High Confidence Migration
Novaspect, a local business partner of Emerson Process Management, was hired by AbbVie to perform a migration front-end engineering design (FEED) study. "Novaspect likes FEED studies and it fits well into Emerson's Project Certainty approach," said Lee Valle, engineered solutions team manager at Novaspect.
"The FEED answers the question of what we really have," Valle said. "It defines the functional, operational and physical needs. And with the pivot table, there was no more hunting for the needle in a haystack," he said. "It helped us tremendously. We used it to get to a confident number more quickly and saved time in estimating the physical and configuration effort. It also improved confidence in the planned migration phases and confidence in electrical packages sent to our vendors."
"Using the pivot table to refine estimates, identify risks and plan the migration, we replaced old functional hardware in the chemical pilot plant and changed over during their busiest time at the facility," Varghese said. "We were spot on and ended up doing more with less and were right on budget."