Once you have the cookies example working if you choose to go through the entire thing, then the actual goal of the profitability analysis project is to implement a Monte Carlo simulation for this spreadsheet. So I've got this spreadsheet. This is profitability analysis starter. So this is what you're going to be doing your main project on. I've got this spreadsheet here. What this is, it's a cash flow analysis, a profitability analysis of a venture. A lot of engineers do stuff like this in design work. This is also done a lot by business folks. So what I've got here is we've got different years. Maybe we're planning out to see if a particular venture is going to be profitable or not. The important thing is this thing here in cell N24 that's known as the net present value after 15 years. So that's sort of like our goal here, our goal is the yellow cell right here. And if you don't have any background in business or financial stuff that's completely fine, you can just sort of think of this as a process that has input. So it's got nine inputs, similar to the cookies example. The inputs are in green and these inputs affect the outputs. So if I change, for example, the working capital here to something like negative 40, then you see that that affects the net present value here. All of these inputs in green go into calculations on this spreadsheet and that's completely fine if you don't understand all the calculations. Then they go into this output. So, inputs and output. What you're trying to do then with this Monte Carlo simulation is just to simulate all of these different inputs, each input has a different distribution associated with it. And then you're trying to do maybe a thousand simulations, similar to what we did in the cookies example, and then you're going to kind of plot this and to analyze the percentage of the simulations that lead to a positive net present value at year 15. If you're interested, I'll just really quickly go over the analysis here, if you're interested. If you're not, just pass on through this, but cash flow in any given year some but generally not all of the following terms will apply. So each year, you're going to have a cash flow. We've got one minus the tax rate, times sales minus costs. So it's like sales revenue minus costs plus your depreciation minus we have total depreciable capital, working capital, the cost of land. So the cost of land is usually in the first year or two, startup costs are only in the first years, a couple of years. We have cost of royalties that can apply every year, depreciation by the way, applies to most years, so your equipment depreciates. You have your salvage values at the end of the venture. Let's say you are only doing a 15-year venture like a power plant or something, then your salvage value at the end you can recoup that, so we have a positive cash flow in that year. And then at the end, you can usually get some working capital back, particularly if you're working in a big company, you can recoup that working capital. In order to be profitable, the net present value of the venture must be positive at the final year and then each of the inputs has variability that I'm going to describe here in the next slide. So those are project premises. Again, we have nine different variables, nine different inputs, so these here are inputs. Each of them is described by a different distribution. I'm not really big into finance and business, so I've just sort of randomly chosen some of these. If you're working on a real-world project, you're probably going to have some sort of background knowledge about the distributions, the underlying distributions of some of these variables that go into your process. I wanted to note that the Monte Carlo simulation is not just for financial projects. A lot of scientists and engineers use Monte Carlo simulations to predict what's going to happen in some sort of complicated process, because you've got all these inputs that may have different distributions and different probabilities and you want to kind of see what the probability of getting an output is. So here, this is going to be sort of where we base our project on. We've got some discrete variables in here like the cost of land. Maybe we're looking at three different properties of land. There's a 20 percent chance we'll get a cheap piece of land for three million. By the way, all of these values are in millions of dollars, but just work in the millions taken away, so just work in these numbers. Also very, very important, anything that's in parentheses means that it's negative and you business and financial folks probably know that. So for example, this $3 in parentheses here for the cost of land, that's negative because it's in parentheses, that's a cost. So, we would have to spend three million. And again, it's millions of dollars, so this really represents three million. But we've got some normally distributed variables, some Beta-PERT variables in there, some uniform variables, and so on. So let me just go ahead and show you what your projects should look like at the end and I have one set of simulated variables that I'll show you in this screencast. On the problem statement on the website, I've got a second scenario that you can kind of check your solution, so you can kind of check to make sure. And then when you get this graded, your peer reviewer is going to go through a third scenario. So, a third combination of different variables to sort of check versus a more or less a solution to see if your project is working properly. So here's my project. This is my solution. And let's go ahead and run this, so I run the simulation, and we've got the user form here. I also have a sort of a screenshot of the user form on the project statement with different values in here, with a different scenario. But this is where you can kind of go through and change the different parameters. And when I do maybe a thousand simulations, it cranks through here and you can see it changing, so it's going through 1000 simulations. It's plucking out the net present value for each of those and it says about 94.8 percent of simulations were profitable, and it also spits out a nice histogram. You can see here on the left, those are negative net present value. I should also note some of you might be wondering what net present value is, just because net present value is negative doesn't mean it's not profitable. It's just not as profitable as putting into an investment with this interest rate. So it's really net present value as a comparison to what you could be making in something like the bank or in a typical investment. So it's really telling us if it's better than a typical investment or worse than a typical investment. So, even if net present value is negative, that still means we're making profit. We're just not making as much as if we put our money elsewhere. But we're getting about 95 percent of the scenarios are profitable, which means that this is probably a good venture to invest in. So that's a demonstration of how your Monte Carlo simulation should work. Thanks for watching and good luck with the project and feel free to post on the discussion forums and help each other with these projects.
