Loading Events

« All Events

  • This event has passed.

MALAYSIA – Microsoft Excel for Project Managers (18 & 19 Nov)

November 18, 2020 @ 9:00 AM - 6:00 PM SMT

REGISTER NOW – MALAYSIA

18 & 19 November 2020 (2 Days) | 16 PDUs

 9.00am to 6.00pm 

Code MEPM


ABOUT THE PROGRAM

Most Project Managers spend a lot of time in interpreting project data, project tracking, reporting and manpower planning. This Course will help project managers and business analysts save a substantial amount of time in their project management activities, learn better solutions to their existing problems and help them plan more effectively.

Top Reasons why you should join the “MS® Excel for Project Manager” Course:

  • Required for Planning, Estimation, Budgeting, Tracking and Reporting
  • Learn systematically to forecast time and cost to complete a project
  • Learn how to create effective Dashboards
  • Learn how to implement scenario analysis
  • Create models based on Earned Value Management
  • Learn Advanced concepts like Monte Carlo Simulation may not get second chance to make first impression, thus good technical writing / communication matters!

REQUIREMENT

You are required to have some fundamental knowledge in using MS® Excel before attending this course.

 

LEARNING OBJECTIVES

At the end of this action-based workshop, participants will be able to:

  • Understand the basic accounting reports
  • Apply MS® Excel functions and formulas for project planning and estimation
  • Learn the various tools available in MS® Excel for analysis
  • Apply the various methods to track and report project status using MS® Excel functions and formulas
  • Apply Earn Value Management (EVM) using MS® Excel functions
  • Calculate cost and time forecasting using MS® Excel formula.
  • Create and apply Macros to project reports

PROGRAM OUTLINE

  • Introduction
    • What is MS® Excel for Project Manager is all about?
  • Understanding basic accounting reports
    • Understanding FSS (Financial Statements)
    • Understanding PL (Profit Loss)
    • Income-Statement-Rev-Recognition
    • Understanding BS (Balance Sheet)
    • Accenture BS
    • Cash-Flow Statement
    • Profitability
    • Optional-Depreciation
  • Planning and Estimation
    • Planning – Context
    • Excel – Shortcuts
    • Excel – Referencing
      • Absolute Cell References
      • Partially Fixed References
      • Referring to Other Worksheets
      • Referring to Other Workbooks
      • Creating and Using a Named Range
      • Creating Slightly Smarter Named Ranges
      • Naming Formulas and Constants
      • Managing Named Ranges
      • Automatically Creating Named Ranges
      • Applying Names to Existing Formulas
    • Excel – Custom Formatting
    • Business Model
    • Steps – Creating Bottom Up Model
    • Understanding Business Situations
    • Creating Layouts
    • Building Assumptions
  • Bottoms Up! Planning
    • Revenue Build up
    • Manpower – Cost
    • Travel – Cost
    • Cost – Build up
    • Recoverable –Costs
    • Projecting – PL (Profit Loss)
    • Projecting – CFS (Cash Flow Statement)
    • Validating Data and Creating Templates
      • Applying Data Validation Rules
      • Creating Templates
      • Modifying templates
  • Important Tools for Analysis
    • Understanding Break Even Analysis
    • Case – Breakeven
    • BreakEven
    • BreakEven – Dashboard
    • BreakEven – Synergy
    • Time – Value – Money
  • Tracking and Reporting Project Status
    • Flexible – Model Reporting I
    • Flexible – Model Reporting II
    • Excel – Data Tables
    • Flexible – Model Reporting III
    • Project – Data Analysis Filters
    • Project – Data Analysis SUMIF
    • Other Statistical Functions
      • Sum, Sumif, Sumifs
      • Average, Averageif, Averageifs
      • Count, Counta , Countif, Countifs, Countblank
      • Max , Min , Large , Small , Rank , Rank.Eq
      • Sumproduct, Product
    • Database Functions
      • Dsum
      • Daverage
      • Dcount
      • Dcounta
      • Dmax
      • Dmin
      • Dproduct
      • Dstdev
    • Logical Functions
      • If
      • If(And(
      • If(Or(
      • Nested If
    • Project – Data Analysis Pivot Tables
      • Designing Pivot Tables
      • Collaborating Formatted tables and Pivot Tables
      • Advanced Pivot Chart Features
      • Sparklines, Slicers and Timeline
      • Introduction to Dashboard
    • Lookup Functions – Consolidated Reporting using VLOOKUP
    • Lookup Functions – Consolidated Reporting using INDEX & MATCH I
    • Lookup Functions – Consolidated Reporting using INDEX & MATCH II
    • The Basic Lookup
      • VLOOKUP(): Vertical Lookups
      • HLOOKUP(): Horizontal Lookups
    • Advanced Lookups
      • MATCH(): Finding the Position of Items in a Range
      • INDEX(): Retrieving the Value from a Cell
      • Performing a “Left Lookup”
      • Performing a Double Lookup
    • Information Functions
      • The “IS” Functions: Checking the Value Inside a Cell
      • Tutorial: Generating Invoices from a Product Catalog
  • Earned Value Management (EVM)
    • Understanding (EVM)
    • EVM – Case Study
    • EVM – Building Assumptions
    • EVM – Modelling
  • Cost and Time Forecasting
    • Estimated at Completion
    • Cumulative EVM
    • EV Equal PV
    • Uniform Work Distribution
    • EVM status Dashboard
    • Reporting Bottom UP Planning Model
    • Reporting Status Gantt Chart
    • Reporting Scenario Analysis
    • Date and Time Functions
      • TODAY() and NOW(): Inserting the Current Date and Time
      • DAY(), MONTH(), and YEAR(): More Date Calculations
      • WEEKDAY(): Determining the Day of the Week
      • DATEDIF(): Calculating the Difference Between Dates
      • DAYS360(): Finding Out the Number of Days Between Two Dates
      • YEARFRAC(): Calculating the Percentage of a Year Between Two Dates
      • WEEKNUM(): Figuring Out in Which Week a Date Falls
  • Introduction to Macro and Visual Basic Application
    • Macros – Introduction
    • Macros – Recording and Editing
      • Setting up a Macro Security Level
      • Creating a Simple Macro
      • Editing a Macro
      • Creating an Action Button for Macro
      • Deleting Macro
  • Introduction to VBA

REGISTER NOW – MALAYSIA

 

Details

Date:
November 18, 2020
Time:
9:00 AM - 6:00 PM SMT
Event Category:
Event Tags:
, , , ,

Venue

ePm Training Services Sdn Bhd
A-20-3, Level 20, Tower A (Lower Zone) Menara UOA Bangsar No.5 Jalan Bangsar Utama 1
Kuala Lumpur,59000 Malaysia
+ Google Map

Organizer

EPM Training Services Sdn Bhd
Phone
+603 2201 9767