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*