This course is not currently offered by Global Knowledge. Information here is provided for reference only.

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. 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'll learn 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. You'll 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 learn 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.

What You'll Learn

• Discover trends in data and reports for risk analysis and forecasting by creating a regression/trend analysis
• Identify key process/product improvement opportunities as well as perform root cause analysis
• Determine and manage risks in project schedules and define confidence factors
• Know the specific process capability of project processes utilizing Ppk and Cpk metrics: A key tool used in measuring process effectiveness for Six Sigma projects
• Model key business processes and be able to perform sophisticated optimization analyses
• Calculate process control limits and superimpose customer specification limits and determine if process is meeting customers needs
• Identify potential issues or showstoppers in data and be able to identify potential risk triggers
• Know the ins and outs of pivot table creation and how to validate, consolidate, convert, sort and filter your data
• Automate your repeatable processes using macros to create reports and data analysis charts instantly
• Trace Precedents and Dependents to analyze cascading formulas and troubleshoot spreadsheet issues
• Quickly analyze data and create reports using the key financial, math, and statistical functions of the Function Library
• Easily format external data using "Get External DataFunctions and automate this function for repeatable processes
• Create sophisticated data analytics through the use of one- and two-way ANOVA
• Secure workbooks and cells and lock down critical formulas to prevent altering of key computation or data fields
• Determine needs, causes, effects, and anticipated results of a project and implement the most cost-effective process improvement actions using analysis charts
• Learn to quickly and accurately audit formulas through your complex spreadsheets and troubleshoot precedents and successor processes
• Build a customized analysis model based on your firms probabilities and risk tolerance and gain an accurate tool to determine your companies risks
• Modify report formats to meet the needs of specific management and department heads using the multitude of data formatting options in Excel

Who Needs to Attend

• Project Manager
• Data Analyst
• Project Professional

Prerequisites

There are no prerequisites for this course.

Follow-On Courses

• Mastering Microsoft Project

Course Outline

1. The Basics

2. Create Scatter Diagrams and Develop Trend Lines

3. Build an SPC chart!

4. Construct a Pareto Chart

5. How to Construct a Monte Carlo Analysis

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

7. Import External Data into your Excel Spreadsheet or Report

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

9. Analyze & Use Multi-Page Financial Spreadsheets

10. Master the Mysteries of Conditional Probability

11. Utilize the Data-Analysis Add-In

12. Automate Processes with Macros

13. Course Wrap-Up

Exercises:

Exercise 1:

• 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

Exercise 2:

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

Exercise 3: 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 4: 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.

Exercise 5: 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.

Exercise 6: 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 7: Use the included template to create the cumulative distribution (CDF). Here we will compute the potential profitability of a new line of business.

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

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

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

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

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

Exercise 13: 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

Exercise 14: 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!