Value-at-Risk (scenario modeling)
Business Case
- 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.