 Hello everyone welcome to the session of Monte Carlo simulation and risk analytics. In the previous two sessions we have discussed the basic understanding of Monte Carlo simulation and its scheme and the application also we have done the excel illustration and then we have discussed the basic understanding of system dynamic simulation using benching software. Today we will discuss the risk analytics part using the software called address software. Here I have opened the net present value problem, you can find this problem in their website you can download that particular problem by searching this particular name of the problem statement you will get the problem. So here is the basic problem you can see the discount rate is given 12 percent and the investment cost the one year revenue the annual fixed cost annual revenue growth rate 5 percent and annual variable cost are given 50 percent of your annual revenue. So this input data are being given you can calculate your net present value here. Suppose the project that you are going to plan and you are initially you are calculating the net present value that enter present value of the data are being for the next 10 years are being predicted here. So that calculation are being also given you can see here sum of your present value say present value by 1 plus internal rate of return to the power your number of year. So in which year it is say n if you are considering for n year the formula will be like this like this. So this is the NPV calculation net present value calculation also you will have to add one more term like subtract with the present value minus your cost initial investment. So this is the formula let me make it with little bit of large form so that you know you can also observe this. So this is the formula of net present value using this formula you can search Google also this formula generally account and also you know financial people calculate this net present value and internal rate of return are nothing but at what point of discount rate of net internal rate of return your net present value will be 0. So if you do not know the internal rate of return you calculate this net present value you assign assume that net present value as 0 and then if you make this equation equal to 0 you will get the internal rate of return also. In our case the discount rate or internal rate of return has been given as 12%. So in each year how much return you will get you will be able to calculate and the current value you will be able to determine. So here you can see so here it is your only one calculation one year calculation I will show you the present value here your interest cost investment cost and then you can see your fixed cost that we are calling and then here you can see your variable cost. So we are subtracting your fixed cost and variable cost and cash flow from your on-year revenue. So this is your not investment cost investment cost is here initial investment cost look at minus C7 it is nothing but minus C7 you can see this one. So that will subtract here like here that will subtract but this is your revenue of first year. So you can see your cash flow this one the present value is like this. So you can see 1 lakh minus 35 minus 50 is your total current flow. So this is the first year. Now second year your annual revenue is growing with 5% look at 5% annual revenue growth. So your annual revenue say here you are calculating 5% extra right. Similarly next year 5% extra of that second year so third year will get this much of revenue and in a similar manner you can calculate the fixed cost which is fixed for every year we are assuming the base model and then variable cost will be the 50% of the current annual revenue. So you can see 50% of that and you can calculate the cash flow. So this way we are increasing 5% annual revenue and the corresponding cash flow we have calculated for the next 10 years. But the current value the net present value is coming out to be this formula this particular formula right. So if you use this particular formula you can calculate the net present value here and the corresponding internal rate of return. We are concentrating only say net present value. So this is the basic formula where you know any charter accountant can give you on you can calculate using this particular calculation process of net present value. So there is no uncertainty, there is no risk, there is no prediction based on this is called deterministic prediction which people calculate. Now what we are going to do? We are going to bring the uncertainty in terms of investment cost, in terms of annual revenue, in terms of annual growth rate. So you do not know what is going to happen in future right. The growth rate could vary say from 5% to 10% 7% 8% 9% 4% 0% also it can be negative growth also. So if this type of situation happens initially we cannot predict everything will be 5%. Similarly your investment like you know revenue also will change, annual investment cost might also be very in that case what would be your net present value if the entire situation become uncertain. So you can see the address has come, we have installed it now. So now we will run this particular net present value calculation under risk environment where you can see your investment cost is not 1 lakh. In the previous model it was 1 lakh but here it is not 1 lakh, here you can see it is a triangular distribution where lower side is could be investment where it could be say 10% less as I mentioned in the previous class or previous example or say it can go up to 50% extra your investment like you know expenditure of your projects say. Suppose you are constructing a bridge or you are constructing a say you know any particular project where you know your investment can also have a uncertain which is very practical in real life. So therefore your net present value will also change. So we are not considering only the investment variation because of the delay you know political dilemma or say you know local people protest raw material and time and many other aspects comes together right. So all this uncertainty if you bring together whether risk. So in that case your investment may, investment cost may go high. So suppose we are now making it as a triangular for the sake of illustration in reality you may consider the actual data with the pattern with the distribution how to draw the distribution, how to fit the distribution I have discussed in the previous session. So you can consider accordingly the corresponding pattern of the data. So here investment cost we are assuming all of them we are assuming as a triangular distribution for the sake of illustration and the practical net present value calculation under risk environment. So now similarly your annual revenue look at it also has a variation say 20% downside say 10% extra up. So if you get a higher revenue it will be high but here suppose you have kept a variation in terms of lower bound and upper bound also. So in that case your annual fixed cost is also been changed and then this part is very important look at here your annual growth rate we are assuming in the previous case we are assuming 5% annual growth rate every year right every year 5%. But here we are not considering 5% we are considering that 5% the mean of annual growth rate for next 10 years or 5% but standard deviation is 8%. That means there is 8% downside risk and upside risk also so it has a variation. So in that case if all these data sets are following some distribution with this parameter or uncertainty and your annual growth rate is also not fixed it is also following some normal distribution with a mean of 5% and standard deviation of 8% in that case what would be your net present value even we have considered our annual percentage of cost variable cost is also following not a 50% fixed it also have a 2% of variation say for the sake of illustration only in practice you can actually capture the data based on the historical information or you know experts opinion based. So now we have calculated all this look at here now if you look at the changes here you can see this 5% we are putting as it is like the previous formula but this 5% look at this particular 5% C10 it is not a fixed value 5% it is following normal distribution with mean 5% and standard deviation 8%. So we are considering these aspects suppose let me put this is the most important parameter so let me put with a yellow color rest all are also uncertain just I am capturing this as a different color but rest all will vary also because you know all these things are being changed with the distribution function. So therefore your net present value will also change now net present value calculation you can see here say if your calculation are like this is a deterministic calculation of net present value the way you know accountant people calculate this but here since it is a risk output you have to go to a risk button and you click it as a output cell risk output cell that is it. Now this cell and this cell are same so anyone you can run so we are going to run say say this for analysis for say 500 iterations say this there are so many you know even say 1000 iterations right sufficient simulation you can get to know and let us run it look at here. So we have found the net present value and in that case you can see your net present value the mean is now 36 in the previous case what was the you know on your calculation almost 41000 look at here right net present value in a deterministic case but here an internal rate of return is also something different but here like you know internal rate of return is also changing because in a similar simulation in a same simulation both are been run actually in net present value and internal rate of return also. In earlier case just you have made your net present value equal to 0 and you found 19.7% as an internal rate of return fixed case but here both are becoming random and here you can see this is also random you can see here risk output is even given here so that means you have run the simulation has run the IRR also but we are concentrating only net present value and in that case you can see we have run the result so let us see browse the result here you can see the mean of net present value reduced because of uncertainty it is now 36000 not a matter look at the standard deviation it is 114000 is your standard deviation. Net present value is just 36000 but net but standard deviation of that is 114000 that means the net present value can be negative also imagine if your investment is high and revenue are not been generated effectively in every year and growth rate is also very low like all are random look at here if your investment cost is high like 1.5 lakhs suppose to the high side and through triangular distribution suppose in a simulation you have generated that the net present investment cost is 1.50 lakhs the upper side of triangular distribution suppose through inverse function you have generated say in a particular simulation out of 1000 simulation and your annual revenue are coming up to very less a near 80,000 lower side and the growth rate the 5% growth rate suppose are also been lower side downside of your normal distribution you are considering in that case you may see your total net present value can come out to be minus 2 lakhs imagine here look at here minus 2 lakhs and if your investment cost is less and revenue are been high with the high growth rate in every year in that case you might get a net present value of 5.56 lakhs which all these aspects were not available in the deterministic case in the basic case but here you are able to see all these aspects in well in advance in your hand and accordingly you can play with your project you know give more emphasis on how to increase my net present value or how to concentrate on my annual growth rate or what are the factors that impact my revenue so all these things where I may increase my revenue here on your basis so all these analysis you will be able to see even you can see here your confidence interval look at the confidence interval of your data look at here all these are been posted here which you can copy and paste in your excel or you know in your world or PPT but here also you can see that 90% confidence interval are been given with say you know minus 1 lakh 10,000 to 2 lakh 46,000 look at the 17,000 are a representative in one of the like you know in the thumb length it is one of the simulation process where you found this but it is not the exact exact mean is on an average mean is 36,000 here you can see on an average mean is 36,600 but this 17,000 is just a representative like a thumbnail some some one of the simulation value has come here because the range is minus 2 lakhs to 5 lakhs so in between some value has come here it does not mean that this is your expected net present value but in the previous model if you go back to the previous model here this 41,000 is your actual net present value because deterministic case you have calculated but here in this the address model that your mean is 36,000 of your net present value and also you can see the variation also you can also play with your you know the upper bound lower bound all these things the way I have shown you in the previous example we can play with this also so suppose you know you want to see that my net present value should not go below 0 so what is the chance for that you can see you know almost 43% chance 43.7% chance that your net present value may go below 0 so this kind of value at risk you can calculate actually you can see the upside value of risk also the opportunity that you may get look at here only 4.6% chance that your net present value will be higher than 2,60,000 but there is a almost 5% chance that your net present value may go up to 5 lakhs so this little slight opportunity also there which you are able to see from this distribution function or histogram analysis of your entire you know cumulative data here you can see also the cumulative graph you can see the cumulative graph look at here how that data are being spread you can also see the tournament analysis here who are making maximum variation look at the annual growth rate is making maximum variation to your net present value in earlier problem we had a different variables but here you can see the annual fix both are all of them are random look at I have kept all are as a risk parameter look at here look at the data sets here all of them I have considered maybe in the basic case here it is I have considered triangular distribution first three parameter and annual growth rate and the percentage of extra cost or the variable cost I have considered normal but here you can see who is making maximum variation the annual growth rate because everywhere it is changing and because of that because of annual growth rate you can see your total revenue are being changed here now it is not only 5% increment it is very it is varying so this analysis you are able to see and corresponding net present value prediction the confidence interval also you can make a in any business setting. So, this is the advantage of business forecasting through this particular simulation based address software. So, you can trigger annual growth rate so if you can you know enhance more annual growth rate you can see the variation of net present value so you focus reduce the downside risk and increase the outside opportunity upper side opportunity you may increase your net present value also. So, if you can trigger that growth rate you will be able to get a higher net present value because it is making maximum variation. So, now one more thing I would like to show you perhaps I have shown you that earlier also here if you want to calculate your scatter plot with your net present value with your annual growth rate suppose this one you can see how it is making changes look at here look at the scatter plot. So, here it is says that if your annual growth rate increases little bit you see your the exponential growth of net present value look at here this kind of analysis even can give you a better insights to the managers in making a better strategic decision making or investment opportunity. So, here you can see how that you know net present value are getting impacted throw little changes in the annual growth rate look at the annual fixed cost if you calculate that with annual fixed cost you would not find this much of you know variation look at it is also following triangular distribution but look at here it is scatterness in almost you know equal manner in all the poor content. So, it is not making much changes in your net present value here you can clearly observe, but the annual growth rate has a high impact on your net present value, but not annual fixed cost look at it is spread it is you know scattered manner. So, it is not having much correlation with that data. So, this type of analysis also you will be able to see you can actually copy all this and you can paste in your PPT Excel award and you can make a management presentation also or project presentation also. The drawback here is that which you can improve further to make it more practical problem that here you can see this 5 percent change are coming from this normal distribution right look at here normal distribution through Monte Carlo simulation you can generate one input of like through inverse function one input of random variable from your cumulative line here which will come here which will come here which will come here, but all value will be the same for example, in here in place of 5 percent if you put say 4 percent say 4 percent in a simulation you have generated 4 percent through random function. So, the 4 percent will come here everywhere everywhere you will generate 4 percent of annual growth rate right. In a second iteration of your 1000 simulations say suppose you have got 4.5 percent say 4.5 percent say look at here 4.5 percent in that case your annual growth rate will change to 4.5 percent to all the next 10 years if you make it say 6 percent. So, if you make it 6 percent so every year you will see the annual growth rate of 6 percent. So, accordingly you will be able to generate 1000 simulation output right or iterations and then corresponding net present value calculations I have shown you and because of that you can see the variation of your net present value. Let me run you show you the result again and here you can see the variation of your annual growth rate to net present value which is very high. Let me go to the main graph and here you can see your standard deviation is 1,14,000 note down 1,14,000 and here the variation of your tornado graph by annual growth rate is high minus 1,007,000 to almost 2,0072,000. If you keep other parameters fixed static then only annual growth rate is having so much of variation minus 1,007,000 to only this much itself annual growth rate forget about other parameters 2,0073,000 note down it now what I am going to do this input data of annual growth rate say 5 percent, 6 percent in every simulation we are generating some variation right but that variation is remain same for every year. So, 4 percent annual growth rate means in a simulation if you have generated 4 percent so every year you are considering 4 percent growth next 10 years. 6 percent means every year are generating 6 percent growth 2 percent means if there is a negative growth say every year generating negative growth how come if there is a negative growth in a particular year how come every year will be a negative growth if there is a 7 percent say 10 percent annual growth rate suppose in normal distribution in certain case suppose you found 10 percent annual growth rate or say 8 percent annual growth rate how come your every year it will remain same 8 percent. So, that mistake we are doing here despite we are generating 1000 of simulation that of difficulties we are going to overcome through this particular extension look at here. So, in this case what we are going to do here we are the other parameter we have kept as it is but look at the annual growth rate we have shifted here and we are assuming that on an average every year next 10 years the mean value of annual growth rate will remain 5 percent and the standard deviation of 8 percent as it is but every year we will not generate the same value. We will generate 10 another simulation along with the simulation process where every year my annual growth rate will change if this year it is also following normal distribution with 5 percent mean and standard deviation of 8 percent the next year also from the same data sets we are not considering the input to annual growth rate of each next 10 years for every year individually we are generating this data set. So, here suppose here we have generated 4 percent with a different normal distribution here we have generated say another distribution another random number. So, we will get another growth rate for fourth year we will get another growth rate for fifth year another growth rate because these are now independent not are same all you know annual growth rate percentage changes are not same. So, every year we will have a new random number input the corresponding annual growth rate will also change which is more practical right you can put a correlation right that that additional extension you can do, but so far let us understand if you change the variation of an annual growth rate in a at a same same instance single instances will generate for next 10 years but not same 5 percent or same 4 percent or not same 8 percent for every year. You are making a variation in year on year basis on year on year basis to each annual growth rate each year's annual growth rate in that case if you run your net present value let us see what happens look at here one illustration I can show you here you can see this we are considering 5 percent of that, but this 5 percent are generated through a separate normal distribution clear this 5 percent is coming from the previous year's growth plus 5 percent, but this 5 percent will be generated through a separate normal distribution not fixed. So, all these are now a independent normal distribution. So, now in that case if you run the simulation what will be a net present value and the corresponding 5000 will be too high say 1000 simulation let us run the simulation now. So, see here what happened your mean value look at it has reduced further it is 17000 now and standard emission has come down to 48000 look at the reality now earlier case it was 41000 in a deterministic case now when you have put the uncertainty to each of them with a fixed 5 percent for everybody you have got 36000 as your mean value, but standard deviation was 14000 like you know we have observed that you know your annual growth rate has a maximum impact in your or variation in on your net present value now we are making a different annual growth for different year. So, in which is more practical. So, therefore, here you can see net present value is still positive it is 17000 which is much more practical as well as you can see your standard deviation has come down from variation has come down to 48000 only imagine and also you can changes overall range of net present value here it was minus 28000 now here you can see the only minus 14000 and maximum it was it went up to 5 lakhs I think more than 2 lakhs, but now here you can see the maximum is just 1,87000. So, overall variation the lower bound and upper bound of your net present value has also been reduced only one parameter you have changed what is that that is called annual growth rate rest all you have kept as it is with uncertain parameters I have not changed the set 2 and set 3, but here what I have done only I have considered the annual growth rate for each year are random are different from year on year basis. So, therefore, which is more practical and your variation actually it will not increase it will rather come down and you can see your net present value mean of net present value and the standard deviation look at the ternograph it will also have a maximum variation, but look at the variation changes has been reduced now look at here and also look at couple of year annual growth rate second year and third year has a maximum annual growth rate because annual growth rate are becoming most of the annual growth rate is are making maximum impact, but based on this 1000 simulation you can see you know like 1 year even is also having impact investment cost has also impact, but annual variable cost has also impact, but overall you can see only and annual growth rate are coming up as a more explained variable to your net present value and here also you can see which year you are having maximum variation that is depend on the random sample, but in practice also you can analyze the situations in a better manner. You can also see you know your fighter grab also which are making to some extent much more steady as compared to the previous case. So, this many other features also you will be able to generate through this. So, now if you want to conclude this particular analysis you can go back to the previous examples you can see here the annual growth rate is having maximum variation where you know lower bound is minus 1 lakh and upper bound is 2,70,000. But here you can see your annual growth rate are not that much making impact it is it is making maximum impact, but variation are quite low variation are not that high minus look at here like here you can see minus 19,000 only one of them to only 57,000 and you can see the highest one which is coming from the second year. But overall you can see the variation has been reduced and your net present value is also much more practical as well as your standard deviation overall standard deviation has also come down from 1 lakh 14,000 to 48,000 corresponding in a interval also confidence interval also is being changed. This case is much more practical than the previous two case and with that you can go to the practical case and you can move the bidding or the company presentation and the corresponding analysis cost estimation, budget estimation you can do also the time estimation part also you can do through this analysis also where uncertainty can also be incorporated. But this is one illustration I am showing as a net present value calculation as a part of financial application. But remember this type of analysis can be used for any practical case studies in real life and you can make a better prediction with uncertainty and risk analytics through the software using the Monte Carlo simulation concept. Here you can see you will not be able to understand what is where is Monte Carlo simulation, but effectively we are running the Monte Carlo simulation in each and every random parameter we are generating some random sample from Monte Carlo simulation and you are correspondingly you know generating some x value and that x is coming to the output cell in each of the row 18. And we have calculated the net present value here. I hope it is clear to everybody.