Modelowanie finansowe w programie Excel (bezpłatny przewodnik krok po kroku + szablon)

Modelowanie finansowe w programie Excel to proces budowania modelu finansowego reprezentującego transakcję, operację, fuzję, przejęcie, informacje finansowe w celu przeanalizowania, w jaki sposób zmiana jednej zmiennej może wpłynąć na ostateczny zwrot, tak aby podjąć decyzję dotyczącą jednej lub więcej wyżej wymienione transakcje finansowe.

Co to jest modelowanie finansowe w programie Excel?

Modelowanie finansowe w programie Excel jest w całej sieci i napisano wiele o nauce modelowania finansowego, jednak większość elementów szkolenia z zakresu modelowania finansowego jest dokładnie taka sama. Wykracza to poza zwykły bełkot i analizuje praktyczne modelowanie finansowe używane przez bankierów inwestycyjnych i analityków badawczych.

W tym przewodniku Excela dotyczącym bezpłatnego modelowania finansowego wezmę przykład Colgate Palmolive i przygotuję od podstaw w pełni zintegrowany model finansowy.

Ten przewodnik ma ponad 6000 słów i zajęło mi 3 tygodnie. Zapisz tę stronę do wykorzystania w przyszłości i nie zapomnij jej udostępnić :-)

NAJWAŻNIEJSZE - Pobierz szablon programu Excel do modelowania finansowego Colgate i postępuj zgodnie z instrukcjami

Pobierz szablon modelu finansowego Colgate

Naucz się modelowania finansowego krok po kroku w programie Excel

Modelowanie finansowe w szkoleniu dotyczącym programu Excel - przeczytaj najpierw

Krok 1 - Pobierz szablon modelu finansowego Colgate. Będziesz używać tego szablonu do samouczka

Pobierz model finansowy Colgate

Krok 2 - Pamiętaj, że otrzymasz dwa szablony - 1) nierozwiązany model finansowy Colgate Palmolive 2) rozwiązany model finansowy Colgate Palmolive

Krok 3 - Będziesz pracować nad szablonem nierozwiązanego modelu finansowego Colgate Palmolive . Postępuj zgodnie z instrukcjami krok po kroku, aby przygotować w pełni zintegrowany model finansowy.

Krok 4 - Przyjemnej nauki!

Spis treści

Stworzyłem łatwy w nawigacji spis treści, abyś mógł wykonać to modelowanie finansowe

# 1 - Model finansowy Colgate - historyczny
# 2 - Analiza współczynników Colgate Palmolive
# 3 - Projekcja rachunku zysków i strat
# 4- Harmonogram kapitału obrotowego
# 5 - Harmonogram amortyzacji
# 6 - Harmonogram amortyzacji
# 7 - Inny harmonogram długoterminowy
# 8 - Wypełnianie rachunku zysków i strat
# 9 - Harmonogram kapitałowy akcjonariuszy
# 10 - Pozostały harmonogram akcji
# 11 - Wypełnianie wyciągów z przepływów pieniężnych
# 12- Zalecany harmonogram zadłużenia i odsetek
Kurs modelowania finansowego
Darmowe modele finansowe

Jeśli nie masz doświadczenia z modelowaniem finansowym, zapoznaj się z tym przewodnikiem na temat tego, co to jest modelowanie finansowe?

Jak zbudować model finansowy w Excelu?

Przyjrzyjmy się, jak budowany jest model finansowy od podstaw. Ten szczegółowy przewodnik po modelowaniu finansowym zawiera instrukcje krok po kroku dotyczące tworzenia modelu finansowego. Podstawowym podejściem przyjętym w tym przewodniku po modelowaniu finansowym jest modułowość. Podejście modułowe zasadniczo oznacza, że tworzymy podstawowe zestawienia, takie jak rachunek zysków i strat, bilans i przepływy pieniężne, używając różnych modułów / harmonogramów. Głównym celem jest przygotowanie każdego oświadczenia krok po kroku i połączenie wszystkich wspierających harmonogramów z podstawowymi oświadczeniami po zakończeniu. Rozumiem, że na razie może to nie być jasne, jednak zdasz sobie sprawę, że jest to bardzo łatwe w miarę postępów. Poniżej można zobaczyć różne harmonogramy / moduły modelowania finansowego -

Zwróć uwagę na następujące -

Podstawowe wyciągi to rachunek zysków i strat, bilans i przepływy pieniężne.
Dodatkowe harmonogramy to harmonogram amortyzacji, harmonogram kapitału obrotowego, harmonogram dotyczący wartości niematerialnych, harmonogramu kapitałów udziałowców, harmonogram innych pozycji długoterminowych, harmonogram zadłużenia itp.
Dodatkowe harmonogramy są powiązane z podstawowymi oświadczeniami po ich ukończeniu
W tym przewodniku po modelowaniu finansowym zbudujemy od podstaw zintegrowany model finansowy Colgate Palmolive krok po kroku.

# 1 - Modelowanie finansowe w programie Excel - Projektuj historie

Pierwszym krokiem w Przewodniku po modelowaniu finansowym jest przygotowanie danych historycznych.

Krok 1A - Pobierz raporty 10K Colgate

„Modele finansowe są przygotowywane w programie Excel, a pierwsze kroki należy rozpocząć od poznania, jak branża radziła sobie w ostatnich latach. Zrozumienie przeszłości może dostarczyć nam cennych spostrzeżeń związanych z przyszłością firmy. Dlatego pierwszym krokiem jest pobranie wszystkich danych finansowych firmy i umieszczenie ich w arkuszu Excela. W przypadku Colgate Palmolive można pobrać roczne raporty Colgate Palmolive z sekcji Relacji z Inwestorami. Po kliknięciu „Raport roczny” pojawi się okno pokazane poniżej -

Krok 1B - Utwórz arkusz historycznych sprawozdań finansowych

Jeśli pobierzesz 10 tys. Z 2013 r., Zauważysz, że dostępne są tylko dane sprawozdań finansowych z dwóch lat. Jednak dla celów modelowania finansowego w programie excel zalecanym zestawem danych jest sprawozdanie finansowe z ostatnich 5 lat. Pobierz raport roczny z ostatnich 3 lat i wypełnij dane historyczne.
Często zadania te wydają się zbyt nudne i żmudne, ponieważ sformatowanie i umieszczenie programu Excel w pożądanym formacie może zająć dużo czasu i energii.
Nie należy jednak zapominać, że jest to praca, którą musisz wykonać tylko raz dla każdej firmy, a także wypełnienie danych historycznych pomaga analitykowi zrozumieć trendy i sprawozdanie finansowe
Więc nie pomijaj tego, pobierz dane i uzupełnij dane (nawet jeśli uważasz, że to robota osła ;-))

Jeśli chcesz pominąć ten krok, możesz bezpośrednio pobrać tutaj model historyczny Colgate Palmolive.

Rachunek zysków i strat Colgate z wypełnieniem historycznym

Dane historyczne bilansu Colgate

# 2 - Analiza współczynników

Drugim krokiem w modelowaniu finansowym w programie Excel jest wykonanie analizy współczynników.

Kluczem do nauki modelowania finansowego w programie Excel jest umiejętność przeprowadzania analizy fundamentalnej. Jeśli analiza fundamentalna lub analiza współczynników jest dla Ciebie czymś nowym, polecam poczytać trochę w Internecie. Zamierzam przeprowadzić dogłębną analizę współczynników w jednym z moich nadchodzących postów, jednak oto krótkie podsumowanie wskaźników Colgate Palmolive

WAŻNE - Zwróć uwagę, że zaktualizowałem analizę współczynników Colgate w osobnym poście. Proszę spojrzeć na tę kompleksową analizę wskaźników.

Krok 2A - Analiza pionowa Colgate

W rachunku zysków i strat analiza wertykalna jest uniwersalnym narzędziem pomiaru względnych wyników firmy z roku na rok pod względem kosztów i rentowności. Powinien być zawsze uwzględniany jako część każdej analizy finansowej. W tym przypadku wartości procentowe są obliczane w odniesieniu do sprzedaży netto, którą uznaje się za 100%. Ta analiza pionowa w rachunku zysków i strat jest często nazywana analizą marży, ponieważ daje różne marże w stosunku do sprzedaży.

Wyniki analizy pionowej

Marża zysku wzrosła o 240 punktów bazowych z 56,2% w 2007 r. Do 58,6% w 2013 r. Wynika to przede wszystkim ze spadku kosztów sprzedaży.
Zysk operacyjny lub EBIT również wykazały poprawę marż, zwiększając tym samym z 19,7% w 2007 r. Do 22,4% w 2012 r. (Wzrost o 70 punktów bazowych). Wynikało to ze zmniejszenia kosztów ogólnych i administracyjnych sprzedaży. Należy jednak zwrócić uwagę, że marże EBIT spadły w 2013 roku do 20,4% w wyniku wzrostu „Pozostałych kosztów”. Sprawdź również różnicę między EBIT a EBITDA
Marża zysku netto wzrosła z 12,6% w 2007 r. Do 14,5% w 2012 r. Jednak marża zysku w 2013 r. Spadła do 12,9%, głównie z powodu wzrostu „pozostałych kosztów”.
Zysk na akcję stale wzrastał od roku 2007 do roku obrotowego 2012. Jednak EPS w całym 2013 roku nieznacznie spadł
Należy również zwrócić uwagę, że amortyzacja i amortyzacja są przedstawione oddzielnie w rachunku zysków i strat. Jest wliczony w koszt sprzedaży

Krok 2B - Analiza pozioma Colgate

Analiza pozioma to technika używana do oceny trendów w czasie poprzez obliczanie procentowych wzrostów Excela lub spadków w stosunku do roku bazowego. Zapewnia analityczne powiązanie między rachunkami obliczonymi w różnych terminach przy użyciu waluty o różnych siłach nabywczych. W efekcie ta analiza indeksuje rachunki i porównuje ich ewolucję w czasie. Podobnie jak w przypadku metodologii analizy pionowej, pojawią się problemy, które należy zbadać i uzupełnić innymi technikami analizy finansowej. Nacisk kładziony jest na poszukiwanie objawów problemów, które można zdiagnozować za pomocą dodatkowych technik.

Przyjrzyjmy się horyzontalnej analizie Colgate

Wyniki analizy poziomej

Widzimy, że sprzedaż netto wzrosła o 2,0% w 2013 roku.
Zwróć także uwagę na trend w kosztach sprzedaży, widzimy, że nie wzrosły one w takim samym stosunku, jak sprzedaż.
Te obserwacje są niezwykle przydatne podczas modelowania finansowego w programie Excel

Krok 2C - Wskaźniki płynności Colgate

Wskaźniki płynności mierzą relację bardziej płynnych aktywów przedsiębiorstwa (najłatwiej wymienialnych na gotówkę) do zobowiązań krótkoterminowych. Najpopularniejszymi wskaźnikami płynności są: Wskaźnik bieżącej płynności Wskaźnik testu kwasowości (lub szybkie aktywa) Wskaźnik gotówki
Wskaźniki rotacji, takie jak rotacja należności, rotacja zapasów i rotacja zobowiązań

Najważniejsze dane dotyczące wskaźników płynności

Obecny stosunek Colgate jest większy niż 1,0 we wszystkich latach. Oznacza to, że aktywa obrotowe są większe niż bieżące zobowiązania i być może Colgate ma wystarczającą płynność
Wskaźnik płynności Colgate zawiera się w przedziale 0,6-0,7, co oznacza, że papiery wartościowe Colgates Cash and Marketable mogą spłacić nawet 70% bieżących zobowiązań. Wygląda to na rozsądną sytuację dla Colgate.
Cykl ściągalności gotówki uległ skróceniu z 43 dni w 2009 roku do 39 dni w 2013 roku. Wynika to przede wszystkim ze skrócenia okresu spłaty należności.

Zapoznaj się również z tym szczegółowym artykułem na temat cyklu konwersji gotówki

Krok 2D - Wskaźniki rentowności operacyjnej Colgate

Wskaźniki rentowności określające zdolność firmy do generowania zysków w odniesieniu do sprzedaży, aktywów i kapitału własnego

Najważniejsze informacje - wskaźniki rentowności Colgate

Jak widać z powyższej tabeli, Colgate ma ROE bliski 100%, co oznacza duże zwroty dla posiadaczy akcji.

Krok 2E - Analiza ryzyka Colgate

Poprzez Analizę Ryzyka staramy się ocenić, czy firmy będą w stanie spłacić swoje krótko- i długoterminowe zobowiązania (zadłużenie). Obliczamy wskaźniki dźwigni, które koncentrują się na wystarczalności aktywów lub generowaniu z aktywów. Wskaźniki, które są brane pod uwagę, to

Wskaźnik zadłużenia do kapitału własnego
Wskaźnik zadłużenia
Wskaźnik pokrycia odsetek
Wskaźnik zadłużenia do kapitału systematycznie wzrastał do wyższego poziomu 2,23x. Oznacza to zwiększoną dźwignię finansową i ryzyko na rynku
Jednak wskaźnik pokrycia odsetek jest bardzo wysoki, co oznacza mniejsze ryzyko niewypłacalności odsetek.

# 3 - Modelowanie finansowe w programie Excel - zaprojektuj rachunek zysków i strat

Trzecim krokiem w modelowaniu finansowym jest prognozowanie rachunku zysków i strat, w którym zaczniemy od modelowania pozycji sprzedaży lub przychodów.

Krok 3A - Prognozy przychodów

Dla większości firm przychody są podstawową siłą napędową wyników gospodarczych. Niezwykle ważny jest dobrze zaprojektowany i logiczny model przychodów, który dokładnie odzwierciedla rodzaj i kwoty przepływów dochodów. Istnieje tyle sposobów projektowania harmonogramu przychodów, ile jest firm. Niektóre typowe typy to:

Wzrost sprzedaży: Założenie wzrostu sprzedaży w każdym okresie określa zmianę w stosunku do poprzedniego okresu. Jest to prosta i powszechnie stosowana metoda, ale nie daje wglądu w składniki ani dynamikę wzrostu.
Efekty inflacyjne i wolumen / mix: Zamiast prostego założenia dotyczącego wzrostu stosuje się czynnik inflacji cenowej i czynnik wolumenu. To przydatne podejście umożliwia modelowanie kosztów stałych i zmiennych w przedsiębiorstwach wieloproduktowych i uwzględnia zmiany cen w stosunku do wolumenu.
Wielkość jednostkowa, zmiana wielkości, średnia cena i zmiana ceny: ta metoda jest odpowiednia dla firm, które mają prosty asortyment produktów; pozwala na analizę wpływu kilku kluczowych zmiennych.
Wielkość rynku i wzrost dolara: udział w rynku i zmiana udziału - przydatne w przypadkach, gdy dostępne są informacje na temat dynamiki rynku i gdy te założenia mogą mieć fundamentalne znaczenie dla podjęcia decyzji. Na przykład branża telekomunikacyjna
Wielkość rynku jednostkowego i wzrost: jest to bardziej szczegółowe niż w poprzednim przypadku i przydatne, gdy ceny na rynku są kluczową zmienną. (Na przykład dla firmy stosującej strategię obniżania ceny lub najlepszego w swojej klasie gracza niszowego o najwyższej cenie) np. Rynek samochodów luksusowych
Wydajność wolumenu, wskaźnik wykorzystania mocy produkcyjnych i średnia cena: Te założenia mogą być ważne dla firm, w których moce produkcyjne są ważne dla podjęcia decyzji. (Na przykład przy zakupie dodatkowej mocy lub w celu ustalenia, czy rozbudowa wymagałaby nowych inwestycji).
Dostępność i ceny produktów
Przychody były napędzane przez inwestycje w kapitał, marketing lub badania i rozwój
Przychody oparte na zainstalowanej bazie (ciągła sprzedaż części, materiałów jednorazowego użytku, usług i dodatków itp.). Przykłady obejmują klasyczne firmy z żyletkami i firmy takie jak komputery, w których ważna jest sprzedaż usług, oprogramowania i uaktualnień. Kluczowe znaczenie ma modelowanie zainstalowanej bazy (nowe dodatki do bazy, ścieranie się bazy, stałe przychody na klienta itp.).
Oparte na pracownikach: na przykład przychody profesjonalnych firm usługowych lub firm zajmujących się sprzedażą, takich jak brokerzy. Modelowanie powinno skupiać się na zatrudnieniu netto, przychodach na pracownika (często na podstawie rozliczanych godzin). Bardziej szczegółowe modele będą obejmowały staż pracy i inne czynniki wpływające na ceny.
Na podstawie sklepu, obiektu lub materiału kwadratowego: firmy zajmujące się handlem detalicznym są często modelowane na podstawie sklepów (stare sklepy plus nowe sklepy w każdym roku) i przychodu na sklep.
Oparte na współczynniku obłożenia: to podejście ma zastosowanie do linii lotniczych, hoteli, kin i innych firm o niskich kosztach krańcowych.

Prognozowanie przychodów Colgate

Przyjrzyjmy się teraz raportowi Colgate 10K 2013. Zauważmy, że w rachunku zysków i strat, Colgate nie dostarczył informacji o segmentach, jednak, jak kawałek dodatkowe informacje, Colgate przedstawił kilka szczegółów segmentów na stronie 87 Source - Colgate 2013 - 10K, Strona 86

Ponieważ nie posiadamy żadnych dalszych informacji na temat segmentów, na podstawie dostępnych danych będziemy prognozować przyszłą sprzedaż Colgate. Do sporządzenia prognoz wykorzystamy podejście wzrostu sprzedaży we wszystkich segmentach. Zobacz poniższe zdjęcie. Obliczyliśmy roczną stopę wzrostu dla każdego segmentu. Teraz możemy założyć procentowy wzrost sprzedaży w oparciu o historyczne trendy i przewidzieć przychody w każdym segmencie. Całkowita sprzedaż netto to suma segmentów Doustna, Opieka osobista i domowa oraz Żywienie zwierząt.

Krok 3B - Prognozy kosztów

Procent przychodów: prosty, ale nie oferuje wglądu w jakąkolwiek dźwignię finansową (ekonomia skali lub obciążenie kosztami stałymi
Koszty inne niż amortyzacja jako procent przychodów i amortyzacja według oddzielnego harmonogramu: To podejście jest w rzeczywistości minimalnym akceptowalnym w większości przypadków i pozwala tylko na częściową analizę dźwigni operacyjnej.
Koszty zmienne oparte na przychodach lub wolumenie, koszty stałe oparte na trendach historycznych i amortyzacja z oddzielnego harmonogramu: Podejście to jest minimum niezbędnym do analizy wrażliwości rentowności w oparciu o wiele scenariuszy przychodów

Prognozy kosztów dla Colgate

Przy prognozowaniu kosztów pomocna będzie wcześniejsza analiza pionowa. Przyjrzyjmy się ponownie analizie pionowej -

Ponieważ prognozowaliśmy już sprzedaż, wszystkie inne koszty stanowią niektóre marże tej sprzedaży.
Podejście polega na przyjęciu wytycznych z historycznych marż kosztów i wydatków, a następnie prognozowaniu przyszłej marży.
Na przykład koszt sprzedaży mieścił się w przedziale 41% -42% przez ostatnie 5 lat. Na tej podstawie możemy spojrzeć na prognozowanie marż.
Podobnie, koszty sprzedaży, koszty ogólne i administracyjne były w przeszłości w przedziale 34% -36%. Na tej podstawie możemy przyjąć przyszłą marżę z kosztów sprzedaży, kosztów ogólnych i administracyjnych. Podobnie możemy przejść do innego zestawu wydatków.

Korzystając z powyższych marginesów, możemy znaleźć rzeczywiste wartości na podstawie obliczeń wstecznych.

Do obliczenia rezerwy na podatki posługujemy się założeniem efektywnej stawki podatkowej

Należy również zauważyć, że nie wypełniamy wiersza „Koszty z tytułu odsetek (dochód)”, ponieważ rachunek zysków i strat zostanie ponownie wystawiony na późniejszym etapie.
Koszty odsetek i dochody z odsetek.
Nie naliczyliśmy również amortyzacji, która została już uwzględniona w koszcie sprzedaży.
To uzupełnia rachunek zysków i strat (przynajmniej na razie!)

# 4- Modelowanie finansowe - harmonogram kapitału obrotowego

Teraz, gdy ukończyliśmy rachunek zysków i strat, czwartym krokiem w modelowaniu finansowym jest przyjrzenie się harmonogramowi kapitału obrotowego.

Poniżej znajdują się kroki, których należy przestrzegać w przypadku harmonogramu kapitału obrotowego

Krok 4A - Połącz sprzedaż netto i koszt sprzedaży

Krok 4B - Odnieś się do danych bilansowych dotyczących kapitału obrotowego

Odwołaj się do przeszłych danych z bilansu
Oblicz kapitał obrotowy netto
Dojdź do wzrostu / spadku kapitału obrotowego
Należy pamiętać, że nie uwzględniliśmy zadłużenia krótkoterminowego oraz środków pieniężnych i ich ekwiwalentów w kapitale obrotowym. Oddzielnie zajmiemy się długiem oraz środkami pieniężnymi i ich ekwiwalentami.

Krok 4C - Oblicz wskaźniki obrotów

Oblicz historyczne wskaźniki i procenty
Użyj salda końcowego lub średniego
Oba są dopuszczalne, o ile zachowana jest długa konsystencja

Krok 4D - Wypełnij założenia dotyczące przyszłych pozycji kapitału obrotowego

Pewne pozycje bez oczywistego powodu są zwykle zakładane w stałych ilościach
Upewnij się, że założenia są rozsądne i zgodne z biznesem

Krok 4E - P rojektuj przyszłe salda kapitału obrotowego

Krok 4F - Oblicz zmiany w kapitale obrotowym

Osiągaj przepływy pieniężne na podstawie poszczególnych pozycji
Upewnij się, że znaki są dokładne!

Krok 4G - Połącz prognozowany kapitał obrotowy z bilansem

Krok 4H - Połącz kapitał obrotowy z zestawieniem przepływów pieniężnych

# 5 - Modelowanie finansowe w programie Excel - harmonogram amortyzacji

Po ukończeniu harmonogramu kapitału obrotowego, kolejnym krokiem w tym modelowaniu finansowym jest projekt nakładów inwestycyjnych Colgate i prognozowanie wartości amortyzacji i aktywów. Colgate 2013 - 10 tys., Strona 49

Amortyzacja nie jest przedstawiana jako oddzielna pozycja, jednak jest ujęta w koszcie własnym
W takich przypadkach należy spojrzeć na zestawienia przepływów pieniężnych, w których znajdują się koszty amortyzacji i amortyzacji. Należy również pamiętać, że poniższe liczby to 1) Amortyzacja 2) amortyzacja. Więc jaki jest numer amortyzacji?
Saldo końcowe dla PPE = Saldo początkowe + nakłady inwestycyjne - Amortyzacja - Korekta z tytułu sprzedaży aktywów (równanie PODSTAWOWE)

Krok 5A - Połącz dane dotyczące sprzedaży netto w harmonogramie amortyzacji

Skonfiguruj elementy zamówienia
Referencyjna sprzedaż netto
Wprowadź wcześniejsze wydatki kapitałowe
Przybądź do Capex jako% sprzedaży netto

Krok 5B - Prognozowanie pozycji wydatków kapitałowych

Do prognozowania wydatków kapitałowych można zastosować różne podejścia. Jednym z powszechnych podejść jest przyjrzenie się komunikatom prasowym, prognozom kierownictwa, MD&A, aby zrozumieć opinię firmy na temat przyszłych wydatków kapitałowych
Jeśli firma przedstawiła wytyczne dotyczące przyszłych wydatków kapitałowych, możemy bezpośrednio wziąć te liczby.
Jeśli jednak liczby Capex nie są bezpośrednio dostępne, możemy je obliczyć z grubsza, używając Capex jako% sprzedaży (jak pokazano poniżej)
Opieraj się na wiedzy branżowej i innych uzasadnionych czynnikach

Krok 5C - Odniesienie do przeszłych informacji

Wykorzystamy saldo końcowe dla PPE = saldo początkowe + nakłady inwestycyjne - amortyzacja - korekta z tytułu sprzedaży aktywów (równanie BASE)
Bardzo trudno jest uzgodnić przeszłe PP&E ze względu na przekształcenia, sprzedaż aktywów itp
Dlatego zaleca się, aby nie godzić wcześniejszych ŚOI, ponieważ może to prowadzić do nieporozumień.

Polityka amortyzacji Colgate

Zwracamy uwagę, że firma Colgate nie przedstawiła wyraźnie szczegółowego podziału aktywów. Raczej połączyli wszystkie aktywa w grunty, budownictwo, maszyny i inny sprzęt
Zakres obejmuje również okresy użytkowania maszyn i urządzeń. W takim przypadku będziemy musieli zgadywać, aby dojść do średniego okresu użytkowania pozostałego dla aktywów
W przypadku „innego sprzętu” nie podano również wskazówek dotyczących okresu użytkowania. Będziemy musieli oszacować okres użytkowania innego sprzętu

Colgate 2013 - 10 tys., Strona 55

Poniżej przedstawiono szczegóły dotyczące majątku trwałego w latach 2012 i 2013

Colgate 2013 - 10 tys., Strona 91

Krok 5D - Oszacuj rozbicie rzeczowego majątku trwałego (PPE)

Najpierw znajdź wagi aktywów aktualnego PPE (2013)
Zakładamy, że te wagi aktywów z 2013 r. Będą kontynuowane
Używamy tych wag aktywów do obliczenia podziału szacowanych wydatków kapitałowych

Krok 5E - Oszacuj amortyzację aktywów

Należy pamiętać, że nie obliczamy amortyzacji gruntów, ponieważ grunty nie są aktywami podlegającymi amortyzacji
Aby oszacować amortyzację z ulepszeń budynków, najpierw korzystamy z poniższej struktury.
Amortyzacja jest tutaj podzielona na dwie części - 1) amortyzacja z aktywów związanych z ulepszeniami budynku już wymienionymi w bilansie 2) amortyzacja z przyszłych ulepszeń budynku
Aby obliczyć amortyzację od ulepszeń budowlanych wymienionych w aktywach, używamy prostej metody amortyzacji liniowej
Aby obliczyć przyszłą amortyzację, najpierw transponujemy Capex za pomocą funkcji TRANSPOSE w programie Excel
Amortyzację obliczamy od wkładu majątku każdego roku
Ponadto amortyzacja w pierwszym roku jest dzielona przez 2, zgodnie z przyjętą w połowie roku konwencją dotyczącą rozmieszczenia aktywów

Całkowita amortyzacja ulepszenia budynku = amortyzacja od aktywów ulepszeń budynku już wykazanych w bilansie + amortyzacja od przyszłych ulepszeń budynku Powyższy proces szacowania amortyzacji służy do obliczenia amortyzacji 1) sprzętu i maszyn produkcyjnych oraz 2) innego sprzętu jako pokazane poniżej.

Inne rodzaje sprzętu

Całkowita amortyzacja Colgate = amortyzacja (ulepszenia budynku) + amortyzacja (maszyny i sprzęt) + amortyzacja (inny sprzęt) Po ustaleniu całkowitych wartości amortyzacji możemy umieścić to w równaniu BASE, jak pokazano poniżej

Dzięki temu otrzymujemy wartości Ending Net PP&E dla każdego roku

Krok 5F - Połącz netto PP&E z bilansem

# 6 - Harmonogram amortyzacji

Szóstym krokiem w tym modelowaniu finansowym w programie Excel jest prognozowanie amortyzacji. Mamy tu do rozważenia dwie szerokie kategorie - 1) Wartość firmy i 2) Inne wartości niematerialne.

Krok 6A - Prognozowanie wartości firmy

Colgate 2013 - 10 tys., Strona 61

Wartość firmy pojawia się w bilansie, gdy firma przejmuje inną firmę. Prognozowanie wartości firmy na przyszłe lata jest zwykle bardzo trudne.
Jednakże wartość firmy corocznie poddawana jest testom na utratę wartości, które przeprowadza sama spółka. Analitycy nie są w stanie przeprowadzić takich testów i oszacować utraty wartości
Większość analityków nie prognozuje dobrej woli, po prostu utrzymuje to na stałym poziomie i tak też zrobimy w naszym przypadku.

Krok 6B - Prognozowanie innych wartości niematerialnych

Jak zauważono w raporcie Colgate 10K, większość wartości niematerialnych o ograniczonym okresie użytkowania jest związana z przejęciem Sanex
„Dodatki do wartości niematerialnych” są również bardzo trudne do przewidzenia
Raport Colgate 10K dostarcza nam szczegółowych informacji na temat kosztów amortyzacji następnych 5 lat.
Skorzystamy z tych szacunków w naszym modelu finansowym Colgate 2013 - 10 tys., Strona 61

Krok 6C - Końcowe wartości niematerialne netto są powiązane z „Innymi wartościami niematerialnymi”

Krok 6D - połącz amortyzację ze sprawozdaniami z przepływów pieniężnych

Krok 6E - Powiązanie nakładów inwestycyjnych i dodatkowych z wartościami niematerialnymi z zestawieniami przepływów pieniężnych

# 7 - Inny harmonogram długoterminowy

Następnym krokiem w tym modelowaniu finansowym jest przygotowanie innego harmonogramu długoterminowego. To jest harmonogram, który przygotowujemy dla „resztek”, które nie mają określonych czynników do prognozowania. W przypadku Colgate pozostałe pozycje długoterminowe (pozostałe) to odroczony podatek dochodowy (zobowiązania i aktywa), inne aktywa i inne zobowiązania.

Krok 7A - Odnieś się do danych historycznych z bilansu

Oblicz także zmiany w tych pozycjach.

Krok 7B - Prognozowanie aktywów i pasywów długoterminowych

Utrzymuj pozycje długoterminowe na stałym poziomie przez przewidywane lata w przypadku braku widocznych sterowników
Połącz prognozowane pozycje długoterminowe z bilansem, jak pokazano poniżej

Krok 7C - Odniesienie do bilansu innych pozycji długoterminowych

Krok 7D - Połącz pozycje długoterminowe z Zestawieniem przepływów pieniężnych

Należy pamiętać, że gdybyśmy utrzymywali długoterminowe aktywa i pasywa na stałym poziomie, wówczas zmiana wpływająca do rachunku przepływów pieniężnych wyniosłaby zero.

# 8 - Modelowanie finansowe w programie Excel - wypełnianie rachunku zysków i strat

Zanim przejdziemy dalej w tym modelowaniu finansowym opartym na Excelu, wrócimy i ponownie spojrzymy na rachunek zysków i strat
Wypełnij historyczną podstawową średnią ważoną akcji i rozwodnioną średnią ważoną liczbę akcji
Te liczby są dostępne w raporcie Colgate 10K

Krok 8A - Odniesienie do akcji podstawowych i rozwodnionych

Na tym etapie załóżmy, że przyszła liczba akcji podstawowych i rozwodnionych pozostanie taka sama jak w 2013 roku.

Krok 8B - Oblicz podstawowy i rozwodniony zysk na akcję

Dzięki temu jesteśmy gotowi, aby przejść do naszego następnego harmonogramu, tj. Harmonogramu kapitałowego akcjonariuszy.

# 9 - Modelowanie finansowe - Harmonogram kapitałowy akcjonariuszy

Następnym krokiem w tym szkoleniu dotyczącym modelowania finansowego w programie Excel jest przyjrzenie się harmonogramowi kapitałowemu akcjonariusza. Głównym celem tego harmonogramu jest przedstawienie pozycji związanych z kapitałem, takich jak kapitał własny, dywidendy, wykup akcji, wpływy z opcji itp . Raport Colgate 10K dostarcza nam szczegółowych informacji na temat akcji zwykłych i akcji własnych w ostatnich latach, jak pokazano poniżej. Colgate 2013 - 10 tys., Strona 68

Krok 9A - Odkup udziałowy: Wypełnij dane historyczne

Historycznie rzecz biorąc, Colgate odkupił akcje, jak widać na powyższym harmonogramie.
Wypełnij wykup akcji Colgate (w milionach) w arkuszu Excela.
Połącz historyczny rozwodniony zysk na akcję z rachunku zysków i strat
Rachunek przepływów pieniężnych powinien zawierać odniesienie do historycznej kwoty odkupionej

Zapoznaj się również z przyspieszonym zakupem akcji

Krok 9B - Odkup udziałów: Oblicz wielokrotność PE (wielokrotność EPS)

Oblicz sugerowaną średnią cenę, po której Colgate w przeszłości dokonywał wykupu akcji. Jest to obliczane jako kwota wykupiona / liczba udziałów
Oblicz wielokrotność PE = implikowana cena akcji / EPS

Krok 9C - Odkup udziałów: Znalezienie odkupionego udziału Colgate

Firma Colgate nie ogłosiła oficjalnie liczby akcji, które zamierzają odkupić. Jedyną informacją, że ich akcje objęte są raportem 10 tys., Jest to, że zezwolili na odkup do 50 milionów akcji. Colgate 2013 - 10 tys., Strona 35

Aby znaleźć liczbę odkupionych akcji, musimy przyjąć kwotę odkupu akcji. Opierając się na historycznej kwocie odkupu, przyjmuję tę liczbę jako 1500 milionów dolarów na wszystkie przyszłe lata.
Aby znaleźć liczbę odkupionych akcji, potrzebujemy prognozowanej implikowanej ceny akcji podczas potencjalnego wykupu.
Domniemana cena akcji = zakładany multipleks PE na EPS
Na podstawie trendów historycznych można założyć, że Future odkupi wielokrotność PE. Zwracamy uwagę, że Colgate odkupił akcje w średnim przedziale PE 17x - 25x
Poniżej znajduje się migawka z agencji Reuters, która pomaga nam zweryfikować asortyment PE dla Colgate

www.reuters.com

W naszym przypadku założyłem, że wszystkie przyszłe wykupy Colgate będą miały wartość PE równą 19x.
Używając PE równego 19x, możemy znaleźć cenę implikowaną = EPS x 19
Teraz, gdy znaleźliśmy cenę implikowaną, możemy znaleźć liczbę odkupionych udziałów = kwota w USD użyta do odkupu / cena domniemana

Krok 9D - Opcje na akcje: Wypełnij dane historyczne

Z zestawienia akcji zwykłych i kapitału własnego znamy liczbę opcji wykonywanych każdego roku.

Ponadto mamy również Wpływy Opcji z rachunku przepływów pieniężnych (w przybliżeniu)

Dzięki temu powinniśmy być w stanie znaleźć efektywną cenę wykonania

Colgate 2013 - 10 tys., Strona 53

Należy również zauważyć, że opcje na akcje są umowne na sześć lat, a prawo do nich przysługuje przez trzy lata. Colgate 2013 - 10 tys., Strona 69

Tymi danymi uzupełniamy dane dotyczące opcji, jak pokazano poniżej. Zwracamy również uwagę, że średnia ważona cena wykonania opcji na akcje za 2013 r. Wyniosła 42 USD, a liczba opcji do wykonania wyniosła 24,151 mln. Colgate 2013 - 10 tys., Strona 70

Krok 9E - Opcje na akcje: znajdź dochody z opcji

Umieszczając te liczby w naszych danych dotyczących opcji poniżej, zauważamy, że wpływy z opcji wynoszą 1,014 miliarda USD

Krok 9F - Opcje na akcje: prognozy danych ograniczonych jednostek magazynowych

Oprócz opcji na akcje istnieją Ograniczone Jednostki Akcji przyznawane pracownikom ze średnim ważonym okresem 2,2 roku. Colgate 2013 - 10 tys., Strona 81

Wypełnianie tych danych w zestawie danych Opcji Dla uproszczenia nie przewidzieliśmy emisji opcji (wiem, że to nie jest właściwe założenie, jednak ze względu na brak danych nie zamierzam już więcej wydawać opcji w przyszłości. te są równe zero, jak zaznaczono w szarej strefie powyżej. Ponadto przewiduje się, że ograniczone jednostki magazynowe będą wynosić 2,0 miliony w przyszłości.

Przyjrzyj się również metodzie papierów skarbowych

Krok 9G - Dywidendy: prognozowanie dywidend

Prognozuj szacunkowe dywidendy za pomocą wskaźnika wypłaty dywidendy
Stała wypłata dywidendy na jedną akcję
Z raportów 10K wyciągamy wszystkie wcześniejsze informacje o dywidendach
Mając informacje o wypłaconych dywidendach, możemy obliczyć współczynnik wypłaty dywidendy = suma wypłaconych dywidend / dochód netto.
Obliczyłem wskaźnik wypłaty dywidendy dla Colgate, jak widać poniżej - Zauważamy, że wskaźnik wypłaty dywidendy mieścił się w szerokim zakresie 50% -60%. Przyjmijmy, że w kolejnych latach wskaźnik wypłaty dywidendy wynosi 55%.
Możemy również połączyć prognozowany dochód netto z zestawienia dochodów
Korzystając zarówno z prognozowanego dochodu netto, jak i wskaźnika wypłaty dywidendy, możemy obliczyć sumę wypłaconych dywidend

Krok 8H - Całość prognozowania rachunku kapitałowego

Z prognozą wykupu akcji, wpływami z opcji i wypłaconymi dywidendami, jesteśmy gotowi do wypełnienia harmonogramu kapitałowego akcjonariuszy. Połącz wszystkie te elementy, aby znaleźć końcowe saldo kapitału własnego dla każdego roku, jak pokazano poniżej.

Krok 9I - Połącz kończący się kapitał udziałowca z bilansem

Krok 9J - Połącz dywidendy, odkup akcji i opcje przechodzi do CF.

# 10 - Pozostały harmonogram akcji

Następnym krokiem w tym szkoleniu dotyczącym modelowania finansowego online w programie Excel jest przyjrzenie się harmonogramowi sprzedaży akcji. Zestawienie harmonogramu zaległych akcji

Akcje podstawowe - rzeczywiste i średnie
W razie potrzeby rejestruj wcześniejsze efekty opcji i kabrioletów
Akcje Rozwodnione - średnia
Odkupione Akcje Referencyjne i nowe akcje z wykonanych opcji
Oblicz prognozowane podstawowe udziały (rzeczywiste)
Oblicz średnie podstawowe i rozwodnione akcje
Odniesienie prognozowanych udziałów do rachunku zysków i strat (przywołaj rachunek zysków i strat!)
Wprowadź historyczne informacje o akcjach
Uwaga : Harmonogram ten jest zwykle zintegrowany z harmonogramem akcji

Krok 10A - Wprowadź historyczne liczby z raportu 10K

Wyemitowane akcje (faktyczna realizacja opcji) i akcje odkupione można znaleźć w Tabeli Akcjonariuszy
Ponadto wkład ważony średnią liczbą akcji i efektem opcji na akcje w latach historycznych.

Krok 10B - Połącz emisje i odkup akcji z harmonogramu akcji.

Udziały podstawowe (koniec) = Udziały podstawowe (początek) + emisje akcji - Udziały odkupione.

Krok 10C - Znajdź podstawowe akcje średniej ważonej,

znajdujemy średnio dwa lata, jak pokazano poniżej.
Dodaj również efekt opcji i ograniczonych jednostek akcji (zgodnie z tabelą akcji akcjonariusza), aby znaleźć rozwodnioną średnią ważoną tytułów uczestnictwa.

Krok 10D - Połącz podstawowe i rozwodnione akcje ważone z rachunkiem zysków i strat

Teraz, gdy obliczyliśmy rozwodnioną średnią ważoną akcji, nadszedł czas, abyśmy zaktualizowali to samo w rachunku zysków i strat.
Połącz prognozowane rozwodnione średnie ważone akcje pozostające w obrocie z rachunkiem zysków i strat, jak pokazano poniżej

Na tym kończymy Harmonogram sprzedaży akcji i czas na przejście do kolejnego zestawu oświadczeń.

# 11 - Wypełnianie wyciągów z przepływów pieniężnych

Ważne jest, abyśmy w pełni wypełnili rachunki przepływów pieniężnych, zanim przejdziemy do naszego następnego i ostatecznego harmonogramu w tym modelowaniu finansowym, tj. Harmonogramu zadłużenia Do tego etapu jest tylko kilka rzeczy, które są niekompletne

Rachunek zysków i strat - na tym etapie koszty / przychody z tytułu odsetek są niekompletne
Bilans - na tym etapie pozycje kasowe i dłużne są niekompletne

Krok 11A - Oblicz przepływ środków pieniężnych dla działań finansowych

Sprawdź również Przepływy pieniężne z finansowania

Krok 11B - Znajdź wzrost (spadek) netto środków pieniężnych i ekwiwalentów środków pieniężnych

Krok 11C = Wypełnij zestawienia przepływów pieniężnych

Znajdź środki pieniężne i ich ekwiwalenty na koniec roku na koniec roku.

Krok 11D - Połącz środki pieniężne i ich ekwiwalenty z bilansem.

Teraz jesteśmy gotowi zająć się naszym ostatnim i ostatecznym harmonogramem, czyli Harmonogramem Zadłużenia i Oprocentowania

# 12- Modelowanie finansowe w programie Excel - harmonogram zadłużenia i odsetek

Następnym krokiem w tym modelowaniu finansowym online jest wypełnienie harmonogramu zadłużenia i odsetek. Podsumowanie zadłużenia i odsetek - harmonogram

Krok 12A - Ustaw harmonogram zadłużenia

Odnieś się do przepływów pieniężnych dostępnych do finansowania
Odnieś się do wszystkich źródeł kapitału i wykorzystania gotówki

Krok 12B - Oblicz przepływ środków pieniężnych ze spłaty zadłużenia

Odwołaj się do początkowego salda gotówkowego z bilansu
Odejmij minimalne saldo gotówki. Założyliśmy, że Colgate chciałby zatrzymać co najmniej 500 milionów dolarów rocznie.

Pomiń emisję / spłatę długu długoterminowego, gotówka dostępna w sekcji Kredyt odnawialny i rewolwer Na razie z raportu Colgate 10K zwracamy uwagę na dostępne szczegóły dotyczące kredytu odnawialnego Colgate 2013 - 10 000 , strona 35

W dodatkowych informacjach na temat Zadłużenia podano również zaciągnięte długoterminowe spłaty zadłużenia. Colgate 2013 - 10 tys., Strona 36

Krok 12C - Oblicz końcowe zadłużenie długoterminowe

Korzystamy z powyższego harmonogramu spłaty zadłużenia długoterminowego i obliczamy Saldo końcowe spłat długu długoterminowego

Krok 12D - Połącz długoterminowe spłaty zadłużenia.

Krok 12E - Oblicz uznaniowe pożyczki / spłaty

Korzystając ze wzoru wypłaty gotówki, jak pokazano poniżej, oblicz uznaniowe pożyczki / spłaty.

Krok 12F - Oblicz koszty odsetek z długu długoterminowego

Oblicz średnie saldo dla kredytu odnawialnego i zadłużenia długoterminowego
Przyjmij rozsądne założenie dotyczące stopy procentowej na podstawie informacji zawartych w raporcie 10 tys
Oblicz całkowite koszty odsetek = średnie saldo zadłużenia x stopa procentowa

Znajdź łączny koszt odsetek = odsetki (kredyt odnawialny) + odsetki (dług długoterminowy)

Krok 12G - Powiązanie wypłat długu głównego i rewolwerów z przepływami pieniężnymi

Krok 12H - Wartość odniesienia bieżącego i długoterminowego do bilansu

Oddziel bieżącą część długu długoterminowego i długu długoterminowego, jak pokazano poniżej

Połącz Kredyt Odnawialny, Zadłużenie Długoterminowe i Bieżącą Część Długu Długoterminowego z Bilansem

Krok 12I - Oblicz dochód z odsetek na podstawie średniego salda gotówkowego

Krok 12J - Połącz koszty z tytułu odsetek i przychody z odsetek z rachunkiem zysków i strat

Przeprowadź kontrolę bilansu: Aktywa ogółem = Pasywa + Kapitał własny akcjonariusza

Krok 12K - Audyt bilansu

W przypadku rozbieżności musimy przeprowadzić audyt modelu i sprawdzić, czy nie ma błędów w sprzężeniu

Zalecany kurs modelowania finansowego

Mam nadzieję, że spodobał Ci się bezpłatny przewodnik Excel dotyczący modelowania finansowego. Jeśli chcesz nauczyć się modelowania finansowego w programie Excel, korzystając z naszych wykładów wideo ekspertów, możesz również zapoznać się z naszym szkoleniem z bankowości inwestycyjnej. To przede wszystkim 99 kursów w pakiecie z bankowości inwestycyjnej. Ten kurs zaczyna się od podstaw i prowadzi do zaawansowanego poziomu pracy w bankowości inwestycyjnej. Kurs podzielony jest na 5 części -

Część 1 - Szkolenia z bankowości inwestycyjnej - podstawowe kursy
(26 kursów)
Część 2 - Zaawansowane szkolenie z modelowania bankowości inwestycyjnej
(20 kursów)
Część 3 - Dodatki do bankowości inwestycyjnej
(13 kursów)
Część 4 - Kursy Fundacji Bankowości Inwestycyjnej
(23 kursy)
Część 5 - Umiejętności miękkie dla bankierów inwestycyjnych
(17 kursów)

Pobierz modele finansowe

Model finansowy Alibaba
Model finansowy Box IPO
Szablony modelowania finansowego
Kurs modelowania finansowego w bankowości

Co następne?

Jeśli nauczyłeś się czegoś nowego lub podobało Ci się to modelowanie finansowe oparte na Excelu, zostaw komentarz poniżej. Powiedz mi co myślisz. Wielkie dzięki i uważaj. Miłej nauki!