Analisi what-if con Scenario Manager

Scenario Manager è utile nei casi in cui si hanno più di due variabili nell'analisi di sensibilità. Scenario Manager crea scenari per ogni set di valori di input per le variabili in esame. Gli scenari ti aiutano a esplorare una serie di possibili risultati, supportando quanto segue:

  • Variabili fino a 32 set di input.
  • Unire gli scenari da diversi fogli di lavoro o cartelle di lavoro.

Se desideri analizzare più di 32 insiemi di input e i valori rappresentano solo una o due variabili, puoi utilizzare le tabelle dati. Sebbene sia limitata a una o due variabili, una tabella dati può includere tutti i valori di input diversi desiderati. Fare riferimento a Analisi what-if con tabelle dati in questo tutorial.

Scenari

Uno scenario è un insieme di valori che Excel salva e può sostituire automaticamente nel foglio di lavoro. È possibile creare e salvare diversi gruppi di valori come scenari su un foglio di lavoro e quindi passare da uno scenario all'altro per visualizzare i diversi risultati.

Ad esempio, è possibile disporre di diversi scenari di budget che confrontano vari livelli di entrate e spese possibili. Puoi anche avere diversi scenari di prestito da diverse fonti che confrontano vari possibili tassi di interesse e durata del prestito.

Se le informazioni che si desidera utilizzare negli scenari provengono da origini diverse, è possibile raccogliere le informazioni in cartelle di lavoro separate e quindi unire gli scenari delle diverse cartelle di lavoro in una sola.

Dopo aver ottenuto tutti gli scenari di cui hai bisogno, puoi creare un rapporto di riepilogo dello scenario:

  • Ciò incorpora informazioni da tutti gli scenari.
  • Ciò ti consente di confrontare gli scenari fianco a fianco.

Scenario Manager

Scenario Manager è uno degli strumenti di analisi what-if in Excel.

Per creare un report di analisi con Scenario Manager, devi seguire questi passaggi:

Step 1 - Definire l'insieme di valori iniziali e identificare le celle di input che si desidera variare, chiamate celle modificabili.

Step 2 - Crea ogni scenario, assegna un nome allo scenario e inserisci il valore per ogni cella di input che cambia per quello scenario.

Step 3- Seleziona le celle di output, chiamate celle dei risultati che desideri monitorare. Queste celle contengono formule nell'insieme iniziale di valori. Le formule utilizzano le celle di input che cambiano.

Lo Scenario Manager crea un report contenente i valori di input e output per ogni scenario.

Valori iniziali per gli scenari

Prima di creare diversi scenari diversi, è necessario definire una serie di valori iniziali su cui si baseranno gli scenari.

I passaggi per impostare i valori iniziali per gli scenari sono:

  • Definisci le celle che contengono i valori di input.
  • Assegna un nome appropriato alle celle di input.
  • Identifica le celle di input con valori costanti.
  • Specificare i valori per gli input costanti.
  • Identifica le celle di input con valori che cambiano.
  • Specificare i valori iniziali per la modifica degli input.
  • Definisci le celle che contengono i risultati. Le celle dei risultati contengono formule.
  • Assegna un nome appropriato alle celle dei risultati.
  • Posiziona le formule nelle celle dei risultati.

Considera il precedente esempio di prestito. Ora procedi come segue:

  • Definisci una cella per Importo prestito.

    • Questo valore di input è costante per tutti gli scenari.

    • Assegna un nome alla cella Loan_Amount.

    • Specificare il valore come 5.000.000.

  • Definire le celle per Tasso di interesse, Numero di pagamenti e Tipo (Pagamento all'inizio o alla fine del mese).

    • Questi valori di input cambieranno negli scenari.

    • Assegna un nome alle celle Tasso_interesse, NPER e Tipo.

    • Specificare i valori iniziali per l'analisi in queste celle come 12%, 360 e 0 rispettivamente.

  • Definisci la cella per l'EMI.

    • Questo è il valore del risultato.

    • Assegna un nome alla cella EMI.

    • Posiziona la formula in questa cella come -

      =PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type)

Il tuo foglio di lavoro appare come mostrato di seguito:

Come puoi vedere che le celle di input e le celle dei risultati sono nella colonna C con i nomi indicati nella colonna D.

Creazione di scenari

Dopo aver impostato i valori iniziali per gli scenari, è possibile creare gli scenari utilizzando Scenario Manager come segue:

  • Fare clic sulla scheda DATI sulla barra multifunzione.
  • Fare clic su Analisi what-if nel gruppo Strumenti dati.
  • Seleziona Scenario Manager dall'elenco a discesa.

Viene visualizzata la finestra di dialogo Scenario Manager. Puoi osservare che contiene un messaggio:

“No Scenarios defined. Choose Add to.”

È necessario creare scenari per ogni serie di valori modificati in Scenario Manager. È bene avere il primo scenario definito con i valori iniziali, poiché consente di tornare ai valori iniziali ogni volta che si desidera durante la visualizzazione di diversi scenari.

Crea il primo scenario con i valori iniziali come segue:

  • Clicca il Add nella finestra di dialogo Scenario Manager.

Il Add Scenario appare la finestra di dialogo.

  • In Nome scenario, digitare Scenario 1.
  • In Modifica celle, inserisci i riferimenti per le celle, ad esempio C3, C4 e C5 con il tasto Ctrl premuto.

Il nome della finestra di dialogo cambia in Modifica scenario.

  • Modifica il testo nel file Comment as – Initial Values scatola.

  • Selezionare l'opzione Impedisci modifiche in Protezione e quindi fare clic su OK.

Il Scenario Valuesappare la finestra di dialogo. I valori iniziali definiti vengono visualizzati in ciascuna delle caselle delle celle che cambiano.

Scenario 1 con i valori iniziali viene creato.

Crea altri tre scenari con valori variabili nelle celle che cambiano come segue:

  • Clicca il Add nella finestra di dialogo Valori scenario.

Viene visualizzata la finestra di dialogo Aggiungi scenario. Notare che C3, C4, C5 vengono visualizzati nella casella Modifica celle.

  • Nella casella Nome scenario, digitare Scenario 2.

  • Modifica il testo nel file Comment as - Tasso di interesse diverso.

  • Seleziona Impedisci modifiche in Protezione e fai clic su OK.

Il Scenario Valuesappare la finestra di dialogo. I valori iniziali vengono visualizzati nelle celle che cambiano. Modifica il valore diInterest_Rate per 0.13 e fare clic Add.

Il Add Scenarioappare la finestra di dialogo. Notare che C3, C4, C5 vengono visualizzati nella casella sotto le celle che cambiano.

  • Nella casella Nome scenario, digitare Scenario 3.

  • Modifica il testo nel file Commentbox as - Diverso n. dei pagamenti.

  • Seleziona Impedisci modifiche in Protezione e fai clic su OK.

Viene visualizzata la finestra di dialogo Valori scenario. I valori iniziali vengono visualizzati nelle celle che cambiano. Modificare il valore di NPER in 300 e fare clicAdd.

Il Add Scenarioappare la finestra di dialogo. Notare che C3, C4, C5 vengono visualizzati nella casella Modifica celle.

  • Nella casella Nome scenario, digitare Scenario 4.

  • Modifica il testo nel file Comment box as - Diverso tipo di pagamento.

  • Seleziona Impedisci modifiche in Protezione e fai clic su OK.

Il Scenario Valuesappare la finestra di dialogo. I valori iniziali vengono visualizzati nelle celle che cambiano. Modificare il valore di Tipo su 1. Fare clic su OK dopo aver aggiunto tutti gli scenari che si desiderava aggiungere.

Il Scenario Managerappare la finestra di dialogo. Nella casella sotto Scenari, troverai i nomi di tutti gli scenari che hai creato.

  • Fare clic su Scenario 1. Come si sa, lo Scenario 1 contiene i valori iniziali.

    • Ora fai clic su Summary. Viene visualizzata la finestra di dialogo Riepilogo scenario.

Rapporti di riepilogo dello scenario

Excel fornisce due tipi di rapporti di riepilogo dello scenario:

  • Riepilogo dello scenario.
  • Rapporto di tabella pivot scenario.

Nella finestra di dialogo Riepilogo scenario, è possibile trovare questi due tipi di rapporto.

Seleziona Riepilogo scenario in Tipo di rapporto.

Riepilogo dello scenario

Nel Result cells casella, seleziona la cella C6 (Qui, avevamo messo il file PMTfunzione). Fare clic su OK.

Il report di riepilogo dello scenario viene visualizzato in un nuovo foglio di lavoro. Il foglio di lavoro è denominato Riepilogo dello scenario.

È possibile osservare quanto segue nel rapporto Riepilogo scenario:

  • Changing Cells- Elenca tutte le celle utilizzate come celle modificabili. Poiché le celle sono state denominate Interest_Rate, NPER e Type, questi sembrano rendere significativo il report. In caso contrario, verranno elencati solo i riferimenti di cella.

  • Result Cells - Visualizza la cella del risultato specificata, cioè EMI.

  • Current Values - È la prima colonna e elenca i valori di quello scenario selezionato nella finestra di dialogo Scenario Manager prima di creare il report di riepilogo.

  • Per tutti gli scenari che hai creato, le celle che cambiano saranno evidenziate in grigio.

  • Nella riga EMI, verranno visualizzati i valori dei risultati per ogni scenario.

È possibile rendere il report più significativo visualizzando i commenti aggiunti durante la creazione degli scenari.

  • Fare clic sul pulsante + a sinistra della riga contenente i nomi degli scenari. I commenti per gli scenari vengono visualizzati nella riga sotto i nomi degli scenari.

Scenari da diverse fonti

Supponiamo di ottenere gli scenari da tre diverse fonti e di dover preparare il report di riepilogo dello scenario in una cartella di lavoro principale. Puoi farlo unendo gli scenari di diverse cartelle di lavoro nella cartella di lavoro principale. Segui i passaggi indicati di seguito:

  • Supponiamo che gli scenari siano nelle cartelle di lavoro, Bank1_Scenarios, Bank2_Scenarios e Bank3_Scenarios. Apri le tre cartelle di lavoro.

  • Apri la cartella di lavoro principale, in cui hai i valori iniziali.

  • Fare clic su DATI> Analisi what-if> Scenario Manager nella cartella di lavoro principale.

Il Scenario Manager Viene visualizzata la finestra di dialogo.

Come puoi osservare, non ci sono scenari in quanto non ne hai ancora aggiunti. ClicMerge.

Viene visualizzata la finestra di dialogo Unisci scenari.

Come puoi vedere, in Unisci scenari da, hai due caselle:

  • Book
  • Sheet

È possibile selezionare un foglio di lavoro specifico da una cartella di lavoro specifica che contiene gli scenari, che si desidera aggiungere ai risultati. Fare clic sulla freccia a discesa diBook per vedere le cartelle di lavoro.

Note - Le cartelle di lavoro corrispondenti dovrebbero essere aperte per apparire in questo elenco.

Seleziona il libro - Bank1_Scenarios.

Viene visualizzato il foglio Bank1. Nella parte inferiore della finestra di dialogo, viene visualizzato il numero di scenari trovati nel foglio di origine. Fare clic su OK.

Viene visualizzata la finestra di dialogo Scenario Manager. I due scenari che sono stati uniti nella cartella di lavoro principale verranno elencati in Scenari.

Clicca il Mergepulsante. IlMerge Scenariosappare la finestra di dialogo. Ora selezionaBank2_Scenarios dall'elenco a discesa nella casella Libro.

Il foglio Bank2 viene visualizzato. Nella parte inferiore della finestra di dialogo, viene visualizzato il numero di scenari trovati nel foglio di origine. Fare clic su OK.

Il Scenario ManagerViene visualizzata la finestra di dialogo. I quattro scenari che sono stati uniti nella cartella di lavoro principale sono elencati in Scenari.

Clicca il Mergepulsante. IlMerge Scenariosappare la finestra di dialogo. Ora selezionaBank3_Scenarios dall'elenco a discesa nella casella Libro.

Viene visualizzato il foglio Bank3. Nella parte inferiore della finestra di dialogo, verrà visualizzato il numero di scenari trovati nel foglio di origine. Fare clic su OK.

Viene visualizzata la finestra di dialogo Scenario Manager. I cinque scenari che sono stati uniti nella cartella di lavoro principale verranno elencati in Scenari.

Ora hai tutti gli scenari necessari per produrre il rapporto di riepilogo dello scenario.

Fare clic sul pulsante Riepilogo. IlScenario Summary appare la finestra di dialogo.

  • Seleziona Riepilogo scenario.
  • Nella casella Celle dei risultati, digitare C6 e fare clic su OK.

Il report di riepilogo dello scenario viene visualizzato in un nuovo foglio di lavoro nella cartella di lavoro principale.

Visualizzazione degli scenari

Si supponga di presentare i propri scenari e di voler passare dinamicamente da uno scenario a un altro e visualizzare l'insieme dei valori di input e dei valori dei risultati dello scenario corrispondente.

  • Fare clic su DATI> Analisi what-if> Scenario Manager dal gruppo Strumenti dati. Viene visualizzata la finestra di dialogo Scenario Manager. Viene visualizzato l'elenco degli scenari.

  • Seleziona lo scenario che desideri visualizzare. ClicShow.

I valori sul foglio di lavoro vengono aggiornati a quelli dello scenario selezionato. I valori dei risultati vengono ricalcolati.

Rapporto tabella pivot scenario

È possibile visualizzare il rapporto sullo scenario anche sotto forma di tabella pivot.

  • Fare clic sul pulsante Riepilogo nel file Scenario ManagerLa finestra di dialogo. Viene visualizzata la finestra di dialogo Riepilogo scenario.

  • Seleziona il Scenario PivotTable report in Tipo di rapporto.

  • Digita C6 nel file Result cells scatola.

Il rapporto di tabella pivot dello scenario viene visualizzato in un nuovo foglio di lavoro.