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

Spisie treści

Co to jest modelowanie finansowe w programie Excel?

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 dostępne w całej sieci. Dużo napisano o nauce modelowania finansowego; jednak większość szkoleń dotyczących modelowania finansowego jest 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 trzy 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 w samouczku

Pobierz model finansowy Colgate

Wprowadź adres e-mail Kontynuując powyższy krok, zgadzasz się na nasze Warunki użytkowania i Politykę prywatności.

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

Jak zbudować model finansowy?
# 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ść. System modułowy zasadniczo oznacza tworzenie podstawowych zestawień, takich jak rachunek zysków i strat, bilans i przepływy pieniężne przy użyciu różnych modułów / harmonogramów. Głównym celem jest przygotowanie każdego oświadczenia krok po kroku i połączenie wszystkich programów pomocniczych z podstawowymi oświadczeniami po zakończeniu. Rozumiem, że teraz może to nie być jasne; jednak zdasz sobie sprawę, że jest to bardzo łatwe, gdy posuwamy się naprzód. 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.
Różne harmonogramy to harmonogram amortyzacji, harmonogram kapitału obrotowego, harmonogram dotyczący wartości niematerialnych i prawnych, harmonogram kapitału udziałowca, harmonogram innych pozycji długoterminowych, harmonogram zadłużenia itp.
Poszczególne harmonogramy są powiązane z podstawowymi oświadczeniami po ich ukończeniu.
W tym przewodniku po modelowaniu finansowym, krok po kroku, zbudujemy od podstaw zintegrowany model ekonomiczny Colgate Palmolive.

# 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ć 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 sprawozdania 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łnionymi danymi historycznymi

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 elementarna 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 poniżej znajduje się krótkie podsumowanie wskaźników Colgate Palmolive.

WAŻNE - Proszę zauważyć, ż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 dotyczące 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ż, które wzrosły 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 zauważyć, że marże EBIT spadły w 2013 roku do 20,4% z powodu 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 kontami obliczonymi w różnych terminach przy użyciu waluty o różnych uprawnieniach kupna. W efekcie ta analiza indeksuje raporty 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. Celem jest poszukiwanie symptomów problemów, które można zdiagnozować za pomocą dodatkowych metod.

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 koszcie 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. Najczęstszymi wskaźnikami płynności są wskaźnik bieżącej płynności, wskaźnik testu kwasowości (lub wskaźnik aktywów szybkich) wskaźniki gotówkowe.
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 wskaźnik Colgate jest bardziej znaczący niż 1,0 we wszystkich latach. Oznacza to, że aktywa obrotowe są ważniejsze niż bieżące zobowiązania i być może Colgate ma wystarczającą płynność.
Szybki stosunek Colgate mieści się w przedziale 0,6-0,7; Oznacza to, że papiery wartościowe Colgates Cash and Marketable mogą spłacać nawet 70% bieżących zobowiązań. Wygląda na to, że Colgate ma dobrą sytuację.
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 stosunku 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 doskonał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. Sprawdzane stawki 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 wskazuje na 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 tworzenia 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 dolara i wzrost: 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 jest 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, stopień wykorzystania mocy produkcyjnych i średnia cena: Te założenia mogą być ważne dla firm, w których moce produkcyjne są kluczowe 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, serwisu i dodatków itp.). Przykłady obejmują klasyczne firmy z żyletkami i firmy takie jak komputery, w których sprzedaż usług, oprogramowania i uaktualnień jest niezbędna. Kluczowe jest modelowanie zainstalowanej podstawy (nowe dodatki do podłogi, ścieranie w gruncie, 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ę sprzedażą detaliczną są często modelowane na podstawie sklepów (stare sklepy plus nowe sklepy w każdym roku) i przychodów na sklep.
Na podstawie współczynnika obłożenia: podejście to 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. Zwracamy uwagę, że w rachunku zysków i strat Colgate nie przedstawił informacji segmentowych; jednakże, jako dodatkowa informacja, Colgate podał pewne szczegóły dotyczące segmentów na stronie 87 Źródło - Colgate 2013 - 10 tys., Strona 86

Ponieważ nie mamy żadnych dalszych informacji o funkcjach, na podstawie tych 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 elementu. Teraz możemy założyć procentowy wzrost sprzedaży w oparciu o historyczne trendy i przewidzieć przychody z każdej części. 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 innego 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: To podejście 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 pięć 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 zakończyliśmy zestawienie dochodów, 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 współczynniki 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 wybitnego kierowcy 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

Uzyskaj 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 wliczony w koszt sprzedaży
W takich przypadkach zapoznaj się z zestawieniami przepływów pieniężnych, w których znajdziesz amortyzację i koszty amortyzacji. Należy również zauważyć, że poniższe liczby to 1) Amortyzacja 2) Amortyzacja. Więc jaki jest numer amortyzacji?
Saldo końcowe 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ź przeszłe wydatki kapitałowe
Przybądź do Capex jako% sprzedaży netto

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

W celu prognozowania wydatków kapitałowych można zastosować różne podejścia. Jedną z powszechnych praktyk jest zapoznanie się z komunikatami prasowymi, prognozami 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)
Uzgodnienie przeszłych PP&E z powodu przeszacowań, sprzedaży aktywów itp. Jest skomplikowane.
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. Zamiast tego połączyli wszystkie aktywa w grunty, budynki, 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 znajduje się podział szczegółów dotyczących 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, które są już wymienione w bilansie, 2) amortyzacja z przyszłych ulepszeń budynku.
Aby obliczyć amortyzację z 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 na podstawie wkładów majątkowych z każdego roku.
Ponadto amortyzacja w pierwszym roku jest dzielona przez dwa, zgodnie z przyjętą w połowie roku konwencją dotyczącą rozmieszczenia aktywów.

Całkowita amortyzacja ulepszeń 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 (wyposażenie dodatkowe) Po ustaleniu rzeczywistych 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 skomplikowane.
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 utrzymują to jako stałe, co zrobimy również 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ż skomplikowane w projekcie
Raport Colgate 10K dostarcza nam szczegółowych informacji na temat kosztów amortyzacji przez następne pięć lat.
Skorzystamy z tych szacunków w naszym modelu finansowym Colgate 2013 - 10 tys., Strona 61

Krok 6C - Końcowe wartości niematerialne i prawne 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. Wtedy przygotowujemy się na „resztki”, które nie mają określonych czynników prognostycznych. W przypadku Colgate pozostałymi pozycjami długoterminowymi (pozostałymi) były odroczone podatki dochodowe (zobowiązania i aktywa), inne inwestycje 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 programie Excel, 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 pierwotnych 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 akcjonariuszy. Głównym celem tego harmonogramu jest przedstawienie pozycji związanych z kapitałem, takich jak kapitał własny, dywidendy, odkup akcji, wpływy z opcji itp . Raport Colgate 10K dostarcza nam szczegółowych informacji o akcjach zwykłych i akcjach 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
W rachunku przepływów pieniężnych należy odnieść się do historycznej kwoty odkupionego.

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 odkupiona / liczba udziałów.
Oblicz wielokrotność PE = implikowana cena akcji / EPS

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

Colgate nie ogłosił oficjalnie, ile akcji zamierza wykupić. Jedyną informacją, jaką mają udziały w raporcie 10K, jest to, że zezwolili na odkupienie 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 dotyczącej potencjalnego wykupu.
Rzeczywista cena akcji = zakładany PE multipleks EPS.
Można założyć, że przyszłość odkupi wielokrotność PE na podstawie trendów historycznych. 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ć gamę 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 zobaczyć 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.

Poza tym mamy również Wpływy z 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 Opcji, jak pokazano poniżej. Zwracamy również uwagę, że średnia ważona cena wykonania opcji na akcje w 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 z powodu braku danych nie podejmuję żadnych dalszych problemów z opcjami). jako 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 na podstawie 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 Colgate, jak widać poniżej - Zauważamy, że wskaźnik wypłaty dywidendy mieścił się w szerokim zakresie 50% -60%. Załóżmy, że w przyszłych latach wskaźnik wypłaty dywidendy wyniesie 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 nieprzetworzone wartości procentowe (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 dotyczącym akcji

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

Wyemitowane akcje (faktyczna realizacja opcji) i akcje odkupione można znaleźć w Tabeli Akcjonariuszy
Wejście ważone średniej liczby akcji i wpływu opcji na akcje w latach historycznych.

Krok 10B - Połącz emisje i wykup 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 także 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 zestawienia 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 niekompletnych rzeczy

Rachunek zysków i strat - na tym etapie koszty / przychody z tytułu odsetek są niekompletne
Bilans - pozycje kasowe i dłużne na tym etapie 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

Klasa 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ń na razie emisję / spłatę długu długoterminowego, gotówka dostępna w sekcji Kredyt odnawialny i rewolwer Z raportu Colgate 10K; zwracamy uwagę na dostępne szczegóły dotyczące Revolved Credit Facility Colgate 2013 - 10K, strona 35

W dodatkowych informacjach o zadłużeniu 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 harmonogramu spłat Długu Długoterminowego przedstawionego powyżej 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 / obniżki.

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 - Zadłużenie głównego powiązania i wypłaty rewolwerowe do przepływów pieniężnych

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, Dług długoterminowy 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 programu 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 rozpoczyna się od podstaw i prowadzi do zaawansowanego poziomu pracy w bankowości inwestycyjnej. Ten kurs jest podzielony na pięć części -

Część 1 - Szkolenia z bankowości inwestycyjnej - Kursy podstawowe
(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!