NumXL Cookbook - GLM with Binary Data

	Attachment	Size
	GLM-Budworms.xlsx
	GLM-Tutorial.pdf

In this tutorial, we will use a sample data gathered during a clinical trial of a new chemical/pesticide on tobacco Budworms. The subjects (i.e. Budworms) are grouped into batches of 20, and exposed to different doses of the chemical. The results are summarized below

Input data table for GLM with binary data example

Data preparation

Our objective here is to model (and forecast) the effectiveness of the new chemical using different dosages, and explain, to some extent, any variation based on the gender of the budworm. Furthermore, we want to express the results in term of the worm mortality rates (i.e. probability).

GLM in Excel - Covert binary data into mortality rates

We plot the data into two separate curves: males and females. It is apparent that mortality rate is affected by those two factors: gender and dosage.

GLM in Excel - Plot for mortality rates for male and female Bud worm

We will make two assumptions: (1) the results for each trial (i.e. batch) are drawn from a Binomial distributed population; we would like to estimate p - the probability of success (i.e. worm’s death). The probability (p) is allowed to vary across different trials (batches). (2) The probability of success is affected by two factors: gender of the subject and administered dosage of the drug.

Based on these two assumptions, we would model this relationship:

$P=f(X,Y)=E[p|X,Y]$

Modeling

We are ready now to propose a statistical model: generalized linear model with residuals following the Binomial distribution.

Generalized linear model (GLM) Wizard in Excel

For now, we choose “Logit” as our link (transform) function, specify the trial or batch size(20), and instruct the Wizard to calibrate (i.e. compute optimal values for the coefficients). Leave the Goodness-of-fit and residual diagnosis options checked.

Generalized linear model (GLM) - Model specification output table in Excel

Calibration

In this case, the GLM Wizard has calibrated the model’s coefficients, so we can skip this step.

But, in the event we wish to experiment with different link functions: LOGIT, PROBIT or LOG-LOG, then we need to re-calibrate the model. To do so, we can either:

Create a new model with the wizard, or,
(2) Change the “Lvk” parameter in an existing model table, and run the calibration using NumXL toolbar

Generalized linear model (GLM) calibration wizard or user form in excel

Step 1: Select the cell that acts as a header for the model table

Step 2: Click on the calibration icon/menu (Excel 2003)

Step 3: Click on “Solve” button in the Solver window

Forecast

Once the model is calibrated, and we are happy with the residuals, we can use it to construct our forecast mean (and confidence interval around it).

Using NumXL function (GLM_FORE), we can compute the mean. Using GLM_FORECI, we can compute the upper and lower limit of the confidence interval.

Generalized Linear model (GLM) forecast output table in Excel

Plotting the data again (actual) versus the model values.

generalized linear model Forecast plot with confidence region for male budworm in Excel

generalized linear model (GLM) Forecast with confidence region for female Bud worm in Excel

The dots represent the sample data, while the center line is the forecast mean. The shaded regions in the graphs are the 95% confidence intervals.

Notes

The forecast error decrease as we increase the dosage (C.I. gets tighter). This is evident in male and female batches
The logarithmic relation detected when we plot the raw data can be merely a data anomaly; the GLM shows more like a quadratic-type of relationship.
The mean is not exactly the center of the confidence interval due to the discrete-nature of the underlying binomial distribution, and the small batch/trial size.

Reference

Generalized Linear Models