The first screen of the worksheet will now appear, with the instruction to <PgDn> to begin the assignment. When you do that you will see the first of several screens of data needed to do this assignment. The data you have come from Earl J. Hamilton's American Treasure and the Price Revolution in Spain, 1501-1650, Cambridge, Mass.: Harvard University Press, 1934. In your template file you have prices for five basic goods from 1520 to 1650. The prices are for the regions of Old Castile-Leon and are in the local currency, maravedis. The price of sugar is given terms of an arroba. One arroba is 11.51 kilograms or 25.36 pounds. The price of wheat is given in terms of a fanega. One fanega = 55.5 liters = 50.4 dry quarts. The unit of wine is the cantara, which is equal to 15.85 liters or 16.75 liquid quarts. The unit for pricing beef and nails is the libra = 0.46 kilograms = 16.22 ounces. In addition to price data, you have information on the wages of a master mason and a linen weaver. The mason's wages are in diners per day. The figures for the weaver represent what he received per alna of cloth (1 alna = 91 cm.). This is a piece rate. One common estimate is that one to three alnas represented about a day's work. So to compare the weaver's wages to the mason's you will want to multiply the weaver's figures by three.

CONSTRUCTING PRICE INDICES
You will use the data on the five goods to construct three price indices. Below you are given the formulas you will use to calculate the indices. You will have to translate these formulas into Excel commands that will manipulate the data. Once you have the correct command sequence, you will find it easy to finish any index using the "Copy" command, or using the mouse pointer to drag the cell with the correct formula over the cells you need to "populate."

The base year for this index and the rest is 1520. Let the prices of the six goods be represented by X1, X2, X3, X4, X5, X6. The first step to calculate this index is to convert each price series into an index where the index is set to a value of 100 for the base year, 1520. This is done by dividing the price in each year by the price of 1520 and multiplying the result by 100. You can do this manually or with the PC, entering the formula, +B37/B$37*100, in cell Q37 (the base year index value for sugar in 1520) and then using the COPY command to copy cell Q37 to the entire matrix, Q37..V167. This gives you the indexes immediately for all six goods for all 131 years, a task that must have taken Mrs. Hamilton a week to do, and assistants another couple of weeks to verify.

To construct the index you must now sum up the index values for each year and divide by the number of goods in your index. The Lotus formula is @SUM(Q37..V37)/6. This will give you an unweighted price index where the base year has a value of 100. Enter this in the column K37..K167. You may now check your calculated index by pressing the "/GNU" keys as before and highlighting the graph named "PRICES".

What is the percentage increase in prices over the entire period, using this index? Which good does it seem to track most closely? Why do you suppose that is?

You can weight each good in the index in an effort to make it reflect the market basket of goods which the average Spaniard might have consumed. We know most most of the diet was grain. Sugar was a luxury good still, as was pepper. Wine was commonly drunk and beef infrequently eaten. Housing took a small percentage of income. On this basis, we will assign a weight of 0.05 to both sugar and pepper, 0.1 to beef, 0.2 to nails (a component of housing), 0.4 to wheat, and 0.2 to wine. Note that the weights add up to 1.0.

To calculate this index, divide the price of each good by its price in the base year, multiply it by 100, and multiply that value by the appropriate weight:

etc.

The base year, 1520, should again have a value of 100. Be certain your calculated values are in the column, "Index2". Check again the graph named "PRICES" and see how this index compares to the unweighted one.

What is the percentage increase in prices over the entire period, using this index? How do you explain the difference between the two indexes?

Let the nominal wages be represented by Y1 = Mason, Y2 = Weaver and Y3=Laborer. Using the second price index, "Index2", you will now calculate the real wages for each craftsman and the average laborer. The base year for real wages is still 1520. Simply divide the nominal wage by the value of the index and multiply by 100 to get the real wage for any year, Z1 for "RMason" and Z2 for "RWeaver".

[Y11520/I21520] 100 = Z11520 [Y21520/I21520] 100 = Z21520

[Y11521/I21521] 100 = Z11521 [Y21521/I21521] 100 = Z21521

[Y11522/I21522] 100 = Z11522 [Y21522/I21522] 100 = Z21522

etc.

Check to see that each of these calculated values is in the appropriate column. Since the Weaver's wages are piece rates, multiply each cell by three (3), to make them comparable to the Mason's. You should now look at the named graph "WAGES" to see how each behaved over the entire period.

What were the real wages of each in 1650? Did the real wages of the Masons, Weavers and Laborers decline or increase over the whole period? When were they the best in the period? When did weavers become better off relative to masons? to laborers?

You will now have completed the first table in the Template file. Page Down (keep hitting the "PgDn" key) until you reach the end of the table. You will continue this assignment by filling in the next table. Now for each item (column) in the table, compute the average or mean value (MEAN), the maximum value (MAX), the minimum value (MIN), the annual average rate of growth. (RATE), the standard deviation (STD DEV), and the coefficient of variation (STD DEV/MEAN). For the first three calculations there are simple Lotus 1-2-3 commands that will do these for you. For RATE there are various ways you can calculate the rate of growth in the following formula:

A1650 = A1520(1 + R)130

The solution for R is:

R = (A1650/A1520)(1/130) - 1

Print out the full set of summary statistics for all the series, individual prices, nominal wages, price indexes, and real wages.

Which series has the highest mean? greatest range? highest rate of growth? What does this imply for your choice of which goods to include in your index and the weights to give them?

If you move the cursor now below the set of summary statistics, you will find a new set of headings, "TREND REGRESSIONS". These are for the results you will obtain from using the "Regression" function of  EXCEL.  The X-range for each trend regression will be the years 1520-1650 in the first column, the Y-range is the prices for the particular good, you want to compute an intercept, and the output range should consistently be cell L188. After each regression, transfer the VALUES for the intercept and the X1 coefficient to the cells above each good. (This is very important!) After each such transfer, press the F9 key to re-calculate all the formulas in the spreadsheet. New numbers should appear in the column below the heading each time you do this.

Hamilton once said that it didn't much matter what weights you gave to various goods or which you included in your price index, because all prices tend to move together. Based on your trend regressions and volatility measures from Question 5, especially the coefficient of variation for the individual goods and the individual wage series, would you agree or disagree with this statement?

To complete your understanding of the price revolution in Spain, and to appreciate the crudity of the way you have calculated trend rates of growth, it is useful to look once again at your named graphs, which should now have the trend values you calculated graphed with the actual values. Print these graphs and turn them in with your answers to the questions above.