Excel — Kostencalculatie
Module 1 — Kostprijsberekeningen
Goal Seek, Scenario Manager en machine-uurtarief automatiseren
Concepts
Excel als calculatie-engine voor KC4
In de KC4-cursus heb je kostprijsmethoden, machine-uurtarieven en break-even analyses handmatig berekend. Nu automatiseer je die berekeningen in Excel. Het voordeel: wijzig één invoerwaarde en alle uitkomsten updaten direct. Van Ginkel Solutions BV gebruikt dit voor snel doorrekenen van offertes en investeringsbeslissingen.
Goal Seek | Terugrekenen
Stel een doelwaarde in en laat Excel de input zoeken
Ideaal voor break-even: bij welk volume maak ik geen verlies?
---
Scenario Manager | Vergelijken
Sla drie inputsets op (laag/normaal/hoog)
Eén druk op "Samenvatting" toont alle uitkomsten naast elkaar
---
Machine-uurtarief | Calculator
Automatiseer de vijf kostencomponenten van het uurtarief
Pas gebruiksduur of bezettingsgraad aan en alles herberekentOpslagmethode automatiseren
Bij de opslagmethode bereken je de verkoopprijs door kosten op te tellen en een winstopslag toe te voegen. In Excel bouw je dit als een formulemodel:
Invoer Van Ginkel Solutions BV — IT-server:
B2: Inkoopprijs = €1.800
B3: Directe loonkosten = €120
B4: Directe materiaalkosten = €45
B5: Opslagpercentage indirecte kosten = 35%
B6: Winstopslagpercentage = 20%
Berekeningen:
B9: Directe kosten = B3 + B4 → €165
B10: Indirecte kosten (opsl)= B9 * B5 → €57,75
B11: Kostprijs = B2 + B9 + B10 → €2.022,75
B12: Winstopslag = B11 * B6 → €404,55
B13: Verkoopprijs = B11 + B12 → €2.427,30> EXAMTIP: Bij de opslagmethode worden indirecte kosten berekend als percentage van de directe kosten (of soms van de loonkosten). Let op welke grondslag het examen aangeeft.
Machine-uurtarief calculator
Het machine-uurtarief bestaat uit vijf componenten. Automatiseer dit in Excel zodat je snel kunt aanpassen:
Machine-uurtarief calculator Van Ginkel — orderpicker:
Invoer:
B2: Aanschafwaarde = €48.000
B3: Restwaarde = €3.000
B4: Economische gebruiksduur = 8 jaar
B5: Techische gebruiksduur = 10 jaar
B6: Rentepercentage = 5%
B7: Jaarlijkse capaciteit = 2.000 uur (bij volledige bezetting)
B8: Bezettingsgraad = 80%
B9: Jaarlijkse energiekosten = €3.200
B10: Jaarlijkse onderhoudskosten = €1.800
Berekeningen:
B13: Afschrijving/jaar = (B2 - B3) / B4 → €5.625
B14: Gemiddeld geïnvesteerd = (B2 + B3) / 2 → €25.500
B15: Rente/jaar = B14 * B6 → €1.275
B16: Werkelijke capaciteit = B7 * B8 → 1.600 uur
B17: Capaciteitskosten/uur = (B13 + B15) / B16 → €4,31
B18: Energiekosten/uur = B9 / B16 → €2,00
B19: Onderhoudskosten/uur = B10 / B16 → €1,13
B20: Machine-uurtarief = B17 + B18 + B19 → €7,44> EXAMTIP: Het machine-uurtarief wordt altijd berekend op basis van de werkelijke (geplande) bezetting, niet de maximale capaciteit. Een lagere bezettingsgraad verhoogt het uurtarief.
Goal Seek voor break-even analyse
Break-even berekenen via Goal Seek is sneller dan met de hand en laat je experimenteren met verschillende prijsniveaus.
Break-even model Van Ginkel — productlijn USB-hubs:
B2: Verkoopprijs per eenheid = €89
B3: Variabele kosten per eenheid = €54
B4: Vaste kosten per maand = €14.000
B5: Verkoopvolume (variabele!) = 400
B8: Omzet = B2 * B5 → €35.600
B9: Variabele kosten = B3 * B5 → €21.600
B10: Dekkingsbijdrage = B8 - B9 → €14.000
B11: Vaste kosten = B4 → €14.000
B12: Nettowinst = B10 - B11 → €0
Goal Seek voor break-even:
Gegevens → Wat-als-analyse → Doelzoeker
Cel instellen: B12 (nettowinst)
Op waarde: 0
Door wijzigen: B5 (verkoopvolume)
Resultaat: B5 = 400 eenheden (break-even punt)
Handmatige controle:
Break-even = €14.000 / (€89 - €54) = €14.000 / €35 = 400 ✓Scenario Manager voor drie volumescenario's
Gebruik Scenario Manager om drie volumescenario's naast elkaar te zetten:
Scenario's (wisselende cel = B5 = verkoopvolume):
Scenario "Laag volume": B5 = 300 eenheden
Scenario "Normaal volume": B5 = 500 eenheden
Scenario "Hoog volume": B5 = 750 eenheden
Samenvatting (resultaatcellen: B10, B12):
Scenario | Volume | Dekkingsbijdrage | Nettowinst
Laag volume | 300 | €10.500 | -€3.500
Normaal volume | 500 | €17.500 | +€3.500
Hoog volume | 750 | €26.250 | +€12.250Bij het "Laag"-scenario maakt Van Ginkel verlies. Dit is een signaal om óf de vaste kosten te verlagen, óf de verkoopprijs te verhogen.
> EXAMTIP: Scenario Manager slaat scenario's permanent op in het werkboek. Andere gebruikers kunnen direct schakelen tussen scenario's zonder de formules te begrijpen.
Delingscalculatie en meerdere producten
Bij meerdere productgroepen gebruik je een combinatie van INDEX/MATCH of XLOOKUP om kostprijzen per productgroep op te halen:
Kostprijstabel meerdere producten (bereik A15:D20):
Product | Inkoop | Var. kosten | Kostprijs
Server rack | €1.800 | €165 | =B16+C16
USB-hub | €54 | €8 | =B17+C17
Monitor | €220 | €25 | =B18+C18
Keyboard | €35 | €5 | =B19+C19
Opzoekformule (product geselecteerd in cel B22):
=XLOOKUP(B22; A16:A19; D16:D19; "Onbekend product")
Als B22 = "Monitor" → kostprijs = €245Missie
STORY: Van Ginkel Solutions BV overweegt te investeren in een eigen bezorgdienst voor IT-apparatuur. Karin wil weten wat het machine-uurtarief wordt voor de bezorgbus, en bij welk aantal leveringen per maand de dienst break-even draait. Ze vraagt jou een compleet calculatiemodel te bouwen in Excel.
Stap 1 — Machine-uurtarief bezorgbus
Bouw de machine-uurtarief calculator voor de bezorgbus van Van Ginkel:
Invoer werkblad "Machine":
B2: Aanschafwaarde bezorgbus = €42.000
B3: Restwaarde na 5 jaar = €6.000
B4: Economische gebruiksduur = 5 jaar
B5: Rentepercentage = 4,5%
B6: Maximale capaciteit = 2.500 uur/jaar
B7: Bezettingsgraad = 75%
B8: Brandstofkosten/jaar = €8.400
B9: Onderhoudskosten/jaar = €2.200
B10: Verzekeringskosten/jaar = €1.800
Berekeningen:
B13: Afschrijving/jaar = (B2-B3)/B4 → €7.200
B14: Gemiddeld geïnvesteerd = (B2+B3)/2 → €24.000
B15: Rente/jaar = B14*B5 → €1.080
B16: Werkelijke capaciteit = B6*B7 → 1.875 uur/jaar
B17: Afschrijving/uur = B13/B16 → €3,84
B18: Rente/uur = B15/B16 → €0,58
B19: Brandstof/uur = B8/B16 → €4,48
B20: Onderhoud/uur = B9/B16 → €1,17
B21: Verzekering/uur = B10/B16 → €0,96
B22: Machine-uurtarief = SOM(B17:B21) → €11,03Stap 2 — Break-even leveringen via Goal Seek
Bereken bij hoeveel leveringen de bezorgdienst quitte draait:
Break-even model werkblad "Break-even":
B2: Prijs per levering = €35
B3: Variabele kosten/levering = €18 (brandstof + chauffeurstijd)
B4: Vaste kosten/maand = €2.100 (afschrijving + rente + verzekering)
B5: Aantal leveringen (variabel)= 120
B8: Omzet = B2*B5 → €4.200
B9: Variabele kost. = B3*B5 → €2.160
B10: Dekkingsbijdr. = B8-B9 → €2.040
B11: Vaste kosten = B4 → €2.100
B12: Nettowinst = B10-B11 → -€60
Goal Seek:
Instelling: B12 = 0, wijzig B5
Resultaat: 123,5 → 124 leveringen per maand (break-even)
Controle:
Break-even = €2.100 / (€35 - €18) = €2.100 / €17 = 123,5 ✓Stap 3 — Drie volumescenario's vergelijken
Maak Scenario Manager met drie volume-scenario's voor de bezorgdienst:
Scenario's (wisselende cel = B5 = aantal leveringen):
Scenario "Pessimistisch": B5 = 90 leveringen/maand
Scenario "Realistisch": B5 = 150 leveringen/maand
Scenario "Optimistisch": B5 = 220 leveringen/maand
Samenvatting (resultaatcellen: B10; B12):
Scenario | Leveringen | Dekkingsbijdrage | Nettowinst
Pessimistisch | 90 | €1.530 | -€570
Realistisch | 150 | €2.550 | +€450
Optimistisch | 220 | €3.740 | +€1.640
Conclusie voor Karin:
De bezorgdienst is alleen winstgevend bij minimaal 124 leveringen per maand.
In het realistische scenario (150 per maand) is de winst €450 — een marge van 10,7%.
Het optimistische scenario (220 per maand) maakt het echt interessant: €1.640 winst.
Karin besluit de dienst een proefperiode van 6 maanden te geven.