Assignment 12 Write-up: Spreadsheets and Stamps

Chelsea Henderson

The Rising Cost of Stamps

This data set is based on the first class letter postage for the US Mail from 1933 to 2008. Plot the data and develop a prediction function. When will the cost of a first class postage stamp reach \$1.00? When will the cost be 64 cents? How soon should we expect the next 3 cent increase?

Many Americans have noticed a sharp increase in the cost of a postage stamp in the last few years. When I was born in 1987, my parents could send out an announcement of my birth for just 22 cents. Twenty-two years later, this past May, the cost to send out my college graduation announcement would have been 44 cents. It's a good thing my family never actually got around to sending those announcements.

Below is a chart showing the increase in the cost of a postage stamp over the last ninety years, from 1919 to 2009.

Before we use the data on an Excel spreadsheet to make future predictions, I would like to briefly look at past trends in the price of a stamp.

During the last ninety years, the price of the stamp has increased from 2 cents to 44 cents. The increments of increase have ranged from one penny to five cents. The time between raising the price varies from 26 years to1 year.

Analyzing the cost increases of the stamp over the past 90 years is not only interesting, but also it is useful for predicting future prices. What we have seen is that the price increase is not linear. Prices increase in varying increments and at different times. One year it may stay constant, while another year it may increase by 1 cent, and yet another year could see a 5 cent increase. We know from our analysis that the price of a stamp is rather unpredictable- there is no set function that the United States Post Office uses to determine the rise in price of a stamp.

Using a spreadsheet will help us see if there has been any more recent trend and maybe we can make a prediction of what the future rise in the price of a stamp will bring. We can attempt to answer the question of when will the price of the stamp reach a certain number, but we must realize that our prediction is only an educated guess.

Using the data in the chart above, Excel can easily produce a graph of the data points. Below, the x-axis shows the year, while the y-axis represents the cost of the stamp. The plotted points are the price of the stamp for the year.

Looking at the graph of plotted points above, we can more easily see the sharp increase in the cost of a stamp over the last 40 years, after an almost constant price for fifty years before that.

We can ask Excel to connect the points.

Looking at the graph above, how can we predict the future price of a stamp?

Using a Trendline

It would be nice if the price of the stamp increased linearly. If the price increased one cent every two years, for example, then the cost of a stamp would be a linear function of time.

Unfortunately, as we have seen above, this is not the case. We can however, imagine what if it was linear. This process is called Linear Regression and is often used in statistics. I will not go into the specifics here, but what we want to do is create the closest possible line to the points given.

Lucky for us, the mathematics involved is not neccessary, because Excel can plot this line for us. This line is known by Excel as the trendline.

Using all of our data points, the linear trendline is below.

Obviously, the trendline is not exact. The majority of the points show a fast increase in the price of a stamp and thus the trendline represents this sharp increase. This representation of later points causes the line to show a negative price for older years. These older years still affect the line, however, and cause the line to yield a lower price for the higher years.

We can get a more accurate trendline if we do not plot the first two points, where the price stayed constant for so long.

Plotting only points after 1958, we have the following trend\regression line.

Above, we see a much more accurate line than before. The line is still not exact, because the actual data is not linear, but the line comes pretty close.

How can we use the trendline to find future points?

Excel will not only plot the trendline, but also tell us the equation of the trendline. We can use this equation to predict future points.

The equation excel gives for the line above is

Remember, this equation did not come out of thin air or the magic of Excel. Excel used mathematical, statistical concepts to find the equation above.

The equation above represents the line closest to all of the data points that we have so far. Excel has used all of the prices of stamps for the last forty years to develop the above equation.

We can use the equation to predict future prices for varying years. Our predictions will not be exact, but they can be considered a pretty good estimate.

Let's make a data chart with x representing the year and y representing the price of the stamp, using our trendline equation.

Above, the cost was calculated by multiplying the year by .8597 and then subtracting 1683.9.

Now, we have some predictions for what the price will be in certain years.

We can also find the year that the stamp will reach a certain price by solving for x given the value of y.

Above, the year was found by adding 1683.9 to the cost and dividing that total by .8597.

Final Predictions

A wise investment would be the Forever stamps now offered by the post office that sell for the price of a current stamp but do not represent an actual stamp value and are theoretically able to be used forever.

Return Home