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/highlight column(s) of data
- Quickly 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 the scatter diagram 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.
EXERCISE:
- 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 1920's, 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 2003 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 Pareto's and Juran's 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 2007 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 organization's 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 progress report 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
You've 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. Create 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 2 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
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 keys tools and perform exercises using:
- Single factor ANOVA
- Chi-squared
- F-test
- Correlation
- Covariance
- Regression
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 put together everything worked 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
|