#### **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*