Best practice per la modellazione finanziaria avanzata: hack per una modellazione intelligente e priva di errori

Sintesi

Introduzione: un modello finanziario

I modelli finanziari sono una parte indispensabile del toolkit finanziario di ogni azienda. Sono fogli di calcolo che descrivono in dettaglio i dati finanziari storici di una determinata attività, ne prevedono la performance finanziaria futura e ne valutano il profilo di rischio e rendimento. I modelli finanziari sono tipicamente strutturati attorno ai tre rendiconti finanziari della contabilità, vale a dire: conto economico, stato patrimoniale e rendiconto finanziario. La gestione della maggior parte delle società si basa, almeno in parte, sui dettagli, le ipotesi e i risultati dei modelli finanziari, che sono tutti fondamentali per i processi decisionali strategici e di capitale di tali società.

Questo articolo funge da guida passo passo per i professionisti della finanza alle prime armi e di livello intermedio che desiderano seguire le migliori pratiche degli esperti durante la creazione di modelli finanziari. Per il modellatore finanziario avanzato, questo articolo mostrerà anche una selezione di suggerimenti e hack di livello esperto per ottimizzare il tempo, l'output e l'efficacia della modellazione. Cominciamo.

Pianificazione del tuo modello

Come per tutte le cose complesse, il primo passo per costruire un modello finanziario ("modello") è progettare attentamente un progetto. Modifiche strutturali non pianificate e impreviste durante un esercizio di modellazione possono richiedere molto tempo, creare confusione e essere soggette a errori, soprattutto se l'adattatore del modello non è lo stesso del suo autore. Tali sfide sono facilmente sovvertite con un po' di tempo dedicato alla pianificazione all'inizio dell'esercizio. Ti consiglio di eseguire la tua fase di pianificazione come segue:

1. Definire l'obiettivo finale del modello.

La definizione chiara dello scopo di un modello è la chiave per determinarne il layout, la struttura e i risultati finali ottimali. Come parte di questo processo, prenditi il ​​tempo necessario per assicurarti che le parti interessate chiave del tuo modello firmino il tuo progetto e la progettazione del processo prima di iniziare a costruire. Questo dà loro l'opportunità di esprimere eventuali preferenze o intenzioni finali, evitando così qualsiasi "scope creep" (gergo industriale) o reindirizzamento doloroso lungo la strada.

2. Comprendere le tempistiche sia per la costruzione del modello che per la sua vita utile.

Sebbene secondario rispetto all'obiettivo finale del modello, anche la comprensione delle tempistiche per la costruzione del modello e per quanto tempo verrà utilizzato sono input importanti per determinare l'approccio all'esercizio di modellazione. I modelli di lunga durata e di lunga durata (vita utile) sono in genere costruiti su misura da zero e includono enormi quantità di dettagli operativi, flessibilità e capacità di sensibilità. Per modelli operativi o di progetto di capitale più immediati e di durata inferiore, i modellisti utilizzeranno spesso modelli prefabbricati per massimizzare la velocità di costruzione riducendo al minimo gli errori. Inoltre, i modelli di modello tendono anche ad essere più familiari e quindi più facili da usare/manipolare da parte dei diversi stakeholder all'interno delle organizzazioni.

3. Determina il compromesso ottimale tra "dettaglio" e "riutilizzabilità".

Quando si decide il compromesso ottimale tra il livello di dettaglio desiderato e la riutilizzabilità del modello (ad esempio, se il modello è destinato a essere rielaborato per più tipi/scopi di transazione o è stato invece progettato solo per questo esercizio una tantum), un quadro utile per decidere la propria scelta/approccio del modello, che ho seguito per gran parte della mia carriera, è il seguente:

Con la fase del progetto/pianificazione ora completa e le decisioni chiave stabilite, possiamo ora passare alla fase successiva della modellazione.

Strutturare il tuo modello

A questo punto, siamo pronti per aprire Excel e iniziare a pensare alla strutturazione. Al livello più alto possibile, ogni modello può/dovrebbe essere suddiviso in tre sezioni: (a) input/driver, (b) calcoli (rendiconti finanziari previsti) e (c) output. Migliore è la segregazione di queste sezioni, più facile sarà controllare e modificare il modello riducendo al minimo gli errori e ottimizzando i tempi.

Ho seguito lo stesso approccio strutturale per quasi tutti i modelli che ho costruito; un approccio che sia i miei rispettivi stakeholder che io abbiamo sempre trovato pratico, digeribile e, in definitiva, utile. Le sue sezioni sono le seguenti:

1. Pagina di copertina (scheda): nome in codice del progetto, una descrizione dell'intento del modello, le informazioni di contatto dell'autore ed eventuali disclaimer applicabili.
2. Scheda Driver: input e ipotesi.
3. Scheda Modello: Calcoli (vale a dire, le tre proiezioni di bilancio ei calcoli).
4. Scheda Output: un riepilogo pulito e ordinato dei punti salienti più importanti del modello.
5. Scheda Sensibilità: la gamma di scenari, sensibilità e risultati dei dati su cui la direzione farà affidamento durante la transizione nel processo decisionale.

Analizzerò ciascuna di queste sezioni per te, una alla volta. Come segue:

Copertina

La copertina è il primo punto di contatto con il tuo lavoro. Sebbene sia il più semplice da costruire, se fatto bene, lascia un'ottima prima impressione e spiega chiaramente cosa accadrà. Una copertina semplice e istruttiva è generalmente l'approccio migliore e in genere include le seguenti sezioni:

1. Nome del modello: autoesplicativo.
2. Scopo del Modello: un paragrafo che ne descriva gli usi previsti
3. Indice del modello: una breve tabella che descrive in dettaglio la descrizione e lo scopo di ciascuna scheda. Questa sezione può essere particolarmente utile per gli operatori non finanziari, aiutandoli a "digerire" la struttura e il flusso del modello evidenziando quali schede devono utilizzare per gli input, quali output su cui concentrarsi durante il processo decisionale e quali schede di calcolo complesse dovrebbero lasciare intatto.
4. Cronologia delle versioni del modello: investire alcuni secondi nella digitazione, in base alla data, delle modifiche chiave apportate al modello man mano che si procede, consente di risparmiare sempre tempo, soprattutto se è necessario ripercorrere e invertire/modificare le modifiche. Ciò è particolarmente vero per modelli complessi e modelli che potresti utilizzare come modelli in periodi futuri.
5. Informazioni di contatto dell'autore: autoesplicativo
6. Disclaimer legali applicabili (se presenti, come fornito dal tuo consulente legale) : autoesplicativo

Nota: consiglio che la copertina sia sempre bloccata a chiunque ea tutti senza espressa autorizzazione ad apportare modifiche, al di fuori dell'autore.

Scheda del conducente: input e ipotesi

Immediatamente dopo la copertina del modello, deve apparire la scheda dei driver (ingressi) . Devi assicurarti che questa scheda sia chiara, concisa e di facile comprensione, poiché questa è la scheda che gli operatori non finanziari probabilmente manipoleranno più spesso. Di solito consiglio di implementare due sezioni di input all'interno della scheda input, una per gli input statici e l'altra per i file dinamici . Per input statici intendo input che non cambiano nel tempo, come l'ipotetica “dimensione di una centrale elettrica” o “il saldo del debito iniziale di un'azienda”; e per input dinamici intendo input variabili nel tempo (ad es. mese per mese o anno per anno) come ipotesi di "inflazione", "costo del debito" o ipotesi di "crescita dei ricavi".

Esempio di una scheda Driver e ipotesi di esempio

All'interno di entrambe le sezioni di input statici e dinamici di cui sopra, ti consiglio anche di separare chiaramente i tuoi dati in due tipi: (1) cifre codificate che non cambiano indipendentemente dallo scenario delle ipotesi e (b) parametri sensibilizzanti che guida diversi scenari di ipotesi e, in definitiva, le tue tabelle di sensibilità. Si noti, tuttavia, che non si sa mai completamente quali parametri costituiranno parametri di sensibilità e quali non si conosceranno fino alle fasi finali del progetto. Per ulteriori informazioni sulla modellazione della sensibilità, fare riferimento al seguente articolo.

Scheda Modello: calcoli dettagliati e struttura operativa

Questa scheda rappresenta il cuore del modello, in cui tutti gli input, le ipotesi e gli scenari interagiscono per proiettare la performance finanziaria di un'azienda negli ultimi anni. È anche fuori da questa scheda che verranno eseguiti vari scenari basati su ipotesi, nonché la parte di valutazione dell'esercizio che verrà condotta prima della decisione strategica finale.

Esempio di una scheda Modello di esempio

Scheda Scenari e Sensibilità

Gli operatori di modelli di terze parti autorizzati utilizzeranno la scheda Scenari e sensibilità abbastanza spesso, anche solo per selezionare la loro scelta di scenari preprogrammati. Per questo motivo, è necessario creare scenari in modo intuitivo, proteggere gli scenari effettivi da modifiche esterne e creare sensibilità sufficientemente variegate in modo tale che la manciata di scenari preprogrammati sia sufficiente per fornire un'ampia visione dei possibili risultati una volta che le tabelle di sensibilità (esempio di seguito) sono anche costruiti.

Per tua considerazione, la struttura del formato degli scenari su cui ho fatto affidamento durante la mia carriera è la seguente, solo per un tipo di esempio:

Alcune note sull'immagine sopra:

1. L'utente del modello dovrebbe essere in grado di modificare solo questo, poiché è qui che selezionerà il numero dello scenario. Il numero si riferisce a uno degli scenari presentati sul lato destro del foglio di calcolo. L'utente presenterà quindi lo scenario selezionato (in questo caso il n. 6) nella prima colonna. Questa è l'unica colonna del foglio di calcolo Scenario e Sensibilità a cui si fa riferimento nel modello.
2. Aggiungi qui un paio di campi descrittivi che riassumono in modo efficace ciò che rappresenta lo scenario selezionato.
3. Trovo sempre molto utile, soprattutto se qualcun altro utilizzerà il modello, per aggiungere una colonna che specifichi ogni unità di input.
4. Questa colonna richiama la statistica/campo della leva dello scenario selezionato (in questo caso, n. 6), che sono tutti visualizzati a destra (in blu). La formula necessaria per pilotarlo è una funzione di offset, ovvero “=OFFSET (inserire cella vuota immediatamente a sinistra del primo scenario evidenziato in rosso sopra ,, Cella in cui è selezionato/evidenziato lo scenario).” Tieni presente che c'è uno spazio vuoto tra le due celle, quindi le due virgole (,,) non sono un errore di battitura.
5. Raggruppa le tue ipotesi in macro-categorie e sottocategorie. Ciò aiuterà sia te (il modellatore) che i tuoi utenti a comprendere chiaramente quale scenario è stato selezionato dal modello.

Esempio di una tabella di sensibilità del campione

Scheda Uscita

Le schede di output sono le schede che gli operatori del modello utilizzeranno più frequentemente. Nel corso degli anni, mi sono trovato a propendere per almeno tre schede di output per i modelli medio-complessi:

1. Scheda Output finanziario: questo è un riepilogo abbreviato dei dati finanziari dettagliati nella scheda modello. Di solito sono presentati su base annuale (anche se il modello può essere trimestrale). Questo output dovrebbe essere compreso tra 50 e 150 righe e dovrebbe presentare tutti gli elementi pubblicitari chiave dalle schede di calcolo. Assicurati di presentare dettagli sufficienti in modo da garantire che gli utenti non passino da questa scheda alle varie schede Calcoli. Tieni inoltre presente che, come procedura consigliata, nessuna scheda di output deve rieseguire alcun calcolo e queste informazioni devono includere solo collegamenti diretti.
2. Scheda Riepilogo esecutivo: questa scheda è piuttosto standard e di solito presenta un mix di grafici, grafici e tabelle, che illustrano, nel modo più semplice e facilmente digeribile possibile, le varie tendenze, analisi e statistiche riassuntive chiave che i dirigenti e i membri del consiglio richiedono per navigare nel loro decisioni chiave.
3. Scheda Output specifico: questa scheda contiene output specifici, generalmente dettati dal modello della nota di investimento, dalla presentazione del comitato di investimento o dalle richieste dei dirigenti e dei membri del consiglio come richiesto per raggiungere i propri punti decisionali.

Esempio di una scheda di output del modello, che include tabelle, grafici e grafici

A questo punto la fase di costruzione del modello è ufficialmente completata. Potremmo rivolgere la nostra attenzione ad alcune delle migliori pratiche di modellazione a livello di esperti a cui ho fatto riferimento all'inizio dell'articolo. Iniziamo con la formattazione.

Formattare il tuo modello

In primo luogo, è importante notare che ogni azienda/gruppo potrebbe avere le proprie preferenze o pratiche interne. Pertanto, durante la costruzione, è importante controllare prima e attenersi a qualsiasi formato prescritto dalla rispettiva azienda. In assenza di pratiche specifiche dell'azienda, tuttavia, il contenuto seguente descrive in dettaglio il linguaggio universale di Wall Street per la formattazione di un modello.

Il primo e più basso metodo di formattazione per la modellazione finanziaria consiste nell'utilizzare schemi di colori coerenti e identificabili per denotare diversi tipi di celle e dati. Come segue:

Blu = input o qualsiasi dato hardcoded, come valori storici, ipotesi e driver.

Nero = Formule, calcoli o riferimenti derivanti dallo stesso foglio.

Verde = Formule, calcoli e riferimenti ad altri fogli (si noti tuttavia che alcuni modelli saltano del tutto questo passaggio e utilizzano il nero per queste celle).

Viola = collegamenti, input, formule, riferimenti o calcoli ad altri file Excel (di nuovo, nota che alcuni modelli saltano del tutto questo passaggio e usano il nero anche per queste celle).

Rosso = Errore da correggere.

Esempio di riepilogo dei dati finanziari ben formattato (con codice colore).

Tieni presente che non esiste alcuna funzionalità di automazione integrata per codificare a colori i tuoi fogli di calcolo Excel in base agli standard di codifica a colori universali di cui sopra. Invece, puoi progettare le tue macro per ottenere questi risultati e successivamente creare combinazioni di scorciatoie per codificare automaticamente il colore del tuo lavoro.

Qualche volta nel mio recente passato, ho ricevuto da un collega (che ringrazio ancora oggi) le seguenti macro (incluse istruzioni dettagliate), che da allora mi hanno risparmiato diverse ore di lavoro manuale. Vorrei condividerli, se posso.

Istruzioni per la creazione di macro (per le versioni Mac e PC di Excel):

1. Premi Alt + W + M + R, contemporaneamente, per nominare e iniziare a registrare la tua macro.
2. Premi F5 ("Vai alla cella") e poi Alt + S, contemporaneamente, per arrivare al menu "Vai a speciale".
3. Premi "O" per selezionare Costanti e "X" per deselezionare il testo.
   
4. Ora premi Alt + H + FC (o Ctrl + 1), contemporaneamente, e seleziona il colore del tuo carattere blu per queste costanti.
5. Premi Esc.
6. Ora fai la stessa cosa, iniziando con F5, ma seleziona Formule (F) invece di costanti e premi "X" per deselezionare il testo.
7. Ora premi Alt + H + FC (o Ctrl + 1) e seleziona un colore del carattere Nero per queste costanti.
8. Interrompi la registrazione della macro con Alt + W + M + R o Alt + T + M + R.

Collegamenti ad altre cartelle di lavoro

Trovare collegamenti ad altre cartelle di lavoro e fogli di lavoro è complicato e molto probabilmente dovrai utilizzare VBA per farlo funzionare correttamente. Ecco l'idea di base: cerca la presenza del simbolo “!” in ogni cella che contiene una formula nella cartella di lavoro, quindi cambia il colore del carattere in verde. Dovrai modificarlo nell'editor VBA e renderlo un ciclo for each ciclo attraverso tutte le istanze di "!" che trovi, quindi cambia il colore del carattere per ciascuno di questi.

Tieni presente che questa scorciatoia non funzionerà ancora il 100% delle volte perché alcune formule faranno riferimento a celle in altri fogli di lavoro senza collegarsi direttamente ad esse. Fortunatamente, le celle verdi sono più rare delle celle nere o blu, quindi il metodo sopra funziona abbastanza bene nella maggior parte dei modelli (e puoi formattare organicamente il resto dei tuoi collegamenti ad altri fogli di lavoro manualmente mentre vengono visualizzati o quando li incontri).

Migliori pratiche per l'audit di un modello

Durante la modellazione, ti incoraggio a tenere sempre questa singola domanda in fondo alla tua mente: "Sto rendendo questo modello facilmente verificabile?" perché per ogni attività eseguita, formula creata e collegamento creato, ci sarà sempre un modo più veloce e "sporco" (nel gergo del settore) per svolgere il lavoro. Tali hack e trucchi, per quanto intelligenti possano sembrare in quel momento, e specialmente dopo intervalli di tempo, saranno invariabilmente dimenticati e porteranno a errori difficili da rintracciare. Tenere a mente un revisore in terza persona ti guiderà attraverso il tuo processo e ti aiuterà a prendere la decisione giusta nei momenti chiave.

Di seguito sono riportate una serie di migliori pratiche su come costruire con una mentalità da auditor. Come segue:

1. Una riga, una formula

Dovresti avere solo una formula per riga, il che significa che qualsiasi formula utilizzata nella prima cella di una determinata riga dovrebbe essere la stessa formula applicata uniformemente sull'intera riga. Gli utenti dovrebbero comprendere la struttura del tuo modello osservando la prima cella di ogni riga mentre procedono verticalmente lungo il tuo modello.

Sebbene questo sia semplice in linea di principio, viene violato abbastanza spesso per evidenziare ulteriormente. Un caso comune si verifica spesso quando i fogli di calcolo sono suddivisi in un gruppo di colonne "dati finanziari storici" e "previsioni per l'anno successivo" (vedere l'immagine sopra intitolata "Esempio di riepilogo di dati finanziari ben formattati (codificati a colori)", come riferimento ).

Un modo semplice per affrontare queste istanze è l'uso di flag (ad esempio, 1/0, TRUE / FALSE ) posizionati nella parte superiore del foglio di calcolo, quindi referenziati utilizzando le istruzioni IF attraverso il corpo del proprio modello. Una semplice illustrazione di questo al lavoro è la seguente:

Esempio di "L'uso dei flag" nella modellazione di Excel

2. Nessun numero codificato incorporato nelle formule

Non utilizzare mai numeri codificati incorporati nelle formule perché sono molto difficili da individuare se l'utente ha meno dimestichezza con il modello. Invece, evidenziare e separare chiaramente gli input/hard-code dalle formule; meglio ancora, raccogli tutti gli input/codici rigidi (a seconda dei casi) e aggregali nella stessa scheda. Successivamente fai in modo che le tue formule le estraggano/le facciano riferimento a seconda dei casi dalla cella richiesta e dalla scheda appropriata.

3. Semplice è sempre meglio

È sempre meglio evitare formule complicate. Invece, suddividi la tua formula in passaggi facilmente digeribili. Invece di una riga apparentemente ordinata, questo approccio creerà spesso molte più righe, risultando in un foglio di calcolo più grande; ma uno che sarà molto più facile da seguire e controllare da una terza parte.

4. Aderire in modo coerente alla Convenzione sui segni

Dovresti decidere a tempo zero quale sarà la tua convenzione/chiave di segno. A titolo illustrativo, chiediti nella fase di progettazione del tuo modello: "Costi, spese, detrazioni, ammortamenti, CapEx, ecc. Verranno presentati come numeri negativi o positivi?" La mia preferenza personale è di presentare sempre i costi come numeri negativi per due motivi: (a) i totali saranno sempre somme semplici e minimizzerai l'errore dell'utente e (b) sarà più facile individuare gli errori usando solo i segni.

5. Evita di nominare le tue celle, affidati invece alla logica della griglia di Excel

Ove possibile, consiglio vivamente di evitare di nominare le tue celle poiché diventa difficile individuare l'input di origine per detta cella denominata (ad esempio, "Inflation") lungo la strada. Invece, ti consiglio di fare affidamento sulla convenzione della griglia di Excel all'interno delle tue formule (ad esempio, semplicemente collegandoti alla cella C4 o alla posizione, [Tab Name]l'!G21 , se il riferimento si trova in una scheda o cartella di lavoro diversa).

6. Non avere mai lo stesso input in più posizioni

Organizza i tuoi input in modo semplice e trasparente. È mia raccomandazione di consolidare tutti gli input in alcune schede del driver e di farvi riferimento dai loro singoli punti di origine in tutto il foglio di calcolo.

7. Evita di collegare file

Evita di creare collegamenti ad altri file. È meglio inserire i dati rilevanti richiesti da un file diverso come input hardcoded, che poi aggiornerai manualmente secondo necessità. È noto che i collegamenti incrociati causano l'arresto anomalo di modelli Excel più grandi o si aggiornano in modo incoerente, creando così errori difficili da tracciare.

8. Non nascondere fogli o righe

All'interno di un foglio di calcolo più lungo, "raggruppa" righe/colonne anziché "nasconderle".

9. Meno schede più grandi sono migliori di più schede più piccole

Questa pratica si basa al 100% sull'esperienza. È più facile seguire e controllare una matrice continua di dati su un foglio di calcolo ampio e contiguo, piuttosto che su più schede o, peggio, più fogli di calcolo collegati tra loro.

10. Crea controlli in tutto il tuo modello tramite "Controlli errori aggregati" situati in una scheda

I controlli sono il modo più semplice per rivedere rapidamente l'integrità di un modello. I "controlli" comprendono tutto, dall'assicurare che i totali che dovrebbero corrispondere siano effettivamente validi per garantire che il proprio bilancio sia effettivamente in equilibrio. Di solito creo alcuni controlli nella parte superiore o inferiore di ogni foglio di calcolo, quindi li consolido in una "scheda di controllo" separata. Ciò garantisce che sia facile trovare un errore nel modello e quindi rintracciare l'origine dell'errore.

Esempio di bilancio "Assegno"

Si noti che fare affidamento esclusivamente sui controlli per verificare l'integrità di un modello non è mai una buona idea poiché i controlli sono generalmente di livello piuttosto elevato. Ma è un buon punto di partenza.

Ritaglia - Per utenti avanzati: suggerimenti su Excel

Questa sezione illustra un paio di best practice di Excel molto efficaci per i nostri utenti più avanzati. Questi potrebbero richiedere un po' di aggiustamento, ma dovrebbero far risparmiare diverse ore di lavoro in seguito ed essere relativamente semplici da implementare. Sono i seguenti, in breve, proiettili succinti e al punto:

1. Usa il maggior numero possibile di scorciatoie da tastiera. Esistono diversi file in Internet sulle scorciatoie da tastiera di Excel che soddisfano esigenze diverse. Ne citerò un paio qui:
   • Foglio illustrativo della chiave di modellazione
   • Elenco completo di suggerimenti di Excel
2. Usa F5 ("vai a speciale") per individuare rapidamente tutti i numeri o le formule hardcoded.
3. Utilizzare Trace Precedents e Trace Dependents per controllare il modello.
4. Utilizzare XNPV e XIRR per consentire l'applicazione di date__ personalizzate ai flussi di cassa, in rotta verso un'analisi dei rendimenti; questo, al contrario delle funzioni NPV e IRR di Excel, che assumono implicitamente intervalli di tempo equidistanti per il calcolo.
5. Usa la funzione INDEX MATCH sulla funzione VLOOKUP per cercare informazioni su fogli di calcolo di grandi dimensioni.
6. VLOOKUP è quasi sempre superiore alle istruzioni IF ; mettersi a proprio agio con esso.
7. Prendi l'abitudine di includere IFERROR nella sintassi delle tue formule.
8. Utilizzare una combinazione della funzione data, delle istruzioni EOMONTH e IF per rendere dinamiche le date.
9. Rimuovere le linee della griglia durante la presentazione o la condivisione del modello finanziario; rende il documento di output più pulito e raffinato.

Amore o odio…

Lo ami o lo odi, Excel è onnisciente, onnipresente e onnipotente quando si tratta di finanza aziendale, analisi e processo decisionale basato sui dati. E che tu ci creda o no, non deve essere intimidatorio o doloroso, anche per i principianti o per i non iniziati. Come la maggior parte delle cose nella vita, la pratica, la coerenza e l'attenzione ai dettagli (come nel caso di Excel, le scorciatoie) ti porteranno quasi ovunque.

Una volta acquisita familiarità con l'applicazione, la troverai un potente strumento di produttività e narrazione numerica, di cui sarai scarsamente in grado di funzionare senza, anche nella tua vita personale. Man mano che avanzi nelle varie fasi della fluidità di Excel, ti auguro il meglio e ti incoraggio a mantenere questo articolo come una guida pratica di riferimento a cui fai spesso riferimento.