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
