In this module I introduced two tools for extending the use of your spreadsheet models to help you make better business decisions. The first was the use of Monte Carlo simulations. The second was linear programming for the purpose of optimization. My focus was on how to implement those methods within spreadsheets. Other courses within the Business and Financial Modeling Specialization cover the theory and application of these methods in much greater depth. I encourage you to explore those. Throughout this course, I've mentioned that both Excel and Google Sheets have the capabilities you need to build useful business models in a spreadsheet. Let me finish here by illustrating the many similarities and a few differences between these two popular spreadsheet applications. So one more time let's turn to the spreadsheet. In the demonstrations for this course, I intentionally used formula's and syntax that could apply both to Excel and to Google Sheets and I'm showing you Google Sheets now. So for example, here's the historical sales data we looked at in Excel. Notice that virtually all the functions we have used in Excel work the same in Sheets. They include for example the sum function here in Q3, the minimum function in N3, the maximum function here, as well as the average function. There's almost an identical usage in Excel and Sheets for all of these functions, including some product, brand, brand between, forecast, growth, the EXP function, the correlation function, and range names. There are a few syntactical differences. For example, use of the standard deviation formula in Excel has been discouraged in favor instead of a standard deviation dot P or a standard deviation dot S reference for suggesting that you're looking at either an entire population or a sample from a population. The syntax in Sheets is slightly different, in that they don't use a period between the STDEV reference and the P, and that's the source of this error that needs to be corrected. As I pointed out in an earlier demonstration, the statistical add ins for Excel and Google Sheets are also different. In Sheets I recommended the statistical add ins from XLMiner. The XLMiner Analysis ToolPak includes all of the functions we saw previously in the Excel Data Analysis ToolPak. For example, that includes the random number generator that we used in Excel. With various distributions that include uniform or normal distribution or the Bernoulli distribution that we used. The Excel minor analysis tool pack also has tools for regression analysis, much like the regression analysis we used in Excel. One current difference between Sheets and Excel is that there's no sheets feature like the Excel Scenario Manager. If you recall, that's the tool we used earlier to store a set of assumptions. For example, a set of assumptions associated with a high growth scenario versus a set of assumptions associated with a business turn down. Google sheets does have a great version manager and that might help a bit, but it's not the same as the scenario manager. If you have a choice, as of 2016, Excel is clearly faster with very large spreadsheets, let's say those with tens of thousands of rows. At the same time, Google Sheets has worked harder on the collaboration interface, and is stronger in that regard, at least in my opinion. I'm sure that many of you have your own opinions about that as well. Fortunately most of us can use both. Now let's summarize module four. In this module we practiced creating two classic types of business models. A Monte Carlo simulation and a linear program optimization model. We begin by reviewing the types of probability distributions available, looking at the Bernoulli distribution for the first time. We explored a new spreadsheet model layout that simplified the running of the number of simulation trials with our model, allowing us to test thousands of different random variables. We practiced the four steps involved in Monte Carlos simulations. That is, we identified probability distributions for the variables in our model. We generated random variables, based on those probability distributions. We ran a large number of trials using our models. Then we summarized and analyzed the results to evaluate risk. We learned how to use the empirical rule to evaluate risk. As an example, we explored how risky it might be for an entrepreneur to quit her job and work full time on a new venture. Next we turned to linear programming and the use of the solver tool in Excel. We looked at a resource allocation problem and worked on a model to identify the best mix of product types for an optimal production run. We practiced how to lay out the elements of a linear programming problem for use with solver and then we used solver to automate the what if process, the one that we had used previously, and identify an optimal production decision within a set of constraints. Finally, we looked at the differences between Excel and Google Sheets. We saw how most basic operations and functions were either identical or very similar. We noted that there were some differences in syntax. We also saw that Sheets lacked the scenario manager functions available in Excel. Sheets also requires third party plug ins for both the statistical tool packs as well as the solver function. We looked at some of the suppliers of fee based add-ins that provide a more robust tool set, both for linear optimization as well as for other types of models.
