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

Innehållsförteckning

Vad är ekonomisk modellering i Excel?

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?

Ekonomisk modellering i Excel finns överallt på nätet. Det har skrivits mycket om att lära sig ekonomisk modellering; emellertid är de flesta av de finansiella modelleringsutbildningarna desamma. 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 är ö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

Ange e-postadress Genom att fortsätta ovanstående steg godkänner du våra användarvillkor och sekretesspolicy.

Steg 2 - Observera att du får två mallar - 1) Oupplöst 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 finansiella modellering

Hur bygger man en ekonomisk modell?
# 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 aktieplan
# 10 - Utestående schema för aktier
# 11 - Slutföra kassaflödesanalyserna
# 12- Schema 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. Det modulära systemet innebär i huvudsak att bygga 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ödprogram till kärnuttalanden när de är färdiga. Jag kan förstå att detta kanske inte är klart nu; dock kommer du att inse att detta är väldigt enkelt när vi går framåt. Du kan se nedan olika finansiella modeller / scheman -

Snälla notera följande -

Kärnuppgifterna är resultaträkningen, balansräkningen och kassaflödena.
De olika scheman är avskrivningsplanen, rörelsekapitalschemat, immateriella schemat, aktieägarnas schema, andra långsiktiga poster, skuldschema etc.
De olika 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 företagets finanser och fylla i samma i ett excel-ark. För Colgate Palmolive kan du ladda ner årsredovisningarna för Colgate Palmolive från deras sektion för investerarrelationer. När du klickar på "Årsredovisning" hittar du fönstret som visas nedan -

Steg 1B - Skapa kalkylbladet Historiska finansiella rapporter

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 måste 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 elementanalysen 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 fördjupad analys 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 avseende försäljning.

Vertikala analysresultat

Vinstmarginalen hade ö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 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 ökade ”Övriga kostnader”. Kontrollera också skillnaden mellan EBIT och EBITDA
Nettovinstmarginalen ökade från 12,6% 2007 till 14,5% 2012. Men vinstmarginalen 2013 minskade till 12,9%, främst på grund av ökade ”övriga kostnader”.
Vinsten 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 excel 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 valutan med olika köpkrafter. I själva verket indexerar denna analys rapporterna och jämför utvecklingen av dessa över tid. Precis som 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 symtom på problem som kan diagnostiseras med hjälp av ytterligare metoder.

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

Horisontella analysresultat

Vi ser att nettoomsättningen ökade med 2,0% 2013.
Notera också trenden i försäljningskostnader. 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 den nuvarande förhållandet, syra test (eller snabb tillgång) förhållandet 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 mer betydande än 1,0 för alla år. Detta innebär att omsättningstillgångar är betydelsefullare än kortfristiga skulder, och kanske har Colgate tillräcklig likviditet.
Den snabba förhållandet mellan Colgate ligger i intervallet 0,6-0,7; Detta innebär att Colgates Cash och Marketable Securities kan betala så mycket som 70% av kortfristiga skulder. Det här ser ut som en bra situation för Colgate.
Kassacykelcykeln hade 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 avkastning på närmare 100%, vilket innebär utmärkt avkastning till aktieägarna.

Steg 2E - Riskanalys av Colgate

Genom riskanalys försöker vi bedöma om företagen kommer att kunna betala sina korta och långsiktiga förpliktelser (skuld). Vi beräknar skuldsättningsgrader som fokuserar på tillgångarnas tillräcklighet eller generering från tillgångar. Priser 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 indikerar mindre risk för räntebetalning.

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

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

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 typen och mängden av inkomstflöden exakt är extremt viktig. Det finns lika många sätt att skapa 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- / mixeffekter: 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 mot 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 avgörande variabel. (För ett företag med en prisdiskonteringsstrategi, till exempel, eller en bäst av ras premiumpris nischspelare) t.ex. lyxbilsmarknaden
Volymkapacitet, kapacitetsutnyttjandegrad och genomsnittspris: Dessa antaganden kan vara viktiga för företag där produktionskapacitet är avgörande 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äkter baserat på installerad bas (fortsatt försäljning av delar, engångsartiklar, service och tillägg etc.). Exempel är klassiska rakbladföretag och företag som datorer där försäljning av service, programvara och uppgraderingar är nödvändig. Modellering av den installerade basen är nyckeln (nya tillägg på golvet, förslitning i marken, 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; som ytterligare information har Colgate dock lämnat några detaljer om segment på sidan 87 Källa - Colgate 2013 - 10K, sidan 86

Eftersom vi inte har ytterligare information om funktionerna kommer vi att projicera den framtida försäljningen av Colgate på grundval av tillgängliga data. Vi kommer att använda försäljningstillväxtmetoden ö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 element. Nu kan vi anta en försäljningstillväxtprocent baserat på de historiska trenderna och projicera intäkterna under varje del. Total nettoomsättning ä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 annat schema: Detta tillvägagångssätt är verkligen det lägsta som är acceptabelt i de flesta fall och tillåter endast partiell analys av driftshävstång.
Variabla kostnader baserade på intäkter eller volym, fasta kostnader baserat 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 utgiftsmarginalerna och sedan prognostisera framtida marginal.
Exempelvis har försäljningskostnaden legat i intervallet 41-42% de senaste fem å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 den framtida SG&A-utgiftsmarginalen på grundval av detta. 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 (inkomst)" 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å schemat för rörelsekapital.

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

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

Steg 4B - Referens 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 framträdande förare antas vanligtvis i konstanta belopp
Se till att antagandena är rimliga och i linje med verksamheten

Steg 4E - P roject 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 - Avskrivningsplan

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 och avskrivningar tillhandahålls inte som en separat rad; dock ingår det i försäljningskostnaden
I sådana fall, ta en titt på kassaflödesanalyserna där du hittar avskrivningar och avskrivningar. Observera också 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 avskrivningsschemat

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. En vanlig praxis ä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 slutbalansen för PPE = nybalans + capex - avskrivningar - justering för tillgångsförsäljning (BASE-ekvation)
Det är komplicerat 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 istället förenat 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 utrustningsdetaljerna 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 anges i 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 två eftersom vi antar halvårskonventionen för tillgångsutnyttjande.

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 avskrivningar 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 (ytterligare utrustning) När vi har fått reda på de verkliga 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 beakta 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 vanligtvis komplicerat att projicera goodwill för kommande år.
Goodwill testas dock årligen av nedskrivningsprövningar som utförs av företaget självt. Analytiker har ingen möjlighet att genomföra sådana tester och upprätta uppskattningar av nedskrivningar.
De flesta analytiker projicerar inte goodwill; de håller bara detta konstant, vilket 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 liv relaterat till Sanex-förvärvet
”Tillägg till immateriella tillgångar” är också komplicerade att projicera
Colgates 10K-rapport ger oss information om de kommande fem års avskrivningskostnader.
Vi kommer att använda dessa uppskattningar i vår finansiella modell Colgate 2013 - 10K, sidan 61

Steg 6C - Avslutande 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 ekonomiska modellering är att förbereda det andra långsiktiga schemat. Det är då vi förbereder oss för ”resterna” som inte har specifika drivkrafter för prognoser. När det gäller Colgate var de övriga långfristiga posterna (rester) uppskjuten inkomstskatt (skuld och tillgångar), andra investeringar 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 - Hänvisning till andra långsiktiga 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 att gå tillbaka och titta på resultaträkningen
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 primära och utspädda aktier förblir detsamma som under 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 finansiella modellering i Excel-utbildning är att titta på aktieägarnas aktieschema. Det huvudsakliga 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
Den historiska återköpta mängden 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 meddelande om hur många aktier de tänker återköpa. Den enda informationen att deras 10K-rapportaktier är att de har godkänt ett återköp av 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 som $ 1 500 miljoner för alla kommande år.
För att hitta antalet återköpta aktier behöver vi det beräknade implicita aktiekursen för den potentiella återköpet.
Faktisk aktiekurs = antagen PE multiplex EPS.
Framtida återköp av PE-multipel kan antas baserat på historiska trender. Vi noterar att Colgate har återköpt 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 se 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 borde vi kunna hitta ett effektivt lösenpris.

Colgate 2013 - 10K, Sida 53

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

Med dessa uppgifter fyller vi i alternativdata enligt nedan. Vi noterar också att det vägda genomsnittliga lösenpriset för aktieoptioner för 2013 var $ 42 och antalet optioner som kunde utnyttjas var 24,151 miljoner Colgate 2013 - 10 000, 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 begränsade aktienheter till de anställda med en vägd genomsnittlig period på 2,2 år Colgate 2013 - 10K, sid 81

Att fylla i dessa data i alternativdatasetet För enkelhets skull har vi inte projicerat optioner (jag vet att detta inte är rätt antagande, men på grund av brist på data tar jag inga fler alternativfrågor framåt. Vi har just tagit dessa som noll, som markeras i det grå området ovan. Dessutom beräknas de begränsade lagerenheterna 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å utdelningsutdelningen = Totalt utdelad utdelning / nettoresultat.
Jag har beräknat utdelningsgraden för Colgate enligt nedan - Vi noterar att utdelningsgraden för utdelning har varit stort i intervallet 50-60%. Låt oss anta utdelningsgraden 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 utdelningsutdelningen kan vi hitta den totala utdelningen som betalats.

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 aktieschema. Koppla samman alla dessa för att hitta det slutliga kapitalbalansen för varje år, som visas 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 beräknade råa procentsatser (faktiska)
Beräkna genomsnittliga bas- och utspädda aktier
Referensprojekterade aktier till resultaträkningen (minns 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 aktieschema
Insatsen vägde 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 genomsnittliga aktierna,

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

Steg 10D - Länk grundläggande och utspädda viktade aktier till resultaträkningen

Nu när vi har beräknat de utspädda viktade genomsnittliga aktierna ä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 slutför vi schemaläggningen av 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 det här steget finns det bara ett par ofullständiga saker

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 - Koppla 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

Grad 12A - Ställ in ett skuldschema

Referera till det kassaflöde 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ångsiktig skuldemission / återbetalningar, kontanter tillgängliga för revolverande kreditfacilitet och revolveravdelning för tillfället Från Colgates 10K-rapport; vi noterar tillgängliga uppgifter om Revolved Credit Facility Colgate 2013 - 10K, Sida 35

I ytterligare information om skulden finns också de åtagna långfristiga återbetalningarna. Colgate 2013 - 10K, Sida 36

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

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

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

Steg 12E -Beräkna de diskretionära lånen / uppspelningarna.

Beräkna diskretionära upplåningar / utbetalningar med hjälp av kontotsvepformeln, som visas 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 - Huvudsaklig länk skuld och revolver uttag 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 revolverande kreditfaciliteten, långfristiga skulderna 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 med grunderna och tar dig till avancerad nivå för Investment Banking Job. Kursen är uppdelad i fem delar -

Del 1 - Utbildning för investeringsbanker - Kärnkurser
(26 kurser)
Del 2 - Utbildning för avancerad investeringsbankmodellering
(20 kurser)
Del 3 - Tillägg till Investment Banking
(13 kurser)
Del 4 - Kurser för Investment 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 finansiell modellering
Banking Financial Modelling Course

Vad händer nu?

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