Applicare la break-even analysis con Excel - Un modello per dimensionare la produzione, in base ad una stima che bilancia costi e ricavi e fornisce valori potenzialmente critici.

Applicare la break-even analysis con Excel
Un modello per dimensionare la produzione, in base ad una stima che bilancia costi e ricavi e fornisce valori potenzialmente critici.

Il break-even point è un metodo di calcolo che consente di determinare le quantità di prodotto in corrispondenza delle quali i costi sostenuti nella produzione si eguagliano ai ricavi di vendita. Valori di produzione (e vendite) superiori al “punto di rottura” consentono all’impresa di ottenere degli utili, mentre valori inferiori indicano che l’impresa produce sostanzialmente in perdita. Pur non privo di qualche limite (in una impresa che produce diversi prodotti, è necessario ripartire i Costi Fissi tra i diversi prodotti, cosa che non piace agli economisti sostenitori del “direct costing”!) il calcolo del “punto di equilibrio” è uno strumento utile per avere una indicazione, seppur “approssimativa”, circa le quantità da produrre e vendere per evitare che l’impresa operi in perdita. In questo tutorial proponiamo un modello che consente di calcolare tale valore e di simulare, al tempo stesso, scenari operativi attraverso l’indicazione di valori previsionali.
=>Scarica il modello
Come utilizzare il modello

Il modello è composto da quattro tabelle di tre colonne, disposte in sequenza; la colonna A è di tipo descrittivo, la colonna B è destinata ad accogliere i dati reali mentre, la colonna C dei dati previsionali.



=> Applicativi, software e piattaforme web per imprese online Nell’intervallo B4:B8 della prima tabella, vanno indicati i dati che consentono il “calcolo del punto di equilibrio” (ovvero, il prezzo unitario di vendita, il costo variabile unitario, i costi fissi complessivi) e altri due dati (ovvero le quantità prodotte e le quantità massime producibili) che consentono di tenere sotto controllo il grado di sfruttamento della capacità produttiva (quest’ultimo dato dovrà, in sede di simulazione, non essere superiore al 100%). Le quantità di equilibrio vengono calcolate nella cella B11 (relativa alla seconda tabella); la cella B12 visualizza, invece, il grado di sfruttamento della capacità produttiva in corrispondenza del punto di equilibrio. Nelle celle dell’intervallo C4:C9 si possono indicare dei valori previsionali che consentono il calcolo del “nuovo” punto di equilibrio nella cella C11. Nell’esempio in figura, un incremento dei costi variabili unitari (da 15 a 16) determina un nuovo punto di equilibrio (cella C11) pari a 30.000 e un aumento del grado di sfruttamento della capacità produttiva che raggiunge ora il 50% (C12).



La terza tabella del modello riproduce, a partire dai valori indicati nella prima tabella, lasituazione economica dell’azienda evidenziando il risultato operativo corrente nella cella B21 e quello previsionale nella cella C21. Come si può osservare in figura, l’incremento dei costi variabili unitari ha determinato un abbattimento del Margine lordo di contribuzione (cella C19) di 40.000 che, a parità di costi fissi, si riflette sul risultato operativo di gestione (cella C21). Nell’ultima tabella del modello vengono calcolate le quantità che consentono di conseguire un determinato risultato operativo (da indicare nelle celle B24 e C24) e viene visualizzato, per monitorare la fattibilità delle previsioni, il grado di sfruttamento della capacità produttiva a seguito delle scelte operate. Dalla figura si osserva che per incrementare il risultato operativo di 20.000 (e quindi passare dall’attuale 120.000 a 140.000) è necessario, in base ai dati correnti, produrre 2.222 unità di prodotto in più (le quantità passano da 40.000 a 42.222, il valore della cella B25). I dati della colonna C indicano invece che, per ottenere un risultato operativo pari a quello attuale (inserito nella cella C24), supponendo soltanto la crescita dei costi variabili a 16 (inseriti nella cella C5), sarà necessario produrre 45.000 unità di prodotto (calcolati nella cella C25). Nell’esempio riproposto in figura abbiamo ipotizzato, per semplicità di esposizione, soltanto la variazione dei costi variabili unitari ma è possibile modificare (nella colonna C della prima tabella) anche più parametri simultaneamente (ad esempio, si può ipotizzare un incremento del prezzo di vendita, una riduzione delle quantità vendute, un incremento dei costi fissi a seguito dell’acquisto di nuovi impianti, etc.).
Come costruire il modello

Il break-even point si determina a partire dall’equazione matematica Ricavi=CostiTotali; i Ricavi sono dati dal prezzo (p) per le quantità prodotte/vendute (q) mentre i CostiTotali sono dati dalla sommatoria tra i CostiVariabili (CV) e i CostiFissi (CF); poiché iCostiVariabili si calcolano, a loro volta, moltiplicando le quantità (q) per i costi variabili unitari (cv), l’equazione di partenza può essere scritta in questo modo:

p*q = cv*q + CF

Risolvendola per q si ottiene

q = CF / (p – cv)

ovvero, le quantità di equilibrio si calcolano dividendo i costi fissi (CF) per la differenza tra il prezzo unitario di vendita (p) e il costo variabile unitario (cv).
=> Reporting aziendale e break even analysis

Nella cella B11, pertanto, è stata utilizzata tale formula all’interno di una funzione SE (che, a sua volta, utilizza l’operatore logico ‘O’) per evitare la comparsa del messaggio di errore prima della compilazione del modello:

=SE(O(B4=”";B5=”");0;B6/(B4-B5))

La funzione SE consente di calcolare le quantità di equilibrio (B6/B4-B5) soltanto se le celle B4 e B5 contengono un valore, altrimenti restituisce uno zero (0). Il grado di sfruttamento della capacità produttiva si calcola dividendo le quantità prodotte per le quantità massime producibili con gli impianti a pieno regime e moltiplicando tale valore per 100. Pertanto, nella cella B12 è stata utilizzata la formula

=SE(B8=”";0;B11/B8)

La formula restituisce la divisione tra le quantità di equilibrio (risultato ottenuto nella cella B11) e le quantità massime producibili (valore inserito nella cella B8) a condizione che la cella B8 contenga un valore. Da notare che il rapporto tra i due valori non è stato moltiplicato per 100 in quanto alla cella è stato assegnato un formato percentuale. Per determinare la situazione economica nella terza tabella sono state utilizzate semplici formule. La cella B15 richiama il valore delle quantità prodotte inserito nella cella B7 (=B7) . Per determinare il grado di sfruttamento della capacità produttiva “reale” si è utilizzato nella cella B16 la formula

=SE(B8=”";0;B7/B8)

Il Fatturato si calcola moltiplicando il prezzo unitario di vendita (B4) per le quantità prodotte/vendute (B7); pertanto nella cella B17 si è utilizzata la formula =B4*B7.

I costi variabili complessivi sono stati calcolati nella cella B19 con la formula =B5*B7, ovvero moltiplicando il costo variabile unitario per le quantità. Il margine lordo di contribuzione è dato dalla differenza tra i ricavi e i costi variabili totali; nella cella B19 è stata, pertanto, utilizzata la formula =B17-B18. Nella cella B20 vengono richiamati semplicemente i costi fissi indicati nella cella B6 (attraverso la formula =B6) mentre il risultato operativo è stato calcolato per differenza tra il margine di contribuzione lordo e i costi fissi (=B19-B20). Per determinare, nella quarta tabella, le quantità da produrre al variare del risultato operativo (valore da inserire nella cella B24) è necessario partire dall’equazione matematica del reddito operativo (RO); quest’ultima è data sottraendo ai Ricavi di vendite (p*q) i CostiVariabiliTotali (cv*q) e i CostiFissi (CF), ovvero:

RO = p*q – q*cv – CF

Risolvendo questa equazione per q si ottiene, dopo pochi e semplici passaggi algebrici:

q = (RO + CF) / (p – cv)

Pertanto, le quantità da produrre per conseguire un determinato risultato operativo sono date sommando il Reddito operativo (cella B24) e i Costi Fissi (cella B6) e dividendo il risultato per la differenza tra il Prezzo unitario di vendita (cella B4) e i Costi variabili unitari (B5).

In B25, pertanto, è stata utilizzata la formula:

=SE(O(B4=”";B5=”");0;(B24+B6)/(B4-B5))

=SE(B8=”";0;B25/B8) Anche in questo caso, si è ricorso alla funzione SE per evitare la comparsa del messaggio di errore a “prospetto vuoto”. Nella cella B26 è stato determinato il gradi di sfruttamento della capacità produttiva con riferimento alle quantità indicate nella cella B25; dopo l’inserimento della formula:

la cella è stata formattata con lo stile percentuale. Le formule contenute nella colonna C sono simili nella struttura a quelle esaminate ma fanno riferimento ai dati “simulati” inseriti nell’intervallo C4:C8; pertanto, sono state create semplicemente trascinando verso destra le formule contenute nelle celle della colonna B.

di Corrado Del Buono

fonte: http://www.pmi.it/tecnologia/software-e-web/tutorial/2024/applicare-la-break-even-analysis-con-excel.html