This image has an empty alt attribute; its file name is image.png

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