 So, we have seen these, we use a differential equation of A that is adoption rate, AR is nothing but dA by dt which is A is adopters and we solve to get the equation in the algorithm of A t by P t minus the algorithm of A naught by P naught is C into I into t. It can be further rearranged to give equation 3 as well as equation 4. This equation 4 is also called as a logistic model, a special case of which will be a sigmoid curve. If you come across that, it is a similar kind of model that we have seen. When does max adoption of sales rate occur, we figured that also. Now, let us get back to our example and we want to estimate the parameters. The first approach is to use equation 2 to estimate the parameters. We are going to do log transformation, linear regression to estimate the parameters. Since only C into I can be estimated, C and I separately cannot be estimated, we do not have enough data for that. So, we will just take the product and estimate it. So, before we go to the spreadsheet, let us just quickly come back to our notes to see what is it that we are going to estimate. In approach 1, we are going to estimate parameters from equation number 2. We know that logarithm of A by N minus A t is equal to logarithm of A naught by N minus A naught plus C into I into t or logarithm of A t by P t is logarithm of A naught by P naught plus C into I into t. Again, note that P t is N minus A t. So, you have this. This is in linear regression form, the form of y equal to A plus B x. C is a constant which is nothing but this. B is nothing but C into t, x is nothing but your C into I, rather B is C into I, x is your t. So, that is what we have. So, this is your y variable, this is your A, C into I is your B and t is your x and we can get the product. So, if you have these two data values, we can plot a simple linear regression of it to find the intercept and the slope. Let us put it precisely what we are going to do. This is of the form, then I am going to use linear regression to estimate A and B. So, if a confusion let us just call it small a, we already have capital A in use. So, just avoid confusion. So, then we will use regression to do that. That is precisely what we are going to do. Let us go back to a spreadsheet. Let us go up to the spreadsheet. So, before taking logarithm, we need to figure out what is the potential adopters. So, for the first approach 1, you can see on the right side of the spreadsheet that is from column M onwards. First is step 4, calculate total population N. So, we need to guess some value of N. So, for that you can just check what has been the total cumulative sales so far. It says 7900. So, that means that is the total number of adopters it has been. So, any number greater than that is to be sufficient. So, for basic purposes let us just assume that it is 7900 at this bowl. So, you can just write the number as 7900 as the total population N. Now, if you approach the approach here to column H, you need to compute the Pt which is N minus 80. The formula needs a little update. It is dollar over dollar 6 minus F2 because total N does not change. So, update the column, update the formula minus the actual sales 80 using that you can compute a values of P. So, you do that, you drag it downwards till row 29. So, you should see that the value of P goes in the opposite direction as A which is as expected it goes from 7900 to 0. So, once you do that we need our Y column that is nothing but logarithm of A by P. Sample has already been calculated for us logarithm of A divided by P. So, I am going to simply drag that column all the way to row number 28. So, I have done till step 6 in our approach we have done till step 6. Now, step 7 is to plot column I versus time. So, to do that I hope all of you know how to plot it in excel. Select the column I click insert click a scatter plot as soon as click a scatter plot you will get this right click select data because you want to set the x axis data x axis should be the time that series that we have got right by default it just take 1, 2, 3, 4 we just select the go to series 1 select x values and the x values are 0.25 to 0.65 in column D. You will get a curve like this or a plot like this you can move the plot down sorry it does not hide any of the figures or text in the text you have. And if it is a regression it is quite simple in excel you can just right click the data series or any of the dots right click it click add trend line by default it will be linear select linear display equation on chart display r square value on the chart and you are done. So, this graph you can see here we just selected both the columns other value is time we selected time as x axis and we selected logarithm of a by p as the y axis values we plotted them and we fit a regression line linear regression and we got a equation y equal to 1.5509 x minus 5.2674 with r square of 0.94. So, let us write these values. So, the intercept is minus 5.2674 the slope C i is 1.5509 r square is 0.94554 even if you did not get it you can copy the values that I am just typing here into the boxes that has been provided for you in column O step 8. So, you got the intercept slope as well as r square values. So, from this the intercept is nothing but logarithm of a naught by p naught right. So, we need to calculate a naught how will we do that? Logarithm of a naught by p naught is equal to minus 5.267 and p naught value what is initial value of p then go up and see initial value of p is 7900 that is also given. So, value of a naught will be e power the intercept value into p naught which is nothing but total population n. So, I just wrote equal to exp of o11 multiplied by o6. So, a naught comes to 40.7. So, that is the initial number of adopters it says is 40.7. As soon as you enter the values you will see that in column K the first two rows are filled. Now, we are moving to step 10. Step 10 says use equation 4 to complete fitted A. Now, I need to compute the sequence for A and for A we derived that equation 4 which gives the expression for A which is nothing but shown here in this formula n divided by 1 plus what is it? n minus a naught by a naught into e power minus c into i into t which is what is coded for you. So, if you drag this since now this A t is just dependent on time t you can drag it even beyond 6.5 because you are just now using it to estimate the value. So, we are actually doing a little bit of forecasting. So, you can drag the second row downwards. So, these values are fitted cumulative sales A after completing column K you scroll down you will see two graphs the blue line corresponds to the actual data and the red line corresponds to the fitted data is an approximate fit. So, let us see how the fitted data corresponds to the actual sales data that is step number 11 computed fitted sales per year. So, if you drag the formula for that all the way down. So, you see this graph that appears x axis here I have just all the formulas are prefilled. So, if you fill the basic values and drag it you should get this curve. So, red curve gives the fitted curve which is kind of an approximation for this given data. So, to get this curve you need to fill these values in column O you can take a quick look at it you can fill these values and just drag the formula it should work for you ok just passing for a minute. So, you can see enter it intercept is minus 5.26 slope C i is 1.55 A naught is 40.7 even if you do not know the formula you can just type the number. Now, let us interpret this graph idea is not just fit some curve it should be an accurate fit you can observe that initially the model seems to be underestimating here here it seems a reasonable fit peak it seems to overestimate it seems to overestimate the peak and again starts underestimating the tail or rather the second half of the curve it seems to underestimate heavily here it seems to overestimate it here. The result is this cumulative graph which seems to kind of underestimate in the starting period and then overestimate here and towards the end kind of reach it much earlier than this can do. This is figure we have instead of this one approach see this approach is heavily dependent on your initial value of population instead of this initial value of population we can use any other values we could have put any other values of C and i to see what happens or can we use that existing equation number 4 to directly solve this which is what we are going to do in approach 2. In approach 2 we are directly going to use equation number 4 and we are going to choose parameters n A A naught C into i so that the least square error is minimized. You can do it in a trial and error method we do not need to convert into regression model trying to do a logarithmic regression and if it is not that great we can try to see how we can do that. So, in your same excel spreadsheet go to sheet number 2. Again the first up to column D is exactly the same as the old one we had the sales data ready convert into B by B2 by 4 drag the sales data some values in column O you will see it is already prefilled for you that is it 7900 slope is 1.5 A naught is 40 we do not know how we got it assume we got it we can always fit this curve A t which is what let us complete the cumulative sales and the fitted equation we know the equation so I can just simply drag the equation till the end and I can compute the square error for it and I am going to drag the fitted sales also along with that. Now, you can see that if you are going to change this initial population I am just going to focus on column O and if I change that initial population suppose into 7900 I put 8000 you will observe that the sum of squared error will change you can just take any number 8 1 or 7 9 5 0 the square error changes and if A naught changes from 40 to 45 again square error changes you just drag all the formulas that is written except for column E where that should be the sales value divided by 4 not time divided by 4. So, if I change those values I am going to get arbitrary fits what is it fit? I request you go back to sheet 1 and if you scroll I just put everything on one graph the green line. So, this green line will keep shifting for whatever values I am going to enter in the sheet number 2 but I want the best fit. So, to do that we can actually use the optimization solver to fit it where I minimize the mean square error. So, do that you need to go to tools excel add in include the solver. So, if you have it then you will go to tools if you do not have it go to excel add in add the solver go to solver set the objective as 11 I have given what is to be entered by changing all these 3 values nci and a naught again what is to be entered I have written it here right here I have entered it here what is to be entered and that is it you do not need to worry about any constraints just solve it keep solver solution okay okay oops I maximized it sorry data tools solver minimize it yes minimize the mean square error by changing the cells solve it okay I need to set it at initial values slope is okay. Once you solve it in this case we got a estimated total population of 8,075 with a slope of 1.24 and a naught as 18,9 and automatically we have seen that all your h and i's would have changed on case. So, you can simply go to sheet 1 and observe that I am going to another curve here which is green curve which seems to be slightly better fit than the previous case but still it seems to be overestimating in the start peak seems to be much better and some underestimation is happening in the second half of the curve but not as much as the previous one. So, this seems to be a better fit and here when you put the cumulative sales you find that it seems to be much better fit with your blue line and the green line. So, now the drawbacks of this diffusion model is that there has to be some initial number of adopters. So, to get over that assumption we have to update our model in something called as a BAS diffusion model named after the person BAS which we will look at it in next class. But for a given diffusion model I hope you found out how to fit based on existing data and in the columns I have given whatever steps to be if you follow that you should be able to get this answers. Thank you.