Hoe kan ik break-even analyse in Excel berekenen?

Break-even analyse is de studie van het aantal verkopen of verkochte eenheden dat moet worden afgebroken, zelfs nadat alle vaste en variabele kosten voor het runnen van de activiteiten zijn opgenomen. Break-even analyse is van cruciaal belang bij bedrijfsplanning en bedrijfsfinanciering, omdat aannames over kosten en potentiële verkopen bepalen of een bedrijf (of project) op koers ligt voor winstgevendheid.

Laten we als een eenvoudig voorbeeld een limonade nemen. Hoeveel limonade u ook verkoopt, u ​​moet nog steeds elke maand hetzelfde bedrag betalen. Huurprijs is dus een vaste kost. Vergelijk dit met uw uitgaven voor citroenen: u koopt alleen de hoeveelheid fruit die u nodig hebt om te voldoen aan de limonade-eis die u voor uw bedrijf voorspelt. In de zomer kunnen dit meer citroenen zijn; in de winter, minder citroenen. Dus dat maakt citroenaankopen variabele kosten. Variabele kosten stijgen evenredig met uw bedrijf.

Formule voor break-even analyse

Met het bovenstaande voorbeeld in gedachten, treedt break-even op wanneer:

Totale vaste kosten + Totale variabele kosten = omzet

• Totale vaste kosten zijn meestal bekend; ze omvatten zaken als huur, salarissen, nutsvoorzieningen, rentelasten, afschrijvingen en amortisatie.
• Totale variabele kosten zijn moeilijker om te weten, maar ze zijn te schatten en omvatten zaken als direct materiaal, factureerbare arbeid, commissies en vergoedingen.
• Opbrengst is eenheidsprijs * Aantal verkochte eenheden

Met deze info kunnen we elk stuk van de puzzel algebraïsch oplossen. Voordat we dat illustreren, zijn er een paar punten die het vermelden waard zijn.

De eerste is dat er binnen de formule onenigheid bestaat over wat de beste equalizer is. De standaarddefinitie is omzet, maar het probleem met het gebruik van omzet is dat het zeggen "we moeten het X-bedrag verkopen om de kosten te dekken" exclusief belastingen, wat een zeer reële uitgave is. In bedrijfsplanning moet u altijd overwegen hoe het bedrijfsresultaat na belastingen eruit ziet, en die maatregel wordt het netto bedrijfsresultaat na belastingen (NOPAT) genoemd. Door NOPAT te gebruiken, neemt u de kosten op van alle daadwerkelijke bewerkingen, inclusief het effect van belastingen. De algemeen begrepen definitie gebruikt echter inkomsten, dus dat is wat we zullen gebruiken.

Het tweede kritieke punt is dat de kern van deze formule de contributiemarge is, die een cruciale rol speelt in het hefboomeffect.

Het derde vermeldenswaardige is dat elk deel van de vergelijking - totale vaste kosten, totale variabele kosten en totale inkomsten - kan worden uitgedrukt als "Totaal" of als een meting per eenheid, afhankelijk van de specifieke break-even-maat die we nodig hebben. Dit wordt grondiger onderzocht in ons Excel-voorbeeld.

Break-even verkoop

Wanneer we kijken naar de totale omzet, is de meting in dollars ($), niet in eenheden:

Break-even omzet = totale vaste kosten / (contributiemarge)

Bijdrage marge = 1 - (Variabele kosten / opbrengsten)

Merk op dat dit per eenheid of totaal kan zijn, omdat het wordt uitgedrukt als een percentage.

Break-even-eenheden

Wanneer we kijken naar de totale omzet, is de meting in eenheden, niet in dollars. Afhankelijk van de gegevens die u hebt, moet u mogelijk de totale dollarwaarden omzetten in waarden per eenheid:

Break-even-eenheden = totale vaste kosten / (prijs per eenheid - variabele kosten per eenheid)

Break-even prijs

Hier lossen we de prijs op met bekende vaste en variabele kosten, evenals een geschat aantal verkochte eenheden. Merk op dat we in de eerste twee formules de verkoopprijs kennen en in feite de verkochte hoeveelheid afleiden aan break-even. Maar in dit geval moeten we zowel het aantal verkochte eenheden (of de totale verkochte hoeveelheid) schatten en dat relateren als een functie van de verkoopprijs waarvoor we oplossen.

Variabele kosten Percentage per eenheid = Totale variabele kosten / (Totale variabele + Totale vaste kosten)

Totale vaste kosten per eenheid = Totale vaste kosten / Totaal aantal eenheden

Break-even prijs = 1 / ((1 - Totaal variabele kostenpercentages per eenheid) * (totale vaste kosten per eenheid))

In wezen kunnen al deze formules worden beschouwd als een vorm van analyse van de terugverdientijd, behalve dat de "tijd in jaren" effectief is hoe lang het duurt om het vereiste aantal verkopen in de bovenstaande berekeningen te genereren.

Break-even analyse in Excel

Nu we weten waaruit break-even analyse bestaat, kunnen we beginnen met het modelleren ervan in Excel. Er zijn een aantal manieren om dit te bereiken. De twee meest bruikbare zijn door een break-even calculator te maken of door Doel zoeken te gebruiken, een ingebouwde Excel-tool.

We demonstreren de rekenmachine, omdat deze beter voldoet aan de best practices voor financiële modellen, waarin wordt gesteld dat formules moeten worden uitgesplitst en controleerbaar zijn.

Door een scenarioanalyse te maken, kunnen we Excel laten berekenen op basis van eenheid. ( Opmerking: als de tabel klein lijkt, klik met de rechtermuisknop op de afbeelding en open deze in een nieuw tabblad voor een hogere resolutie.)

Of op basis van prijs:

Ten slotte kunnen we eenvoudig een gevoeligheidsmatrix maken om te onderzoeken hoe deze factoren op elkaar inwerken. Gezien de verschillende kostenstructuren zien we een reeks break-even prijzen van $ 28 tot $ 133.