COURSE 4785 | 2-DAY SESSION
Power Excel
Course Outline:
I. Statistical Process Control Concepts and Charts
The statistical process chart is a cornerstone tool that can be used not only to baseline current process but to track the effectiveness of the solution over time and keep the process on track. The metrics generates from an SPC chart can instantly show whether a process has shifted and pinpoint the accuracy and precision of your processes.
a. How we define accuracy and precision - for a PM, what it means to our metrics
b. Data types and what constitutes a ‘legal’ computation.
c. Measures of Continuous capability
- X-Bar-R, X-Bar-S, XmR, and ImR charts
- P, np, c, and u charts
- Chi-Square distribution
d. Measures of Discrete capability
- Binomial Distribution
- Poisson Distribution
- Hypergeometric distribution
II. Forecasting and Trend Analysis
Participants will explore the use of the key features of the Excel Data Analysis Tool from the creation of randomized test data to the setup of histograms, trendlines, and regression analysis. Students will also get hands-on experience using the MMULT and ARRAY functions to model uncertainties that occur over time: the Markov Chain
III. Process Optimization
We are all faced with resource allocation how to effectively allocate resources force; how to construct the maximum boats given limited raw materials; how return on a pension fund while minimizing what shipping method should be used provide the lowest cost when there is uncertainty regarding how many orders will be optimization model identifies what parameters adjusted, identifies constraints and allows find the ‘best’ answer to their problem
a. Use Solver to optimize processes
b. Re-optimize the model utilizing ‘what-if’ scenarios
c. The Pareto chart - find the noise
IV. Probability
Frequently PMs are called upon to deliver estimates. Unfortunately this is the area highest risk on a project - there is a low that our time estimates will be accurate. Monte Carlo analysis to give your project the reality check it needs. Sometimes between project tasks or events will take out probability forecasting a step where the ability to compute conditional becomes crucial
a. The Monte Carlo analysis
b. Conditional Probability
V. Managing Data
When faced with mountains of data, it sometimes becomes necessary to zero in on specific data relationships or identify the proverbial ‘needle in the haystack’. Excel provides the tools to take you from the 50,000 foot level to the 1 inch level and enables you to reshuffle the data in a format that will be more user friendly. The following functions are key tools in enabling the user to manipulate data
a. AutoFilter function
b. PivotTables
VI. Excel Chart Wizard
Learn how to ‘torture’ Excel into making charts that are not ‘out of the box’. Sometimes the user needs to do something a bit out of the ordinary and unfortunately, the more sophisticated functions are only found in the more sophisticated statistical packages. Find out how Excel can be used to create these charts quickly and easily.
a. Create the following charts using the chart wizard:
- Cumulative distribution
- Probability density
- Binomial and normal distributions
- Box-and-Whisker plot
b. The Excel ‘Movie’
VII. Basic Macro Writing
Why perform a long string of manual steps process when it can be automated? Let writing function allow you to automate process and calculations. Macros can be automatically or at the click of a button.
a. Automating workbook processes
b. Automating chart processes
VIII. Finance Bonus!
Extract the secret to correctly compute Present value) and IRR (Internal Rate of Discover the pitfalls of IRR so that you compute rate of return.
IX. Putting it all together
Use the skills acquired in class to apply study that will use the range of the tools techniques covered in class.
a. Reusable templates
b. Automated processes
c. Probability analysis
d. Process optimization
e. Complex data manipulation
