using excel for financial modeling slides webinar 082014.pdfgovernment finance officers association...

Using Excel for Financial Modeling

WebinarAugust 20, 2014

GOVERNMENT FINANCE OFFICERS ASSOCIATION

Course Instructor Rob Roque

Technology Services ManagerGFOA, Research and Consulting Center


Agenda

Introduction Overview of Functions Being Used During Webinar

Basics Database Preparation Example:

• Preparing a Basic Database Intermediate

Pivot Tables/Data Analysis Example:

• Revenue Forecasting Advanced

Database Calculations Example:

• Dashboard


Basics – Data Extraction

How do you get from this?EXAMPLE RAW DATA,,,,,,,,,Account,Account,County of Chart of Accounts,Type,,,Current,1001-0000-0,Acme Bank - AP,01-CashAssets,1005-0000-0,Acme Bank - Jury,01-Cash,1010-0000-0,Acme Bank - Payroll,01-Cash,1015-0000-0,Cash Other,01-Cash,1020-0000-0,Imprest Cash,01-Cash,1020-0000-B,Fam Support - Palmer,01-Cash,1020-0010-0,Imprest Cash - Civil - Alford,01-Cash,1030-0000-0,Trust - Traffic,01-Cash,1040-0000-0,Trust - Superior Court,01-Cash,1050-0000-0,Encumbered Cash,01-Cash,1105-0000-0,Accounts Receivable,01-Cash,1110-0000-0,A/R Allow for Doubtful Accts,01-Cash,1115-0000-0,Grant Receivable,01-Cash,1120-0000-0,Due (To) From Non-Trial Court,01-Cash,1130-0000-0,Deposits with Others,01-Cash,1205-0000-0,Other Assets,01-Cash,1210-0000-0,Inventory,01-Cash,1215-0000-0,Investments,01-Cash

To this?NT CODE DESCRIPTION ACCOUNT TY0000 0 Acme Bank ‐ AP0000 0 Acme Bank ‐ Jury0000 0 Acme Bank ‐ Payroll0000 0 Cash Other0000 0 Imprest Cash0000 B Fam Support ‐ Palmer0010 0 Imprest Cash ‐ Civil ‐ Alford0000 0 Trust ‐ Traffic0000 0 Trust ‐ Superior Court0000 0 Encumbered Cash0000 0 Accounts Receivable0000 0 A/R Allow for Doubtful Accts0000 0 Grant Receivable0000 0 Due (To) From Non‐Trial Court0000 0 Deposits with Others0000 0 Other Assets


Data Extraction Process

The Tools File Import Text Import Wizard Data Shaping Text To Columns Format Table

Purpose Read External Data How To Interpret Data Database Best Practices Refine Database Layout Format the Data


Data Extraction – The Steps

,,,,,,Account,Account,County of Chart of Accounts,Type,,,Current,1001-0000-0,Acme Bank - AP,01-CashAssets,1005-0000-0,Acme Bank - Jury,01-Cash,1010-0000-0,Acme Bank - Payroll,01-Cash,1015-0000-0,Cash Other,01-Cash,1020-0000-0,Imprest Cash,01-Cash,1020-0000-B,Fam Support - Palmer,01-Cash

Step 1 – Data Extract: Step 2 – Raw Data Preparation:

Step 3 – Database Preparation:


Data PreparationIllustration


Database Set-Up

The Tools Filters Create Table Database Formulas

Create Table Data Validation

Purpose Analyze Related Data Manage Data Views Calculating Data Based

Upon Data Criteria Simple Database

Shortcuts Controlling Data Input


Database Prep – The StepsStep 1 – Define Database:

Step 2 – Define Criteria:

Step 3 – Extract Data:


Database UseIllustration


Intermediate – Data Analysis

How do you get from this? To this?


Pivot Tables

The Tools Pivot Table Wizard

Pivot Chart

Slicer

Purpose Creates multi-

dimensional table view of data

Creates multi-dimensional chart that supports pivot table

Additional views of pivot tables


Pivot Table – The StepsStep 1 – Create Pivot Data: Step 2 – Define Filters:

Step 3 – Define Slices:


Pivot TableIllustration


Forecasting

Tools Scatter Chart

Chart Layout Wizard

Forecast Function

Trend Function

Purpose Plot observed points of

data Access other charting

tools such as line estimation between points of linear data

Estimate future linear data

Regression data based on least squares


Forecasting – The Steps(Linear Data)

YEAR REVENUE1 26,040,891$ 2 26,988,543$ 3 27,846,749$ 4 28,500,401$ 5 29,364,010$ 6 30,145,643$ 7 29,878,456$ 8 31,114,651$ Formula Forecast Trend9 36,184,944$ 31,827,392$ 31,827,392$ 10 36,799,660$ 32,514,608$ 32,514,608$ 11 33,201,824$ 12 33,889,041$ 13 34,576,257$ 14 35,263,474$ 15 40,199,490$ 35,950,690$ 35,950,690$

Actual

Projected

Step 1 – Define Observed Data:

Step 2 – Chart Observed Data:

Step 3 – Forecast Future Data:


Linear Data ForecastIllustration


Scenarios

Tools Scenario Manager

Goal Seek

Data Table

Purpose Define and Apply

Scenarios to Data Define Unknown Data

Based on Known Data and Goal.

Apply a Table of Data to a Scenario


Scenarios – The Steps

Building Permit Revenue ‐ ScenarioYEAR PERMITS FEE PER INCREREVENUE

1 1500 300$ 2.0% 450,000$ 2 1530 300$ 2.0% 459,000$ 3 1558 300$ 1.8% 467,262$ 4 1587 300$ 1.9% 476,140$ 5 1619 300$ 2.0% 485,663$ 6 1653 300$ 2.1% 495,862$ 7 1689 300$ 2.2% 506,771$ 8 1721 300$ 1.9% 516,399$ 9 1756 300$ 2.0% 526,727$ 10 1793 300$ 2.1% 537,789$

Actual

Step 1 – Set Up Data: Step 2 – Conduct What-if:

Step 3 – Scenario Results:


What-IfIllustration

(Scenario Manager, Goal Seek, Data Table)


Dashboards

Tools Pivot Tables

Pivot Charts

Slicers

Pivot Table Connections

Purpose Multi-Dimensional View of

Dashboard Source Data Graphical Depiction of

Pivot Table Data Allow External Users To

Choose Pivot Table Criteria

Connect All Charts to Criteria


Simple Dashboard – The Steps

BUDGETED POSITIONS

FUND TYPE (All)

Row Labels BUDGETED POSITIONS FILLED POSITIONSBOARD OF ELECTION COMMISSIONER 118 105BOARD OF ETHICS 9 9BUS AFFAIRS AND CONSUMER PROT 186 154CHICAGO DEPT OF TRANSPORTATION 361 261

Performance Metrics Dashboard

0

50

100

150

200

250

300

350

400

450

DEPARTMENT OF LAW

BUDGETED POSITIONS

FILLED POSITIONS

$77,000$78,000$79,000$80,000$81,000$82,000$83,000$84,000$85,000$86,000$87,000

DEPARTMENT OF LAWCDBG

DEPARTMENT OF LAWLocal

Average Budgeted Salary by Fund Type

$0$100,000$200,000$300,000$400,000$500,000$600,000

Top 10 Salaries Positions By Fund Type

DEPARTMENT OF LAWCDBG

DEPARTMENT OF LAWLocal

Step 1 – Define Pivot Table:Step 2 – Define Views:

Step 3 – Define Layout:


Dashboard Set-UpIllustration


Resources

Other Tools – Data Analysis May need to install (Excel Add-In) Provides high-end descriptive statistics

Basics in Statistics Khan Academy – www.khanacademy.org

Excel Basics “Real Excel power users know these 11 tricks”

(http://www.pcworld.com/article/2109084/real-excel-power-users-know-these-11-tricks.html)

“11 Advanced Excel Tricks That Will Help You Get An Instant Raise At Work” (http://www.businessinsider.com/excel-tricks-vlookup-index-match-pivot-tables-array-2013-5?op=1#ixzz3AlbS6pPN)

“20 Tricks That Can Make Anyone An Excel Expert” (http://www.lifehack.org/articles/technology/20-excel-spreadsheet-secrets-youll-never-know-you-dont-read-this.html)


Thank You!

Rob RoqueTechnology Services Manager312-977-9700312-339-1455 (cell)[email protected]

using excel for financial modeling slides webinar 082014.pdfgovernment finance officers association...

Documents