Come costruire scenari a discesa nel tuo modello finanziario

Il metodo più comunemente utilizzato per creare scenari consiste nell'utilizzare una combinazione di formule e caselle a discesa. Nel modello finanziario si crea una tabella di possibili scenari e dei relativi input e si collegano i nomi degli scenari a una casella a discesa della 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 gli output, i risultati del modello cambieranno quando l'utente seleziona diverse opzioni dalla casella a discesa.

Le caselle a discesa di convalida dei dati vengono utilizzate per una serie di scopi diversi nella modellazione finanziaria, inclusa l'analisi degli scenari.

Utilizzo delle convalide dei dati per modellare scenari di redditività

Scarica il file 0801.xlsx . Aprilo e seleziona la scheda con l'etichetta 8-1-start.

Nel modo in cui è stato modellato, gli input sono allineati nella colonna B. È possibile eseguire l'analisi della sensibilità semplicemente modificando uno degli input, ad esempio, modificare i clienti per operatore di chiamata nella cella B3 da 40 a 45 e vedere tutti i numeri dipendenti cambiare. 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 qualcosa di più che modificare manualmente alcuni numeri.

Per eseguire un'analisi dello scenario utilizzando le caselle a discesa di convalida dei dati, attenersi alla seguente procedura:

Prendi il modello scaricato e taglia e incolla 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 l'intervallo attivo che guida il modello e lo rimarranno. Tuttavia, devono diventare formule che cambiano a seconda della casella a discesa che creerai.

Copia l'intervallo nella colonna B nelle 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 fino a quando non li cambierai.

Nella riga 2 inserire i titoli migliore cassa , caso base , e peggiore di casi.

Impostazione del modello per l'analisi di scenario.

Nota che le formule si collegano ancora agli input nella colonna B, come puoi vedere selezionando la cella C12 e premendo il tasto di scelta rapida F2.

Modifica gli input sotto ogni scenario.

Puoi inserire qualsiasi cosa pensi sia probabile, ma per abbinare i numeri a quelli in questo esempio, inserisci i valori. Ignora la colonna B per ora.

Input per l'analisi di scenario.

Ora devi aggiungere la casella a discesa in alto, che guiderà i tuoi scenari. Non importa dove metti esattamente la casella a discesa, ma dovrebbe trovarsi in una posizione facile da trovare, di solito nella parte superiore della pagina.

Nella cella E1, inserisci il titolo Scenario .

Seleziona la cella F1 e modifica la formattazione in input in modo che l'utente possa vedere che questa cella è modificabile.

Il modo più semplice per farlo è seguire questi passaggi:

Fare clic su una delle celle già formattate come input, ad esempio la cella E3.

Premi l'icona Copia formato nella sezione Appunti sul lato sinistro della scheda Home. Il cursore si trasformerà in un pennello.

Seleziona la cella F1 per incollare la formattazione.

Format Painter è normalmente per uso singolo. Dopo aver selezionato la cella, il pennello scomparirà dal cursore. Se desideri che Copia formato 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, modificare il menu a discesa Consenti in Elenco, utilizzare il mouse per selezionare l'intervallo =$C$2:$E$2 e fare 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, Caso base).

Applicazione di formule agli scenari

Le celle nella colonna B continuano a guidare il modello e 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 il Colore di riempimento dal gruppo Carattere nella scheda Home.

Cambia il Colore di riempimento su uno sfondo bianco.

È molto importante distinguere tra formule e celle di input in un modello. È necessario chiarire a qualsiasi utente che apre il modello che le celle in questo intervallo contengono formule e non devono essere sovrascritte.

Ora è necessario sostituire i valori hardcoded nella colonna B con formule che cambieranno man mano che la casella a discesa cambia. Puoi farlo usando una serie 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 a seconda di cosa c'è nella cella F1.

Ecco quale sarà la formula sotto le diverse opzioni:

• =CERCA.ORIZZ($F$1,$C$2:$E$8,2,0)

  Si noti che con questa soluzione è necessario modificare il numero di indice di riga da 2 a 3 e così via mentre si copia la formula. Invece, potresti usare una funzione RIGA nel terzo campo in questo modo: =CERCA.ORIZZ($F$1,$C$2:$E$8,RIGA(A3)-1,0)

• =SE($F$1=$C$2,C3,SE($F$1=$D$2,D3,E3))
• =IFS($F$1=$C$2,C3,$F$1=$D$2,D3,$F$1=$E$2,E3)
• =SOMMA.SE($C$2:$E$2,$F$1,C3:E3)

  Come sempre, ci sono diverse opzioni tra cui scegliere e la soluzione migliore è quella più semplice e facile da capire. Ognuna di queste funzioni produrrà esattamente lo stesso risultato, ma dover modificare il numero di indice di riga in CERCA.ORIZZ non è affidabile e l'aggiunta di RIGA può creare confusione per un utente. L'istruzione IF nidificata è difficile da compilare e da seguire e, sebbene la nuova funzione IFS sia progettata per semplificare una funzione IF nidificata, è ancora piuttosto ingombrante. Il SUMIF è abbastanza semplice da costruire e seguire, ed è facile da espandere se è necessario aggiungere ulteriori scenari in futuro.

  Tieni presente che IFS è una nuova funzione disponibile solo con Office 365 ed Excel 2016 o versioni successive installate. Se usi questa funzione e qualcuno apre questo modello in una versione precedente di Excel, può visualizzare la formula, ma non potrà modificarla.

Copia la formula nella cella B3 lungo la colonna.

L'analisi di scenario completata.

Usando un normale copia e incolla, perderai tutta la tua formattazione. È importante mantenere la formattazione del modello in modo da poter vedere a colpo d'occhio quali input sono in valori in dollari, percentuali o numeri di clienti. Utilizzare Incolla formule per mantenere la formattazione. Puoi accedervi copiando la cella negli appunti, evidenziando l'intervallo di destinazione, facendo clic con il pulsante destro del mouse e selezionando l'icona Incolla formule per incollare solo le formule e lasciare intatta la formattazione.

Adesso per la parte divertente! È il momento di testare la funzionalità dello scenario nel modello.

Fare clic sulla cella F1, modificare la casella a discesa e osservare gli output del modello cambiare mentre si alternano i diversi scenari.