The use of custom Excel programming and @RISK APIs allows the automated analysis of historical data and construction of sophisticated risk models. Here, we present an application in the energy sector as an example.
As part of its Custom Development services, Palisade developed an add-in that automates the construction of a risk analysis model to assess the Value-At-Risk (VaR) of a portfolio of gas future contracts. This application uses a Principal Component Analysis (PCA) to describe the variability of historical correlated forward price curves; this analysis allows the creation of a @RISK Monte Carlo simulation model to generate forward price curves and compare them against the current positions of the portfolio.
PCA is a statistical technique which can identify the main independent components (sources of risk or information) in data (In this example, historical prices of natural gas forward contracts.). There will generally be as many components as there are forward contracts in the analysis. Therefore, if we are analyzing monthly contracts up to 36 months forward, the analysis would reveal 36 components. In data which is highly correlated (such as natural gas forward prices), typically only 2 or 3 components are significant, accounting for nearly all the variation or “movement” in the data set. For the forward price curves of natural gas, the first principal component generally corresponds to a parallel shift in prices, while subsequent principal components correspond to relative price changes (i.e. a change in calendar spreads).
Using a VBA macro, historical data is analyzed using PCA. The macro constructs an Excel model and @RISK runs a simulation to generate forward price curves so the risk profile of the portfolio can be assessed. The figure below presents a result that shows the predicted performance a of sample portfolio where the VaR (@ 5%) is shown:
If you are interested in the implementation of this type of model, @RISK can be of great help. You can concentrate on the quality of the model and input data and let @RISK deal with the simulation and generation of reports.
