Excel kennis verspreiden - deel 08: Stijlen in Excel
- 16/02/2014
 | Voorbeeldbestanden bij dit artikel: |
Pre-inleiding
Dit is ondertussen het 100ste Nederlandstalige Excel artikel op mijn website ! Jubileum ! Proficiat aan mezelf en aan de trouwe lezers !
Inleiding
Deel 08 is hier! Vandaag wil ik het hebben over Stijlen in Excel, maar eigenlijk is dat maar 1 van de verschillende onderwerpen. Je moet dit artikel (en bijgevoegd bestandje) vooral zien in het doorgeven van best practices in Excel voor het opstellen van formules. De toepassing die we gaan bekijken, is die van dienstencheques in België: een door de overheid gesubsidieerd systeem voor het activeren van over het algemeen laaggeschoolden, in voornamelijk poetshulp, koken, strijken boodschappen doen, enz. En daarbij deze activiteiten uit de zwarte markt te halen.
Ik heb eens een aantal Excel berekeningen gemaakt omtrent de vraag: "Wat kosten dienstencheques naargelang het gebruik (aantal uren per week, maand, jaar)?" Wat is de brutokost en wat is de nettokost (de aanschaf van dienstencheques is fiscaal aftrekbaar, zie verder). Met jullie wil ik graag deze formules doorlopen: ofwel zijn ze doodsimpel, ofwel zijn ze iets lastiger en kan je iets opsteken van de berekeningswijze. Daarnaast wil ik de aandacht vestigen op het gebruik van benoemde bereiken (klinkt bekend in de oren na vorige les) en tevens het gebruik van stijlen.
Cellen bestemd voor invoer
De invoercellen in bijgevoegd bestandje zijn als volgt - zie de cellen in de oranje achtergrondkleur.
| Cel | Benoemd bereik | Omschrijving | Waarde |
| C2 | INPUT_LagePrijs | Lage prijs | € 9,00 |
| C3 | INPUT_VerhoogdePrijs | Verhoogde prijs | € 10,00 |
| C4 | INPUT_LimietVoorLagePrijs | Limiet cheques aan lage prijs | 400 |
| C5 | INPUT_LimitFiscaal | Limiet fiscale aftrekbaarheid | € 1.380,00 |
| C6 | INPUT_FiscaalVoordeel | Fiscaal voordeel | 30% |
| C7 | INPUT_ConversieUurCheque | Conversie: 1 uur is x cheques | 1 |
| C9 | INPUT_VraagUrenPerWeek | Aantal uur nodig per week | 4 |
| C10 | INPUT_VraagUrenPerJaar | Aantal uur nodig per jaar | 208 |
De invoercellen bevatten de verlaagde prijs, waartegen de eerste 400 dienstencheques jaarlijks kunnen aangekocht worden. Daarnaast ook de verhoogde prijs van 10 € voor de cheques boven 400 stuks per jaar. Jaarlijks heeft een particulier ook recht op 30% belastingvoordeel voor cheques tot en met 1.380 €. Ik heb ook de parameter toegevoegd voor de conversie van werkuren naar cheques: met een overheid die steeds meer in de zakken van de burgers zit, lijkt het mij niet ondenkbaar dat de overheid de prijs van de cheques constant kan houden, maar de verhouding tussen uren en cheques kan aanpassen. Als men 2 ipv 1 cheque nodig heeft voor 1 werkuur, verdubbelt eigenlijk de prijs per uur en de prijs per cheque blijft gelijk. De voorlaatste parameter is er één om mee te experimenteren: het aantal gevraagde werkuren per week. Dat is mijn uitgangspunt: we weten hoeveel uur we nodig hebben per week (input veld) en dat bepaalt de totale kosten per week, maand, jaar gegeven de waarde van de overige parameters. Vooral in de les van volgende keer gaan we via simulaties onmiddellijk het effect van wijzigende aantal uren op de kosten bekijken. De laatste parameter is niet echt spannend, die ga je zelf ook wel snappen :-)
Elke cel heeft een naam gekregen: cel C2 noem ik INPUT_LagePrijs. Cel C3 noem ik INPUT_VerhoogdePrijs. Bekijk in de tabel hierboven even de namen en het gebruik van de prefix INPUT_ in hoofdletters, gevolgd door afwisselend hoofdletters en kleine letters. Cellen bestemd voor (tussen)berekeningen definieer ik met een ander prefix (CALC_ van calculatie) en maak ik automatisch op in een andere stijl: oranje letterkleur zonder achtergrondkleur.
Cellen bestemd voor berekeningen
| Cel | Benoemd bereik | Omschrijving | Waarde |
| C12 | CALC_BrutoKostPerJaar | Brutokost per jaar | € 1.872,00 |
| C13 | CALC_NettoKostPerJaar | Nettokost per jaar | € 1.458,00 |
| C14 | CALC_BrutoKostPerMaand | Brutokost per maand | € 156,00 |
| C15 | CALC_NettoKostPerMaand | Nettokost per maand | € 121,50 |
| C16 | CALC_BrutoKostPerWeek | Brutokost per week | € 36,00 |
| C17 | CALC_NettoKostPerWeek | Nettokost per week | € 28,04 |
De formules in de cellen C12:C17 (dat merk je aan de stijl die ik toepas en ook aan de namen van de cellen die beginnen met CALC_) zijn nu aan de beurt. Er zijn 2 berekeningsmanieren (bruto en netto) en 3 tijdsaspecten (week, maand, jaar). De kosten per week en per maand zijn simpel: de jaarkosten delen door 52 en 12, respectievelijk (een jaar bestaat hier simpelweg uit 52 weken, je zou hier meer fancy zaken kunnen voor opzetten indien je dat wenst). De kosten per jaar zijn iets minder voor de hand liggend en gaan we nu in detail bekijken.
Voor de brutokost per jaar hebben we een som van de kosten van de cheques tot 400 stuks per jaar, en van de kosten van de cheques boven 400 stuks per jaar. Dit laatste zou niet van toepassing kunnen zijn, bijvoorbeeld als iemand er maar 250 per jaar bestelt en niet aan de limiet van 400 stuks komt - dan is deel 2 gelijk aan 0. Het eerste deel kan je berekenen als:
=INPUT_LagePrijs * (MIN(INPUT_LimietVoorLagePrijs; INPUT_VraagUrenPerJaar * INPUT_ConversieUurCheque))
Bovenstaande namen in formules geef je gemakkelijk in via functietoets F3. Als je een formule opstelt druk je even op F3 waarop Excel een alfabetisch lijstje van de benoemde bereiken toont. Daarop kan je een naam selecteren en invoegen in de formule.
De eerste 400 cheques (400 is INPUT_LimietVoorLagePrijs) gaan aan 9 €, bij ons is dat INPUT_LagePrijs. We weten hoeveel cheques we nodig hebben: INPUT_VraagUrenPerJaar maal de conversie van uren naar cheques (de conversie is 1 gewoonlijk). We nemen dus 9 € per cheque maal het minimum van enerzijds 400, en anderzijds het aantal benodigde cheques per jaar. De MIN functie zorgt ervoor dat bijvoorbeeld 450 cheques worden afgeklopt op 400 cheques aan de verlaagde prijs. 50 cheques moeten dan aan de hogere prijs gerekend worden in deel 2 van de formule.
Het tweede deel slaat op de kosten van de cheques boven 400 stuks per jaar:
INPUT_VerhoogdePrijs * (MAX(0; INPUT_VraagUrenPerJaar * INPUT_ConversieUurCheque - INPUT_LimietVoorLagePrijs))
De verhoogde prijs vermenigvuldigen we met het verschil van de gevraagde cheques per jaar, en 400. Is dat verschil negatief (minder dan 400 cheques per jaar gekocht) dan geeft de MAX functie ons 0 en vermenigvuldigen we met andere woorden met 0. Bestudeer even goed het gebruik van MIN en MAX: ze zijn een goed en kort alternatief voor ALS-functies.
Voor de nettokosten per jaar hebben we ook een som van 2 zaken en gebruiken we weer MIN en MAX. Die kennen we nu toch. Deel 1 zijn de brutokosten tot aan het huidige fiscaal plafond van 1.380 €:
=(1 - INPUT_FiscaalVoordeel) * (MIN(INPUT_LimitFiscaal; CALC_BrutoKostPerJaar))
70% van de brutokosten draag je zelf (1 - 30% voordeel), als het minder is dan 1.380 € - vandaar de MIN functie. Daarnaast is er geen fiscaal voordeel voor het bedrag boven het plafond, dus tellen we dat er integraal bij, middels de MAX functie:
MAX(0; CALC_BrutoKostPerJaar - INPUT_LimitFiscaal)
Deze werkwijze is geheel analoog aan de logica voor de brutokosten. Dit leidt dan tot een gemiddelde maandkost van 121,50 € netto voor 4 werkuren per week.
Cellen bestemd voor output
Na invoer van parameters en berekeningen van resultaten heb je vaak ook nog de cellen bestemd voor de output. Die maak ik hier op met een stijl zodat ze een celrand hebben, grijze achtergrondkleur en vet lettertype.
| Cel | Benoemd bereik | Omschrijving | Waarde |
| C19 | OUTPUT_Belastingvoordeel_? | Belastingvoordeel per jaar | € 414,00 |
| C20 | OUTPUT_Belastingvoordeel_Perc | Belastingvoordeel per jaar | 22% |
| C22 | OUTPUT_UrenMetEnkelBelastingvoordeel | Uren/week met enkel belastingvoordeel | 2,95 |
| C24 | OUTPUT_GemiddeldNettoUurtarief | Gemiddeld netto uurtarief | € 7,01 |
| C29 | OUTPUT_KostVoorKeuzes | De nettokost per maand is: | € 121,50 |
Het belastingvoordeel op jaarbasis is simpel: trek de nettokosten van de brutokosten af. Procentueel kan je dat ook uitdrukken: het zal maximaal 30% zijn als belastingvoordeel (bij de huidige parameterwaarde). Stel dat je wil weten hoeveel uren je per week kan vragen zonder enig fiscaal nadeel te leiden. Dat is dan op weekbasis:
= ( INPUT_LimitFiscaal / INPUT_LagePrijs ) / INPUT_ConversieUurCheque / 52
Voor 1.380 € kan je tegenwoordig iets meer dan 150 cheques per jaar kopen. Dat zetten we om in werkuren en delen door 52 weken. Tot slot, het gemiddeld netto uurtarief is doodsimpel de nettokosten per jaar gedeeld door het aantal gevraagde uren per jaar.
ZEER BELANGRIJK: grijp terug naar de Formula Auditor van enkele lessen geleden, als je in detail en stap voor stap wil weten hoe elke cel berekend wordt. Formules in cellen maken gebruik van de namen van andere cellen, waardoor ze leesbaarder worden. Al worden de formules iets langer, het komt de leesbaarheid ten goede. Tevens begrijp je een tijd later veel beter hoe je destijds de formule opstelde.
Oh ja, u heeft gelijk, cel C29 (OUTPUT_KostVoorKeuzes) heb ik nog niet besproken. Dat is een leuke formule. Op basis van de oranje invoercellen C26 en C27 (INPUT_KeuzeBrutoNetto en INPUT_KeuzePeriodiciteit) via keuzelijstjes, bepaal ik hetgeen de gebruiker wenst te zien:
=INDIRECT("CALC_" & INPUT_KeuzeBrutoNetto & "KostPer" & INPUT_KeuzePeriodiciteit)
De INDIRECT functie bestaat ook in een Nederlandse Excel omgeving onder die naam. Van een tekst (String) gevormd door concatenaties gaan we met INDIRECT naar een benoemd bereik met die samengestelde naam, en halen de waarde op.
Meeteenheden
Ter info, maar zeker niet nutteloos, vermeld ik in kolom D de meeteenheid van de verschillende getallen. De stijl van die kolom is "Explanatory" en grijs/schuin gedrukt.
Extra info
Die stijl in het grijs pas ik ook toe op de kolommen F en G, die informatie bevatten over de benoemde bereiken. Gewoon ter info voor jullie en voor iedereen die het bestandje met zijn formules wil uitkleden.
Stijlen toepassen
Hoe pas je een Stijl toe in Excel? Zeer gemakkelijk. Klik de cel of cellen aan, en kies de gewenste stijl uit de Lint (Excel 2007 en later):
Uit onderstaande schermafdruk blijkt dat je ook een nieuwe Stijl kan definiëren, net als een bestaande Stijl dupliceren en (desgewenst) aanpassen. Bestaande stijlen aanpassen is ook mogelijk.
Stijlen hebben betrekking op een aantal opmaakmogelijkheden:
Het grote voordeel van de Stijlen is dat je zeer consistent kan werken: in al jouw bestanden kan je bepaalde cellen op dezelfde manier opmaken. Input is altijd opmaak X, output is altijd opmaak Y, berekeningscellen altijd opmaak Z. Dat maakt het auditen van (oudere) bestanden veel gemakkelijker, neem dit van mij aan. Zeker als je in een team dezelfde nomenclatuur afspreekt en hanteert.
Ter afsluiting
In dit artikel heb ik de formules besproken voor de casus van de dienstencheques. Daarbij, en veel belangrijker, is het gebruik van namen en stijlen in Excel. Een consequent toepassen hiervan leidt tot grote voordelen. Volgende les zal gaan over scenario’s, zoals de nettokosten per per jaar, afhankelijk van het aantal werkuren per week en het geldende fiscale regime. Excel bevat een aantal leuke tools voor scenario’s en data analyses. To be continued ! :-)
