Analisi di simulazione in Excel: Ricerca obiettivo, Gestione scenari e Tabelle dati

Devi fornire tre cose:

• Imposta cella: la cella che contiene la tua formula (l’output)
• A valore: il risultato target che desideri
• Modificando la cella: la cella di input che Excel è autorizzato a modificare

Vediamolo in pratica. 

Gestisci una piccola attività che vende quaderni. I tuoi ricavi attuali sono $25,000, ma il tuo obiettivo è $50,000. Il prezzo è fisso a $25, quindi devi capire quante unità devi vendere. 

Ecco come fare:

1. Vai su Dati > Analisi di simulazione > Ricerca obiettivo

2. Nella cella Imposta, fai clic sulla cella B4 (la cella della formula dei ricavi)

3. Nel campo A valore, digita 50000

4. In Modificando la cella, seleziona B2 (Unità vendute)

5. Fai clic su OK

Ricerca obiettivo regola B2 da 1.000 a 2.000 unità — il numero esatto necessario per raggiungere 50.000 $ di ricavi a 25 $ per unità.

Uso di Ricerca obiettivo per ottenere un risultato specifico. Immagine dell’autore.

Come usare Gestione scenari in Excel

Gestione scenari è uno strumento di Analisi di simulazione che ti permette di salvare e passare tra più insiemi di valori di input nello stesso foglio. Ogni versione salvata è chiamata scenario. 

Puoi crearne quanti ne servono e passare da uno all’altro all’istante per confrontare i risultati. 

È utile quando vuoi pianificare risultati di caso migliore, caso peggiore e caso atteso senza mantenere tre file separati. 

Devi fornire tre cose:

• Nome dello scenario: un’etichetta per ciascun insieme di ipotesi
• Celle da modificare: le celle di input che differiscono tra gli scenari
• Valori: i numeri specifici per ciascuno scenario

Crea uno scenario

Poniamo che tu stia pianificando le vendite trimestrali per la tua attività di quaderni.

Supponiamo che il tuo profitto attuale sia $15,000. Vuoi modellare tre scenari: caso migliore, caso peggiore e caso atteso. 

Ecco come creare uno scenario:

1. Vai su Dati > Analisi di simulazione > Gestione scenari

2. Nella finestra di dialogo Gestione scenari, fai clic su Aggiungi

3. In Nome dello scenario, digita Best Case.

4. In Celle da modificare, seleziona B2:B4.

5. Apparirà una finestra di dialogo Valori scenario. Nei campi indicati, inserisci questi valori:

• B2 = 2000, 

• B3 = $30

• B4= $10

6. Fai clic su OK. 

Ripeti questi passaggi per gli altri due scenari utilizzando i valori sotto:

Crea uno scenario con tre casi. Immagine dell’autore.

Per passare tra gli scenari, selezionane uno dall’elenco e fai clic su Mostra. Excel sostituisce i valori all’istante e il tuo profitto si aggiorna.

Fai clic su Mostra per vedere i valori di ciascuno scenario. Immagine dell’autore.

Modifica gli scenari

Seleziona qualsiasi scenario dall’elenco e fai clic su Modifica. Puoi rinominarlo, cambiare le celle coinvolte o aggiornare i valori. Nel foglio di lavoro non cambia nulla finché non fai clic su Mostra

Genera report sugli scenari

Gestione scenari può anche creare un report che confronta affiancati tutti gli scenari salvati. 

Per generare un report sugli scenari:

1. All’interno di Gestione scenari, fai clic su Riepilogo

2. In Riepilogo scenario, scegli Riepilogo scenario

3. In Celle risultato, seleziona B7 (la tua cella del profitto)

4. Fai clic su OK

Excel genera una tabella di riepilogo su un nuovo foglio mostrando tutti e tre gli scenari affiancati

Genera un report di riepilogo degli scenari. Immagine dell’autore.

Come usare le Tabelle dati in Excel

Le Tabelle dati automatizzano calcoli ripetuti su un intervallo di valori di input. Invece di cambiare manualmente un input, registrare l’output e ripetere, definisci l’intervallo una volta ed Excel compila tutti i risultati in un colpo solo.

Devi fornire tre cose:

• Intervallo di input: i valori che vuoi testare
• Cella di input riga/colonna: la cella nella tua formula che quei valori sostituiscono
• Cella della formula: l’output attorno a cui è costruita la tua tabella

Tabelle dati a una variabile

Una Tabella dati a una variabile testa come la modifica di un solo input influisce sul risultato di una formula.

Per esempio, hai una formula per la rata di un prestito e vuoi vedere come cambia la rata mensile con tassi di interesse dal 5% al 10%.

Per creare una Tabella dati a una variabile:

1. Prepara il dataset. Una cosa da notare: nel mio dataset, nella cella E1, direttamente sopra il primo tasso, non accanto all’intestazione di colonna, inserisco =B5. Se E1 resta vuota o il riferimento di formula va in un’altra cella, la tabella dati verrà eseguita ma restituirà tutti zeri invece dei valori della rata.

Crea una Tabella dati a una variabile. Immagine dell’autore.

2. Poi seleziona l’intervallo completo D1:E9 (inclusa E1)

3. Vai su Dati > Analisi di simulazione > Tabella dati

4. Lascia vuota la Cella di input riga

5. Nella Cella di input colonna, seleziona B4 (tasso di interesse)

6. Fai clic su OK.

Excel compila la tabella con i risultati calcolati per ciascun valore di input. Nota come la rata non aumenta in modo uniforme. Il salto dal 3% al 4% è di 49 $, ma dal 9% al 10% è di 61 $. Le variazioni del tasso di interesse pesano di più quanto più sono alte.

Risultati calcolati per ciascun valore di input. Immagine dell’autore.

Tabelle dati a due variabili

Una tabella dati a due variabili testa come la modifica simultanea di due input influisce sul risultato di una formula.

Per esempio, un modello di pricing può dipendere sia dal prezzo del prodotto sia dalle unità vendute. Una tabella a due variabili può mostrare il profitto su diversi punti di prezzo e volumi di vendita in un’unica vista.

Per prima cosa, prepara il dataset. 

Guarda il dataset che sto usando:

• Nella cella D1, inserisci =B5. Questo è il riferimento della formula e deve andare nell’angolo in alto a sinistra della griglia, non accanto a un’etichetta

• Sulla riga 1, a partire da E1, elenca i valori di unità vendute che vuoi testare: 500, 1000, 1500, 2000

• Nella colonna D, a partire da D2, elenca i punti di prezzo che vuoi testare: $20, $25, $30, $35

Prepara un dataset per la Tabella dati a due variabili. Immagine dell’autore.

Per creare una Tabella dati a due variabili:

1. Seleziona l’intervallo completo D1:H5.

2. Vai su Dati > Analisi di simulazione > Tabella dati

3. Nella Cella di input riga, seleziona B3 (unità vendute)

4. Nella Cella di input colonna, seleziona B2 (prezzo per unità)

5. Fai clic su OK.

Excel compila il profitto per ogni combinazione di prezzo e unità vendute

Risultati calcolati per il valore di input. Immagine dell’autore.

Le Tabelle dati a due variabili sono particolarmente utili quando una sola ipotesi non racconta l’intera storia. Il prezzo può sembrare buono da solo, ma il profitto dipende anche da quante unità comprano davvero i clienti. È quella seconda variabile a rendere il modello più utile.

Ricerca obiettivo vs. Gestione scenari vs. Tabelle dati

Ricerca obiettivo, Gestione scenari e Tabelle dati fanno tutte parte della cassetta degli attrezzi dell’Analisi di simulazione di Excel, ma risolvono problemi diversi. 

Ecco un rapido confronto tra loro 

Analisi di simulazione vs. Risolutore

Analisi di simulazione e Risolutore ci aiutano entrambi a esplorare risultati diversi in Excel, ma risolvono tipi di problemi differenti.

• Analisi di simulazione testa ipotesi. Modifichi uno o più input e vedi come quei cambiamenti influenzano il risultato. L’obiettivo è comprendere i possibili esiti, non trovare il migliore.
• Risolutore funziona in modo diverso. Gli dai un obiettivo, ad esempio massimizzare il profitto o minimizzare il costo, e cerca la combinazione di input che soddisfa meglio quell’obiettivo. Non sta testando una singola ipotesi. Sta risolvendo per quella ottimale. 

Per esempio, un richiedente un prestito potrebbe usare Ricerca obiettivo per determinare l’importo del prestito che produce una rata mensile di 500 $. Ma un’azienda manifatturiera potrebbe usare Risolutore per determinare il mix di produzione che genera il profitto più alto restando entro i limiti di manodopera e materiali. 

Nota: Risolutore non fa parte degli strumenti standard di Analisi di simulazione. È un componente aggiuntivo separato. Nella maggior parte delle versioni di Excel, dovrai attivarlo manualmente nelle Opzioni di Excel prima che compaia nella scheda Dati.

Best practice per l’Analisi di simulazione

Ecco alcune best practice da seguire quando lavori con l’Analisi di simulazione: 

1. Tieni separate le ipotesi dalle formule

Memorizza i valori di input in celle dedicate e fai riferimento a quelle celle nelle formule. Evita di inserire numeri direttamente nei calcoli. 

Per esempio, questa formula è difficile da aggiornare:

=(50*1000)-10000

Ma questa versione è molto più semplice da mantenere:

=(B2*B3)-B4

Quando le ipotesi cambiano, aggiorni solo le celle di input invece di modificare formule in tutto il file.

2. Etichetta chiaramente gli scenari

I nomi degli scenari dovrebbero dire subito a chi legge cosa rappresentano. “Scenario 1”, “Scenario 2” e “Scenario 3” tra sei mesi non diranno nulla. Chiamali per ciò che rappresentano davvero, ad esempio “Best Case: crescita 15%”. 

3. Valida gli input

L’Analisi di simulazione è affidabile solo quanto gli input testati. Prima di eseguire un’analisi, verifica che:

• Le percentuali siano inserite correttamente
• Le date usino un formato coerente
• Le unità di misura siano coerenti
• I valori di input rientrino in intervalli realistici

Una previsione di ricavi basata su un aumento delle vendite del 500% può essere tecnicamente corretta ma comunque inutile per prendere decisioni.

4. Documenta le ipotesi

Un modello che oggi ha senso per te, tra tre mesi non necessariamente lo avrà per te o per altri. 

Una breve nota accanto a ogni ipotesi, anche solo “basata sui dati effettivi del Q3”, evita molte congetture in seguito. Documentarle aiuta gli altri a capire da dove vengono i numeri e rende molto più semplici i futuri aggiornamenti.

Problemi comuni con l’Analisi di simulazione

La maggior parte dei problemi dell’Analisi di simulazione deriva dall’impostazione del foglio, più che dagli strumenti in sé. Se Ricerca obiettivo, Gestione scenari o Tabelle dati danno risultati inattesi, controlla alcune aree comuni: di solito si risolve così.

1. Le formule non si aggiornano 

Modifichi un input e non succede nulla. Di solito significa che la modalità di calcolo di Excel è stata impostata su manuale lungo la strada. 

Per risolvere: vai su Formule > Opzioni di calcolo e assicurati che sia impostato su Automatico. Se lo è già, premi F9 per forzare un ricalcolo.

2. Riferimenti di cella errati

Gli strumenti di Analisi di simulazione dipendono da riferimenti di cella accurati. Se vengono selezionate celle sbagliate, i risultati saranno errati anche se Excel completa il calcolo con successo. 

Per esempio, Ricerca obiettivo potrebbe cambiare la cella di input sbagliata. Una Tabella dati potrebbe fare riferimento alla cella di input riga o colonna sbagliata. Gestione scenari potrebbe memorizzare valori per celle errate.

Per risolvere: rivedi le celle di input prima di eseguire l’analisi, ricontrolla cosa è selezionato in ogni finestra di dialogo degli strumenti e usa etichette descrittive in modo che sia chiaro cosa rappresenta ogni cella. 

3. Capire male i limiti di Ricerca obiettivo

Molti si aspettano che Ricerca obiettivo risolva problemi con più variabili da modificare. Non può. Ricerca obiettivo cambia una sola cella di input alla volta per raggiungere un valore target. 

Per esempio, Ricerca obiettivo può determinare il volume di vendite necessario per generare 15.000 $ di profitto. Non può allo stesso tempo aggiustare volume, prezzo e costi per raggiungere quel target.

Per risolvere: usa Ricerca obiettivo quando serve modificare una sola variabile. Usa Gestione scenari o Risolutore quando sono coinvolte più variabili. 

4. Modelli di scenario troppo complessi

Gestione scenari consente tecnicamente fino a 32 celle da modificare per scenario, ma solo perché si può non significa che si debba. 

Oltre quattro o cinque variabili, gli scenari diventano difficili da confrontare e ancora più difficili da spiegare a qualcun altro. Se il tuo modello ha bisogno di così tante parti in movimento, probabilmente è il caso di ristrutturarlo invece di stipare tutto in un unico scenario.

Considerazioni finali 

Ogni foglio di calcolo contiene ipotesi da qualche parte. Potrebbe essere un tasso di crescita, un prezzo o un tasso di interesse.

L’Analisi di simulazione non elimina quelle ipotesi. Ti mostra cosa succede se si rivelano sbagliate, che è gran parte di ciò su cui si basa un buon processo decisionale.

Se sei all’inizio, non partire con un modello che ha cinque variabili e tre scenari annidati. Costruisci prima qualcosa di piccolo, tipo un input, un output, una Ricerca obiettivo, e impara come si muovono insieme input e risultato. Le cose complesse possono venire dopo, quando le basi sono chiare.