This book is basically a very large set of national accounts. It contains several sheets which calculate the final energy demand for different sectors, based on the input parameters and calibration data.
Input parameters are controlled from the Scenarios sheet. This contains a set of input boxes where options can be selected, such as the rate of population growth, or the rate of technology shifting in the transport sector. Not all of these have more than one option yet - which is something we would like to change.
The calculation sheets should be read vertically - each column represents a country or group of countries, each row a stage in the calculation.
The calculations are relatively simple, but because there are 40 countries and 10 fuels to consider, they are rather large, and can be difficult to navigate.
The general method for calculating future final energy demand is the same for all sectors:
We start by calibrating to 2010 - giving data on energy production and consumption, technology mix and efficiencies, and demand for energy services
Energy services are projected forwards to 2050, based on population, incomes and prices (although prices aren't factored in yet)
Modal shift, technology mixes and efficiencies for 2050 are drawn from scenario data
These are combined to give annual final energy demand
Extraction and refinery losses are incorporated as appropriate
Primary energy demand (including that used to generate electricity) will then be calculated from the other modules (which consider the electricity sector in greater depth)
A basic workflow with this model would be:
Go to the Scenarios sheet
Choose the set of parameters you would like to investigate
Go to the Annual sheet and browse results
Chances are, at the moment, the parameters you would like to investigate aren't already in the model. They could be directly implanted into the calculation sheets, but that would make it difficult to switch between parameters later on, or to allow other users to investigate them. Instead, you could create a new scenario for these parameters.
There are three steps involved:
Add a new set of parameters to the Scenario Data section of the relevant calculation worksheet.
Choose a label to identify your scenario and add that to the list of valid scenarios on the Scenarios sheet
Go to the cells that are controlled by this scenario and add the new parameters as an option.
You want to add a new technology mix for aeroplanes.
Step 1 - add new parameters:
Go to Air ~> Scenario Data ~> 2050 Technology Mix (Air!B109)
Copy the “High Change” group of data (116:119)
Paste it below (121:124), and change the values to what you would like. Let's say fuel cells on planes may become popular, so air travel will demand lots of hydrogen.
Step 2 - make a scenario to select them:
Go to Scenarios ~> Air Transport ~> 2050 Technology Mix (Scenarios!H251)
The grey box which controls the scenario (H251) takes its valid options from the range AA251:AB251
Add “FC” to AC251 – this is the identifier for your new scenario
Add 3 to AC252 – this is the sequential number that corresponds to the scenario
Add “heavy on the fuel cells” to AC252 – this describes your scenario
With the grey control cell selected (H251) go to menu ~> Data ~> Data Validation ~> Source
Extend the range of valid inputs to include your new “FC” cell, so that it reads AA251:AC251 - this allows your scenario to be selected from the grey box
Note, that if you want more than 11 options for a given parameter, you will have to also edit the HLOOKUPs in Column I and J. These cells convert the scenario labels into a number, which can be easily used in the data sheets to pick between values, and a description (the italic grey text) to inform the user. These lookups are a necessary consequence of using Excel sadly… I could probably improve this with a macro, one day...
Step 3 - make your new scenario visible to the model's calculations:
You need your new scenario data to be usable within the calculations of the data sheet. You must locate the cells in the sheet that are affected by this scenario, and extend them to use the new parameters you entered.
You can find which cells are affected by selecting a parameter from one of the existing sets - e.g. Air ~> Scenario Data ~> 2050 Technology Mix ~> High Change (e.g. Air!C117)
Go to the menu ~> Formulas ~> Trace Dependents. Arrows will now point to all cell that rely on this parameter.
In our case, there should be an arrow leading to cell C56 - which is part of the 2050 Technology Mix selector block. This seems sensible, as we're looking at parameters for 2050 technology mix.
Selector blocks like this can be identified as they have a grey box in column A, indicating which scenario is currently chosen. This scenario decides which set of parameters is displayed in the selector block.
The selector blocks work using CHOOSE(), which take the scenario number (1,2,3) and choose a particular value based on it (tech change in the low, high, and fuel cell case)
Adjust the CHOOSE() variable to include your new parameter as the last option -- (CHOOSE(SCEN_AirTechMix, C112, C117, C122)
Tadaa!
Not yet!!
Aplenty!!