Hands-On Power Excel for Project Managers

2 Days

Description

Advanced analysis tools for maximum performance  Achieve the best results with less resources through better planning, management and decision making. Become a turbocharged project manager!

Bonus CD: Those attending will receive an instructor-developed CD loaded with analytical tools, resources and customized templates .

This class teaches you:

  • Using pivot tables
  • Automating Function Keys
  • Regression and trend analysis
  • Statistical Process Charts
  • Sophisticated data analytics
  • Scatter Diagrams
  • Using data analysis add-in
  • PERT and Monte Carlo Analysis
  • Using Ppk and Cpk
  • Creating Pareto Charts
  • Calculating process control limits

Unleash the Power of Excel  Create High Performance Tools to Ensure Your Success as a Project Manager

Excel has long been a well established tool, used by most for rudimentary functions, not much more than simple spreadsheets and basic calculations. Excel holds hidden power for high-level analysis and complex reporting  it just has to be drawn out. As a Project Manager you have the capability of using Excel to easily help you in your day to day decision making on many levels. This hands-on workshop will help you create those tools, charts, graphs and projections that will become a key factor in your continued success.

The sophisticated reporting you need made simple

In two days, Project Managers will have the ability to create the customized charts, analytic tools and projection systems you need for any short and long range management objectives. You will get right to the good stuff  covering in detail the commands and capabilities that are available to you in Excel. Youll understand how function keys and tool bars can do much more than you thought and how simple setup and construction options can help you easily create complex tables, charts and projections. Youll also see how Excel can help simplify tasks such as sorting large volumes of data with accuracy and importing outside data with ease. You will know how to create formats and data sorts that fit your needs as a Project Manager­­­ the reliable tools to make your job a bit easier.

Reliable Tools and Analysis  Excel does the work!

From constructing a Monte Carlo analysis and statistical process charts to using pivot tables, youll create and use the reporting and analysis functions you need for any type of sophisticated reporting and analysis. Whats more, youll have dozens of practical options at your disposal and know the best analytical tool to use for your environment. As a project manager, you have enough on your plate, let Excel do the work!

Create the Management Tools and Build Excel Skills!

In this class, you'll cover..

  • Regression and trend analysis
  • Pareto Chart
  • PERT and Monte Carlo Analysis
  • Computing Process Capability
  • Probability Density and Cumulative Distributions
  • Statistical Process Charts
  • Automating Macros
  • Conditional Probability

Come to this class ready to get hands-on with Excel

This course is a fast-paced, hands-on workshop. Every student will be provided with a computer running Excel. Become comfortable interpreting trends, combining data sources, spotting problems areas and more. Guided by an expert instructor, you'll leave this course with the real-world experience you need to fully harness the powerful tools Excel has to offer back in your workplace.

 

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Hands-On Power Excel for Project Managers class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date

Course Overview

1. The Basics

Review of the Excel cheat sheet to easily navigate spreadsheets and quickly find key cells.

EXERCISES:

  • Use navigation controls to quickly:
  • Select and highlight column(s) of data
  • Sum a column of numbers
  • Create cumulative sums
  • Format data cells
  • Sort and filter columns of data
  • Create data patterns
  • Name cell ranges for easy access
  • Translate text to columns
  • Perform data validation
  • Change worksheet views, headings, gridline and more

2. Create Scatter Diagrams and Develop Trend Lines

The basic causation chart: does a change in X cause a change in Y? The scatter diagram can show trends, either positive or negative.

EXERCISES:

  • Create the scatter diagram and master basic chart formatting techniques
  • Create the trend line

3. Build an SPC chart!

The Statistical Process Chart, developed by Dr. Walter Shewhart at Western Electric in the 1920s, is the foundational tool used for process analysis and process improvement activities.

It identifies:

  • Is my process in control?
  • How do I know the difference between an error and the normal variance of the process?
  • What can be corrected by a person who operates the system versus what has to be handled by management?

EXERCISES:

Build an SPC chart from scratch! Calculate upper and lower control limits and instantly produce run charts of many types including:

  • P chart
  • NP chart
  • C chart
  • U chart

EXERCISE:

Use the Cp and Cpk charts to show whether your process is accurate, precise or accurate and precise. A key tool defining the process capability of project deliverables.

4. Construct a Pareto Chart

Excel allows you to create this valuable chart out of the box! A key tool in finding root causes and implementing the best bang-for-the-buck process improvement actions. Based

on Paretos and Jurans 80/20 rule  80% of your issues can be addressed with 20% effort  the chart instantly gives the business a clear focus on the biggest issues.

EXERCISE:

Using data from a class template, construct the Pareto chart in about 10 minutes. For Excel users an import template will be available as your own customized form that can be used explicitly for creation of the Pareto chart.

5. How to Construct a Monte Carlo Analysis

The PERT estimation process is fine, but for one nagging issue  it is only a point estimate. Find out how to use the PERT as part of a Monte Carlo analysis to build a model of your probabilities. Based on the organizations risk tolerance, the Monte Carlo will give a much more accurate risk picture than the PERT alone.

EXERCISE:

Work with team members to create your project time estimates based on your own expert judgment. Then feed the data into the Monte Carlo template and give management the real estimate with built in confidence factors.

EXERCISE:

Use the included template to create the cumulative distribution (CDF). Here we will compute the potential profitability of a new line of business.

6. Use Conditional Formatting to Make your Progress Reports Pop!

Setting up a project dashboard or progress report that will automatically highlight data fields for OK (green), warning (yellow), or showstopper (red) can be set up to automatically color code itself based on a range of criteria.

EXERCISE:

Create a risk register template using conditional formatting. Set and change criteria to create easily recognizable formats for managers and executives to quickly comprehend.

7. Import External Data into your Excel Spreadsheet or Report

Got external data you need to feed into Excel? Not a problem. Set up the import function to address specific, repeatable data formats and have your reports ready to go. We will work on several different types formatting so that you will have varied approaches to dealing with complex data layouts.

EXERCISE:

Use the files on the participant CD to import into Excel and then format reports or spreadsheets as instructed.

8. Use the Pivot Table to Re-scramble your Data Instantly

Youve set up the data in your spreadsheet but you need the information grouped differently. It may take hours to redo the report or spreadsheet manually. Enter the pivot table. Resort, regroup and recombine data elements almost instantly with a few simple clicks of the button.

EXERCISE:

Using the enclosed data sheet to spin up multiple views of data for dashboards or reports.

9. Analyze & Use Multi-Page Financial Spreadsheets

Lock down cell formulas to prevent accidental user tampering. Audit and trace formulas through complex spreadsheets to troubleshoot precedents and successor processes.

EXERCISE:

Set up multi-page formulas and cascade them through multiple pages on a complex report.

10. Master the Mysteries of Conditional Probability

One of the more counter-intuitive aspect of probability is the comprehension of Bayesian logic: if the probability of one event appears to be causally related to another event, how can we tell to what extent A influences B?

EXERCISE:

We will look at two examples applying concepts of conditional probability to identify some surprising results and develop a template for measuring conditional probability.

11. Utilize the Data-Analysis Add-in

This tool is one of the least understood and most scantily explained in the help system. We will dive into the key functions and get the most use from the Statistical data add-in.

EXERCISES:

We will explore some of the key tools and perform exercises using:

One- and two-way ANOVA

Linear Programming to solve problems involving the optimization of:

  • Production mix
  • Scheduling
  • Transportation

12. Automate Processes with Macros

Use the macro function to automate repetitive tasks or set up controls on a spreadsheet that will run macros at the touch of a button.

EXERCISE:

We will complete the session by setting up a complex spreadsheet that can be automated with macros. This will bring together everything you have worked on over the two day session, wrapped up in a macro ribbon and packaged for use!

13. Course Wrap-Up

  • Review of Analysis Tools
  • External Resources Available for Project Managers
  • Question and Answer Session

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Hands-On Power Excel for Project Managers class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date

Prerequisites

Who should attend

For the Project Manager, Data Analyst or Project Professional:

This high-level hands-on program is specifically developed and delivered to help you, as a Project Professional, in your day to day tasks by using the sophisticated and advanced decision making tools that Excel has available to you. Once up to speed, youll let Excel do the work, helping you manage and deliver projects on-time and in budget. Projections, trends, problem areas become more obvious, easy to track, easy to report and easier to correct, all with Excels ability and its at your disposal.

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Hands-On Power Excel for Project Managers class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date

Course Comments

This class is offered as a private training session only.  If you would like KTCS to deliver this class for your organization please call us at 866-444-6548..

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Hands-On Power Excel for Project Managers class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date