Gestione degli scenari di Excel

La Gestione degli scenari in Excel è uno strumento che consente di confrontare diversi set di dati all'interno di un Foglio di lavoro e di riepilogarli automaticamente in un report generato dal programma.
Questo strumento è particolarmente utile per eseguire analisi comparative tra diverse condizioni o situazioni, permettendo di vedere come varia il risultato in base a diverse combinazioni di valori.
Il principale vantaggio dell'analisi degli scenari è la possibilità di semplificare il processo decisionale quando si hanno più opzioni possibili da valutare. Ad esempio, se si sta cercando di prevedere i risultati di un budget in base a diverse ipotesi (come variazioni nei costi o nei ricavi), la Gestione degli scenari permette di creare scenari "ottimistici", "pessimistici" e "realistici", confrontando rapidamente le loro implicazioni.
La gestione degli scenari aiuta a identificare l'opzione ottimale in base alle esigenze o agli obiettivi aziendali.
Con la Gestione degli scenari, è possibile creare e memorizzare più combinazioni di valori che influenzano una determinata formula o insieme di formule nel foglio di lavoro. Ogni scenario può includere diverse serie di input, e Excel li riepiloga in un report chiaro e facilmente interpretabile, dove si evidenziano le differenze tra i vari scenari.
L'analisi degli scenari è solo uno dei tre principali strumenti di simulazione offerti da Excel nella sezione What-If Analysis (Analisi di simulazione).
Gli altri due strumenti sono: Ricerca obiettivo, che consente di individuare il valore necessario di una variabile per ottenere un risultato specifico in una formula. Risolutore, un componente aggiuntivo più avanzato che permette di risolvere problemi complessi ottimizzando più variabili contemporaneamente e rispettando vincoli definiti.
La Gestione degli scenari rappresenta quindi il primo passo nell'ambito delle analisi di simulazione, fornendo un metodo semplice e diretto per confrontare alternative. Se le necessità di analisi diventano più complesse, è possibile integrare o combinare l'uso della "Ricerca obiettivo" e del "Risolutore", per ottenere una visione ancora più completa e dettagliata del problema da risolvere.

Nel presente tutorial viene presentato lo strumento della Gestione degli scenari di Excel.
Per spiegare passo per passo l'impostazione dello strumento, viene proposta una comparazione fra i costi di produzione di un manufatto realizzato presso quattro sedi territoriali della medesima azienda.

Esempio di applicazione dell'Analisi di simulazione Gestione scenari

Quale primo passaggio è importante descrivere lo scenario che si vuole analizzare, le caratteristiche dei dati (se variabili o meno) e l'obiettivo della comparazione.

Nella tabella raffigurata nell'immagine sottostante sono classificati ed elencati i costi di realizzazione di uno prodotto "xyz" presso la sede storica di una azienda.
Nella cella B3 è inserito il costo di vendita del bene prodotto.
Con lo sfondo grigio sono evidenziati i costi di produzione (variabili) che saranno oggetto della comparazione con altre tre sedi.
Nella cella B13 viene calcolata la somma di tutti i costi di produzione, e nella sottostante cella B14 è inserita la differenza fra il prezzo di vendita di un singolo prodotto e la somma dei costi di produzione.

Obiettivo dell'analisi è quello di confrontare i costi di produzione di un manufatto presso la sede storica dell'azienda, con i costi di produzione di altre tre sedi di cui una italiana e due delocalizzate all'estero. (vedi immagine sottostante)

 

Preparare i dati per la Gestione degli scenari (definire gli intervalli denominati) 

Per fare riferimento alle descrizioni delle voci presenti nella tabella attraverso una etichetta, e non semplicemente un riferimento di cella, (ad esempio $B$2), conviene creare degli intervalli denominati per i valori inseriti nella tabella.
Per procedere con la creazione degli intervalli denominati, una volta selezionati i dati, nel gruppo pulsanti Nomi definiti della scheda Formule, fare clic sul comando Crea da selezione.
Nella finestra di dialogo Crea nomi da selezione, scegliere l'opzione Colonna sinistra e confermare cliccando su OK.

La creazione degli intervalli denominati, permette di descrivere i valore presenti nella tabella con il nome dell'etichetta della colonna a sinistra dei dati.

Se vi fossero dei dubbi sulla effettiva realizzazione dei nomi definiti per le celle presenti nel prospetto, sempre nel gruppo pulsanti Nomi definiti presenti nella scheda Formule fare clic sul comando Gestione nomi.

Gestione nomi permette di organizzare, modificare, creare o eliminare le etichette (intervalli denominati) presenti nella cartella di lavoro corrente di Excel.
All'interno della Finestra di dialogo Gestione nomi è possibile prendere visione sull'effettiva realizzazione degli intervalli denominati.

Configurare il primo scenario di comparazione

A questo punto si vogliono inserire i costi di produzione delle sedi (scenari) da confrontare con l'azienda già descritta sul Foglio Excel.
Per procedere, bisogna in primo luogo selezionare SOLO le celle del Foglio di lavoro che contengono i dati variabili, ovvero (vedi successiva immagine) l'intervallo di celle B5:B11
Successivamente, nella scheda Dati gruppo pulsanti Previsioni, fare clic sul pulsante Analisi di simulazione e scegliere l'opzione Gestione scenari...

Una volta selezionata l'opzione Gestione scenari si apre l'omonima Finestra di dialogo Gestione scenari nella quale non è impostato nessun tipo di valore predefinito o di analisi.
Per caricare il primo scenario (nel caso dell'esempio i dati della prima azienda) fare clic sul pulsante Aggiungi...

Cliccato su aggiungi si apre la finestra di dialogo Aggiungi scenario.
Nel campo Nome scenario: va inserito un nome alle informazione che andranno inserite.
Celle variabili: se erano state selezionate le celle del Foglio di lavoro contenente i dati della sede storica, il riferimento a queste celle (le variabili) verrà automaticamente proposto nella finestra di dialogo.
Commento: per impostazione Excel propone il nome dell'utente e della data attuale. Informazioni che possono ovviamente essere modificate o integrate con altre spiegazioni.

:: Nella Finestra di dialogo Aggiungi scenario sono disponibili anche delle opzioni di Protezione:
Selezionando Impedisci modifiche non si permette la modifica dello scenario se il foglio di lavoro è protetto.
Selezionare Nascondi si impedisce la visualizzazione dello scenario se il foglio di lavoro è protetto.

Una volta descritto il nome dello scenario e definite impostazioni e protezione, fare clic su OK per passare nella Finestra di dialogo Valori scenario per inserire il primo scenario.
A questo punto è possibile iniziare a inserire i dati della nuova sede Italia, sostituendo i valori che Excel ha rilevato nell'intervallo celle B2:B11

Se non fossero state create delle etichette attraverso l'applicazione degli intervalli denominati, anzichè disporre della descrizione del valore da inserire (vedi immagine sottostante) sarebbero presenti una serie di riferimenti di cella tipo $B$2 che renderebbero più arduo riconoscere la voce corretta da imputare.

Una volta terminato l'inserimento dei dati della prima sede aziendale confermare con OK. e nella successiva finestra Gestione scenari cliccare su Aggiungi... per inserire una ulteriore sede.

Inserite tutte e tre le nuove sedi,  si consiglia di inserire come scenario anche i valori della sede storica, completando in questo l'inserimento dei costi di produzione di tutte le sedi.

Confrontare gli scenari sul Foglio di lavoro 

Nella Finestra di dialogo Gestione scenari (vedi immagine sottostante) selezionando uno qualsiasi dei quattro scenari i valori relativi a quella sede andranno a sostituirsi a quelli inizialmente presenti sul Foglio di lavoro.
Ovviamente le due celle  B13 (somma dei costi) e B14 (differenza prezzo di vendita - costi) aggiorneranno il calcolo relativamente ai valori di produzione della sede selezionata.
La Finestra di dialogo Gestione scenari è il cruscotto operativo delle operazioni di analisi. Da questa finestra è possibile Aggiungere, Eliminare o Modificare gli scenari.

Con il comando Unisci si passa ad un procedura che permette di unire informazioni provenienti da fogli diversi.

Il pulsante che rappresenta sicuramente l'elemento di massimo interesse del processo di valutazione degli scenari è il comando Riepilogo...

 

Creare una tabella di riepilogo dell'Analisi scenari di Excel

Una volta inseriti tutti gli scenari, anzichè far scorrere i dati delle diverse sedi produttive nelle celle del Foglio di lavoro, è possibile organizzare una tabella riepilogativa di confronto.
Per creare la tabella di confronto fare clic sul pulsante Riepilogo... per passare alla finestra di dialogo Riepilogo scenari.

Nella finestra di dialogo Riepilogo scenari è possibile procedere con due distinti tipi di rapporto:
la tabella di Riepilogo, oppure una Analisi degli scenari con una Tabella pivot.

Importante. Qualora non fosse stata correttamente individuata da Excel, è fondamentale che nel campo Celle risultato della finestra Riepilogo scenari, sia selezionata la cella B14 che nell'esempio proposto (vedi immagine di cui sopra) rappresenta il Netto, ovvero la differenza fra il valore di vendita del prodotto e i costi di produzione.

Confermata con OK la scelta Riepilogo, il risultato comparativo che Excel produce è davvero interessate (vedi successiva immagine).
Nella colonna a sfondo bianco è descritto il valore corrente, ovvero il valore presente sul Foglio di lavoro.
Nelle colonne selezionate in grigio sono descritti gli scenari delle quattro sedi di produzione.
Ognuno di questi scenari ha il calcolo del netto relativamente ai costi di produzione della sede. La sede storica (valore corrente) è ripetuta anche nell'ultima colonna.
A sinistra del Foglio di lavoro, è presente una barra di struttura, che permette di comprimere o espandere le voci in tabella.

Creazione di una Tabella Pivot riepilogativa dell'Analisi scenari di Excel

Come descritto nella Finestra di dialogo Riepilogo scenari, è possibile individuare quale destinazione finale un'analisi dati Tabella pivot.
Una volta selezionata l'opzione Scenario rapporto di tabella pivot cliccare su OK.
Il risultato di questa impostazione è la creazione su un nuovo Foglio di lavoro di una Tabella Pivot.
Nel caso dell'immagine sottostante, per evidenziare il diverso margine delle quattro sedi produttive, è stato individuato il Netto come valore da analizzare in tabella.

Indice Generale Argomenti: EXCEL

[Valter Borsato: ottobre - 2020 | Ultimo aggiornamento 21/06/2024]