Has your question already been answered?

Get a quicker response; before you post, why not search to see if it has already been answered

Monte Carlo Simulation

8 replies [Last post]
S V
User offline. Last seen 19 weeks 1 day ago. Offline
Joined: 31 May 2018
Posts: 4
Groups: None

Hello Our company would like to do a monte carlo/probability analysis on a financial model which has a number of variables which includes a number of funding/profit sources, borrowing sources, interest rates, years/duration's, etc. We are trying to do breakeven analysis and/or probability of achievement analysis given these variables and some other factors. My boss figured this could be down with the primavera risk analysis software i have installed, however in my experience, this software is more directed towards schedule risk analysis and not suitable for this application. Can anyone confirm this and/or provide recommendations on what software would be more suited for our current needs? Thanks

Replies

S V
User offline. Last seen 19 weeks 1 day ago. Offline
Joined: 31 May 2018
Posts: 4
Groups: None

Thanks All, this is great insight

S V
User offline. Last seen 19 weeks 1 day ago. Offline
Joined: 31 May 2018
Posts: 4
Groups: None

Thanks All, this is great insight

Rafael Davila
User offline. Last seen 58 min 45 sec ago. Offline
Joined: 1 Mar 2004
Posts: 4534

Financial model shall consider timing as well as financial constraints, the same goes with Monte Carlo. 

Financial resources leveling from Rafael Davila
Renzo Constenla B...
User offline. Last seen 7 weeks 1 day ago. Offline
Joined: 23 Mar 2011
Posts: 14

Dear SV:

 

Primavera Risk analysis is usefull to time related analysis (duration, critical path).

There are more suitable softwares for incomes/outcomes (revenues, costs) analysis, like Crystal Ball (a Oracle tool) or Safran.

Regards

Santosh Bhat
User offline. Last seen 18 hours 35 min ago. Offline
Joined: 15 Apr 2005
Posts: 102

SV you are right Primavera Risk Analysis is more suited to Schedule Risk Analysis.

Rafael Davila
User offline. Last seen 58 min 45 sec ago. Offline
Joined: 1 Mar 2004
Posts: 4534

Correlation is the degree to which the observed value of two or more random variables are related. If two random variables are uncorrelated, the observed value of the second variable will not be affected by the value of the first, and vice versa. If one variable is likely to take a high (low) value when the other variable takes a high (low) value, the two variables are positively correlated. If the opposite occurs (a high value for one variable is likely to occur at the same time as a low value for the other variable) the two variables are negatively correlated.

To believe you can find true multiple correlations is naïve.

To believe you can easily model multiple scenarios that require different activities using Monte Carlo is naive.  Only a few risk software such as Spider Project can model conditional scenarios, advanced, complicated and in some cases not enough. 

Spider Project provides for Monte Carlo as well as for 3 scenarios risk models.  Spider Project provides for modeling non-deterministic production rates and their impact on the schedule.  Spider Project allows for conditional models. Spider Project calculates IRR, NPV and can level funding constraints. Fortunately you can model 3 scenarios if using your CPM software and Excel to fit the probability distribution and the related S curve.

You are better off if using 3 scenarios approach.

Bogdan Leonte
User offline. Last seen 19 weeks 2 days ago. Offline
Joined: 18 Aug 2012
Posts: 142

I'm afraid your problem is a bit more complex. I did my disertation thesis on this subject from the contractors point of view and here are some problems you must consider:
1. Payments -  when you pay vendors, subcontractors, salaries, taxes, etc. and how much do you pay;
2. Revenue - when you recieve money from your clients and how much;
3. Internal funding - how much money and when you have it (how much money your company can invest in a project without external funding - bank, for example);
4. Return of the internal funding - when you will be able to get back the money you invest in a project portfolio - here you should include monetary depreciation depending on inflation, exchage rates between currencies, etc.
5. External funding - how much money and when you can get it (how much money you need to borrow from an external source - bank for example);
6. Cost of external funding - how much money you have to pay for loans - here you will have 2 things to consider: fixed costs for approving loan and accessing it and time costs which may occur;
7. Return of external funding - when you are able to pay the external loan and how much

All of these are milestones, time costs for external funding should be placed on a hammock, for which costs are calculated using a time parameter if you would like to have a dinamic model.
You build 3 scenarios (Optimistic, Probable, Pesimistic). These scenarios are inversely proportional to the execution schedule because in the optimistic schedule everything will be more condesed and payments will occur ealyer, also because of this some milestones will be missing from one scenarios to another, usually the optimistic execution schedule is the most expensive.

Ok now after you do all this you cannot run a Monte Carlo because as I said some milestones may be missing from one scenario to another. You must run a 3 Scenarios Simulation on the project level and you will get the results you want + buffers.

Here is a cumulative diagram of Paymens vs Revenue, where red is payment and green is revenue:

5648
payment_vs_revenue.png

Here is a Cumulative diagram considering all 7 components:

5649
all_7_components.png

For a Portfolio of 5 projects and 1300 activities, spaning 1 year you should get around 150 milestones.

Now this is a detailed analisys, depending on your precision needs you may simplify it.

Hope this helps.

Best regards,
Bogdan

Rafael Davila
User offline. Last seen 58 min 45 sec ago. Offline
Joined: 1 Mar 2004
Posts: 4534

For risk analysis models considering funding constraints please click the following link: