Date: Sunday, 19 February, 2012 - 09:00 - Thursday, 23 February, 2012 - 17:00
Location: Dubai
Duration: 5 Days
This five-day intensive public course is being hosted by a third party training provider. Contact us for more information on attending this event in Dubai. To run a course like this at your organisation, contact us for quote.
Specifically designed for business professionals who need to take their Excel skills to the next level, this intensive full day course focusses on the technical Excel skills that will be required for the Financial Modelling course. This very practical course covers everything you need to perform your job and nothing you don’t!
For those who don’t have time to sit through extensive Excel “how to” training, this course will arm you with a range of tools, techniques and formulas essential for Finance. Guaranteed to contain “no fluff”, this optional day will equip you with the skills to produce more efficient and accurate Excel models and increase productivity.
· Key points on upgrading from Excel 2003 to 2007 and latest, 2010
· Absolute referencing
· Named Ranges
· Time saving shortcuts
· Formulas you simply can’t live without
· LOOKUP, VLOOKUP, HLOOKUP
· Logical functions eg. IF, AND
· Aggregation functions eg. SUMIF, COUNTIF
· Financial Functions eg. NPV, IRR, PMT
· Formula Nesting
· Protect data by locking cells
· Password Protection
· Restricting incorrect data entry with data validations
· Using worksheet protection to prevent entry
· Protecting your file using “Read Only” and password protection
· Creating and modifying a chart
· Making your charts look fabulous
· Charting tips, tricks and shortcuts
· Autofilters
· Goal Seek
· Macros
· Pivot Tables
· Array formulas
· Scenario Manager
· Drop-down boxes
o In cell drop-down boxes
o Combo boxes
· Data Tables
· Spin buttons
· Hiding
· Working with dates
· Hyperlinking
· Conditional formatting
1. What is Financial Modelling?
Typical examples and purposes of financial models
2. Excel or Access?
Tool selection and advantages and disadvantages of Excel and Access
3. Model Design
Model planning and steps in building your model
4. Skills needed for Financial Modelling
The technical, design, business and industry knowledge required for financial modelling
5. Model Tools & Functions
Which formula or tool is most appropriate in which modelling situation?
6. Issues and logic surrounding the calculation of customer acquisition
a) Creating the pool of potential customers
b) Narrowing down the pool
c) Expected customer acquisition
d) Lost customers / customer defection
e) Market penetration
Practical exercises: Modelling exercises using a range of financial functions and tools. Building a business case utilising financial modelling techniques
1. Modelling Techniques
a) What makes a good model?
Attributes of a good model such as user-friendly and structural features
b) Avoiding Excel errors
A wrong calculation in a model can have disastrous consequences! Strategies to reduce errors in financial modelling
c) Rebuilding an inherited model
d) Building error checks
e) Correcting errors
f) Create a more efficient model with links
g) Dealing with links and the potential errors they cause
2. Bullet-proofing your model
Retain model integrity and prevent misuse of your model by learning to restrict incorrect inputs
a) Protection
b) Locking cells
c) Restriction incorrect data entry with data validations
3. Making a model user-friendly
a) Formatting
b) Navigation
4. Dealing with uncertainty and risk
a) Economic inputs to model, and modelling fluctuations in external factors
b) Use of stress testing to validate a model
c) What-if analysis
d) Importance of assumptions when assessing risk
5. Create a best, base and worst case scenario on your model. Technical tools for creating scenarios:
a) Manual sensitivity analysis; drop-down scenarios
b) Scenario manager
c) Data tables
6. Displaying final model results and findings
Communicate the results of your model clearly and concisely whilst getting the key message across to the audience
a) Documentation and source referencing
b) Writing assumptions
c) Writing operation instructions
Practical exercise: Building scenarios and perform sensitivity analysis on our business case model
Practical application of modelling in Excel for the purpose of budgeting for organisations
1. Advanced forecasting functions; Time series, exponential smoothing and regression analysis.
2. Modelling for “Stepped” costs when forecasts include both fixed and variable costs.
3. Sales forecasting
Predication of likely sales revenue based on drivers
4. Seasonality
Simply and easily spread revenue or expenses throughout the budget period based on season variations
5. Building an expense budget
Based on given assumptions, we will build a budget for 12 months
6. Contingency Planning
Calculating a contingency based on assumptions about probability and impact of unexpected events
7. Budgeting for capital expenditure
Capital expenditure is budgeted for cashflow and then depreciated
8. Cash flow budgeting
Translating our profit and loss statement into a cashflow forecast
9. Reporting against budget; variance analysis
Creating a template into which actual and budget can be compared and analysed
Practical Exercise: Create a model in Excel which will calculate volume-dependent “stepped” costs.
1. Charting and graphing fundamentals – how to build charts
2. When to use a table or charts; combining tables and charts using chart data table and data bars
3. Choosing the correct chart to display your findings
4. Visual Design - dos, don’ts and common mistakes in graphical presentation of data and reports
5. How to present easy to follow information concisely and clearly
6. Pivot Tables and their role in financial modelling
Practical exercise: Build your own flexible report including a dynamic chart
Demonstration and overview of the following:
1. Tiering tables
One of the more complex and widely used calculations in financial modelling. Discussion of two different methods of calculation; a simple and progressive table
2. Pricing models
Calculating profitability and break even points at various prices
3. Dynamic charting
Create a user-friendly report with a dynamic chart based on drop-down selections
4. Modelling for risk assessment
Assess project risk with a risk assessment model using conditional formatting
Practical Exercise: Forecast a sales cycle based on historical data by calculating the seasonality index and adjusting the forecast accordingly.