Skip to content

Home / Monte Carlo / Analyzing the Model / Percent Contribution / Percent Contribution How-To

Percent Contribution How-To

This walkthrough uses a small model you can build in a minute: two inputs, one output, and a specification band on the output.

Press Copy for Excel, then paste the table into a blank worksheet.

Model Value
Wall thickness 10
Bore 4
Outside width =B2*2+B3

Click cell A1 in a blank worksheet and press Ctrl+V. The two headings land in row 1, the labels in column A, and the model in column B: B2 holds the wall thickness, B3 the bore, and B5 the formula that reads them both. The empty row is deliberate, so the output lands in B5 and not B4, which is what every Monte Carlo walkthrough calls it.

Mark B2 and B3 as inputs with Mark Input, giving each a Normal distribution: B2 mean 10 standard deviation 0.2, B3 mean 4 standard deviation 0.1. Mark B5 as an output with Mark Output and give it a lower limit of 23 and an upper limit of 25.

Steps

  1. QXL Monte Carlo > Monte Carlo > Contribution Tools > Percent Contribution... There is no need to run the model first: each ticked method runs its own simulation.

  2. All three methods start ticked. Leave them, and their numbers of simulations, as they are, and click OK.

What you should see, and why

B2 should take much the larger share. The model is B5 = B2*2 + B3, so B2 enters with a coefficient of 2 and B3 with 1. Contribution goes with the coefficient and the input's own spread together, and here B2 has both the larger coefficient and the larger standard deviation.

Work it out before you look. B2 contributes about (2 * 0.2)^2 = 0.16 and B3 about (1 * 0.1)^2 = 0.01, so B2 should hold roughly 94 percent of the variance and B3 roughly 6 percent in the LMG, OLS Type I and OLS Type III blocks.

Unexplained Variation should be almost nothing. The model is a weighted sum of the inputs, which is exactly what a linear decomposition reproduces, so R-squared should be close to 1.

The Type III block also shows a small Shared Variation. The two inputs are drawn independently, but a finite sample of them is never exactly uncorrelated, so this remainder comes out near zero and can be slightly negative.

The Spearman Rank Correlation block shows about 94.5 and 5.5 percent, and no R-squared and no Unexplained Variation. Its contributions are built from rank correlations and total 100 percent.

Now make the unexplained part appear

Change B5 to =MAX(B2*2+B3, 24) and run Percent Contribution again. The outside width is now held at 24 whenever the sum would fall below it.

Unexplained Variation is now clearly nonzero in the LMG and Least Squares blocks, at roughly 27 percent, because the floor is not a weighted sum and a straight line cannot reproduce it. The two input shares shrink to make room for it, to roughly 69 and 4 percent.

Things to try next

  • Compare the LMG block with the OLS Type III block. The LMG contributions sum to R-squared by construction; the Type III contributions need not, and the difference is the Shared Variation row.
  • Make the two inputs correlated and compare the Shared Variation row under Type III with the uncorrelated run. Math Details explains why neither input is credited with it.
  • Give B3 a standard deviation of 1 and watch the shares swap over.

See Also