LET OP: na het downloaden de extensie wijzigen in xlsb
In het vorige artikel (Grenzen aan de groei – 1) hebben we beloofd dat we een poging zouden wagen om het model uit het rapport van de Club van Rome in Excel te implementeren, althans een vereenvoudigde versie daarvan.
In dat artikel is te lezen hoe we dat zouden willen doen en aan de hand van wat vingeroefeningen hebben we laten zien dat het (in theorie) mogelijk zou moeten zijn.
Die laatste conclusie staat nog steeds, maar helaas hebben we wel moeten constateren dat de hoeveelheid verbanden tussen de diverse variabelen en de daarbij behorende parameters zo groot is dat een totale implementatie heel erg veel tijd gaat kosten. Dus deze keer vormt het Voorbeeldbestand geen afgerond project.
In dit artikel zullen we laten zien hoe ver we gekomen zijn en welke Excel-opties daarbij zijn gebruikt.
Voor eenieder de uitdaging om de ‘handdoek in de ring’ weer op te rapen en het model verder uit te werken!
Nogmaals: het model
Dit is een schematische weergave van het (vereenvoudigde) ‘Club van Rome’-model; voor nadere uitleg zie het vorige artikel.
Belangrijk om te weten is het volgende:
- de pijlen geven aan welke relaties er tussen de variabelen zijn onderkend
- de pijlen laten zien in ‘welke richting’ de beïnvloeding loopt
- we onderkennen in het model 3 soorten variabelen: de Inputs, de Kritische Factoren en de Tussen-variabelen.
De Input-variabelen worden niet beïnvloed door de omgeving, maar kunnen wel in de loop van de jaren variëren.
De KF’s zijn die 5 variabelen die in alle grafieken van het rapport terugkomen.
Alle overige vallen onder de categorie Tussen-variabelen. - het rapport van de Club van Rome is in 1972 gepubliceerd; waar in dit artikel naar het verleden wordt verwezen bedoelen we dan ook de periode van 1900 tot en met 1970.
- voor diverse variabelen is in de literatuur (zie het tabblad Docu van het Voorbeeldbestand) te achterhalen wat de waardes in het verleden zijn geweest. Dit is de eerste basis van de implementatie van het model.
- bij de verdere implementatie heeft iedere variabele een eigen tabblad gekregen (behalve de Inputs; zie hierna). Alle aannames voor de berekening van een variabele staan in het betreffende tabblad vermeld. Vaak is daar ook een grafiek opgenomen van die variabele om snel het resultaat van de aannames te controleren.
Implementatie
Tabblad Beschr
In dit tabblad van het Voorbeeldbestand staat een overzicht van alle gebruikte variabelen met daarbij (zover al uitgezocht) de meest relevante berekening:
De betekenis van de Code en de Naam moge duidelijk zijn; de kolom Schema bevat de tekst zoals die op het tabblad SystemDynamics wordt gebruikt. In de kolom Omschrijving staat in het kort een nadere toelichting op de variabele en de laatste kolom bevat de (belangrijkste) formule voor de berekening van die variabele.
NB de codes onder de 10 voor de Tussenvariabelen hebben een extra 0 gekregen; dit om bij standaard-sorteringen altijd direct de juiste volgorde te hebben (anders zou bijvoorbeeld T10 vóór T2 komen).
Tabblad Inputs
Dit tabblad bevat de gegevens van de 4 Input-variabelen. Deze vormen samen de Excel-tabel tblInputs.
Op dit moment zijn alleen de I3 en I4 gevuld en per kolom hebben alle jaren dezelfde waarde. De juiste interpretatie van deze variabelen vergt nog onderzoek.
De KF-tabbladen
Zoals gezegd hebben alle KF’s (en ook de Tussen-variabelen) een eigen tabblad. Deze bladen hebben allemaal dezelfde structuur: de eerste kolom bevat de jaren en de tweede kolom de daarbij behorende waarde. Samen vormen deze 2 kolommen een Excel-tabel met een overeenkomende naam (in bovenstaand voorbeeld tblKF1).
NB de waarde-kolom heeft een voorwaardelijke opmaak: wanneer een cel een formule bevat dan wordt de inhoud in het groen, vet en cursief weergegeven.
In de volgende kolommen staat altijd de code en naam van de variabele, eventueel gebruikte bronnen en de diverse aannames.
De rest van het tabblad wordt gebruikt om zo nodig extra berekeningen uit te voeren, de aannames toe te lichten etcetera.
NB bij KF5 wordt een zogenaamde Eigen functie gebruikt (zie voor een toelichting het vorige artikel, Vingeroefening 2).
De T-tabbladen
LET OP in dat artikel hebben we, bij het gebruik van een periode, een rekenkundig gemiddelde gebruikt. Dat is, theoretisch gezien, niet juist. Hoewel dat in dit model, bij niet te grote ontwikkelingen in de tijd, niet echt relevant is, hebben we toch de betere methode gehanteerd waarbij het gemiddelde wordt bepaald met behulp van de Pde-machts wortel (ofwel tot de macht 1/P).
Resultaten
In principe zijn de resultaten van het model bekend als alle tabbladen zijn gevuld met waardes en formules. Door ’te spelen’ met de aannames, en vooral met de eventuele GF-, V– en P-waardes kan het model gefinetuned worden.
We zijn gestart met de onderkant van het model, het gedeelte rond de Industriële productie. De bovenkant is op de uitkomsten daarvan gebaseerd. Het implementeren van deze onderkant heeft heel wat hoofdbrekens gekost (wat is de betekenis van de variabelen, hoe zijn deze variabelen van elkaar afhankelijk, hoe kunnen we de afhankelijkheid modelleren, welke GF-, V– en P-waardes geven een zo getrouw mogelijk beeld van de werkelijkheid etcetera).
Door tijdgebrek moet G-Info het verder vullen van het model dan ook aan anderen overlaten.
Wel zullen we hieronder nog laten zien op welke manier de resultaten in Excel gemakkelijk kunnen worden weergegeven.
Tabblad Variabelen
Op dit tabblad in het Voorbeeldbestand staan de 25 variabelen van het model nogmaals in een overzicht. Maar ditmaal met aanvullende gegevens, waarmee we de lay-out van de output kunnen sturen:
- in de eerste kolom staat aangegeven welk soort variabele het betreft: Input, KF of Tussen
- de tweede kolom geeft aan of de betreffende variabele in de standaard-grafieken van het rapport van de Club van Rome is opgenomen
- in dat rapport wordt een variabele altijd op dezelfde (onzichtbare) schaal weergegeven. Dat kunnen we nabootsen door aan te geven welke waarde van de variabele overeenkomt met de onderkant van het grafiekgebied (de kolom GrafMin) en welke waarde met de bovenkant (GrafMax)
- in de laatste kolom staat een formule waarmee het label bij de betreffende lijn in de grafiek wordt bepaald. Standaard is het een combinatie van de 3e en 4e kolom, gescheiden door een ‘harde return’:
Dit overzicht is een Excel-tabel met de naam tblVar.
Query’s en verbindingen
Alle resultaten moeten nu nog geschaald worden. Dit kan uiteraard op de diverse tabbladen zelf door extra kolommen toe te voegen, maar we hebben er voor gekozen om dit met behulp van Power Query te implementeren.
NB1 Ziet u de Excel-verbindingen aan de rechterkant van het scherm niet, kies dan in de menutab Gegevens de optie Query’s en verbindingen.
Voor alle Excel-tabellen is een verbinding gemaakt. Wil je bekijken hoe die er uit ziet? Klik rechts op een verbinding en kies de optie Bewerken (of dubbel-klik op een query-naam).
NB2 de Input-query’s zijn iets ingewikkelder omdat daar één specifieke kolom uit de input-tabel moet worden opgehaald.
Bij het bewaren van de query’s is de optie Alleen verbinding maken gekozen en is de optie Toevoegen aan gegevensmodel aangevinkt.
Die laatste optie zorgt er voor dat de query’s in het gegevensmodel van deze Excel-sheet worden opgeslagen.
Het gegevensmodel bevat nog één extra query, q_GrafData. Deze combineert alle andere verbindingen tot één database. Deze database vormt de basis voor een draaitabel en een daarbij behorende grafiek.
Ook deze query is opgeslagen met de eigenschappen Alleen verbinding en Toevoegen aan gegevensmodel.
Bekijk de query door dubbelklikken op de naam.
Tabblad GrafData
Op het tabblad GrafData van het Voorbeeldbestand staat een draaitabel, gebaseerd op de query q_GrafData. Hoe genereer je zo’n draaitabel?
- selecteer een lege cel, waar de draaitabel moet komen
- kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
- in het pop-up scherm ziet u dat Excel het gegevensmodel zal gaan gebruiken:
- klik op het driehoekje vóór de gewenste query uit het gegevensmodel en sleep de benodigde velden naar de juiste plaats:
In het voorbeeld hebben we ook 3 slicers toegevoegd waarmee het maken van keuzes vergemakkelijkt wordt. Hierboven hebben we met een klik op Ja in de eerste slicer alleen die variabelen geselecteerd, die ook in het rapport van de Club van Rome in de standaard-grafieken voorkomen.
LET OP als er iets aan de parameters van het model wordt gewijzigd dan worden de gegevens op het betreffende tabblad direct gewijzigd. Ook resultaten van formules op andere tabbladen kunnen daardoor wijzigen. De query’s en de draaitabel wijzigen niet automatisch mee! Alles zal vernieuwd moeten worden.
Op het tabblad GrafData wordt met één klik op de betreffende button een VBA-routine gestart die deze totale verversing van het Excel-systeem voor zijn rekening neemt. Dit kan wel enkele minuten duren!
Tabblad Grafiek
Het tabblad Grafiek van het Voorbeeldbestand bevat een draaitabelgrafiek. Dit is de grafische weergave van de gegevens van de draaitabel van het tabblad GrafData. Dus: ook keuzes gemaakt met de slicers worden in deze grafiek meegenomen.
Duidelijk is te zien dat de implementatie van het model nog lang niet klaar is. De industriële productie en de hulpbronnen geven bijvoorbeeld wel al een verwacht verloop, terwijl de blijvende toename van de bevolking of voedsel per hoofd niet reëel is.
Het model in het Voorbeeldbestand is dan ook nog maar voor een klein gedeelte geïmplementeerd. Zoals gezegd: tijdgebrek noopt ons om de rest aan andere Excel-liefhebbers over te laten.