Modellazione finanziaria in Excel

La modellazione finanziaria in Excel è il processo di costruzione di un modello finanziario per rappresentare una transazione, operazione, fusione, acquisizione, informazioni finanziarie per analizzare come una modifica in una variabile può influenzare il rendimento finale in modo da prendere una decisione su uno o più dei suddette operazioni finanziarie.

Che cos'è la modellazione finanziaria in Excel?

La modellazione finanziaria in Excel è ovunque sul Web e si è scritto molto sull'apprendimento della modellazione finanziaria, tuttavia, la maggior parte delle parti di formazione sulla modellazione finanziaria sono esattamente le stesse. Questo va oltre il solito incomprensibile ed esplora la modellazione finanziaria pratica utilizzata dai banchieri di investimento e dagli analisti di ricerca.

In questa guida Excel gratuita per la modellazione finanziaria, prenderò un esempio di Colgate Palmolive e preparerò da zero un modello finanziario completamente integrato.

Questa guida è composta da oltre 6000 parole e mi ci sono volute 3 settimane per completarla. Salva questa pagina per riferimento futuro e non dimenticare di condividerla :-)

PIÙ IMPORTANTE - Scarica il modello Excel di modellazione finanziaria di Colgate per seguire le istruzioni

Scarica il modello di modello finanziario Colgate

Impara la modellazione finanziaria passo dopo passo in Excel

Formazione sulla modellazione finanziaria in Excel - Leggimi prima

Passaggio 1: scarica il modello di modello finanziario Colgate. Utilizzerai questo modello per il tutorial

Scarica il modello finanziario di Colgate

Passaggio 2 - Si prega di notare che verranno visualizzati due modelli: 1) Modello finanziario Colgate Palmolive non risolto 2) Modello finanziario Colgate Palmolive risolto

Passaggio 3 : Lavorerai sul modello di modello finanziario Colgate Palmolive non risolto . Segui le istruzioni passo passo per preparare un modello finanziario completamente integrato.

Passaggio 4 - Buon apprendimento!

Sommario

Ho creato un sommario di facile navigazione per consentirti di eseguire questa modellazione finanziaria

  •  # 1 - Modello finanziario di Colgate - Storico
  •  # 2 - Analisi del rapporto di Colgate Palmolive
  •  # 3 - Proiezione del conto economico
  •  # 4- Programma del capitale circolante
  •  # 5 - Piano di ammortamento
  •  # 6 - Piano di ammortamento
  •  # 7 - Altro programma a lungo termine
  •  # 8 - Completamento del conto economico
  •  # 9 - Programma del patrimonio netto degli azionisti
  •  # 10 - Programma in sospeso delle azioni
  •  # 11 - Completamento del rendiconto finanziario
  •  # 12- Programma di debito e interessi consigliato
  •  Corso di modellazione finanziaria
  •  Modelli finanziari gratuiti

Se non conosci la modellazione finanziaria, dai un'occhiata a questa guida su Che cos'è la modellazione finanziaria?

Come costruire un modello finanziario in Excel?

Vediamo come viene costruito un modello finanziario da zero. Questa guida dettagliata alla modellazione finanziaria ti fornirà una guida passo passo alla creazione di un modello finanziario. L'approccio principale adottato in questa guida ai modelli finanziari è Modulare. L'approccio modulare significa essenzialmente che costruiamo rendiconti fondamentali come conto economico, stato patrimoniale e flussi di cassa utilizzando diversi moduli / programmi. L'obiettivo principale è preparare ogni dichiarazione passo dopo passo e collegare tutti i programmi di supporto alle dichiarazioni principali al completamento. Posso capire che questo potrebbe non essere chiaro fin da ora, tuttavia, ti renderai conto che è molto facile mentre andiamo avanti. Di seguito puoi vedere vari programmi / moduli di modellazione finanziaria:

Si prega di notare quanto segue -

  • Le dichiarazioni principali sono il conto economico, lo stato patrimoniale e i flussi di cassa.
  • I programmi aggiuntivi sono il programma di ammortamento, il programma del capitale circolante, il programma dei beni immateriali, il programma del patrimonio netto degli azionisti, il programma di altri elementi a lungo termine, il programma del debito, ecc.
  • I programmi aggiuntivi sono collegati alle dichiarazioni principali al loro completamento
  • In questa guida ai modelli finanziari, costruiremo da zero un modello finanziario integrato passo dopo passo di Colgate Palmolive.

# 1 - Modellazione finanziaria in Excel - Proietta gli storici

Il primo passo nella Guida ai modelli finanziari è preparare gli storici.

Passaggio 1A: scarica i rapporti 10K di Colgate

“I modelli finanziari sono preparati in modo eccellente e i primi passi iniziano con la conoscenza di come ha fatto l'industria negli ultimi anni. Comprendere il passato può fornirci preziose informazioni relative al futuro dell'azienda. Quindi il primo passo è scaricare tutti i dati finanziari dell'azienda e popolare gli stessi in un foglio excel. Per Colgate Palmolive, è possibile scaricare i rapporti annuali di Colgate Palmolive dalla sezione Investor Relation. Dopo aver fatto clic su "Rapporto annuale", troverai la finestra mostrata di seguito:

Passaggio 1B: creare il foglio di lavoro dei rendiconti finanziari storici
  • Se scarichi 10K del 2013, noterai che sono disponibili solo due anni di dati di bilancio. Tuttavia, ai fini della modellazione finanziaria in Excel, il set di dati consigliato è di avere gli ultimi 5 anni di bilancio. Scarica gli ultimi 3 anni del rapporto annuale e popola lo storico.
  • Molte volte, queste attività sembrano troppo noiose e noiose in quanto potrebbero essere necessari molto tempo ed energia per formattare e mettere l'Excel nel formato desiderato.
  • Tuttavia, non bisogna dimenticare che questo è il lavoro che ti viene richiesto di fare una sola volta per ogni azienda e inoltre, popolare gli storici aiuta un analista a capire le tendenze e il bilancio
  • Quindi per favore non saltare questo, scarica i dati e popola i dati (anche se ritieni che questo sia un lavoro di asino ;-))

Se desideri saltare questo passaggio, puoi scaricare direttamente il  Colgate Palmolive Historical Model qui. 

Conto economico Colgate con dati storici popolati

Dati storici di bilancio Colgate

# 2 - Analisi del rapporto 

Il secondo passaggio nella modellazione finanziaria in Excel consiste nell'eseguire l'analisi del rapporto.

Una chiave per l'apprendimento della modellazione finanziaria in Excel è essere in grado di eseguire l'analisi fondamentale. Se l'analisi fondamentale o Ratio Analysis è qualcosa di nuovo per te, ti consiglio di leggere un po 'su Internet. Ho intenzione di fare un'analisi approfondita del rapporto in uno dei miei prossimi post, tuttavia, ecco una rapida istantanea dei rapporti Colgate Palmolive

IMPORTANTE - Tieni presente che ho aggiornato l'analisi del rapporto di Colgate in un post separato. Si prega di dare un'occhiata a questa analisi completa del rapporto.

Passaggio 2A - Analisi verticale di Colgate

Sul conto economico, l'analisi verticale è uno strumento universale per misurare la performance relativa dell'azienda di anno in anno in termini di costi e redditività. Dovrebbe sempre essere incluso come parte di qualsiasi analisi finanziaria. Qui, le percentuali sono calcolate in relazione alle vendite nette che sono considerate al 100%. Questo sforzo di analisi verticale nel conto economico è spesso indicato come analisi dei margini poiché produce i diversi margini in relazione alle vendite.

Risultati dell'analisi verticale
  • Il margine di profitto è aumentato di 240 punti base dal 56,2% nel 2007 al 58,6% nel 2013. Ciò è dovuto principalmente alla diminuzione del costo del venduto
  • Anche l'utile operativo o EBIT ha mostrato un miglioramento dei margini, aumentando così dal 19,7% nel 2007 al 22,4% nel 2012 (un aumento di 70 punti base). Ciò era dovuto alla diminuzione dei costi generali e amministrativi di vendita. Tuttavia, si noti che i margini EBIT si sono ridotti nel 2013 al 20,4% a causa di un aumento delle "Altre spese". Inoltre, controlla la differenza tra EBIT ed EBITDA
  • Il margine di profitto netto è aumentato dal 12,6% nel 2007 al 14,5% nel 2012. Tuttavia, il margine di profitto nel 2013 è sceso al 12,9%, principalmente a causa dell'aumento delle "altre spese".
  • Gli utili per azione sono aumentati costantemente dal FY2007 al FY2012. Tuttavia, c'è stato un leggero calo nell'EPS del FY2013
  • Inoltre, si noti che l'ammortamento e l'ammortamento sono forniti separatamente nel conto economico. È incluso nel costo delle vendite
Passaggio 2B - Analisi orizzontale di Colgate

L'analisi orizzontale è una tecnica utilizzata per valutare le tendenze nel tempo calcolando gli aumenti percentuali in eccellenza o le diminuzioni rispetto a un anno base. Fornisce un collegamento analitico tra conti calcolati in date diverse utilizzando valute con poteri di acquisto diversi. In effetti, questa analisi indicizza i conti e confronta l'evoluzione di questi nel tempo. Come per la metodologia di analisi verticale, emergeranno questioni che devono essere studiate e integrate con altre tecniche di analisi finanziaria. L'obiettivo è cercare i sintomi dei problemi che possono essere diagnosticati utilizzando tecniche aggiuntive.

Esaminiamo l'analisi orizzontale di Colgate

Risultati dell'analisi orizzontale
  • Vediamo che le vendite nette sono aumentate del 2,0% nel 2013.
  • Inoltre, nota l'andamento del costo delle vendite, vediamo che non sono cresciuti nella stessa proporzione delle vendite.
  • Queste osservazioni sono estremamente utili durante la modellazione finanziaria in Excel
Fase 2C - Rapporti di liquidità di Colgate
  • Gli indici di liquidità misurano la relazione tra le attività più liquide di un'impresa (quelle più facilmente convertibili in contanti) e le passività correnti. I rapporti di liquidità più comuni sono: Rapporto corrente Rapporto acido test (o asset rapido) Rapporto di cassa
  • Rapporti di fatturato come fatturato contabilità clienti, fatturato inventario e fatturato debiti    
Punti salienti principali degli indici di liquidità
  • L'attuale rapporto di Colgate è maggiore di 1,0 per tutti gli anni. Ciò implica che le attività correnti sono maggiori delle passività correnti e forse Colgate ha una liquidità sufficiente
  • Il rapporto rapido di Colgate è compreso tra 0,6 e 0,7, ciò significa che Colgates Cash e titoli negoziabili possono pagare fino al 70% delle passività correnti. Questa sembra una situazione ragionevole per Colgate.
  • Il ciclo di riscossione del contante è passato da 43 giorni nel 2009 a 39 giorni nel 2013. Ciò è dovuto principalmente alla riduzione del periodo di riscossione dei crediti.

Inoltre, dai un'occhiata a questo articolo dettagliato sul ciclo di conversione in contanti

Fase 2D - Rapporti di redditività operativa di Colgate

I rapporti di redditività sono la capacità di un'azienda di generare guadagni relativi a vendite, attività e capitale proprio

Punti salienti principali - Rapporti di redditività di Colgate

Come possiamo vedere dalla tabella sopra, Colgate ha un ROE più vicino al 100%, il che implica grandi rendimenti per i detentori di azioni.

Passaggio 2E - Analisi dei rischi di Colgate

Attraverso l'analisi del rischio, cerchiamo di valutare se le società saranno in grado di pagare i propri obblighi a breve e lungo termine (debito). Calcoliamo rapporti di leva finanziaria che si concentrano sulla sufficienza degli asset o sulla generazione da asset. I rapporti che vengono esaminati sono

  • Rapporto debito / capitale
  • Rapporto debito
  • Rapporto di copertura degli interessi    
  • Il rapporto debito / capitale proprio è aumentato costantemente fino a raggiungere un livello superiore di 2,23 volte. Ciò significa una maggiore leva finanziaria e rischi nel mercato
  • Tuttavia, il rapporto di copertura degli interessi è molto alto, il che significa un minor rischio di insolvenza del pagamento degli interessi.

# 3 -  Modellazione finanziaria in Excel -  Proietta il conto economico

Il terzo passaggio nella modellazione finanziaria è la previsione del conto economico, in cui inizieremo con la modellazione degli elementi di vendita o di ricavo.

Fase 3A - Proiezioni dei ricavi 

Per la maggior parte delle aziende, i ricavi sono un motore fondamentale della performance economica. Un modello di entrate ben progettato e logico che rifletta accuratamente il tipo e gli importi dei flussi di entrate è estremamente importante. Esistono tanti modi per progettare una pianificazione delle entrate quante sono le aziende. Alcuni tipi comuni includono:

  • Crescita delle vendite: l'  ipotesi di crescita delle vendite in ciascun periodo definisce la variazione rispetto al periodo precedente. Si tratta di un metodo semplice e comunemente utilizzato, ma non offre approfondimenti sui componenti o sulle dinamiche di crescita.
  • Effetti inflazionistici e Volume / Mix:  invece di una semplice ipotesi di crescita, vengono utilizzati un fattore di inflazione dei prezzi e un fattore di volume. Questo utile approccio consente la modellazione dei costi fissi e variabili in aziende multiprodotto e tiene conto dei movimenti del prezzo rispetto al volume.
  • Volume unitario, variazione del volume, prezzo medio e variazione del prezzo:  questo metodo è appropriato per le aziende che hanno un semplice mix di prodotti; consente l'analisi dell'impatto di diverse variabili chiave.
  • Dimensione e crescita del mercato del dollaro:  quota di mercato e variazione della quota - Utile per i casi in cui sono disponibili informazioni sulle dinamiche di mercato e dove è probabile che queste ipotesi siano fondamentali per una decisione. Ad esempio l'industria delle telecomunicazioni
  • Dimensione e crescita del mercato unitario:  è più dettagliata del caso precedente ed è utile quando la determinazione del prezzo nel mercato è una variabile chiave. (Per un'azienda con una strategia di sconto sul prezzo, ad esempio, o un giocatore di nicchia di prim'ordine a prezzi premium) ad es. Mercato delle auto di lusso
  • Capacità di volume, tasso di utilizzo della capacità e prezzo medio:  queste ipotesi possono essere importanti per le aziende in cui la capacità di produzione è importante per la decisione. (Nell'acquisto di capacità aggiuntiva, ad esempio, o per determinare se l'espansione richiederebbe nuovi investimenti.)
  • Disponibilità e prezzi dei prodotti
  • I ricavi sono stati guidati da investimenti in capitale, marketing o ricerca e sviluppo
  • Basato sui ricavi sulla base installata (vendita continua di parti, articoli monouso, assistenza e componenti aggiuntivi, ecc.). Gli esempi includono le classiche aziende con lama di rasoio e attività come i computer in cui le vendite di servizi, software e aggiornamenti sono importanti. La modellazione della base installata è fondamentale (nuove aggiunte alla base, attrito nella base, ricavi continui per cliente, ecc.).
  • Basato sui dipendenti: ad  esempio, i ricavi di società di servizi professionali o società di vendita come broker. La modellazione dovrebbe concentrarsi sul personale netto, entrate per dipendente (spesso basate su ore fatturabili). I modelli più dettagliati includeranno l'anzianità e altri fattori che influenzano i prezzi.
  •  Basato su negozio, struttura o metraggio quadrato: le società di vendita al dettaglio sono spesso modellate sulla base dei negozi (vecchi negozi più nuovi negozi ogni anno) e delle entrate per negozio.
  • Basato sul fattore di occupazione:  questo approccio è applicabile a compagnie aeree, hotel, cinema e altre attività con costi marginali bassi.
Proiezione dei ricavi di Colgate

Vediamo ora il rapporto Colgate 10K 2013. Notiamo che nel conto economico Colgate non ha fornito informazioni segmentali, tuttavia, come informazione aggiuntiva, Colgate ha fornito alcuni dettagli dei segmenti a pagina 87 Fonte - Colgate 2013 - 10K, pagina 86

Poiché non disponiamo di ulteriori informazioni sui segmenti, proietteremo le vendite future di Colgate sulla base di questi dati disponibili. Useremo l'approccio alla crescita delle vendite in tutti i segmenti per ricavare le previsioni. Si prega di vedere l'immagine sotto. Abbiamo calcolato il tasso di crescita anno su anno per ogni segmento. Ora possiamo ipotizzare una percentuale di crescita delle vendite basata sui trend storici e proiettare i ricavi in ​​ogni segmento. Le vendite nette totali sono la somma totale dei segmenti Oral, Personal & Home CarePet Nutrition.

Passaggio 3B - Proiezioni dei costi
  • Percentuale dei ricavi: semplice ma non offre informazioni su alcun effetto leva (economia di scala o onere dei costi fissi
  • Costi diversi dall'ammortamento come percentuale dei ricavi e dall'ammortamento da una pianificazione separata: questo approccio è davvero il minimo accettabile nella maggior parte dei casi e consente solo un'analisi parziale della leva operativa.
  • Costi variabili in base ai ricavi o al volume, costi fissi basati su tendenze storiche e ammortamenti da un programma separato: questo approccio è il minimo necessario per l'analisi di sensibilità della redditività basata su più scenari di entrate
Proiezioni dei costi per Colgate

Per la proiezione del costo, sarà utile l'analisi verticale eseguita in precedenza. Rivediamo l'analisi verticale:

  • Poiché abbiamo già previsto le vendite, tutti gli altri costi sono alcuni margini di queste vendite.
  • L'approccio consiste nel prendere le linee guida dai costi storici e dai margini di spesa e quindi prevedere il margine futuro.
  • Ad esempio, il costo delle vendite è stato compreso tra il 41% e il 42% negli ultimi 5 anni. Possiamo guardare alla previsione dei margini su questa base.
  • Allo stesso modo, le spese di vendita, generali e amministrative sono state storicamente comprese tra il 34% e il 36%. Possiamo ipotizzare il futuro margine di spesa SG&A su questa base. Allo stesso modo, possiamo andare avanti per un'altra serie di spese.

Utilizzando i margini di cui sopra, possiamo trovare i valori effettivi mediante calcoli a ritroso.

 Per calcolare l'accantonamento per imposte, utilizziamo l'ipotesi di aliquota fiscale effettiva

  • Inoltre, si noti che non completiamo la riga "Interessi passivi (entrate)" poiché avremo una nuova revisione del conto economico in una fase successiva.
  • Interessi passivi e interessi attivi.
  • Inoltre, non abbiamo calcolato l'ammortamento che è già stato incluso nel costo del venduto.
  • Questo completa il conto economico (almeno per il momento!)

# 4- Modellazione finanziaria - Programma del capitale circolante

Ora che abbiamo completato il conto economico, il quarto passaggio nella modellazione finanziaria è esaminare il programma del capitale circolante.

Di seguito sono riportati i passaggi da seguire per la pianificazione del capitale circolante

Passaggio 4A: collegare le vendite nette e il costo delle vendite 

Passaggio 4B - Fare riferimento ai dati di bilancio relativi al capitale circolante
  • Fare riferimento ai dati passati dal bilancio
  • Calcola il capitale circolante netto
  • Arrivare a un aumento / diminuzione del capitale circolante
  • Si noti che non abbiamo incluso il debito a breve termine e le disponibilità liquide nel capitale circolante. Tratteremo il debito e la liquidità e gli equivalenti di cassa separatamente.

Passaggio 4C - Calcola i rapporti di fatturato
  • Calcola rapporti e percentuali storici
  • Usa il saldo finale o medio
  • Entrambi sono accettabili purché si mantenga la consistenza

Passaggio 4D: compilare le ipotesi per gli elementi del capitale circolante futuro
  • Alcuni articoli senza un driver evidente sono generalmente considerati a importi costanti
  • Garantire che le ipotesi siano ragionevoli e in linea con l'attività

Step 4E - P roject i saldi futuri del capitale circolante

Passaggio 4F - Calcola le variazioni del capitale circolante
  • Arriva ai flussi di cassa in base alle singole voci
  • Assicurati che i segnali siano accurati!

Passaggio 4G: collegare il capitale circolante previsto allo stato patrimoniale

Passaggio 4H - Collegamento del capitale circolante al rendiconto finanziario 

# 5 - Modellazione finanziaria in Excel - Piano di ammortamento

Con il completamento della pianificazione del capitale circolante, il passo successivo in questo modello finanziario è il progetto Capex di Colgate e la proiezione delle cifre di ammortamento e attività.   Colgate 2013 - 10K, pagina 49

  • L'ammortamento non è fornito come voce separata, tuttavia è incluso nel costo del venduto
  • In tali casi, si prega di dare un'occhiata al Rendiconto finanziario in cui si trovano gli ammortamenti e le spese di ammortamento Si noti inoltre che le figure seguenti sono 1) Ammortamento 2) Ammortamento. Allora qual è il numero di ammortamento?
  • Saldo finale per DPI = Saldo iniziale + Capex - Ammortamento - Rettifica per vendita di asset (equazione BASE)

Passaggio 5A: collegare i dati delle vendite nette nel piano di ammortamento
  • Imposta gli elementi pubblicitari
  • Vendite nette di riferimento
  • Immettere le spese in conto capitale passate
  • Arrivo a Capex come% delle vendite nette

Passaggio 5B - Previsione delle voci di spesa in conto capitale
  • Per prevedere la spesa in conto capitale esistono diversi approcci. Un approccio comune è guardare i comunicati stampa, le proiezioni di gestione, MD&A per comprendere il punto di vista dell'azienda sulla futura spesa in conto capitale
  • Se la società ha fornito indicazioni sulla futura spesa in conto capitale, allora possiamo prendere direttamente quei numeri.
  • Tuttavia, se i numeri Capex non sono direttamente disponibili, possiamo calcolarli grossolanamente utilizzando Capex come% delle vendite (come fatto di seguito)
  • Usa il tuo giudizio in base alla conoscenza del settore e ad altri fattori ragionevoli

Passaggio 5C - Riferimento alle informazioni passate
  • Useremo il saldo finale per i DPI = saldo iniziale + Capex - Deprezzamento - Rettifica per le vendite di asset (equazione BASE)
  • È molto difficile conciliare PP&E passate a causa di rideterminazioni, vendite di asset, ecc
  • Si raccomanda pertanto di non conciliare i precedenti DPI in quanto potrebbe creare una certa confusione.

Politica di ammortamento di Colgate
  • Notiamo che Colgate non ha fornito esplicitamente una suddivisione dettagliata degli Asset. Hanno piuttosto bastonato tutte le risorse in terreni, edifici, macchinari e altre attrezzature
  • Inoltre, le vite utili per macchinari e attrezzature sono fornite nell'intervallo. In questo caso, dovremo fare alcune congetture per arrivare alla vita utile media rimasta per i beni
  • Inoltre, la guida per la vita utile non è fornita per "Altre apparecchiature". Dovremo stimare la vita utile per altre apparecchiature

Colgate 2013 - 10K, pagina 55

Di seguito è riportato il dettaglio dei dettagli relativi a immobili, impianti e macchinari del 2012 e del 2013

Colgate 2013 - 10K, pagina 91

Fase 5D - Stimare il breakup di Property Plant and Equipment (PPE)
  • Innanzitutto, trova i pesi degli asset dell'attuale DPI (2013)
  • Assumeremo che queste ponderazioni degli asset dei DPI 2013 continueranno ad andare avanti
  • Usiamo questo ponderazione degli asset per calcolare la ripartizione della spesa in conto capitale stimata

Passaggio 5E - Stimare l'ammortamento delle attività
  • Si prega di notare che non calcoliamo l'ammortamento del terreno poiché il terreno non è un bene ammortizzabile
  • Per stimare l'ammortamento derivante dai miglioramenti agli edifici, utilizziamo innanzitutto la struttura seguente.
  • L'ammortamento qui è diviso in due parti: 1) ammortamento dal bene di miglioramento degli edifici già elencato nel bilancio 2) ammortamento dai futuri miglioramenti degli edifici
  • Per calcolare l'ammortamento derivante dalle migliorie edilizie elencate nell'attività, utilizziamo il metodo di ammortamento lineare semplice
  • Per calcolare l'ammortamento futuro, trasponiamo prima il Capex utilizzando la funzione TRANSPOSE in Excel
  • Calcoliamo l'ammortamento dal contributo patrimoniale di ogni anno
  • Inoltre, l'ammortamento del primo anno viene diviso per 2 poiché si assume la convenzione di metà anno per la distribuzione degli asset

Ammortamento totale del miglioramento degli edifici = ammortamento dal bene di miglioramento degli edifici già elencato nello stato patrimoniale + ammortamento dal futuro Miglioramenti degli edifici Il processo di cui sopra per la stima dell'ammortamento viene utilizzato per calcolare l'ammortamento di 1) Attrezzature e macchinari di mostrato sotto.

Altri tipi di apparecchiature

Ammortamento totale di Colgate = Ammortamento (Miglioramenti degli edifici) + Ammortamento (Macchinari e attrezzature) + Ammortamento (altre attrezzature) Una volta individuati i dati sull'ammortamento totale, possiamo inserirli nell'equazione BASE come mostrato di seguito

  • Con questo, otteniamo i dati finali di PP&E netti per ciascuno degli anni

Passaggio 5F: collegare il PP&E netto al bilancio

# 6 - Piano di ammortamento

Il sesto passaggio in questa modellazione finanziaria in Excel è prevedere l'ammortamento. Abbiamo due grandi categorie da considerare qui: 1) Avviamento e 2) Altri beni immateriali.

Passaggio 6A - Previsione dell'avviamento

Colgate 2013 - 10K, pagina 61

  • L'avviamento entra in bilancio quando una società acquisisce un'altra società. Normalmente è molto difficile proiettare l'Avviamento per gli anni futuri.
  • Tuttavia, l'Avviamento è soggetto annualmente a test di impairment che vengono eseguiti dalla società stessa. Gli analisti non sono in grado di eseguire tali test e preparare stime delle svalutazioni
  • La maggior parte degli analisti non proietta la buona volontà, la mantiene costante e questo è ciò che faremo anche nel nostro caso.

Passaggio 6B - Previsione di altre attività immateriali
  • Come notato nel rapporto 10K di Colgate, la maggior parte dell'intangibile a vita finita è correlata all'acquisizione di Sanex
  • Anche le "aggiunte agli intangibili" sono molto difficili da proiettare
  • Il rapporto 10K di Colgate ci fornisce i dettagli dei prossimi 5 anni di ammortamento.
  • Useremo queste stime nel nostro modello finanziario Colgate 2013 - 10K, pagina 61

Passaggio 6C - Gli intangibili netti finali sono collegati alle "Altre attività immateriali"

Passaggio 6D: collegare il deprezzamento e l'ammortamento ai rendiconti del flusso di cassa

Passaggio 6E - Collegamento di investimenti e aggiunte a beni immateriali ai rendiconti del flusso di cassa

# 7 - Altro programma a lungo termine

Il passo successivo in questo modello finanziario è preparare l'altro programma a lungo termine. Questo è il programma che prepariamo per gli "avanzi" che non hanno driver specifici per la previsione. Nel caso di Colgate, gli altri elementi a lungo termine (rimanenti) erano Imposte sul reddito differite (passività e attività), Altre attività e altre passività.

Passaggio 7A: fare riferimento ai dati storici del bilancio

Inoltre, calcola le modifiche in questi elementi.

Passaggio 7B - Previsione delle attività e delle passività a lungo termine
  • Mantenere costanti gli elementi a lungo termine per gli anni previsti in caso di assenza di driver visibili
  • Collegare gli elementi a lungo termine previsti allo stato patrimoniale come mostrato di seguito

Passaggio 7C - Riferimento ad altri elementi a lungo termine nel bilancio

Passaggio 7D: collegare gli elementi a lungo termine al rendiconto finanziario

Si noti che se abbiamo mantenuto costanti le attività e le passività a lungo termine, la variazione che fluisce nel rendiconto finanziario sarebbe zero.

# 8 - Modellazione finanziaria in Excel - Completamento del conto economico

  • Prima di procedere ulteriormente in questo modello finanziario basato su Excel, torneremo effettivamente indietro e esamineremo nuovamente il conto economico
  • Popolare la media ponderata di base storica delle azioni e il numero medio ponderato diluito di azioni
  • Queste cifre sono disponibili nel rapporto 10K di Colgate

Passaggio 8A - Riferimento alle azioni base e diluite

In questa fase, supponiamo che il numero futuro di azioni di base e diluite rimarrà lo stesso del 2013.

Passaggio 8B - Calcola l'utile base e diluito per azione

Con questo, siamo pronti a passare al nostro prossimo programma, ovvero il programma azionario degli azionisti.

# 9 - Modellazione finanziaria - Prospetto del patrimonio netto degli azionisti

Il passaggio successivo di questo corso di formazione sulla modellazione finanziaria in Excel consiste nell'esaminare il programma azionario degli azionisti. L'obiettivo principale di questo programma è quello di proiettare elementi relativi alle azioni come patrimonio degli azionisti, dividendi, riacquisto di azioni, proventi da opzioni ecc . Colgate 2013 - 10K, pagina 68

Passaggio 9A - Riacquisto di azioni: popolare i numeri storici 
  • Storicamente, Colgate ha riacquistato azioni, come possiamo vedere nella tabella sopra.
  • Compilare il riacquisto di azioni Colgate (milioni) nel foglio Excel.
  • Collegare l'EPS storico diluito dal conto economico
  • L'importo storico riacquistato deve essere referenziato dai rendiconti dei flussi di cassa

Inoltre, dai un'occhiata al riacquisto accelerato di azioni

Passaggio 9B - Riacquisto di azioni: Calcola il multiplo PE (multiplo EPS)
  • Calcola il prezzo medio implicito al quale Colgate ha storicamente effettuato il riacquisto di azioni. Viene calcolato come importo riacquistato / numero di azioni
  • Calcola il multiplo PE = Prezzo azionario implicito / EPS

Passaggio 9C - Riacquisto di azioni: trovare la quota di Colgate riacquistata

Colgate non ha fatto alcun annuncio ufficiale su quante azioni intende riacquistare. Le uniche informazioni che le loro quote da 10K segnalano sono che hanno autorizzato un riacquisto fino a 50 milioni di azioni. Colgate 2013 - 10K, pagina 35

  • Per trovare il numero di azioni riacquistate, dobbiamo ipotizzare l'Importo di riacquisto delle azioni. Sulla base dell'importo storico del riacquisto, ho preso questo numero come $ 1.500 milioni per tutti gli anni futuri.
  • Per trovare il numero di azioni riacquistate, abbiamo bisogno del prezzo implicito previsto delle azioni del potenziale riacquisto.
  • Prezzo delle azioni implicito = presunto PE multiplex EPS
  • Il futuro riacquista PE multiplo può essere ipotizzato sulla base delle tendenze storiche. Notiamo che Colgate ha riacquistato azioni a un intervallo medio di PE di 17x - 25x
  • Di seguito è riportata l'istantanea di Reuters che ci aiuta a convalidare la gamma PE per Colgate

www.reuters.com

  • Nel nostro caso, ho ipotizzato che tutti i futuri riacquisti di Colgate avverranno a un multiplo di 19 volte.
  • Usando il PE di 19x, possiamo trovare il prezzo implicito = EPS x 19
  • Ora che abbiamo trovato il prezzo implicito, possiamo trovare il numero di azioni riacquistate = $ importo utilizzato per il riacquisto / prezzo implicito

Passaggio 9D - Opzioni su azioni: inserire dati storici
  • Dal riepilogo delle azioni ordinarie e del patrimonio netto, sappiamo il numero di opzioni esercitate ogni anno.

Inoltre, abbiamo anche l'opzione Proventi dai rendiconti dei flussi di cassa (circa)

  • Con questo, dovremmo essere in grado di trovare il prezzo di esercizio effettivo

Colgate 2013 - 10K, pagina 53

Inoltre, si noti che le stock option hanno termini contrattuali di sei anni e maturano su tre anni. Colgate 2013 - 10K, pagina 69

Con questi dati, compiliamo i dati delle Opzioni come di seguito. Notiamo inoltre che il prezzo di esercizio medio ponderato delle stock option per il 2013 era di $ 42 e il numero di opzioni esercitabili era di 24,151 milioni Colgate 2013 - 10K, Pagina 70

Passaggio 9E - Opzioni su azioni: trova i proventi dell'opzione

Inserendo questi numeri nei nostri dati sulle opzioni di seguito, notiamo che i proventi dell'opzione sono $ 1,014 miliardi

Passaggio 9F - Opzioni su azioni: previsione dei dati sulle unità di stock limitate

Oltre alle stock option, ci sono Restricted Stock Units assegnate ai dipendenti con un periodo medio ponderato di 2,2 anni Colgate 2013 - 10K, Pagina 81

Popolamento di questi dati nel set di dati delle opzioni Per semplicità, non abbiamo previsto l'emissione di opzioni (so che questo non è il presupposto corretto, tuttavia, a causa della mancanza di dati, non prenderò più alcuna emissione di opzioni in futuro. Abbiamo appena preso questi sono pari a zero, come evidenziato nell'area grigia sopra. Inoltre, le unità di stock limitate dovrebbero essere 2,0 milioni in futuro.

Inoltre, dai un'occhiata al metodo delle azioni del tesoro

Step 9G- Dividendi: prevedi i dividendi
  • Previsione dei dividendi stimati utilizzando il rapporto di pagamento dei dividendi
  • Importo fisso del dividendo Pagamento per azione
  • Dai rapporti 10.000, estraiamo tutte le informazioni passate sui dividendi
  • Con le informazioni sui dividendi pagati, possiamo scoprire il rapporto di pagamento dei dividendi = Dividendi totali pagati / Utile netto.
  • Ho calcolato il rapporto di pagamento dei dividendi di Colgate come mostrato di seguito -    Notiamo che il rapporto di pagamento dei dividendi è stato ampiamente compreso tra il 50% e il 60%. Supponiamo che il rapporto di pagamento dei dividendi sia del 55% negli anni futuri.
  • Possiamo anche collegare il reddito netto previsto dal conto economico
  • Utilizzando sia l'utile netto previsto che il rapporto di pagamento dei dividendi, possiamo trovare il totale dei dividendi pagati

Passaggio 8H - Previsione del conto azionario nella sua interezza

Con la previsione del riacquisto di azioni, dei proventi delle opzioni e dei dividendi pagati, siamo pronti a completare il Programma del patrimonio degli azionisti. Collega tutti questi per trovare il saldo finale del capitale per ogni anno come mostrato di seguito.

Passaggio 9I - Collegamento del patrimonio netto finale allo stato patrimoniale 

Passaggio 9J - Collegamento di dividendi, riacquisto di azioni e opzioni procede a CF

# 10 - Programma in sospeso delle azioni

Il passaggio successivo di questa modellazione finanziaria online nella formazione su Excel è esaminare la pianificazione delle azioni in sospeso. Riepilogo delle azioni in circolazione

  • Azioni di base: effettive e medie
  • Cattura gli effetti passati di opzioni e convertibili come appropriato
  • Azioni diluite - media
  • Azioni di riferimento riacquistate e nuove azioni da opzioni esercitate
  • Calcola quote di base previste (effettive)
  • Calcola la media delle azioni base e diluite
  • Riferimento delle azioni previste al conto economico (richiama l'accumulo del conto economico!)
  • Immettere le informazioni in circolazione sulle azioni storiche
  • Nota : questo programma è comunemente integrato con il programma azionario
Passaggio 10A: inserire i numeri storici dal rapporto 10K 
  • Le azioni emesse (realizzazione effettiva delle opzioni) e le azioni riacquistate possono essere referenziate dal Prospetto del patrimonio netto degli azionisti
  • Inoltre, l'input ponderato un numero medio di azioni e l'effetto delle stock option per gli anni storici. 
Passaggio 10B - Collegamento delle emissioni e dei riacquisti di azioni dal programma di azioni azionarie.

Azioni di base (fine) = Azioni di base (inizio) + Emissioni di azioni - Azioni riacquistate.

Passaggio 10C - Trova le azioni medie ponderate di base,
  • troviamo una media di due anni come mostrato di seguito.
  • Inoltre, aggiungi l'effetto delle opzioni e delle quote di azioni limitate (a cui fa riferimento il prospetto del patrimonio netto dell'azionista) per trovare le azioni medie ponderate diluite.    
Passaggio 10D - Collegamento delle azioni ponderate di base e diluite al conto economico
  • Ora che abbiamo calcolato le quote medie ponderate diluite, è giunto il momento di aggiornare le stesse nel conto economico.
  • Collegare le azioni medie ponderate diluite previste in circolazione al conto economico come mostrato di seguito

Con questo, completiamo il programma di esclusione delle azioni e il tempo per passare alla nostra prossima serie di dichiarazioni.

# 11 - Completamento del rendiconto finanziario

È importante per noi completare completamente i rendiconti dei flussi di cassa prima di passare al nostro programma successivo e finale in questo modello finanziario, ovvero il programma del debito Fino a questa fase, ci sono solo un paio di cose che sono incomplete

  • Conto economico: gli interessi passivi / proventi sono incompleti in questa fase
  • Stato patrimoniale: le voci di cassa e debito sono incomplete in questa fase
Passaggio 11A: calcolo del flusso di cassa per attività di finanziamento

Inoltre, controlla il flusso di cassa dal finanziamento

Passaggio 11B - Trova l'aumento (diminuzione) netto in Liquidità ed equivalenti

Passaggio 11C = Completare i rendiconti del flusso di cassa

Trova la liquidità e gli equivalenti di cassa di fine anno alla fine dell'anno. 

Passaggio 11D - Collegare la liquidità e gli equivalenti di cassa allo stato patrimoniale.

Ora siamo pronti per occuparci del nostro ultimo e definitivo programma, vale a dire Debito e Programma interessi

# 12- Modellazione finanziaria in Excel - Programma debiti e interessi

Il passo successivo in questo modello finanziario online è completare il programma di debito e interessi. Riepilogo del debito e degli interessi - Programma

Passaggio 12A: impostare una pianificazione del debito
  • Fare riferimento al flusso di cassa disponibile per il finanziamento
  • Fai riferimento a tutte le fonti di capitale e agli usi del contante    
Passaggio 12B - Calcola il flusso di cassa dal rimborso del debito
  • Fare riferimento al saldo di cassa iniziale dal bilancio
  • Detrarre un saldo di cassa minimo. Abbiamo ipotizzato che Colgate vorrebbe mantenere un minimo di $ 500 milioni ogni anno.

Salta emissione / rimborsi di debito a lungo termine, liquidità disponibile per Revolving Credit Facility e sezione Revolver per ora     Dal report 10K di Colgate, notiamo i dettagli disponibili su Revolved Credit Facility Colgate 2013 - 10K, pagina 35

Inoltre, nelle informazioni aggiuntive sul debito vengono forniti i rimborsi del debito a lungo termine impegnato. Colgate 2013 - 10K, pagina 36

Passaggio 12C - Calcola il debito a lungo termine finale

Usiamo il programma di rimborso del debito a lungo termine fornito sopra e calcoliamo il saldo finale dei rimborsi del debito a lungo termine

Passaggio 12D: collegare i rimborsi del debito a lungo termine.

Passaggio 12E -Calcolare i prestiti / pagamenti discrezionali

Utilizzando la formula cash sweep come mostrato di seguito, calcolare i prestiti / pagamenti discrezionali. 

Passaggio 12F - Calcola la spesa per interessi dal debito a lungo termine
  • Calcola il saldo medio per Revolving Credit Facility e Long Term Debt
  • Fai un'ipotesi ragionevole per un tasso di interesse in base alle informazioni fornite nel rapporto 10K
  • Calcola la spesa per interessi totali = saldo medio del debito x tasso di interesse

Trova la spesa per interessi totali = Interessi (Revolving Credit Facility) + Interessi (debito a lungo termine)

Passaggio 12G - Collegamento del debito principale e dei prelievi di revolver ai flussi di cassa 

Passaggio 12H - Riferimento corrente e lungo termine al bilancio
  • Delimitare la parte attuale del debito a lungo termine e del debito a lungo termine come mostrato di seguito

  • Collegare la Revolving Credit Facility, il debito a lungo termine e la parte corrente del debito a lungo termine al bilancio  
Passaggio 12I - Calcola il reddito da interessi utilizzando il saldo di cassa medio

Passaggio 12J - Collegare la spesa per interessi e il reddito per interessi al conto economico 

Eseguire il controllo dello stato patrimoniale: Attività totali = Passività + Patrimonio netto

Passaggio 12K: verifica del bilancio

In caso di discrepanze, è necessario verificare il modello e verificare eventuali errori di collegamento

Corso di modellazione finanziaria consigliato


Spero che ti sia piaciuta la guida gratuita per la modellazione finanziaria in Excel. Se desideri apprendere la modellazione finanziaria in Excel tramite le nostre videoconferenze di esperti, puoi anche guardare la nostra formazione sull'investment banking. Si tratta principalmente di 99 corsi di pacchetto di formazione sull'investimento bancario. Questo corso parte dalle basi e ti porta al livello avanzato di Investment Banking Job. Questo corso è diviso in 5 parti:

  • Parte 1 - Formazione sull'investment banking - Corsi di base

    (26 Corsi)

  • Parte 2 - Formazione sulla modellazione avanzata degli investimenti bancari

    (20 corsi)

  • Parte 3 - Componenti aggiuntivi per l'investment banking

    (13 corsi)

  • Parte 4 - Corsi sulla Fondazione bancaria d'investimento

    (23 Corsi)

  • Parte 5 - Competenze trasversali per i banchieri di investimento

    (17 corsi)

Download dei modelli finanziari


  • Modello finanziario di Alibaba
  • Box IPO Financial Model
  • Modelli di modellazione finanziaria
  • Corso di modellazione finanziaria bancaria

E dopo?

Se hai imparato qualcosa di nuovo o ti è piaciuto questo modello finanziario basato su Excel, lascia un commento qui sotto. Fatemi sapere cosa ne pensate. Molte grazie e abbi cura di te. Buon apprendimento!