Come creare scenari drop-down nel proprio modello finanziario - dummies

Il metodo più comunemente utilizzato per costruire scenari consiste nell'utilizzare una combinazione di formule e caselle a discesa. Nel modello finanziario, si crea una tabella di possibili scenari e i relativi input e si collegano i nomi dello scenario a una casella a discesa cella di input. Gli input del modello sono collegati alla tabella degli scenari. Se il modello è stato costruito correttamente con tutti gli input che passano attraverso le uscite, i risultati del modello cambieranno mentre l'utente seleziona diverse opzioni dalla casella a discesa.

Le caselle a discesa di convalida dei dati vengono utilizzate per un numero di scopi diversi nella modellazione finanziaria, compresa l'analisi degli scenari.

Utilizzo delle convalide dei dati per modellare gli scenari di redditività

Scarica il file 0801. xlsx. Aprilo e seleziona la scheda etichettata 8-1-start.

Il modo in cui questo è stato modellato, gli input sono allineati nella colonna B. È possibile eseguire analisi della sensibilità semplicemente modificando uno degli input, ad esempio, modificare l'operatore clienti per chiamata nella cella B3 da 40 a 45 e vedrai cambiare tutti i numeri dipendenti. Questa sarebbe un'analisi di sensibilità, perché stai cambiando solo una variabile. Invece, cambierai più variabili contemporaneamente in questo esercizio completo di analisi dello scenario, quindi dovrai fare più che modificare alcuni numeri manualmente.

Per eseguire un'analisi di scenario utilizzando le caselle di riepilogo di convalida dei dati, seguire questi passaggi:

Prendere il modello scaricato e tagliare e incollare le descrizioni dalla colonna C alla colonna F. Puoi farlo evidenziando le celle C6: C8, premendo Ctrl + X, selezionando la cella F6 e premendo Invio.
Gli ingressi nelle celle da B3 a B8 sono il raggio attivo che guida il modello e rimarrà tale. Tuttavia, devono diventare formule che cambiano a seconda della casella a discesa che verrà creata.
Copia l'intervallo nella colonna B attraverso le colonne C, D ed E.
Puoi farlo evidenziando B3: B8, premendo Ctrl + C, selezionando le celle C3: E3 e premendo Invio. Questi importi saranno gli stessi per ogni scenario finché non li cambierai.
Nella riga 2 immettere i titoli Caso migliore , Caso di base , e Caso peggiore.
Impostazione del modello per l'analisi degli scenari.
Si noti che le formule si collegano ancora agli input nella colonna B, come si può vedere selezionando la cella C12 e premendo il tasto di scelta rapida F2.
Modifica gli input sotto ogni scenario.
Puoi inserire qualsiasi cosa tu pensi sia probabile, ma per abbinare i numeri a quelli in questo esempio, inserisci i valori. Ignora la colonna B per ora.

Input per analisi di scenario.
Ora è necessario aggiungere la casella a discesa nella parte superiore, che sta per guidare i tuoi scenari. Non importa esattamente dove metti la casella a discesa, ma dovrebbe trovarsi in una posizione facile da trovare, in genere nella parte superiore della pagina.
Nella cella E1, immettere il titolo Scenario .
Seleziona la cella F1 e modifica la formattazione da inserire in modo che l'utente possa vedere che questa cella è modificabile.
Il modo più semplice per eseguire questa operazione è seguire questi passaggi:
1. Fare clic su una delle celle già formattate come input, ad esempio cella E3.
2. Premere l'icona Copia formato nella sezione Appunti sul lato sinistro della scheda Home. Il tuo cursore cambierà in un pennello.
3. Seleziona la cella F1 per incollare la formattazione.
  Format Painter è normalmente per uso singolo. Dopo aver selezionato la cella, il pennello sparirà dal cursore. Se vuoi che Format Painter diventi "appiccicoso" e si applichi a più celle, fai doppio clic sull'icona quando la selezioni dalla scheda Home.
Ora, nella cella F1, seleziona Convalida dati dalla sezione Strumenti dati della scheda Dati.
Viene visualizzata la finestra di dialogo Convalida dati.
Nella scheda Impostazioni, cambia il menu Consenti elenco a discesa, usa il mouse per selezionare l'intervallo = $ C $ 2: $ E $ 2 e fai clic su OK.
Creazione degli scenari a discesa di convalida dei dati.
Fare clic sulla casella a discesa, che ora appare accanto alla cella F1, e selezionare uno degli scenari (ad esempio, Base Case).

Applicazione di formule agli scenari

Le celle nella colonna B stanno ancora guidando il modello e queste devono essere sostituite da formule. Prima di aggiungere le formule, tuttavia, è necessario modificare la formattazione delle celle nell'intervallo per mostrare che contengono formule, anziché numeri codificati. Segui questi passi:

Seleziona le celle B3: B8 e seleziona Colore riempimento dal gruppo Font nella scheda Home.
Cambia il Colore di riempimento su uno sfondo bianco.
È molto importante distinguere tra le formule e le celle di input in un modello. Devi rendere chiaro a tutti gli utenti che aprono il modello che le celle di questo intervallo contengono formule e non devono essere sovrascritte.

Ora è necessario sostituire i valori codificati nella colonna B con le formule che cambieranno quando la casella a discesa cambia. Puoi farlo usando un numero di funzioni diverse; un HLOOKUP, un'istruzione IF annidata, un IFS e un SUMIF faranno tutti il trucco. Aggiungi le formule seguendo questi passaggi:

Seleziona la cella B3 e aggiungi una formula che cambierà il valore in base a ciò che si trova nella cella F1.
Ecco quale sarà la formula sotto le diverse opzioni:
- = HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, 2, 0)
  Nota che con questa soluzione, devi cambiare il numero di indice di riga da 2 a 3 e così via mentre copiate la formula in basso. Invece, potresti usare una funzione ROW nel terzo campo come questo: = HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, ROW (A3) -1, 0)
- = IF ($ F $ 1 = $ C $ 2, C3, IF ($ F $ 1 = $ D $ 2, D3, E3))
- = IFS ($ F $ 1 = $ C $ 2, C3, $ F $ 1 = $ D $ 2, D3, $ F $ 1 = $ E $ 2, E3)
- = SUMIF ($ C $ 2: $ E $ 2, $ F $ 1, C3: E3)
  Come sempre, ci sono diverse opzioni tra cui scegliere e la soluzione migliore è quella che è il più semplice e facile da capire.Ognuna di queste funzioni produrrà esattamente lo stesso risultato, ma dover modificare il numero di indice della riga in HLOOKUP non è robusto, e l'aggiunta della ROW potrebbe confondere un utente. L'istruzione IF annidata è complicata da compilare e seguire, e sebbene la nuova funzione IFS sia progettata per rendere più semplice una funzione IF annidata, è ancora piuttosto ingombrante. SUMIF è abbastanza semplice da compilare e seguire ed è facile da espandere se è necessario aggiungere altri scenari in futuro.
  
  Si noti che IFS è una nuova funzione che è disponibile solo con Office 365 ed Excel 2016 o versioni successive. Se si utilizza questa funzione e qualcuno apre questo modello in una versione precedente di Excel, può visualizzare la formula, ma non sarà in grado di modificarla.
Copia la formula nella cella B3 lungo la colonna.
L'analisi dello scenario completata.
Usando una normale copia e incolla, perderai tutta la tua formattazione. È importante conservare la formattazione del modello in modo da poter vedere a colpo d'occhio quali input sono espressi in valori in dollari, percentuali o numeri di clienti. Usa Incolla formule per mantenere la formattazione. È possibile accedervi copiando la cella negli appunti, evidenziando l'intervallo di destinazione, facendo clic con il pulsante destro e selezionando l'icona Incolla formule per incollare solo le formule e lasciare intatta la formattazione.

Ora per la parte divertente! È tempo di testare la funzionalità dello scenario nel modello.
Fai clic sulla cella F1, cambia la casella a discesa e osserva le uscite del modello che cambiano mentre ti sposti tra i diversi scenari.