Als succesvolle horeca ondernemer maak jij zulke lekkere broodjes, dat mensen helemaal van de andere kant van de stad naar jouw zaak komen. Het gaat goed, maar toch heb je het gevoel dat je af en toe wat omzet mist. Je weet alleen niet waar en wanneer.
In drie eenvoudige stappen laat ik je zien hoe je met Excel inzicht krijgt in wat je aan broodjes mist en hoe dat effect heeft op je winst.
Stap 1: Maak elke dag een inventarisatie
Maak in Excel een invoertab waarin je dit gemakkelijk kan invullen zoals in onderstaand voorbeeld. Vanaf B8 tot en met G28 voer je zelf handmatig in elke dag. In kolommen C, D en E de verkopen en in kolom G wat er aan het eind van de dag nog over is.
Met behulp van directe verwijzing halen we de verkoop- en inkoopprijzen in kolom H en I uit tabblad Stuurgegevens erbij.
In tabblad Stuurgegevens staan ook de naam van de medewerkers, die we dan op het eerste tabblad met een dropdown menu kunnen selecteren.
Door eenmalig de prijzen in een apart tabblad te vullen, bespaar je tijd en verminder de kans op typefouten.
Om de kans op foutieve invoer te verminderen, kan je een aantal datumcontroles uitvoeren. Met als uitgangspunt dat elke medewerker maximaal één inventarisatie uitvoert per dag, zoek je in I6 de laatst ingevoerde datum van de betreffende medewerker op in het tabblad DATA. Dit is dan de formule:
=IF(DATA!E4<>””;MAX(IF(DATA!$B$4:$B$5001=$C$3;DATA!$E$4:$E$5001));””)
In E4 en E5 check je op chronologie.
E4: =IF(I6=””;””;IF(C4<=$I$6;”Let op: start ligt op of voor laatst gevonden datum”;””))
E5: =IF(C5<C4;”Einddatum ligt voor startdatum”;””)
Het unieke serienummer in C6 hebben we nodig om eventueel vorig saldo te bepalen. Broodjes die nog over zijn van de vorige dag en goed van kwaliteit zijn om te verkopen. Met de formule in E6 bepalen we het hoogste serienummer van de betreffende medewerker: =MAX(IF(DATA!$B$4:$B$5001=$C$3;DATA!$C$4:$C$5001))
In C6 tellen we daar simpelweg eentje bij op om het uniek te maken.
Liever uitbesteden aan Expert Excel?
Met het team Excel specialisten sta ik voor je klaar!
Stap 2: Verzamel alle gegevens:
Met de macroknop ‘Sla gegevens op’, worden de gegevens naar het tabblad DATA gekopieerd.
Gelijk verschijnt een datum in I6. Het serie nummer in C6 is verhoogd naar 2 en in E4 staat een waarschuwing.
Op het tabblad DATA zijn de kolommen B tot en met M gevuld via de macro.
Om de blog overzichtelijk te houden, zal ik in de volgende blog het Macro gedeelte uitleggen. Voor nu kan je handmatig de data uit de invoer tab kopiëren om hetzelfde resultaat te bereiken.
Tot en met kolom M staat de handmatig invoer en vanaf kolom O de berekeningen. In kolom O tel je de verkopen over kassa 1 tot en met 3.
In kolom P check je of er nog iets over is van vorige keer. Hier neem je het huidige serienummer en haal je er eentje vanaf om het saldo (kolom S) te bepalen van het betreffende product (en opgenomen door de dezelfde medewerker):
=IF(B4=””;””;IFERROR(INDEX($S$4:$S$5000;MATCH($B4&$C4-1&$F4;$B$4:$B$5001&$C$4:$C$5001&$F$4:$F$5001;0));0))
In kolom Q bereken je wat je verwacht in de ideale situatie zonder derving. Je start met de verkopen als min getal en telt daar de inkopen en het saldo van vorige keer bij op.
In kolom R maak je de vergelijking tussen wat je geïnventariseerd hebt en wat je verwacht.
In kolom S staat een directe verwijzing naar kolom K ter informatie als we in de toekomst ‘Saldo vorige’ willen bepalen.
De omzet in kolom T bereken je door de verkoopprijs in kolom L te vermenigvuldigen met het aantal verkopen in kolom O. De inkoop bereken je door kolom J te vermenigvuldigen met kolom M. Voor derving vermenigvuldig je de inkoopprijs in kolom M met kolom R. Voor de winstbepaling haal je in kolom W de inkoop (U) en derving (V) van de omzet (T) af.
Stap 3: Maak een draaitabel
Met één ingevoerde dag is de derving nog redelijk overzichtelijk maar het liefst wil je dag op dag alles getotaliseerd zien. Een draaitabel biedt de perfecte oplossing.
Selecteer vanaf regel 3 kolom O tot en met W. Klik via Invoegen, op draaitabel.
Klik op het volgende venster op OK, om de draaitabel op een nieuw tabblad te creëren.
Sleep de velden Naam en Startdatum naar ‘rijen’ in het venster aan de rechterkant. Vervolgens sleep je de velden Omzet, Inkoop, Derving en Winst naar ‘waarden’.
In de omschrijving van de velden bij ‘Waarden’ staat hoe er nu getotaliseerd wordt. Dit moet op Som gedaan worden. Met de rechtermuisknop kan je dit aanpassen.
Dat resulteert in het volgende overzicht.
Als laatste stap kan je op het linker pijltje klikken en alleen ‘Dirk’ selecteren in de filter.
Wanneer je morgen een nieuwe dag invoert, ververs je de draaitabel met de laatste data door met rechtermuisknop in de draaitabel te klikken en op verversen te klikken. Nu heb je per dag inzicht in je belangrijkste cijfers.
Met een draaitabel kan je natuurlijk nog veel meer. Wil je na een aantal dagen weten welke producten de meeste derving hebben, dan haal je het veld ‘Startdatum’ uit de rijen en voeg je het veld ‘Product’ toe. Uit de waarden sleep je verder nog Omzet, Inkoop en Winst weer terug naar boven zodat alleen Derving overblijft.
Nu weet je precies waar op welke broodjes je actie kan ondernemen om derving te verminderen.
Succes!