Hoe voert u een break-even-analyse uit in Excel?

Stel dat u een nieuw product wilt verkopen en u kent de variabele kosten per eenheid en de totale vaste kosten. Op basis daarvan wilt u mogelijke verkoopvolumes voorspellen en de prijs van het product hierop afstemmen.

1. Maak een eenvoudige tabel en vul de rijen in met de gegeven gegevens. Zie de schermafbeelding hieronder:

2. Voer de juiste formules in om omzet, variabele kosten en winst te berekenen. Zie de schermafbeelding hierboven:
Omzet = Eenheidsprijs × Aantal verkocht
Variabele kosten = Kosten per eenheid × Aantal verkocht
Winst = Omzet – Variabele kosten – Vaste kosten

3. Klik op Gegevens > Wat-als-analyse > Doelzoeken.

4. In het venster Doelzoeken dat verschijnt, gaat u als volgt te werk (zie bovenstaande schermafbeelding):
(1) Stel de Cel instellen in op de winstcel; in ons voorbeeld is dit cel B7;
(2) Stel de Op waarde in op 0;
(3) Stel de Door cel te wijzigen in op de cel voor de eenheidsprijs; in ons voorbeeld is dit cel B1.
(4) Klik op de OK-knop.

5. Vervolgens verschijnt het dialoogvenster Status Doelzoeken. Klik op de OK-knop om de wijzigingen toe te passen.

De eenheidsprijs wordt nu aangepast van 40 naar 31,579, waardoor de nettowinst op 0 uitkomt. Als u dus een verkoopvolume van 50 verwacht, mag de eenheidsprijs niet lager zijn dan 31,579 — anders lijdt u verlies.

Download complexe break-even-sjabloon

http://office.microsoft.com/en-us/templates/breakeven-analysis-TC001116512.aspx
http://office.microsoft.com/en-us/templates/break-even-analysis-TC001017515.aspx

In vergelijking met de functie Doelzoeken kunnen we ook eenvoudig een formule gebruiken om een break-even-analyse uit te voeren in Excel.

1. Maak een eenvoudige tabel en vul de items in met de gegeven gegevens. Bij deze methode gaan we uit van een winst van 0, waarbij het aantal verkochte eenheden, de kosten per eenheid en de vaste kosten al zijn voorspeld. Zie de onderstaande schermafbeelding:

2. Typ in de tabel in cel B1 de formule =B6/B2+B4 om de eenheidsprijs te berekenen, in cel B3 de formule =B1*B2 om de omzet te berekenen en in cel B5 de formule =B2*B4 voor de variabele kosten. Zie de onderstaande schermafbeelding:

Wanneer u vervolgens een van de voorspelde waarden wijzigt – het aantal verkochte eenheden, de kosten per eenheid of de vaste kosten – past de eenheidsprijs zich automatisch aan. Zie bovenstaande schermafbeelding:

Tip : Sla een bereik op als AutoTekst-item (met behoud van celopmaak en formules) voor hergebruik in de toekomst
Het is waarschijnlijk erg tijdrovend om telkens opnieuw cellen te verwijzen en meerdere formules toe te passen voor de break-even-analyse. Kutools voor Excel biedt hiervoor een handige oplossing via de AutoTekst-functie: sla het bereik op als een AutoTekst-item, inclusief alle celopmaak en formules. Vervolgens kunt u dit bereik met slechts één klik hergebruiken in elk werkboek.

Ontvang nu een gratis proefversie van 30 dagen met alle functies!

Als u de verkoopgegevens al hebt vastgelegd, kunt u ook break-even-analyse uitvoeren met een grafiek in Excel. Deze methode helpt u eenvoudig een break-even-grafiek te maken.

1. Bereid een verkoopoverzicht voor zoals in de onderstaande schermafbeelding. In ons voorbeeld gaan we uit van een vast aantal verkochte eenheden, vaste kosten per eenheid en vaste vaste kosten, en voeren we de break-even-analyse uit op basis van de eenheidsprijs.

2. Voltooi de tabel zoals hieronder weergegeven:
(1) Typ in cel E2 de formule =D2*$B$1 en sleep de automatische invulgreep naar het bereik E2:E13;
(2) Typ in cel F2 de formule =D2*$B$1+$B$3 en sleep de automatische invulgreep naar het bereik F2:F13;
(3) Typ in cel G2 de formule =E2-F2 en sleep de automatische invulgreep naar het bereik G2:G13.

Tot zover hebben we de brongegevens van de break-even-grafiek die we later zullen maken, voltooid. Zie de onderstaande schermafbeelding:

3. Selecteer in de tabel tegelijkertijd de kolommen Omzet, Kosten en Winst en klik vervolgens op Invoegen > Lijn- of oppervlakgrafiek invoegen > Lijn. Zie de schermafbeelding:

4. Er wordt nu een lijngrafiek gemaakt. Klik met de rechtermuisknop op de grafiek en selecteer Gegevens selecteren in het contextmenu. Zie de schermafbeelding hieronder:

5. In het dialoogvenster Gegevens selecteren brongegevens gaat u als volgt te werk:
(1) Selecteer in het gedeelte Bereik van de reeksnaam een van de beschikbare reeksen. In mijn voorbeeld kies ik de Omzet-reeks;
(2) Klik op de knop Bewerken in het gedeelte Horizontale aslabels;
(3) Geef in het verschijnende dialoogvenster Bereik van de aslabels de kolom Eenheidsprijs op (zonder de kolomkop) als Bereik van aslabels;
(4) Klik op OK > OK om de wijzigingen op te slaan.

In de break-even-grafiek ziet u nu dat het break-even-punt optreedt wanneer de prijs gelijk is aan 36, zoals in de onderstaande schermafbeelding wordt weergegeven:

Op dezelfde manier kunt u ook een break-even-grafiek maken om het break-even-punt te analyseren op basis van het aantal verkochte eenheden, zoals in de onderstaande schermafbeelding wordt weergegeven:

Exporteer/sla selectie eenvoudig op als afzonderlijk PDF/CSV/TEKST/HTML-bestand in Excel

Wanneer u bijvoorbeeld uw break-even-analyse in Excel hebt voltooid en hiermee een presentatie wilt maken, kunt u de Bereik exporteren naar bestand-functie van Kutools voor Excel gebruiken om de selectie razendsnel te exporteren als afzonderlijke PDF-, HTML-, CSV- of tekstbestanden, enz.