Hoe voer je een break-evenanalyse uit in Excel?

Stel dat je van plan bent een nieuw product te verkopen, en je kent de variabele kosten per eenheid en de totale vaste kosten. Nu ga je de mogelijke verkoopvolumes voorspellen en het product op basis daarvan prijzen.

1. Maak een eenvoudige tabel en vul items met gegeven data in de tabel. Zie onderstaande schermafbeelding:

2. Voer de juiste formules in om de opbrengsten, variabele kosten en winst te berekenen. Zie bovenstaande schermafbeelding:
Opbrengsten = Verkoopprijs x Aantal Verkocht
Variabele Kosten = Kosten per Eenheid x Aantal Verkocht
Winst = Opbrengsten – Variabele Kosten – Vaste Kosten

3. Klik op Gegevens > Wat-als Analyse > Goal Seek.

4. In het dialoogvenster Goal Seek dat wordt geopend, doe het volgende (zie bovenstaande schermafbeelding):
(1) Specificeer de Instelcel als de Winstcel, in ons geval is dit Cel B7;
(2) Specificeer de Tot waarde als 0;
(3) Specificeer de Door wijziging van cel als de Verkoopprijs-cel, in ons geval is dit Cel B1.
(4) Klik op OK knop.

5. En dan verschijnt het dialoogvenster Goal Seek Status. Klik op de OK knop om het toe te passen.

Nu verandert de verkoopprijs van 40 naar 31,579, en de nettowinst verandert naar 0. Daarom, als je voorspelt dat het verkoopvolume 50 zal zijn, mag de verkoopprijs niet minder zijn dan 31,579, anders ontstaat er verlies.

Download complexe Break-even Template

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 Goal Seek-functie kunnen we ook de formule toepassen om gemakkelijk een break-evenanalyse uit te voeren in Excel.

1. Maak een eenvoudige tabel en vul items met gegeven data in de tabel. Bij deze methode gaan we ervan uit dat de winst 0 is, en we hebben al het voorspelde aantal verkochte eenheden, de kosten per eenheid en de vaste kosten voorspeld. Zie onderstaande schermafbeelding:

2. Typ in de tabel de formule =B6/B2+B4 in Cel B1 voor het berekenen van de Verkoopprijs, typ de formule =B1*B2 in Cel B3 voor het berekenen van de opbrengsten, en typ de formule =B2*B4 in Cel B5 voor variabele kosten. Zie onderstaande schermafbeelding:

En dan, wanneer je één waarde verandert van voorspelde verkochte eenheden, kosten per eenheid of vaste kosten, zal de waarde van de verkoopprijs automatisch veranderen. Zie bovenstaande schermafbeelding:

Tip: Sla een bereik op als AutoTekst-item (behoud celopmaak en formules) voor hergebruik in de toekomst
Het moet heel vervelend zijn om elke keer cellen te raadplegen en meerdere formules toe te passen voor de break-evenanalyse. Kutools voor Excel biedt een handige oplossing via de AutoTekst functie om het bereik op te slaan als een AutoTekst-item, dat de celopmaak en formules in het bereik behoudt. En dan kun je dit bereik met slechts één klik hergebruiken in elk werkblad.

Start nu met een 30-daagse proefversie met alle functies!

Als je de verkoopgegevens al hebt vastgelegd, kun je ook een break-evenanalyse maken met een grafiek in Excel. Deze methode leidt je door het gemakkelijk maken van een break-even grafiek.

1. Bereid een verkoop tabel voor zoals hieronder in de schermafbeelding getoond. In ons geval gaan we ervan uit dat de verkochte eenheden, kosten per eenheid en vaste kosten vast zijn, en we moeten de break-evenanalyse doen op basis van de verkoopprijs.

2. Voltooi de tabel zoals hieronder getoond:
(1) Typ in Cel E2 de formule =D2*$B$1, en sleep de AutoVul-handgreep omlaag naar Bereik E2:E13;
(2) Typ in Cel F2 de formule =D2*$B$1+$B$3, en sleep de AutoVul-handgreep omlaag naar Bereik F2:F13;
(3) Typ in Cel G2 de formule =E2-F2, en sleep de AutoVul-handgreep omlaag naar het Bereik G2:G13.

Tot nu toe hebben we de brondata van de break-even grafiek voltooid die we later zullen maken. Zie onderstaande schermafbeelding:

3. Selecteer in de tabel tegelijkertijd de kolom Opbrengsten, Kostenkolom en Winstkolom, en klik dan op Invoegen > Lijn of Oppervlaktegrafiek invoegen > Lijn. Zie schermafbeelding:

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

5. In het dialoogvenster Gegevensbron selecteren, doe het volgende:
(1) In de Legenda-items (Reeksen) sectie, selecteer een van de reeksen zoals je nodig hebt. In mijn voorbeeld selecteer ik de Opbrengsten reeks;
(2) Klik op de Bewerken knop in de Horizontale (Categorie) Aslabels sectie;
(3) In het pop-up venster Aslabels, specificeer de kolom Verkoopprijs (behalve de kolomnaam) als aslabelbereik;
(4) Klik op OK > OK om de wijzigingen op te slaan.

Nu zie je in de break-even grafiek dat het break-even punt optreedt wanneer de prijs gelijk is aan 36 zoals hieronder in de schermafbeelding te zien is:

Op dezelfde manier kun je ook een break-even grafiek maken om het break-even punt te analyseren op basis van verkochte eenheden zoals hieronder in de schermafbeelding te zien is:

Exporteer/opslaan selectie eenvoudig als afzonderlijke PDF/CSV/TEXT/HTML bestanden in Excel

Bijvoorbeeld, wanneer je je break-evenanalyse in Excel hebt voltooid, wil je misschien je presentatie maken met deze break-evenanalyse. In deze situatie kun je Kutools voor Excel’s Export Range To File functie gebruiken om snel de selectie te exporteren als een apart PDF-bestand, HTML-bestand, CSV-bestand, Tekstbestand, etc.