www.valterborsato.it

Gestione degli scenari di Excel

La Gestione degli scenari di Excel consente di confrontare sul Foglio di lavoro più set di dati e di riepilogarli in un report che viene automaticamente realizzato dal programma. La Gestione degli scenari consente pertanto di analizzare e confrontare condizioni diverse. 
Lo scopo di una analisi di scenario è infatti quello di semplificare la scelta tra varie condizioni possibili, ed individuare quella ottimale rispetto le esigenze.

La Gestione degli scenari è il primo di tre Strumenti di Analisi di simulazione di Excel (What-If Analysis) che comprende: Gestione degli scenari; Ricerca obiettivo e Risolutore.

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.

Analisi di simulazione, la gestione degli scenari di Excel: tabella dati

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)

Esempio analisi di scenario 

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.

Analisi di simulazione, la gestione degli scenari di Excel: definizione degli intervalli denominati

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.

Analisi di simulazione, la gestione degli scenari di Excel: Gestione nomi 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...

Analisi di simulazione, la gestione degli scenari di Excel: impostazione analisi scenario

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...

Analisi di simulazione, la gestione degli scenari di Excel: Finestra di dialogo Gestione scenari

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.

Analisi di simulazione, la gestione degli scenari di Excel: Finestra di dialogo Aggiungi scenario

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.

Analisi di simulazione, la gestione degli scenari di Excel: descrizione degli scenari da confrontare

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...

 Confronto degli scenari di Excel

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.

Riepilogo Analisi 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.

Gestione degli scenari di Excel: Riepilogo degli scenari

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.

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

Indice argomenti

[Valter Borsato: ottobre - 2014 | Ultimo aggiornamento 09/01/2023]