PRINCIPLES AND PRACTICE

Written for professional modellers for professional modellers and students.
ISBN: 978-0-9803286-0-8

Overview

The book is designed for Financial Modellers who are either already professional modellers or students looking to develop modelling skills and business solutions particularly for third party use.  The book and accompanying CD are produced to work together providing a hands-on learning experience as well as a reference source for modellers. Part A (8 chapters) covers the tools and principles of modelling, introducing new Excel 2007 functions and features. These early chapters also introduce readers to modelling techniques and sound methodology such as error trapping, input control and overall design.

Part B (10 chapters) includes most of the common financial modelling topics, while also introducing additional powerful tools for specific business solutions.An expanded text including new topics for this powerful application -  Excel 2007

The accompanying CD includes framework files for each chapter, case studies, and data for problems, as well as solutions for chapter examples and case studies.

A step by step approach is used in all examples since the text is designed to facilitate and re-enforce self learning.             

(680 pages).

Download Sample Chapter

CONTENT

PART A

Chapter 1  - Basic Excel 2007 Tools
This is an introductory chapter on common modelling features of Excel 2007. It includes working with:
- Graphical and arithmetic functions.
- Operational rules, colour use, and data formula development.
- Protection and security.
- Printing and presentation.
- Sorting and filtering with Excel 2007 new.

Chapter 2  - Excel 2007 Functions and Features
- More complex functions used in modelling are considered, including:
- Logical functions and extensions.
- SUM and its variations including new SUMIFS.
- ARRAY functions uses and examples.

Chapter 3 - Advanced Excel Features
Some very specific and useful modelling tools are covered with several of these used repeatedly in later chapters:
- Dynamic Graphing with automatic inclusions.
- Database functions and uses.
- Importing data - cleaning and striping.
- Pivot Tables and the new options for reports.
- Using Solver to optimise model solutions.

Chapter 4 - Macros and Screen Control
Control of screen operations, functions creation and macro writing, plus:
- The macro recorder - writing code
- Useful routines and procedures for modellers.
- Input ad Message boxes - control loops.
- Macros for importing data.
- Creating User Forms and worksheet control.
- Scrolls, buttons and ticks.

Chapter 5  - Model Design and Development
This critical chapter examines the rationale behind modelling - its advantages and problems. A systematic approach to modelling is outlined together with:
- Modelling methodology and ideal structure.
- The modelling process and development
- Error trapping and Excel functions
- Input controls using Data Validation.
- Database control and model Testing.
- Function and model logic auditing

Chapter 6  - Financial Functions
Excel’s powerful financial functions are covered here:
 - from depreciation, through time value (both single sum and series functions) and DCF functions to bond valuation.
- Depreciation functions and uses.
- Time value functions, including a loan model.
- Rate, payment and time variations using Excel functions.
- Bond and Debenture functions and uses including zero coupon bonds.
- MacCauley DURATION functions and variance.

Chapter 7  - Uncertainty and Probability
This is the foundation chapter for two later applications chapters on uncertainty and simulation, hence it includes:
- Risk and probability concepts and measures.
- Excel 2007 functions for dispersion and risk identification.
- Probability distributions and Excel’s functions.
- Bayesian Probability and using Excel.

Chapter 8   - Forecasting in Models
Forecasting is of primary importance to management, planners, investors, and modellers alike. Key issues are:
- Type and tests of forecasting methods including auto-correlation.
- Static Data forecasting, smoothing techniques.
- Linear, Curvilinear and multiple Regression using Excel’s Add-in.
- Time Series - trend, cycles and seasons.
- Scrolls, buttons and ticks.

PART B

Chapter 9 - Pro-Forma Statement Models
The chapter begins with short term cash budgeting, but examines the accrual to cash problem and creating complete budgetary systems. It also includes:
- Cash Flow Budgeting, accrual/ cash relations.
- Credit Policy and working capital inclusions.
- Cash and Resource Use - budget revisions.
- Project Cash budgeting.
- A complete budgeting system.

Chapter 10  - Pro-forma Statement Models
This chapter builds the most common pro-forma statements used in financial modelling with the emphasis on linkages and external funding models. It includes:
- Forecasting, Linking and Standards for pro-forma models.
- Income Statement and Balance Sheet models using Scenario Manager.
- Pro-forma modelling issues- circular references, summarising, and external funding.
- Free Cash Flow and Funds Statement inclusions.

Chapter 11 - Statement Analysis
Modellers use accounting data and interpret it for assessment of performance, planning and control. The chapter includes:
- Ratio analysis including Performance, Liquidity, Leverage and Coverage ratios.
- Auto signalling models, common size analysis.
- Market based ratios and building automatic signalling and alarm models.
- Using common size financial for comparisons.
- A Dupont model - a structured approach.

Chapter 12  - Break-Even and Optimisation
The chapter begins with the basic accounting concepts of break-even. It uses Goal Seek and Solver in:
- Break-even and Target level including by-product models.
- Operating and Financial Leverage modelling.
- NPV and Break-even including non-linear break-even models.
- Regression, and optimisation functions in break-even modelling.

Chapter 13  - Applications of Probability
The first of two application chapters focussing on uncertain variables and modelling outcomes. It includes:
- Scenario and Sensitivity Analysis with Excel.
- Certainty Equivalence and RADR
- Pay-off Tables and models.
- Decision Trees including joint probabilities.

Chapter 14  - Simulation Models
This chapter uses Excel 2007 random number generation and uncertain variables to develop VaR models. It  includes:
- Monte Carlo simulation methods.
- VaR models including Financial Planning.
- Single and multi level Queuing models.
- Markov analysis for specific modelling problems including Bad Debt valuation.

Text Box: Chapter 15  - Options and Portfolio Insurance
Both theoretical and practical aspects of share market investment are developed, providing the user with strong practical models. The topics covered include:
- Option terminology and Binomial options.
- Option valuation and Black-Scholes.
Share Price simulation and Options.
- Convertible securities and valuation.
- Portfolio insurance modelling and strategies.



Chapter 16  - Equity and Portfolio Models
Models to value equity and portfolios are developed with the main topics including:
- Valuing equity using CAPM, security betas, dividend and free cash flow.
- Over and Under valued equity models.
- Portfolio risk and Return - Markowitz model.
- An efficient portfolio and share correlations.

Chapter 17  - Investment Evaluation Model
The chapter examines three areas of current interest:
- WACC - individual rate calculations andcombinations.
- Target and optimum capital structures.
- DCF and other models of investment evaluation.
- Lease/buy and payment scheduling models.
- Large resource industry case study.


Chapter 18  - Distribution Models
This chapter is about the distribution of wealth and building model to determine the consequences:
- Mergers and Acquisitions - the Net Gain and market anticipation.
- Liquidation models - including Soft Asset distribution models.
- Dividend Distribution models - policy and effect modelling. Lintner’s coefficients.