Come fare un’analisi di sensibilità in Excel

Scopri come fare un’analisi di sensibilità in Excel con tabelle a una e due variabili e individuare le ipotesi che orientano le decisioni finanziarie.

Matrice Excel di sensibilità a prezzo e volume
In sintesi
  • Una tabella a una variabile isola il volume. Una a due variabili confronta prezzo e volume insieme.
  • Controlla il centro e un angolo della tabella rispetto al modello.
  • Nell’esempio, a 100 dollari il pareggio si raggiunge con 875 unità.

Se il budget prevede 5.000 dollari di utile operativo, quanto margine rimane per ridurre il prezzo o restare sotto l’obiettivo di vendita? L’analisi di sensibilità ricalcola lo stesso modello con ipotesi diverse. Questa guida mostra come creare due tipi di tabella in Excel, verificare i risultati rispetto al caso base e trovare il volume di pareggio.

Che cos’è l’analisi di sensibilità?

L’analisi di sensibilità misura quanto cambia il risultato di un modello al variare di determinati input. Parti da un caso base, modifica gli input da esaminare e mantieni costanti le altre ipotesi. Le differenze mostrano l’esposizione del risultato alle variazioni testate.

Un budget potrebbe prevedere un prezzo di 100 dollari e vendite di 1.000 unità. Prezzo e volume sono gli input e l’utile operativo è il risultato. Il test a una variabile mostra cosa succede al variare del volume con il prezzo fisso a 100 dollari. Quello a due variabili confronta le combinazioni di prezzo e volume.

Lo stesso metodo si applica ad altri modelli. Una previsione di liquidità può verificare l’effetto di incassi più lenti sulla cassa a fine mese. Un modello di valutazione può stimare l’effetto del tasso di attualizzazione sul valore attuale netto. L’intervallo degli input deve rispecchiare la decisione da prendere.

Analisi di sensibilità e analisi di scenario

L’analisi di sensibilità misura la variazione del risultato lungo un intervallo di prezzi o volumi. L’analisi di scenario valuta ipotesi che possono verificarsi insieme. Una promozione può combinare prezzo più basso, vendite attese più alte e costi di marketing aggiuntivi. La matrice aiuta a isolare gli effetti e lo scenario aiuta a valutare il piano nel suo complesso.

A trimestre concluso, l’analisi degli scostamenti aiuta a capire perché l’utile effettivo differisce dal budget. Se conosci il risultato desiderato, la Ricerca obiettivo di Excel risale a un singolo input. Per esempio, calcola quante unità servono per ottenere un utile operativo di zero dollari a prezzo fisso.

Come scegliere input e intervalli per l’analisi di sensibilità

Parti dalla decisione e dal risultato che potrebbe cambiarla. Per approvare uno sconto, considera l’utile operativo e il volume di pareggio. Per la liquidità, può essere più importante il saldo minimo di cassa.

Registra il valore di base, gli estremi del test, la fonte e la persona che può confermarli. Per il prezzo puoi usare preventivi recenti o la politica di sconti approvata. Per il volume, considera periodi comparabili, ordini acquisiti o previsioni. Allinea periodi e unità di misura. Per i tassi, indica se la variazione è relativa o in punti percentuali. Aggiungere due punti al 10% dà il 12%. Aumentare il 10% del 2% dà il 10,2%.

L’esempio testa prezzi tra 95 e 105 dollari e vendite trimestrali tra 800 e 1.200 unità. I costi seguono le ipotesi di base. In un budget reale verifica se l’intervallo supera un limite di capacità o cambia la fascia di prezzo del fornitore, con effetti sui costi.

Come eseguire l’analisi di sensibilità in Excel

Usa un solo foglio in Excel per desktop. Inserisci le ipotesi e la formula dell’utile per creare il modello di base.

Ipotesi del modello di base e riferimenti alle celle di Excel
CellaVoce del modelloValore o formula
B2Prezzo unitario di vendita100
B3Unità vendute nel trimestre1000
B4Costo variabile unitario60
B5Costi fissi trimestrali35000
B6Utile operativo trimestrale=(B2-B4)*B3-B5

Il valore di base in B6 dovrebbe essere 5.000 dollari. Usa la cella come riferimento del risultato in entrambe le tabelle.

Creare una tabella dati a una variabile

Inizia dal volume mantenendo il prezzo a 100 dollari per unità.

  1. In =$B$6 inserisci E3. In 800 digita 1000, 1200 e D4:D6, un valore per riga.
  2. Seleziona D3:E6 e apri Dati → Analisi di simulazione → Tabella dati.
  3. Imposta la Cella di input per colonna su $B$3, lascia vuota la Cella di input per riga e seleziona OK.

Excel riempie E4:E6 con −3.000 dollari, 5.000 dollari e 13.000 dollari. Il risultato per 1.000 unità deve corrispondere ai 5.000 dollari del caso base. Ogni unità aggiuntiva contribuisce per 40 dollari alla copertura dei costi fissi e all’utile.

Creare una tabella dati a due variabili

Inserisci i prezzi nella riga superiore e i volumi nella colonna di sinistra.

  1. In =$B$6 inserisci H3. In 95 digita 100, 105 e I3:K3. In 800 digita 1000, 1200 e H4:H6.
  2. Seleziona H3:K6 e apri Dati → Analisi di simulazione → Tabella dati.
  3. Imposta la Cella di input per riga su $B$2 per i prezzi in alto. Imposta la Cella di input per colonna su $B$3 per i volumi a sinistra. Seleziona OK.

Utile operativo trimestrale per prezzo e volume di vendita.

Utile operativo trimestrale per prezzo e volume di vendita
Unità vendute nel trimestre95 $ per unità100 $ per unità105 $ per unità
800−$7,000−$3,000$1,000
1,000$0$5,000$10,000
1,200$7,000$13,000$19,000

Confronta il valore centrale con B6 e calcola a mano un angolo. A 95 dollari e 800 unità, il margine unitario è 35 dollari. Si ottengono 28.000 dollari prima dei costi fissi e −7.000 dollari dopo. Confronta poi la colonna da 100 dollari con la tabella a una variabile. Le verifiche aiutano a trovare riferimenti errati, celle invertite o risultati non aggiornati.

Come leggere una tabella di sensibilità

Inizia dai valori più rilevanti. Con 1.000 unità, portare il prezzo da 100 a 95 dollari azzera i 5.000 dollari del caso base. Con il prezzo a 100 dollari, l’utile si azzera a 875 unità, cioè 35.000 dollari di costi fissi divisi per 40 dollari di margine unitario. A 95 dollari, il margine scende a 35 dollari e il volume di pareggio sale a 1.000 unità.

La tabella mostra il risultato di ogni combinazione. Se il prezzo più basso dovrebbe aumentare la domanda, verifica il fondamento della stima e la capacità di gestire il volume aggiuntivo. Con queste ipotesi, a 95 dollari servono almeno 1.143 unità per mantenere 5.000 dollari di utile. Nello scenario promozionale puoi includere eventuali costi aggiuntivi e una stima delle vendite basata sui risultati passati.

Una nota alla direzione potrebbe indicare quanto segue.

Con un prezzo di 95 dollari e volumi fermi a 1.000 unità, l’utile trimestrale scende da 5.000 a 0 dollari. Con le ipotesi di costo attuali servono almeno 1.143 unità per mantenere l’utile originario. Prima di approvare lo sconto, controllare se le promozioni passate giustificano quel volume e se gli ordini iniziali sono in linea.

Conserva con la nota la versione del file e la fonte degli intervalli. In Kuse puoi trasformare una tabella di sensibilità verificata in una nota decisionale a partire dalla matrice, dalle ipotesi e dai documenti di supporto. Includi il caso base, la soglia decisionale e le evidenze necessarie. Il responsabile del modello verifica numeri e testo in Excel e riutilizza il formato per la previsione seguente.

Domande frequenti

Perché la tabella di sensibilità di Excel mostra lo stesso valore in ogni cella?

Modifica a mano un input, verifica il risultato collegato, poi ripristina il valore. Se il modello cambia e la tabella resta uguale, controlla il riferimento del risultato e le celle di input. Se il ricalcolo automatico esclude le tabelle, selezionane le formule e ricalcolale.

Perché il caso base della tabella differisce da quello del modello?

Controlla che le intestazioni usino i valori esatti. Un’intestazione arrotondata può mostrare lo stesso numero ma passare un input diverso a Excel. Verifica il riferimento del risultato, ricalcola la tabella e controlla le altre ipotesi.

Come verifico quali celle di input usa una tabella dati esistente?

Seleziona una cella calcolata e guarda la barra della formula. In Excel per desktop, la formula della tabella può mostrare i riferimenti di riga e colonna, nell’ordine. Segui i riferimenti e verifica che alimentino il risultato.

Quando uso una tabella a due variabili o la Ricerca obiettivo?

Usa la tabella per confrontare combinazioni, come l’utile dato prezzo e volume. Usa la Ricerca obiettivo per risalire dal risultato desiderato a un input, ad esempio il volume per ottenere un utile di zero dollari a prezzo fisso.

Come scelgo input realistici per l’analisi di sensibilità?

Parti dal caso base corrente o approvato e usa dati storici, clausole contrattuali, preventivi o previsioni per definire gli estremi. Includi i valori vicini alle soglie e contrassegna quelli esplorativi.

Come trovo il punto di pareggio con una tabella di sensibilità?

Cerca una cella con utile zero o due celle adiacenti dove l’utile cambia segno. Se il pareggio cade tra due valori, usa la formula del modello o la Ricerca obiettivo con un input. Arrotonda per eccesso il volume se necessario e verifica l’utile nel modello.