This course can be tailored to your needs for private, onsite delivery at your location.
ASPE is an IIBA Endorsed Education Provider of business analysis training. Select Project Delivery courses offer IIBA continuing development units (CDU) in accordance with IIBA standards.
Select courses offer Leadership (PDU-L), Strategic (PDU-S) and Technical PMI professional development units that vary according to certification. Technical PDUs are available in the following types: ACP, PBA, PfMP, PMP/PgMP, RMP, and SP.
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. You'll 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. 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 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.
Create the Management Tools and Build Excel Skills!
In this project management training course, you'll cover: Regression and trend analysis, Pareto Charts, PERT and Monte Carlo Analysis, Computing Process Capability, Probability Density and Cumulative Distributions, Statistical Process Charts, Automating Macros, and Conditional Probability.
This project management training course is a fast-paced, hands-on workshop. Every student will be provided with a computer running Excel. Guided by an expert instructor, you'll leave this project management training course with the real-world experience you need to fully harness the powerful tools Excel has to offer back in your workplace.
- Upcoming Dates and Locations
All Live Online times are listed in Eastern Time Guaranteed To Run
- Course Outline
1. The Basics
Review of the Excel 'cheat sheet' to easily navigate spreadsheets and quickly find key cells.
- 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.
- 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.
- 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?
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
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 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.
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 organization's risk tolerance, the Monte Carlo will give a much more accurate risk picture than the PERT alone.
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.
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.
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.
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.
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.
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'?
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.
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, and 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.
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
- Who should attend
For the Project Manager, Data Analyst or Project Professional:
This high-level, hands-on project management training 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, you’ll 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 Excel’s ability— and it’s at your disposal.
- Bonus Materials
Bonus CD: Those attending will receive an instructor-developed CD loaded with analytical tools, resources and customized templates.