In the article about ground exploitation risk we explored how we approached the estimation of ground risk within the municipality of Almere. Let’s now look into the risk tooling itself.
Despite its various issues, Excel is still by far the most used tool when it comes to analytics within many organisations. Therefore, for ease of use and flexibility, we created a single excel interface for the end-user to obtain all relevant risk reports. The aim of the workflow is to guide the user through the steps necessary to complete a re-evaluation of the final risk report for one ground project at a time. The user follows a predefined workflow as represented in Figure 1 below.
The main steps can be summarised as follows:
- clear the workbook from previous data & load new data from source system
- control of mappings of loaded revenue & cost items
- adjusting parameters for simulations
- risk calculation
Control mappings of new revenue or cost items
In some cases, the source system contains a new (unmapped to a broader category) revenue or cost item (system ID). To be able to calculate risks on such an item, it needs to be mapped to the correct category.
This is a manual process which requires proper human input and consideration. Automated error report guides the user if re-configuration of mappings is indeed needed.
Control mappings to risk drivers
In other cases, a new revenue or cost category might need to be created. A separate mapping sheet allows one to assign this new category to the correct risk factor. The majority of the risk drivers are available via our cloud API, some are custom and specifically created for the project.
The ground project has two broader category risk drivers on the revenue side: price and volume risk. Price risk is related to a decrease in the ground price, while volume risk is related to a decrease in the number of units that can be sold in a negative scenario.
On the cost side we see three main risk factors: site preparation, preparation for residential use, and planning costs. For each of them there is an associated price and volume risk. Price risk is related to a potential increase in the costs per unit built. Volume risk is coupled with the reduction in the total amount of lots that can be built in a negative scenario. In this case the volume risk has a decreasing (and therefore positive) effect on the costs.
Detailed risk report
A detailed sheet shows the projection of the budget and applicable price and volume risks over the years. We estimate two risk values: one relative to budget (Risk vs Budget) and one relative to the expected VaR50 scenario (Risk vs VaR50).
Additionally, we have included the possibility of postponement of the lots. This means they can still be sold after the initially projected sales period in case a negative scenario materializes and leads to unsold capacity. Therefore, we calculate the risk on postponed sales as well (risk vs maximum capacity).
It should be noted that the business logic (excel formulas) are made visible to the end user so one could see in a controlled way how all the data is related. They are re-populated with each update of the risk report so the possibility to change business logic by mistake is eliminated.
Aggregate risk report
It is important to understand how risk changes if some of the assumptions change. We have included several fields with input parameters. For example, the end user can choose:
- the risk appetite by selecting a given certainty percentile (e.g. 75%, 80%, or 90%)
- whether to include or exclude postponement of revenue and costs
Another feature is the ability to simulate price changes for both the cost and revenue-side items, as well as volume changes with a positive/negative tilt (distribution of exposure towards the beginning/end of the projected period).
The final risk report puts it all together to create a final view of all the revenues, costs, and results in the negative scenario.
It also displays the cumulative effect over the years and reports the results including correlation effects between the risk factors (as not all risks occur simultaneously).
Graphical presentation of the risks
In addition to the tables in the risk reports we also provide graphical presentation of the risks for the income, costs and the net results.
Summary
The possibility to automate the creation of risk reports for ground exploitation projects by providing raw input data, coupled with simulation parameters for different scenarios, enables us to easily calculate and compare risks using a user-friendly interface.
The combination of automated script with excel formulas ensures that the main business logic remains available to the end user enabling them to remain in charge of it while eliminating the possibility of someone erroneously changing some of the formulas.
In addition, the possibility to update the risk estimations via the Asset Mechanics Risk API enables one to have an objective and relevant calculation of the risks, and to make the best decisions under changing conditions.
If you have a similar (ground exploitation) project, don’t hesitate to contact us to explore how we can also help your organisation with data-driven risk estimation.