See Target Ultimate Parent Mini Data Projectdata
I have done the data cleaning work following the instructions below. But i need to fix some errors that my professor pointyed out! So , i just need the fix!!!!!!!!!
Here is all the instructions:
(1)The instruction below is based on DIVEST00 file as a reference, but pls data-clean DIVEST07 file, which means DIVEST file in year 2001.
(2)You should only focus on 2 tabs in DIVEST files – (1) Completed SP, (2) Finalcount (Ignore all other tabs.)
(3)Within these 2 tabs, focus only on the blue columns. In “Completed SP” tab, the 3 blue columns are (a) CompletedDivestCount, (b) CompletedDivestSumMajorityCount, (c) CompletedDivestMaxMajorityCount. In “Finalcount” tab, the 3 blue columns are (i) CompletedDivestCt (ii) CompletedSumMajorityDivestCt (iii) CompletedMaxMajorityDivestCt. Note that data in (a) = (i), data in (b)=(ii), data in (c) = (iii). Basically the data in Completed SP tab was pulled over to Finalcount tab, where the company names matched between these 2 tabs. Carefully analyze the formula for the said blue columns to see the logic for how past GRA1 construct the data in the 3 blue columns.
(4)The count numbers in blue columns in “Completed SP” tab refer to the count data for each TARGET ULTIMATE PARENT company in column F (which is the same company in CONAME column A in “Finalcount” tab). This count data refers to the number of TARGET NAME (or business units) which has been divested, sold or removed from TARGET ULTIMATE PARENT. For example, in “Completed SP” tab, we see TARGET ULTIMATE PARENT called 3Com Corp has divested 2 of its business units in column A (3Com Corp-Chicago Mnfr Op, NewS@t(3Com Corp). Hence, in blue column IY, you see CompletedDivestCount =”2” for 3Com Corp. Take another example, ADAC Laboratories, which is TARGET ULTIMATE PARENT company in column F. We see ADAC Laboratories has divested 3 of its business units in column A (ADAC Health Care Info-Cardio, ADAC Laboratories-Health Care, ADAC Laboratories). That’s why CompletedDivestCount =”3” for ADAC Laboratories in column IY.
(5)The other 2 blue columns in “Completed SP” tab are “CompletedDivestSumMajorityCount” (column JE) and “CompletedDivestMaxMajorityCount” (column JH), which were computed by my past GRAs based on Sum Majority and Max Majority, respectively. These are just 2 different versions of the original 1st blue column “CompletedDivestCount” (column IY).
(6)CompletedDivestSumMajorityCount (column JE) means past GRA counted the divestiture event if the “% of Shares Acq” (column U) associated with divestiture related to the TARGETNAME (column A) is greater than 50%. Unlike CompletedDivestCount variable (column IY), any divestiture with % of Shares Acq < 50% or missing data are not counted in CompletedDivestSumMajorityCount (column JE).
(7)CompletedDivestMaxMajorityCount (column JH) means past GRA counted the divestiture event if the “% of Shares Acq” (column U) associated with divestiture related to the TARGETNAME (column A) is greater than 50% and has the maximum (MAX) value. Same logic as point (6), except here you considered MAX rather than SUM.
(8)Your job is to break down existing count data for each company in each of 3 blue columns (columns IY, JE, JH) into 3 subcolumns (for total of 9 subcolumns = 3 x 3) based on “Target Primary SIC Code” (column C) in “Completed SP” tab.
a.In 1st subcolumn = based on existing count data in “CompletedDivestCount” (column IY), count only when Target Primary SIC Code has the same first 2 digits in the 4-digit SIC code. Label this variable “CompletedDivestCount2SIC”.
b.In 2nd subcolumn = based on existing count data “CompletedDivestCount” (column IY), count only when Target Primary SIC Code has the same first 3 digits in the 4-digit SIC code. Label this variable “CompletedDivestCount3SIC”.
c.In 3rd subcolumn = based on existing count data in “CompletedDivestCount” (column IY), count only when Target Primary SIC Code has the same first 4 digits in the 4-digit SIC code. Label this variable “CompletedDivestCount4SIC”.
d.Repeat process 3 times each, for each of remaining 2 blue columns (columns JE and JH).
e.You may construct these 9 subcolumns in “CompletedSP” tab, and then pull the data for all 9 subcolumns over to “finalcount” tab, where you need to create 9 new subcolumns to store the data you pulled over from CompletedSP tab.
f.Let’s take an example, AMRESCO Inc.
ii.1st subcolumn = CompletedDivestCount2SIC = 3 (because the first 2 digits “61” are same for all 3)
iii.2nd subcolumn = CompletedDivestCount3SIC = 2 (because the first 3 digits “616” are same for 2 of the 3)
iv.3rd subcolumn = CompletedDivestCount4SIC = 2 (because the first 4 digits “6162” are same for 2 of the 3)
v.CompletedDivestSumMajorityCount (column JE) = 2 (based on divestitures of AMRESCO UK Holdings Ltd and AMRESCO Inc-Homebuilder in column A)
vi.4th subcolumn = CompletedDivestSumMajorityCount 2SIC = 2 (because the first 2 digits “61” are same for all 2)
vii.5th subcolumn = CompletedDivestSumMajorityCount 3SIC = 0 (because the first 3 digits “616” and “614” are not same)
viii.6th subcolumn = CompletedDivestSumMajorityCount 4SIC = 0 (because the first 4 digits “6162” and “6164” are not same)
ix.Coincidentally, the 3 subcolumns for MAX blue column would produce the same counts as SUM blue column. NOTE: Although MAX and SUM would typically produce same counts, they are not always the same.
(9)The easiest way to complete this project is probably to use the existing formula from GRA1 for the original 3 blue columns, and then modify GRA1’s formula to allow for breakdown of existing counts into 3 subcolumns (based on first 2, 3, or 4 digits of the 4 digit SIC code) for each of original 3 blue columns.