Consumpties turven voor de voetbalploeg
- 23/12/2019
 | Voorbeeldbestanden bij dit artikel: |
Opmerking
Er zit celbeveiliging op de meeste cellen in het bestand. Je kan de beveiliging van het werkblad halen, er is geen paswoord aanwezig.
Ik ben als speler en bestuurslid aangesloten bij een voetbalploeg die op zondag zijn wedstrijden afwerkt, maar dat wist de regelmatige bezoeker reeds. Sinds dit voetbalseizoen zorgen wij voor de kantine waar spelers en supporters zich tegoed kunnen doen aan drank en versnaperingen. Dit levert de club inkomsten op. Echter, aangezien de voetbalpleinen gehuurd worden, zijn de inkomsten bestemd voor de verhurende partij. Een gedeelte (25% van de behaalde omzet) mag als tegenprestatie gehouden worden. Daarboven wordt er een vaste prijs per wedstrijd bepaald.
Naast deze bedragen worden er nog andere onkosten gegenereerd, zoals een bijdrage voor de scheidsrechter met dienst en een kleine bijdrage voor het gebruik van flessenwater.
Een en ander leidt tot, u raadt het al, het bijhouden van een kassa. De omzet moet bepaald kunnen worden per wedstrijd. Er zijn zeker 2 methoden mogelijk: je kan de beginvoorraad bepalen, de eindvoorraad, en aldus het verschil uitrekenen, per item (drank, versnapering, ...). De gebruikte aantallen vermenigvuldig je met de eenheidsprijzen waardoor er omzet berekend wordt. Dit is omslachtig.
Het is wellicht interessanter om te turven: met streepjes bijhouden wat er verbruikt wordt. Aantal streepjes maal (verkoop)prijs, optellen en klaar. Maar dan komt het: hoe ga je turven in Excel ? Kunnen we ook in Excel streepjes zetten ? Jazeker !
Zeer mooi Excelbestand
Dit heb ik in Excel gezet. Zonder overdrijven, dit Excel bestand bevat ook voor gemiddelde Excel gebruikers nog veel zaken die nieuw gaan zijn. In het xlsx bestand zitten geen macro's al gaan we er wel 1 bespreken op het einde van het artikel. Die vind je terug in de xlsm variant die je ook bovenaan kan downloaden.
De layout van het 3 pagina's tellende bestand is als volgt. Je ziet o.a. van links naar rechts:
- het turven van de consumpties voor de eigen ploegleden
- het turven van de consumpties voor de tegenstander
- de financiële afwikkeling van de namiddag voetbal, zijnde opbrengsten en kosten, ook de rekening bestemd voor de eigenaar van de velden.
Excel functionaliteiten die je kan aantreffen in het bestand, zijn:
- het gebruik van benoemde bereiken en constanten, wat leidt tot een eenvoudig onderhoud van de toepassing
- het gebruik van stijlen, wat leidt tot een eenvoudig onderhoud van de toepassing
- het tegelijkertijd hebben van een inputblad (geprint op papier voor offline gebruik) als digitaal ingeven van de gegevens
- het turven van consumpties in Excel, wat ik doe via Voorwaardelijke opmaak
- het beveiligen van het blad om accidenteel en onvrijwillig overschrijven van formules tegen te gaan
- een bestand dat netjes op 3 pagina's geprint kan worden
- formules om uit te rekenen wat omzet, kosten, financieel resultaat zijn. Wie ontvangt wat ? Hoe evolueerde de kassa ?
- functies om te input te valideren / te kijken of er reeds input werd gedaan (indien niet, andere resultaten worden getoond)
- enz.
Voor de constanten, die je zelf ook zal willen aanpassen, zie het blad 'Help'.
Dit bestand wordt nu bij elke thuiswedstrijd van onze ploeg gebruikt, en geeft een voldaan gevoel dat alles goed afgehandeld wordt. Noodzakelijke voorwaarde is uiteraard dat de consumpties juist geturfd worden. De juistheid van de resultaten staat of valt hiermee.
Uitleg voor hoe de invoer gebeurt
Je ziet 2 keer dezelfde diagrammen: links nemen wij voor onze eigen ploeg, rechts nemen wij voor de bezoekende ploeg. Per ploeg hebben we vervolgens deze situatie, de nummers 1-2a-2b-3 werden aangeduid op de afbeelding.
- "Op de poef": Eerst kunnen we per prijsniveau bijhouden hoeveel er geconsumeerd wordt. Dit is het turven. Het gaat hier over de consumpties die nog niet rechtstreeks betaald worden. Er zijn 2 opties:
- Ofwel geef je in de groene cellen, per prijsniveau, in hoeveel consumpties er waren.
- Ofwel geef je in de individuele cellen in hoeveel er waren. Bvb. 5 / 5 / 2 in de eerste 3 vakjes, geeft dan 12 consumpties. Elk geheel getal tussen 0 en 99 is toegelaten.
- "Afgerekende consumpties / afgerekende betalingen": Wanneer een speler vertrekt en nog op de poef heeft staan, geeft hij / zij een bedrag daarvoor, dat dan in de kassa terecht komt:
- a) Ofwel weet men exact voor welke consumpties dit was, en gaat men turven (dit is de vaakst voorkomende optie, men kan het dan ook zo noteren)
- b) Ofwel wenst men dit niet te noteren als individuele consumpties, en geldt een totaalbedrag. Dan zetten we het betaalde bedrag in 1 vakje. Bvb. 9,50 €
- "Direct betaald": Naast het turven van consumpties komt het ook voor dat spelers / supporters contant en direct betalen zonder op de poef te schrijven. Daarvoor dient het laatste vak.
Dit systeem geldt voor zowel de eigen ploeg als de bezoekende ploeg. Onder de tabellen verschijnen vervolgens de totalen van de verzette bedragen. Op blad 3 verschijnt tevens meteen de afrekening: omzet kan opgeteld worden (mits inachtname van het type - geturfde consumpties of direct betaald, aangezien geturfd nog niet in de kassa zit en direct betaald wel).
Als men op blad 3 dan ook de kosten voor scheidsrechter, veld en water voorziet (of zet die kosten simpelweg op 0), dan rolt er zonder veel moeite de financiële afwikkeling uit. Weten we direct wat de rekening is voor de eigenaar van de voetbalvelden, weten we het kassaresultaat, enz. Wat dan ten slotte nog gebeurt is de betaling aan de eigenaar, via overschrijving of cash, met paraaf als bewijs.
Stijlen
Bekijk zeker de stijlen die in het bestand vervat zitten. Door stijlen te hanteren kan je het bestand veel meer onderhoudbaar en uitbreidbaar maken, aangezien stijlen voor opmaak en celbeveiliging centraal (eenmalig) beheerd worden. Geen nood om in tig cellen de opmaak in te stellen. Wat als er iets verandert ? Dan heb je veel meer extra werk.
Turven
Naast de stijlen zit de voorwaardelijke opmaak ook goed in mekaar:
- 0 consumpties wordt getoond als een lege cel
- 1 consumptie wordt getoond als I
- 2 consumpties wordt getoond als II
- 3 consumpties wordt getoond als III
- 4 consumpties wordt getoond als IIII
- 5 consumpties wordt getoond als IIIII maar daar gebruik ik bijkomend de StrikeThrough (doorhalen) om 5 te tonen als
IIIII - Getallen groter dan 5 en kleiner dan 99 worden zonder bijkomende opmaak getoond
- Negatieve getallen of getallen groter dan 99 wordne geblokkeerd door gegevensvalidatie in de cellen.
Belangrijk is dat het hier gaat om opmaak: in de cellen zelf blijven de getallen (consumpties) staan waardoor ze nog gewoon optellen tot totalen. De formule om de vakjes van het turven op te vullen als er een totaal aantal consumpties in de groene cellen wordt ingevuld, is niet te ingewikkeld, aangezien ik gebruik maak van een opzoektabel. Zie daarvoor het blad 'Help'. Alles valt zo in zijn plooi zonder heel moeilijk te gaan doen. Let op: als je een getal schrijft in een vakje dat een formule bevat, dan overschrijf je de formule en houd je best een kopie bij van het template, of download mijn versie bovenaan deze pagina opnieuw. Je moet vermijden dat je eerdere wijzigingen aan jouw template verliest !
Uitbreidingen
Onmiddellijk bij het lezen zal je merken dat het toepassingsgebied veel ruimer is dan alleen een voetbalploeg. Denk maar aan alle verenigingen, groepen, bedrijven die iets organiseren. Een simpel café. Iedereen die de voorraad wenst bij te houden kan hier ook mee aan de slag.
De tabellen voor het turven zijn niet allemaal even groot, en dat is bewust. De consumpties met een prijs van 1,80 € gaan vlotter over de toonbank dan pakweg wijn en jenever. Het is dus ook minder nodig om een grote tabel te voorzien voor die laatste. Opmerking met betrekking tot wijn: dit staat bij een prijs van 1,50 €. Echter, de prijs van een glas wijn is 3,00 €. Er dienen dus 2 streepjes gezet te worden bij een consumptie van 1 glas wijn.
Uiteraard ben ik mij ervan bewust dat dit bestand nuttig kan zijn in vele situaties, ook in het geval er geen geld betaald wordt aan een eigenaar. Ik nodig jullie dan ook uit om het bestand aan te passen (op blad 3) zodat er omzet berekend wordt maar geen 'eigenaar' in het spel is: alle omzet is dus voor jullie.
Macro: alles op nul zetten
Er zijn in dit bestand een aantal invoervelden. Als het bestand al mooi opgevuld werd, dan kan het wel eens niet evident zijn om alle cellen terug zoals in de beginstand te zetten. Want in die beginstand heb je nl. geen lege cellen, maar formules die zorgen voor bvb. puntjes: ......... € Je wil dus niet die cellen zomaar leeg maken via "Delete". Maar ik wilde ook geen macro in het bestand zetten, dat kan (onterecht) mensen afschrikken. Vandaar de gele pijl rechts boven: het is een shape (vorm) met daaraan gelinkt de macro om de cellen weer zoals in de beginstand te zetten. Hieronder zet ik de inhoud van de macro voor zij die het wensen te gebruiken - ik raad het ten sterkste aan ! Maar, zoals reeds aangehaald, behoud steeds een recente kopie van jouw definitieve lege template. Je kan er zelfs een template voor maken in Excel, dat is een stukje functionaliteit in Excel die naar mijn mening over het algemeen onderbenut wordt.
Deze macro kan je best in Personal.xlsb bestand zetten. Ik doe dat ook en dan kan je verder werken met het xlsx bestand. Pas indien nodig een verwijzing aan zodat de macro feilloos loopt bij jou.
Sub Toog_Restart_From_Scratch()
With ActiveSheet
'maak cellen leeg
.Range("ZZZ_Restart_Clearen").ClearContents
'stel de formule opnieuw in in bepaalde bereiken (bestaande uit meerdere cellen)
.Range("ZZZ_Restart_Input_Stuks").Formula = "=INPUT_Stuks"
.Range("ZZZ_Restart_Input_Datum").Formula = "=INPUT_Datum"
.Range("ZZZ_Restart_Input_Lang_Bedrag").Formula = "=INPUT_Lang_Bedrag"
.Range("ZZZ_Restart_Input_Lange_Tekst").Formula = "=INPUT_Lange_Tekst"
'zet het woord "Neen" in een cel
.Range("ZZZ_Restart_Neen").Value = "Neen"
End With
End Sub
Je kan uiteraard ook de macro opslaan in het bestand zelf, dan kom je tot een xlsm bestand (zie bovenaan). In dat bestand heb ik overigens nog een tweede macro gezet die misschien wel nuttig voor jou kan zijn. Je kan dubbelklikken op een cel waar geturfd wordt. Bij elke dubbelklik verhoogt de waarde van de cel met 1. Tevens verlies je dan ook de formule die in die cel staat, aangezien een cel in Excel ofwel een formule heeft, ofwel een vaste waarde. Niet beide tegelijk.
Je ziet dat ik in de macro benoemde bereiken gebruik die reeds in de toepassing zitten: dit maakt het bestand veel robuuster. Je mag rijen / kolommen toevoegen / weglaten, zonder averechtse effecten op de macro - meteen nog een handigheidje die ik jullie niet wilde onthouden, om Excel VBA toepassingen onderhoudbaar te maken en te houden. En nu ik eraan denk, hier volgt nog een andere truc bij het beveiligen van cellen. Beveilig cel A1 niet of anderszins de eerste cel links bovenaan het werkblad. Als je dan Ctrl + Home drukt om naar de eerste cel links boven te springen, dan kan je die cel ook daadwerkelijk selecteren. Stel dat je die cel beveiligt, dan kan je er niet naartoe springen en blijft de cursor ergens anders staan. Ik heb het zelf graag als een bestand gesaved wordt 'links boven', aangezien dit heel vaak is waar het belangrijkste gedeelte van een werkblad zich bevindt.
We hebben in dit artikel gezien dat je zelfs met "enkel" + - * / % en ALS formules kan je toch heel veel bereiken. Wees wel creatief met stijlen, voorwaardelijke opmaak en gegevensvalidatie. Zo, dit bestand bespaart ons vaak het nodige rekenwerk en kan zeer kort op de bal spelen (pun intended) ! Doe er je voordeel mee en laat het vooral weten als jullie dit gaan inzetten in jullie vereniging / groep / bedrijf / ...
