A repeatable and reliable scenario
modeling approach to support Value-at-Risk analysis did not exist.
Developing scenarios for the entire portfolio was subject to error and
consumed weeks to complete.
The business required spreadsheet flexibility and could not change the
underlying processes.
Actions Taken
Obtained CTQs, observed model usage, and
identified constraints.
Developed a data structure that would be used to mechanically receive
input assumptions, and then mechanically provide resulting outputs.
Prototyped candidate approaches using Office Automation.
A flexible modeling capability was developed
which used the existing XL workbooks. By
design it accommodated all possible modeling approaches used within the
workbooks.
Business Results
Developed a flexible client-server application using MS ACCESS, SQL
and VBA to manage
the distribution of assumptions and the acquisition of results from different scenarios across
the entire portfolio.
The tool used existing workbooks but did not affect modeling flexibility.
The system enabled
efficient, consistent deployment of assumptions and scenarios across the
portfolio without user disruption.
The entire effort was reduced from weeks to
hours and significantly improved the business' strategic posture.