Assignment 12: The Spreadsheet in Mathematics Explorations

The assignment for this write-up was to use historical data for the price of stamps to formulate a function to fit a line to the historical data and to be used as a prediction function for future price increases.

The data was collected in an Excel file and is as follows:

 YEAR PRICE(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

To understand the type of relationship between the dependent (the price of stamps) and independent (the year) variables, let's plot a graph of this data and see the shape it takes. For instance, if this graph can take the shape of a straight line, we would know that the relationship between the two variables is linear.

It's important to note here that if we plot only the price changes as listed in the table given above, the graph (as we see above) does not reflect the differences in the number of years lapsed between price increases.

We can adjust for this by creating a table that lists the price data for intermediate years.

Here is a picture of the graph with adjustments made to reflect the time lapses between rate increases:

The graph above is a more accurate illustration of the relationship between time and the price of stamps. We can also see from the graph that the relationship is more likely to be exponential than it is to be linear. For this reason, instead of finding an equation to fit the linear form of :

price = x*(number of years) + c

We will focus on the form :

price = x^(a*number of years)+c

Because our initial price is 2 cents, let's start by looking at the estimates we would derive from an exponential equation that used the form:

price = 2*A^(current year - 1919), where A would be the average ratio of change-to-change prices

(For now let's start with A close to 1. We'll assign the value A=1.1 and see what this creates:

 YEAR RATE(in cents) Estimated Rate Absolute Difference 1919 2 2 0 1932 3 6.90454242878621 3.90454242878621 1958 4 82.289555578502 78.289555578502 1963 5 132.528152154733 127.528152154733 1968 6 213.437914326719 207.437914326719 1971 8 284.085863968864 276.085863968864 1974 10 378.118284942558 368.118284942558 1975 13 415.930113436813 402.930113436813 1978 15 553.602980984399 538.602980984399 1981 20 736.845567690235 716.845567690235 1985 22 1078.81559565527 1056.81559565527 1988 25 1435.90355781717 1410.90355781717 1991 29 1911.18763545465 1882.18763545465 1994 32 2543.79074279014 2511.79074279014 1997 33 3385.78547865368 3352.78547865368 1999 34 4096.80042917095 4062.80042917095 2002 37 5452.84137122654 5415.84137122654

Clearly we have assigned a wrong value somewhere...!

Let's go back and look at the ratios of prices, where the new price year n+x is compared to the price of year n. For example, what is the ratio of the price of stamps in 2002 to the price of stamps1999?

 YEAR RATE(in cents) Ratio 1919 2 1932 3 1.5 1958 4 1.33 1963 5 1.25 1968 6 1.2 1971 8 1.33 1974 10 1.25 1975 13 1.3 1978 15 1.15 1981 20 1.33 1985 22 1.1 1988 25 1.14 1991 29 1.16 1994 32 1.1 1997 33 1.03 1999 34 1.03 2002 37 1.09

From this table, we can calculate the average ratio, which is 1.2. If we change A to equal 1.2, the table of expected price increases and our table looks even worse:

 YEAR RATE(in cents) Estimated Rate Absolute Difference 1919 2 2 0 1932 3 21.3986410758144 18.3986410758144 1958 4 2449.61927994848 2445.61927994848 1963 5 6095.43664668139 6090.43664668139 1968 6 15167.3969166702 15161.3969166702 1971 8 26209.2618720062 26201.2618720062 1974 10 45289.6045148266 45279.6045148266 1975 13 54347.525417792 54334.525417792 1978 15 93912.5239219445 93897.5239219445 1981 20 162280.84133712 162260.84133712 1985 22 336505.552596652 336483.552596652 1988 25 581481.594887015 581456.594887015 1991 29 1004800.19596476 1004771.19596476 1994 32 1736294.73862711 1736262.73862711 1997 33 3000317.30834764 3000284.30834764 1999 34 4320456.92402061 4320422.92402061 2002 37 7465749.56470761 7465712.56470761

We could add a constant term to the equation, but this would not resolve the discrepancy, since the difference is certainly not equal to any constant. Since the absolute difference between the observed and the estimated rates increases with the number of years, we should examine how the years are used in the equation. Let's first try dividing the exponent (which is the year minus the initial year, 1919) by 2 and see what our table looks like:

 YEAR RATE(in cents) Estimated Rate Absolute Difference 1919 2 2 0 1932 3 6.54196317259802 3.54196317259802 1958 4 69.9945609308106 65.9945609308106 1963 5 110.412287782487 105.412287782487 1968 6 174.168865855355 168.168865855355 1971 8 228.950919945766 220.950919945766 1974 10 300.963800198053 290.963800198053 1975 13 329.689324721902 316.689324721902 1978 15 433.387872285196 418.387872285196 1981 20 569.703153119447 549.703153119447 1985 22 820.372540492004 798.372540492004 1988 25 1078.4077103647 1053.4077103647 1991 29 1417.60374997018 1388.60374997018 1994 32 1863.4885235102 1831.4885235102 1997 33 2449.61927994848 2416.61927994848 1999 34 2939.54313593817 2905.54313593817 2002 37 3864.12980235074 3827.12980235074

Much better! After some further 'tweaking' of the value between 2 and 6, we arrive at the following table, generated with the exponent divided by 5.1. This is the value that gave us the least total absolute difference between the estimated and the observed rates:

 YEAR RATE(in cents) Estimated Rate Absolute Difference 1919 2 2 0 1932 3 3.1832045317011 0.183204531701096 1958 4 8.06368662962834 4.06368662962834 1963 5 9.64189315262576 4.64189315262576 1968 6 11.5289826895141 5.52898268951412 1971 8 12.8341819108252 4.83418191082523 1974 10 14.2871430859174 4.28714308591743 1975 13 14.8071381629952 1.80713816299523 1978 15 16.4834582443642 1.48345824436417 1981 20 18.3495549715824 1.65044502841758 1985 22 21.1703712462204 0.829628753779552 1988 25 23.5670746509853 1.43292534901467 1991 29 26.2351094907829 2.7648905092171 1994 32 29.2051932701198 2.79480672988023 1997 33 32.5115210304234 0.488478969576612 1999 34 34.921167692408 0.921167692408048 2002 37 38.8746024495667 1.87460244956669

If we introduce a constant, can we reduce the total absolute difference even more?

Using the function price = 2*1.2^((current year-1919)/5.1)-1, the data becomes:

 YEAR RATE(in cents) Estimated Rate Absolute Difference 1919 2 1 1 1932 3 2.1832045317011 0.816795468298904 1958 4 7.06368662962834 3.06368662962834 1963 5 8.64189315262576 3.64189315262576 1968 6 10.5289826895141 4.52898268951412 1971 8 11.8341819108252 3.83418191082523 1974 10 13.2871430859174 3.28714308591743 1975 13 13.8071381629952 0.807138162995233 1978 15 15.4834582443642 0.483458244364165 1981 20 17.3495549715824 2.65044502841758 1985 22 20.1703712462204 1.82962875377955 1988 25 22.5670746509853 2.43292534901467 1991 29 25.2351094907829 3.7648905092171 1994 32 28.2051932701198 3.79480672988023 1997 33 31.5115210304234 1.48847896957661 1999 34 33.921167692408 0.0788323075919521 2002 37 37.8746024495667 0.874602449566694

Now we can graph the estimated rates against the observed rates and see how they compare. We can also see the difference created by subracting one from the function:

2*1.2^((current year-1919)/5.1)

price = 2*1.2^((current year-1919)/5.1)-1:

How can we use our equation to predict when the cost of a first class postage stamp will reach \$1.00?

Our predicion function:

price = 2*1.2^((current year-1919)/5.1)-1.

To find the year when postage will reach \$1.00, simply set this function equal to \$1.00 and solve for the 'current year' in the equation.

100 = 2*1.2^((current year-1919)/5.1)-1

(100+1)/2=1.2^((current year-1919)/5.1)

ln48.095=((current year-1919)/5.1)ln1.2

Using the ln function, these values can also be calculated in spreadsheet software:

(3.873178)=((current year-1919)/5.1)(0.182322)

21.24=(current year-1919)/5.1

108.34=current year - 1919

1919+108=2027.

From this result, we can predict that the price of first-class stamps will be \$1.00 in 2027.

When will the cost be 64 cents?

We can answer this question just as we did above, by setting our equation equal to 64 and solving for the 'current year'.

64= 2*1.2^((current year-1919)/5.1)-1

3.45=((current year-1919)/5.1)(0.182322)

current year=96.68+1919

From this we can conclude that the price of stamps will reach 64 cents in 2015.

When can we expect the next 3-cent increase?

To find the answer to this question, we need to use our equation and set it equal to the current price of stamps plus 3 cents, or 40 cents:

40 = 2*1.2^((current year-1919)/5.1)-1

Using the same methods as above, we find that the next 3-cent increase in the price of first-class stamps will occur in 2002...

If we remove the constant from the function, the 3-cent increase would happen later in 2002, so this would not remedy our faulty forecast for 40-cent stamps. Our review of the total absolute difference between the expected and observed prices indicates that the prediction function selected is the "best-fit" for the entire history of prices of stamps. Obviously there is some adjustment required in the calculation; perhaps the forecast would have been better modeled on recent (within the past 20 years) price increases only.