
I have taught hundreds of workshops and course using Excel. Contact me to discuss how I can help your organization use Excel in a more efficient and sophisticated way to improve your operations. We can go from the basics, through intermediate level and advanced topics.
Typical Workshop Outline
PART
1
Introduction
1.1 Excel Efficiency
1.1.1 Auto Fill
1.1.2 Selecting in Excel
1.1.3 Formulas, Functions and
Addressing
1.2 Naming Cells and Ranges
1.3 Simple & Quick Charts
1.3. Advanced Charting techniques
1.4. Sparklines
PART 2 If functions Text & Flash-Fill
2.1 IF Functions in Excel
2.2 Nested if– Payroll Example
2.3 Text manipulation
2.4 Auto-fill and simulations
PART 3 Statistics for non-statisticians
3.1 Descriptive statistics
3.2 Frequency Distributions
3.3 Data Analysis.
3.4 Histograms
3.5 Regressions Using Excel and Excel Charts
PART
4 What IF
Analysis & Sensitivity Analysis
4.1 What-if Analysis
4.1.1 Goal Seek
4.1.2 Data Tables Sensitivity Analysis
4.1.3 Two Way Tables
4.3 Using Scroll Bars for Sensitivity Analysis
4.4 Break-Even-Analysis and animating charts
PART 5 3D and Lookup
5.1 Going the 3rd Dimension
5.1.1 A Payroll Example
5.1.2 A Second Example Inventory
5.2 Lookup Tables
5.2.1 Approximate Match Lookup
5.2.2 An Exact Match Lookup
PART
6 Data and
Data Mining
6.1 Sorting Data
6.2 Auto Filter
6.3 Data Forms
6.4 Grouping and Outlining Data
6.5 Subtotals
6.6 Pivot Tables and PivotTable Examples
6.6.1 Simple Pivot Tables
6.6.2 Advance Pivot Tables and Data
Mining
6.6.3 Pivot Table Slicers
PART 7 Advanced Topics
7.1 Solver
7.2 Monte Carlos Simulation Risk
7.3 More Finance
7.3.1 Amortization tables
7.3.2 NPV and IRR, and advanced Excel financial function (XNPV, XIRR)
Advanced Topics
Understanding How Financial Functions work (1, time value of money, IRR & NPV, compare investments)
PV- calculates the total amount that a future investment is worth currently
RATE- is used to calculate the interest applied on a loan or an investment.
NPV- uses a series of cash flows to calculate the present value of an investment
IRR – is used to calculate an internal rate of return based on a series of cash flows
Sample Depreciation Functions (depreciation)
SLN- Returns the straight-line depreciation of an asset for one period
SYD- Returns the sum-of-years’ digits depreciation of an asset for a specified
period
Loans and Amortization Tables (Amortization)
PMT- is used to calculate the regular payment of loan or an investment
PPMT- calculates the actual amount that applies to the balance of the loan.
This is referred to as the principal
IPMT- calculates the amount paid as interest for a period of a loan or an
investment
Building Amortization Tables
Using the Excel Data Table to Create Financial Tables and
Charts (2, Data Table)
Data Table
Goal Seek
Charts
Apartment building Case study
Advanced Excel financial function (XIRR and XNPV)
XNPV- Returns the net present value for a schedule of cash flows that is not
necessarily periodic
XIRR- Returns the internal rate of return for a schedule of cash flows that is
not necessarily periodic
Monte Carlos Simulations using Excel
How to uses Monte Carlo simulation
RAND() and how to utilize it with different probability distribution functions:
Simulating discrete and normal random variables
Simulation Risk Analysis Case study
Solver and Finance (intro solver, capital budget)
Using the Solver to choose among capital investment projects
Investment Case study