Hvordan kan jeg beregne break-even-analyse i Excel?
Break-even-analyse er undersøgelsen af, hvor meget salg, eller solgte enheder, der kræves for at blive jævnt efter at have inkorporeret alle faste og variable omkostninger til drift af virksomheden. Break-even-analyse er kritisk i forretningsplanlægning og virksomhedsøkonomi, fordi antagelser om omkostninger og potentielt salg afgør, om et firma (eller projekt) er på vej til lønsomheden.
Lad os tage et limonadestand som et simpelt eksempel. Ligegyldigt hvor meget limonade du sælger, skal du stadig betale det samme lejebeløb hver måned. Så leje er en fast pris. Sammenlign dette med dine udgifter til citroner: Du vil kun købe den mængde frugt, du har brug for for at imødekomme den limonadeefterspørgsel, du forudsiger for din virksomhed. Om sommeren kan dette være flere citroner; om vinteren færre citroner. Så det gør citronkøb til en variabel pris. Variable omkostninger stiger i forhold til din virksomhed.
Formel til break-even analyse
Med ovenstående eksempel i tankerne opstår break-even, når:
Samlede faste omkostninger + Samlede variable omkostninger = Omsætning
- Samlede faste omkostninger er normalt kendt; de inkluderer ting som husleje, løn, forsyningsselskaber, renteudgifter, afskrivninger og afskrivninger.
- Samlede variable omkostninger er sværere at vide, men de er estimable og inkluderer ting som direkte materiale, fakturerbar arbejdskraft, provision og gebyrer.
- Omsætningen er enhedspris * Antal solgte enheder
Med denne info kan vi løse ethvert stykke af puslespillet algebraisk. Før du illustrerer det, er der et par punkter, der er værd at nævne.
Den første er, at der inden for formlen er uenighed om, hvad den bedste udligning er. Standarddefinitionen er indtægter, men problemet med at bruge indtægter er, at det at sige "vi er nødt til at sælge X-beløb for at dække omkostninger" udelukker skatter, som er en meget reel udgift. I forretningsplanlægning skal du altid overveje, hvordan driftsindtægter efter skat ser ud, og denne foranstaltning kaldes Netto driftsresultat efter skat (NOPAT). Ved at bruge NOPAT indarbejder du omkostningerne ved alle faktiske operationer, inklusive skattenes virkning. Imidlertid bruger den vidt forståede definition indtægter, så det er det, vi bruger.
Det andet kritiske punkt er, at kernen i denne formel er bidragsmarginen, der spiller en kritisk rolle i driften af gearing.
Den tredje ting, der er værd at bemærke, er, at hver del af ligningen - samlede faste omkostninger, samlede variable omkostninger og samlede indtægter - kan udtrykkes som "I alt" eller som en måling pr. Enhed afhængigt af, hvilken specifik break-even-måling vi har brug for. Dette undersøges mere grundigt i vores Excel-eksempel.
Break-Even-salg
Når man ser på det samlede salg, er målingen i dollars ($), ikke enheder:
Gennemsnitligt salg = samlede faste omkostninger / (bidragsmarginal)
Bidragsmarginal = 1 - (Variable omkostninger / indtægter)
Bemærk, at dette kan være enten pr. Enhed eller samlet, da det udtrykkes i procent.
Break-Even-enheder
Når man ser på det samlede salg, er målingen i enheder, ikke dollars. Afhængigt af de data, du har, skal du muligvis oversætte de samlede dollarværdier til pr. Enhedsværdier:
Break-Even-enheder = Samlede faste omkostninger / (Pris pr. Enhed - Variabel pris pr. Enhed)
Gennemsnitlig pris
Her løser vi for prisen med en kendt fast og variabel pris samt et anslået antal solgte enheder. Bemærk i de to første formler, vi kender salgsprisen og er i det væsentlige afledt mængde solgt til break-even. Men i dette tilfælde er vi nødt til at estimere både antallet af solgte enheder (eller den samlede solgte mængde) og fortælle det som en funktion af den salgspris, vi løser for.
Variable omkostninger Procent pr. Enhed = Samlede variable omkostninger / (Samlet variabel + samlede faste omkostninger)
Samlede faste omkostninger pr. Enhed = Samlede faste omkostninger / Samlet antal enheder
Gennemsnitlig pris = 1 / ((1 - Samlede variable omkostninger Procent pr. Enhed) * (Samlede faste omkostninger pr. Enhed))
I det væsentlige kan alle disse formler betragtes som en form for tilbagebetalingsperiode-analyse, bortset fra at "tiden i år" er effektivt, hvor lang tid det tager at generere det krævede antal salg i ovenstående beregninger.
Break-Even-analyse i Excel
Nu hvor vi ved, hvad break-even-analyse består af, kan vi begynde at modellere den i Excel. Der er en række måder at gøre dette på. De to mest nyttige er ved at oprette en break-even lommeregner eller ved at bruge Goal Seek, som er et indbygget Excel-værktøj.
Vi demonstrerer lommeregneren, fordi den bedre er i overensstemmelse med den bedste praksis for økonomisk modellering, hvori det hedder, at formler skal udbrydes og revideres.
Ved at oprette en scenarieanalyse, kan vi fortælle Excel at beregne baseret på enhed. ( Bemærk: Hvis tabellen virker lille, skal du højreklikke på billedet og åbne i en ny fane for højere opløsning.)
Eller baseret på pris:
Endelig kan vi let opbygge en følsomhedsmatrix for at undersøge, hvordan disse faktorer interagerer. I betragtning af forskellige omkostningsstrukturer kan vi se en række break-even-priser fra $ 28 til $ 133.