Tutorial sull’analisi di sensibilità in Excel: panoramica, tipi e best practice

Quando progetti un modello in un foglio di calcolo, potresti voler capire come può cambiare un risultato modificando il valore di una delle variabili coinvolte. Questo processo può essere eseguito in Excel effettuando un’analisi di sensibilità

Saper condurre un’analisi di sensibilità è fondamentale se lavori con i dati in ambito di modellazione finanziaria o nei processi decisionali. 

In questo articolo esploreremo l’analisi di sensibilità in profondità. Tratteremo le basi di questa utile tecnica all’interno della famiglia delle analisi “what-if” e come condurre e interpretare i risultati di analisi di sensibilità di base e avanzate in Excel. Infine, parleremo del ruolo dell’AI e del machine learning nel rendere più potenti le analisi di sensibilità, oltre a best practice ed errori comuni. 

Se vuoi metterti subito alla prova, dai un’occhiata al nostro corso Financial Analysis in Excel, che approfondisce l’analisi what-if. 

Che cos’è l’analisi di sensibilità in Excel?

L’approccio più comune nell’analisi di sensibilità è esaminare come i cambiamenti di una variabile alla volta influenzano il risultato finale mantenendo costanti tutti gli altri fattori. Questo si ripete per tutte le variabili considerate. Aiuta a identificare i driver più influenti in un modello, consentendo alle aziende di concentrarsi sui fattori chiave che incidono sulla loro performance. 

L’analisi di sensibilità aiuta le aziende a dare priorità a rischi e opportunità individuando i fattori che hanno il maggiore impatto sulla performance. Per sua natura, è comune in finanza, business e project management, ad esempio per calcolare come i tassi d’interesse possono variare nel tempo o per comprendere diversi trend di crescita. 

Inoltre, l’analisi di sensibilità è spesso combinata con la scenario analysis, che in genere analizza la combinazione di variabili contemporaneamente. Queste due analisi what-if studiano come una variabile dipendente reagirà a determinati input. Tuttavia, l’analisi di scenario è specifica a un certo "scenario", mentre l’analisi di sensibilità è più aperta perché fornisce un intervallo di input e valori. 

L’obiettivo dell’analisi di sensibilità è capire come la variabile dipendente reagisce a un intervallo di valori di input, noti come variabili indipendenti.

Incorporando questa tecnica nel processo decisionale, le organizzazioni possono adeguare proattivamente le strategie e aumentare la resilienza all’incertezza. 

Per una spiegazione più dettagliata della teoria alla base dell’analisi di sensibilità, consigliamo vivamente il nostro corso Advanced Probability: Uncertainty in Data.

Spesso, l’analisi di sensibilità è presentata in una tabella di sensibilità con formattazione condizionale che evidenzia i valori dal più alto al più basso. 

Di seguito trovi un esempio della sensibilità del prezzo rispetto a domanda e offerta. Domanda e offerta sono le variabili indipendenti indicate sugli assi, e il prezzo è la variabile dipendente che riempie i valori nella tabella.

Nelle prossime sezioni lavoreremo con tabelle di sensibilità Excel simili.

Tabella di sensibilità. Fonte: DataCamp

Primi passi con l’analisi di sensibilità in Excel

Vediamo come eseguire un’analisi di sensibilità in Excel, seguendo ogni fase del processo. 

Strumenti e funzionalità in Excel

Excel è uno strumento pratico per creare modelli matematici, incluse le analisi what-if. L’analisi what-if consiste essenzialmente nel creare formule che permettono di esplorare domande, come un’analisi di sensibilità.

Le versioni più recenti di Excel includono la scheda Dati, che offre una miriade di strumenti e possibilità per lavorare con i dati. Qui trovi il pulsante Analisi di simulazione (What-if) sulla barra multifunzione. Include tre strumenti. Il pulsante Tabella dati è pensato specificamente per creare tabelle di sensibilità.

Per eseguire un’analisi di scenario, basta fare clic sul pulsante Gestione scenari.

Useremo anche Risolutore (Solver), un componente aggiuntivo che puoi attivare dalla scheda Sviluppo. 

Risolutore è usato nelle analisi what-if per trovare un valore ottimale (massimo o minimo) per una formula in una cella — chiamata cella obiettivo — soggetto a vincoli, o limiti, sui valori di altre celle formula in un foglio di lavoro.

Prima di abilitare Risolutore, devi accedere alla scheda Sviluppo, che non è visualizzata per impostazione predefinita. Per mostrare questa scheda:

• Vai su File > Opzioni
• Seleziona Personalizzazione barra multifunzione dal pannello di sinistra
• Nella colonna di destra sotto "Schede principali", seleziona la casella Sviluppo

Eseguire un’analisi di sensibilità a singola variabile

Ora che abbiamo gli strumenti pronti, iniziamo creando una semplice analisi di sensibilità a una sola variabile in Excel.

Impostare il tuo modello

Il primo passo per eseguire un’analisi di sensibilità in Excel è identificare gli input e gli output su cui si baserà il tuo modello. 

In questo tutorial useremo l’analisi di sensibilità per studiare come varia il profitto netto di un’azienda che vende chitarre se cambiamo il valore di alcune variabili di input, come il numero di chitarre vendute, il prezzo della chitarra o i costi di produzione.

Di seguito trovi le tabelle con le variabili di input e output. 

Quando si usano tabelle dati per l’analisi di sensibilità, è importante collegare le celle di output alle variabili di input in modo che Excel possa comprendere la relazione tra le variabili del modello. Questo processo si effettua tramite formule. Nel nostro esempio:

• Ricavi si calcolano moltiplicando il prezzo per unità per le unità vendute (=B2*B4)
• Costo del venduto si calcola moltiplicando il costo di produzione per unità per le unità vendute (=B3*B4)
• Utile si calcola sottraendo ai ricavi il costo del venduto (=B7-B8).

Creare una tabella dati a una variabile

Potresti voler sapere come cambia l’utile se aumentiamo o diminuiamo il prezzo per unità, mantenendo invariati il costo di produzione e il numero di unità vendute. Quando analizziamo come cambia una variabile di output al variare del valore di una variabile di input, abbiamo bisogno di una cosiddetta tabella dati a una variabile.

La GIF seguente mostra come creare una tabella dati a una variabile in Excel. Come puoi vedere, la tabella ricalcola automaticamente l’utile per ogni unità venduta. Naturalmente, l’utile quando vendiamo 250 unità coincide con i parametri originali (cioè 50.000 €).

Potresti anche voler studiare come cambiano diverse variabili di output quando modifichiamo le vendite in unità. Anche questo è piuttosto semplice, come mostrato sotto. Nella GIF seguente calcoliamo come le vendite in unità influenzano sia gli utili sia il costo del venduto.

Condurre un’analisi di sensibilità a due variabili

Invece di analizzare come un output è influenzato da un solo input, puoi creare una tabella a due variabili per analizzare l’impatto di due variabili di input. Diciamo che vuoi sapere come cambia l’utile se modifichiamo le vendite di chitarre e il prezzo per chitarra. 

Per prima cosa, devi creare una tabella bidimensionale con gli intervalli di prezzo per unità nelle righe e le vendite in unità nelle colonne. Poi, nell’angolo in alto a sinistra della tabella, scegli la variabile che vuoi studiare. Infine, scegli i riferimenti di cella delle variabili indipendenti nella tabella delle variabili di input (cioè prezzo per unità e unità vendute).

Come puoi vedere, una volta creata la tabella, puoi cambiare facilmente la variabile di output nell’angolo in alto a sinistra con un’altra variabile e Excel ricalcolerà tutti i valori

Interpretare i risultati dell’analisi di sensibilità in Excel

Ora che hai le tue tabelle dati, l’ultimo passaggio è interpretare i risultati per generare insight su come i cambiamenti negli input impattano gli output.

Analizzare gli output delle tabelle dati

Data questa analisi di sensibilità, possiamo prendere una decisione informata sull’utile. Ecco alcuni insight:

• Se aumentiamo i prezzi delle chitarre a 350 €, potremmo eguagliare l’utile attuale di 50.000 € vendendo solo 200 chitarre.
• Se riduciamo il prezzo delle chitarre a 200 €, dovremmo vendere 500 unità per raggiungere gli utili attuali.
• Se riduciamo il numero di chitarre vendute a 150, non eguaglieremo l’utile attuale a meno che non aumentiamo il prezzo delle chitarre oltre 400 €.

L’interpretazione dell’analisi dipenderà dalle esigenze specifiche della tua azienda. Porsi domande come quante chitarre puoi produrre, qual è il livello minimo di utili necessario per mantenere l’azienda solida e cosa puoi fare per ridurre il costo di produzione della chitarra sarà cruciale per trarre insight significativi dall’analisi di sensibilità.

Usare Risolutore per l’analisi di sensibilità

Quando esegui un’analisi di sensibilità, ottieni un intervallo di possibili output in base ai cambiamenti delle variabili di input di un modello. Tuttavia, potresti voler conoscere più in dettaglio il funzionamento interno del tuo modello.

Fortunatamente, Excel include un potente strumento chiamato Risolutore, che può aiutarti in questo compito. Come detto, Risolutore è usato nelle analisi what-if per trovare un valore ottimale (massimo o minimo) per una formula soggetto a vincoli, o limiti, sui valori di altre celle formula in un foglio di lavoro. 

Questo può sembrare l’obiettivo di Trova obiettivo (Goal Seek), un altro tipo di analisi what-if che si concentra sulla regolazione di una singola variabile per ottenere un risultato desiderato. Tuttavia, Risolutore è molto più di Trova obiettivo. 

Innanzitutto, Risolutore ti consente di calcolare un risultato ottimale basato su più di una variabile e permette di includere vincoli nei modelli. 

Inoltre, Risolutore dispone anche di una funzione opzionale di sensibilità che offre indicazioni su come cambia la soluzione ottimale quando modifichi i coefficienti del modello. 

Per illustrare la potenza di Risolutore, torniamo alla nostra azienda di chitarre. Immagina che l’azienda produca due modelli di chitarra (modello A e B), ciascuno dei quali richiede una certa combinazione di mogano e cedro. Ogni modello ha un prezzo diverso. 

L’azienda vuole sapere quante unità di ciascun modello produrre per massimizzare i profitti, data la disponibilità attuale di mogano e cedro. 

Ci sono tre cose che vogliamo che Risolutore calcoli: utile, unità del modello A e unità del modello B. L’immagine sopra mostra gli input, le formule e i vincoli su cui si baserà il modello. 

Nella GIF seguente mostriamo come usare Risolutore per trovare i valori ottimali di unità per massimizzare i profitti, utilizzando i vincoli di disponibilità del legno e assicurandoci che le unità siano numeri interi. Esistono diverse strategie che Risolutore può usare per ottimizzare il modello, sia per problemi lineari sia non lineari. Nel nostro caso useremo Simple LP. 

Una volta inseriti tutti i parametri, Risolutore stima che 48 unità del modello A e 9 del modello B siano la combinazione migliore per massimizzare l’utile (17.591 €). Possiamo fare clic su Sensibilità prima di chiudere la finestra di Risolutore ed Excel creerà un nuovo foglio con un report di sensibilità.

La sensibilità ci fornisce preziosi insight per stimare come varierebbero gli utili se cambiassimo le variabili di input. Il prezzo ombra (Shadow Price) indica di quanto aumenterebbe l’utile se avessi un’unità aggiuntiva di una risorsa vincolata. In questo esempio, ogni metro quadrato aggiuntivo di mogano aumenta l’utile di 98 €, mentre il cedro lo aumenta di 103 €, evidenziando il maggiore potenziale ritorno sull’investimento del cedro

Un altro elemento importante nel report di sensibilità sono gli intervalli entro cui puoi mantenere i prezzi dei modelli per lasciare invariati i risultati del modello. 

L’utile si calcola moltiplicando le unità di ciascun modello per il relativo prezzo, riportato nelle colonne Valore finale (Final Value) e Coefficiente obiettivo (Objective Coefficient) . Gli intervalli sono riportati nelle colonne Aumento consentito (Allowable Increase) e Diminuzione consentita (Allowable Decrease). 

Ad esempio, Risolutore troverebbe lo stesso numero di unità da produrre se aumentassi il prezzo del Modello A di 1.140 € o lo diminuisci di 180 €.

Visualizzare i dati con la formattazione condizionale

Se crei tabelle di sensibilità a una o due variabili, formattare i risultati può fare la differenza per individuare rapidamente le informazioni rilevanti e migliorare il processo decisionale. La formattazione condizionale è fondamentale per creare soglie importanti e heatmap efficaci. 

Torniamo alla nostra tabella a due vie. Invece di mostrare la tabella con celle bianche, possiamo usare la formattazione condizionale per creare un’heatmap multicolore o a due colori, stabilendo una soglia personalizzata, come mostrato nella GIF.

Integrare AI e Machine Learning

Vediamo brevemente come AI e machine learning possono aiutare a migliorare la tua analisi di sensibilità.

Analisi di sensibilità potenziata dall’AI

Finora abbiamo visto come eseguire manualmente un’analisi di sensibilità. Tuttavia, con l’aumentare della complessità dei tuoi modelli, condurre e interpretare i risultati dell’analisi di sensibilità può diventare dispendioso in termini di tempo. 

Qui entra in gioco l’AI. Con AI e machine learning, puoi lasciare che le macchine eseguano i calcoli ogni volta che si verificano determinate condizioni e inseriscano i risultati in dashboard efficaci. 

Una volta chiariti i calcoli che vuoi eseguire, il passo successivo è definire con precisione una pipeline di AI per semplificare e automatizzare il processo. 

Sei curioso di sapere come fare? Dai un’occhiata al nostro tutorial Machine Learning, Pipelines, Deployment and MLOps.

Simulazioni Monte Carlo

La simulazione Monte Carlo è una tecnica matematica utilizzata per modellare la probabilità di diversi risultati in un processo che non può essere facilmente previsto a causa dell’intervento di variabili casuali.

Il problema viene prima modellato tramite una distribuzione di probabilità per ciascuna variabile che presenta incertezza intrinseca. Si estraggono poi grandi numeri di campioni casuali da queste distribuzioni di probabilità, e questi campioni vengono usati per calcolare gli esiti. 

Questo processo si ripete molte volte per creare una distribuzione di possibili risultati, che può essere analizzata statisticamente per fornire previsioni su come si comporterà un sistema. Monte Carlo aiuta ad analizzare l’incertezza in modo più completo rispetto alle tradizionali tabelle di sensibilità.

Puoi imparare tutto su questa potente tecnica nella nostra Guida alla simulazione Monte Carlo in Excel.

Data la natura della simulazione Monte Carlo, puoi usarla per potenziare la tua analisi di sensibilità e trovare quali variabili hanno l’impatto più significativo sull’output. 

Nel nostro esempio delle chitarre, possiamo mantenere costanti il prezzo di costo e le unità vendute e cambiare il prezzo unitario per comprendere le variazioni nelle stime. Poi, ripetere lo stesso processo per le altre due variabili una alla volta. Alla fine, questa tecnica aiuta a capire su quale variabile concentrare gli sforzi per migliorare l’accuratezza.

Best practice ed errori comuni

Sebbene Excel renda l’analisi di sensibilità estremamente semplice e accessibile, ci sono comunque alcuni errori comuni da tenere a mente. Ecco alcune cose da considerare.

Progettare modelli robusti

Come hai visto in questo tutorial, perché l’analisi di sensibilità funzioni, Excel deve conoscere le relazioni tra variabili di input e di output. Ciò richiede di usare le formule in modo accorto, fornendo riferimenti e intervalli di celle chiaramente definiti. 

Se vuoi aggiungere nuovi valori alle tabelle dati, una buona pratica per evitare potenziali problemi durante il ricalcolo è usare le Tabelle di Excel, poiché sono intrinsecamente dinamiche, il che significa che si espandono automaticamente quando si aggiungono nuove righe o colonne. 

Questo ti aiuterà a eseguire i ricalcoli delle formule giusti per mantenere le informazioni nel formato corretto. 

Ridurre gli errori e garantire l’accuratezza

Anche se le tue analisi di sensibilità sono chiaramente definite, devi verificare attentamente i risultati. Test manuali e grafici sono ottime opzioni per visualizzare i risultati e controllare valori anomali o outlier negli output.  

Tuttavia, è importante ricordare che l’analisi di sensibilità potrebbe semplicemente non funzionare per il tuo caso d’uso. Questa analisi si basa su una serie di assunzioni semplici, come l’indipendenza delle variabili di input, l’esistenza di relazioni lineari e la natura statica delle variabili di input. 

Tuttavia, il tuo modello potrebbe essere più complesso ed essere influenzato da fattori esterni che l’analisi di sensibilità, per sua natura, trascura. Quindi, nonostante i suoi vantaggi, è importante avere una comprensione completa del tuo modello al di là della sensibilità per garantire un’analisi corretta.

Conclusione

Complimenti per essere arrivato alla fine di questo tutorial. L’analisi di sensibilità è una potente analisi what-if che può fare la differenza nel tuo processo decisionale. Esistono altre analisi what-if per potenziare i tuoi modelli finanziari, come Trova obiettivo (Goal Seek) o il Gestore scenari. Per fortuna, DataCamp è qui per aiutarti. Dai un’occhiata ai nostri corsi e materiali su Excel selezionati:

• Data Analysis in Excel
• Financial Modeling in Excel
• Excel Fundamentals
• Data Analysis in Google Sheets
• Advanced Excel Functions
• How to Calculate Confidence Intervals in Excel
• Excel Formulas Cheat Sheet
• How to Calculate the Coefficient of Variation in Excel
• Monte Carlo Simulation in Excel: A Complete Guide