Spreadsheets include a variety of tools for incorporating uncertainty and probability in our models. In Excel, the rand and the rand between functions can be used to generate random numbers. In this lecture, we'll look at ways to use rand and randbetween, with some historical data to probe a cash flow model. We'll develop a forecast using a randomized assumption, regarding demand for products and services. We'll review some probability distributions and see how they effect our model and what they can tell us about any risks we might be facing. We'll also review some of the more commonly used statistical functions in Excel. Let's look at the spreadsheet model. Here's some historical data on the past three years of sales. Let's assume we're looking back from the future in the year 2019. I've used the Min and Max functions that you see here in columns N and O to identify the lowest and highest sales numbers per year. So for example, the Min of cell range B3 through M3 or the Max of that same range. In R4 and R5, I calculated the year over year increase. So in the most recent year, that means there were 38 units as a minimum. And 60 units is a maximum for our monthly unit sales. And the question now before us is, what's our forecast for next year? I could just use the average of what's in row 4 for 2017 is an increase over 2016, that 15% increase and possibly average it with the next year's increase and apply that to the number 608 that we totaled for last year and divide that across the 12 months. On the other hand, it's worth the effort to consider some of the randomness that happens with sales in a real marketplace. For that I can use the Excel function randbetween to introduce some uncertainty in our forecast between the worst and best cases per period. So as you see here, I've projected sales for 2019 from January through December, and I can use the function randbetween- Which takes two parameters. The worst case, or the lowest case, and the highest case. Now I could just use the two numbers that I'm familiar with form last year as the minimum and maximum. That would be 38 and 60 However, it's always bad to bury our logic inside of a formula if you can avoid it. So instead, let's reference the cell addresses that indicate the minimum from last year. That's here in cell N5 and the maximum from last year which is in cell O5. So our random projected sales for January based on that range of values is 44. So in this case, the low and high values in the randbetween formula use cell references and in cell N9, I pulled together the minimum from last year, and multiplied it times my average year over year increase.. I did the same for the maximum, which is in cell O9. For my random numbers, I now use these min and max calculations as the high and the low. For example, here's the January forecast of 64. Now notice each time I recalculate the spreadsheet, which I can do using the F9 key, the new random values are shown. If that's distracting or for some reason not useful, you can fix the current random values by simply copying their current state- And pasting them in the row below. But don't paste them as a regular formula, paste them just as the values the formula generates. Then, if I recalculate the sheet, you'll notice that January 2019's row nine projection changes, but row ten remains the same. If you repeat those steps ten times, you will then get ten forecasts. So here are those ten randomly generated values for the monthly sales that we're using to forecast next year. First I'll copy our forecast from cells B10 to M10. And I will paste those into the full cash load projection that looked at earlier. I'll paste those into the row 18, where we have projected sales by month for next year. You'll see now that the cash flow minimum margin and end-period cash balance reflect this first randomly generated projection for sales. Next I'll copy and paste these new results, pasting again as values. And I'll put those in column D, so I can refer to them later. If you repeat that process ten times, You'll now have a range of forecasts for the full cash flow that includes, in one scenario, negative numbers. Randbetween uses a uniform distribution for pulling a number. That means that each number between your high and low value has an equal chance of appearing. It's possible that your numbers are more normally distributed. By that I mean the very high numbers and the very low numbers don't occur as often as the average numbers in the middle. This is part of the concept of a normal distribution. There are other types of distribution as well. Distributions are covered in more detail in another class, in the business and financial model and specialization but I'll show you here how to replace the uniform distribution we are using with the randbetween function with a normal distribution instead. And I'll take this opportunity to introduce you to the statistical tool pack. From the Data menu, choose Data Analysis. Then scroll down if you need to to the Random Number Generation option and click OK. Let's say in this case that I want 100 random values for the sales variables for our monthly forecast. So the number of variables I want are 12 and the number of random numbers i want to generate are 100 rows of them. The distribution in this case is not uniform as was the case with randbetween, but rather normal. The mean I take from spreadsheet for average, so the mean from last year was 50. And the standard deviation I take from last year as well, which was seven. I've indicated for the output range to put it in cell B10 which is where January's forecast will be. When I click OK, We have now generated 100 sets of monthly random projections for our sales that meets a normal probability distribution. So far, we've created 100 sets of random numbers that are normally distributed and based on historical data, and we plan to use those numbers as our sales forecasts In our cash flow projection model. We're not done yet. To get our forecast more in line with the uncertainty in the real world, we would need to run that same process on all the sensitive variables in the forecast model. Then we would need to use those sets of random input variables in our forecast many times and calculate a set of forecasted outputs. But when we're done, we can calculate the standard deviation of our results for example for the profit margin. So if I create a standard deviation formula in this cell, and choose the range for the ten simulated runs that we've done thus far. I can see that the standard deviation is roughly 1.5%. Statistics tells us that in a normal distribution we would see margins between 17% and 23% about 95% of the time. Assuming the mean is 20% and the standard deviation is 1.5. That means there's a risk of 2.5% that we will see margins less than that. That's helpful, but it took a lot of detailed manual work to get to those results using standard spreadsheet tools. Fortunately, others have built add-ins for use in spreadsheets to make that process a lot simpler. Unfortunately, those tools are not generally free, but so far we've seen how to implement random variables in normal distributions in spreadsheet models. We'll pick up on this topic in a later module when we look at simulations. And we'll take a look at those tools I mentioned that make this work easier to do.
