Creating an excel Shift-share analysis on fredericton oromocto region
you need to create an excel Shift-share analysis on fredericton oromocto region (canada). An example of Vancouver island region will be attached.
SS = NS + IM + RS
1. go to www.statcan.gc.ca &#61664; English &#61664; students and teachers &#61664; CANSIM
2. Into search field enter ?LFS economic region?
3. Choose table 282-0061
4. Choose ?Add/Remove data? tab
5. In ?Step 1: Geography? click ?+ Expand? and remove all selections by clicking ?All?
6. Select ?Canada? , your province and your economic region
7. Scroll to ?Step 2 ? Select NAICS? and select 1st and 3rd boxes next to ?All?
8. In ?Step 3 ? Select time frame? choose ?From 1993 to 2013?
9. In ?Step 4 ? Select the screen output format? select ?HTML table, time as columns.?
10. Click ?Apply?
11. Under ?Download? tab, go to ?option 1? ensure ?English? is the chosen language, select ?Time as columns? and ensure ?CSV? is chosen as file format
12. Click ?Download data? under ?Option 1? (not ?download entire table? under ?Option 2?)
13. Click ?Download file from CANSIM (CSV version)?
14. Save document under a recognizable name such as ?Shift-share for Econ2503? and under ?Excel workbook? format (.xlsx)
15. Put Geography and years in bold, freeze panes from cell C5 and hide all columns but 1993, 1998, 2003, 2008 and 2013
16. In cell Y3 write ?employment change? and write ?2008-2013? in cell Y4
17. To calculate employment change, in cell Y5 write formula =W5-R5 and then click-drag all the way down
18. In cell Z4, write ?% change?.
19. In cell Z5, write formula =W5/R5-1, click-drag all the way down, then format cells to % and one decimal.
20. Call cell AA4 ?National growth share?, cell AB4 ?Industry mix share? and cell AC4 ?Regional shift-share?
21. National growth share is the portion of local industry growth attributable to overall national employment growth.
iLocalt-1*CANt/CANt-1 In cell AA5, add a formula to multiply industry growth by total national employment growth. What formula do we write? How do we keep the total national employment value constant in our formula? =R5*$W$5/$R$5 ?then click-and-drag formula all the way down.
22. Industry-mix identifies slow and fast growing local sectors based on nation-level growth rates for those sectors
(iLocalt-1*iCANt/iCANt-1) – NS To calculate ?industry-mix share?, in cell AB5 write the formula =R5*(W5/R5)-AA5then click-drag all the way down, and manually reset to W5 and R5 every time a new region starts
23. Regional shift compares local industry growth rate with national industry growth rate.
iLocalt-1*( iLocalt/iLocalt-1) -iCANt/iCANt-1) To calculate regional shift, write =R39*(W39/R39-W5/R5) click-drag all the way down. Manually reset 2nd cell to W5/R5 whenever a new region is reached.
24. Highlight all values in columns AA, AB and AC and make sure format is set to numbers and 3 digits.
25. Hide national and provincial rows.
26. Save your work. Admire the beauty.