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)
 Regional Employment Growth: 630
 National Growth Effect: 290
 Total Shift: 340
 Differential Effect (net): 420
 Composition Effect (net): 80
Columns:
 REGIONAL industrial composition: Employment shares by industry for
1985 in %

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

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")

NATIONAL increase in employment 19851995, in %

Holukuluku: Absolute employment, 1985

Holukuluku: Absolute employment, 1995

Holukuluku's increase in employment from 1985 to 1995, in %
Excel: (G3F3)/F3*100

Holukuluku: EXPECTED employment, 1995 (Expected on the basis of total
national employment growth = 29%)
Excel: F3*1.29

Total Employment Shift by industries, 19851995 (col.6  col.8)
Excel: G3I3

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)

Differential Effect (or "localfactor effect) (=Shift based on
different growth rates of individual industries in Holukuluku as compared
with the nation; col.6  col.10)
Excel: G3K3

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: K3I3
Return to: Shift and Share Analysis  Econ & Bus Geography
2002 [econgeog@u.washington.edu]