Financial Modelling using the advanced features

of Microsoft Excel

Versions 2000, XP, 2003

Course Outline

Intro & history, bibliographical notes.

Scope of Excel.

Setting up desired options and add-ins.

Customise Toolbar.

Multi-user modelling concepts, planning for data integrity

Start with the end – EXLPlan – a UK cash flow model

Scrapbook - Revision:

Absolute & Relative cell addressing.

Exercise – Jimmy’s Sportswear.

A quick look at dates.

Conditional formatting, cell validation techniques.

Exercise: Named Ranges – Natural Language formulas.

Macros - Recorded

The Costing model analysed.

First Macros – automate procedures, reduce repetitive tasks, and place information where you want it.

The template macro – add to menu.

ActiveX controls – how they can help you and your users.

Model: BOM/Code generator - Blow a Gasket!

Condomise your models – prevent a tragedy

Exercise:

From one If to many Ifs, Ands and Ors. Why VBA might be better and easier.

NestedIf, Error cleanup, formats, auditing.

Basic Financial exercises:

Goal Seek – single variable calculation.

Multi-variable Scenarios.

Massage dates.

Arrays – save work and memory.

More on Nested Ifs.

One dimensional tables.

Two dimensional tables.

Simple margin analysis.

Use a basic Macro function to calculate the cost of trees.

Win the Lotto – random numbers.

The SUMIF formula.

Functions – common, financial, statistical – set up a histogram.

Data Filtering Techniques.

Outlining Data.

Subtotalling large spreadsheets.

The Lookup Functions: V/Hlookup, Choose, Mask.

- Using Forms to input data

- manipulating text

The built-in forms feature for rapid spreadsheet editing.

Use a workbook as a web page.

Macros – written

Communication to and from users – Input and Message forms.

Model: Provide workbook navigation techniques using hyperlinks and dialog forms – Visual Basic for Applications

Use Excel’s Consolidate technique to total regional P & L accounts.

Compare to 3D formulas.

Financial reporting – link to Word.

Advanced graphing techniques.

Using Excel’s Report Manager. Microsoft dropped this feature in XP.

Share workbooks – multi-user considerations.

Working with enterprise data.

- Importing text data

- importing ‘foreign’ information

MS Query – massage data from a mainframe/server.

Summarising data with PivotTables and Charts.

Run Solver to optimise an Ad. Campaign.

Styles – setting up preferences

Model: MRP - WIP

During the course models from governments and companies will be reviewed.

Current state of the art examples of Excel applied in practical financial applications.

Duration: 2 days

Manual: On CD