Initial Values and Convergence Section
The Mark II Economy spreadsheets, http://www.dematerialism.net/Mark-II-Economy.xls and http://www.dematerialism.net/Mark-II-Economy-CSP.xls, are divided into four sections: (i) the Data Section from Column AA to Column CO on both Sheet 1 and Sheet 4, (ii) the Initial Values and Convergence Section from CP to DA on Sheet 1, (iii) the Results Section from DN to DZ on Sheet 4, and (iv) the Computational Section from EA to GL on Sheet 1 and EA to IV on Sheet 4 with one or more exceptional columns that are placed according to the whim of the author.
Although I am colorblind, I tried to set the background of cells from which the computed guesses for next iterates are to be copied to light yellow and the cells into which they are to be pasted light green as in the case of the fractions of the cash flows to the sectors retained to pay salaries in columns DA and CQ. I chose light green for cells in which design variables are to be initiated such as xo in CQ15 and gold for important final results such as the fractions of the energy budget consumed by the six sectors in CU6 through CU11. Saved values from previous calculations are stored in cells with a pink background (that looks like purple to me) such as CU25 through CU30. Occasionally a red background is used for a repeated value of a variable such as the initial values for the total number of AUs, MUs, TUs in the money calculation stored in DM3 through DM5 taken from the results in DM6, DM8, and DM9. (Red background has been used lately to identify cells to which I need to pay special attention.)
Unfortunately, exceptions to the color rules abound. In a subsequent version of this paper a table will appear with a description of the principal symbols used in the spreadsheets. Please accept my apologies for not having gotten around to making every subscript a subscript and employing the Greek alphabet properly in most cases. Moreover, because the spreadsheet grew like Topsy, the arrangement of the various collections of data leaves much to be desired. If interest is sufficiently great, I promise to take the extra pains to clean things up.
Columns AB through AF contain πj, j = C,A,M,T,E, the fractions of the population in the commercial, agricultural, manufacturing, transportation, and energy sectors. The tentative fractions of the GDP for the sectors are in AH:AM but is not used. The fractions of gross cash flow to the sectors that is retained for salaries and other payments to stakeholders are in AW:BD. Column BD holds the fraction of the gross cash inflow to commerce that is passed on to other sectors, namely, β = 1 - xo - xMF - xTF - xEF - xC.
Columns BL to BO hold the fractions of the gross cash flow to each sector retained for overhead except for the commercial sector which retains fraction xo. Column BP holds θ, the fraction the worker’s consumption of each commodity retained by furloughed stakeholders. The variable κ is in BQ and η in BR. Columns BS through CF are retained by the fij and the δij. The prices are in CH through CO which ends the data section.
If you press <control>a (abbreviated <c>a), you will be looking at the Initial Values and Convergence Section of the spreadsheet. It runs from Column CP to Column DA.. The sequence of Newton iterations that plugs the kth iterates in Column DA into the k+1th iterates in CQ will be described in detail in an appendix that will appear in a subsequent version of this paper. It determines the prices of the commodities, pi, the fractions of net cash withheld to pay salaries, xi, the fractions of the population associated with each sector, πi,, and the fractions of each sector on active duty, zi. For the Base Case, one can take one Newton step by pressing <control><shift>O (abbreviated <CS>O) on both spreadsheets. For the other cases, press <CS>O on Mark-II-Economy.xls and <CS>Z on Mark-II-Economy-CSP.xls. (CSP stands for constant sector population.)
The four fixed values of ηi, i = A, R, M, T, the consumptions per capita of the four commodities, are found in CT23:CT26, the fixed values of θi, i = A, R, M, T, the factor by which the consumption of each of the four commodities is diminished by furloughed people, and κi, i = A, R, M, T, the increase in total consumption of each of the four commodities of workers because some workers are paid more than others, are found in CU23::CV26, and the fixed value of zC is found in CR11.
The number in CV16 is the sum of the squares of the differences between CQ3:CQ26 and CZ3:CZ26 or between CQ3:CQ26 and DA23:DA26. The number in CV17 is the sum of the squares of the difference between 1.0 and the sum of the fij, i = E, for j = M, T, E, stored in GX31, which is the hardest constraint equation to converge. The number in CV18 is the result of a simple test to determine if 711 rows are sufficient to contain all of the non-negligible computational data generated by successive trips through the economy. The number in CV19 is the sum of the squares of the differences between the salaries and expenses in DP10::DY11. If the numbers in CV16, CV17, CV18, and CV19 are less than 10-11, we have convergence.
The other values stored in the Initial Values and Convergence Section are labeled or are ancillary values used in the computation. They will be described in more detail in an appendix to be written later.
To go immediately to the results section on Sheet 4 between Columns DB through DZ press <control>v. The accounting tables for commodity units (DB to DM) and monetary units (DN to DZ) are across the top of the page. These are, at the same time, initial values for the rest of the computation and results to be pondered. Column DO contains the principal results, which are labeled in Column DN. We shall be discussing these results in the Experiments Section which is hyperlinked to the cover page. If we may be permitted to identify rectangles of data by their upper left and lower right cell names separated by two colons, the immediate EROI results are in DN52::DR65; these results are tabulated for the various cases in DN69::DV92; the computation of production per worker and the ratio between them for various cases can be found in DN42::DX46; the results of estimating ΔE with E/GDP and monetary and energy fractions are given in DN30::DY35 where, for purposes of comparing fractions of energy flows computed in CU6:CU11 of Sheet 1 to those of the US economy, the agriculture, manufacturing, and energy sectors are lumped together in an industrial sector in DX31 of Sheet 4; important results for GDP, energy (E), E/GDP, percent energy saved, work hours per week, and production are identified in Column DN between Row 18 and Row 46. Experiment 4, in which consumption and energy overhead are varied to reflect the extent to which conservation measures have been applied to adjust the energy budget relative to David Pimentel’s figure for Maximum Renewables, is labeled in DN94 at the upper left hand corner of the block wherein the results are displayed. DN100 contains the label for Experiment 5 in which energy and other statistics are recorded as EROI is reduced to the point where the spreadsheet fails to converge well above 1.0. The data for Chart 1 are tabulated in DN121::DR126; and, the emergies for the four commodities are recorded and tested in DN128::DQ136. Most results are labeled well enough; however, I will describe them in greater detail in Mark-II-Experiments.html.
Press <c>d. The principal computation is done in EA2::FK303. In the next version of this paper, I will spare no effort to explain every cell on the spreadsheet to enable the reader to use the spreadsheet to do other experiments and as a memory aid for myself. For this early draft, however, I will defer a detailed description until later except that I have been leaving messages attached to various cells throughout the spreadsheet explaining such details as I am able to explain at this time. Clicking the Messages command on the View drop-down menu toggles the messages from all-shown to all-hidden or back.
Suffice it to say that, starting with the number of units of the four commodities required by the consumers, we compute on each successive pass through the commercial, manufacturing, transportation, and energy sectors the amounts withheld for distribution to both the furloughed and active workers and the overhead for the C, M, T, and E sectors. The agricultural production and the salaries of the workers are disposed of in Rows 3 and 6 leaving only the overhead from agriculture to iterate through the economy. Otherwise, the overhead from each pass through the four sectors consisting of just four rows is collected in the appropriate places in the next set of four rows to pass once again through the economy. It requires energy, for example, to produce 1 MU; and, it requires MUs, TUs, and more EUs to produce that energy which leads to tertiary expenses until the transactions dwindle down to insignificance (zero to eight decimal places) after which the computation ends as evidenced by a positive number very close to zero in DO39 and CV 18 of Sheets 1 and 4, i. e., 10-50 or less. When EROI is forced to get as close to zero as possible this number eventually grows to an order of magnitude near one and the spreadsheet is no longer converged. Each and every transaction must pass through the commercial sector or the financial planning apparatus where cash is withdrawn to pay for the overhead (except in the no-commerce cases) and to pay for the living expenses of commercial employees or furloughed people from any sector. In the no-commerce cases, the economic planning apparatus (still called the commercial sector) has no employees and no overhead; but, cash is withdrawn (automatically) to pay for the living expenses of all furloughed employees.
Columns FP to FU contain the energy consumption of the individual sectors; FV6:FV11 hold the energy fractions consumed by the individual sectors computed from FP to FU; Column FX holds the sums of the cash inputs to the M, T, and E sectors; Column FY holds the difference between Column FX and the net cash from the commercial sector, which is a secondary way to verify that the constraint equations are satisfied at every level provided it holds all zeroes; and GA:GE hold the values of zi, i = C, A, M, T, E, the fractions of the population of each sector on active duty.
Finally, go to Sheet 4 and press <c>b to go to the separate calculations to obtain the separate components of EI. Columns GM through IV of Sheet 4 hold the new EROI computation. In a subsequent version of this paper I will describe this calculation thoroughly in a section on Methods hyperlinked to the cover page. The same columns on Sheet 1 hold the debris from the EROI computational method that was previously used and which gave incorrect results. The old results were close enough to the correct ones and led to sufficiently correct conclusions about the way EROI changes with political economy that it was difficult to find anything wrong with them until under extreme circumstances they gave an EI that exceeded ER.
Thomas L Wayburn
Houston, Texas
October 15, 2006