overzicht |

Breng leegstand in kaart met interactieve grafiek in Excel

Je bent net begonnen als adviseur ruimtelijke ordening. De eerste opdracht is het in kaart brengen van de leegstand bij het lokale winkelcentrum. Na weken wachten heb je dan eindelijk een databestand in Excel. Net op tijd, want over twee dagen mag je een eerste update geven aan de stakeholders. Het liefst wil je iets interactiefs waarbij de betrokken partijen zelf gemakkelijk doorsnedes kunnen maken.

In deze blog laat ik je zien hoe je met behulp van een draaitabel en slicers, snel en simpel een interactief overzicht en een grafiek maakt.

Dit is het databestand wat je hebt gekregen.

En dit is het interactief overzicht waar we naar toewerken, met aan de linkerkant de slicers waarmee makkelijk gefilterd kan worden.

Databewerking

In het databestand staat in kolom G het bouwjaar. Het is niet handig om alle bouwjaren als filteroptie te geven in de slicer. De lijst met mogelijkheden zou superlang worden. Daarom voegen we een kolom toe en maak je er categorieën van met behulp van de volgende formule:

=IF(ISBLANK(G2);”Onbekend”;IF(G2<1945;”<1945″;IF(G2<1970;”1945-1970″;IF(G2<1990;”1970-1990″;IF(G2<2010;”1990-2010″;”>2010″)))))

Verder ziet het bestand er goed uit.

Visualisatie

Met behulp van een draaitabel gaan we het interactief maken. De slicers kan je namelijk alleen gebruiken in combinatie met een draaitabel. Allereerst selecteren we de data in het databestand, kolom A t/m L. Dan via de menuknop invoegen, kies je voor draaitabel.

Klik ok op het volgende venster om de draaitabel op een nieuw werkblad te zetten.

Je krijgt daarna het volgende te zien op je werkblad.

De velden uit het datawerkblad kan je nu in kolommen en rijen weergeven. Ook kan je, indien nodig, waarden totaliseren door velden naar het venster rechtsonder te slepen (values). We slepen Onderneming_branche in de rij en tellen Pand_straatnaam in de values.

Als Excel standaard de waarden totaliseert (sum), dubbelklik dan op het veld in de values, en kies tellen (count).

Nu zien we in één oogopslag hoeveel leegstand we hebben. We missen alleen nog een mooie visualisatie om dit in verhouding te zien. Door met de cursor in de draaitabel te staan, kies je voor invoegen draaigrafiek.

De volgende grafiek verschijnt dan.

Dit geeft al mooi weer hoe de verhouding zijn, maar het kan nog beter. Met rechtermuisknop in de grafiek, kies ik voor een circeldiagram. Bovenaan kies ik eerst voor style 3 en daarna voor style 6.

Door deze volgorde te kiezen heb ik gelijk ook percentages in de donut staan.

Ik wil ook graag de waarden uit de draaitabel er in terugzien. Door te dubbelklikken op een van de percentages in de grafiek verschijnt er rechts een venster met opties. Ik kies ook voor value en de scheid de waarden door een nieuwe regel.

Verder pas ik de titel aan door er op te dubbelklikken.

Detailinformatie

In de volgende stap maken we een overzicht met meer details. Je kopieert en plakt de draaitabel naar cel N4 voor meer ruimte in het Excel werkblad. De grafiek sleep je daaronder.

De ene draaitabel begint nu op D4 en de andere in N4. In de draaitabel in D4, selecteer je de volgende velden voor in de rijen:
Eigenaar_Soort, Onderneming_Branch, Pand_Straatnaam, Pand_Huisnummer, Pand_Huisnummer_toevoeging, Pand_Bouwjaar, Locatus_Branch en Contactgegevens pandeigenaar.

Ook kunnen de headers wat klantvriendelijker gemaakt worden in regel drie.

Interactieve filters

Als laatste stap gaan we de interactieve filters toevoegen: de slicers. Filteren op straatnaam, bouwjaarcategorie, type eigenaar en op Onderneming branche, geven het meeste inzicht.

De slicers zet je netjes onder elkaar in de eerste drie kolommen. Het aanpassen van de naam kan via Slicer Settings (rechtermuisknop). Vink dan gelijk ‘items verbergen zonder data’ aan. Dit zorgt ervoor dat je geen ‘lege’ knoppen krijgt.

Als je in de draaitabel staat en vervolgens klik op Filter Connections, dan zie je welke slicers verbonden zijn met de draaitabel. Als het goed is met alle vier.

Om de slicers ook te verbinden met de draaitabel in N4 met zijn bijbehorende grafiek, gaan we in de de draaitabel (N4) staan en klikken via Filter Connections ook bovenstaande vier slicers aan.

Het resultaat

Als je nu op ‘leegstand’ klikt in de bovenste slicer, dan wordt zowel de detailinformatie gefilterd als de grafiek. Ook zie je dat de keuzes in de andere slicers meebewegen met je eerst keuze. Zo kan je bijvoorbeeld in de tweede slicer alleen maar verder kiezen uit de Institutionele en Particuliere belegger. Simpelweg omdat dit de twee type eigenaren zijn met leegstand.

In dit voorbeeld heb ik voor een prettige weergave de grafiek over de draaitabel in N4 heen gesleept.In het detailoverzicht heb je verder direct de belangrijkste informatie beschikbaar en kan je gelijk in actie komen door de betreffende pandeigenaar te benaderen voor meer informatie.

De visualisatie voegt in deze filtering niet zoveel toe, maar wel als je bijvoorbeeld alleen filtert op de bouwjaren 1990-2010 in de Ferdinand Bolstraat. Dan zie je gelijk dat de selectie slechts twee branche categorieën heeft.

Je kan in dezelfde slicer ook meerdere waarden selecteren. Dat doe je door eerst de multi-select aan te zetten met de groene vinkjes. Filtering weer ongedaan maken, doe je met het kruisje bij de filter helemaal rechts van de slicer.

Nu ben je in staat om zelf je volgende draaitabel met interactieve grafiek te maken.

Succes!

Meer inzicht in je maandelijkse marges met Excel

Met je draadloze headset ben jij als oplosser van problemen de hele dag in gesprek met mensen. Detachering of werving & selectie, jij zorgt voor de juiste mensen op de juiste plaats. ’s Avonds zit je op je gemak nog even in Excel de administratie te doen. Doordat je alles strak elke dag bijwerkt, heb je een goed overzicht in de marges per consultant, maar wat zijn eigenlijk je marges voor de komende maanden?

Met behulp van het onderstaande voorbeeld laat ik je zien hoe je in een drietal eenvoudige stappen een prognose kan maken van je marges voor de komende maanden.

De basis op orde

De linkerkant, tot en met kolom K, bestaat uit basisgegevens.

Vanaf kolom L zijn de meeste kolommen berekend om uiteindelijk de gemiddelde marge per maand te berekenen in kolom R.

Het uurloon wordt berekend door het salaris te delen door het (maximaal) gemiddeld aantal werkuren per maand van 173,33 uur. Het tarief wordt berekend door het uurloon te vermenigvuldigen met de opslagfactor in kolom I.

In kolom Q hanteer ik een vast gemiddeld aantal werkuren van 150 in de maand om de gemiddelde marge per maand te bereken in kolom R (kolom P x kolom Q). Dit getal kan je natuurlijk zelf naar eigen inzicht aanpassen.

De formule in kolom N telt het aantal dagen, op basis van kolom K, bij de Datum in dienst om tot de Datum uit dienst te komen:
=IF((J11+(K11/8)*(7/5)/(Q11/173))=0;””;(J11+(K11/8)*(7/5)/(Q11/173)))
Eerst gaan we naar dagen door K11 door 8 uur te delen en dat vermenigvuldigen we met de “werkdagen factor” (Q11/173).

De kostprijs wordt berekend door het uurloon te vermenigvuldigen met een overheadsfactor van 1,8. De marge in kolom P is dan het verschil tussen het tarief in kolom M en de kostprijs in kolom O.

Voor het berekenen van de marge voor de werving en selectie activiteiten, wordt de wervingsfee in kolom H vermenigvuldigd met de FTE factor in kolom C. Bij projecten is dat kolom P x kolom C.

Nu we op een eenduidige manier de marges hebben bepaald per consultant, bepalen we in de volgende stap de marges per maand.

Marges per maand per consultant

Voor elke regel gaan we per maand bepalen of daar marge geteld moet worden.

Dat doen we met onderstaande formule voor bijvoorbeeld de maand februari (U11):
=IFERROR(IF(AND($J11<V$10;$N11>U$10);MIN(($N11-U$10)/(U$10-T$10)*$R11;(V$10-$J11)/(U$10-T$10)*$R11;$R11);0);0)

Valt de marge binnen de betreffende maand (is datum in dienst kleiner dan de volgende maand en is de datum uit dienst groter dan de betreffende maand), dan wordt er daarna bepaald voor hoeveel dagen de marge geteld moet worden. Dit doet de formule door te kijken of de gehele maand marge moet worden meegenomen, een deel van de marge op basis van de datum in dienst, of een deel van de marge op basis van de datum uit dienst.

Om het deel van de marge te bepalen, kijkt de formule wat de kleinste waarde oplevert:
=IFERROR(IF(AND($J11<V$10;$N11>U$10);MIN(($N11-U$10)/(U$10-T$10)*$R11;(V$10-$J11)/(U$10-T$10)*$R11;$R11);0);0)

1. Is het verschil tussen de de eerste van de betreffende maand (1 feb) en de datum uit dienst, vermenigvuldigd met het gemiddelde marge van die maand het kleinst? ($N11-U$10)/(U$10-T$10)*$R11

2. Is het verschil tussen de eerste van de opvolgende maand (1 mrt) en de datum in dienst, gedeel door totaal aantal dagen in de in de maand, vermenigvuldigd met het gemiddelde marge van die maand het kleinst? (V$10-$J11)/(U$10-T$10)*$R11

3. Of is het gehele gemiddelde marge bedrag van die maand het kleinst? $R11

Door te zoeken naar de kleinste waarde kom je ‘als vanzelf’ uit of je het deel van de maand vanaf het begin van het contract moet berekenen of juist dat deel van de maand tot en met het einde contract.

Zodra het verschil aan dagen in de eerste twee gevallen boven het totaal aantal dagen in de maand uitkomt, zal dit zorgen voor een hoger berekend gemiddeld marge bedrag en vallen die daardoor af.

Voor U11 kom je dan uit op de volgende waarden:
1. ($N11-U$10)/(U$10-T$10) = 350,3/31)
($N11-U$10)/(U$10-T$10)*$R11 = (350,3/31 * 2582)

2. (V$10-$J11)/(U$10-T$10) = 41/31)
(V$10-$J11)/(U$10-T$10)*$R11 = (41/31 * 2582).

3. $R11 = 2582

Zoekend naar de kleinste waarde vallen de eerste twee opties af in de vergelijking voor U11.

Marges per maand getotaliseerd

Als laatste stap willen we alle categorieën, Consultants in dienst, Werving en Selectie, ZZP’ers en Projecten, per maand getotaliseerd hebben.

Alles bij elkaar optellen is een mogelijkheid, maar getotaliseerd per categorie biedt natuurlijk het meeste inzicht. Door middel van een SUMIFS formule, kunnen we verschillende criteria opgeven voor het optellen.

Door aan elke categorie een ‘verborgen’ kenmerk mee te geven in kolom S, kan je datzelfde kenmerk weer gebruiken in de formule. Bij de consultants In Dienst zet je dan in witte letters in kolom S ‘detachering’. Op dezelfde manier ‘ws’ voor werving en selectie, ‘zzp’ voor de ZZP categorie en ‘project’ voor Projecten.

De formule voor marge detachering (T45) is: =SUMIFS(T$11:T$42;$S$11:$S$42;”detachering”)
Ik gebruik standaard een SUMIFS formule in plaats van een SUMIF, ook als ik zoals nu maar één criterium gebruik. Mocht het in de toekomst nodig zijn om een criterium toe te voegen, dan staat de formule al in het juiste volgorde format (die afwijkt van de SUMIF). Voor de andere categorieën maak je op dezelfde manier ook de SUMIFS.

Nu zie je precies wat je per categorie aan marges kan verwachten de komende maanden. En op basis van dit overzicht kan je weer nieuwe plannen maken voor de toekomst.

Succes!