Hur kan jag beräkna break-even-analys i Excel?

Jämnhetsanalys är studien av hur mycket försäljning, eller sålda enheter, som krävs för att bryta jämnt efter att alla fasta och rörliga kostnader för att driva verksamheten har införlivats. Break-even-analys är avgörande i affärsplanering och företagsfinansiering, eftersom antaganden om kostnader och potentiell försäljning avgör om ett företag (eller projekt) är på väg till lönsamhet.

Som ett enkelt exempel, låt oss ta en limonadställning. Oavsett hur mycket saft du säljer måste du fortfarande betala samma hyresbelopp varje månad. Så, hyra är en fast kostnad. Jämför detta med dina utgifter för citroner: Du kommer bara att köpa den mängd frukt du behöver för att tillgodose den efterfrågade limonad som du förutspår för ditt företag. På sommaren kan det vara mer citroner; på vintern, färre citroner. Så det gör citronköp till en varierande kostnad. Variabla kostnader ökar i proportion till ditt företag.

Formel för Break-Even-analys

Med ovanstående exempel i åtanke inträffar break-even när:

Totala fasta kostnader + Totala variabla kostnader = Intäkter

• Totala fasta kostnader är vanligtvis kända; de inkluderar saker som hyra, löner, verktyg, räntekostnader, avskrivningar och avskrivningar.
• Totala variabla kostnader är svårare att veta, men de är uppskattbara och inkluderar saker som direkt material, fakturerbart arbete, provisioner och avgifter.
• Intäkterna är enhetspris * Antal sålda enheter

Med denna information kan vi lösa alla pusselbitar algebraiskt. Innan du illustrerar det finns det några punkter som är värda att nämna.

Den första är att det inom formeln är oenighet om vad den bästa utjämnaren är. Standarddefinitionen är intäkter, men problemet med att använda intäkter är att säga "vi måste sälja X-belopp för att täcka kostnader" exkluderar skatter, som är en mycket verklig kostnad. I affärsplanering bör du alltid överväga hur driftsinkomsterna efter skatt ser ut, och den åtgärden kallas NOPAT (Nettoperativ vinst efter skatt). Genom att använda NOPAT inkluderar du kostnaden för alla faktiska transaktioner, inklusive effekten av skatter. Men den allmänt förståda definitionen använder intäkter, så det är vad vi kommer att använda.

Den andra kritiska punkten är att kärnan i denna formel är bidragsmarginal, som spelar en avgörande roll för driften av hävstångseffekten.

Det tredje värt att notera är att varje del av ekvationen - totala fasta kostnader, totala rörliga kostnader och totala intäkter - kan uttryckas som "Totalt", eller som en mätning per enhet beroende på vilken specifik jämn mått vi behöver. Detta utforskas mer noggrant i vårt Excel-exempel.

Break-Even försäljning

När man tittar på den totala försäljningen är mätningen i dollar ($), inte enheter:

Jämn försäljning = totala fasta kostnader / (bidragsmarginal)

Bidragsmarginal = 1 - (Variabla kostnader / intäkter)

Observera att detta kan vara antingen per enhet eller totalt, eftersom det uttrycks i procent.

Break-Even-enheter

När man tittar på den totala försäljningen är mätningen i enheter, inte i dollar. Beroende på vilken information du har kan du behöva översätta totala dollarvärden till värden per enhet:

Jämnheter = totala fasta kostnader / (pris per enhet - variabel kostnad per enhet)

Jämnt pris

Här löser vi för priset med en känd fast och variabel kostnad samt ett uppskattat antal sålda enheter. Lägg märke till de första två formlerna, vi känner till försäljningspriset och härrör i huvudsak kvantiteten som sålts till break-even. Men i det här fallet måste vi beräkna antalet sålda enheter (eller den totala sålda kvantiteten) och relatera till det som en funktion av det försäljningspris vi löser för.

Variabla kostnader Procent per enhet = Totala variabla kostnader / (Totala variabel + Totala fasta kostnader)

Totala fasta kostnader per enhet = Totala fasta kostnader / Totalt antal enheter

Jämförelsepris = 1 / ((1 - Totala variabla kostnader Procent per enhet) * (Totala fasta kostnader per enhet))

I huvudsak kan alla dessa formler betraktas som en form av återbetalningsperiodanalys, förutom att "tiden i år" är faktiskt hur lång tid det tar att generera det nödvändiga antalet försäljningar i ovanstående beräkningar.

Break-Even-analys i Excel

Nu när vi vet vad break-even-analys består av kan vi börja modellera den i Excel. Det finns ett antal sätt att göra detta. De två mest användbara är genom att skapa en break-even kalkylator eller genom att använda Goal Seek, som är ett inbyggt Excel-verktyg.

Vi demonstrerar kalkylatorn, eftersom den bättre överensstämmer med bästa metoder för ekonomisk modellering och säger att formler bör brytas ut och vara granskade.

Genom att skapa en scenarioanalys kan vi berätta för Excel att beräkna baserat på enhet. ( Obs! Om tabellen verkar liten högerklickar du på bilden och öppnar i en ny flik för högre upplösning.)

Eller baserat på pris:

Slutligen kan vi enkelt bygga en känslighetsmatris för att undersöka hur dessa faktorer interagerar. Med tanke på olika kostnadsstrukturer kan vi se ett antal break-even-priser från $ 28 till $ 133.