Parameters variation in fundraising financial model

Question

I have created quite a large financial model in Excel with lots of input parameters which (after all calculations) have an influence on the output business indicators.

Among the input parameters are

• the expected average price of product;
• the expected number of purchases in first day;
• the expected demand growth rate;
• the market size limit;
• and so on.

Among the output indicators are

• amount of investments required;
• pay-back period;
• IRR;
• the absolute profit on 3rd or 4th year of project execution;
• and so on.

I want to investigate different scenarios of project execution by varying input parameters in certain range. The goal is to find out the worst possible, the best possible and the most likely scenarios and to prove project attractiveness from the risks point of view.

Are there any well-known scientific approach to such kind of modelling? (One of the main caveats here is that the input and output data is multi-dimensional)

Answer 1

Yes, a Monte Carlo simulation (MC) is what you need. It is a well known and documented approach with many uses in finance, science and engineering. MC simulations are used to simulate the returns of complex financial assets or in your case returns of business ventures under uncertainty.

Your input variables ($x_1, x_2,\cdots, x_n$) are uncertain. If you select values for your input variables you can feed them to your model and get the result vector ($y_1, y_2, \cdots, y_m$). You want to calculate the expected price for each of your output variables but also best and worst case scenarios. You can programmatically feed your model with thousands of input vectors to get a distribution of $y_1, y_2, \cdots, y_m$. The distribution will give you access to VaR types of measures (loss will not exceed this level 95% of the time).

An MC simulation will require VBA. Luckily there are a lot of resources online and it is not difficult to implement. Here is a MC with Excel/VBA tutorial by the "Excel Ninja".

1. Determine a probability distribution for your input variables.
2. Are your input variables dependent? Then you might need a correlation matrix - and you will need to get its Cholesky decomposition and perform some linear algebra, which would increase the complexity of your solution.
3. Create a loop to feed your model with the random generated vectors trough VBA. Save the results for each simulation on a worksheet.
4. Calculate measures like expected return, VaR at various confidence intervals and visualize the results.

Answer 2

Your question is little broad and has two aspect: Theory and Application.

If you are interested in scientific approach and academic literature this kind of thing is called Mathematical_optimization which is branch of Multi-objective optimization which is again a branch of Operations_research. In terms of mathematics of solving these problems multivariate calculus is the foundation for it.

A very relevant research paper which uses Monte Carlo simulation with both DCF and real options risk pricing techniques to evaluate an actual project financing proposal for a small gold mine, that link is here.

Now for real-world applied solution if it is in highly rich corporate setting some people use Oracle Crystal Ball & Decision Optimizer which integrates with excel where this DCF analysis is being done. For a medium cost solution you can use @RISK software from Palisade which also has excel and VBA support. A relevant case study from them for DCF is here.

Answer 3

I would try a Gradient Descent algorithm if possible, although this might take a little VBA knowledge. In general, finding the absolute minimum of a multidimensional problem like that is complicated and time consuming, but the gradient descent will find a local minimum easily.

What you will need to do for worst case scenarios:

1. Construct a cost function that can fold all of your outputs into one "cost", or simply choose one output to minimize or maximize.
2. Choose a starting combination of inputs that you think might be close to the worst-case scenario.
3. Calculate the "cost" term.
4. Evaluate the partial derivative of your cost function with respect to each input. Adjust each input such that $input_n = (input_n-1)*cost*derivative*(LearningWeight)$, where learning weight is some constant you choose to control the speed of adjustment.
5. Repeat with new inputs until the change in costs is less than some small threshold you set.

What this basically does is at every step, move the inputs in the direction that most increases the cost, like finding the top of a hill by going uphill at each step.

You can repeat for best-case scenarios just by switching the sign of the cost function.

If this is too time-intensive, try a Stochastic Gradient instead.