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.

Â