Advanced Excel: Spreadsheet Techniques And Financial Applications
Fully revised and updated for 2013
Bring your own laptop loaded with Excel 2007, 2010 or 2013. Excel for Mac 2011 users will also benefit
Top 5 Learning Objectives 1. Gain advanced skills to perform the most complex calculations and functions you can find in a business situation 2. Understand how to design an effective spreadsheet and increase your ability to forecast outcomes accurately 3. Adopt logical methods to solve complex problems by analysing data accurately using pivot tables 4. Learn how to use your spreadsheet skills effectively for advanced financial applications 5. Work efficiently with related worksheets and workbooks by benefitting from new reporting and presentation techniques
15 – 18 December 2013 • Dubai International Convention Centre, Dubai, UAE
Organised by:
Strategic Career Partner:
Follow us on: www.twitter.com/iirmiddleeast www.facebook.com/iirmiddleeast www.youtube.com/iirmiddleeast
Each delegate receives a complimentary copy of the book
Using Excel for Business Analysis written by your course director Danielle Stein Fairhurst, part of the Wiley Finance series.
www.iirme.com/advancedexcel srednet/moc.emrii.www
Advanced Excel: Spreadsheet Techniques 15 – 18 December 2013 And Financial Applications Dubai International Convention Centre, Dubai, UAE Course Timings: Registration and refreshments will be at 07.30 on Day One. Each day will commence at 08.00 and conclude at 14.30. There will be two refreshments breaks at approximately 10:30 and 12:30 and lunch will be served at the end of each day’s session.
Day One
Course Introduction Advanced Excel: Spreadsheet Techniques And Financial Applications course is specifically designed for professionals who need to take their Excel skills to the next level. This intensive course focusses on the technical Excel skills required in everyday Finance roles. It provides a complete, high-level education which will teach you the skills that you need to respond to the challenges presented by the manipulation of financial data. You will gain the practical skills to: •
Discover hidden short-cuts to dramatically reduce the time you spend on spreadsheets
•
Develop practical solutions to your business problems through superior spreadsheet design
•
Reduce the potential for error when creating and using your spreadsheets
•
Upgrade your spreadsheet skills with advanced data management techniques
•
Use macros more efficiently
•
Develop efficient techniques for using charts
•
Use spreadsheet skills for advanced financial applications
•
Analyse data accurately using pivot tables
Sorting Data In A Spreadsheet • Sorting data in Excel • Custom sort orders • Defining a custom list • Autofilters • Subtotalling
The course material includes extensive use of Excel and delegates will gain the maximum benefit from this course if they are already competent spreadsheet users. This is not a basic Excel course and it is designed for users who do use Excel on a regular basis, and are comfortable with using its tools and functions. During the course you will work through a series of handson practical exercises. Similarly, the financial applications require a sound knowledge of financial techniques. This course provides a comprehensive guide to the latest tools and skills for all finance professionals. At minimum, it is assumed that delegates will know how to: • Navigate confidently in Excel • Use absolute cell references (e.g. =$A$1) • Link between workbooks and worksheets • Create and use advanced Excel tools and functions on a regular basis The course will be taught using the latest Excel 2013 but the models used will also function with Excel 2010 and 2007. Excel 2003 users will have a small amount of limitation on functionality. The text book also contains instructions for Mac users. Due to the pragmatic nature of this course, you will need to have access to a computer and therefore be able to practice and discuss with examples. It is imperative that delegates bring their own laptop computers for the duration of the course. Sharing of laptops is not advised.
The in-house training division of IIR Middle East Tel: +971 4 407 2624 • Email: CTS@iirme.com www.iirme.com/cts
+971 4 335 2437
+971 4 335 2438
Must-Know Tools And Techniques • Key points on upgrading from Excel 2003 to 2007/2010 and the latest, 2013 • Tool selection; why Excel? • Absolute referencing • Named ranges • Time saving shortcuts Formatting Your Spreadsheets • Freezing row and column titles • Using auto format • Format painter • Paste special • Custom number formats • Conditional formatting • Working with dates
Course Pre-Requisite
Would you like to run this course in-house?
Excel Tools And Techniques
Formulas You Simply Can’t Live Without • LOOKUP, VLOOKUP, HLOOKUP • Logical functions eg. IF, AND, OR • Aggregation functions eg. SUMIFS, COUNTIFS • Formula Nesting Linking Data In Spreadsheets • Linking to a cell on the same sheet • Linking to cells in other worksheets and workbooks • Cell references in formulae • Linking to cells in Excel workbooks from Word • Linking charts • Create a more efficient model with links • Dealing with links and the potential errors they cause Making A Model User-Friendly • Formatting • Navigation • Hyperlinking
Day Two Advanced Excel Tools Reducing Errors • Common Excel error values • Supressing errors with IFERROR • Strategies to reduce errors in Excel spreadsheets • Building error checks • Auditing and correcting errors Bullet-Proofing Your Model • Protect data by locking cells • Restricting incorrect data entry with data validations • Applying data validations for drop-down boxes • Using worksheet protection to prevent entry • Protecting your file using “Read Only” and password protection • Hiding columns, rows and worksheets • Grouping as an alternative to hiding • Protecting worksheets and workbooks
register@iirme.com
www.iirme.com/advancedexcel
Spreadsheet Auditing • Tools for auditing your spreadsheet • Rebuilding an inherited model • Formulae and locations • Tracing precedent and dependent cells
Practical exercises: Create a macro to: 1. Format cells 2. Refresh a pivot table 3. Run a goal seek
Day Four
Charting • Creating and modifying a chart • Making your charts look fabulous • Charting tips, tricks and shortcuts • Customising charts • Editing series and markers • Formatting chart axis • Widening the gaps between columns in a chart • Creating waterfall and tornado charts • Building a combo chart with two axes
Using Excel For Financial Modelling Using Financial Functions • The time value of money • Calculation of future value and present value • Net Present Value (NPV) • Present Value (PV) • Future Value (FV) • Internal Rate of Return (IRR)
Other Advanced Tools • Goal seek • Form controls • Spin buttons • Array formulas • Introduction to the solver function
Financial Statement Modelling • Financial statements • Financial ratio analysis • Financial forecasting
Practical exercise: Build a set of financial statements in Excel, and perform scenario analysis on the inputs
Practical exercise: Create a pricing model which allows the user to change currencies and margins using drop-down boxes and spin buttons
Cost of Capital • Loan calculations; PMT, IPMT and PPMT • Cost of debt • Cost of equity • Weighted Average Cost of Capital (WACC)
Day Three Decision Tools In Excel Risk Analysis • Dealing with uncertainty and risk • Sensitivity vs. scenario analysis • Economic inputs to model, and modelling fluctuations in external factors • Use of stress testing to validate a model • What-if analysis • Importance of assumptions when assessing risk Scenario Tools Overview of technical scenario tools in Excel: • Scenario manager • Drop-down boxes - In cell drop-down boxes - Combo boxes • Data Tables
Financial Functions In Modelling • Escalation modelling methods • Break-even point and marginal costing • The discounted cash flow (DCF)
Practical exercise: Create a company valuation financial model using the DCF method
Using Data Tables • Data tables for sensitivity analysis • 1-input data tables • 2-input data tables • Applying advanced conditional formatting to scenarios with formulas
Practical exercise: Assess a property development opportunity under best, base and worst case scenarios using three different methods of scenario analysis. Summarising Data Using Pivot Tables • What pivot tables are and when to use them • Grouping data in pivot tables • Refreshing pivot tables • Formatting a pivot table • Editing grand totals and subtotals • Using calculated fields in a pivot table • Introduction to PowerPivot, a new Microsoft BI tool add-in to Excel
Practical exercise: Create a P&L report using a pivot table with calculated fields Using Excel Macros • Introduction to VBA (Visual Basic for Excel) • Why use macros and when not to use them • Recording macros • Creating a formatting shortcut key macro • Running macros • Using macro buttons
+971 4 335 2437
Practical exercise: Calculate the WACC, and use it for new capital purchasing decision-making
+971 4 335 2438
Project Evaluation And Capital Budgeting Techniques • Calculating a Payback period • Internal Rate of Return (IRR) • Net Present Value (NPV)
Practical Exercise: Calculate the NPV, IRR and payback period for a new project Cash Flow Forecasting • Capital budgeting and depreciation • Linking revenue to the cash flow • Corkscrew cash flow forecasting
Practical exercise: Create a cash flow forecast based on forecast revenue and assumed debtor days
Meet Your Expert Course Director Danielle Stein Fairhurst is an MBA qualified business professional with many years’ experience as a financial analyst. She is the Principal of Plum Solutions, a Sydneybased consultancy specialising in financial modelling and analysis and the author of “Using Excel for Business Analysis: a Guide to Financial Modelling Fundamentals”, John Wiley & Sons, July 2012. With her talent for financial modelling and professional approach, she helps her clients create meaningful financial models in the forms of business cases, pricing models and management reports. She has hands-on experience in a number of industry sectors, including telecoms, information systems, manufacturing and financial services and has taught management accounting subjects at Sydney University.
register@iirme.com
www.iirme.com/advancedexcel
Advanced Excel: Spreadsheet Techniques And Financial Applications 15 – 18 December 2013 • Dubai International Convention Centre, Dubai, UAE FIVE WAYS TO REGISTER IIR Holdings Ltd. P.O Box 9428 Dubai, UAE
+971 4 335 2437 +971 4 335 2438 register@iirme.com
www.iirme.com/advancedexcel
DISCOUNTS AVAILABLE FOR 2 OR MORE PEOPLE CALL – +971 4 335 2483 E-MAIL – a.watts@iirme.com WEB BC5037 Event
Course Fee Before 29 September 2013
Course Fee Before 20 October 2013
Final Fee
US$ 3,895
US$ 4,395
US$ 4,695
Advanced Excel: Spreadsheet Techniques And Financial Applications 15 – 18 December 2013
WOULD YOU LIKE TO RUN THIS COURSE INͳHOUSE?
Course fees include documentation, luncheon and refreshments. Delegates who attend all sessions will receive a Certificate of Attendance.
DELEGATE DETAILS
Job Title: ......................................................................................................... Email: .....................................................................................
All registrations are subject to our terms and conditions which are available at www.iirme.com/terms. Please read them as they include important information. By submitting your registration you agree to be bound by the terms and conditions in full.
Tel: ..................................................... Fax: .................................................... Mobile: ..................................................................................
Name: .............................................................................................................................................................................................................. Job Title: ......................................................................................................... Email: ..................................................................................... Tel: ..................................................... Fax: .................................................... Mobile: ..................................................................................
Name: .............................................................................................................................................................................................................. Job Title: ......................................................................................................... Email: ..................................................................................... Tel: ..................................................... Fax: .................................................... Mobile: ..................................................................................
Name: .............................................................................................................................................................................................................. Job Title: ......................................................................................................... Email: ..................................................................................... Tel: ..................................................... Fax: .................................................... Mobile: ..................................................................................
COMPANY DETAILS Company: ............................................................................................................................................................................................................ Address: ................................................................................................................................................................................................................ Postcode: ................................................................................. Country: ........................................................................................................... Tel: .............................................................................................. Fax: ................................................................................................................. No. of employees on your site: . 1000+ 500-999 250-499
50-249
0-49
Nature of your company's business: ..........................................
YES, I would like to receive information about future events & services via e-mail .................................................................
To assist us with future correspondence, please supply the following details: Name of the Department Head: ..................................................................................................................................................................... Department: ........................................................... Mobile: .......................................... Email: ....................................................................... Training Manager: ............................................................................................................................................................................................. Department: ........................................................... Mobile: .......................................... Email: ...................................................................... Booking Contact: .............................................................................................................................................................................................. Department: ........................................................... Mobile: .......................................... Email: ......................................................................
Payments A confirmation letter and invoice will be sent upon receipt of your registration. Please note that full payment must be received prior to the event. Only those delegates whose fees have been paid in full will be admitted to the event. You can pay by company cheques or bankers draft in Dirhams or US$. Please note that all US$ cheques and drafts should be drawn on a New York bank and an extra amount of US$ 6 per payment should be added to cover bank clearing charges. In any event payment must be received not later than 48 hours before the Event. Entry to the Event may be refused if payment in full is not received. Credit card payment If you would like to pay by credit card, please tick here and a member of our team will contact you to take the details
Cancellation If you are unable to attend, a substitute delegate will be welcome in your place. Registrations cancelled more than 7 days before the Event are subject to a $200 administration charge. Registration fees for registrations cancelled 7 days or less before the Event must be paid in full. Substitutions are welcome at any time.
Avoid Visa Delays - Book Now Delegates requiring visas should contact the hotel they wish to stay at directly, as soon as possible. Visas for non-GCC nationals may take several weeks to process. All registrations are subject to acceptance by IIR which will be confirmed to you in writing. Due to unforeseen circumstances, the programme may change and IIR reserves the right to alter the venue and/or speakers.
Event Venue: Dubai International Convention Centre, Dubai, UAE Tel: +971 4 332 1000 Accommodation Details We highly recommend you secure your room reservation at the earliest to avoid last minute inconvenience. You can contact the IIR Hospitality Desk for assistance on: Tel: +971 4 4072 693 Fax: +971 4 407 2517 Email: hospitality@iirme.com © Copyright I.I.R. HOLDINGS B.V.
MS/ST FN
FINANCE
LR
Name: ..............................................................................................................................................................................................................