Course Introduction

This certificate offers in-depth training in analytical methods used by financial analysts and managers in a variety of settings, including finance departments within corporations; investment firms and other financial services institutions; and consulting and professional services firms. Upon completion of this flexible program, students gain expertise in skills such as:

• Profitability analysis, including calculation of price-earnings ratios.
• Liquidity ratios, asset/productivity analysis, and capital structure/debt ratios.
• Discounted cash flow (DCF) and other valuation techniques.
• Using Excel spreadsheet modeling to forecast sales, earnings, and free cash flow.
• Using Visual Basic for Application (VBA), R and Python programming to solve finance problems, including portfolio modeling.
• Pricing options using the Black-Scholes formula.
• Random number generation, historical and Monte Carlo simulations.

The first part of PCFA(Excel) is a fast-paced comprehensive set of tools to tap into the full power of Excel. The second part of the course builds 'smart' analytical models, useful applications for more advanced techniques, which are transferable throughout an organization. The third part deals with VBA programming to build complex financial models.

Seminar Outline

MODULE 1 (2 days)
Key Techniques for Improved Model Building:

• Structure and model layout
• Design rules for good layout
• Pointing techniques (including cursor movement) to avoid errors
• Keyboard techniques: non-rodent access to menus, cells etc.
• Relative, mixed, absolute and 3D addressing
• Naming ranges, using row and column labels
• Intersection operator
• Formula Palette, finding and using built-in functions
• Working with lists and filling ranges
• Lookup tables using LOOKUP, VLOOKUP, INDEX, MATCH, etc
• Using client add-ins such as Solver, Analysis Toolpak, Advanced Excel ToolPak, etc.
• House keeping and security
• Spreadsheet security and distribution of models
• Developing a planning model including variables and assumptions table

Advanced Techniques:

• Advanced cell formatting techniques
• Custom formatting
• Conditional formatting
• Data validation
• Array functions
• Conditional Functions
• Charting tools and techniques
• Importing and opening non-spreadsheet files
• Parsing text ranges
• Transposing ranges
• Workbook templates
• Techniques for building consolidated reports by function
• Techniques for building consolidated reports using Excel tools
• Spreadsheet settings and preferences
• Creating & using user forms

Sensitivities:

• Sensitivity analysis using Data Tables, Tornado Diagram
• Sensitivity analysis using Scenarios
• Scenario summary report
• Pivot table reports, Pivot table settings

Workbook Linking:

• Building links
• Building safe links using range names and intersection operator
• Understanding link resolution
• Locating and removing invalid links

MODULE 2 (2 days)
Financial Analysis:

• Investment decision model
• Advanced investment analysis including sensitivity to leverage
• Financial functions such as NPV, NFV, DPB, IRR, RATE, PMT, PPMT, IPMT etc
• Forecasting balance sheet and cash requirements
• Sales variance analysis

Statistics:

• Regression analysis with Excel & R
• Forecasting functions such as TREND, FORECAST, GROWTH,etc
• Statistical functions such as SLOPE, INTERCEPT, RSQ, etc
• Charting and forecasting based on regression analysis
• Frequency distribution tables

Date and Time Functionality:

• Date and time based formulas and formatting issues
• Excel date functionality
• Elapsed and projected date and time formulas
• Periodic Cashflows

Database functions and uses:

• Worksheet databases- functions, advantages and limitations
• Finding exact matches and unique entries
• Database functions such as DSUM, DCOUNT, DAVERAGE, SUMIF

Applications:

• Financial statement analysis
• Investment modeling
• Sensitivity modeling
• Pricing options using the Black-Scholes formula
• Monte Carlo simulations with Financial Analysis ToolPak

Introductory macro techniques using VBA:

• Introduction to macros
• Macro recording
• Recording generalized macros
• Recording macros with absolute references
• Recording macros with relative references
• Understanding and editing recorded macros
• Where to store macros
• Activating macros

More advanced macro techniques:

• Custom built menus and toolbars
• Adding commands to Excel's menus
• Assigning macros to worksheet controls
• Personal workbook and stored macros
• Random number simulations with macro

MODULE 3 (3 days)
Programming in VBA:

• Introduction to VBA & Python programming language
• Elements of VBA language and syntax
• Introduction to the Excel Object Model
• Introduction to the Office Object Model
• Object browser and help
• Programming principles
• Designing a good program

Layout and Programming Style:

• Introduction to the VBA IDE
• IDE options
• Module and procedure naming
• Selection of variable types
• Variable naming conventions
• Referencing conventions and use of With blocks to improve readability and performance
• Code layout and indentation to create readable code blocks
• Use of subroutine calls
• Common syntax errors and their rectification

Building an automated loan calculation with amortization report:

• The loan calculation model
• Introduction to the loan calculation model
• Auto-execute and event handling macros
• Formulas and methods of referencing range
• Controlling and assisting user input under VBA
• Custom dialog boxes
• Loan calculation model reporting
• Extracting data from the loan model

Working with user interfaces

• Prompting the user for data input
• Understanding control types, events and tab order
• Dynamic dialog boxes
• Using return values to check for button press
• Understanding the values returned by control types formatted help screens
• Print macros
• Page set-up
• Reporting using file linking
• Reporting using copied data

Other techniques using VBA:

• Entering data and formulas to the worksheet under VBA
• Range validation
• Working with worksheet contents
• Selecting ranges and formatting cells using VBA
• Assigning values to cells and ranges using VBA
• Accessing values of cells and ranges using VBA
• Concatenating strings
• Conditional processing in VBA using IF and SELECT CASE
• Looping using FOR..NEXT loops, FOR EACH..NEXT loops
• Complex conditional expressions
• Using external functions through add-ins like Solver, Financial Analysis ToolPak, etc
• Embedded macro buttons
• Testing and debugging VBA code
• Program testing and debugging
• Debugging with step mode
• Using break points
• Using watch window to view variables
• Using the Immediate Pane to evaluate expressions
• Interpreting run-time errors
• Error handling
• Trapping and responding to run-time errors
• Planning for recovery

Who Will Benefit?

Useful for business analysts, financial modelers, accountants, bankers, finance managers, financial analysts, financial controllers, financial advisers and others who wish to add spreadsheet design, spreadsheet security and advanced modeling to their skill sets.

It appeals especially to those who need to build skills and confidence in complex spreadsheet systems in the area of finance and business, accounting, banking, and insurance.

The course is designed for professionals in the accounting and finance environment who need to enhance their professional skills. This course also benefits the more advanced user who needs to build automated VBA or other programming language-based systems to support corporate strategic information requirements.

Entry Requirements

Preferably a University degree or experience in a finance area and at least 6 months relevant spreadsheet experience.

Award of Certificate

Completion of all three modules, a individual & group project, demonstrating the knowledge in the course will qualify for a Professional Certificate in Financial Analysis Using EXCEL.