Financial Modelling in Excel

Die 10 wichtigsten Methoden für

das Financial Modelling in Excel

Kostenfreies Webinar am 12. Juli 2012

Ihr heutiger Referent – Sebastian Zang

Meilensteine der beruflichen Entwicklung

2006 – 2010 Betriebswirtschaftlicher Berater (zuletzt: Prokurist)

Wirtschaftsprüfungs- & Beratungsunternehmen KPMG AG

Konzeptentwicklung & Implementierung von Better Practice

Prozessen sowie Tools für Finanzorganisationen:

Business Intelligence, Management Informations Systeme,

Controllingtools, Planungssysteme, Simulationswerkzeuge

2011 - heute Geschäftsführer Categis GmbH

Rundum-Service für MS Excel und weitere Office-Anwendungen für

Finanz- und Vertriebsorganisationen

• On-Demand Service (Coaching) zur Fehlerbehebung oder zur

technischen Beratung

• Automatisierung bestehender Anwendungen

(mithilfe VBA Programmierung)

• Entwicklung maßgeschneiderter Tools und Funktionsmodule

Sebastian Zang

2

3

Relevanz von Financial Modelling

"Willkommen in der volatilen Welt“ McKinsey Studie 2011

"Unternehmen sollten jederzeit

darauf vorbereitet sein, unverhoffte

Chancen ergreifen und unerwar-

tete Schocks absorbieren zu

können."

Quelle: http://www.finanzen.net (14.04.2012)

4

Ihr heutiges Webinar

Struktur & Konzeption

Bewährte Struktur eines Financial Models

Methodische Herangehensweise an die Konzeption eines Financial Models

Detaillierte Formelanalyse im Bereich Darlehen

Szenarien

Differenzierung zwischen Szenario-Parametern und Prämissen

Technische Implementierung von Szenarien

CashFlow – eine smarte Berechnungsmethode

Eine CashFlow Methodik, die immer aufgeht

Umsetzungsbeispiel in MS Excel

1

2

3

5

Grundstruktur von Financial Models in MS Excel

Kalkulation & Reporting

Import der Plandaten

für ein „Base Case“

ODER / UND

Import der IST-Daten

als Startpunkt für das

Financial Model

Rohdaten(import) Annahmen / Szenarien Umsatz, Expansion,

Working Capital,

Zinssätze, Covenants

M&A, Desinvestition

Verschiedene

Szenarien

(Base / Worst Case)

Datenverarbeitung auf Basis der

Logik des Geschäftsmodells

Erstellung eines Sets integrierter

Finanzdaten für GuV, Bilanz und

Cash Flow

1 2

3

6

Grundstruktur von Financial Models – Beispielhafter Aufbau

Konsolid

ieru

ng +

Sonstiges

Prämissen Darlehen

Steuern

Etc.

Szenarien Umsätze

Zinssätze

Wechselkurse

Etc.

Ergebnis

Kalkulation [Szenarien]

Input / Rohdaten

Ergebnis

Prämissen & Szenarien Legaleinheiten Konzern

7

Detailanalyse Formelverwendung – Darlehensberechnung

=+WENN(repay_A_USD="Amortisation";

WENN(REST(K$231;1/(12/repay_period_A_USD))=0;1;0);

WENN(UND(final_mat_A_USD>=J$4;final_mat_A_USD<=K$4);1;0))

Welche

Tilgungsvariante Wenn „Amortization“, dann

Überprüfung ob Tilgung in

Quartal ja/nein

Wenn „Amortization“, dann

Überprüfung ob Tilgung in

Quartal ja/nein

1

2

8

Detailanalyse Formelverwendung – Darlehensberechnung

=-K65*K70*BRTEILJAHRE(J256;K256;yearfrac_ind)

Schuldenstand zu

Beginn der Periode

Anzuwendender

Jahreszinssatz

Ermittlung des Bruchteils des Jahres

zur Ermittlung des periodenrelevanten

Zinses

BRTEILJAHRE (Excel-Funktion):

„Wandelt die Anzahl der Tage zwischen

Anfangsdatum und Enddatum

in Bruchteile von Jahren um“

„yearfrac_ind“ (Funktionsparameter)

Zinsmethode

(vgl. Tabelle unten)

Detailanalyse Formelverwendung – Darlehensberechnung (1 von 3)

Aufgelaufene Zinsen (Bilanz)

Zinsaufwand (GuV)

Zahlungswirksame Zinsen

(Cash Flow)

9


=+(K63=0)*(K65*K70*BRTEILJAHRE(J257;K257;yearfrac_ind)+J72)

+(K63=1)*(K65*K70*BRTEILJAHRE(K$257;K$4;yearfrac_ind))

Verkürzte WENN-Formel:

„Wenn Zelleninhalte in K63 gleich 0,

dann ‚1‘ (Wahr), sonst ‚0‘ (Falsch)“

10


=+(K63=0)*(K65*K70*BRTEILJAHRE(J257;K257;yearfrac_ind)+J72)

+(K63=1)*(K65*K70*BRTEILJAHRE(K$257;K$4;yearfrac_ind))

Gliedern Sie grundsätzlich Ihre Excel-Formeln im Formel-

Editor über Absätze / Zeilensprünge: „Alt“+“Enter“

Aufgelaufene Zinsen

der Vorperiode

11

Konzeptionelle Entwicklung des Financial Models (1 von 2)

+Umsatz

-Umsatzkosten

= DB

-Abschreibungen

-Weitere Kosten

= EBIT

+/- Finanzergebnis

= EBT

-Steuern

=Jahresüberschuss

+/- Gewinn-

/Verlustvortrag

+/- Änderung

Rücklagen

-Ausschüttung

=Vortrag auf neue

Rechnung

+/- CF aus laufender

Geschäftstätigkeit

+/- CF aus

Investitionstätigkeit

+/- CF aus

Finanzierungstätigkeit

=Änderung liquider Mittel

+Liquide Mittel Anfang

Periode

+Konsolidierungsbedingte

Änderung

+Wechselkursbedingte

Änderungen

Liquide Mittel am Ende

der Periode

AKTIVA

Anlagevermögen

Umlaufvermögen

Liquide Mittel

PASSIVA

Eigenkapital

-Bewertungs-RL

-Rücklagen

-Bilanzgewinn

Rückstellungen

-Langfristig

-Kurzfristig

Verbindlichkeiten

-Verzinslich

-Unverzinslich

Working

Capital

Planung

Investitions-

Planung

Finanzierungs-

Planung

Personal-

Planung

…

…

12

Beschaffungs

-Planung

Konzeptionelle Entwicklung des Financial Models (2 von 2)

13

Verarbeitungslogik & Struktur des Financial Models

Je konsolidierter Gesellschaft wird ein Tabellenblatt mit integrierter Bilanz, GuV sowie

Kapitalflussrechnung erstellt

Ausländische Gesellschaften werden jeweils in Landeswährung und EUR dargestellt:

Währungsumrechnung erfolgt gemäß der modifizierten Stichtagskursmethode

[Abstimmung mit Herrn XY am DD.MM.YYYY]

Umrechnungsdifferenzen werden erfolgsneutral im Eigenkapital erfasst

[Abstimmung mit Frau AB und Herrn XY am DD.MM.YYYY]

Es wird ein konzernweites Treasury unterstellt, das mittels eines konzernweiten

Verrechnungskontos für liquide Mittel und kurzfristige Verbindlichkeiten aus

Kontokorrentkredit synthetisiert wird

…

…

…

14






Szenarien






1

2

3

15


=INDIREKT(

$R$6&"!"&

SVERWEIS(ZELLE("spalte";E$1);Hilfstabelle!$A$1:$B$21;2)&

ZELLE("zeile";$A11))

Sverweis + Hilfstabelle: Wandelt

Spaltennummer in Spaltenbuchstabe um

… Tabellenblatt =>

Adressangaben (INDIREKT) …

… Spalte =>

… Zeile =>

16






Szenarien






1

2

3

17

CashFlow Ermittlung – WP Methodik

…

…

Operating CF

…

…

Investing CF

Financing CF

Δ Finanzmittel

Bew

egungsbila

nz

(ohne K

asse)

Δ Kassenbestand

Umgliederungen: z.B. Zinsaufwand, Abschreibungen, …

Σ

18


Grundsätzliche Bedeutung von MS Excel in Unternehmen

Stärken / Vorteile von MS Excel

Buchtipps & der Excel Service von Solutiontogo

Die Bedeutung von Excel im Financial Modelling

19

Peripherieanwendungen Hohe Praxisrelevanz

Microsoft Technologie wird sehr häufig ge-

nutzt zur Abbildung von Unternehmenspro-

zessen [ergänzend zu Kernanwendungen]

Hohe Kritikalität zahlreicher Peripherie-

anwendungen aufgrund der Abbildung von

Kernprozessen eines Geschäftsmodells

Niedriger Automatisierungsgrad & geringer

Datenqualitätsfokus aufgrund des hohen

Entwicklungsanteils durch Fachabteilungen

Die Bedeutung von Microsoft Anwendungen heute … und morgen

20

Flexibilität: Umfangreiche Funktionsmodule

von Microsoftanwendungen in Verbindung

mit VBA Programmierung erlauben die

Abbildung komplexer Geschäftsprozesse

Integrationsfähigkeit: Microsoftanwendun-

gen lassen sich dank leistungsfähiger

Schnittstellen an alle gängigen Vorsysteme

anbinden

Rapid Development: Verfügbare Funktions-

module der Standardanwendungen sowie

Modul-Sharing der MS-Community ermögli-

chen schnelle Entwicklungsergebnisse

Hochwertige Entwicklungen in Microsoft Technologie

Stärken von Microsoft Nr. 1 in Business Intelligence

21

Optimierung von Unternehmensprozessen - Quick Wins

Hoher Zeitgewinn für Prozessbeteiligte

durch Automatisierung zahlreicher Prozess-

schritte & Einführung von Best Practices

Maximierung der Datenqualität durch

automatisierte Validierungsroutinen und

verbesserte Datenhaltung

Minimale Investitionen aufgrund Eignung

von Microsoft Technologie als „Rapid

Development Tool“

Datenkonsolidierung

Datenimport & -abgleich

Datenvalidierung

Datenmanagement

Kleine Tools in MS Excel

zur Verwaltung von Daten

Reporting

Vom einfachen Bericht

zum Vorstandsreport

Datenverarbeitung

CashFlow Berechnung

Überleitungen, et cetera

Zeitgewinn Optimierungspotential Ihre Quick Wins

ON-DEMAND Support: Experten-Knowhow, wenn Sie es brauchen!

Webkonferenz

Telefon

Email

Fehleranalyse

Fehlende Erfahrung bei

Umsetzung einer

Anforderung

Zeitaufwändige Umsetzung

von Arbeitsprozessen

Ihre Herausforderung Unser Lösungsangebot

Expertenrat bei allen Fragen

rund um MS Excel

Programmierung kleiner

Makros für einen effizienten

Arbeitsprozess

22

BUCHTIPPS

Financial Analysis and Modeling –

Using Excel and VBA Von Chandan Sengupta

Verlag Wiley Finance – 793 Seiten

ISBN: 978-0-470-27560-3

Professional Excel Development The definitive Guide to Developing Applications

Using Microsoft Excel, VBA, and .NET Von Bob Bovey, Dennis Wallentin, Stephen Bullen, John Green

Verlag Addison-Wesley – 1.156 Seiten

ISBN-13: 978-0-321-50879-9

23

Nächstes Webinar – Mittwoch, 22. August

24

Financial Modelling in Excel unter Nutzung der FreeWare PALO

Kostenfreies Webinar am Mittwoch, 22. August, 16.00 Uhr

Einführung in die FreeWare Datenbank

PALO: Nutzungsphilosophie & Vorteile

Gestaltung von schlanken Financial

Models mit Excel in Verbindung mit PALO

Einfache Methodik zur Nutzung von

Szenarienrechnungen

Solutiontogo

„Das Ergebnis jedes Leistungsprozesses

wird begrenzt durch das am knappsten

vorhandene Hilfsmittel: Die Zeit.“

Peter F. Drucker

Smarte Arbeitsprozesse

Categis GmbH

Am Hofgrund 21

97769 Bad Brückenau

www.solutiontogo.de

[email protected]

Financial Modelling in Excel

Documents

Transcript of Financial Modelling in Excel