Hvordan kan jeg beregne break-even-analyse i Excel?

Break-even-analyse er studien av hvor mye salg, eller solgte enheter, som kreves for å være jevn etter å ha innarbeidet alle faste og variable kostnader for å drifte driften av virksomheten. Break-even-analyse er kritisk i forretningsplanlegging og forretningsfinansiering, fordi antakelser om kostnader og potensielt salg avgjør om et selskap (eller prosjekt) er på god vei til lønnsomheten.

Som et enkelt eksempel, la oss ta en limonadestilling. Uansett hvor mye limonade du selger, må du fortsatt betale samme mengde leie hver måned. Så er leie en fast kostnad. Sammenlign dette med utgiftene dine til sitroner: Du vil bare kjøpe mengden frukt du trenger for å tilfredsstille sitronadeetterspørselen du forutser for din virksomhet. Om sommeren kan dette være flere sitroner; om vinteren, færre sitroner. Så det gjør sitronkjøp til en variabel pris. Variable kostnader øker i forhold til virksomheten din.

Formel for break-even analyse

Med ovenstående eksempel i bakhodet oppstår break-even når:

Samlede faste kostnader + Samlede variable kostnader = Inntekter

• Totale faste kostnader er vanligvis kjent; de inkluderer ting som husleie, lønn, verktøy, renteutgifter, avskrivninger og amortiseringer.
• Totalt varierende kostnader er vanskeligere å vite, men de kan estimeres og inkluderer ting som direkte materiale, fakturerbar arbeidskraft, provisjoner og gebyrer.
• Inntekten er enhetspris * Antall solgte enheter

Med denne informasjonen kan vi løse ethvert stykke av puslespillet algebraisk. Før du illustrerer det, er det noen få poeng som er verdt å nevne.

Den første er at det innenfor formelen er uenighet om hva den beste utjevneren er. Standarddefinisjonen er inntekter, men problemet med å bruke inntekter er at å si "vi trenger å selge X-beløp for å dekke kostnader" ekskluderer skatter, som er en veldig reell kostnad. I forretningsplanlegging bør du alltid vurdere hvordan driftsinntekter etter skatt ser ut, og det tiltaket kalles NOPAT (Nettoperasjonsresultat etter skatt). Ved å bruke NOPAT, tar du med kostnadene for alle faktiske operasjoner, inkludert effekten av skatter. Imidlertid bruker den vidt forståtte definisjonen inntekter, så det er det vi vil bruke.

Det andre kritiske punktet er at kjernen i denne formelen er bidragsmarginen, som spiller en kritisk rolle i driftsinnflytningen.

Den tredje tingen som er verdt å merke seg er at hver del av ligningen - totale faste kostnader, totale variable kostnader og totale inntekter - kan uttrykkes som "Totalt", eller som en måling per enhet avhengig av hvilket spesifikt break-even-mål vi trenger. Dette blir undersøkt grundigere i vårt Excel-eksempel.

Break-Even-salg

Når man ser på totalt salg er målingen i dollar ($), ikke enheter:

Gjennomsnittlig salg = Totale faste kostnader / (Bidragsmargin)

Bidragsmarginen = 1 - (Variable kostnader / inntekter)

Merk at dette kan være enten per enhet eller totalt, siden det er uttrykt i prosent.

Break-Even-enheter

Når man ser på totalt salg, er målingen i enheter, ikke dollar. Avhengig av dataene du har, kan det hende du må oversette totale dollarverdier til enhetsverdier:

Break-Even-enheter = Totale faste kostnader / (Pris per enhet - Variabel kostnad per enhet)

Break-Even-pris

Her løser vi for prisen gitt en kjent fast og variabel kostnad, samt et estimert antall solgte enheter. Legg merke til i de to første formlene, vi kjenner salgsprisen, og henter i hovedsak kvantitet solgt til break-even. Men i dette tilfellet må vi estimere både antall solgte enheter (eller total solgt mengde) og forholde oss til det som en funksjon av salgsprisen vi løser for.

Variable Kostnader Prosent per enhet = Totalt Variable Kostnader / (Total Variabel + Totalt faste kostnader)

Totale faste kostnader per enhet = Totale faste kostnader / Totalt antall enheter

Delepris = 1 / ((1 - Totalt varierende kostnader prosent per enhet) * (Totalt faste kostnader per enhet))

I hovedsak kan alle disse formlene betraktes som en form for tilbakebetalingstidanalyse, bortsett fra at "tiden i år" er effektivt hvor lang tid det tar å generere det nødvendige antall salg i beregningene ovenfor.

Break-Even-analyse i Excel

Nå som vi vet hva break-even-analyse består av, kan vi begynne å modellere den i Excel. Det er flere måter å oppnå dette på. De to mest nyttige er ved å lage en break-even-kalkulator eller ved å bruke Goal Seek, som er et innebygd Excel-verktøy.

Vi demonstrerer kalkulatoren, fordi den bedre samsvarer med beste praksis for økonomisk modellering som sier at formler skal brytes ut og revideres.

Ved å lage en scenarioanalyse, kan vi fortelle Excel å beregne basert på enhet. ( Merk: Hvis tabellen virker liten, høyreklikker du på bildet og åpner i en ny fane for høyere oppløsning.)

Eller basert på pris:

Endelig kan vi enkelt bygge en følsomhetsmatrise for å utforske hvordan disse faktorene samhandler. Gitt forskjellige kostnadsstrukturer, kan vi se en rekke break-even-priser fra $ 28 til $ 133.