Costruire un grafico Break-even point (BEP) con Excel

La rappresentazione grafica di dati aziendali è una operazione abbastanza semplice in quanto si tratta, spesso, di dati storici (ad esempio, andamento delle vendite) o di dati che rappresentano una percentuale o quota di un totale (ad esempio, composizione del capitale sociale) per i quali si utilizzano, rispettivamente, grafici ad istogramma (o a barre) e grafici a torta (o ad anelli). Ci sono però dei dati che per essere rappresentati richiedono l’utilizzo di grafici più di tipo matematico, come ad esempio grafici a DISPERSIONE XY, per i quali occorre conoscere delle procedure operative specifiche per la loro realizzazione. In questo editoriale esaminiamo le procedure necessarie per creare un grafico che rappresenti il “Punto di equilibrio” (B.E.P.), grafico che richiede particolari accorgimenti per la sua realizzazione.

Cosa indica il Break-even point

Il Break-even point indica la quantità che bisogna produrre (e vendere) di un certo prodotto per coprire l’insieme dei costi (fissi e variabili) sostenuti: detto in altri termini, è la quantità che permette un pareggio tra RICAVI e COSTI, e pertanto, è un indicatore molto utile in quanto permette di conoscere da quale produzione (e vendita) si realizzano profitti per l’impresa.

I dati necessari per costruire questo grafico sono, pertanto, i COSTI FISSI (ovvero quei costi che non variano al variare delle quantità come, ad esempio, i costi per gli impianti industriali), il COSTO VARIABILE UNITARIO (dove per Costo Variabile si intendono quei costi che variano al variare delle quantità come, ad esempio, il costo delle materie prime utilizzare nella produzione di quel prodotto) e il PREZZO DI VENDITA. Possiamo iniziare, quindi, a trascrivere questi dati su di un foglio di Excel come indicato in figura.

Per calcolare il BEP è sufficiente dividere i COSTI FISSI per il PREZZO – COSTO VARIABILE. Questa formula deriva risolvendo per Q (quantità) l’equazione:

RICAVI = COSTI TOTALI

• dove RICAVI = P*Q
• COSTI TOTALI = COSTI FISSI + COSTO VARIABILE UNITARIO *Q.

Pertanto possiamo aggiungere alla riga 5 del prospetto la formula del BEP utilizzando i riferimenti alle celle della colonna B e, poiché le quantità sono sempre numeri interi (non si può produrre mezzo prodotto!), inseriamo quest’ultima all’interno della funzione ARROTONDA.PER.ECC.

Nella cella B5 scriveremo, quindi la formula:

= ARROTONDA.PER.ECC(B2/(B4-B3);0)

Il risultato che si ottiene dall’esempio riportato in figura è 16.667: pertanto, per coprire l’insieme dei costi (COSTI TOTALI) bisogna produrre e vendere almeno 16.667 prodotti.

Come rappresentare graficamente (e correttamente) il BEP

Vediamo ora come rappresentare graficamente il “Punto di Equilibrio” (BEP).

Per rappresentare graficamente il BEP abbiamo bisogno di due rette, quella dei RICAVI e quella dei COSTI TOTALI; inoltre servono almeno tre punti (in matematica ne basterebbero due!) ovvero il valore Y in corrispondenza di QUANTITA’ (X) pari a zero (0), pari al BEP e delle quantità superiori al BEP (che servono per mostrare graficamente l’intersezione tra le due rette).

Possiamo quindi creare una tabella che sarà l’origine dei dati per il grafico, a partire dalla colonna D nella quale inseriamo le quantità (variabile X del grafico futuro).

In D2 (D1 la lasciamo per l’intestazione) digitiamo uno zero (0), in D3 richiamiamo le quantità di equilibrio (=B5) e in D4 inseriamo la formula =B5+0,5*B5

Quest’ultima formula consente di calcolare delle quantità maggiorate della metà rispetto a quelle di equilibrio; tale accorgimento calcola un valore “relativo” utile per rappresentare correttamente il grafico anche se si modificano i dati variabili. Per intenderci, se si utilizza nella formula una quantità “assoluta” (ad esempio, =B5+100), quest’ultima potrebbe non essere “adeguata” a rappresentare un grafico dal BEP molto alto (le due rette terminerebbero subito dopo la loro intersezione!).

Nella colonna E inseriamo i valori dei RICAVI in corrispondenza delle quantità indicate nella colonna D. Pertanto, in E2 sarà sufficiente moltiplicare il Prezzo di vendita (posto in D4) per le quantità poste in D2, (ovvero la formula =D2*$B$4) e, successivamente, trascinarla con il quadratino di riempimento verso il basso.

Nella colonna F inseriamo i Costi totali per ciascuna delle quantità presenti nella colonna D. Pertanto, in F2 possiamo scrivere la formula: =$B$2+D2*$B$3 che consente di sommare i COSTI FISSI (cella B2) al risultato che si ottiene dalla moltiplicazione delle quantità (cella D2) per i COSTI VARIABILI UNITARI (cella B3). Utilizzando i riferimenti assoluti, possiamo copiare verso il basso la formula fino alla cella F4.

Abbiamo ora tutto l’occorrente per realizzare il grafico. Poiché la Creazione automatica di grafici della tipologia DISPERSIONE XY non sempre va a buon fine, proponiamo di realizzare il grafico BEP selezionando le serie di dati manualmente (un metodo per imparare a realizzare grafici, anche complessi). Posizioniamoci in una cella vuota del foglio di calcolo ed utilizziamo, dal menu DISPERSIONE posto nel gruppo GRAFICI della BARRA INSERISCI, il grafico DISPERSIONE CON LINEE RETTE E INDICATORI.

Successivamente, utilizziamo il pulsante SELEZIONA DATI posto sulla BARRA CONTESTUALE PROGETTAZIONE degli STRUMENTI GRAFICO e facciamo clic sul pulsante AGGIUNGI della finestra SELEZIONA ORIGINE DATI che appare a video.

Aiutandoci con i pulsanti di collegamento con il foglio di calcolo, posti sul lato destro delle Caselle di testo della finestra MODIFICA SERIE, assegniamo alla casella NOME SERIE la cella E1 del foglio di calcolo, alla casella VALORI X SERIE l’intervallo D2:D4 (le quantità di prodotto) e alla casella VALORI Y SERIE l’intervallo della colonna E relativo ai RICAVI.

Ripetiamo la procedura inserendo una nuova serie dati (COSTI TOTALI). Dopo aver inserito le due serie di dati apportiamo ulteriori modifiche per rendere più gradevole il lavoro aggiungendo, ad esempio, un titolo agli assi e al grafico stesso.

Dopo aver creato il grafico possiamo simulare altri Punti di Equilibrio modificando i dati di origine: come si potrà osservare, avendo utilizzato un valore X “relativo” per il terzo punto del grafico, quest’ultimo risulterà sempre ben costruito.