Tutorial de GLM en Excel

Scene 1:

Welcome to Module 11 of the Getting Started series. In this video we will demonstrate how to use NumXL to construct a GLM model calibrated with the data and estimate values for intermediate points.

For our sample we'll use clinical results for an insecticide application on the tobacco budworm. The experiment variables are subject to gender and the administered dose. The batch size is fixed at 20.

Scene 2:

The dependent variable is the number of deaths per batch, we wish to model the mortality rate as a function of gender and dose amount. Select an empty cell in your worksheet and click on the GLM icon in the NumXL toolbar.

Scene 3:

The GLM model wizard pops up.

For the dependent variable, aka response select column E.

For the explanatory variables, aka independent variables, select column B and C.

For the link function we wish to use log it function as our data follows a binomial distribution. Set the batch size to 20.

Under options, leave the guest coefficients selected. The output range will be set by default to the selected cell in your worksheet. Now click OK.

Scene 4:

The GLM model is printed in our worksheet. Note that the values of the models coefficient are not optimal, but are just a quick guess. Next let's calibrate the model. Select the top cell of the GLM model table, click on the calibration icon in the NumXL toolbar.

Scene 5:

The Excel solver pops up. The different fields in the solver are already there, so we don't have to fill anything. Click on the solve button.

Scene 6:

The solver searches for an optimal set of values that maximizes the LLF values. Upon success it pops up a message box to alert us. Click OK to accept the new values.

Scene 7:

The GLM model table now has the optimal values.

Scene 8:

Next let's compute the model values for all input data points. For forecast, type GLM_FORE, then click on the effects formula button. The function arguments dialog box will pop up.

Scene 9:

For the x-variable, select B and C in row 9. For the models betas, select the cell range of their values in the table. For Phi, select the C28 cell, and for the link function select the C29 cell in your table, then click OK.

Scene 10:

The value is now computed, let's copy the formula to the remaining rows. Next let's compute the forecast standard error. Type GLM_FORESD and follow the same steps we did earlier.

Scene 11:

Copy the formula to the rest of the verbs. For the upper and lower limit we will copy the formula and edit the function name. Change GLM_FORESD to GLM_FORECI, then click on the FX button to add the missing arguments. Set the alpha to 0.05 and set the upper to 1. Click OK.

Scene 12:

Copy the formula again to the right. Now edit the argument to choose the lower limit. Copy the formula to the verse below it. Next let's plot the results. Select the cell range for the male bud worms.

Scene 13:

Overlay the input data values, the 95% confidence interval shows up as a shaded region.

Scene 14:

That is it for now, thank you for watching!