Modellazione finanziaria in Excel (guida gratuita passo passo + modello)

Sommario

Che cos'è la 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. È stato scritto molto sull'apprendimento della modellazione finanziaria; tuttavia, la maggior parte dei corsi di formazione sui modelli finanziari sono gli stessi. 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 tre 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

Inserisci indirizzo e-mail Continuando il passaggio precedente, accetti i nostri Termini di utilizzo e l'Informativa sulla privacy.

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

Come costruire un modello finanziario?
# 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?

Diamo un'occhiata a come un modello finanziario è costruito 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. Il sistema modulare significa essenzialmente costruire dichiarazioni di base 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 fondamentali al completamento. Posso capire che questo potrebbe non essere chiaro ora; tuttavia, ti renderai conto che questo è 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 diversi programmi 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 diversi programmi sono collegati alle dichiarazioni principali al loro completamento.
In questa guida ai modelli finanziari, costruiremo da zero un modello economico integrato 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 elementare 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 - Si prega di notare che ho aggiornato l'analisi del rapporto di Colgate in un post separato. Dai 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 pari al 100%. Questo sforzo di analisi verticale nel conto economico viene spesso definito analisi del margine poiché produce i diversi margini relativi 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 una migliore marginalità, passando dal 19,7% nel 2007 al 22,4% nel 2012 (con un incremento 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 dell'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 all'anno base. Fornisce un collegamento analitico tra conti calcolati in date diverse utilizzando la valuta con poteri di acquisto variabili. In effetti, questa analisi indicizza le relazioni e confronta l'evoluzione di queste 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 metodi aggiuntivi.

Guardiamo 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 il rapporto corrente, il rapporto di prova acido (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 è più significativo di 1,0 per tutti gli anni. Ciò implica che le attività correnti sono più significative delle passività correnti e forse Colgate ha una liquidità sufficiente.
Il rapporto rapido di Colgate è compreso tra 0,6-0,7; ciò significa che Colgates Cash and Marketable Securities può pagare fino al 70% delle passività correnti. Questa sembra una buona situazione 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, asset 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 ottimi rendimenti per i detentori di azioni.

Passaggio 2E - Analisi dei rischi di Colgate

Attraverso l'analisi dei rischi, 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. Le tariffe che vengono esaminate 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, indicando 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 reddito è estremamente importante. Esistono tanti modi per creare 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 di uso comune, 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: questo è più dettagliato del caso precedente ed è utile quando il prezzo nel mercato è una variabile cruciale. (Per un'azienda con una strategia di sconto sul prezzo, ad esempio, o un giocatore di nicchia di prim'ordine a prezzi premium) Ad esempio, il 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 è essenziale 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 la vendita di servizi, software e aggiornamenti è essenziale. La modellazione della base installata è fondamentale (nuove aggiunte al pavimento, attrito nel terreno, 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

Diamo ora un'occhiata al rapporto Colgate 10K 2013. Notiamo che nel conto economico Colgate non ha fornito informazioni di settore; 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 sulle funzionalità, proietteremo le vendite future di Colgate sulla base di questi dati disponibili. Useremo l'approccio alla crescita delle vendite attraverso i segmenti per ricavare le previsioni. Si prega di vedere l'immagine sotto. Abbiamo calcolato il tasso di crescita anno su anno per ogni elemento. Ora possiamo ipotizzare una percentuale di crescita delle vendite basata sui trend storici e proiettare i ricavi in ciascuna parte. Le vendite nette totali sono la somma totale dei segmenti Oral, Personal & Home Care e Pet 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 diversa: 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 ammortamento da una pianificazione separata: 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 cinque 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 con 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 (proventi)" 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 separatamente debito e liquidità e mezzi equivalenti.

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 prominente sono generalmente assunti a importi costanti
Assicurati che le ipotesi siano ragionevoli e in linea con l'attività

Step 4E - P roject i futuri saldi 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 del programma del capitale circolante, il passo successivo in questo modello finanziario è il progetto Capex di Colgate e la proiezione dei dati di ammortamento e attività. Colgate 2013 - 10K, pagina 49

L'ammortamento non è fornito come voce separata; tuttavia, è incluso nel costo del venduto
In questi casi, si prega di dare un'occhiata ai rendiconti del flusso di cassa in cui si trovano gli ammortamenti e le spese di ammortamento. Inoltre, si noti 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
Arriva 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. Una pratica 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 basato sulla conoscenza del settore e 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)
È complicato riconciliare i PP&E passati a causa di riformulazioni, 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 invece riunito 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 proprietà, 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 - Stima dell'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 prima 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 semplice metodo di ammortamento a quote costanti.
Per calcolare l'ammortamento futuro, trasponiamo prima il Capex utilizzando la funzione TRANSPOSE in Excel.
Calcoliamo l'ammortamento dai contributi patrimoniali di ogni anno.
Inoltre, l'ammortamento del primo anno viene diviso per due 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 produzione e 2) altre attrezzature come mostrato di seguito.

Altri tipi di apparecchiature

Ammortamento totale di Colgate = Ammortamento (Miglioramenti degli edifici) + Ammortamento (Macchinari e attrezzature) + Ammortamento (attrezzatura aggiuntiva) Una volta individuati i dati di ammortamento reale, possiamo metterli 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à. Di solito è complicato proiettare la buona volontà per gli anni futuri.
Tuttavia, l'Avviamento è sottoposto annualmente a test di impairment, eseguiti dalla società stessa. Gli analisti non sono in grado di condurre tali test e preparare stime di svalutazioni.
La maggior parte degli analisti non proietta la buona volontà; mantengono questo come costante, cosa 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 complicate da progettare
Il rapporto 10K di Colgate ci fornisce i dettagli dei prossimi cinque 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 agli estratti conto

# 7 - Altro programma a lungo termine

Il passo successivo in questo modello finanziario è preparare l'altro programma a lungo termine. Questo è il momento in cui ci prepariamo per gli "avanzi" che non hanno driver specifici per la previsione. Nel caso di Colgate, gli altri elementi a lungo termine (rimanenze) erano imposte sul reddito differite (passività e attività), altri investimenti 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 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 primarie e diluite rimarrà lo stesso del 2013.

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

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

# 9 - Modelli finanziari - 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 primario di questo programma è quello di elementi di progetto azionari come del Patrimonio Netto, Dividendi, acquisto di azioni proprie, l'opzione proventi, ecc rapporto di Colgate 10K ci fornisce i dettagli di azioni ordinarie e di attività azioni proprie negli ultimi anni, come mostrato di seguito . 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.
Compila il riacquisto di azioni Colgate (milioni) nel foglio Excel.
Collegare l'EPS storico diluito dal conto economico
L'importo storico del riacquisto 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. L'unica informazione che le loro azioni di report 10K 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 assumere 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 delle azioni implicito previsto del potenziale riacquisto.
Prezzo effettivo delle azioni = presunto PE multiplex EPS.
Il futuro riacquista PE multiplo può essere assunto in base alle 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 vedere il numero di azioni riacquistate = importo in $ 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 un prezzo di esercizio efficace.

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, riempiamo i dati delle opzioni come di seguito. Notiamo inoltre che il prezzo di esercizio medio ponderato delle stock option per il 2013 è stato di $ 42 e il numero di opzioni esercitabili è stato 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

Inserimento 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 sto portando avanti altre questioni relative alle opzioni. Le abbiamo appena prese pari a zero, come evidenziato nell'area grigia sopra. Inoltre, si prevede che le unità di stock limitate saranno 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 Dividend Payout Ratio.
Dividendo in uscita fisso 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 il programma di esclusione delle azioni. Programma di 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 le percentuali non elaborate previste (effettive)
Calcola la media delle azioni base e diluite
Riferimento delle azioni proiettate al conto economico (richiama l'accumulo del conto economico!)
Immettere le informazioni in circolazione sulle azioni storiche
Nota : questo programma è comunemente integrato con l'Equity Schedule

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
L'input ponderato un numero medio di azioni e l'effetto delle stock option per gli anni storici.

Passaggio 10B: collegare le emissioni di azioni e i riacquisti dal programma azionario azionario.

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 la pianificazione delle azioni eccezionali 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 incomplete

Conto economico: gli interessi passivi / proventi sono incompleti in questa fase
Stato patrimoniale: le voci di cassa e di 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 contanti e equivalenti di liquidità

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 le disponibilità liquide e gli equivalenti allo stato patrimoniale.

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

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

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

Grado 12A - Imposta un programma di debito

Fare riferimento al flusso di cassa disponibile per il finanziamento
Fai riferimento a tutte le fonti di capitale e agli utilizzi 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 desideri mantenere un minimo di 500 milioni di dollari all'anno.

Salta l'emissione / rimborsi del debito a lungo termine, liquidità disponibile per la sezione Revolving Credit Facility e Revolver per ora Dal report 10K di Colgate; prendiamo atto dei dettagli disponibili su Revolved Credit Facility Colgate 2013 - 10K, pagina 35

In ulteriori informazioni sul debito vengono forniti anche 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 -Calcola i prestiti / playdown 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 - Principal Link Debito e prelievi Revolver ai flussi di cassa

Passaggio 12H - Riferimento corrente e lungo termine al bilancio

Delimitare la parte corrente 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 quota 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 di Excel sulla modellazione finanziaria. Se desideri apprendere la modellazione finanziaria in Excel attraverso 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 inizia con le basi e ti porta al livello avanzato di Investment Banking Job. Questo corso è diviso in cinque 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!