The problem:
Dertermine the precinct partisanship response rates required
to derive the absolute MINIMUM mean Kerry exit poll percentage
resulting from a 100,000 trial simulation.
The only constraints are 1) the weighted average response rate
equals 53% and 2) the following precinct partisanship
weightings:
strong Bush >>>>> strong Kerry
40 415 540 165 90
Kerry% 10 30 50 70 90
Bush% 90 70 50 30 10
The Excel "Solver" tool is a non-linear optimization
algorithm. Here is the description of Solver taken from
Excel’s online documentation:
"Solver is part of a suite of commands sometimes called
what-if analysis tools. With Solver, you can find an optimal
value for a formula in one cell — called the target cell — on
a worksheet. Solver works with a group of cells that are
related, either directly or indirectly, to the formula in the
target cell. Solver adjusts the values in the changing cells
you specify — called the adjustable cells — to produce the
result you specify from the target cell formula. You can apply
constraints to restrict the values Solver can use in the
model, and the constraints can refer to other cells that
affect the target cell formula. Use Solver to determine the
maximum or minimum value of one cell by changing other
cells."
Solver will derive the combination of response rates (subject
to the 53% weighted average response constraint) which will
MINIMIZE Kerry's total average simulation percentage. Rather
than "hunt" and "peck" and try hundreds of
combinations, we let the Solver algorithm derive the optimal
(minimal) solution.
Solver executes the 100,000 trial simulation, checks to see if
a minimum has been reached. If not, it iterates to try another
solution by changing the partisanship response rates and
re-executing the simulation. It performs this iteration
procedure until it finds a minimum, subject to the pre-set
iteration limit.
The Solver algorithm went through its gyrations to derive the
optimal solution and returned the following result:
USING PRECINCT PARTISANSHIP RESPONSE DATA SUPPLIED BY
MITOFSKY, THE SOLVER ALGORITHM COULD NOT FIND A KERRY AVERAGE
SIMULATION PERCENTAGE LOWER THAN 50.20% (50.24% MEDIAN).
THERE IS NO FEASIBLE COMBINATION OF RESPONSE RATES CAN PRODUCE
THE RECORDED 50.73% BUSH VOTE. IN FACT, THE PRECINCT RESPONSE
RATES RETURNED BY SOLVER TO DERIVE KERRY’S 50.20% MINIMUM
PERCENTAGE ARE IMPLAUSIBLE - WHICH MEANS KERRY DID FAR BETTER
THAN 50.20%.
Here are the final precinct category weightings calculated by
Solver to obtain the 50.20% Kerry minimum:
40 Partisan Bush (80-100) : 32.37%
415 Solid Bush (60-80) : 70.33%
540 Non-partisan (40-60) : 46.69%
165 Solid Kerry (60-80) : 43.65%
90 Partisan Kerry (80-100): 18.93%
Kerry Exit Poll simulation - 10,000 trials
Using Solver algorithm to minimize Kerry percentage
K B Other
Mean 50.20% 48.80% 1%
Med 50.24% 48.76% 1%
Max 55.84% 43.16% 1%
Min 44.05% 54.95% 1%
Win 6951 3049
StdDev 0.15 0.05 0.05 0.1 0.15
RespRate 32.37% 70.33% 46.69% 53.65% 18.93%
Printer-friendly format
Email this thread to a friend
Bookmark this thread
(1000+ posts)
Send PM |
Profile |
Ignore
