Ekonomisk modellering i Excel (steg för steg gratis guide + mall)

Finansiell modellering i Excel är processen att bygga en finansiell modell för att representera en transaktion, operation, fusion, förvärv, finansiell information för att analysera hur en förändring i en variabel kan påverka den slutliga avkastningen för att fatta beslut om en eller flera av ovan nämnda finansiella transaktioner.

Vad är ekonomisk modellering i Excel?

Finansiell modellering i Excel finns överallt på nätet och det har skrivits mycket om att lära sig ekonomisk modellering, men de flesta av de finansiella modellerna är exakt samma. Detta går utöver det vanliga bråket och utforskar praktisk ekonomisk modellering som används av investeringsbanker och forskningsanalytiker.

I den här gratisguiden för finansiell modellering kommer jag att ta ett exempel på Colgate Palmolive och förbereda en helt integrerad ekonomisk modell från grunden.

Den här guiden består av över 6000 ord och tog mig tre veckor att slutföra. Spara den här sidan för framtida referens och glöm inte att dela den :-)

VIKTIGST - Ladda ner Excel-mallen för Colgate Financial-modellering för att följa instruktionerna

Ladda ner Colgate Financial Model Mall

Lär dig steg för steg ekonomisk modellering i Excel

Ekonomisk modellering i Excel-utbildning - Läs mig först

Steg 1 - Ladda ner Colgate Financial Model Mall. Du kommer att använda den här mallen för självstudien

Ladda ner Colgates finansiella modell

Steg 2 - Observera att du får två mallar - 1) Olösad Colgate Palmolive Financial Model 2) Löst Colgate Palmolive Financial Model

Steg 3 - Du kommer att arbeta med den olösta Colgate Palmolive-modellen för finansiell modell . Följ anvisningarna steg för steg för att förbereda en helt integrerad finansiell modell.

Steg 4 - Happy Learning!

Innehållsförteckning

Jag har gjort det enkelt att navigera i innehållsförteckningen så att du kan göra denna ekonomiska modellering

# 1 - Colgates finansiella modell - historisk
# 2 - Förhållande analys av Colgate Palmolive
# 3 - Projicera resultaträkningen
# 4- Schema för rörelsekapital
# 5 - Avskrivningsplan
# 6 - Avskrivningsplan
# 7 - Annat långtidsschema
# 8 - Slutföra resultaträkningen
# 9 - Aktieägarnas schema
# 10 - Utestående schema för aktier
# 11 - Slutföra kassaflödesanalyserna
# 12- Skuld och ränteschema rekommenderas
Finansiell modelleringskurs
Gratis ekonomiska modeller

Om du är nybörjare inom Financial Modelling, ta en titt på den här guiden på What is Financial Modelling?

Hur bygger jag en ekonomisk modell i Excel?

Låt oss titta på hur en finansiell modell byggs från grunden. Denna detaljerade finansiella modelleringsguide ger dig en steg-för-steg-guide för att skapa en finansiell modell. Den primära metoden i denna finansiella modelleringsguide är Modular. Den modulära metoden innebär i huvudsak att vi bygger kärnredovisningar som resultaträkning, balansräkning och kassaflöden med hjälp av olika moduler / scheman. Huvudfokus är att förbereda varje uttalande steg för steg och ansluta alla stödjande scheman till kärnuttalanden efter avslutad. Jag kan förstå att detta kanske inte är klart från och med nu, men du kommer att inse att detta är väldigt enkelt när vi går vidare. Du kan se nedan olika finansiella modeller / scheman -

Snälla notera följande -

Kärnuppgifterna är resultaträkningen, balansräkningen och kassaflödena.
Ytterligare scheman är avskrivningsplanen, rörelsekapitalschemat, immateriella schemat, aktieägarnas schema, andra långsiktiga poster, skuldschema etc.
Ytterligare scheman är kopplade till kärnuttalanden när de är färdiga
I den här finansiella modelleringsguiden kommer vi att bygga en steg för steg integrerad ekonomisk modell av Colgate Palmolive från grunden.

# 1 - Ekonomisk modellering i Excel - Projekt historik

Det första steget i Financial Modeling Guide är att förbereda Historicals.

Steg 1A - Ladda ner Colgates 10K-rapporter

”Finansiella modeller förbereds i excel och de första stegen börjar med att veta hur branschen har gjort de senaste åren. Att förstå det förflutna kan ge oss värdefulla insikter relaterade till företagets framtid. Därför är det första steget att ladda ner alla företagets finanser och fylla i dem i ett Excel-ark. För Colgate Palmolive kan du ladda ner årsredovisningarna för Colgate Palmolive från deras investeraravdelning. När du klickar på “Årsredovisning” hittar du fönstret som visas nedan -

Steg 1B - Skapa kalkylbladet Historiska bokslut

Om du laddar ner 10K 2013 kommer du att notera att endast två års finansiella rapporter finns tillgängliga. För finansiell modellering i Excel är dock den rekommenderade datasetet att ha de senaste fem åren av finansiella rapporter. Ladda ner de senaste tre åren av årsredovisningen och fyll i historiken.
Många gånger verkar dessa uppgifter för tråkiga och tråkiga eftersom det kan ta mycket tid och energi att formatera och sätta excel i önskat format.
Man bör dock inte glömma att detta är det arbete som du är skyldig att göra en gång för varje företag och att fylla historiken hjälper en analytiker att förstå trender och finansiella rapporter
Så snälla hoppa inte över detta, ladda ner data och fyll i data (även om du känner att det här är åsnans arbete ;-))

Om du vill hoppa över detta steg kan du ladda ner Colgate Palmolive Historical Model direkt här.

Colgate resultaträkning med historisk befolkning

Colgate Balansräkning Historiska data

# 2 - Förhållande analys

Det andra steget i ekonomisk modellering i Excel är att utföra förhållandeanalys.

En nyckel för att lära sig ekonomisk modellering i Excel är att kunna utföra grundläggande analys. Om den grundläggande analysen eller förhållandeanalysen är något nytt för dig, rekommenderar jag att du läser lite på internet. Jag tänker ta en djupgående förhållandeanalys i ett av mina kommande inlägg, men här är en snabb ögonblicksbild av Colgate Palmolive-förhållandena

VIKTIGT - Observera att jag har uppdaterat förhållandeanalysen av Colgate i ett separat inlägg. Ta en titt på den här omfattande förhållandeanalysen.

Steg 2A - Vertikal analys av Colgate

I resultaträkningen är den vertikala analysen ett universellt verktyg för att mäta företagets relativa resultat från år till år i termer av kostnad och lönsamhet. Det bör alltid ingå som en del av någon ekonomisk analys. Här beräknas procentsatser i förhållande till nettoomsättningen som anses vara 100%. Denna vertikala analysansträngning i resultaträkningen kallas ofta marginalanalys eftersom den ger olika marginaler i förhållande till försäljning.

Vertikala analysresultat

Vinstmarginalen har ökat med 240 punkter från 56,2% 2007 till 58,6% 2013. Detta beror främst på minskade försäljningskostnader
Rörelseresultat eller EBIT har också visat förbättrade marginaler och ökat därmed från 19,7% 2007 till 22,4% 2012 (en ökning med 70 punkter). Detta berodde på sänkta allmänna och administrativa kostnader. Observera dock att EBIT-marginalerna minskade 2013 till 20,4% på grund av en ökning av ”Övriga kostnader”. Kontrollera också skillnaden mellan EBIT och EBITDA
Nettovinstmarginalen ökade från 12,6% 2007 till 14,5% under 2012. Dock minskade vinstmarginalen till 12,9%, främst på grund av ökade ”övriga kostnader”.
Resultatet per aktie har ökat stadigt från 2007 till år 2012. Det var dock ett litet dopp i EPS för FY2013
Observera också att avskrivningar anges separat i resultaträkningen. Det ingår i försäljningskostnaden

Steg 2B - Horisontell analys av Colgate

Horisontell analys är en teknik som används för att utvärdera trender över tid genom att beräkna procentuella ökningar utmärker sig eller minskar i förhållande till ett basår. Det ger en analytisk länk mellan konton som beräknas vid olika datum med hjälp av valuta med olika köpkrafter. I själva verket indexerar denna analys kontona och jämför utvecklingen av dessa över tid. Liksom med den vertikala analysmetoden kommer frågor att dyka upp som behöver undersökas och kompletteras med andra tekniker för ekonomisk analys. Fokus är att leta efter symptom på problem som kan diagnostiseras med hjälp av ytterligare tekniker.

Låt oss titta på den horisontella analysen av Colgate

Horisontella analysresultat

Vi ser att nettoomsättningen har ökat med 2,0% 2013.
Notera också trenden i försäljningskostnaderna, vi ser att de inte har vuxit i samma andel har försäljning.
Dessa observationer är extremt praktiska när vi gör ekonomisk modellering i Excel

Steg 2C - Likviditetsförhållanden för Colgate

Likviditetsgraden mäter förhållandet mellan företagets mer likvida tillgångar (de som är lättast att omvandla till kontanter) och kortfristiga skulder. De vanligaste likviditetsförhållandena är: Aktuellt förhållande Surtest (eller snabb tillgångsförhållande) Kontantkvoter
Omsättningsgrader som kundfordringar, lageromsättning och leverantörsomsättning

Viktiga höjdpunkter i likviditetsförhållanden

Nuvarande förhållande för Colgate är större än 1,0 för alla år. Detta innebär att omsättningstillgångar är större än kortfristiga skulder och kanske Colgate har tillräcklig likviditet
Quick Ratio för Colgate ligger i intervallet 0,6-0,7, vilket innebär att Colgates Cash och omsättbara värdepapper kan betala för så mycket som 70% av kortfristiga skulder. Detta ser ut som en rimlig situation för Colgate.
Kontantcykelcykeln har minskat från 43 dagar 2009 till 39 dagar 2013. Detta beror främst på minskningen av en fordringstid.

Ta också en titt på den här detaljerade artikeln om Cash Conversion Cycle

Steg 2D - Lönsamhetsgraden för Colgate

Lönsamhetsförhållanden ett företags förmåga att generera intäkter i förhållande till försäljning, tillgångar och eget kapital

Viktiga höjdpunkter - Lönsamhetsförhållanden för Colgate

Som vi kan se från ovanstående tabell har Colgate en ROE på närmare 100%, vilket innebär stora avkastningar till aktieägarna.

Steg 2E - Riskanalys av Colgate

Genom riskanalys försöker vi bedöma om företagen kommer att kunna betala sina kort- och långfristiga förpliktelser (skuld). Vi beräknar skuldsättningsgraden som fokuserar på tillgångarnas tillräcklighet eller tillgång från tillgångar. Förhållanden som ses på är

Skuldsättningsgrad
Skuldkvoten
Räntetäckningsgrad
Skuldsättningsgraden har stadigt ökat till en högre nivå på 2,23x. Detta innebär ökad finansiell hävstång och risker på marknaden
Räntetäckningsgraden är dock mycket hög vilket innebär mindre risk för räntebetalning.

# 3 - Ekonomisk modellering i Excel - Projektera resultaträkningen

Det tredje steget i ekonomisk modellering är att prognostisera resultaträkningen, där vi kommer att börja med att modellera försäljnings- eller intäktsposter.

Steg 3A - Prognoser för intäkter

För de flesta företag är intäkterna en grundläggande drivkraft för ekonomisk utveckling. En väldesignad och logisk intäktsmodell som återspeglar exakt typen och mängden av intäktsflöden är extremt viktig. Det finns lika många sätt att utforma ett intäktsschema som det finns företag. Några vanliga typer inkluderar:

Försäljningstillväxt: Antagandet om försäljningstillväxt i varje period definierar förändringen från föregående period. Detta är en enkel och vanligt förekommande metod men ger ingen insikt i komponenterna eller dynamiken i tillväxt.
Inflations- och volym / mix-effekter: I stället för ett enkelt tillväxtantagande används en prisinflationsfaktor och en volymfaktor. Detta användbara tillvägagångssätt möjliggör modellering av fasta och rörliga kostnader i multiproduktföretag och tar hänsyn till pris kontra volymrörelser.
Enhetsvolym, volymförändring, genomsnittspris och prisförändring: Denna metod är lämplig för företag som har en enkel produktmix; det möjliggör analys av effekterna av flera nyckelvariabler.
Dollarmarknadsstorlek och tillväxt: Marknadsandel och förändring i andel - Användbar för fall där information finns tillgänglig om marknadsdynamiken och där dessa antaganden sannolikt är grundläggande för ett beslut. Till exempel telekomindustrin
Enhetsmarknadsstorlek och tillväxt: Detta är mer detaljerat än föregående fall och är användbart när prissättning på marknaden är en nyckelvariabel. (För ett företag med en prisdiskonteringsstrategi, till exempel eller en bäst av rasens premiumpris nischspelare) t.ex. lyxbilsmarknad
Volymkapacitet, kapacitetsutnyttjandegrad och genomsnittspris: Dessa antaganden kan vara viktiga för företag där produktionskapacitet är viktig för beslutet. (Vid till exempel köp av ytterligare kapacitet eller för att avgöra om expansionen skulle kräva nya investeringar.)
Produkttillgänglighet och prissättning
Intäkterna drevs av investeringar i kapital, marknadsföring eller FoU
Intäktsbaserad på installerad bas (fortsatt försäljning av delar, engångsartiklar, service och tillägg osv.). Exempel är klassiska rakbladföretag och företag som datorer där försäljning av service, programvara och uppgraderingar är viktigt. Modellering av den installerade basen är nyckeln (nya tillägg till basen, nedgång i basen, fortsatta intäkter per kund, etc).
Anställd: Till exempel intäkter från professionella tjänsteföretag eller försäljningsbaserade företag som mäklare. Modellering bör fokusera på nettobemanning, intäkter per anställd (ofta baserat på fakturerbara timmar). Mer detaljerade modeller kommer att inkludera anciennitet och andra faktorer som påverkar prissättningen.
Butik, anläggning eller kvadratfilm baserat: Detaljhandelsföretagen modelleras ofta baserat på butiker (gamla butiker plus nya butiker varje år) och intäkter per butik.
Beläggningsfaktorbaserad: Detta tillvägagångssätt gäller för flygbolag, hotell, biografer och andra företag med låga marginalkostnader.

Projicerar Colgate-intäkter

Låt oss nu titta på Colgate 10K 2013-rapporten. Vi noterar att Colgate inte har lämnat segmentinformation i resultaträkningen, men som ytterligare information har Colgate lämnat några detaljer om segment på sidan 87 Källa - Colgate 2013 - 10K, sidan 86

Eftersom vi inte har någon ytterligare information om segmenten kommer vi att projicera den framtida försäljningen av Colgate utifrån dessa tillgängliga data. Vi kommer att använda metoden för försäljningstillväxt över segment för att få fram prognoser. Se bilden nedan. Vi har beräknat tillväxttakten från år till år för varje segment. Nu kan vi anta en procentuell försäljningstillväxt baserat på de historiska trenderna och projicera intäkterna under varje segment. Den totala nettoomsättningen är summan av segmentet Oral, Personal & Home Care och Pet Nutrition.

Steg 3B - Kostnadsprognoser

Procent av intäkter: Enkelt men ger ingen insikt i någon hävstång (skalfördelar eller fast kostnad
Andra kostnader än avskrivningar i procent av intäkterna och avskrivningar från ett separat schema: Detta tillvägagångssätt är i själva verket det lägsta som är acceptabelt i de flesta fall, och tillåter endast partiell analys av driftskapaciteten.
Variabla kostnader baserade på intäkter eller volym, fasta kostnader baserade på historiska trender och avskrivningar från ett separat schema: Detta tillvägagångssätt är det minsta som krävs för känslighetsanalys av lönsamhet baserat på flera intäktsscenarier

Kostnadsprognoser för Colgate

För att projicera kostnaden kommer den vertikala analysen som gjorts tidigare att vara till hjälp. Låt oss ta en titt på den vertikala analysen -

Eftersom vi redan har prognostiserat försäljning är alla andra kostnader några marginaler för denna försäljning.
Metoden är att ta riktlinjerna från de historiska kostnads- och kostnadsmarginalerna och sedan prognostisera framtida marginal.
Exempelvis har försäljningskostnaden legat i intervallet 41-42% under de senaste 5 åren. Vi kan titta på prognoser för marginalerna utifrån detta.
Likaså har försäljnings-, allmänna och administrativa utgifter historiskt legat i intervallet 34-36%. Vi kan anta framtida SG&A-utgiftsmarginal på denna grund. På samma sätt kan vi fortsätta med en annan uppsättning utgifter.

Med hjälp av ovanstående marginaler kan vi hitta de faktiska värdena genom att beräkna tillbaka.

För att beräkna avsättningen för skatt använder vi antagandet om effektiv skattesats

Observera också att vi inte slutför raden "Räntekostnader (Inkomster)" eftersom vi kommer att ta om resultaträkningen i ett senare skede.
Räntekostnader och ränteintäkter.
Vi har inte heller beräknat avskrivningar som redan har inkluderats i försäljningskostnaden.
Detta kompletterar resultaträkningen (åtminstone för tillfället!)

# 4 - Finansiell modellering - Rörelsekapitalschema

Nu när vi har slutfört resultaträkningen är det fjärde steget i finansiell modellering att titta på arbetskapitalplanen.

Nedan följer stegen som ska följas för arbetskapitalplanen

Steg 4A - Länka nettoomsättningen och försäljningskostnaden

Steg 4B - Referera till balansräkningsdata relaterade till rörelsekapital

Referera till tidigare data från balansräkningen
Beräkna nettorörelsekapitalet
Uppnå en ökning / minskning av rörelsekapitalet
Observera att vi inte har tagit med kortfristiga skulder och likvida medel i rörelsekapitalet. Vi kommer att hantera skuld och likvida medel separat.

Steg 4C - Beräkna omsättningsgraden

Beräkna historiska förhållanden och procentsatser
Använd slut- eller medelbalansen
Båda är acceptabla eftersom lång konsistens bibehålls

Steg 4D - Befolk antagandena för framtida rörelsekapitalposter

Vissa artiklar utan en uppenbar drivrutin antas vanligtvis med konstanta belopp
Se till att antagandena är rimliga och i linje med verksamheten

Steg 4E - Förbered de framtida rörelsekapitalbalanserna

Steg 4F - Beräkna förändringarna i rörelsekapital

Anländer till kassaflöden baserat på enskilda rader
Se till att skyltar är korrekta!

Steg 4G - Koppla upp det prognostiserade rörelsekapitalet till balansräkningen

Steg 4H - Länka rörelsekapitalet till kassaflödesanalysen

# 5 - Ekonomisk modellering i Excel - Avskrivningsschema

När arbetskapitalplanen är slutförd är nästa steg i denna finansiella modellering projektet Capex of Colgate och projektet avskrivningar och tillgångar. Colgate 2013 - 10K, Sida 49

Avskrivningar tillhandahålls inte som en separat rad, men ingår i kostnaden för försäljningen
I sådana fall, ta en titt på kassaflödesanalysen där du hittar avskrivningar och avskrivningar. Observera att nedanstående siffror är 1) Avskrivningar 2) avskrivningar. Så vad är avskrivningsnumret?
Slutsaldo för PPE = Ingående balans + Capex - Avskrivningar - Justering för tillgångsförsäljning (BASE-ekvation)

Steg 5A - Länka nettoomsättningssiffrorna i avskrivningsplanen

Ställ in raderna
Referens nettoomsättning
Ange tidigare investeringar
Anländer till Capex som% av nettoomsättningen

Steg 5B - Prognosera kapitalutgiftsposterna

För att prognostisera investeringarna finns det olika tillvägagångssätt. Ett vanligt tillvägagångssätt är att titta på pressmeddelanden, managementprognoser, MD&A för att förstå företagets syn på framtida investeringar
Om företaget har gett vägledning om framtida investeringar kan vi ta dessa siffror direkt.
Om Capex-siffrorna inte är direkt tillgängliga kan vi dock beräkna det grovt med Capex som% av försäljningen (enligt nedan)
Använd ditt omdöme baserat på branschkunskap och andra rimliga drivrutiner

Steg 5C - Referens tidigare information

Vi kommer att använda Ending Balance för PPE = Startbalans + Capex - Avskrivningar - Justering för tillgångsförsäljning (BASE-ekvation)
Det är mycket svårt att förena tidigare PP&E på grund av omräkningar, tillgångsförsäljning etc.
Det rekommenderas därför att inte förena den tidigare PPE eftersom det kan leda till viss förvirring.

Colgates avskrivningspolicy

Vi noterar att Colgate inte uttryckligen har tillhandahållit en detaljerad uppdelning av tillgångarna. De har snarare klubbar alla tillgångar i mark, byggnad, maskiner och annan utrustning
Användningstider för maskiner och utrustning tillhandahålls också inom räckvidden. I det här fallet måste vi göra några gissningar för att nå den genomsnittliga nyttjandeperiod som finns kvar för tillgångarna
Vägledning för livslängd tillhandahålls inte för "Annan utrustning". Vi måste uppskatta nyttjandeperioden för annan utrustning

Colgate 2013 - 10K, Sida 55

Nedan följer uppdelningen av fastighets-, anläggnings- och utrustningsuppgifterna för 2012 och 2013

Colgate 2013 - 10K, Sida 91

Steg 5D - Uppskatta uppdelningen av fastighetsanläggningar (PPE)

Hitta först tillgångsvikten för aktuell PPE (2013)
Vi antar att dessa tillgångsviktar för 2013 PPE kommer att fortsätta framöver
Vi använder denna tillgångsvikt för att beräkna uppdelningen av beräknade investeringar

Steg 5E - Uppskatta tillgångens avskrivningar

Observera att vi inte beräknar avskrivningar på mark eftersom mark inte är en avskrivningsbar tillgång
För att uppskatta avskrivningar från byggnadsförbättringar använder vi oss först av nedanstående struktur.
Avskrivningar här är uppdelade i två delar - 1) avskrivningar från byggförbättringstillgången som redan listats på balansräkningen 2) avskrivningar från framtida byggförbättringar
För att beräkna avskrivningarna från byggförbättringar som listas på tillgången använder vi den enkla avskrivningsmetoden
För att beräkna framtida avskrivningar transponerar vi först Capex med funktionen TRANSPOS i Excel
Vi beräknar avskrivningen från tillgångsbidrag från varje år
Det första årets avskrivningar divideras också med 2 eftersom vi antar mittårskonventionen för tillgångsdistribution

Total avskrivning av byggnadsförbättring = avskrivningar från byggnadsförbättringar Tillgång som redan listats på balansräkningen + avskrivningar från framtiden Byggförbättringar Ovanstående process för att uppskatta avskrivningar används för att beräkna avskrivningarna av 1) Tillverkningsutrustning och maskiner och 2) annan utrustning som visas nedan.

Andra typer av utrustning

Total avskrivning av Colgate = Avskrivningar (byggförbättringar) + Avskrivningar (Maskiner och utrustning) + Avskrivningar (annan utrustning) När vi har fått reda på de totala avskrivnings siffrorna kan vi sätta det i BASE-ekvationen som visas nedan

Med detta får vi Ending Net PP&E siffror för vart och ett av åren

Steg 5F - Länka netto PP&E till balansräkningen

# 6 - Avskrivningsplan

Det sjätte steget i denna finansiella modellering i Excel är att prognostisera avskrivningarna. Vi har två breda kategorier att tänka på här - 1) Goodwill och 2) Andra immateriella tillgångar.

Steg 6A - Prognos för goodwill

Colgate 2013 - 10K, Sida 61

Goodwill kommer i balansräkningen när ett företag förvärvar ett annat företag. Det är normalt mycket svårt att projicera Goodwill för kommande år.
Goodwill är dock föremål för nedskrivningstester varje år som utförs av företaget självt. Analytiker har ingen möjlighet att utföra sådana tester och utarbeta uppskattningar av nedskrivningar
De flesta analytiker projicerar inte goodwill, de håller bara detta konstant och det är vad vi också kommer att göra i vårt fall.

Steg 6B - Prognoser för andra immateriella tillgångar

Som noterats i Colgates 10K-rapport är majoriteten av det obegränsade livets immateriella relaterade till Sanex-förvärvet
”Tillägg till immateriella tillgångar” är också mycket svåra att projicera
Colgates 10K-rapport ger oss information om de kommande 5 års amorteringskostnader.
Vi kommer att använda dessa uppskattningar i vår finansiella modell Colgate 2013 - 10K, sidan 61

Steg 6C - Avslutande netto immateriella tillgångar är kopplade till "Övriga immateriella tillgångar"

Steg 6D - koppla avskrivningar och kassaflödesanalyser

Steg 6E - Länka Capex och tillägg till immateriella tillgångar till kassaflödesanalyser

# 7 - Annat långtidsschema

Nästa steg i denna finansiella modellering är att förbereda det andra långsiktiga schemat. Detta är schemat som vi förbereder för "left overs" som inte har specifika drivkrafter för prognoser. För Colgate var de övriga långfristiga posterna (kvarvarande) uppskjuten inkomstskatt (skuld och tillgångar), övriga tillgångar och andra skulder.

Steg 7A - Referera till historiska data från balansräkningen

Beräkna också förändringarna i dessa artiklar.

Steg 7B - Prognosera långfristiga tillgångar och skulder

Håll de långsiktiga artiklarna konstanta under projekterade år om inga synliga drivrutiner är
Länka de prognostiserade långsiktiga posterna till balansräkningen enligt nedan

Steg 7C - Referens till andra långfristiga poster till balansräkningen

Steg 7D - Länka de långsiktiga posterna till kassaflödesanalysen

Observera att om vi har hållit de långsiktiga tillgångarna och skulderna konstanta, skulle förändringen som flödar till kassaflödesanalysen vara noll.

# 8 - Ekonomisk modellering i Excel - Slutföra resultaträkningen

Innan vi går vidare i denna Excel-baserade finansiella modellering kommer vi faktiskt att gå tillbaka och titta på resultaträkningen igen
Fyll i det historiska viktade genomsnittliga andelarna och det utspädda viktade genomsnittliga antalet aktier
Dessa siffror finns tillgängliga i Colgates 10K-rapport

Steg 8A - Referera till de grundläggande och utspädda aktierna

Antag i detta skede att det framtida antalet bas- och utspädda aktier kommer att förbli detsamma som de var 2013.

Steg 8B - Beräkna grund- och utspädningsresultat per aktie

Med detta är vi redo att gå vidare till vårt nästa schema, dvs. aktieägarnas aktieschema.

# 9 - Ekonomisk modellering - Aktieägarnas aktieschema

Nästa steg i denna ekonomiska modellering i Excel-utbildning är att titta på aktieägarnas aktieschema. Det primära målet med detta schema är att projicera aktierelaterade poster som aktieägarnas kapital, utdelning, återköp av aktier, optionsprovisioner etc. Colgates 10K-rapport ger oss information om stamaktier och aktier under de senaste åren, som visas nedan. Colgate 2013 - 10K, Sida 68

Steg 9A - Återköp av aktier: Fyll i de historiska siffrorna

Historiskt har Colgate återköpt aktier som vi kan se schemat ovan.
Befolk Colgates aktieköp (miljoner) i excelbladet.
Länka den historiska utspädda EPS från resultaträkningen
Historiskt belopp som återköps bör hänvisas från kassaflödesanalysen

Ta också en titt på Accelerated Share Repurchase

Steg 9B - Återköp av aktier: Beräkna PE-multipeln (EPS-multipel)

Beräkna det underförstådda genomsnittskurset till vilket Colgate historiskt har gjort återköp av aktier. Detta beräknas som återköpt belopp / antal aktier
Beräkna PE-multipeln = Implicit aktiekurs / EPS

Steg 9C - Återköp av aktier: Hitta Colgates återköpta aktie

Colgate har inte gjort något officiellt tillkännagivande om hur många aktier de tänker återköpa. Den enda informationen om att deras 10K-rapportaktier är att de har godkänt ett återköp på upp till 50 miljoner aktier. Colgate 2013 - 10K, Sida 35

För att hitta antalet återköpta aktier måste vi anta återköpsbeloppet. Baserat på det historiska återköpsbeloppet har jag tagit detta nummer till 1 500 miljoner dollar för alla kommande år.
För att hitta antalet återköpta aktier behöver vi den beräknade underförstådda aktiekursen för den potentiella återköpet.
Underförstått aktiekurs = antagen PE multiplex EPS
Framtida återköp av PE-multipel kan antas utifrån historiska trender. Vi noterar att Colgate har köpt tillbaka aktier till ett genomsnittligt PE-intervall på 17x - 25x
Nedan är ögonblicksbilden från Reuters som hjälper oss att validera PE-sortimentet för Colgate

www.reuters.com

I vårt fall har jag antagit att alla framtida återköp av Colgate kommer att vara på en PE-multipel på 19x.
Med hjälp av PE på 19x kan vi hitta det implicita priset = EPS x 19
Nu när vi har hittat det implicita priset kan vi hitta antalet återköpta aktier = $ belopp som används för återköp / underförstått pris

Steg 9D - Aktieoptioner: Populera historiska data

Från sammanfattningen av stamaktier och eget kapital vet vi antalet optioner som utnyttjas varje år.

Dessutom har vi också optionens intäkter från kassaflödesanalyserna (ungefär)

Med detta bör vi kunna hitta det effektiva lösenpriset

Colgate 2013 - 10K, Sida 53

Observera också att optionerna har avtalsvillkor på sex år och intjänas över tre år. Colgate 2013 - 10K, Sida 69

Med dessa uppgifter fyller vi i Optionsdata enligt nedan. Vi noterar också att det vägda genomsnittliga lösenpriset på aktieoptionerna för 2013 var $ 42 och antalet optioner som kunde utnyttjas var 24,151 miljoner Colgate 2013 - 10K, sid 70

Steg 9E - Aktieoptioner: Hitta alternativet fortsätter

Om vi lägger in dessa siffror i våra optionsdata nedan, noterar vi att optionens intäkter är $ 1.014 miljarder

Steg 9F - Aktieoptioner: Prognosbegränsad lagerenhetsdata

Utöver aktieoptionerna finns det begränsade aktieenheter som ges till de anställda med en vägd genomsnittlig period på 2,2 år Colgate 2013 - 10K, Sida 81

Att fylla i dessa uppgifter i uppsättningen Alternativ För enkelhetens skull har vi inte planerat att utfärda optioner (jag vet att detta inte är rätt antagande, men på grund av brist på data tar jag inte fler optionsprovisioner framöver. Vi har just tagit dessa är noll som markeras i det grå området ovan. Dessutom beräknas de begränsade aktienheterna vara 2,0 miljoner framöver.

Ta också en titt på Treasury Stock Method

Steg 9G - Utdelningar: Prognosera utdelningarna

Prognos beräknad utdelning med utdelningsutdelning
Fast utdelningsutbetalning per utdelning
Från 10K-rapporterna hämtar vi all tidigare information om utdelning
Med informationen om utdelad utdelning kan vi ta reda på utdelningsgraden = Total utdelning / nettoinkomst.
Jag har beräknat utdelningsutdelningen för Colgate enligt nedan - Vi noterar att utdelningsgraden för utdelning har varit stort i intervallet 50-60%. Låt oss anta utdelningsgraden för utdelningar på 55% under de kommande åren.
Vi kan också länka det beräknade nettoresultatet från resultaträkningen
Med både det beräknade nettoresultatet och utdelningsgraden kan vi hitta den totala utdelningen

Steg 8H - Prognos för eget kapital i sin helhet

Med prognosen för återköp av aktier, optioner och utdelad utdelning är vi redo att slutföra aktieägarnas schema. Länka upp alla dessa för att hitta det slutgiltiga kapitalbalansen för varje år enligt nedan.

Steg 9I - Länka slutaktieägarnas kapital till balansräkningen

Steg 9J - Länk utdelning, återköp av aktier och optioner fortsätter till CF

# 10 - Utestående schema för aktier

Nästa steg i denna online-ekonomiska modellering i Excel-utbildning är att titta på schemat för utestående aktier. Sammanfattning av aktiernas utestående schema

Grundaktier - faktiska och genomsnittliga
Fånga tidigare effekter av alternativ och konvertibler efter behov
Utspädda aktier - genomsnitt
Referensaktier återköpta och nya aktier från utnyttjade optioner
Beräkna prognostiserade basaktier (faktiska)
Beräkna genomsnittliga bas- och utspädda aktier
Referensprojekterade aktier till resultaträkningen (återkalla resultaträkningen bygga upp!)
Input historiska delar utestående information
Obs : Detta schema är vanligtvis integrerat med aktieschemat

Steg 10A - Mata in de historiska siffrorna från 10K-rapporten

Utgivna aktier (faktisk realisering av optioner) och återköpta aktier kan hänvisas från aktieägarens aktieplan
Insatsen vägde också ett genomsnittligt antal aktier och effekten av aktieoptioner för de historiska åren.

Steg 10B - Länka aktieemissioner och återköp från aktieplanen.

Grundaktier (slut) = Grundaktier (börjar) + Aktieemissioner - Återköpta aktier.

Steg 10C - Hitta de viktade viktade genomsnittliga aktierna,

vi hittar i genomsnitt två år enligt nedan.
Lägg också till effekten av optioner och begränsade aktieenheter (refereras från aktieägarnas aktieplan) för att hitta de utspädda viktade genomsnittliga aktierna.

Steg 10D - Länka viktiga och utspädda viktade aktier till resultaträkningen

Nu när vi har beräknat utspädda viktade genomsnittliga aktier är det dags för oss att uppdatera detsamma i resultaträkningen.
Koppla upp prognostiserade utspädda viktade genomsnittliga utestående aktier till resultaträkningen enligt nedan

Med detta kompletterar vi schemaläggningen för aktier och tiden för att gå till nästa uppsättning uttalanden.

# 11 - Slutföra kassaflödesanalyserna

Det är viktigt för oss att fullfölja kassaflödesanalyserna innan vi går vidare till vårt nästa och sista schema i denna finansiella modellering, dvs. skuldschemat Fram till detta stadium är det bara ett par saker som är ofullständiga

Resultaträkning - räntekostnader / intäkter är ofullständiga i detta skede
Balansräkning - kontanter och skuldposter är ofullständiga i detta skede

Steg 11A - Beräkna kassaflöde för finansieringsaktiviteter

Kolla också in kassaflödet från finansiering

Steg 11B - Hitta nettoökning (minskning) av likvida medel

Steg 11C = Fyll i kassaflödesanalyserna

Hitta årets slut kontanter och likvida medel i slutet av året.

Steg 11D - Länka likvida medel till balansräkningen.

Nu är vi redo att ta hand om vårt sista och sista schema, dvs. skuld- och ränteplan

# 12 - Ekonomisk modellering i Excel - Schuld- och ränteschema

Nästa steg i denna finansiella online-modellering är att slutföra schemat och ränteschemat. Sammanfattning av skuld och ränta - schema

Steg 12A - Ställ in ett skuldschema

Referera till kassaflödet som är tillgängligt för finansiering
Referera till alla aktiekällor och användning av kontanter

Steg 12B - Beräkna kassaflöde från återbetalning av skulder

Hänvisa till början på kontantbalansen från balansräkningen
Dra av ett minsta kontantsaldo. Vi har antagit att Colgate vill behålla minst 500 miljoner dollar varje år.

Hoppa över långfristig skuldemission / återbetalningar, kontanter tillgängliga för revolverande kreditfacilitet och revolveravdelning för närvarande Från Colgates 10K-rapport noterar vi de tillgängliga uppgifterna om Revolved Credit Facility Colgate 2013 - 10K, Sida 35

I ytterligare information om skuld finns också de åtaganden om långfristig skuldåterbetalning. Colgate 2013 - 10K, Sida 36

Steg 12C - Beräkna den slutgiltiga långfristiga skulden

Vi använder schemat för återbetalning av långfristiga skulder som anges ovan och beräknar slutbalansen för långfristiga återbetalningar

Steg 12D - Länka de långsiktiga återbetalningarna.

Steg 12E -Beräkna diskretionära lån / nedbetalningar

Beräkna de diskretionära lånen / utbetalningarna med hjälp av kontotsvepformeln enligt nedan.

Steg 12F - Beräkna räntekostnaden från långfristig skuld

Beräkna det genomsnittliga saldot för revolverande kreditfacilitet och långfristig skuld
Gör ett rimligt antagande för en ränta baserat på informationen i 10K-rapporten
Beräkna totala räntekostnader = genomsnittlig skuldsaldo x räntesats

Hitta den totala räntekostnaden = ränta (revolverande kreditfacilitet) + ränta (långfristig skuld)

Steg 12G - Länk huvudskuld och revolverutnyttjande till kassaflöden

Steg 12H - Referensström och långsiktig balansräkning

Avgränsa den aktuella delen av långfristig skuld och långfristig skuld enligt nedan

Länka den roterande kreditfaciliteten, långfristiga skulder och den aktuella delen av långfristig skuld till balansräkningen

Steg 12I - Beräkna ränteintäkterna med hjälp av det genomsnittliga kontantsaldot

Steg 12J - Länka räntekostnader och ränteintäkter till resultaträkningen

Utför balansräkningskontrollen: Totaltillgångar = Skulder + Aktieägarnas kapital

Steg 12K - Granska balansräkningen

Om det finns någon avvikelse måste vi granska modellen och kontrollera om det finns några kopplingsfel

Rekommenderad ekonomisk modelleringskurs

Jag hoppas att du gillade Excel Excel-guiden för gratis finansiell modellering. Om du vill lära dig ekonomisk modellering i Excel genom våra expertvideoföreläsningar kan du också titta på vår Investment Banking-utbildning. Detta är främst 99 kurser Investment Banking utbildningspaket. Kursen börjar från grunderna och tar dig till avancerad nivå för Investment Banking Job. Kursen är uppdelad i 5 delar -

Del 1 - Investment Banking Training - Core Courses
(26 kurser)
Del 2 - Advanced Training Banking Modeling Training
(20 kurser)
Del 3 - Tillägg till Investment Banking
(13 kurser)
Del 4 - Kurser för Foundation Banking Foundation
(23 kurser)
Del 5 - Mjuk kompetens för investeringsbanker
(17 kurser)

Ladda ner ekonomiska modeller

Alibaba ekonomiska modell
Box IPO Financial Model
Mallar för ekonomisk modellering
Banking Financial Modelling Course

Vad händer nu?

Om du har lärt dig något nytt eller gillat denna Excel-baserade finansiella modellering, vänligen lämna en kommentar nedan. Låt mig veta vad du tycker. Stort tack och ta hand. Happy Learning!