finacial modelling using spreadsheet

introduction to modelling tools:
spreadsheet features and functions.
using excel fuctions such as Array functions, refernce functions, financial fuctions, data validation, analysis toolpak
Business forecasting: Time series analysis
using moving average,regression,anova
building a valuation model (includes constructing financial statemetns)
handling business uncertainity
risk analysis using montecarlo simulation
scenario building
checking model for acuracy and assuring data protection
introduction to visual basic

Financial Modeling Using Excel and VBA
Chandan Sengupta

Softcover, 672 pages + bonus CD-ROM
ISBN 0471267686
978-0471267683
Wiley
February 2004
(click below for the best currently available price for this important resource)



Financial modeling is finance in action. It is both challenging and rewarding. Financial Modeling Using Excel and VBA shows you how you can both enjoy the challenge and reap the rewards.

Financial modeling is an essential skill for all financial professionals - and Excel and its built-in programming language, Visual Basic for Applications (VBA), are the preferred tools for the job. Financial Modeling Using Excel and VBA, designed for self-study, classroom use, and reference, presents a comprehensive approach to developing both simple and sophisticated financial models in all major areas of finance using both Excel and VBA.

The book assumes only basic knowledge of finance and Excel, and no previous knowledge of VBA. From that base, it teaches financial modeling, VBA, and advanced features of Excel using a unique, simple approach that is based on the author’s twenty years of experience in financial modeling in the business world and the classroom. The book reviews all the necessary financial theory and concepts, and walks you through a wide range of real-world financial models–over seventy-five of them–that you can imitate and use for practice as well.

The included companion CD-ROM includes several useful modeling tools and full working versions of all the models discussed in the book, so you can be more productive. You may view an excerpt of this book. This book and CD-ROM set provide the following instructions, models and resources:

Introduction to Financial Modeling.
Excel for Financial Modeling
Excel Basics.
Advanced Excel Features.
Excel’s Built-In Functions and Analysis Tools.
Financial Modeling Using Excel
How to Build Good Excel Models.
Financial Statements Forecasting.
Time Value of Money.
Financial Planning and Investments.
Analyzing Market History.
Bond Pricing and Duration.
Simulating Stock Prices.
Options and Option Portfolios.
Binomial Option Pricing.
VBA for Financial Modeling
Introduction to VBA.
VBA Essentials.
Sub and Function Procedures.
Debugging VBA Models.
Financial Modeling Using VBA
How to Build Good VBA Models.
Time Value of Money.
Financial Planning and Investments.
Analyzing Market History.
Simulating Stock Prices.
Options and Option Portfolios.
Binomial Option Pricing.
Appendix A: Keyboard Shortcuts for Excel.
Appendix B: VBA Quick Reference.
Appendix C: Excel and VBA Built-In Functions.
"Excel and VBA have vast capabilities, yet until this book, users were on their own to figure out just how to use these tools for both single and complex financial problems. Financial Modeling fills the gaps with practical guidance to modeling a wide range of finance problems." –Tim Koller, Partner, McKinsey & Company, Inc.

CHANDAN SENGUPTA teaches at the Fordham University Graduate School of Business Administration, specializing in Financial Modeling, Business Valuation, Corporate Finance, and Investment Management. Formerly, he was vice president at the Chase Manhattan Bank for eight years and senior financial advisor for Mobil Corporation for ten years. He is the author of The Only Proven Road to Investment Success, also published by Wiley.

(information from the publisher)

Day One
Learn how to efficiently use VBA to build, debug and analyse financial models

Overview of core VBA skills

* The Visual Basic Environment
* Common elements of the VBA syntax
* Generating your own code versus recording a macro
* Programming standards
* Application: Navigating a model using VBA
* User Defined Functions ('UDF')

How to test and debug VBA applications

* Break Points
* Immediate Window
* Watch Window
* Stack
* Application : Debug a client s Project Finance VBA code

Coding VBA Building blocks

* Difference between sub/ function/ macro
* Interacting with an Excel spreadsheet
* Different types of Looping Operations
* Explanation of Option Explicit
* Application: Solving a circular reference using a cut & paste macro.

Day Two
Applications in financial models

Application session

* Automated Sensitivity and Scenario analysis using VBA
* Sculpting a Debt Repayment to a Target Ratio
* Debt sizing / Debt sculpting optimisation
* Automated execution of Macros
* Presentation applications
* Disclaimer Coding
* Security discussion on passwords and protected/ hidden sheets
* Tariff break-even to a target IRR

Advanced Applications

* Automation controlling ALL of Windows other components
* Explode your Graphs exceptional presentation functionality
* Error Handling what is it and how should you be using it?
*********************************************************************
Course Content

The programme consists of the following 2 modules:

Part I: Excel VBA Fundamentals

Participants will learn the fundamentals of Excel VBA and prepare themselves to create simple financial models in Part II. Small programs will be used to illustrate the underlying programming concepts.

The VBA programming environment
Variables and constants
Macros
Built-in VBA functions and operators
User-defined functions
Branching and looping
Object-based concept: Objects, methods and properties
The Range object
Debugging
The following additional topics may also be covered if time allows:
Arrays
Procedures
Custom dialog boxes
Workbook and worksheet events
Chart objects
Part II: Financial Modeling

Participants will apply VBA programming concepts and skills to build simple yet real-world financial models. The techniques of writing good models will also be discussed. As the scope of financial modeling is huge, topics have to be selective. The following shows a list of possible topics. Actual coverage depends somewhat on the finance background of the class.

Loan amortization
a. Simple model
b. Iteration: The bisection approach
c. Allowance for changing interest rates or repayment period
Financial planning
a. Retirement planning
b. Portfolio restructuring
Market history analysis
a. Normal and real growth of stocks
b. Growth of investment in different assets
Stock simulation
a. Stock volatility estimation
b. The geometric Brownian motion and the log-normal distribution
c. Stock price simulation
Option pricing
a. Different option strategies
b. The Black-Scholes option pricing model
c. Implied volatility estimation
d. Options portfolio
e. The binomial model