9780273772255

All the tools and techniques you need to build, develop, and apply reliable financial models with Microsoft Excel. Updated to reflect Excel 2010 features (including sparklines) and Mac versions of Excel, but still works exceptionally well with older Windows versions of Excel. Fully explains basic and complex modeling techniques, and offers specific, detailed coverage of models ranging from cash flow to company valuation. CD contains models and templates designed for clarity and easy adaptation. This book brings together everything financial professionals and students need to build and develop reliable financial models with the latest versions of Microsoft Excel, and use those models to make more effective and successful decisions. Updated for new Excel 2010 features (including sparklines) as well as the newest versions of Excel for Mac, this edition also remains fully appropriate for older versions of Excel. Leading financial consultant and writer Alastair Day begins by explaining basic modeling techniques, then moves step by- step through building increasingly complex models. In Part I, Day introduces the essentials of model design, demystifying features and techniques every model builder must understand, and showing how they come together in a complete model. Next, in Part II, he focuses on creating accurate models for many specific applications. These include: performance analysis, cash flow, forecasting, variance analysis, breakeven analysis, cost of capital, bonds, investment analysis, risk analysis/management, depreciation, leasing, company valuation, optimization, and much more. Along the way, Day explains relevant data functions and analysis techniques, and presents full chapters on both decision trees and optimization. An accompanying CD-ROM provides an extensive library of templates and sample models designed for easy adaptation. The book also provides end-of chapter exercises, making it an even more valuable resource for formal or classroom study.

Alastair Dayhas worked in the finance industry for 20 years in treasury and marketing functions. In 1990 he established Systematic Finance as a consultancy and financial lessor concentrating on the computer and communications industries. Alastair has a degree in Economics and German from London University, an MBA from the Open University Business School, and is an associate lecturer in corporate finance with the OUBS. He is the successful author of several books including Mastering Cash Flow and Valuation Modelling, Mastering Risk Modelling and The Financial Director’s Guide to Purchasing Leasing.

Contents

Introduction – who needs this book?

Preface to the third edition

Acknowledgements

Conventions

Executive summary

Part A DEVELOPING FINANCIAL MODELS

1 Overview

Introduction

What is financial modelling?

History of spreadsheets

Power of spreadsheets

Objectives for the book

Example spreadsheet

Summary

2 Design introduction

Introduction

Basics of design

Objectives

User interface

Key variables and rules

Layout

Individual modules

Menu structure and macros

Management reporting

Future development

Testing

Protection

Documentation

Peer group comments

Summary

3 Features and techniques

Introduction

Formats

Number format

Lines and borders

Colours and patterns

Specific colours for inputs and results

Data validation

Controls – combo boxes and buttons

Conditional formatting

Use of functions and types of function

Add-ins for more functions

Text and updated labels

Record a version number, author, etc.

Use names to make formulas easier to understand

Paste names as part of documentation

Comment cells

Graphics

Dynamic graphs to plot individual series

Data tables

Scenarios

Goal seek

Solver

Use of templates

Summary

4 Sample model

Introduction

Aims and objectives

User needs and user interface

Key variables and rules

Breaking down the calculations into manageable groups

Setting up individual modules

Menu structure

Program sheets and macros

User assistance

Summaries

Risk and multiple answers

Testing and troubleshooting

Protecting and securing

Help and documentation

Show to peers – take their advice

Control loop – listen, learn and modify

Summary

5 Example model

Introduction

Case study

Design

PPP 1

PPP 2

PPP 3

PPP 4

PPP 5

Documenting, testing and protecting

PPP 6

Summary

Part B APPLICATIONS

6 Analysing performance

Introduction

Profit and loss

Balance sheet

Ratios

Trend analysis

Sustainability

Summary

7 Cash flow

Deriving cash flow

Net operating cash flow (NOCF)

Free cash flow

Cover ratios

International cash flow

Summary

8 Forecasting models

Introduction

Historic forecasts

Trend lines

Trend lines for analysis

Data smoothing

Cyclicality and seasonality

Summary

9 Forecasting financials

Introduction

Key drivers

Deriving financial statements

Alternative approaches

Financial analysis

Summary

10 Variance analysis

Introduction

Cash flow budgets

Monthly cash model

‘Flash’ report and graphics

Summary

11 Breakeven analysis

Introduction

Breakeven

Operating leverage

Financial leverage

Combined leverage

Summary

12 Portfolio analysis

Introduction

Formulas

Optimum portfolio

13 Cost of capital

Introduction

Capital Asset Pricing Model

Dividend growth model

Cost of preference shares

Cost of debt

Weighted average cost of capital

Marginal WACC

Summary

14 Bonds

Introduction

Cash flows

Yield measures

Duration

Portfolio results

Summary

15 Investment analysis

Introduction

Investment model revisited

Payback period

Accounting return

Net present value

Internal rate of return

Benefit/cost ratio

Management tests – cash flow, etc.

Scenarios

Sensitivity analysis and charts

Capital rationing

Summary

16 Risk Analysis

Introduction

Risk assessment process and analysis

Risk adjusted rate iation

Coefficient of variation

Certainty equivalents

Real options

Simulation

Summary

17 Depreciation

Introduction

Straight line

Sum of digits

Declining balance

US MACRS

Amortisation

Comparison

Summary

18 Leasing

Introduction

Rental calculations

Lease versus purchase

Classification

Accounting

Settlements

Summary

19 Company valuation

Introduction

Accounts

Adjusted accounting value

Dividends

Stock market or market method

Free cash flows

Summary

20 Optimisation

Introduction

Elements of optimisation models

Linear programming

Margin maximisation

Pensions

Summary

21 Decision trees

Introduction

Bayes’ theorem

Terminology

Decision tree model

Information example

Summary

22 Risk management

Introduction

Forward rate agreements

Swaps

Foreign exchange

Futures

Options

Black-Scholes

Summary

23 Data functions

Introduction

Dataset

Database functions

Descriptive statistics

Histogram

Function output

Box plots

Summary

24 Data analysis

Introduction

Imported data

Sort

Filter

Subtotals

Pivot table and chart

Database import

Radar chart

Gauge chart

Summary

25 Modelling checklist

Introduction

Design summary

Features

Techniques

Appendices

Appendix 1: Software installation

Appendix 2: Licence

Appendix 3: File list

Bibliography and references

Index

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

Amazon no longer offers textbook rentals. We do!

Mastering Financial Modelling in Microsoft Excel 3rd edn A Practitioner's Guide to Applied Corporate Finance

Mastering Financial Modelling in Microsoft Excel 3rd edn A Practitioner's Guide to Applied Corporate Finance

0273772252

Summary

Author Biography

Table of Contents

Supplemental Materials