Modelare financiară în Excel (ghid gratuit pas cu pas + șablon)

Modelarea financiară în Excel este procesul de construire a unui model financiar care să reprezinte o tranzacție, operațiune, fuziune, achiziție, informații financiare pentru a analiza modul în care o modificare într-o singură variabilă poate afecta randamentul final, astfel încât să se ia o decizie cu privire la una sau mai multe dintre tranzacțiile financiare menționate anterior.

Ce este modelarea financiară în Excel?

Modelarea financiară în Excel este în întregime pe web. S-au scris multe despre învățarea modelării financiare; cu toate acestea, majoritatea modelelor financiare de formare sunt aceleași. Acest lucru depășește tâmpenia obișnuită și explorează modelarea financiară practică, așa cum este utilizată de bancherii de investiții și analiștii de cercetare.

În acest ghid gratuit de modelare financiară Excel, voi lua un exemplu de Colgate Palmolive și voi pregăti un model financiar complet integrat de la zero.

Acest ghid are peste 6000 de cuvinte și mi-a luat trei săptămâni până la finalizare. Salvați această pagină pentru referințe viitoare și nu uitați să o împărtășiți :-)

CELE MAI IMPORTANTE - Descărcați șablonul Excel de modelare financiară Colgate pentru a urma instrucțiunile

Descărcați șablonul de model financiar Colgate

Aflați modelarea financiară pas cu pas în Excel

Modelare financiară în instruire Excel - Citiți-mă mai întâi

Pasul 1 - Descărcați șablonul de model financiar Colgate. Veți folosi acest șablon pentru tutorial

Descărcați Modelul financiar al lui Colgate

Introduceți adresa de e-mail Continuând pasul de mai sus, sunteți de acord cu Termenii de utilizare și politica de confidențialitate.

Pasul 2 - Vă rugăm să rețineți că veți primi două șabloane - 1) Model financiar Colgate Palmolive nerezolvat 2) Model financiar Colgate Palmolive rezolvat

Pasul 3 - Veți lucra la șablonul de model financiar Colgate Palmolive nerezolvat . Urmați instrucțiunile pas cu pas pentru a pregăti un model financiar complet integrat.

Pasul 4 - Învățare fericită!

Cuprins

Am creat un cuprins ușor de navigat pentru a face acest model financiar

• Cum se construiește un model financiar?
• # 1 - Modelul financiar al lui Colgate - Istoric
• # 2 - Analiza raportului Colgate Palmolive
• # 3 - Proiectarea declarației de venit
• # 4- Programul fondului de rulment
• # 5 - Programul de amortizare
• # 6 - Program de amortizare
• # 7 - Alt program pe termen lung
• # 8 - Completarea declarației de venit
• # 9 - Programul de capital propriu al acționarilor
• # 10 - Programul de acțiuni restante
• # 11 - Completarea extraselor de fluxuri de numerar
• # 12- Programul datoriilor și dobânzilor recomandat
• Curs de modelare financiară
• Modele financiare gratuite

Dacă sunteți nou în modelarea financiară, aruncați o privire la acest ghid despre Ce este modelarea financiară?

Cum se construiește un model financiar în Excel?

Să ne uităm la modul în care un model financiar este construit de la zero. Acest ghid detaliat de modelare financiară vă va oferi un ghid pas cu pas pentru crearea unui model financiar. Abordarea principală luată în acest ghid de modelare financiară este Modular. Sistemul modular înseamnă, în esență, crearea de situații de bază, cum ar fi Situația veniturilor, bilanțul și fluxurile de numerar, utilizând diferite module / programe. Obiectivul cheie este să pregătiți fiecare declarație pas cu pas și să conectați toate programele de sprijin la declarațiile de bază la finalizare. Pot să înțeleg că acest lucru nu poate fi clar acum; totuși, veți realiza că acest lucru este foarte ușor pe măsură ce avansăm. Puteți vedea mai jos diferite programe / module de modelare financiară -

Vă rugăm să rețineți următoarele -

• Situațiile principale sunt situația veniturilor, bilanțul și fluxurile de numerar.
• Diferitele programe sunt programul de amortizare, programul fondului de rulment, programul necorporal, programul capitalurilor proprii ale acționarilor, programul altor elemente pe termen lung, programul datoriilor etc.
• Diferitele programe sunt legate de declarațiile de bază la finalizarea lor.
• În acest ghid de modelare financiară, vom construi un model economic integrat pas cu pas al Colgate Palmolive de la zero.

# 1 - Modelare financiară în Excel - Proiectați istoricul

Primul pas în Ghidul de modelare financiară este pregătirea istoricelor.

Pasul 1A - Descărcați rapoartele 10K ale Colgate

„Modelele financiare sunt pregătite în excel, iar primii pași încep cu cunoașterea performanței industriei în ultimii ani. Înțelegerea trecutului ne poate oferi informații valoroase legate de viitorul companiei. Prin urmare, primul pas este să descărcați toate datele financiare ale companiei și să le completați într-o foaie Excel. Pentru Colgate Palmolive, puteți descărca rapoartele anuale ale Colgate Palmolive din secțiunea lor de relații cu investitorii. După ce faceți clic pe „Raport anual”, veți găsi fereastra așa cum se arată mai jos -

Pasul 1B - Creați foaia de lucru cu situațiile financiare istorice

• Dacă descărcați 10K din 2013, veți observa că sunt disponibili doar doi ani de situații financiare. Cu toate acestea, în scopul modelării financiare în excel, setul de date recomandat este să aibă ultimii 5 ani de situații financiare. Vă rugăm să descărcați ultimii 3 ani ai raportului anual și să completați istoricul.
• De multe ori, aceste sarcini par prea plictisitoare și plictisitoare, deoarece poate dura mult timp și energie pentru a formata și a pune excel în formatul dorit.
• Cu toate acestea, nu trebuie să uităm că aceasta este lucrarea pe care trebuie să o faceți o singură dată pentru fiecare companie și, de asemenea, popularea istoricelor ajută un analist să înțeleagă tendințele și situațiile financiare.
• Așadar, vă rugăm să nu omiteți acest lucru, să descărcați datele și să completați datele (chiar dacă credeți că aceasta este lucrarea măgarului ;-))

Dacă doriți să ignorați acest pas, puteți descărca direct modelul istoric Colgate Palmolive aici.

Declarația veniturilor Colgate cu istoric populat

Date istorice ale bilanțului Colgate

# 2 - Analiza raportului

Al doilea pas în modelarea financiară în Excel este efectuarea analizei raportului.

O cheie pentru învățarea modelării financiare în Excel este să puteți efectua analize fundamentale. Dacă analiza elementară sau Analiza raportului este ceva nou pentru dvs., vă recomand să citiți puțin pe internet. Intenționez să fac o analiză aprofundată a raportului într-una din postările mele viitoare, cu toate acestea, iată un instantaneu rapid al rapoartelor Colgate Palmolive.

IMPORTANT - Vă rugăm să rețineți că am actualizat Analiza raportului Colgate într-o postare separată. Vă rugăm să aruncați o privire la această analiză cuprinzătoare a raportului.

Pasul 2A - Analiza verticală a lui Colgate

În contul de profit și pierdere, analiza verticală este un instrument universal pentru măsurarea performanței relative a firmei de la an la an în termeni de cost și profitabilitate. Ar trebui să fie întotdeauna inclus ca parte a oricărei analize financiare. Aici, procentele sunt calculate în raport cu vânzările nete, care sunt considerate a fi 100%. Acest efort de analiză verticală din contul de profit și pierdere este adesea denumit analiza marjei, deoarece produce diferitele marje privind vânzările.

Rezultate analize verticale

• Marja de profit a crescut cu 240 de puncte de bază, de la 56,2% în 2007 la 58,6% în 2013. Acest lucru se datorează în principal scăderii costului vânzărilor
• Profitul operațional sau EBIT au prezentat, de asemenea, marje îmbunătățite, crescând de la 19,7% în 2007 la 22,4% în 2012 (o creștere de 70 de puncte de bază). Acest lucru s-a datorat scăderii costurilor generale și administrative ale vânzării. Cu toate acestea, rețineți că marjele EBIT s-au redus în 2013 la 20,4% din cauza creșterii „Alte cheltuieli”. De asemenea, verificați diferența dintre EBIT și EBITDA
• Marja de profit net a crescut de la 12,6% în 2007 la 14,5% în 2012. Cu toate acestea, marja de profit în 2013 a scăzut la 12,9%, în principal datorită creșterii „altor cheltuieli”.
• Câștigurile pe acțiune au crescut constant de la exercițiul financiar 2007 până la exercițiul financiar 2012. Cu toate acestea, a existat o ușoară scădere în EPS din exercițiul financiar 2013
• De asemenea, rețineți că amortizarea și amortizarea sunt furnizate separat în contul de profit și pierdere. Este inclus în Costul vânzărilor.

Pasul 2B - Analiza orizontală a lui Colgate

Analiza orizontală este o tehnică utilizată pentru a evalua tendințele în timp prin calcularea creșterilor procentuale excelează sau scade în raport cu un an de bază. Oferă o legătură analitică între conturi calculate la date diferite utilizând moneda cu puteri de cumpărare diferite. De fapt, această analiză indexează rapoartele și compară evoluția acestora în timp. Ca și în cazul metodologiei de analiză verticală, vor ieși la iveală probleme care trebuie investigate și completate cu alte tehnici de analiză financiară. Accentul este de a căuta simptome de probleme care pot fi diagnosticate folosind metode suplimentare.

Să ne uităm la analiza orizontală a lui Colgate.

Rezultate analize orizontale

• Vedem că vânzările nete au crescut cu 2,0% în 2013.
• De asemenea, rețineți tendința costului vânzărilor. Vedem că nu au crescut în aceeași proporție cu vânzările.
• Aceste observații sunt extrem de la îndemână în timp ce realizăm modele financiare în Excel.

Pasul 2C - Raportul de lichiditate al lui Colgate

• Ratele de lichiditate măsoară relația dintre activele mai lichide ale unei întreprinderi (cele mai ușor convertibile în numerar) cu pasivele curente. Cele mai frecvente rapoarte de lichiditate sunt raportul curent, raportul Acid test (sau activ rapid) Raport de numerar.
• Raporturi de cifră de afaceri cum ar fi cifra de afaceri a creanțelor din conturi, cifra de afaceri din inventar și cifra de afaceri a datoriilor

Repere cheie ale raporturilor de lichiditate

• Raportul actual al Colgate este mai semnificativ decât 1,0 pentru toți anii. Acest lucru implică faptul că activele circulante sunt mai semnificative decât datoriile curente și poate că Colgate are suficientă lichiditate.
• Raportul rapid al lui Colgate este în intervalul 0,6-0,7; aceasta înseamnă că Colgates Cash și titlurile tranzacționabile pot plăti până la 70% din pasivele curente. Arată o situație bună pentru Colgate.
• Ciclul de încasare a numerarului a scăzut de la 43 de zile în 2009 la 39 de zile în 2013. Acest lucru se datorează în primul rând reducerii perioadei de colectare a creanțelor.

De asemenea, aruncați o privire la acest articol detaliat despre ciclul de conversie a numerarului.

Pasul 2D - Raporturile de rentabilitate operațională ale Colgate

Raportul de rentabilitate al capacității unei companii de a genera câștiguri în raport cu vânzările, activele și capitalurile proprii

Repere cheie - Raporturi de rentabilitate ale Colgate

După cum putem vedea din tabelul de mai sus, Colgate are un ROE mai apropiat de 100%, ceea ce implică randamente excelente pentru deținătorii de acțiuni.

Pasul 2E - Analiza riscurilor pentru Colgate

Prin analiza riscurilor, încercăm să evaluăm dacă companiile vor putea să își achite obligațiile pe termen scurt și lung (datoria). Calculăm ratele de levier care se concentrează pe suficiența activelor sau generarea de active. Tarifele luate în considerare sunt

• Raportul datoriei la capitalul propriu
• Raportul datoriei
• Raportul de acoperire a dobânzii
• Raportul datoriei la capitalul propriu a crescut constant la un nivel mai înalt de 2,23x. Acest lucru înseamnă o pârghie financiară crescută și riscuri pe piață
• Cu toate acestea, rata de acoperire a dobânzii este foarte mare, indicând un risc mai mic de neplată a plății dobânzii.

# 3 - Modelare financiară în Excel - Proiectați declarația de venit

Al treilea pas în modelarea financiară este de a prognoza situația veniturilor, în care vom începe cu modelarea articolelor de vânzări sau venituri.

Pasul 3A - Proiecții de venituri

Pentru majoritatea companiilor, veniturile sunt un motor fundamental al performanței economice. Un model de venituri bine conceput și logic, care reflectă cu exactitate tipul și cantitățile fluxurilor de venituri, este extrem de important. Există tot atâtea modalități de a crea un program de venituri pe cât există companii. Unele tipuri comune includ:

• Creșterea vânzărilor: presupunerea creșterii vânzărilor în fiecare perioadă definește modificarea față de perioada precedentă. Aceasta este o metodă simplă și frecvent utilizată, dar nu oferă informații despre componentele sau dinamica creșterii.
• Efecte inflaționiste și de volum / mix: în loc de o simplă presupunere de creștere, se utilizează un factor de inflație a prețului și un factor de volum. Această abordare utilă permite modelarea costurilor fixe și variabile în companiile cu mai multe produse și ia în considerare mișcările de preț față de volum.
• Volumul unitar, modificarea volumului, prețul mediu și modificarea prețului: această metodă este potrivită pentru companiile care au un mix simplu de produse; permite analiza impactului mai multor variabile cheie.
• Dimensiunea și creșterea pieței dolarului: cota de piață și schimbarea cotei - utilă pentru cazurile în care sunt disponibile informații despre dinamica pieței și în care aceste ipoteze sunt probabil fundamentale pentru o decizie. De exemplu, industria telecomunicațiilor.
• Dimensiunea și creșterea pieței unitare: acest lucru este mai detaliat decât cazul precedent și este util atunci când prețurile pe piață sunt o variabilă crucială. (Pentru o companie cu o strategie de reducere a prețurilor, de exemplu, sau un jucător de nișă de cea mai bună calitate), de exemplu, piața automobilelor de lux
• Capacitatea volumului, rata de utilizare a capacității și prețul mediu: aceste ipoteze pot fi importante pentru întreprinderi în care capacitatea de producție este esențială pentru luarea deciziei. (De exemplu, în achiziționarea unei capacități suplimentare sau pentru a determina dacă extinderea ar necesita noi investiții.)
• Disponibilitatea și prețurile produsului
• Veniturile au fost determinate de investiții în capital, marketing sau cercetare și dezvoltare
• Bazat pe venituri pe baza instalată (vânzările continue de piese, produse de unică folosință, service și suplimente etc.). Exemplele includ întreprinderi clasice cu lame de ras și companii precum computere în care vânzările de servicii, software și upgrade-uri sunt esențiale. Modelarea bazei instalate este esențială (noi adăugări la podea, uzură la sol, venituri continue pe client etc.).
• Bazat pe angajați: De exemplu, veniturile firmelor de servicii profesionale sau ale firmelor bazate pe vânzări, cum ar fi brokerii. Modelarea ar trebui să se concentreze pe personalul net, veniturile pe angajat (adesea pe baza orelor facturate). Modele mai detaliate vor include vechimea și alți factori care afectează prețurile.
• Magazin, instalație sau bazat pe înregistrarea pătrată: companiile de vânzare cu amănuntul sunt adesea modelate pe baza magazinelor (magazinele vechi plus magazine noi în fiecare an) și a veniturilor pe magazin.
• Bazat pe factor de ocupare: această abordare se aplică companiilor aeriene, hotelurilor, cinematografelor și altor companii cu costuri marginale reduse.

Proiectarea veniturilor Colgate

Să ne uităm acum la raportul Colgate 10K 2013. Observăm că în contul de profit și pierdere, Colgate nu a furnizat informații segmentare; totuși, ca o informație suplimentară, Colgate a furnizat câteva detalii ale segmentelor pe Pagina 87 Sursa - Colgate 2013 - 10K, Pagina 86

Deoarece nu avem alte informații despre caracteristici, vom proiecta vânzările viitoare ale Colgate pe baza acestor date disponibile. Vom folosi abordarea creșterii vânzărilor pe segmente pentru a obține previziunile. Vă rugăm să vedeți imaginea de mai jos. Am calculat rata de creștere de la an la an pentru fiecare element. Acum putem presupune un procent de creștere a vânzărilor bazat pe tendințele istorice și să proiectăm veniturile din fiecare parte. Vânzările nete totale reprezintă suma totală a segmentului de nutriție orală, personală și la domiciliu și al animalelor de companie.

Pasul 3B - Costuri proiecții

• Procentul veniturilor: simplu, dar nu oferă nicio perspectivă asupra vreunei pârghii (economie de scară sau sarcină a costurilor fixe)
• Alte costuri decât amortizarea ca procent din venituri și amortizarea dintr-un program diferit: Această abordare este într-adevăr minimul acceptabil în majoritatea cazurilor și permite doar analiza parțială a efectului de levier operațional.
• Costuri variabile bazate pe venituri sau volum, costuri fixe pe baza tendințelor istorice și amortizarea dintr-un program separat: Această abordare este minimul necesar pentru analiza sensibilității profitabilității pe baza mai multor scenarii de venituri

Proiecții de costuri pentru Colgate

Pentru proiectarea costului, analiza verticală făcută mai devreme va fi utilă. Să analizăm vertical analiza -

• Întrucât am prognozat deja vânzări, toate celelalte costuri sunt câteva marje ale acestei vânzări.
• Abordarea este de a lua liniile directoare din marjele de costuri și cheltuieli istorice și apoi de a prognoza marja viitoare.
• De exemplu, costul vânzărilor a fost în intervalul 41% -42% în ultimii cinci ani. Ne putem uita la prognozarea marjelor pe această bază.
• În mod similar, cheltuielile de vânzare, generale și administrative au fost în mod istoric cuprinse între 34% -36%. Ne putem asuma viitoarea marjă de cheltuieli SG&A pe această bază. La fel, putem continua pentru un alt set de cheltuieli.

Folosind marginile de mai sus, putem găsi valorile reale prin calcule înapoi.

Pentru calcularea provizionului pentru impozite, utilizăm ipoteza ratei de impozitare eficiente.

• De asemenea, rețineți că nu completăm rândul „Cheltuieli cu dobânzile (venituri)” deoarece vom relua declarația de venit într-o etapă ulterioară.
• Cheltuieli cu dobânzi și venituri din dobânzi.
• De asemenea, nu am calculat amortizarea și amortizarea, care a fost deja inclusă în costul vânzărilor.
• Aceasta completează Declarația de venit (cel puțin pentru moment!)

# 4- Modelare financiară - Program de capital de lucru

Acum, după ce am finalizat declarația de venit, al patrulea pas în modelarea financiară este să analizăm programul de capital de lucru.

Mai jos sunt pașii care trebuie urmați pentru programul de capital de lucru

Pasul 4A - Conectați vânzările nete și costul vânzărilor

Pasul 4B - Consultați datele bilanțului legate de fondul de rulment

• Faceți referință la datele anterioare din bilanț
• Calculați fondul de rulment net
• Ajunge la o creștere / scădere a fondului de rulment
• Rețineți că nu am inclus datoriile pe termen scurt și numerar și echivalente de numerar în fondul de rulment. Ne vom ocupa separat de datorii și numerar și echivalente de numerar.

Pasul 4C - Calculați ratele de rulare

• Calculați rapoartele și procentele istorice
• Folosiți soldul final sau mediu
• Ambele sunt acceptabile, cu condiția menținerii coerenței

Pasul 4D - Populați ipotezele pentru viitoarele elemente de fond de rulment

• Anumite articole fără un driver proeminent sunt de obicei asumate la sume constante
• Asigurați-vă că ipotezele sunt rezonabile și în conformitate cu afacerea

Pasul 4E - Proiectați viitoarele solduri de fond de rulment

Pasul 4F - Calculați modificările din fondul de rulment

• Sosiți la fluxurile de numerar pe baza elementelor rând individuale
• Asigurați-vă că semnele sunt exacte!

Pasul 4G - Conectați capitalul circulant prognozat la bilanț

Pasul 4H - Conectați fondul de rulment la extrasul de numerar

# 5 - Modelare financiară în Excel - Program de amortizare

Odată cu finalizarea graficului fondului de rulment, următorul pas în această modelare financiară este proiectul Capex al Colgate și proiectarea cifrelor de amortizare și active. Colgate 2013 - 10K, Pagina 49

• Amortizarea și amortizarea nu sunt furnizate ca element rând separat; cu toate acestea, este inclus în costul vânzărilor
• În astfel de cazuri, vă rugăm să aruncați o privire la situațiile fluxurilor de numerar, unde veți găsi cheltuielile de amortizare și amortizare. De asemenea, rețineți că cifrele de mai jos sunt 1) Amortizare 2) amortizare. Deci, care este numărul de amortizare?
• Sold final pentru PPE = Sold inițial + Capex - Amortizare - Ajustare pentru vânzările de active (ecuația de bază)

Pasul 5A - Conectați cifrele vânzărilor nete din programul de amortizare

• Configurați elementele rând
• Vânzări nete de referință
• Introducerea cheltuielilor de capital din trecut
• Sosiți la Capex ca% din vânzările nete

Pasul 5B - Prognoza elementelor de cheltuieli de capital

• Pentru a prognoza cheltuielile de capital, există diverse abordări. O practică obișnuită este să privim comunicatele de presă, proiecțiile de management, MD&A pentru a înțelege viziunea companiei cu privire la cheltuielile de capital viitoare
• Dacă compania a oferit îndrumări cu privire la cheltuielile de capital viitoare, atunci putem lua aceste numere direct.
• Cu toate acestea, dacă numerele Capex nu sunt disponibile direct, atunci îl putem calcula brut folosind Capex ca% din vânzări (așa cum se face mai jos)
• Folosiți-vă judecata pe baza cunoștințelor din industrie și a altor factori rezonabili.

Pasul 5C - Informații anterioare de referință

• Vom folosi Sold final pentru PPE = Sold inițial + Capex - Depreciere - Ajustare pentru vânzările de active (ecuația de bază)
• Este complicat să se concilieze PP&E anterioare din cauza retratărilor, vânzărilor de active etc.
• Prin urmare, se recomandă să nu reconciliați EIP din trecut, deoarece poate duce la o anumită confuzie.

Politica de amortizare a Colgate

• Observăm că Colgate nu a furnizat în mod explicit o separare detaliată a activelor. În schimb, au încorporat toate activele în terenuri, clădiri, utilaje și alte echipamente
• De asemenea, durata de viață utilă pentru mașini și echipamente este asigurată la distanță. În acest caz, va trebui să facem câteva presupuneri pentru a ajunge la durata medie de viață rămasă pentru active
• De asemenea, ghidul pentru viața utilă nu este furnizat pentru „Alte echipamente”. Va trebui să estimăm durata de viață utilă pentru alte echipamente

Colgate 2013 - 10K, Pagina 55

Mai jos este descompunerea detaliilor privind proprietățile, instalațiile și echipamentele din 2012 și 2013

Colgate 2013 - 10K, Pagina 91

Pasul 5D - Estimarea destrămării instalațiilor și echipamentelor imobiliare (EIP)

• Mai întâi, găsiți ponderile activelor EIP curent (2013)
• Vom presupune că aceste ponderi ale activelor PPE 2013 vor continua în continuare
• Folosim aceste ponderile activelor pentru a calcula defalcarea cheltuielilor de capital estimate

Pasul 5E - Estimați amortizarea activelor

• Vă rugăm să rețineți că nu calculăm deprecierea terenului, deoarece terenul nu este un activ amortizabil
• Pentru estimarea deprecierii din îmbunătățirile clădirii, folosim mai întâi structura de mai jos.
• Amortizarea aici este împărțită în două părți - 1) amortizarea activului de îmbunătățiri ale clădirilor listat deja în bilanț, 2) amortizare din viitoarele îmbunătățiri ale clădirii.
• Pentru calcularea amortizării din îmbunătățirile clădirii enumerate pe activ, folosim metoda simplă de amortizare a liniei drepte.
• Pentru calcularea deprecierii viitoare, transpunem mai întâi Capex folosind funcția TRANSPOSE în Excel.
• Calculăm amortizarea din contribuțiile la active din fiecare an.
• De asemenea, deprecierea din primul an este împărțită la doi, deoarece presupunem convenția de la mijlocul anului pentru implementarea activelor.

Deprecierea totală a îmbunătățirii clădirii = amortizarea activului de îmbunătățiri a clădirii deja enumerate în bilanț + amortizarea din viitoarele îmbunătățiri ale clădirii Procesul de mai sus pentru estimarea deprecierii este utilizat pentru a calcula amortizarea 1) Echipamente și utilaje de fabricație și 2) alte echipamente prezentat mai jos.

Alte tipuri de echipamente

Amortizare totală a lui Colgate = Amortizare (îmbunătățiri ale clădirilor) + Amortizare (utilaje și echipamente) + Amortizare (echipament suplimentar) Odată ce am aflat cifrele reale de depreciere, o putem pune în ecuația BASE așa cum se arată mai jos

• Cu aceasta, obținem cifrele PP&E Ending Net pentru fiecare dintre ani

Pasul 5F - Conectați PP&E net la bilanț

# 6 - Program de amortizare

Al șaselea pas din această modelare financiară în Excel este de a prognoza amortizarea. Avem două mari categorii de luat în considerare aici - 1) Fondul comercial și 2) Alte necorporale.

Pasul 6A - Previzionarea fondului comercial

Colgate 2013 - 10K, Pagina 61

• Fondul comercial apare în bilanț atunci când o companie achiziționează o altă companie. De obicei, este complicat să proiectăm fondul comercial pentru anii următori.
• Cu toate acestea, fondul comercial este supus testelor de depreciere anual, care sunt efectuate chiar de companie. Analiștii nu sunt în măsură să efectueze astfel de teste și să pregătească estimări ale deficiențelor.
• Majoritatea analiștilor nu proiectează bunăvoință; ei păstrează acest lucru constant, ceea ce vom face și în cazul nostru.

Pasul 6B - Prognozarea altor active necorporale

• După cum sa menționat în Raportul 10K al lui Colgate, majoritatea vieții intangibile finite este legată de achiziția Sanex
• „Adăugările la necorporale” sunt, de asemenea, complicate de proiectat
• Raportul 10K al lui Colgate ne oferă detalii despre următorii cinci ani de cheltuieli cu amortizarea.
• Vom folosi aceste estimări în Modelul nostru financiar Colgate 2013 - 10K, Pagina 61

Pasul 6C - Imobilizările necorporale finale sunt legate de „Alte active necorporale”.

Pasul 6D - conectați amortizarea și amortizarea la extrasele de fluxuri de numerar

Pasul 6E - Legați capexul și adăugarea la intangibile la situațiile fluxurilor de numerar

# 7 - Alt program pe termen lung

Următorul pas din această modelare financiară este pregătirea Celuilalt Program pe termen lung. Acesta este momentul în care ne pregătim pentru „resturile” care nu au factori specifici pentru prognoză. În cazul Colgate, celelalte elemente pe termen lung (resturi) erau impozite pe profit amânat (pasiv și active), alte investiții și alte pasive.

Pasul 7A - Consultați datele istorice din bilanț

De asemenea, calculați modificările acestor elemente.

Pasul 7B - Prognoza activelor și pasivelor pe termen lung

• Păstrați elementele pe termen lung constante pentru anii proiectați în cazul în care nu există drivere vizibile
• Conectați elementele prognozate pe termen lung la bilanț, așa cum se arată mai jos

Pasul 7C - Trimiteți alte elemente pe termen lung la bilanț

Pasul 7D - Conectați elementele pe termen lung la extrasul de numerar

Vă rugăm să rețineți că, dacă am păstrat activele și pasivele pe termen lung ca fiind constante, atunci modificarea care curge în situația fluxului de numerar ar fi zero.

# 8 - Modelare financiară în Excel - Completarea declarației de venit

• Înainte de a trece mai departe în această modelare financiară bazată pe Excel, ne vom întoarce și vom relua contul de venit
• Populați acțiunile medii ponderate de bază istorice și numărul mediu ponderat diluat de acțiuni
• Aceste cifre sunt disponibile în raportul 10K al lui Colgate

Pasul 8A - Referința acțiunilor de bază și a acțiunilor diluate

În această etapă, presupuneți că numărul viitor de acțiuni primare și diluate va rămâne același ca în 2013.

Pasul 8B - Calculați câștigurile de bază și diluate pe acțiune.

Cu aceasta, suntem pregătiți să trecem la următorul program, adică la Programul de capitaluri proprii ale acționarilor.

# 9 - Modelare financiară - Programul de capital propriu al acționarilor

Următorul pas al acestei formări de modelare financiară în Excel este de a analiza programul de capital propriu al acționarilor. Primar Obiectivul acestui program este de a proiectelor legate de elemente de capital , cum ar fi acționarului capitalului propriu, dividende, răscumpărare a acțiunilor, opțiunea Incasari etc. 10K raportul Colgate ne oferă detalii cu privire la acțiuni comune și activitățile de trezorerie stoc în ultimii ani, așa cum se arată mai jos . Colgate 2013 - 10K, Pagina 68

Pasul 9A - Acțiune de răscumpărare: Populați numerele istorice

• Din punct de vedere istoric, Colgate a răscumpărat acțiuni, așa cum putem vedea programul de mai sus.
• Populați acțiunile Colgate răscumpărate (milioane) în foaia Excel.
• Conectați EPS-ul istoric diluat din contul de profit și pierdere
• Suma istorică răscumpărată ar trebui să fie menționată din situațiile fluxurilor de numerar.

De asemenea, aruncați o privire asupra răscumpărării acțiunilor accelerate.

Pasul 9B - Acțiune de răscumpărare: calculați multiplu PE (multiplu EPS)

• Calculați prețul mediu implicit la care Colgate a efectuat în mod istoric răscumpărarea acțiunilor. Acesta este calculat ca suma răscumpărată / numărul de acțiuni.
• Calculați multiplul PE = Prețul acțiunii implicite / EPS

Pasul 9C - Acțiune răscumpărată: Găsirea acțiunii Colgate răscumpărate

Colgate nu a făcut niciun anunț oficial cu privire la câte acțiuni intenționează să răscumpere. Singurele informații pe care le raportează 10.000 de acțiuni sunt că au autorizat o răscumpărare de până la 50 de milioane de acțiuni. Colgate 2013 - 10K, Pagina 35

• Pentru a găsi numărul de acțiuni răscumpărate, trebuie să ne asumăm suma de răscumpărare a acțiunilor. Pe baza valorii istorice a răscumpărării, am luat acest număr ca 1.500 milioane USD pentru toți anii următori.
• Pentru a găsi numărul acțiunilor răscumpărate, avem nevoie de prețul implicit al acțiunilor proiectat pentru potențialul răscumpărare.
• Prețul efectiv al acțiunii = presupus EPS multiplex PE.
• Viitorul răscumpărare multiplu PE poate fi presupus pe baza tendințelor istorice. Observăm că Colgate a răscumpărat acțiuni la un interval mediu PE de 17x - 25x
• Mai jos este instantaneul de la Reuters care ne ajută să validăm gama PE pentru Colgate

www.reuters.com

• În cazul nostru, am presupus că toate răscumpărările viitoare ale Colgate vor fi la un multiplu PE de 19x.
• Folosind PE de 19x, putem găsi prețul implicit = EPS x 19
• Acum că am găsit prețul implicit, putem vedea numărul de acțiuni răscumpărate = suma utilizată pentru răscumpărare / preț implicit.

Pasul 9D - Opțiuni stoc: Populați date istorice

• Din rezumatul acțiunilor ordinare și al capitalului propriu, știm numărul de opțiuni exercitate în fiecare an.

În plus, avem și încasările din opțiuni din situațiile fluxurilor de numerar (aprox)

• Cu aceasta, ar trebui să putem găsi un preț de atac eficient.

Colgate 2013 - 10K, Pagina 53

De asemenea, rețineți că opțiunile pe acțiuni au condiții contractuale de șase ani și se învestesc pe trei ani. Colgate 2013 - 10K, Pagina 69

Cu aceste date, completăm datele Opțiuni conform celor de mai jos. Remarcăm, de asemenea, că prețul de grevă mediu ponderat al opțiunilor pe acțiuni pentru 2013 a fost de 42 USD, iar numărul de opțiuni exercitabile a fost de 24,151 milioane Colgate 2013 - 10K, Pagina 70

Pasul 9E - Opțiuni stoc: găsiți încasările din opțiune.

Punând aceste numere în datele opțiunilor noastre de mai jos, observăm că încasările din opțiune sunt de 1,014 miliarde USD.

Pasul 9F - Opțiuni stoc: Date privind unitatea de stoc restricționată de prognoză

În plus față de opțiunile pe acțiuni, există unități de stoc restricționate acordate angajaților cu perioada medie ponderată de 2,2 ani Colgate 2013 - 10K, Pagina 81

Popularea acestor date în setul de date Opțiuni Din motive de simplitate, nu am proiectat emiterea de opțiuni (știu că aceasta nu este presupunerea corectă; totuși, din cauza lipsei de date, nu mai iau mai multe probleme cu opțiunile. Abia le-am luat ca zero, așa cum este evidențiat în zona gri de mai sus. În plus, unitățile de stocuri restricționate sunt proiectate să fie de 2,0 milioane în viitor.

De asemenea, aruncați o privire la metoda stocului de trezorerie.

Pasul 9G- Dividende: prognozați dividendele

• Prognoza dividendelor estimate utilizând rata de plată a dividendelor.
• Suma fixă ​​a dividendului se plătește pe acțiune
• Din rapoartele 10K, extragem toate informațiile anterioare privind dividendele.
• Cu informațiile privind dividendele plătite, putem afla raportul de plată a dividendelor = Totalul dividendelor plătite / Venitul net.
• Am calculat raportul de plată a dividendelor pentru Colgate așa cum se vede mai jos - Observăm că raportul de plată a dividendelor a fost în general cuprins între 50% și 60%. Să presupunem o rată de plată a dividendelor de 55% în anii următori.
• De asemenea, putem lega Venitul net proiectat din contul de profit și pierdere.
• Folosind atât venitul net proiectat, cât și raportul de plată a dividendelor, putem găsi dividendele totale plătite.

Pasul 8H - Previzualizarea contului de capitaluri proprii în întregime

Cu prognoza de răscumpărare a acțiunilor, încasările din opțiuni și dividendele plătite, suntem pregătiți să completăm Programul de capital propriu al acționarilor. Conectați toate acestea pentru a găsi Soldul de capitaluri proprii pentru fiecare an, după cum se arată mai jos.

Pasul 9I - Conectarea capitalului propriu final al bilanțului

Pasul 9J - Conectați dividende, răscumpărare de acțiuni și opțiuni înregistrează la CF

# 10 - Programul de acțiuni restante

Următorul pas în această modelare financiară online în cursul de formare Excel este de a analiza programul de susținere a acțiunilor. Rezumatul programului de acțiuni restante

• Acțiuni de bază - reale și medii
• Capturați efectele anterioare ale opțiunilor și convertibilelor, după caz
• Acțiuni diluate - medie
• Acțiuni de referință răscumpărate și acțiuni noi din opțiuni exercitate
• Calculați procentajul brut estimat (real)
• Calculați acțiunile medii de bază și diluate
• Referința acțiunilor proiectate la Declarația de rezultate (reamintesc Declarația de venit Construiți!)
• Introduceți informațiile restante despre acțiunile istorice
• Notă : acest program este în mod obișnuit integrat cu Programul de capitaluri proprii

Pasul 10A - Introduceți numerele istorice din raportul 10K

• Acțiunile emise (realizarea efectivă a opțiunilor) și acțiunile răscumpărate pot fi menționate din Programul de capital propriu al acționarilor
• Intrarea a ponderat un număr mediu de acțiuni și efectul opțiunilor pe acțiuni pentru anii istorici.

Pasul 10B - Conectați emisiile și răscumpărările de acțiuni din programul de acțiuni.

Acțiuni de bază (Încheiere) = Acțiuni de bază (Început) + Emisiuni de acțiuni - Acțiuni răscumpărate.

Pasul 10C - Găsiți acțiunile medii ponderate de bază,

• găsim în medie doi ani, după cum se arată mai jos.
• De asemenea, adăugați efectul opțiunilor și al unităților de acțiuni restricționate (la care se face referire din programul de capital propriu al acționarului) pentru a găsi acțiunile medii ponderate diluate.

Pasul 10D - Conectați acțiunile ponderate de bază și diluate la Contul de profit și pierdere

• Acum că am calculat acțiunile medii ponderate diluate, este timpul să le actualizăm la fel în contul de profit și pierdere.
• Legați acțiunile medii ponderate diluate prognozate în circulație la Contul de profit și pierdere, după cum se arată mai jos

Cu aceasta, completăm programul și timpul necesar pentru trecerea la următorul set de declarații.

# 11 - Completarea extraselor de fluxuri de numerar

Este important pentru noi să completăm complet situațiile fluxurilor de trezorerie înainte de a trece la următorul și ultimul nostru grafic din această modelare financiară, adică programul datoriilor. Până la această etapă, există doar câteva lucruri incomplete

• Situația veniturilor - cheltuielile cu dobânzile / veniturile sunt incomplete în această etapă
• Bilanț - elementele de numerar și datorii sunt incomplete în această etapă

Pasul 11A - Calculați fluxul de numerar pentru activitățile de finanțare

De asemenea, consultați Fluxul de numerar din Finanțare

Pasul 11B - Găsiți o creștere (scădere) netă în numerar și echivalente de numerar

Pasul 11C = Completați situațiile fluxurilor de numerar

Găsiți numerarul și echivalentele de numerar de la sfârșitul anului la sfârșitul anului.

Pasul 11D - Conectați numerarul și echivalentele de numerar la bilanț.

Acum suntem pregătiți să ne ocupăm de ultimul și ultimul nostru program, adică Programul de datorii și dobânzi

# 12- Modelarea financiară în Excel - Programul datoriei și dobânzii

Următorul pas din această modelare financiară online este completarea programului de datorii și dobânzi. Rezumatul datoriei și dobânzii - Program

Gradul 12A - Configurați un program de datorii

• Consultați fluxul de numerar disponibil pentru finanțare
• Faceți referință la toate sursele de capitaluri proprii și utilizările de numerar

Pasul 12B - Calculați fluxul de numerar din rambursarea datoriilor

• Consultați soldul de numerar inițial din bilanț
• Deduceți un sold minim de numerar. Am presupus că Colgate ar dori să păstreze un minim de 500 de milioane de dolari în fiecare an.

Omiteți emiterea / rambursarea datoriilor pe termen lung, numerar disponibil pentru secțiunea Revolving Credit Facility și Revolver pentru moment Din raportul Colgate 10K; menționăm detaliile disponibile despre Facilitatea de credit revoluționată Colgate 2013 - 10K, Pagina 35

De asemenea, în informațiile suplimentare privind datoriile sunt furnizate rambursările datoriilor pe termen lung. Colgate 2013 - 10K, Pagina 36

Pasul 12C - Calculați datoria pe termen lung.

Folosim programul de rambursare a datoriilor pe termen lung furnizat mai sus și calculăm soldul final al rambursărilor datoriilor pe termen lung.

Pasul 12D - Legați rambursările datoriilor pe termen lung.

Pasul 12E - Calculați împrumuturile / reducerile discreționare.

Folosind formula de măturare a numerarului, așa cum se arată mai jos, calculați împrumuturile / plățile discreționare.

Pasul 12F - Calculați cheltuielile cu dobânzile din datoria pe termen lung

• Calculați soldul mediu pentru facilitatea de credit revoluționară și datoria pe termen lung
• Faceți o ipoteză rezonabilă pentru o rată a dobânzii pe baza informațiilor furnizate în raportul 10K
• Calculați cheltuielile totale cu dobânzile = soldul mediu al datoriei x rata dobânzii

Găsiți cheltuielile totale de dobândă = dobândă (facilitate de credit revoluționară) + dobândă (datorie pe termen lung)

Pasul 12G - Datorie principal Link și retrageri Revolver pentru fluxurile de numerar

Pasul 12H - Curent de referință și pe termen lung la bilanț

• Demarcați partea actuală a datoriei pe termen lung și a datoriei pe termen lung, după cum se arată mai jos

• Conectați facilitatea de credit revoluționară, datoria pe termen lung și partea actuală a datoriei pe termen lung cu bilanțul contabil

Pasul 12I - Calculați venitul din dobânzi utilizând soldul mediu de numerar

Pasul 12J - Conectați cheltuielile cu dobânzile și veniturile din dobânzi la situația veniturilor

Efectuați verificarea bilanțului: Total active = Datorii + Capitaluri proprii

Pasul 12K - Auditați bilanțul contabil

Dacă există vreo discrepanță, atunci trebuie să audităm modelul și să verificăm dacă există erori de legătură

Curs de modelare financiară recomandat

Sper că v-a plăcut Ghidul Excel gratuit de modelare financiară. Dacă doriți să învățați modelarea financiară în Excel prin prelegerile noastre video de experți, puteți consulta, de asemenea, instruirea noastră în domeniul băncilor de investiții. Acesta este în principal pachet de instruire de 99 de cursuri în domeniul investițiilor bancare. Acest curs începe cu elementele de bază și vă duce la nivelul avansat de locuri de muncă în investiții bancare. Acest curs este împărțit în cinci părți -

• Partea 1 - Instruire bancară de investiții - Cursuri de bază
  (26 de cursuri)
• Partea 2 - Instruire avansată privind modelarea bancară a investițiilor
  (20 de cursuri)
• Partea 3 - Suplimente bancare de investiții
  (13 cursuri)
• Partea 4 - Cursuri ale Fundației Bancare de Investiții
  (23 de cursuri)
• Partea 5 - Abilități ușoare pentru bancherii de investiții
  (17 cursuri)

Descărcare modele financiare

• Modelul financiar Alibaba
• Caseta Model financiar IPO
• Șabloane de modelare financiară
• Curs de modelare financiară bancară

Ce urmează?

Dacă ați aflat ceva nou sau v-a plăcut această modelare financiară bazată pe Excel, lăsați un comentariu mai jos. Spune-mi ce crezi. Multe mulțumiri și aveți grijă. Învățare fericită!