0:36

To do this we are going to introduce a new a problem.

We want to develop a simulation model for a three year financial analysis

of total profit based on the following data and information.

Sales volume in the first year is estimated to be 100,000 units and

is projected to grow at a rate that is normally distributed

with a mean of 7% per year and a standard deviation of 4%.

The selling price is $10 and the price increase is normally distributed with

a mean of $0.50 and standard deviation of $0.05 each year.

Per-unit variable costs are $3 and annual fixed costs are $200,000.

Per-unit costs are expected to increase by an amount normally distributed

with a mean of 5% per year and a standard deviation of 2%.

Fixed costs are expected to increase following a normal distribution

with a mean of 10% per year and a standard deviation of 3%.

We want to examine both the annual profits and

net present value with a discount rate of 7%.

Let's take a look at a simulation model for

this problem, located in the Excel file, financial analysis.

The workbook has five worksheets.

One with the simulation model, and

then one for each of the charts that we're going to discuss.

We will however, build the charts using the ASB.

We will work off the Financial Analysis Model worksheet.

The first table in the mold has all the assumptions associated with cells,

price and costs.

The second table contains the simulation model.

All the values in the first year are fixed.

We have a sales volume of 100,000 units,

a selling price of $10 and a revenue for the first year of $1 million.

Since the unit cost is $3 and there's a fixed cost of $200,000,

the total cost is $500,000.

This results in a profit of $500,000 for the first year.

The uncertainty in this model is in all the green cells.

There are four sources of uncertainty.

The sales volume, the price, and the variable and fix costs.

The profit for each year and the net present value or

NPV, of all profits are the outputs of them all.

They are in cells B19, C19, D19, and B21.

Click on the analytics of a platform and

make sure that the number of simulation trials is set to10,000.

Now run the simulation by clicking on the play button in the solver options and

model specifications panel.

The first chart that we are going to exam is a frequency chart for the MPV.

Double click on the cell B21 and the chart pops up.

We're going to get rid of one the markers and

set the remaining marker to a value of 1.5 million.

We observe that the mall estimates a 38.66%

chance that the MPV for this investment will be at least 1.5 million.

Now, we want to know what is driving this result.

The mole has A to certain cells from the four sources of uncertainty.

These are the green cells in columns C and D and rows 13 to 16.

So which of these sources of uncertainty

are most likely related to the net present value problems?

To answer this question we go to the sensitivity tie.

The chart shows the correlation of uncertain variables with the output

in this case the net present value.

The chart shows the uncertain variables by their correlation strength

the most correlated variable is at the top of the chart.

And the least correlated variable is at the bottom.

This chart is sometimes called tornado chart because of its shape.

The blue bars indicate positive correlation and

the red bars indicate negative correlations.

The chart shows that cells C13 and D13 are the most correlated with the NPV.

These cells contain the cell's volume for years two and three.

What this tells us is that we should try to obtain very good estimates for

these numbers because they are the ones that have the most influence

on the net present value.

Which is the key output that is going to drive the decision of whether or

not to invest in this opportunity.

At the same time, the chart also indicates that having a precise number for

the selling price on year three is not critical

since it has very small correlation with the NPV.

Note how D14 appears at the bottom of the sensitivity chart.

Close this window and examine the annual profits.

In our model, the profit for the first year is fixed.

We're assuming that either there is no certainty or the certainty is negligible.

For the second and

third year, we would like to compare the distribution of profits.

We can do this with an overlay chart.

Click on Charts, go to the Multiple Simulation Results, and

choose Overlay Chart.

In the output section, choose C19 and D19, and

click on the right arrow to move them over to the right box.

Recall that C19 and D19 are the profits for years two and three.

Click OK. The distribution of profits

in year two is shown in orange and clearly it has a smaller mean

than the distribution of profits in year three, which is shown in blue.

The chart also shows that the variance of profits in year

three is larger than the variance in year two.

This is reasonable given that there is

more uncertainty in predicting profits further in the future.

Similar information can be obtained with trend charts of annual profits.

To build a trend chart, go to Charts, Multiple Simulation Results and Trend.

This time, choose B19, C19, and D19, and move them over to the right.

Click OK.

The trend chart shows the mean values of each of these variables and

also the 75% and 95% band.

Since the probability for

the first year is fixed, there are no bands around the mean value.

The bands get wider in the future because there is more uncertainty.

The positive trend indicates that the revenues are projected to

grow faster than the costs, resulting in higher profits from year to year.

In this video we have seen how charts are valuable

tools to gain insights from simulation results.

The frequency chart helps us

answer probability questions related to an output.

The sensitivity chart tells us which of certain variables are most important.

The overlay and trend charts gives us insights on output variables.

There are other charts that we haven't covered here.

But I encourage you to explore further

either with the analytic solver platform or using a similar software.

Because most likely you will find a chart for

almost anything that you would like to analyzed.