MS Excel (Advanced) 2007/2010

Course Date: 12^th June

Member Rate: €110.00

This course is designed to follow-on from the MS Excel Intermediate course. Having already familiarised yourself with the most efficient way of performing everyday tasks, and having covered some intermediate tasks, it is now time to build on that knowledge. A large proportion of the course is dedicated to using the Database Features in MS Excel - this is a very powerful side to Excel that not many people utilise to its fullest potential. Useful functions such as IF and Lookups are covered in addition to automating tasks using Macros etc.

Objectives:

On completion of this course participants will be proficient in the more advanced functionality of MS Excel including:

• Advanced Data Analysis – Advanced Filtering, Creating Names Ranges, Sub-Totalling feature, Database Function, Pivot Tables

• Recording Macros

• IF, Lookup and other functions

• Goal Seek & Scenario Manager

• Protecting worksheets & workbooks

Course Outline:

Essentials and shortcuts

- An overview / recap of some essential Intermediate topics

- Run-through of shortcuts

Named Ranges

- Creating a named range

- Redefining a named range

- Users for named ranges

Text Manipulation

- Upper / Lower / Proper Functions

- Text to columns

- Concatenation

- Trim

- Right / Left / Mid Function

Database Features

- Sorting Records

- Using AutoFilter

- Removing duplicates

- Subtotalling lists

- Database Functions - DSUM / DAVERAGE / DMAX / DMIN / DCOUNT

Working with Pivot Tables

- Pivot Table Concepts

- Setting up the Pivot Table Layout

- Refreshing Pivot Table Data

- Using PivotTable AutoFormat

- Filtering PivotTable Data

- Creating calculated fields

- Grouping dates and values

More on Functions

- Using the COUNT Functions

- Using LOOKUP Functions (Vlookup & Hlookup)

- Using the IF Function

- Using IS Functions

- Using the SUMIF Function

- Creating Formulae with Nested Functions

- PMT Function

What IF Analysis

- Using Goal Seek

- Scenario Manager

Auditing

- Using the Auditing Toolbar

- Tracing Precedents & Dependents

Macros

- Recording & Running Macros

- Assigning a Macro to the quick access toolbar

Content, Worksheet & Workbook protection

- Data validation

- Protecting Worksheets

- Protecting Workbook Access

- Assigning Password Protection

- Removing a Password

- Preventing Changes to a Workbook

Project Management Professional (3 days) (PMBOK Version 5)

Course Dates:  18^th, 19^th & 20^th June

Member Rate: €420.00

Professional Project Management is designed to establish a detailed understanding of Project Management terms, concepts and methodologies. The hands-on approach of this course ensures trainees “learn by doing” and gain a step-by-step, real-time experience of using the tools and implementing the methodologies and support structures. In simulating real-life environments, trainees gather the detailed knowledge required to apply the training on return to their work environment.

The Project Management course is designed to be practical and help people apply the knowledge to the workplace. The course uses PMBOK Guide Version 5 as its reference and so helps prepare people for PMI certification.

The trainers are expert on multiple project management systems so if a specific company has questions on practical application of theory they are able to answer and give examples of how these systems are used in real life.

A soft copy of tools and PMBOK-compliant templates that participants can take away and use when they are back in the workplace are provided.

Learning Objectives:

• Understand the purpose, inputs and outputs of the core processes in each of the five process groups: Initiating, Planning, Execution, Control and Closing.
• Learn how to Scope a Project clearly by using a Work Breakdown Structure, and how to put in place a mechanism to control any changes to the Project Scope.
• Learn about Program Governance, and how to create a Phase Gate approval system that ensures early identification of project problems.
• Learn about the 10 knowledge areas of project management (Integration, Scope, Time, Cost, Quality, Human Resources, Communications, Risk, Procurement plus Professional and Social Responsibility). Understand how each knowledge area contributes to the progression of a project from start to finish, and know how to create the key deliverables at each stage.
• Learn key project management techniques, together with how and when to use them. Examples of such tools and techniques include Budget, Risk Analysis, Scheduling, Gantt chart, Contingency and Change Control.
• Learn how to identify and analyse risks to your project, and place a focus on avoiding risks rather than recovering from their effect. Learn how to quickly build a Risk Register for your organisation and how to weigh up the best response to different types of risk.
• Learn how to apply this learning a range of industries, with particular emphasis on projects that are relevant to participants’ environment through use of case studies and working exercises based on realistic problems and real-life project scenarios.
• Understand Project Management Technical Skills and Tools such as Microsoft Project Professional, methodologies such as Work Breakdown Structures, Phase Gates, Monte Carlo Simulation Analysis and a robust Project Governance system.
• Learn different techniques for estimating schedule and effort, understanding the different factors that have to be taken into account when moving from an initial estimate through to a detailed plan upon which you can make a commitment.

This module uses PMBoK Version 5.0 as its core reference.