Usage of the Monte Carlo Simulation in Microsoft Excel

Company Valuation with Discounted Cashflows

Implementing a way to reflect different possible cases in your company valuation

Understanding the effect of the different variables on the calculated company value

Creation of Monte Carlo Simulations without buying expensive Excel add-ins

In my Online Course we will create together an Excel File that uses the Monte Carlo Simulation for various variables necessary in a company valuation. I explain the steps to value a firm with the discounted cashflow method and we will complete an example valuation on screen to deepen the understanding of various Excel formulas and financial modeling.

At the End you will have an example Excel File to quickly value any firm you want by taking into account the uncertainty of future values.

You will have the knowledge to adapt the Excel File for your own needs, to extend it if you want to include more variables or to increase the accuracy of your valuation.

In addition you will be able to perform Monte Carlo simulations with different probability distributions in Excel for any topic.

1

Presentation

2

The Monte Carlo Simulation in Theory

In this lecture you will learn :

- What is a Monte Carlo Simulation

- We will have a look at a very easy example

3

Steps to a successful company valuation

In this lecture you will learn:

- Which financial indicators you need for a discounted Cashflow Valuation

- How to calculate the Free Cashflow to the firm (which is the basis of the discounted Cashflow Valuation)

- What is discounting and why do we do it?

- Formula for discounting future Cashflows

4

Presenting the Final Result

In this lecture you will learn:

- How the final result of the Online Course looks like

- What the different Excel Sheets are used for

1

Starting a new valuation

In this lecture you will learn :

- Which are the basic information necessary for a valuation

- Where to find the number of outstanding shares

- How to calculated the market capitalization of a firm

2

Importing the Source Data

In this lecture you will learn :

- Where to get the financial statements of a firm

- Which statements you need for a valuation

- How to import the data in Excel using the Excel import tools

3

Calculating the Free Cashflow

In this lecture you will learn :

- How to calculate the Free Cashflow to the firm

- Which information from the financial statements you need for that

- How to calculate the portions of the revenue for these values

4

Cost of Debt

In this lecture you will learn :

- How to calculate the cost of debt of a firm

- Which values you need from the financial statements for the calculation

- Necessary only if you want to use the weighted average of cost of capital (WACC) as a discount rate

5

Defining the Discount Rate

In this lecture you will learn :

- How to calculate the discount rate from Earnings risks

- Where to find the risk free rate

- Using the MSCI World as a proxy for the market return and risk (standard deviation)

- Calculating the variation coefficient of the earnings

1

Simulation of Revenue, EBIT Margin and R&D adjustment

In this lecture you will learn :

- How to implement a Monte Carlo Simulation with 10 000 cases in Excel

- Understand the influence of expected value and standard deviation in a normal distribution

- Estimate the future revenue growth of the firm with the Monte Carlo Simulation

- Estimate the EBIT Margin in the future with the Monte Carlo Simulation

- Implement a new variable in the simulation (R&D adjustment) and estimate its future values with the Monte Carlo Simulation

2

Simulation of CAPEX, change in Working Capital and the Tax Rate

In this lecture you will learn :

- How to estimate the future Capital Expenditure with the Monte Carlo Simulation

- How to estimate the future depreciation with the Monte Carlo Simulation

- How to measure and estimate the future change in Working Capital with the Monte Carlo Simulation

- Understand the different variables that influence a triangular distribution

- How to estimate the future tax rate using a triangular distribution for the Monte Carlo Simulation

3

Estimating Future Cashflows

In this lecture you will learn :

- How to combine all the simulated values

- How to calculate the Free Cashflows in the Future (distinct for each year)

- Arrive at your "Planed" Free Cashflow Calculation for the firm

4

Finishing the Cashflow Calculation

In this lecture you will learn :

- How the discount the Free Cashflows of every year

- How to calculate the TV of a Free Cashflow row

- How to take into account the bankruptcy risk of a firm

- How to discount the TV

At the end of this lecture you will arrive at the total enterprise value.

1

Calculating the enterprise value

In this lecture you will learn :

- How to arrive at the Equity Value of the firm

- How to estimate the future share price

- How to analyze the outcome of your Monte Carlo Simulation

- Measuring the potential return of an investment in this firm

- Calculate the probability of a gain (or loss) from your simulated share prices

2

Summary and Outlook

In this lecture we will :

- Look over all the Excel File again

- I will sum up the work that we did together

- Farewell

You can view and review the lecture materials indefinitely, like an on-demand channel.

Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!

Register New Account

Log In