Excel heeft een aantal functies om voorspellende analyses te vergemakkelijken. Dat is een feature die bijvoorbeeld van pas komt om omzetvoorspellingen te doen op basis van een nieuwe lockdown, of juist met het oog op aantrekkende verkoopcijfers post-corona. Cm: legt uit hoe de functie Scenariobeheer van de Wat-als-analyses werkt aan de hand van een voorbeeld.

Scenariobeheer (Scenario Manager) is, net als Doelzoeken (Goal Seek), een onderdeel van Excels Wat-als-analyse-tools. Met Scenariobeheer is het eenvoudig om te zien wat er gebeurt wanneer meerdere cellen veranderen. Dat kunt u ook handmatig doen met meerdere kolommen of tabbladen, maar vooral bij grote hoeveelheden gegevens of meerdere scenario’s, is dit een heel handige feature. Bovendien levert Scenariobeheer een samenvatting op een overzichtspagina waar alle scenario’s in één oogopslag kunnen worden vergeleken.

Voorbeeld: Omzet tekenwinkel

Ter illustratie ziet u hieronder de omzet per 11 juni 2021 van de fictieve winkel G. Apeldee Kunstkeet. De omzet van deze winkel is vorig jaar fors teruggelopen door een maandenlange gedwongen sluiting in 2020.

Met de wat-als-analyse binnen Excel heeft u in een handomdraai enkele scenario’s in elkaar gezet om te zien wat er gebeurt als de verkoop doorloopt op dit niveau, als de winkel weer even moet sluiten of als de verliezen van vorig jaar worden goedgemaakt door tekenaars die hun aankopen van 2020 alsnog doen in 2021.

In het eerste scenario wordt er ervan uit gegaan dat de huidige verkoop op peil blijft. Eigenlijk zouden dit ervan uit moeten gaan dat maanden = 9 of 10, vanwege de eerdere lockdown. Maar om het niet té complex te maken (deze keer) wordt gewoon 12 maanden gebruikt, alsof de winkel het hele jaar open is geweest. In het tweede voorbeeld een scenario waarbij de winkel twee maanden dicht moet. In het derde zelfs een gedwongen sluiting van drie maanden.

Tot en met vandaag is ongeveer 45 procent van 2021 verstreken. In die tijd zijn er 62 kleine potlodensets verkocht. 62 / 0,45 = 137,7. Dus eind december zouden er 138 potloden verkocht moeten zijn voor een totaalomzet van 1097,10 euro dit jaar – als u uitgaat van de huidige cijfers.

>> Lees ook: Zoeken in kruistabel, scenario’s berekenen en seizoenspatronen voorspellen

Scenariobeheer gebruiken

De verschillende scenario’s overschrijven de resultaten van de originele tabel, dus het is verstandig om eerst een scenario aan te maken dat de huidige cijfers weergeeft. Ga naar het tabblad Gegevens (Data) en klik bij Voorspellingen (Forecast) op Wat-als-analyse (What-If Analysis). Kies hier voor Scenariobeheer (Scenario Manager). Klik op Toevoegen (Add) om een nieuw scenario toe te voegen. Deze noemt u iets als ‘huidige cijfers’ onder Scenarionaam (Scenario name). Typ onder Veranderende cellen (Changing cells) het celbereik van de input. In dit geval de verkoopcijfers van 2021, G2 t/m G12. Klik op OK. Verander vervolgens niets aan de input en klik op OK.

Voor de extrapolatie van de cijfers voegt u een hulpkolom toe waarin G2 t/m G12 worden gedeeld door 0,45 (=G2/0,45 en deze cel vervolgens naar beneden trekken). Deze cijfers, afgerond naar boven omdat het om hele producten gaat, voert u in bij Scenariobeheer. Ga naar Gegevens > Wat-als-analyse > Scenariobeheer (Data > What-If Analysis > Scenario Manager) en klik op Toevoegen (Add). Bij Scenarionaam voert u ‘Verwachte omzet’ in en tikt u bij Veranderende cellen G2:G12. Deze keer verandert u de input zoals uit de hulpkolom is gebleken. Dat levert het volgende resultaat op:

Nog twee scenario’s

Klik op OK om het scenario toe te voegen. Nu kunt u al naar het nieuwe omzetscenario kijken, maar laten we nu uitgaan van een scenario waarbij de winkel in de loop van 2021 nog eens twee maanden gesloten wordt. Dat zou ruwweg betekenen dat 62 kleine potlodensets nu zo’n 64 procent van de verwachte verkoop zal zijn. 62 / 0,64 = 96,9. Een hulpkolom kan ook hier handig zijn waarin G2 t/m G12 worden gedeeld door 0,64. Ook deze cijfers voert u in bij Scenariobeheer zoals in de vorige stap. Noem dit scenario bijvoorbeeld ‘Verkoop bij coronamaatregelen (2 maanden)’.

Voor drie maanden herhalen we deze stappen, maar nu met een sluiting van drie maanden. Dat maakt de verkoop van 62 potloden grofweg 73 procent van de totale verwachte verkoop. Met een hulpkolom waarin G2 t/m G12 worden gedeeld door 0,73, is een nieuw scenario in te vullen. Noem deze ‘Verkoop bij coronamaatregelen (3 maanden)’.

>> Lees meer: Omzetvergelijking huidig jaar met vorig jaar

Het is ook mogelijk om scenario’s te ontwikkelen op basis van inputs die door de feature Oplosser (Solver) worden gezocht. In dit artikel over Oplosser als geavanceerde versie van Doelzoeken leest u meer over het aanpassen van waardes op basis van deze functie. Deze voert u vervolgens bij Scenariobeheer in. Denk aan een optimistisch verhaal waarbij er juist een grotere omzet wordt gedraaid dan in 2019. Dit bedrag, bijvoorbeeld 20k, gebruikt u dan als Waarde van: (Value Of:).

Overzichtsrapport

Door de inputs (veranderende cellen) in te voeren, verandert niet alleen de tabel wanneer er op Weergeven (Show) wordt geklikt bij een specifiek scenario, maar kan Excel ook een totaaloverzicht genereren met de scenario’s naaste elkaar. Klik daarvoor op Samenvatting (Summary) en de relevante resultaatcel(len). In dit geval gaat het om de totale omzet (H14). Excel genereert dan het rapport met alle gegevens met in de eerste plaats de input (de veranderde cellen) en in de tweede plaats de resultanten (de cellen die worden veranderd door de nieuwe input).

In deze rapportweergave wordt overigens ook standaard een kolom met Huidige waardes (Current Values) toegevoegd. Het scenario met de originele cijfers is voor deze feature overbodig. Dit scenario met oorspronkelijke gegevens is handig om te hebben, om met Weergeven (Show) in het venster Scenariobeheer eenvoudig terug te springen naar de originele data. Maar voor dit rapport kunt u de eerste kolom wissen. Het rapport ziet er dan als volgt uit:

>> Rapportages fijnmazig zelf beheersen? Gebruik daarvoor Power Query om rapportages te bouwen.

Het rapport kan ook worden gegenereerd als draaitabel, wat handig is als u visualisaties wilt maken van deze scenario’s.

>> Aan de slag met visualisaties op basis van voorspellende scenario’s? Lees ook: Dynamische lijngrafiek actuele saldi met forecast als stippellijn.

Volg CMWeb op LinkedIn!