Today, I'm going to tell you something about Monte Carlo simulations within Excel.

Well, you're right, that is where the name comes from. Monte Carlo simulations allow us to assess the impact of certain parameters in a forecasting model. I'll give an example.

Suppose you're manufacturing some goods. On one hand, the cost of these goods depends on the cost of the raw materials you need to purchase and the manpower needed to manufacture these goods. On the other hand, you'll export all these items abroad and depending of the exchange rate your activity will be more or less profitable.

You've put your accountants, treasurers, procurement officers and controllers at work and they have built a model telling you how the price of raw materials and exchange rates are likely to evolve in the future. The word

For sure! But before showing how Monte Carlo can be used in Excel, I need to tell you something about a really powerful Excel tool: Data Tables.

Let's have a closer look at the example below. You're a Europe based company that buys some raw materials in Japan at a certain exchange rate, manufactures them to some finished goods and sells them in the United States of America again taking into account a certain exchange rate.

Hold on! I don't care about the business model, it is just an example to make things clear. Now, your company treasurer or your financial controller would like to estimate, ceteris paribus, what the profit will be if the EURJPY rate changes over time.

Well you could do that, but I suppose you'd like to have an overview of the profit evolution.

Yes it will, but it will as well be a waste of your time. I'm sure you've better things to do, right? Don't feel obliged to answer that question...

Whatever, this is where

We're now all set to let Excel (and the Data Tables) do the computing for us. We need to select the table in grey we just created and go to the Data Table section of the "What-If Analysis" tab in the ribbon:

Excel then asks you the following question: "You provided me a lot of values in that first column, but to what parameter (i.e. cell) does it refer to in your model?"

Correct. So as your variable data has been stored in columns you need to put G5 in the column input cell field:

What follows next is pure magic there Excel will do all the iterations for you and will tell you what your profit will be for each FX rate:

You can barely walk and would like to run already! Anyway, the answer to your question is however an easy one: you can create data tables with two dimensions:

As shown above, you should now tell Excel the EURJPY rate is in the table column, whereas the EURUSD rate is organized in rows. Note that the cell to watch (our profit) is now at the intersection of the row and column containing the exchange rates.

Absolutely.

You want a third variable? No problem. I propose to add as well another variable to watch, like the total cost in EUR, how does that sound?

The way to deal with multiple input variables is to define upfront the scenarios you would like to assess:

Choose then a random cell in which you'll select what scenario you want to see in your model. You'll need to link the model to the scenarios using i.e. VLOOKUP's, as for instance can be seen below for the EURJPY FX Rate:

That is correct.

Exact! Glad you understood.

Well, in a model with just one input parameter, you can create as many output columns as you want like shown below: the first column refers to cell F20, the scenario description. The second column refers to cell D9, our total cost and the last one to cell D15, the profit.

Select afterwards your table and choose the cell with the selected scenario (F19) as input column. Excel will then for each scenario number modify cell F19 which in turn will affect the values in our model. The result will be shown in the data table:

What is now going to change in comparison to the above is that we'll add some statistics to our model. Suppose you've studied the past 10 years of data and you've come to the conclusion that the cost of labor follows a normal (or Gaussian, bell shaped around a mean) distribution. The mean appears to be 6,00 ï¿½ and the standard deviation is 0,70 ï¿½.

Important is now to assume that this as well will be the way the cost of labor will behave in the future. Let's now put our model at work. Please by my guest...

What have we been doing all the time?

Correct.

Excel has plenty of statistical features that are really handy. The one we will need here is the

- a probability P;
- a mean M;
- a standard deviation S.

Excel will then, given a Gaussian distribution of mean M and standard deviation P, return a value such that the probability that you'll hit a number that is less than or equal to that value has a probability of P. So Excel will return exactly 5 for the function "

Look at the example below:

What we did in column F is filling our cost of labor according the estimated distribution, but instead of using a fixed probability in the

As you can see, the average profit is 2,95 ï¿½ with a standard deviation of 0,71 ï¿½. And you can run some further statistics like estimating how much chance you'll have that the profit will be higher than x EUR etc.

Good point, but this case is for illustration purposes only. And at least, it proves the model is coherent.

This is indeed the last thing I'd like to share with you today. Suppose all variables - exchange rates and cost of labor - follow a normal distribution, all with their own mean and standard deviation of course. What we'll do is defining this in our model directly:

Well, remember that what data tables basically do is iterate. So on each iteration our variables will have another value (there the RAND() function, hence our profit as well, will be different). Again, if we let the data table iterate enough, we'll be able to do some statistical analysis on our results:

The nice thing is, the row or column input cell have become irrelevant, you should just refer to any cell though, otherwise Excel will bother you with an error message.

That is absolutely correct. By the way, the download link can be found here.

- Monte Carlo is just a model and the results should be used with care. If you don't trust the variability of your inputs, don't trust the model results;
- Don't use just 2 or 10 scenarios. A sufficient number of scenarios should be used to make the variance analysis meaningful;
- If you use a randomly generated number as column or row input cell, every time the data table iterates, the value will change. Hence there will be no link anymore between the input parameter and the measured output. That is why we recommend to add an extra variable to watch in the data table.

Are you looking for an

** Flexible**,

At Numis, we help you to get more out of your financial data than you thought possible. From enhanced reporting functionalities and detailed analysis to providing enterprise budgeting and forecasting solutions, we will help you to achieve your business objectives. When using Numis, you can share your analysis and strategy with your business partners, suppliers or employees as needed.

Numis is a product from Cintellis bvba & FS&P bvba.

CONTACT Us

You can contact us by phone, mail or by filling out the form below.

**Corporate address: **

Alsembergsesteenweg 588

1653 Dworp

Belgium

**Phone Numbers: **

+32 (0)471 65 19 66

**E-Mail: **info@cintellis.com