Creați o diagramă a profitabilității în Excel

Analiza profitabilității este un instrument pentru estimarea potențialului de profit al unui model de afaceri și a diferitelor strategii de stabilire a prețurilor. Puteți să combinați cu ușurință costurile fixe, costurile variabile și opțiunile de tarifare în Excel pentru a determina pauza de eveniment a produsului dvs. Acesta este numărul de unități pe care trebuie să le vindeți la prețul stabilit pentru a atinge acest prag.

conținut

Metodă

Partea 1creați tabelul pentru costurile variabile
Partea 2creați tabelul cu costuri fixe
Partea 3creați foaia de pauză
Partea 4introduceți formulele dvs.
Partea 5determinați punctul de rupere (punctul de pauză)

Sfaturi
Avertismente

metodă

Partea 1
Creați tabelul pentru costurile variabile

Deschideți Excel și creați un registru de lucru nou, gol. Veți crea mai multe coli în acest registru de lucru pentru a vă documenta toate costurile.

Faceți clic pe butonul "+" de lângă "Fila 1" din partea de jos a ecranului. Aceasta va crea o foaie de lucru nouă, goală.

Imaginea intitulată Faceți o diagramă de eveniment în Excel Excel Pasul 3

Redenumiți noua foaie de lucru la "Costuri variabile". În această fișă veți găsi tabelul care documentează toate costurile variabile ale produselor dvs. Costuri de expediere, comision și alte costuri.

Imagine intitulată Faceți o diagramă de întrerupere în Excel Pasul 4

Creați anteturi de tabel pentru foaia nouă. Pentru un tabel simplu, introduceți "Descriere" în A1 și "Cantitate" în B1.

Imaginea intitulată Faceți o diagramă de eveniment în Excel Excel Pasul 5

Introduceți numele costurilor variabile ale companiei dvs. în coloana A. Sub titlul "Descriere", introduceți tipurile de costuri variabile care există pentru produsul dvs.

Imagine intitulată Faceți o diagramă de întrerupere în Excel Pasul 6

Lăsați coloana B ("Cantitate") goală momentan. Veți completa costurile reale mai târziu.

Imagine intitulată Faceți o diagramă Even Break Even in Excel Pasul 7

Creați un tabel din datele introduse. Când convertiți datele într-o foaie de calcul, devine ușor să le puneți în formule mai târziu:

Selectați toate datele, inclusiv rubricile și rândul seturilor goale, făcând clic cu mouse-ul și glisându-le peste toate celulele.
Faceți clic pe butonul "Format ca tabel". Puteți găsi acest lucru în fila "Start". Dacă aveți Excel pentru Mac, faceți clic pe tabelul "Tables", apoi pe butonul "New" și apoi selectați "Insert table with headings".
Puneți un marcaj de lângă pictograma "Tabelul meu are antete". Aceasta păstrează legenda în prima linie ca titluri.
Faceți clic pe câmpul "Nume tabel" din colțul din dreapta sus și numiți-l "Costuri variabile"

Partea 2
Creați tabelul cu costuri fixe

Faceți clic pe butonul "+" de lângă "Costuri variabile" din partea de jos a ecranului. Aceasta va crea o altă foaie goală.

Imaginea intitulată Faceți o diagramă de eveniment în Excel Excel Pasul 9

Redenumiți noua foaie de lucru la "costuri fixe". În această fișă, veți găsi tabelul care documentează toate costurile fixe ale produselor dvs. Cheltuieli de închiriere, de asigurare și alte costuri care nu se modifică.

Imagine intitulată Faceți o diagramă de întrerupere în Excel Pasul 10

Creați titlurile tabelului. Creați subtitrarea "Descriere" în celula A1 ca în foaia de lucru "Costuri variabile" și "Cantitate" în celula B1.

Imaginea intitulată Faceți o diagramă de renunțare la eveniment în Excel Pasul 11

Introduceți numele costurilor fixe ale companiei dvs. în coloana A. Completați prima coloană cu descrieri ale costurilor fixe. De exemplu, "chiria".

Imaginea intitulată Faceți o diagramă Even Break Even in Excel Pasul 12

Lăsați coloana B ("Cantitate") goală momentan. Veți completa aceste costuri atunci când ați creat restul mesei.

Imaginea intitulată Faceți o diagramă de întrerupere în Excel Pasul 13

Creați un tabel din datele pe care le-ați introdus. Alegeți tot ce ați creat pe această pagină, inclusiv titlurile:

În fila "Start" faceți clic pe butonul "Format ca tabel".
Verificați "Masa mea are anteturi" pentru a transforma rândul 1 în anteturi pentru masă.
Faceți clic pe câmpul "Nume tabel" și denumiți tabelul "Costuri fixe".

Partea 3
Creați foaia de pauză

Fișa de nume 1 în "Break Even" și selectați-o. În această foaie, puneți diagrama pentru pauză chiar sub. Nu trebuie să îl redenumiți Break Even, dar vă ușurează navigarea în registrul de lucru.

Imaginea intitulată Face o diagramă Even Break Even in Excel Pasul 15

Creați structura pentru foaia de corespondență. Pentru acest exemplu, creați foaia dvs. cu următorul aspect:

A1: Vânzări: Aceasta este eticheta pentru zona de vânzare din această foaie de lucru.
B2: Prețul unității: acesta este prețul pe care îl calculați pe unitatea vândută.
B3: Unități de vânzare: acesta este numărul de unități pe care le-ați vândut pentru o anumită perioadă la prețul specificat.
A4: Cost: Aceasta este eticheta pentru zona de cost din această foaie de lucru.
B5: Cost variabil: Acesta este costul produsului dvs. asupra căruia dețineți controlul (transport, comisioane etc.)
B6: Costuri fixe: Acesta este costul produsului pe care nu-l aveți control (chirie pentru clădiri, asigurări etc.)
A7: Vânzări: Aceasta este suma pe care o va aduce vânzările produsului înainte de luarea în considerare a costurilor.
B8: Marjă pe unitate: Aceasta este suma pe care o câștigi pe unitate după ce costurile au fost luate în considerare.
B9: Marja brută: Aceasta este suma totală pe care o câștigați cu toate unitățile vândute, după ce au fost contabilizate costurile.
A10: BEP: acesta este titlul secțiunii punct parțială a foii de lucru.
B11: Unități: Acesta este numărul de unități pe care trebuie să le vindeți pentru a vă acoperi cheltuielile.

Imagine intitulată Faceți o diagramă de întrerupere a evenimentului în Excel Pasul 16

Modificați formatul numeric pentru celulele de ieșire și de intrare. Trebuie să modificați formatul numeric pentru anumite celule astfel încât datele să fie afișate corect:

Marcați C2, C5, C6, C8 și C9. Dați clic pe meniul derulant "Plăți" din fila "Începeți" și selectați "Monedă".
Marca C3 și C11. Faceți clic pe meniul derulant și selectați "Mai multe formate de număr". Mergeți la "Număr" și apoi setați "Locurile zecimale" la "0".

Imaginea intitulată Faceți o diagramă de eveniment în Excel Excel Pasul 17

Creați zone pentru a fi utilizate în formule. Selectați următoarele zone pentru a face ca formula să funcționeze. Aceasta creează variabile pe care le puteți introduce în formule, astfel încât să puteți ușor să le referiți și să le actualizați cu ușurință.

Alegeți B2: C3 și apoi faceți clic pe fila "Formule". Faceți clic pe "Creați din selecție" și apoi pe "OK".
Selectați B5: C6 și apoi faceți clic pe fila "Formule". Faceți clic pe "Creați din selecție" și apoi pe "OK".
Selectați B8: C9 și apoi faceți clic pe fila "Formule". Faceți clic pe "Creați din selecție" și apoi pe "OK".
Alegeți B11: C11 și apoi faceți clic pe fila "Formule". Faceți clic pe "Creați din selecție" și apoi pe "OK".

Partea 4
Introduceți formulele dvs.

Imaginea intitulată Faceți o diagramă de întrerupere în Excel Pasul 18

Introduceți formula pentru costuri variabile. Aceasta calculează costurile variabile totale pentru numărul de unități vândute. Faceți clic pe C5 și introduceți următoarea formulă:

= SUM (Cost variabil) * Unități vândute

Imaginea intitulată Faceți o diagramă de întrerupere în Excel Pasul 19

Introduceți formula pentru costurile fixe. Acesta calculează costurile fixe totale ale produsului dvs. Faceți clic pe C6 și introduceți următoarea formulă:

= SUM (fix)

Imaginea intitulată Faceți o diagramă de întrerupere în Excel Pasul 20

Introduceți formula pentru marja pe unitate. Aceasta calculează marja pe care o câștigi după luarea în considerare a costurilor variabile. Faceți clic pe C8 și introduceți următoarea formulă:

= Prețul pe unitate SUM (cost variabil)

Imagine intitulată Faceți o diagramă de eveniment în Excel Excel Pasul 21

Introduceți formula pentru marja brută. Aceasta determină suma totală pe care o câștigați pentru toate unitățile vândute după luarea în considerare a costurilor variabile. Faceți clic pe C9 și introduceți următoarea formulă:

= Marja pe unitate * unități vândute

Imaginea intitulată Faceți o diagramă Even Break Even in Excel Pasul 22

Introduceți formula BEP. Aceasta compară costurile fixe cu marjele dvs. și vă spune câte unități trebuie să vindeți până când ajungeți la pauză chiar. Faceți clic pe C11 și introduceți următoarea formulă:

= IFERROR (costuri / marjă fixă pe unitate, 0)

Partea 5
Determinați punctul de rupere (punctul de pauză)

Introduceți costurile variabile ale companiei dvs. Reveniți la tabelul Costuri variabile și completați toate costurile pentru produsul dvs. Cu cât esti mai aproape, cu atât este mai exact calculul tău de compensare.

Toate costurile din tabelul costurilor variabile ar trebui să fie per unitate vândută.

Introduceți costurile fixe ale companiei dvs. Introduceți-le în tabelul cu costuri fixe. Acestea sunt costurile de funcționare a afacerii dvs. și ar trebui să se refere la aceeași perioadă de timp (de exemplu, costuri lunare).

Imaginea intitulată Faceți o diagramă de eveniment în Excel Excel Pasul 25

Introduceți un preț pe unitate. Introduceți o estimare inițială pe unitate în foaia de pauză. Puteți ajusta acest lucru atunci când efectuați calculele.

Imaginea intitulată Face o diagramă de renunțare la eveniment în Excel Pasul 26

Introduceți numărul de unități pe care doriți să le vindeți. Luați în considerare aceeași perioadă ca și pentru costurile fixe. Dacă costurile fixe z. De exemplu, dacă includeți chiria lunară și asigurarea lunară, unitățile vândute reprezintă numărul de unități pe care le vindeți în aceeași perioadă de timp.

Citiți ieșirea la "unități". Această celulă (C11) afișează numărul de unități pe care trebuie să le vindeți în fiecare perioadă pentru a ajunge la un eveniment de rentabilitate. Acest număr se modifică în funcție de prețul pe unitate, costul variabil și tabelele cu costuri fixe.

Ajustați prețul și costul. Modificarea prețului pe unitate modifică numărul de unități de care aveți nevoie pentru a atinge pragul. Modificați prețul și vedeți ce se întâmplă cu valoarea evenimentului dvs. de pauză.

Sfaturi

Dacă creșteți prețul de vânzare, puteți ajunge la punctul de rentabilitate cu un volum redus de vânzări, dar poate și să reduceți numărul total de vânzări pe care le faceți.