Turning off the Equilibrators The model file consists of four worksheets:
- Data - Equations - Tables - Graphs
To perform an update for this file, one should first go to the bottom of the Equations sheet and turn off the equilibrators. This will keep the model from possibly erroring out as new data is introduced. The three equilibrators, shown in Figure 1, consist of a line that reflects the price that is currently being used by the model (“Old Price”), a line that computes the difference in supply and demand that results at the price currently used by the model (“Supply – Demand”), and two line that contain formulas to adjust the model to a new price that more closely aligns supply and demand (“New Price”). This new price is what the Data sheet of the model will access. When a model is in equilibrium, as is the case in Figure 1, the difference in supply and demand is zero. To turn off the equilibrators:
- Go to the new price lines located in the three equilibrators
- Perform an Edit-Copy, Edit-Paste Special-Values beginning in the first year of the forecast until the last year of the forecast (Do not overwrite the formulas in historical observations, this will keep the formulas needed to turn the equilibrators back on later).
The Equilibrators
Updating historical data
Once the equilibrators have been turned off, the next step is to update historical data in the Data sheet. It consists of five sections:
- Endogenous beef data available in data publications - Endogenous beef data calculated by the model - Endogenous pork data available in data publications - Endogenous chicken data available in data publications - Exogenous data
In order to update data that was already available historically and has just been revised (this data will appear as a typed in number in the data sheet), simply type over the old historical data with the revised number. In order to update data that has previously been forecasted by the model, note the formula that resides in the cell before typing in the new historical data number. This is important so that you can see which equation (or calculated data) now has an additional observation.
How to deal with this fact will be discussed later.
The “endogenous beef data calculated by the model” section is different from the other data sections. All of the data that exists historically has been computed by formulas referencing other available data. In the forecast period, some of these data lines reference the Equations sheet, while others continue to run with formulas. The most important thing to remember when updating this section is when one should copy formulas to the right as more data becomes available, allowing more historical calculated data to be available. For example, Female 1-2 Slaughter is calculated historically as Female Beef Cattle Slaughter less Cow Slaughter. In the forecast period, this variable is determined in the Equations sheet.
When another data observation for Female Beef Cattle Slaughter becomes available and is typed into the “endogenous beef data available in data publications” section, the formula for Female 1-2 Slaughter can be copied forward one period as well. This is likely the most difficult concept to updating data, and much time will be spent talking about it in person.
Forecasting exogenous data
The next step after updating the historical data in the Data sheet involves forecasting the exogenous data. The exogenous data resides in the bottom section of the Data sheet. Analysts employ judgment about past trends and market knowledge to forecast exogenous data not available in data publication forecasts.
Lining up the equations
The Equations sheet contains equations for all endogenous variables, other than those generated by a simple calculation process. A typical equation, as shown in Figure 2, contains a description of the independent variable and parameter estimates for the dependent variables in Column A, and lists dependent variables in Column B.
In addition, SUM, ADJUSTMENT, ESTIMATE, and ACTUAL lines are denoted in Column B. The rows corresponding to the parameter estimates and dependent variable descriptions contain formulas of the product of the parameter estimate and the dependent variable as it resides in the Data sheet. The SUM row merely adds together the contributions of each dependent variable. The ADJUSTMENT line is the error term of the equation, and historically is simply the actual level of the variable less the SUM line. In the forecast period, analysts use the ADJUSTMENT line to alter error terms as deemed appropriate due to market knowledge, identification of trends, etc.
The ADJUSTMENT line often begins as a formula setting future error terms equal to the last observed error term, and any necessary modifications are made from this point.
The ESTIMATE line purely adds the SUM and ADJUSTMENT lines, and this is the line that the Data sheet references for forecasts of endogenous variables. The ACTUAL line references the Data sheet to show the current value of the independent variable as it resides in the Data sheet.
Sample Equations
When additional historical data observations become available, an analyst must copy the (ACTUAL – SUM) formula in the ADJUSTMENT line forward in order to account for the fact that new data now exists for the equation. This allows the analyst to incorporate the additional observed error term into the decision process for setting the path of future error terms.
Turning the equilibrators back on
In order for the model to be in equilibrium, the equilibrators must be turned back on at some point. The timing of this depends on the preference of the analyst as well as the level of disparity between supply and demand. When the equilibrators are turned off, as occurred in the first step, market clearing prices are not allowed to move with changes in supply and demand. Instead, non-zero values appear in the supply – demand line of Figure 1. Once the equilibrators are turned back on, the market clearing prices will adjust in order to balance supply and demand. Through experience, an analyst can often have a feel for how much price adjustment must take place in order for the model to be in equilibrium from a non-equilibrated state. If the level of price adjustment seems to be in reason, the analyst will turn the equilibrator on. If the level of price adjustment appears to be too radical, the analyst may re-examine the error terms in the Equations sheet to better align supply and demand before turning on the equilibrator.
The process of turning on the equilibrator is simply undoing what was done in the first step.
- Go to the formula residing historically in the New Price line - Copy it forward into the forecast period
- Calculate each cell containing the just copied formula (F2-ENTER) before calculating the entire spreadsheet by simply hitting F9.
Tables and Graphs
The Tables sheet contains the output tables for the model. The Graphs sheet contains selected graphics from the model output. Feel free to make any modifications to these sheets, in order to provide the output desired by the modelers.
Erroring out the Model
It is possible to cause the model to begin to show errors for all of the endogenous variables in the forecast period if the modeling system receives shocks too severe for it to handle. When this occurs, the analyst has choices about how to fix the situation. If little work will be lost by simply closing the file without
saving it and starting over, this is often the easiest solution. However, there will be times in which the best solution is to try and “recover” the model. This can be accomplished by copying the formulas in the forecast period of the data sheet to the blank area to the right of the forecast period (in order to still have these formulas available). Then, paste reasonable values into the cells that have errors in them. Calculate the spreadsheet by hitting F9 in order to remove errors from the equations sheet. Then, copy the formulas that were off to the right back into the cells that originally had formulas in them. You will want to F2-Enter these cells as you copy the formulas back before calculating the entire spreadsheet by hitting F9.