Fwd(2): Excel Spreadsheets...statistics, finance

Sanderson Smith (Sanderson_Smith@cate.org)
Fri, 17 Dec 1999 20:38:32 -0800


Dec. 17, 1999

Hi group...

I now have a total of 30 excel spreadsheets, some of which might be
of interest to those teaching AP Statistics. And, I have some that
do mortgages and accumulate monthly deposits, etc.

I'm more than willing to share them, and I have offered them up
before. I know that some have found them useful and others have not
been able to download them and make them work. They were created on
a Mac. Our computer expert here at Cate recently told me that if I
attach .xls to the file name, then those with PC's can download them
and get them to run. So, if you've asked for them in the past and
haven't been able to get them to run, you might want to try again.
Very brief descriptions of the programs appear below.

If you'd like to have them, just send your e-mail address and I will
send them via. attachment.

I don't claim these are great, but I do make use of them in AP
Statistics. My students download them and experiment around, much as
you would do with other statistical software. These, of course,
aren't as fancy or sophisticated as the commercial materials. The
Cate faculty have found the financial spreadsheets interesting.

-Best wishes for a great holiday season.
Sanderson
=============================

COIN FLIPPING (or 50% YES population)
Simulates flipping a coin 500 times, records number of heads
obtained, and calculates confidence intervals (99%, 95%, 90%, 80%)

DEGREES OF FREEDOM DEMO
Demonstrates what is mean by "degrees of freedom" for data in a
single row (or column), data in a 2-by-2 table, or data in a 3-by-2
table.

TI-83 STATISTICAL FUNCTIONS
normalcdf(lowerbound, upperbound, mean, st. dev.) , invNorm(area,
mean, st.dev.), normalpfd(x, mean, st. dev.)

DEMO STAT. FORMULAS
Excel stat. formulas: AVERAGE, STDEV, STDEVP, SUM, MAX, MIN, COUNT,
LINEST

SAMPLE MEAN DISTRIBUTION
Distribution of means for 20 samples of size 5 (with replacement)
from the set {0, 1, 2, 3,..., 98, 99, 100}

MEAN, VAR. (DIFF. SAMPLE SIZES)
Larger sample sizes usually produce more reliable statistics.
Sample size = 4 vs. sample size = 16.

RANDOM INTEGER GERERATOR
Gererates 200 random integers between two user supplied values
(smallest, largest). Also produces some statistical displays related
to rows and columns of the random number set.

SCATTERPLOT/REGRESSION LINE
Produces regression line for user-supplied set of 5 points. Shows
how changing points affects regression line.

PROPORTION CONFIDENCE INTERVALS
User supplies sample proportion and sample size. Displays confidence
intervals (99%, 95%, 90%, 80%).

SAMPLE SIZE (POLLING)
Produces sample size needed for specified margin of error (95%
confidence interval).

GAME OF SPADES
User inputs cost to play and payoffs for 0,1,2,3,4,5 spades in a hand
of five cards. Casino expectation (money) calculated.

BINOMIAL VS. NORMAL (20)
User supplies sample proportion of YESSES. Displays binomial and
normal distributions for samples of size 20.

CONFIDENCE INT. (t-DIST.)
User supplies sample of size 12. Confidence intervals (99%, 95%,
90%, 80%) constrcuted.

MATCHED PAIRS (t-DIST.)
User inputs 10 pairs, t-test run on difference of means. Confidence
intervals (99%, 95%, 90%, 80%) computed.

DIFF. OF MEANS (t-DIST.)
User supplies two samples (sizes = 10, 13). Demonstrates t-test on
difference of means to determine if samples came from same
population. Computes confidence intervals (99%, 95%, 90%, 80%).

COMPARING PROPORTIONS (z)
User inputs two samples (size, number saying YES). Test to see if,
based on Yes proportions, the two samples came from same population.
Computes confidence intervals (99%, 95%, 90%, 80%).

DEMO (CLT, UNBIASED EST.)
Polulation size = 3 (user input), all nine samples of size 2
produced. Programs demonstrates Central Limit Theorem and the fact
that s2 is an unbiased statistic.

95% CONFIDENCE INTERVALS
Produces 95% confidence intervals for samples (with replacement) of
size 12 taken from population P = {0,1,2,..., 18, 19, 20}.

RELATION (r AND REGRESSION LINE)
Illustration of formula (slope) = (regression coeff.)(sy/sx)

ILLUST. (TYPE I, II ERRORS)
Shows computation of Type I and Type II errors. (Two bags with
numbers in them, pick number from bag, determine which bag number
came from.)

TYPE I AND TYPE II ERRORS
Demonstrates computations related to Type I and Type II errors.

SIMPSON'S PARADOX
Provides examples of Simpson's paradox.

CI FOR SLOPE OF REGRESSION LINE
Given sample, the finds confidence intervals (99%, 95%) for the slope
of the regression line.

BINOMIAL SAMPLE SIZE 20
User inputs proportion of YESSES. Displays binomial distribution for
samples of size 20.

CREDIT CARD TRAP
Shows what can happen in two years if minimum payment is made. User
supplied input needed.

ACCUM DEPOSIT
Accumulates monthly deposits (user supplied) for 15 years at
user-supplied interest rates.

HERKY AIRLINES (43)
Simulates airline overselling. Program analyzes what can be expected
to happen if 43 tickets are sold for a 40 passenger plane. User
input needed.

20 YEAR MORTGAGE
User inputs loan amount and interest rate. Program shows 20 years of
payments divided into interest and principal portions, and the total
amount of interest paid over 20 years.

25 YEAR MORTGAGE
Same as 20 YEAR MORTGAGE except that accumulation is for 25 years.

30 YEAR MORTGAGE
Same as 20 YEAR MORTGAGE except that accumulation is for 30 years.