Excel — Kostprijsberekening
Module 2 — Kostprijsberekening
Goal Seek, Scenario Manager en automatische afschrijvingstabel
Concepts
Excel als calculatietool
Na het leren van kostprijs, break-even en afschrijving met de hand is het tijd om dezelfde berekeningen in Excel te automatiseren. Excel maakt het mogelijk om met één druk op de knop tientallen scenario's door te rekenen. Voor Van Ginkel Solutions BV — die met wisselende inkoopprijzen werkt — is dit een groot voordeel.
Goal Seek | Doelzoeker
Zoekt automatisch de inputwaarde voor een gewenste uitkomst
Handig voor: hoeveel moet ik verkopen om quitte te spelen?
---
Scenario Manager | Scenario's
Sla meerdere scenario's op in één werkblad
Vergelijk laag/midden/hoog en schakel makkelijk
---
Afschrijvingstabel | Automatisering
Genereer automatisch een complete afschrijvingstabel
Gebruik formules voor lineair en degressiefGoal Seek — automatisch break-even vinden
Goal Seek (Doelzoeker) is een ingebouwd Excel-hulpmiddel. Je zegt: "Ik wil dat cel B12 de waarde 0 heeft — welke waarde moet cel B3 dan krijgen?" Excel lost dit automatisch op.
**Stap voor stap:**
- Zorg dat je resultaatcel (bijv. nettowinst) een formule heeft die verwijst naar de invoercel (bijv. aantal verkochte eenheden).
- Ga naar het tabblad **Gegevens → Wat-als-analyse → Doelzoeker**.
- Stel in: Cel instellen = B12 (nettowinst), Op waarde = 0, Door wijzigen cel = B3 (aantal verkochte eenheden).
- Klik OK — Excel berekent automatisch het break-even punt.
Voorbeeld Van Ginkel Solutions BV — IT-producten:
Cel B2: Verkoopprijs per eenheid = €450
Cel B3: Aantal verkopen = 100 (dit gaat Excel aanpassen)
Cel B4: Variabele kosten per eenk. = €280
Cel B5: Vaste kosten per periode = €12.000
Cel B8: Omzet = B2 * B3 → =450*100 = €45.000
Cel B9: Var. kost. = B4 * B3 → =280*100 = €28.000
Cel B10: Dekkingsbijdrage = B8 - B9 → €17.000
Cel B11: Vaste kosten = B5 → €12.000
Cel B12: Nettowinst = B10 - B11 → €5.000
Goal Seek: zet B12 op 0 door B3 te wijzigen
→ Resultaat: B3 = 70,59 → 71 eenheden (break-even punt)
Controle:
Break-even = Vaste kosten / Dekkingsbijdrage per eenheid
= €12.000 / (€450 - €280) = €12.000 / €170 = 70,59 ✓> EXAMTIP: Goal Seek werkt alleen met één variabele tegelijk. Wil je meerdere variabelen aanpassen? Gebruik dan Scenario Manager of Oplosser (Solver).
Scenario Manager — drie inkoopprijsscenario's
Scenario Manager slaat meerdere sets inputwaarden op. Je kunt per scenario de inkoopprijs instellen (laag/midden/hoog) en direct zien hoe de kostprijs en winst veranderen.
Scenario's Van Ginkel — inkoopprijs serverrack:
Scenario 1 (Laag): Inkoopprijs = €280
Scenario 2 (Midden): Inkoopprijs = €320
Scenario 3 (Hoog): Inkoopprijs = €370
Stel in via: Gegevens → Wat-als-analyse → Scenariobeheer
→ Voeg toe: naam "Laag", wisselende cel = B4, waarde = 280
→ Voeg toe: naam "Midden", wisselende cel = B4, waarde = 320
→ Voeg toe: naam "Hoog", wisselende cel = B4, waarde = 370
Klik op "Samenvatting" voor een overzichtstabel van alle drie scenario's**Uitkomsten per scenario (verkoopprijs €450, vaste kosten €12.000, 100 eenheden):**
Laag Midden Hoog
Inkoopprijs: €280 €320 €370
Kostprijs: €280 €320 €370
Dekkingsbijdr:€17.000 €13.000 €8.000
Nettowinst: €5.000 €1.000 -€4.000
Break-even: 71 93 150 eenheden> EXAMTIP: Een negatieve nettowinst bij het "Hoog"-scenario betekent dat Van Ginkel bij die inkoopprijs verlies maakt op 100 stuks. Het break-even punt stijgt dan naar 150 eenheden — mogelijk niet haalbaar.
Automatische afschrijvingstabel in Excel
Een lineaire afschrijvingstabel is eenvoudig te automatiseren met Excel-formules. Je hoeft alleen de begingegevens in te voeren en de rest berekent Excel automatisch.
Invoer (eenmalig):
B1: Aanschafwaarde = €24.000
B2: Restwaarde = €0
B3: Gebruiksduur = 5 jaar
Formule kolom afschrijving (cel C8):
=($B$1 - $B$2) / $B$3
→ Absolute verwijzingen ($) zodat je de formule naar beneden kunt kopiëren
Formule boekwaarde begin jaar n (cel D8):
=B1 - (A8-1) * C8
waarbij A8 het jaarnummer is (1, 2, 3, 4, 5)
Formule boekwaarde einde jaar n (cel E8):
=D8 - C8**Volledig schema Van Ginkel — magazijninrichting €24.000, 5 jaar lineair:**
Jaar | Boekwaarde begin | Afschrijving | Boekwaarde einde
1 | €24.000 | €4.800 | €19.200
2 | €19.200 | €4.800 | €14.400
3 | €14.400 | €4.800 | €9.600
4 | €9.600 | €4.800 | €4.800
5 | €4.800 | €4.800 | €0
Jaarlijkse afschrijving = (€24.000 - €0) / 5 = €4.800> EXAMTIP: Gebruik altijd absolute celverwijzingen ($B$1) voor de vaste invoerwaarden. Zo kun je de formule voor 5 of 10 jaar naar beneden kopiëren zonder fouten.
Kostprijs-model combineren
Een compleet kostprijsmodel in Excel combineert alle berekeningen:
Structuur kostprijsmodel Van Ginkel:
INVOER:
Inkoopprijs (scenario-afhankelijk)
Opslagpercentage (vast of variabel)
Vaste kosten (huur, afschrijving, salarissen)
Verkoopaantal (doel of schatting)
BEREKENINGEN:
Opslagbedrag = inkoopprijs × opslagpercentage
Kostprijs = inkoopprijs + opslagbedrag
Dekkingsbijdrage = verkoopprijs - variabele kosten
Break-even punt = vaste kosten / dekkingsbijdrage
UITVOER (automatisch bijgewerkt):
Nettowinst = totale opbrengsten - totale kosten
Break-even aantal = berekend via formule of Goal SeekMissie
STORY: Van Ginkel Solutions BV overweegt een nieuwe productlijn: refurbished laptops inkopen en met winst doorverkopen. Karin wil weten hoeveel laptops ze minimaal moet verkopen om break-even te draaien, en hoe de winst verandert bij drie verschillende inkoopprijsscenario's. Ze vraagt jou een compleet calculatiemodel in Excel te bouwen.
Stap 1 — Basismodel opzetten
Maak een Excel-werkblad met de basisgegevens van Van Ginkel Solutions BV:
Cel B2: Verkoopprijs per laptop = €549
Cel B3: Inkoopaantal (variabel) = 80
Cel B4: Inkoopprijs per laptop = €320 (startscenario)
Cel B5: Transportkosten per laptop = €15
Cel B6: Vaste kosten per maand = €8.500
Berekeningen (met formules):
Cel B9: Variabele kosten/eenheid = B4 + B5 → €335
Cel B10: Dekkingsbijdrage/eenheid = B2 - B9 → €214
Cel B11: Totale omzet = B2 * B3 → €43.920
Cel B12: Totale variabele kosten = B9 * B3 → €26.800
Cel B13: Totale vaste kosten = B6 → €8.500
Cel B14: Nettowinst = B11 - B12 - B13 → €8.620Stap 2 — Goal Seek voor break-even
Gebruik Goal Seek om te berekenen hoeveel laptops Van Ginkel minimaal moet verkopen:
Stap: Gegevens → Wat-als-analyse → Doelzoeker
Instelling:
Cel instellen: B14 (nettowinst)
Op waarde: 0
Door wijzigen: B3 (inkoopaantal)
Resultaat:
Break-even = 39,72 → 40 laptops per maand
Handmatige controle:
Break-even = vaste kosten / dekkingsbijdrage per eenheid
= €8.500 / €214 = 39,72 → 40 laptops ✓Stap 3 — Scenario Manager voor drie inkoopprijzen
Maak drie scenario's aan voor de inkoopprijs (cel B4) via Scenario Manager:
Scenario's aanmaken:
Gegevens → Wat-als-analyse → Scenariobeheer → Toevoegen
Scenario "Laag": B4 = €280 (gunstige inkoop, bulk)
Scenario "Midden": B4 = €320 (normale marktprijs)
Scenario "Hoog": B4 = €370 (krappe markt, weinig aanbod)
Klik op "Samenvatting" → selecteer B10; B14 als resultaatcellen
Verwachte samenvatting (bij 80 laptops):
Scenario | Inkoopprijs | Dekkingsbijdrage | Nettowinst | Break-even
Laag | €280 | €254/stuk | €11.820 | 34 laptops
Midden | €320 | €214/stuk | €8.620 | 40 laptops
Hoog | €370 | €164/stuk | €4.620 | 52 laptopsKarin concludeert: bij een hoge inkoopprijs van €370 moet ze minstens 52 laptops verkopen om quitte te draaien. Dat is haalbaar, maar laat weinig marge. Ze gaat onderhandelen voor een inkoopprijs dichter bij het "Laag"-scenario.