The Price of Stamps: An
Excel Exploration
By Sharon K. OÕKelley
This
is a basic exploration looking at how EXCEL can be used to do a regression and
to make a prediction. The data set is based on the first class letter postage
for the US Mail from 1919 to 2002.
|
year |
rate(in cents) |
|
|
|
|
1919 |
2 |
|
1932 |
3 |
|
1958 |
4 |
|
1963 |
5 |
|
1968 |
6 |
|
1971 |
8 |
|
1974 |
10 |
|
1975 |
13 |
|
1978 |
15 |
|
1981 |
20 |
|
1985 |
22 |
|
1988 |
25 |
|
1991 |
29 |
|
1994 |
32 |
|
1997 |
33 |
|
1999 |
34 |
|
2002 |
37 |
If we
plot the data using EXCEL, the following graph is producedÉ.
From
the graph, it looks like we can predict that this would either be an
exponential function or a power function. Using EXCEL, I can perform a
regression analysis to determine which one is the better predictor of the data
trend.
Exponential:
Power:
The trendline is most reliable
when the R-squared value is close to 1. In both of the instances above,
R-square is close to 1, but the exponential function has a higher value,
therefore, it is a better-fitting trendline of the two.
The exponential function yields
the following tableÉ.
|
Year |
Rate(Cents) |
|
1919 |
1.220172121 |
|
1932 |
2.044374615 |
|
1958 |
5.739039898 |
|
1963 |
6.999172524 |
|
1968 |
8.535995025 |
|
1971 |
9.615649536 |
|
1974 |
10.83186151 |
|
1975 |
11.2705365 |
|
1978 |
12.69606282 |
|
1981 |
14.30189336 |
|
1985 |
16.7632993 |
|
1988 |
18.88356432 |
|
1991 |
21.2720059 |
|
1994 |
23.96254368 |
|
1997 |
26.99338757 |
|
1999 |
29.22404798 |
|
2002 |
32.92038041 |
These values are somewhat close
to the original table.
Predictions
1.
When will the cost of a first class postage stamp reach $0.64 and $1.00?
Extending the table providesÉ.
|
2019 |
64.65024735 |
|
2020 |
67.26849042 |
|
2021 |
69.99276861 |
|
2022 |
72.8273762 |
|
2023 |
75.77678136 |
|
2024 |
78.84563323 |
|
2025 |
82.03876924 |
|
2026 |
85.36122272 |
|
2027 |
88.81823084 |
|
2028 |
92.41524287 |
|
2029 |
96.15792878 |
|
2030 |
100.0521881 |
Therefore, according to this
model, the stamp will cost a $0.64 around year 2019 and $1.00 around 2030.
3. How
soon should we expect the next 3 cent increase?
|
2002 |
32.92038041 |
|
2003 |
34.25360899 |
|
2004 |
35.64083144 |
|
2005 |
37.08423443 |
|
2006 |
38.58609318 |
|
2007 |
40.14877507 |
|
2008 |
41.77474337 |
|
2009 |
43.46656106 |
|
2010 |
45.22689496 |
|
2011 |
47.05851989 |
|
2012 |
48.96432302 |
|
2013 |
50.94730846 |
|
2014 |
53.010602 |
|
2015 |
55.15745598 |
|
2016 |
57.39125451 |
|
2017 |
59.71551869 |
|
2018 |
62.13391227 |
|
2020 |
67.26849042 |
|
2021 |
69.99276861 |
|
2022 |
72.8273762 |
Based on the data, the
prediction can be made that the next 3 cent increase will occur in 2017.