- This page will describe the changes that lead to the construction of the current SAM120 for California. Beginning from the original 104 sector SAM that was estimated for 2003, the SAM has been expanded from 104 industry sectors to 120 industry sectors (counting the NA sector, but not including Labor and Capital). The new sectors are specifically targeted by GHG mitigation measures that have been considered by CalEPA to achieve the Governor’s 2010 and 2020 emission targets. These sectors include dairy, livestock, forestry, landfill, semiconductors, refrigeration, vehicle services by transport technology, waste services, etc.
- Further modifications have also been made to the SAM120 to include changes in investment, changes to taxes, and a rerouting of payment of gasoline. The final file contains the matrix that will be used for SAM120.prn E-DRAM input.
- The files in this document are introduced linearly and most of them build upon each other. The descriptions also include notes and methods that were used for calculations. Flow charts for the files SAM120_DRAM.xls, SAM120_Make.xls, SAM120_Use.xls, v0304 oct05.xls, SAM120-original.xls are available in SAM120_Flow-Charts.doc.
SAM120_Make.xls and SAM120_Use.xls
This is the original file specifying the mapping of the detailed NAICS (5 or 6 digits) to the 120 SAM industries. It also shows how the 104 SAM industry sectors are disaggregated into 120 SAM industry sectors.
This file adapts the detailed mapping above to a mapping of the 527 BEA industries to the 120 SAM industries. It also creates a SAM industry listing with sector codes (numbers 1-120)
This workbook generates sector by sector ratios of US 1997 wages to CA 2003 wages (e.g., the ratio of wages paid by the OILGAS sector to CA employees in 2003 to wages paid by OILGAS nationally in 1997). These ratios are ultimately used in the IO table (SAM120_V0304.xls) to transform the 1997 US IO matrix to the CA 2003 IO matrix.
· Raw data inputs:
1. California wage data (Q2 2002 – Q1 2003) from CA ES202 (Quarterly Census of Earnings and Wages)
2. CA employment by sector (Q2 2002 – Q4 2003) from CA CES (Current Employment Series)
3. BEA wage data for California and US
o National: http://www.bea.gov/bea/dn/nipaweb/TableView.asp?SelectedTable=53&FirstYear=2003&LastYear=2004&Freq=Qtr
o State: http://www.bea.gov/bea/regional/sqpi
· Data inputs from other model workbooks:
- National wages by industry from the 1997 US Use matrix (Aggregate Use Matrix.xls)
- Final mapping of NAICS codes to SAM sectors
IO Table calculations (SAM120_V0304.xls, SAM120_Use.xls, SAM120_Make.xls)
· Notes / Method:
· The sheet ‘CES Empl’ is raw data from CES showing CA employment (in # of persons) by NAICS code for Q2 2002 – Q4 2003.
· The sheets ‘2002 Q1’ – ‘2003 Q1’ are raw data from ES202s showing CA wages by NAICS code. The reason for the cutoff at 2003 Q1 is that no data was available beyond this point at the time of development of this workbook.
· The sheet ‘NAICS detail’ organizes the raw data from the sheets above onto one sheet. Note that the column 4Q total sums the wage data from Q2 2002 – Q1 2003.
· The sheet ‘NAICS to model sectors’ pulls the 4Q total column from the NAICS detail sheet for each NAICS code, and cross-lists the values with the SAM sectors.
· The sheet ‘CA wages by model sectors’ calculates the 2003 CA wages for each SAM sector. For each sector, the wages paid from 2Q 2002 – 1Q 2003 are multiplied by the ratio of 2003 employment to 2Q 2002 – 1Q 2003 employment, to yield an estimate of 2003 wages.
· The sheet ‘National wages 1997’ is the transpose of the LABOR row from the 1997 US Use Matrix.
· The ‘Aggregate Wage Comparison Sheet’ accounts for the difference between the sum of the wages in each sector ($505.8m) and the total CA wages for 2003 reported by the BEA ($670.3m). The primary difference is govt wages ($142.4m). The remaining difference of $22.0m is unaccounted for and likely a result of, to some extent, inflation.
· The sheet ‘Wage ratios’ is the main result of the workbook. The CA 2003 column shows CA 2003 wages, adjusted so that the total matches the BEA figure of $670.3m. Note that govt wages of $142.4m are mapped to the NA sector. The US 1997 column simply copies over the data from the ‘National wages 1997’ sheet. This data does not account for the one-to-many sectors (the retail, wholesale, and ACC sectors). This is corrected in the Adj US 1997 column, which allocates all US RETAIL wages to the 12 retail sectors on the basis of CA wages (and operates similarly on the wholesale and ACC sectors). These sectors are highlighted in red on the sheet. Finally, the Ratio column gives the ratio of CA 2003 wages to US 1997 wages for each sector.
4. SAM120_Make.xls and SAM120_Use.xls
US 1997 IO database (raw data)
The resulting worksheets are used in Matlab to produce the 1997 US IO table, which is used in the next file, v0304 oct05.xls. The Matlab code used is CalcIOAggregate.m and LoadIO.m.
The SAM120_Make file begins with ‘US 1997 Agg Make Matrix’, which is a copy from the US 1997 IO database. This is only a 90x90 matrix, as it omits the one-to-many retail, whole, and ACC sectors. The purpose of the rest of the workbook is to expand this matrix to the full 120x120 US 1997 Make matrix.
The sheet ‘Wage ratios’ is a paste from the result of SAM120_DRAM.xls. This will be used to obtain the following sheets, ‘Horizontal Expansion’ and ‘Vertical Expansion’.
The sheet ‘Horizontal Expansion’ expands the matrix to a 90x120 matrix by estimating the values for the one-to-many columns. Using the retail columns (56-67) as an example, the estimates are calculated by allocating total retail commodity production from the 90x90 IO table to each retail sector on the basis of CA 2003 wages in each retail sector. The sheet ‘Vertical Expansion’ expands the matrix from 90x120 to 120x120 using the same method, except the allocations act on the rows instead of columns. The new values are highlighted in red.
The final sheet ‘US Make Matrix’ is the 120x120 1997 US Make matrix. The only difference between this sheet and the ‘Vertical Expansion’ sheet is that values of unity have been plugged into the last diagonal entries (121,121) and (122,122) to ensure that usage of Labor and Capital will show up in the IO table.
SAM120_Use.xls operates in the same way as SAM120_Make.xls, except there is no need for the final step of adding ones to the final two diagonal entries.
5. v0304 oct05.xls
Using the aggregate national IO table produced from the previous file, this file will compute the CA 2003 IO table for use in the SAM. It will serve as the new IO table portion of the SAM.
SAM120_Make.xls and SAM120_Use.xls
‘Wage ratios’ worksheet from SAM120_DRAM.xls
‘v0304’ worksheet in SAM120-original.xls
The first sheet ‘Aggregate Natl IO Table’ is output from Matlab as a result of SAM120_Make.xls and SAM120_Use.xls. This is the 1997 US IO table. The ‘Wage ratios transpose’ worksheet is a transpose of the ‘Wage ratios’ sheet from SAM120_DRAM.xls.
The result is ‘CA IO Table’ and is calculated by multiplying each column of the 1997 US IO Table by the wage ratio for that column. This sheet is the 2003 CA IO Table.
This is the master SAM file and contains the new SAM120. The final worksheet is ‘SAM’. This file contains copies of all the files that are necessary to construct the SAM, including the original SAM. Much of original SAM remains unchanged, but changes made are as follows:
- The original ‘V0304’ and ‘detail SAM’ sheets were renamed ‘V0304 Old’ and ‘detail SAM Old’. New versions of these sheets are ‘V0304’ and ‘detail SAM’, and include rows and columns for the new sectors. On the ‘detail SAM’ sheet, transactions between the new sectors and all non-industry sectors (i.e. households, govt, etc) are blank.
- The ‘ROW’ sheet was expanded to accommodate the new sectors, and otherwise functions as it did before.
- The sheet ‘detail SAM allocated’ fills in the blank transactions between the new sectors and non-industry sectors. This is accomplished by allocating the transactions from the old SAM to the new sectors based on labor shares. For example, the taxation of the new Aluminum sector is determined by multiplying the old taxation of the PrimaryMetals sector by the percentage of PrimaryMetals labor that is Aluminum. These percentages are calculated on the sheet ‘Labor Use’, which is used as a guide in filling in the ‘detail SAM allocated’ sheet.
- The ‘Energy’ sheet contains a paste from Energy_data_10.xls. It has been expanded to include the new industry sectors. A flow chart of how ‘detail SAM’ incorporates energy adjustments to get ‘detail SAM energy correct’ can be found at SAM120_Energy-Flow-Charts.doc, and a detailed description of the calculations can be found in SAM120_Energy-Flow-Chart_comments.doc.
- The ‘detail SAM energy correct’ incorporates the data from the ‘Energy’ sheet into the ‘detail SAM allocated’ sheet, creating a SAM that more accurately reflects energy use at 2003 prices.
- The last sheet 'SAM' contains the final SAM. It is similar to the 'detail SAM energy correct' worksheet, except that the RETAIL sectors have been consolidated. The only adjustment is the addition of SAM('OILREF', 'CTRNS') to SAM('OILREF', 'RETGAS') and SAM('RETGAS', 'CTRNS') and subtraction from SAM('OILREF', 'CTRNS').
Beginning with ‘SAM_DRH’, the matrix includes a number of changes from the previous matrix. Major changes are done on the INVES and ROW columns due to recalculations of investments in the CCM (refer to CCM0304-822_for_120.xls). A big change to (‘ROW’, ‘RETELC’) and other minor changes were made to fix negative SAM elements for exports and imports. These changes are indicated by comments.
The remaining two sheets, ‘dSee_Tr’ and ‘SAM_posi,’ transpose the matrix and then makes everything positive, respectively. ‘SAM120original’ is a paste from SAM120original.xls. ‘Diffs’ is a comparison of the resulting matrix and the original SAM120, and shows the maximum and minimum difference. The final sheet ‘SAM’ is a rounding of ‘SAM_posi’ to 6 digits.
The first sheet, ‘SAM_raw,’ builds onto ‘SAM_DRH’ from the previous file. ‘SAM_DRH’ contained outdated SAM elements for tax interactions so changes to taxes were made.
The following two sheets, ‘dSee_Tr’ and ‘SAM_posi,’ transpose the matrix and then makes everything positive. The final sheet ‘SAM’ is a rounding of ‘SAM_posi’ to 6 digits.
The first sheet, ‘SAM_raw,’ only includes one change from the previous ‘SAM_raw’. It routes payment of gasoline from RETGAS to OILREF. (‘RETGAS’, ‘CTRNS’) is added to (‘OILREF’, ‘RETGAS’) and subtracted from (‘ROW’, ‘RETGAS’) and (‘OILREF’, ‘ROW’) to maintain balance. The following two sheets transpose the resulting matrix and make all the values positive. It is then compared to the original SAM120 in the ‘Diffs’ sheet. The row sums and columns sums are checked in the ‘Sums’ sheet.
The final sheet ‘SAM’ rounds the resulting SAM to 6 digits, and is the matrix that will be used for SAM120.prn E-DRAM input.