Extra Lesson 4: Estimating Pi using Monte Carlo


Introduction

Let's do something different today. In lesson 4, we learned about data tables and the Monte Carlo method. We showed that this can be a really powerful tool for risk mitigation and forecast analysis.

Yes it really is. Is there anymore to tell about it?

Well yes, I will make a small intermezzo and hope I will be able to enthusiasm the mathematicians among us.

I don't think enthusiasm and math fit in the same sentence...

Wait until you've heard my story and look at the chart below, isn't it beautiful?


Nice colors indeed, but what is it?

It's a circle!

Nice circle indeed but I repeat, what is it?

Estimating Pi

It is a scatter chart. The way to obtain it was quite easy. We just drew 5.000 dots in random locations within a square of a certain width. We did that by using the RAND() function for both the X and Y axis.

5.000 is a lot!

It's peanuts. And remember that data tables can do the job for you!

OK, what did you do then?

Well, we then determined if the dots were within a circle that was exactly inscribed within that square (the green dots) or outside the circle (the blue dots).

Sounds complicated. How do you know that?

It sounds complicated, but it is not. You remember Pythagoras? The square root of the sum of both your X and Y axis values squared tells you if the dot is inside the circle or not. Indeed, if that value is less than the radius of your circle the dot will be inside it.


I see the circle, so I see the link with Pi, but I still have no clue how you'll be able to calculate Pi.

You know how to compute the area of a square right?

Sure, it is the length of the side squared.

Correct. And do you remember how to compute the area of a circle?

Yes sir I do! It is Pi times the radius squared.

Correct again. Now have a look: the circle's radius is exactly half the size of the square's side length.

So what?

This allows us to easily compare the area of both shapes:


In other words, Pi is equal to the ratio of the area of an inscribed circle to the area of its outer square multiplied by four. And this is how we'll estimate a value for Pi

But you know the area of the square, but you don't know the area of the circle!

You're right, but I can figure out what the ratio of the one to the other is!

How?

I just need to count the amount of dots inside the circle and compare it to the total amount of dots. In other words, if I randomly paint 100 dots in the square and 75 are inside the circle, I can soundly estimate the area of the circle to be 75% of the square area.

And if you multiply that ratio by 4 you have Pi!

Well, just an estimation of Pi... And that is exactly what we did in our Excel file that can be downloaded here.



Good to know, and to remember...

  • The file might be heavy to load;

  • Again, don't use just 2 or 10 scenarios. We used 10.000 in our example, and the value of Pi is "quite OK";

  • Give Excel some time to refresh your chart.

We appreciate your feedback!


About Cintellis

Let us surprise you with how much more you can achieve by using Excel and VBA based products.

Are you looking for an on the level, reliable and trustworthy company that will surprise you with how much more you can achieve by using Excel and VBA based products? Learn more about our philosophy, products, services or contact us for more info.





About Numis

Flexible, cheap and very short implementation time, discover Numis, our Excel based Cash Management and Management Accounting Software and experience a totally new way to analyse and report on your cash positions.

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