Accessible risk management using workflow simulation into Microsoft Excel

. We live in a digital era. Nowadays almost all accounting and production project information are collected into long electronic spreadsheets that are easily accessible for the production managers so they can extract the data and analyze it according to their needs. In every work project, especially in long project production, IT or manufacturing, there is the need to perform an elaborated risk management, just because there is a gap of uncertainty from the process of planning to the final workflow cost. One main task that often causes problems is the quantitative risk analysis. In this article we will show how to use a well-known standard product, that is easily accessible even to micro entrepreneur – Microsoft Office/Excel. With the help of MS-Excel it will be cost effective and easy to extract the data and create standard risk simulations, which will help managers to take decisions and reduce their risk exposition


Introduction
In every project, especially in a long term projects like creating an IT product, there is the need to manage workflow cost risk, as the time differs and the production factors could change. This risk management process includes several steps: Step 1: Risk management planning The risk management plan describes how risk management will be structured and performed on the project. The risk management plan includes the methodology, the roles and responsibilities, the risk budget, the timing, the risk categories etc. [1][2][3] Step 2: Risk identification Risk identification determines which risks might affect the project and documents their characteristics. In this phase a lot of creativity techniques (e.g. brainstorming, Delphi technique) may be applied [4,14] he result should be a risk register that contains the stochastic factors that will impact with the most strength the process throughout the whole timing until completing it.
Step 3: Qualitative risk analysis Qualitative risk analysis includes methods for prioritizing and evaluating the identified risks for further analysis. Here you can also find the application of a variety of creativity techniques (e.g. brainstorming, expert interviewing, and Delphi technique). The result should be an update of the risk register that now also contains an estimation of the probability (density) of the occurrence and the impact of a risk. Perhaps you will detect dependencies between several risks, so using the model the managers have to use also correlation matrix to show how the move on one factor impacts another one. [4][5][6]12] Step 4: Quantitative risk analysis During the quantitative risk analysis the individual risks are modeled together. Here modeling techniques as Monte Carlo simulation or decision trees are used. The quantitative risk analysis should provide an impression of the probabilistic analysis of the project workflows and individual stochastic risk factors [7,8,13].
Step 5: Risk response planning Risk response planning is the process of developing options, and determining actions to enhance opportunities and reduce threats to the project's objectives. Beside the acceptance of risk, you can also try to avoid, reduce or transfer the risk or to mitigate the impact [2,9].
Step 6: Risk monitoring and control During the whole project the identifying, analyzing, and planning for newly arising risks is a permanent process. Using the Excel model it is possible to keep track of the identified risks and those on the watch list, reanalyzing existing risks, monitoring trigger conditions for contingency plans, monitoring residual risks, and reviewing the execution of risk responses [11]. Especially in the fourth step a lot of stochastic analytic methods are needed, which makes this step a little complicated. We want to demonstrate this with two examples. Afterwards we will solve these examples by using Monte Carlo methods with Excel [12,15,17].
As everybody knows Monte Carlo simulation is a method that relies on repeated random sampling from given distributions [10,11,16]. Because of their reliance on repeated computations and random or pseudo-random numbers, Monte Carlo methods are most suited to calculation by computers. It is possible to import or export from/to Microsoft Excel all risks, opportunities, actions or consequences. Optional data exchange to Microsoft Word and Open Office is possible too. Marked areas of tables can be exported or imported to/from Clipboard. The model presented below will concentrate on the quantitative risk analysis of risk with workflow simulation and 2 stochastic factors.

Graphic User Interface for Risk Calculation In Excel
The Graphical User Interface should be Browser based including tree, grid controls, property list and business graphics. The Risk Management Solution has to use the Project Management System Visual Components and the Project Management System Tag Library.
Following examples of GUI elements are generated by a simulation of the workflow cost example in Fig. 4    Tasks, sub-tasks, resources and risk factors can be ordered on a hierarchic tree structure. Algebraic expressions and formulas that define the value of a dependent workflow node (1. Product Specification costs from example - Fig. 3) as a function of values of other nodes can be free edited using edit box and function buttons.
The workflow can be calculated for every future period using the algebraic expressions that define the start and end point of a task or a sub-task, the fixed costs at beginning the task effort and the assigning of resources (Fig. 4). The algebraic expressions are expanded for every analysis period (like the drag of formula filed in MS-Excel). The expression behavior is affected by the column index using if-statements. It is assumed, that the expressions can be constructed by Risk Management System synthetically based on workflow definition data.    The development of costs for tasks and for whole workflow in the simple calculation (without Monte Carlo simulation) can be represented by area charts. Cost Limits per task and for whole workflow can be shown graphically. For example, 100.000 for Product Development and 500.000 for whole workflow.

Fig. 7. Simulation of Workflow.
A set of simulation results (Confidence Value, Risk, Risk in %, Min., Max, Total Range) can be displayed on more rows for every workflow node after Monte Carlo simulation.
It is possible to calculate the scheduled value (green bars) to be below a limit of 550.000 (red line), but the simulation shows that the confidence value (red bars) go to 650.000 (blue line).
This means the expected (most probable) cost for the workflow is 550.000, but it is a 5% probability (the confidence level is set to 5%) that the cost can increase to 650.000. The distribution of the workflow cost one period for workflow end, i.e. on 01.08.2021, is given on Fig. 8. The expected value = -514.874.10 is below a limit of -550.000, but the Confidence value (right to red area on the graphic) is -587.772,20. The square of the red graphic area is 5% of whole distribution area. The Risk Management System allows for free definition of risk factors (rights on Fig. 9). A subset of the risk factors (above left on Fig. 9 Task Duration and Effort Development) is then assigned to simulation environment used to simulate one or more workflows. The future development of risk factors (blue line above) and their volatility (standard deviation) (red and green lines above) for every future point are forecasted based on historic data before 01.09.2020 or on experience (s. Fig. 10, 11 (a),(b) ). Given the historic and future development and the forecast for the volatility of all risk factors is enough to derive the needed simulation covariance matrix.

Conclusion
In conclusion, the following theoretical-practical conclusions can be derived: 1. Using simulation with Excel offers an easy accesible tool for evaluation of the workflows in order to provide a good basis for taking the appropriate measures to reduce the risk exposure. 2. Amongst the major advantages underlined in this paper we count the relative costeffective availability and spread in the new digital era for every business on the globe to use Excel instead of equivalent software packages. 3. Best practices to use the model it to review the risk assessment at intervals not exceeding the selected periods (less then twelve months). 4. Historical data of stochastic factors is needed to import into the model. The longest the data is, the better the simulation results are. 5. Finally, we must conclude that such quantitative mathematical models provide guidance for analysis, but due to their statistical dependence, do not give great results in force majeure market influences we have witnessed in recent years, such as COVID-19 or military invasions close by.