Print
Print
|
Introduction
This hands-on, application driven 4-day programme leads delegates through selected advanced Excel functions and procedures pertinent to the construction of financial models, explaining the function or procedure and their uses. It then moves on to cover good model structure, both for single and multi-file models, and ways of maintaining the integrity of the model using the features provided by Excel. Delegates are introduced to a number of functions and techniques essential to the preparation of sophisticated financial models, such as logical tests, lookup tables, discounted cash flow over equal and unequal periods, internal rates of return, free cash flow, loan repayments, and “switches”. The program covers what should be done once the model is complete, e.g. sensitivity analysis, data tables and the creation and comparison of scenarios. Finally it touches on optimisation models utilising “solver” to solve complex problems. Teaching method Delegates are guided through the course by a highly-experienced course director. Once a certain principle, technique, or function has been worked through in class, the delegates tackle a practical exercise pertinent thereto. The course is non-threatening and relaxed, the course director always being on hand to help and guide where necessary. Delegates are provided with a comprehensive set of notes designed to be used as a reference manual after the course, as well as a CD containing all the worked and unworked examples referred to in the notes, and all the exercises and solutions for the work done in class 
Who Should Attend
This course has been designed for:
• Budget & planning officers
• Financial controllers and managers
• Financial & business analysts
• Accountants
• Corporate treasury managers
• General managers
NOTE: Delegates need to have a working knowledge of Excel, and are encouraged to bring their own laptops for the computer based examples and exercises. It is essential that the add-ins "Analysis Toolpak" and "Solver Add-in" have been loaded.
Pre Requisite(S)
Delegates should be familiar with Microsoft Excel (though not necessarily expert in the use thereof, as any relevant Excel function pertinent to an exercise will be discussed prior to tackling the exercise). Excel knowledge required:
Basic & Intermediate Excel skills, including:
a) Moving around the spreadsheet & workbook
b) Inserting & deleting rows and columns
c) Inserting text & formulas
d) Copying and constructing formulas
e) Formatting cells
f) Basic Excel functions – calling and using Excels functions
g) Making cell addresses absolute
Outcomes
On completion of this practical, hands-on training course, delegates will:
• Be able to build well-structured, flexible financial models
• Be able to apply many of the advanced functions and techniques available in Excel
• Understand, create, and use scenarios, data tables, sensitivity tables, goal-seeking, etc.
• Be able to apply what they’ve learnt in the calculation of Wacc, NPV,
IRR, loans, free cash flow and break-even points
• Be able to apply Excel’s Solver in the preparation of optimisation models
• Have at their fingertips a comprehensive reference manual and worked
examples relating to the building of financial models
Course Content
Module 1 Introduction Some useful tips Protecting data in spreadsheets Hiding formulas Naming cells, ranges & formulas Naming formulas Creating your own formats Creating conditional formats Creating conditional formats in Excel 2007 o Exercises - Conditional formatting Validating data & producing drop-down lists Multiple Sheet Models Drilling down Hyperlinks Logical tests - IF o Exercise - Logical tests (1) Calculation of commissions o Exercise - Logical tests (2) - Nested Ifs Sumif and Countif Excel's database Searching Producing pivot table reports Importing data o Exercise - Interpreting volumes of data Pivot Tables - Consolidating data from multiple ranges Match Vlookup and Hlookup o Exercise - Vlookup and Match o Exercise - Using Hlookup Index & Lookup INDEX LOOKUP Row & Column functions The Rows & Columns functions Using "masks" The Sumproduct function o Exercise - Index, Row, Columns, Masks and Sumproduct Max & Min o Exercise - Index, Match & Max String functions Date functions Module 2 Types of financial models Example of a deterministic model Model Structure The integrity of the model Extracting positive and negative balances (to arrive at a Cash balance or an Overdraft balance) Using the "Offset" function o Exercise-Completing a monthly budget model Effective and Nominal rates Average growth or inflation rate ISNUMBER, ISBLANK, and COUNTA Multi-file models A Sales model with links and overridden data o Exercise - Linking Files and using override Building a 20-year project model
Financial Ratios Valuing Companies Optimisation Models |