Workbook: Energy data.xls
Notes / Method:
· The sheet ‘CALEB’ is an exported Excel worksheet from the CALEB MS Access database. The export is the full-detail option with units of tbtu.
· The sheet ‘CALEB sign chg’ has changed the signs of some of the transformation sector cells from ‘CALEB’: these cells are highlighted in yellow. This was done so that both transformational uses of energy as well as energy consumption both have positive sign. This makes things easier in the other sheets.
· The sheet ‘SAM in dollars’ is a paste values from the ‘detail SAM’ sheet of ‘SAM.xls’. It is the dollar transactions SAM
· The sheet ‘Fuel mapping’ effectively adds a row to ‘CALEB sign chg’ which maps each fuel column to a SAM industry sector. (e.g. NGL is mapped to OILGAS)
· The sheet ‘Agg fuels’ aggregates the columns of ‘CALEB sign chg’ by the mapping above. The transformation sectors require special attention here. By default, natural gas consumption by any sector is mapped to DSTGAS. However, consumption of natural gas by electric utilities must be mapped directly to OILGAS, to better align with the dollar SAM. In addition, all natural gas produced by OILGAS that isn’t sold to electric utilities must be sold to the DSTGAS sector, so that DSTGAS may then sell gas to end-users. The boxed cell H2 indicates the percentage of natural gas that DISTEL obtains directly from OILGAS. The yellow cells then utilize this percentage to allocate the natural gas correctly amongst the transformation sectors.
· The sheet ‘Econ sector mapping’ is the main workhorse of the workbook. The far left columns map SAM sectors to CALEB sectors, with numerous one to many and many to one mappings, which are highlighted in yellow. The ‘Values from Dollar SAM’ section then pulls, for each SAM sector, the amount paid by that sector to the fuel sector indicated by the column heading. The ‘Energy Transactions from CALEB’ section similarly pulls, for each CALEB sector, the amount of energy used by that sector in the form of the fuel indicated by the column heading. The ‘Many to one factors’ section indicates, in the highlighted cells, the factors by which energy use from one CALEB sector may be allocated to many SAM sectors—these ratios are derived from the ‘Values from Dollar SAM’ section. Finally, the ‘Amount of energy to aggregate’ section simply multiplies each cell in the ‘Energy Transactions from CALEB’ section by each cell in the ‘Many to one factors’ section.
· The ‘More Allocations’ sheet first aggregates energy consumption under SAM sectors by applying the SUMIF function to the ‘Amount of energy to aggregate’ section of the ‘Econ sector mapping’ sheet. This occurs in the left set of columns. Note that DSTGAS’s consumption of OILGAS must be specially defined so that DSTGAS buys all natural gas that is not sold directly to DISTEL. The ‘Additional Allocation from Above’ set of columns then indicate specific adjustments to the aggregated consumption on the left side of the sheet. These additional allocations are due to the 5 adjustments specified in the box on the top of the sheet, namely:
1. Distribution losses, initially all taken by the DISTEL sector, must be allocated to end-use sectors.
2. Non-specified (industry) use of energy must be allocated to all industry sectors
3. Memo: Non-energy use Ind/Trans/Ener must be allocated to all industry sectors
4. CHP, Industrial Power use of energy, initially all taken by the DISTEL sector, must be allocated to industrial sectors.
5. CHP, Commercial Power use of energy, initially all taken by the DISTEL sector, must be allocated to commercial sectors.
· The sheet ‘Final consumption’ summarizes the results thus far. At the left are purchases by SAM sectors of energy produced by each SAM energy sector, in units of tbtu. To the right of these are the inferred price paid by each sector for energy, derived by dividing the dollar transaction from the dollar SAM by the energy transaction in tbtu. There are two summary sections at the top of the sheet. The top one is a check that the total energy consumption in tbtu indicated in this sheet matches energy production + imports – exports from CALEB. The bottom summary section calculates the average price paid for energy by fuel type. These prices should agree to some extent with actual energy prices as indicated on the ‘Prices’ sheet, described next.
· The sheet ‘Prices’ gathers price information for each fuel type, and calculates the ratio of price in 2003 to that in 1997. These price ratios are then corrected by the increase in average CA wages over the same time period. This must be done since the energy price ratios are designed to be used to update the dollar energy transactions in the SAM for real energy prices. However, the SAM dollar transactions have already been adjusted for changes in wages (see documentation for ES202 Dram Summary and V0304), so the increase in wages needs to be netted out of the increase in energy prices to avoid double counting.
· The ‘To SAM’ sheet is formatted for export to the ‘Energy’ sheet of the SAM.