"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."