Sometimes in business, changes happen that are easily captured, using the kinds of linear models we have used so far. For example, in the case of the costs associated with manufacturing speakers, a linear function can capture the fixed costs of maintaining a manufacturing capability, and add in the variable cost of each unit. That type of formula is a linear function. Occasionally, the activity we are modeling is changing at a non-linear rate. For example, if we are starting a new online social network, we hope to see adoption of new users at an exponential rate. In this lecture, we will look at two examples of models that illustrate linear growth over time and compare that to an example of exponential growth. In those models, we will use power, exponential and log functions. Let's look at an example in a spreadsheet. Here are some examples of linear and non-linear functions in spreadsheets that model change over time. This is an example of linear growth through a constant increase per period, in this case year by year. The formula for cumulative deposits Here in C9. I wrote in order to show an absolute reference to cell B4. I can change the deposit amount to any number in cell B4 and have an effect on the entire projection This second model is an example of proportionate growth. That type of growth is a constant percent increase from one period to the next instead of a constant amount. In this example, I added an assumption cell here in cell B5. With an interest rate that we hope to receive for our deposit. It's currently set at 1.5%. As you can see in cell C9, I edited the formula for the value of our deposits to allow for that 1.5% rate compounding over each year of ten year horizon. In this output cell, B6, you see the result of that compounded interest. I've referenced here the value of our account from year 10. In this spreadsheet, we're modeling exponential growth. The scenario here is not a happy one. It's the exponential growth of cases of an epidemic. There are many examples of business scenarios in which exponential growth of a variable are involved. You saw this in an earlier model, in example of rapid customer growth. Exponential growth can also be negative, meaning exponential decay. For example, when an older technology is displaced by a newer and better one, and sales drop off exponentially. In an exponential function, the variable of most interest is not the base value here shown in A5, it's the power or exponent in the calculation, in this case shown in cell A6. In the case of this scenario, the value is .15 as the growth rate per period. That variable can be positive, resulting in exponential growth, or, negative, meaning exponential decay or decline. For example, if this were a negative 0.15, you'd see the formula in cell B13 change to show instead of 46, a decline to less than 5 cases in the forecast. The base in the equation is the mathematical constant called e. This topic is covered in other courses in the Business and Financial Modeling specialization, and I've posted some links for your further reading on this topic. For our purpose here, let's look at implementing the exponential functions in spreadsheet models. This scenario begins in time period 0 with 15 cases of a disease in a remote location. That's the value in cell A5. Growth rate per week is exponential at -0.15 and I'll return this to its positive form. And that's located in cell A6. It will take about 7.5 weeks for or doctors to establish a local clinic capable of dealing with the outbreak. We enter that in cell A7 as our time variable. How many people will those doctors need to plan on treating? Well the objective function to determine that is located in cell B13. Pb equals the number of infections at the base period 0. R is the rate of growth per period. T is the number of periods after the base. So, I wrote the formula using the cell addresses for Pb. R and t, those being A5, A6, and A7. The EXP function in Excel, calculates the mathematical constant E using the exponent of growth times the time period. Let's say the doctors are delayed, to maybe ten weeks. Here's the number of infected patients expected by the forecast, 67 or so. Let me share with you two more quick shortcuts available in Excel for forecasts that are based on historical data. The first is the function forecast. This function is used to calculate the next point in a series of data that appear to be growing or declining based on linear functions. If I show you the formula in cell H3, you'll see that the function first asks where the next X value is. In this case, next year that is 2017. That's located in cell H2. The second parameter is the series of known Y values. That is the prior five years of sales. That's located in cells C3 through G3. The third parameter is the related set of x values, or in this case, the reference to the actual sales years found in cells C2 through G2. This second shortcut, the function GROWTH projects the next number in a series that is growing exponentially. This function works a little differently from the forecast function. The first parameter is the series of known Y values, the prior year's sales. The second are the X values or the years themselves. And the third is the X value we're interested in, that is next year. Notice that the 425,000 number is higher than the 393,000 we saw when we were using the forecast function. The question for us, then, is which number fits the data better? If I add a plot for the past five years of sales, I get a chart that looks like this. The data seems to fit a straight line better than an exponential curve. It seems more likely that the real sales for next year will be closer to the 393. That was calculated by the forecast function, rather than 425, calculated by the growth function. So I think the right choice is probably forecast.
