The problem
Existing cost models were spreadsheet-based and contained more than 150 modeled elements along with market assumptions, in-house labor assumptions, rate cards, and other inputs. They could produce estimates, but the process was difficult to scale, difficult to govern, and largely opaque to end users.
My role
End-to-end individual-contributor ownership spanning business analysis, framework design, data architecture, model logic, application development, execution workflows, analytical reporting, deployment, and ongoing support, in close collaboration with planners and business users.
The approach
- Replace spreadsheet-based modeling with a centralized, database-driven execution framework.
- Separate model logic, assumptions, node specifications, and execution so models could be reused and governed consistently.
- Preserve the exact model, inputs, assumptions, and node specification used for each result to support later analysis and traceability.
- Automate high-volume model execution across approximately 46,000 network nodes.
- Expose model structure, assumptions, scenarios, and results through the CPR web application for review, comparison, and end-user execution.
Technology & capabilities
- Oracle data architecture, SQL, aggregation, and statistical staging
- React-based analytical and execution interfaces
- Node.js API and model orchestration
- Automated model execution and result processing
- Reusable dimensional and hierarchical calculation framework
Outcomes
- Replaced spreadsheet-based modeling with a centralized, repeatable, database-driven execution and analysis process.
- Created a reusable framework capable of supporting multiple dimensional and hierarchical calculation scenarios rather than a single hard-coded model.
- Enabled planners to compare modeled costs across markets, node types, and other business dimensions while understanding the variation behind averages.
- Made model assumptions, execution results, and scenario comparisons visible to end users through CPR rather than leaving the modeling process hidden inside analyst-controlled spreadsheets.
- Preserved model, assumption, node-specification, and execution context with each result to support reproducibility, traceability, and post-analysis.
- Established a foundation for later forecasting and financial-timing analysis.
From a calculation to a framework
The original need was straightforward in concept: provide planners with a consistent way to estimate the cost of planned work. In practice, the problem involved far more than applying a single formula. Costs depended on combinations of project attributes, network characteristics, market conditions, model assumptions, and historical behavior.
Rather than treating each estimate as a one-off calculation, I designed the solution as a reusable framework. Model inputs, business dimensions, calculation logic, execution, and analytical outputs were separated so that the system could grow as the business questions changed.
Designing for repeatability
A central goal was to remove manual intervention from the modeling process. The framework supports repeatable execution across large sets of planning inputs and stages the results for analysis, allowing planners to move from individual estimates to broader questions about patterns, variation, and expected cost behavior.
The analytical layer provides comparative views across dimensions such as market and network type, along with measures including average cost, minimum and maximum values, standard deviation, cost per mile, and cost per home. These measures help users understand not only the expected value, but also how widely real-world outcomes can vary.
More than a cost model
The most important architectural decision was to avoid making the framework specific to one cost-model use case. At its core, the system supports hierarchical calculations, dimensional rollups, reusable logic, and staged analytical output.
That design made it possible to extend the work into related capabilities including forecasting and period-based financial analysis without abandoning the original framework.
Automation at scale
Model execution was designed as an automated process rather than a sequence of analyst steps. Large sets of planning records can be processed consistently, with results written back into analytical structures for immediate use in dashboards and downstream planning workflows.
This reduced repetitive human effort while improving consistency and making the model easier to rerun as assumptions or planning inputs changed.
From hidden logic to transparent analysis
A major goal was to make the modeling process visible and usable beyond the person running the model. CPR exposed the model structure, assumptions, results, and comparative scenarios through the web application, allowing users to review how estimates were produced, compare model runs, and execute approved scenarios themselves.
Because each result retained the exact node specification, model, assumptions, and associated data used at execution time, later analysis could be tied back to the conditions that produced it. That created a level of traceability and transparency that was difficult to achieve in the prior spreadsheet-based process.
Supporting judgment rather than replacing it
The framework is intended to support planning decisions, not dictate them. Cost estimates and statistical patterns provide a common analytical foundation, while planners retain the business context needed to interpret individual projects and exceptions.
That balance—automation where repetition adds little value, visibility where uncertainty matters, and human judgment where context matters most—has been a guiding principle of the design.