VERDIEPINGSARTIKEL

Prognoses maken met Excelmodellen

Wilt u een kijkje nemen in de glazen bol, dan kunt u daarvoor Excel gebruiken en bijvoorbeeld een ‘wat als-analyse’ maken. Met deze prognoses kunt u diverse scenario’s simuleren voor de verkoop van uw producten en diensten en zien wat daarvan het effect is voor de omzetverwachting van uw organisatie in de komende jaren.


12 maart 2020 8 minuten Door redactie

Dit verdiepingsartikel wordt u aangeboden door Rendement Online


Excelmodellen zijn eigenlijk niks meer en niks minder dan tabellen met variabelen, die op basis van uw input meerdere mogelijke uitkomsten weergeven. Nadat u een aantal gegevens heeft ingevuld, kunt u in uw model zien wat deze waarden voor effect hebben op het eindresultaat.

Dit effect kunt u in het Excelmodel bereiken door uw informatie te koppelen aan zogenoemde ‘door de markt bepaalde cijfers’. Een voorbeeld van zo’n cijfer zou bijvoorbeeld het groeipercentage van de economie kunnen zijn, of de inflatieverwachting. Als u Excelmodellen toepast, bent u dus eigenlijk prognoses aan het maken. Excelmodellen worden daarom ook wel ‘wat als-analyses’ genoemd.

Twee soorten analyses

Er zijn twee soorten analyses mogelijk:

  • een tabel met één variabele en meerdere formules;
  • een tabel met twee variabelen en één formule.

Een ‘wat als-analyse’ is dus bedoeld om zinvolle analyses te kunnen maken van de manieren waarop relevante cijfers zich in de toekomst zouden kunnen gaan ontwikkelen. Met de instructies uit deze whitepaper kunt u een voorbeeldmodel maken.

Nadat u dit voorbeeld onder de knie heeft, kunt u het model invullen met uw werkelijke cijfers. Als u bijvoorbeeld een indruk wilt krijgen van de mogelijke omzet over het boekjaar 2020, kunt u het model dat het antwoord moet geven op uw vraag in een aantal stappen in elkaar zetten. Zie tabel hieronder.

Knutselen

Als u niet zelf wilt knutselen en meteen aan de slag wilt gaan met het Excelmodel, kunt u ervoor kiezen om de rekentool ‘Omzet voorspellen met Excel’ te downloaden. U kunt dan meteen de relevante cijfers invullen om een beeld te krijgen van de mogelijke omzet.

Natuurlijk kunt u de omzet niet 100% voorspellen, maar een voorzichtige indicatie biedt ook al veel informatie. Als u besluit het model zelf te maken, opent u om te beginnen een leeg Excelwerkblad. In cel A1 schrijft u op ‘Omzet 2019’.

In cel B1 plaatst u het bedrag dat uw team of een andere afdeling aan omzet heeft behaald, in dit voorbeeld € 1.000.000. In cel A2 en B2 verwerkt u de kosten en in cel A3 en B3 verwerkt u de winst. In cel A4 en B4 verwerkt u het groeipercentage dat u verwacht te behalen, bijvoorbeeld -2%.

In dit voorbeeld is er gekozen voor een negatief groeipercentage, waarmee u te maken zou kunnen krijgen als de concurrentie in uw markt erg toeneemt door de toetreding van nieuwe aanbieders. In cel B3 staat de uitkomst van de eenvoudige formule =B1-B2. Om in cel B7 de uitkomst van de prognose afgebeeld te krijgen, moet u in de formulebalk een formule instellen.

Meerdere jaren voorspellen

Mocht u voor meerdere jaren de omzet willen voorspellen, dan moet u extra aandacht besteden aan de verwijzingen die u in uw formule plaatst. Een verwijzing is namelijk standaard relatief. Dit betekent dat de verwijzing bij het kopiëren naar een andere locatie aangepast wordt aan de nieuwe situatie.

Tabel met variabelen laat meerdere groeipercentages zien

U kunt dit probleem oplossen door de verwijzingen naar cellen absoluut te maken. U doet dit door in de formulebalk dollartekens in te typen voor de kolom- en rijaanduiding van de cel verwijzing. Deze ingewikkelde handeling kunt u eenvoudiger maken en uitvoeren door op de naam van de betreffende cel in de formulebalk te klikken en daarna de functietoets F4 in te toetsen. De dollartekens verschijnen nu automatisch in de formulebalk.

Om uw model nog gemakkelijker te kunnen ontwerpen, moet u de cellen waarnaar wordt verwezen voorzien van namen. Als u één cel een naam geeft, wordt de verwijzing naar deze cel meteen absoluut. U geeft een cel een naam door bijvoorbeeld de celnaam B1 – in het vakje naast de functietoets fx – te vervangen door ‘Omzet’. De celnaam B2 vervangt u door ‘Kosten’ en de celnaam B3 vervangt u door ‘Winst’. De celnaam B4 vervangt u door ‘Groei’.

Daarna kunt u uw formules instellen. Dit doet u door op cel B7 te klikken en vervolgens via de functietoets fx de volgende formule in te voeren =(1+Groei)*Omzet. In cel B7 verschijnt nu het bedrag € 980.000. Dit zal waarschijnlijk de omzet zijn voor het jaar 2020. Als u voor meerdere jaren omzet wilt voorspellen, typt u in cel C7 de formule =(1+Groei)*B7. De verwijzing naar cel B7 maakt u niet absoluut, zodat u de formule kunt doortrekken naar cel D7 en E7.

Meerdere groeipercentages

U heeft nu met een soortgelijke formule berekend wat in de daaropvolgende jaren de omzet zal zijn bij een groeipercentage van -2%. Als u in cel B7 het groeipercentage wijzigt, kunt u kijken welke gevolgen een ander percentage voor de groei heeft voor de omzetprognose. Een gegevenstabel met variabelen is echter handiger, want u kunt dan de resultaten laten zien van meerdere groeipercentages.

Dit model kunt u in elkaar zetten door uw model uit te breiden met tabel 2. In de cellen A7 t/m A11 staan de te testen waarden: groeipercentages van -2% tot +2%. Dit zijn voorbeelden van mogelijke groeipercentages voor 2020 en de daaropvolgende jaren.

Als u een omzetprognose maakt voor andere groeipercentages, is cel B4 (met de naam Groei) de variabele die uit de formule moet worden gehaald. Excel beschikt over een functie die in één keer de tabel invult en die u kunt toepassen nadat u cel A7 t/m E11 heeft geselecteerd.

Vervolgens klikt u in het lint op Gegevens, Wat-als-analyse en Gegevenstabel. Er verschijnt nu een instellingsvenster. Omdat de testwaarden in een kolom staan, klikt u in het vak achter het vakje Kolom invoercel. Hier typt u de naam Groei. U sluit af met OK. U krijgt nu een tabel met omzetprognoses tot en met 2023.