SITE MAP SEARCH! E & B GEOG RESOURCES A-Z INDEX


Shift & Share Table with Excel Explanations

(http://faculty.washington.edu/krumme/350/ssexcel.html)
The following table was generated by Microsoft Excel.
 
Excel
Col.A
Industry		 Regio
in %		 Natio
in %		 Loc
Quot		 NatChg
in %		 Regio
1985		 Regio
1995		 RegChg
in %		 Expec
Empl.		 Total
Shift		 Expec
Empl.		 Diff.
Effect		 Comp
Effect
Excel
Row 2		 1/B 2/C 3/D 4/E 5/F 6/G 7/H 8/I 9/J 10/K 11/L 12/M
I 50 10 5 10 500 1000 100 645 355 550 450 -95
II 10 20 0.5 20 100 100 0 129 -29 120 -20 -9
III 20 40 0.5 30 200 250 25 258 -8 260 -10 2
IV 20 30 .667 40 200 280 40 258 22 280 0 22
Total: 100 100 1 29 1000 1630 63 1290 340 1210 420 -80


Excel Explanations:

In Excel, col.A was used for "Industries". Thus, our col.1 was called "B", col.2 = "C" etc.; The row for Industry I, i.e. the first data row, in Excel was "row 3". Row 7 was the "Totals" row. Thus the full Excel data table extended from B3 to M7 (in yellow)

Columns:

  1. REGIONAL industrial composition: Employment shares by industry for 1985 in %

  2. NATIONAL industrial composition: Employment shares by industry for 1985 in %

  3. Location Quotients for Holukuluku, 1985 (col.1 / col.2)

    Excel formula: B3/C3; then pull down for industries 2, 3 & 4, as well as Total (="1")


  4. NATIONAL increase in employment 1985-1995, in %

  5. Holukuluku: Absolute employment, 1985

  6. Holukuluku: Absolute employment, 1995

  7. Holukuluku's increase in employment from 1985 to 1995, in %

    Excel: (G3-F3)/F3*100


  8. Holukuluku: EXPECTED employment, 1995 (Expected on the basis of total national employment growth = 29%)

    Excel: F3*1.29


  9. Total Employment Shift by industries, 1985-1995 (col.6 - col.8)

    Excel: G3-I3


  10. Holukuluku: EXPECTED employment, 1995 (Expected on the basis of national employment growth of individual industries; see col.4)

    Excel: F3+F3*E3/100 [pull down without total]
    Total (K7): Sum(K3:K6)


  11. Differential Effect (or "local-factor effect) (=Shift based on different growth rates of individual industries in Holukuluku as compared with the nation; col.6 - col.10)

    Excel: G3-K3


  12. Composition Effect (or "structural" or "proportionality effect") (=Shift based on differences in the industrial composition between region and nation at the beginning of the period (col.3) and the effect of the deviation of individual national industrial growth rates (col.4) from their total aggregate (and weighted) growth rate (29%). There are two ways to arrive at the values of col.12. First, they represent the residuals of col.9 and col.11; they also can be calculated on the basis of col.4 and col.5; the average national growth rate (29%) is subtracted from the individual industrial rate of change in col.4 and the difference divided by 100 and multiplied by the absolute regional employment (base year 1985) for the respective industry (col.5)).

    Excel: K3-I3


    Return to: Shift and Share Analysis || Econ & Bus Geography
    2002 [econgeog@u.washington.edu]