Excel @ Work

In this course, you will learn how to create customised macro scripts to automate repetitive steps in Excel.  In addition, you will also learn to use VBA to create interactive user forms as well as Excel functions and procedures.

1. Introduction
   1. Overview of programming techniques
   2. Understand datatypes, operators and expressions
2. Introduction to VBA
   1. Programming in Microsoft Excel
   2. Understand VBA terminology
   3. Setting up Developer tab
   4. Navigating VBA editor screen
3. Macro Recording
   1. Record and run Macro
   2. Editing a Macro in VB Editor
4. Working with Procedures
   1. Select cells and ranges
   2. Passing variables by reference and value
   3. Declaring data types
5. Using Functions in VBA
   1. Understand Functions
   2. Create user-defined functions
   3. Function data types and library
6. Program Execution Controls
   1. Use control-of-flow structures
   2. Understand Boolean expressions
   3. Using IF.. End IF
   4. Using Select Case … End Select
   5. Using Do… Loop
   6. Using For.. To.. Next
   7. Best practice guidelines
7. Working with Forms and Controls
   1. Understand UserForms
   2. UserForm Properties
   3. Controls and Properties
8. Debugging Code
9. Error Handling
10. Expense Claim Project
    1. Design
    2. Creating User Forms
    3. Coding User Forms
    4. Demo

Who Should Attend

Experienced Excel users who would like to write VBA codes in order to automate repetitive tasks that cannot be accomplished by using Macros

Duration: 09.00AM-5PM (2 days)

Full Course Fees: $450.00

This course is suitable for learners keen to create financial models using the What-if Analysis feature available in Excel.  In addition, you will also learn how to use advanced Excel functions like nested IF and index and match to look up values.

1. Basics of Modelling
   1. What is Modelling?
   2. What is a Financial Model?
   3. Principles of good spreadsheet design
2. Development of a Financial Model
   1. Specification and Planning
   2. Design Model
   3. Testing and Documentation
   4. Implementation
   5. Housekeeping
3. Advanced Excel Functions
   1. Using IF and Nested IF statements
   2. Using Lookup Functions
   3. Using Data Validation
   4. Using Name Ranges
   5. Creating Pivot Tables
4. What-if Analysis
   1. Using Data Tables for analysis
   2. Create Goal Seek analysis
   3. Using Solver
   4. Creating Scenario Manager
5. Database Functions
   1. DSUM, DAVERAGE
   2. DMAX, DCOUNT, DMIN
6. File and Worksheet Protection
   1. Protect worksheet
   2. Hide Formulas
   3. Protect Workbook
   4. Password protect workbook
7. Advanced Built-in Features
   1. Outline and Subtotal
   2. Advanced Filtering
   3. Data Validation
   4. Create Macros
   5. Using hyperlinks
8. Types of Models
   1. Financial Statement Models
   2. Sensitivity Analysis Models
   3. Investment Models
      1. Understand what is Discounted Cash Flows (DCF)
      2. Existing Financial functions in Excel
      3. Estimating of Cash Flows

Who Should Attend

Experienced Excel users interested to expand their knowledge of advanced features like what-if analysis to formulate financial models for analysis

Duration: 09.00AM-5PM (2 days)

Full Course Fees: $450.00