Op basis van historische gegevens kunt u in Excel een voorspelling maken van de toekomstige cijfers. Daartoe maakt u gebruik van de ingebouwde faciliteit waarmee u interactief een schatting kunt maken.  Met behulp van een Excel-macro kunt u de gebruikersvriendelijkheid verder verhogen.

Het uitgangspunt is een tabel bestaande uit twee kolommen, te weten datum en de grootheid u die u wenst te bekijken, bijvoorbeeld omzet.

De tabel dient u op te maken als een echte Excel-tabel via het lint: Start > Stijlen > Opmaken als tabel. Via Tabelontwerp > Eigenschappen > Tabelnaam: DataOmzet gevolgd door Enter.

Werken met het voorspellingblad

Op basis van de ingevoerde tabel kunt een afzonderlijk voorspellingblad maken met bijbehorende grafiek. Plaats de cursor in de tabel en uit het lint kiest u: Gegevens > Voorspelling > Voorspellingblad.

Rechts bovenaan kunt u een lijngrafiek of een staafgrafiek kiezen.  Door het klikken op Opties ziet u de volgende mogelijkheden.

Twee belangrijke opties die u kunt instellen zijn:

• Einddatum van de prognose is. De standaardinstelling is 6 maanden gerekend vanaf de laatste datuminput.
• Standaard staat deze op 95%.

Nadat u op de knop Maken klikt, wordt automatisch een afzonderlijk blad aangemaakt met de volgende indeling:

Het nieuwe blad kunt u herbenoemen in Forecast. Als u een nieuw scenario wenst te maken, dan dient u weer een selectie te maken en het oude Forecast blad te verwijderen. Het zou handig zijn als de gebruiker rechts van de inputtabel de variabelen (einddatum en betrouwbaarheid) kan wijzigen en met een druk op de knop de oude Forecast verwijderd wordt en de forecastgrafiek op het inputblad wordt afgebeeld.

Variabelen

U maakt de volgende variabelen aan:

U maakt bereiknamen aan door het gebied te markeren D1 tot en met E7 en uit het Lint kiest u: Formules > Gedefinieerde namen > Maken o.b.v. selectie > Linkerkolom > OK.

U voert de volgende formules in:

• MaxDatum =MAX(DataOmzet[datum])
• EindDatum=DATUM(JAAR(MaxDatum);MAAND(MaxDatum)+Maanden;1)

U kunt het aantal maanden en de betrouwbaarheid zelf invoeren.

Forecast Macro

De forecast macro ziet er als volgt uit:

U kunt de macro koppelen aan een knop. Teken daartoe een knop via Ontwikkelaars > Besturingselementen > Formulierbesturingselementen > Knop. U kunt de knop uitlijnen en voorzien van de tekst: Forecast. Klik dan met de rechtermuisknop op de knop en kies Macro toewijzen en koppel deze aan de macro.

Het datablad ziet er uiteindelijk er als volgt uit:

Auteur: Tony De Jonker

Dit artikel is verschenen in cm: 2022, afl. 1.

Hier leest u meer Excel tips en trucs door Tony De Jonker

Volg CMWeb op LinkedIn!