Introduction
This week's objective are:

• to consolidate your knowledge of EXCEL with further practice;
• to learn how to incorporate trend lines in your charts, and to calculate moving averages and compound growth rates; and to explore a substantial cross-country demographic dataset and to draw conclusions about two populations.

Your are reminded that this week's work is assessed, and indeed both your exercises are directly solely to the production of your assignment.

Resources
As for last week.
On the demographic issues underlying your assignment, see D. Coleman and J. Salt The British population: patterns, trends and processes (1992) and C. Trebilcock The industrialization of the continental powers (1981).

Exercise 1: using unemployment data (annual and monthly) to detect trends and cycles
This set of exercises concern interwar unemployment, and with all the data you need in eh068.xls. Load this into EXCEL and then save it to your floppy disk, following the usual rules for file names (this time ending 11, as in gobr11.xls).

First construct the chart below, the trend line being added at the final stage by clicking on the series and selecting Insert Trendline and then choosing Linear. As you can see interwar unemployment was subject to much variability around an upward trend. This trend line is calculated by EXCEL as the line of best fit, that which minimises the sum of the square of deviations between the line and the actual series for each year. If you click on the trend line to format it, and select Display R_squared value on chart you will get the result that R² = 0.1418. Were it equal to 1 (its maximum value) the line would be a perfect fit, a value of 0.14 is a poor fit, with the explanation the extreme variability of the annual unempl oyment series. With this completed, save your file.

If unemployment is growing on trend are any other patterns we can detect.? Let's focus on the monthly data for 1927 and 1928, the two years before the so-called great depression struck the British economy. Your aim is the chart below which incorporates the monthly series from eh068.xls and 3-monthly and 5-monthly moving averages which, like the linear trend line, are added to the graph at the end through the Insert trendline command. As you can see the effect of the moving averages is to smooth the series, making clear the longer-run (upward trend). You should also see from the unsmoothed data that, within the year, unemployment is high in January and February and low in the spring-summer (the seasonal affect in labour markets, particularly oper ating on the building trade).

Exercise 2: examining French and British demographic trends, 1851-1971/5
The resource file is eh094.xls, with the best way for you to proceed being to copy cells A1:Q39 from this file to a blank sheet in the file you established for exercise 1. NB Be particularly careful with this data as you need to keep its organisati on and structure for the exercises you are going to undertake.

First, we need to compare population growth for the two countries between the base and terminal years. Unless you have a calculator that has nth roots, the calculation of the geometric mean can be rather complicated. Fortunately, it is very easy with a spreadsheet; even more so as eshcalc1.xls has been set up with the required formula. Load this file - as with all resource files, it is in d:\teaching\it97\data - and do the calculations necessary to fill in the following grid which should be stored in y our file and clearly labelled:
 

You should obtain the result that the population of Great Britain grew at a compound rate more than twice that of France, with the growth rates of the female population in both countries very slightly exceeding those for men. Once you have finished wit h eshcalc1.xls close it down without saving changes.

The former result is in accordance with traditional concerns about the low birth rate in France, and is also relevant to the argument advanced by some that industrialisation inhibited population growth in France (see Trebilcock 1981, pp. 163-7, 397). W hat other demographic differences can we find as between the two countries?

The second part of the exercise is to investigate the changing age striucture of the two countries between 1851 and 1971/5. At present the raw data is in 17 age groups and 1 unknown. It will be acceptable to transform these into three; it will also per mit calculation of dependency ratios, defined as the ratio of the population in the age groups 0-14 and 65 and over in relation to the age group 15-64. It is thus a measure of the extent to which there is dependency upon a relatively small economically pr oductive part of the population.

Your exercise is to fill in the gaps in the following with the age distribution calculated to 1 decimal place, and the depedency ratio to 2 decimal places, again with the results clearly displayed and saved in your file.
 

HINT assuming that you copied the whole block of cells A1:Q39 to a blank sheet where they are now A1:Q39, and that you are building this table underneath the data in your new sheet, the formula in the cell where the asterix is should be:

=sum(D14:D16)/(D33)*100

where the numerator is the sum of cells D14 through D16 (which captures everybody aged 14 and under) and the denominator is cell D33 (the total population). The result has then been transformed into a percentage by multiplying it by 100. You may find < B>Window/Split useful so that you can divide the screen between the data that will underlie the formula and the cells further down the worksheet where you are building the formulas. Place the cursor at the point where you wish the split to occur before selecting the above.

Next, let's graphically represent the differences between the French and British populations. Your objective is to construct the following two pie charts, from which it is quiet clear that, for the initial year, the French population was on average old er than that of the British population.

Pie charts show changes in the overall distribution but not changes in composition and in overall size. Stacked column graphs are good for this purpose, and your next exercise is to construct the chart below (for which you need the raw data that underl ay the two previous charts, rather than the percentages):

To complete the assignment you need to use the data on the numbers of births/deaths/marriages (which starts at cell A35 if you followed the copying procedure) to fill in the following table where births etc are the numerator, total population is the de nominator and the results are mutiplied by 1,000 to express them as per 1,000: Save your file, backing up your master to your backup disk.
 

FINALLY, your assignment is to deposit your backup floppy disk (just the disk, not in its box) so that your progress can be assessed. Please make sure that you get your disk to Roger Middleton's pigeonhole on time and that it is properly labeled , both electronically and with the sticky label on the disk. Assuming that your assignment passes you will be able to collect it the following Monday from the same place.

Those wishing to deepen their skills with spreadsheets and the quantitative assessment of historical data should follow the additional EXCEL exercises link.

To IT-MA home page
To Department of Historical Studies home page.

(c)R. Middleton 1997. Last modified 15 December 1999.