Workbook: SAM.xls

 

Notes / Method: Supporting worksheets

 

·         While the worksheet ‘SAM’ holds the final SAM, and ‘detail SAM energy correct’ holds a more detailed version (‘SAM’ collapses all the retail sectors into one sector called RETAIL), the other worksheets do the job of holding all supporting data.  We go through these sheets one by one.

·         ‘Energy’ holds data and calculations that correct the non energy version of the SAM (the ‘detail SAM’ worksheet) so that it better reflects energy btu transactions and their prices.  The yellow section of this worksheet is a paste values from ‘Energy data.xls’.  The primary data calculation takes place in the box below labeled ‘Update to SAM for purchases of goods from energy industries’.  Most of the cells in this box are simply price corrections; however, the yellow cells are specific large corrections that utilize large btu transactions that were pasted in from ‘Energy data.xls’.  The calculations further below are simply minor reallocations of consumption to ensure that the consumption sectors and household sectors still balance.

·         ‘detail SAM’ is a summary sheet and is linked to the supporting sheets below.

·         ‘V0304’ is the IO table, and is a direct paste values of the IO table from ‘V0304.xls’

·         ‘G0304’ covers most government transactions (does not include personal taxes and transfer payments) and is a direct paste values from the ‘g0304’ worksheet of ‘g0304.xls’

·         ‘Govtchecks’ is a validation worksheet which verifies that the final total government revenues and expenditures from the SAM match the same values from g0304.xls.  It does not contribute any new data to the SAM

·         ‘Indust prod’ is a direct paste values of the ‘Aggregate Industry Consumption’ sheet from ‘CA Household industry demand.xls’

·         ‘HH Agg cons raw’ is a direct paste values of the ‘Aggregate Household Consumption’ sheet from ‘CA Household industry demand.xls’

·         ‘HH Cons’ adjusts the consumption in ‘HH Agg cons raw’ so that each HH sector in the SAM balances.  The raw HH consumption data, originally from IMPLAN, does not allow the very poorest and very richest households to consume enough, and allows the middle income households to consume too much (the aggregate over all households is correct by design, however).  The ‘Ratio to apply’ row shows gives factors by which to adjust the consumption of each HH sector: this is accomplished in the box immediately below it.  Unfortunately, this adjustment throws the aggregate consumption sectors slightly out of balance.  The box labeled ‘Allocate out differences’ corrects for this.

·         ‘Ptax’ is a direct paste values from the ‘Calcs’ sheet of ‘PIT0304.xls’

·         ‘N0304’ is a direct paste values from the ‘N0304’ sheet of ‘CCM0304.xls’

·         ‘HH saving’ is a direct paste values from the ‘Bridge to Disposable Income’ sheet of ‘CA Household industry demand.xls’

·         ‘F’ is used to calculate household wage and capital income in such a way that the LABOR sector balances, and total personal income equals the BEA figure of $1,142.6bn.  Income tax data from the ‘Ptax’ sheet are used to calculate the shares of total labor and capital income attributable to each HH sector.  Total labor income is then the sum of all payments to labor in the SAM, less Social Security.  Total capital income equals total CA personal income (from BEA), less labor income (post SS tax), less government transfer payments.

·         ‘TP0304’ calculates government transfer payments to households.  The bordered yellow box at the bottom left is a paste values of the yellow box on the ‘TP0304’ worksheet of ‘g0304.xls’, and indicates the total amount of transfers paid by each govt spend sector.  The two boxes at the far right of the sheet then estimate the distribution of these funds amongst the household categories.  The numbers in red reflect estimates of the percentage of non-working households receiving benefits in each group, and are based only on what was in the 1999 version of the SAM; i.e., there is no hard data to support these.

·         ‘ROW’ calculates industry exports and imports.  The large bordered yellow box is a paste values of the yellow box on the ‘ROW’ worksheet of ‘ROW.xls’.  These values, originally from IMPLAN, show the ratio of exports to imports for each industry.  The net trade column comes from the difference of the column total and row total for each industry: positive values indicate net exports, negative values indicate net imports.  The remaining three columns then calculate estimated imports and exports for each industry.  In some cases, the calculations have been adjusted to disallow negative values.  This is done, in cases in which the IMPLAN data shows zero exports or imports, by setting imports or exports to zero.  In other cases, if the SAM data indicates net exports, then imports are set to equal IMPLAN data, and exports are set to equal imports plus net exports.  The opposite holds true in the case of net imports.  NOTE: ROW feeds directly to the ‘detail SAM energy correct’ sheet rather than the ‘detail SAM’ sheet.

 

Notes / Method: ‘detail SAM’ Worksheet

The SAM is divided into 64 blocks alternating in color from green to yellow; the following describes them one at a time starting at the top left and working down, then across:

·         Block (1,1): Industry payments to Industry.  This is the IO matrix.  All cells are linked to the ‘V0304’ sheet.

·         Block (2,1): Industry payments to labor.  Also part of the IO matrix.  All cells are linked to the ‘V0304’ sheet.

·         Blocks (3,1), (4,1), (5,1): Industry payments to commodities, households, and investment.  All null.

·         Block (6,1): Industry tax payments.  Linked to the corresponding block of ‘G0304’.

·         Block (7,1): Industry payments to govt spend sectors and ROW.  Null.

·         Block (8,1): Industry payments to ROW (imports).  Links directly to ROW sheet.

·         Blocks (1,2), (2,2), (3,2): Labor and capital payments to industries, labor and capital, and commodities.  All null.

·         Block (4,2): Labor and capital payments to households.  Links directly to ‘F’ sheet.

·         Block (5,2): Labor and capital payments to investment.  Null.

·         Block (6,2): Labor and capital payments to taxes.  Includes social security payment from ‘PTax’ sheet.

·         Block (7,2): Labor and capital payments to govt spend sectors.  Null.

·         Block (8,2): Labor and capital payments to ROW.  Value for labor is zero by design.  Positive value for capital indicates capital payments out of state.

·         Block (1,3): Production of commodities by industries.  Links directly to ‘Indust prod’ sheet.

·         Blocks (2,3) through (8,3): Production of commodities by non-industries.  All null.

·         Blocks (1,4), (2,4): Household payments to industries and labor and capital.  All null.

·         Block (3,4): Household purchases of commodities.  Links directly to ‘HH Cons’ sheet.

·         Block (4,4): Household payments to households.  Null.

·         Block (5,4): Household investment.  This block is calculated in the SAM itself.  It sets total investment equal to the total amount of household saving, and allocates this investment spend among households on the basis of capital income.

·         Block (6,4): Household tax payments.  This is mostly directly linked to the ‘Ptax’ sheet (via an array transpose).  The exception is social security payments, which are zero (they are instead directly paid out of labor).

·         Blocks (7,4), (8,4): Household payments to govt spend sectors and ROW.  All null.

·         Block (1,5): Investment in industries.  Linked directly to ‘N0304’ sheet.

·         Blocks (2,5) through (8,5): Investment in non-industry sectors.  All null.

·         Blocks (1,6), (2,6), (3,6): Payments from tax sectors to industry, labor and capital, and consumption sectors.  All null.

·         Block (4,6): Payments from tax sectors to households.  Social security and unemployment insurance payments linked to ‘TP0304’ sheet.  Otherwise null.

·         Block (5,6): Payments from tax sectors to investment.  Null.

·         Block (6,6): Payments from tax sectors to tax sectors.  Tax flows into CGENF (CA General fund) linked to ‘G0304’ sheet.  The payment from CGENF to CTREG (motor vehicle registration) is a plug to force CTREG into balance.

·         Block (7,6): Payments from tax sectors to govt spend sectors.  Mostly linked to ‘G0304’ sheet, with the following exceptions:

o        Payments from FTSOC to FSNON are plugged to force FTSOC to balance

o        Payments from FTPIT to FSNON are set so that all FTPIT revenues are paid into FSNON.  Thus, all federal tax revenues flow into the FSNON sector.

o        The payments from the local taxes to LSOTH are plugs so that the local tax sectors balance.  Thus, all local taxes, with the exception of some property tax revenue that flows directly into LSK14 (schools), flow into the LSOTH sector.

·         Block (8,6): Payments from tax sectors to ROW.  Null.

·         Block (1,7): Payments from govt spend sectors to industries.  Links directly to ‘G0304’ sheet.

·         Block (2,7): Payments from govt spend sectors to labor and capital.  Links directly to ‘G0304’ sheet.

·         Block (3,7): Payments from govt spend sectors to consumption sectors.  Null.

·         Block (4,7): Payments from govt spend sectors to households.  Fed non-defense spend and welfare link directly to ‘TP0304’ sheet.  Otherwise null.

·         Block (5,7): Payments from govt spend sectors to investment.  Null.

·         Block (6,7): Payments from govt spend sectors to tax sectors.  Social security and unemployment insurance payments for govt employees link directly to ‘G0304’ sheet.  Otherwise null.

·         Block (7,7): Payments from govt spend sectors to govt spend sectors.  Mostly linked to ‘G0304’ sheet, with the following exceptions:

o        Payment from FSNON to FSDEF is a plug to force FSDEF to balance.  Thus and federal imbalance (budget deficit) is isolated in the FSNON sector.

o        Payments from CSOTH to the other CA govt spend sectors are calculated so that each CA govt spend sector is in deficit to the extent that it transfers money to local govt.

o        Payments from LSOTH to other local spend sectors are plugged so that each local spend sector is in balance.

·         Block (8,7): Payments from govt spend sectors to ROW.  Null.  Positive values would only appear if there were a budget surplus.

·         Block (1,8): Payments from ROW to industries (exports).  Links directly to ‘ROW’ sheet.

·         Block (2,8): Payments from ROW to labor and capital.  Value for labor is zero by design.  Value for capital is zero as capital flows out of the state, not into it.  (Positive entry is in block (8,2).

·         Blocks (3,8), (4,8): Payments from ROW to consumption sectors and households.  All null by design.

·         Block (5,8): Payment from ROW to Investment.  Value is a plug to balance INVES.  Positive value indicates investment from out of state.  Note: the difference of blocks (5,8) and (8,2) indicate the net capital inflow to CA from out of state.

·         Block (6,8): Payment from ROW to tax sectors.  Mostly null by design, except for unemployment insurance and CA general fund, the values of which are plugs.  Positive values indicate a deficit, negative values a surplus.

·         Block (7,8): Payments from ROW to govt spend sectors.  Mostly null by design, all non-zero values are plugs.  Positive values indicate a deficit.  Federal deficit is located entirely in the FSNON sector.  The state deficit is spread amongst several state sectors.  The local deficit would be located entirely in the LSNON sector, except this deficit was set to zero by design of the ‘g0304.xls’ workbook.

·         Block (8,8): Payment from ROW to ROW.  Null.