Description
The “static approach” develops a set of key assumption that have one output, irrespective of whether it may or may not be realistically attainable.
Introduction to Financial
Modeling
Copyright 2008, The World Bank Group. All Rights Reserved.
Static vs. Dynamic
The “static approach” develops a set of key
assumption that have one output,
irrespective of whether it may or may not
be realistically attainable.
2
The dynamic approach assesses the
interaction of all essential variables
such as, technical, institutional and
financial and derives a solution
within the current political economy
and that important trade-offs
between objectives that may be in
conflict.
Static vs.
Dynamic
Copyright 2008, The World Bank Group. All Rights Reserved.
Dynamic Financial Modeling Features
? Basis for Key Financial and Policy Decisions
? Employs All Major Assumptions of the Case
(i.e... Financial, Operating, Regulatory,
etc...)
? Fully Integrates the Three Primary
Statements & All Operating Schedules
? Allows Quick Simulation of Key
Performance Variables
? Flexible, Powerful, yet Extremely Simple
Analytical Tool
? Guides to Find a Solution
Dynamic Funds Flow
Cash
Equity Infusion
Operations
Losses
Operations
Profits
Assets
Step 1: Simple Cash
Business
Dynamic Funds Flow
Dividends Paid
Cash
Equity Infusion
Operations
Losses
Operations
Profits
Assets
P
u
r
c
h
a
s
e
S
a
l
e
Receivables
Inventories
Sales
Taxes
Step 2:
Expanding
Operations
Dynamic Funds Flow
Dividends Paid
Cash
Equity Infusion
Operations
Losses
Bad Debts
Operations
Profits
Assets
P
u
r
c
h
a
s
e
S
a
l
e
Receivables
Inventories
Debt
Collections
Receivables
Sales
Retained
Taxes
Step 3: Extending
Credit & Marketing
Dynamic Funds Flow
Dividends Paid
Cash
Equity Infusion
Operations
Losses
Bad Debts Operations
Profits
Assets
P
u
r
c
h
a
s
e
S
a
l
e
Receivables
Trade Credit
Inventories
Debt
Debt Service
Interest
Collections
Receivables
Sales
Retained
Taxes
Step 4:
Raising
&
Servicing
Debt
Copyright 2008, The World Bank Group. All Rights Reserved.
Essentially a Giant Puzzle!!
9
But there’s
redemption
from all this
madness!
10
It’s Organization!!
Copyright 2008, The World Bank Group. All Rights Reserved.
Components of a Financial Model
? Income Statement
? Cash Flow
? Balance Sheet
? Investment
Program
? Borrowing
Summary
? Account
Movements
? Financial Mgnt.
Assumptions
? Installed Capacity
? Operating
Characteristics
? Revs. & Production
? Operating Expenses
Financial Module Operations Module
Dempasar Badung Klungkung Tabanan Gianyar
South Bali
Treatment Plant
Opening
Project Data
Mgnt. Assump.
Movement of Main Accounts
Balance Sheet
Cash Flow Statement
Consolidated Income Statement
Financing Borrowing Summary
Investment Program
Tariff Schedule
Connections
Tariff Schedule
Connections
Tariff Schedule
Connections
Tariff Schedule
Connections
Tariff Schedule
Connections
Production
Operating Exp. Operating Exp. Operating Exp. Operating Exp. Operating Exp.
Fixed Assets Fixed Assets Fixed Assets Fixed Assets Fixed Assets Fixed Assets
Operation Analysis Operation Analysis Operation Analysis Operation Analysis Operation Analysis
Operating Exp.
Gen & Overhead
Conceptual Design of Regional Company Financial Model
Xiaolangdi Multipurpose Dam Project, Phase II
Cost Allocation Table
Power Unit Water Supply F&S Control Consolidated
Capital Investment Costs 37.0% 29.0% 31.0% 97.0%
Operating Costs:
Materials Cost 100.0% 0.0% 0.0% 100.0%
Wages 54.4% 17.9% 27.7% 100.0%
Other Direct Costs 54.4% 17.9% 27.7% 100.0%
Works Maintenance 44.2% 21.8% 34.0% 100.0%
Reservoir Maintenance 100.0% 0.0% 0.0% 100.0%
Overhead 54.4% 17.9% 27.7% 100.0%
Sales & Miscellaneous 100.0% 0.0% 0.0% 100.0%
Administrative Expenses 54.4% 17.9% 27.7% 100.0%
Bad Debt Expense 0.0% 100.0% 0.0% 100.0%
Depreciation Expense 26.0% 29.0% 45.0% 100.0%
FX Loans Losses 26.0% 29.0% 45.0% 100.0%
Interest Charges 26.0% 29.0% 45.0% 100.0%
Income Taxes 100.0% 0.0% 0.0% 100.0%
Industrial Free Zone
Total Site Size Allocation 1997 1998 1999 2000 2001 m2
Site Size (m2) 472000 472000
Allowance for Green Area 70.00%
for Industrial Lots (m2)
Total Developed Area 330400 Completion Schedule 330400
of Which: Developer will Build Factories 63.00% 0.00% 26.00% 58.00% 100.00% 100.00% 208152
of Which: Tenant Will Build Factories 37.00% 90.00% 100.00% 100.00% 100.00% 100.00% 122248
Total Leaseable Area 288770 288770
of Developer Factory Buildings 80.00% 0 43296 96583 166522 166522 166522
of Industrial Lots 100.00% 110023 122248 122248 122248 122248 122248
Breakdown of Industrial Lots by Activity 122248 122248
for Free Trade Zone Area 40.00% 44009 48899 48899 48899 48899 48899
for Gaza Industrial Park Area 60.00% 66014 73349 73349 73349 73349 73349
Breakdown of Factory Buildings by Activity 166522 166522
for Free Trade Zone Area 38.00% 0 16452 36701 63278 63278 63278
for Gaza Industrial Park Area 62.00% 0 26843 59881 103243 103243 103243
Occupancy Rates
for Factories 0.00% 0.00% 50.00% 75.00% 95.00% 95.00%
for Industrial Lots 50.00% 75.00% 95.00% 95.00% 95.00% 95.00%
Water Consumption & Sales
Opening Consumption 1995 35,884
1996 1997 1998 1999 2000
Total Water Sales (P000) 469,240 539,626 633,295 692,824 705,434
Total Consumption 26,661,374 30,660,580 35,982,644 39,365,012 40,081,455
Total Connections 60,368 69,423 81,473 89,132 90,754
Percent Growth 68.2% 15.0% 17.4% 9.4% 1.8%
Growth Factor 1.68 1.93 2.27 2.48 2.53
Commecial Consumption
Annual Sales (P000) 184,206 211,837 248,607 271,976 276,926
Annual Consumption (m3) 10,466,242 12,036,178 14,125,418 15,453,207 15,734,456
Average Usage/Connection 1.90 1.90 1.90 1.90 1.90
Tariff/m3 17.60 17.60 17.60 17.60 17.60
Number of Connections 15,092 17,356 20,368 22,283 22,688
Percent Commercial 25% 25% 25% 25% 25%
Residential Consumption
Annual Sales (P000) 285,034 327,789 384,687 420,848 428,507
Annual Consumption (m3) 16,195,132 18,624,402 21,857,226 23,911,805 24,347,000
Average Usage/Connection 0.980 0.980 0.980 0.980 0.980
Tariff/m3 17.60 17.60 17.60 17.60 17.60
Number of Connections 45,276 52,067 61,105 66,849 68,065
Percent Residential 75% 75% 75% 75% 75%
Water District
Copyright 2008, The World Bank Group. All Rights Reserved.
Organizing Your Model
Key Considerations
? Who’s Perspective
? Currency Risk
? Profit Cost/Centers
? Implementation Stages
Copyright 2008, The World Bank Group. All Rights Reserved.
Some Useful Tips
? Use a Standard Template for All Models
? Years in Columns, Accounts/Categories in Rows
? Don’t Cut Corners (worksheets schedules
statements)
? Put all The Output Statements in One Sheet (pass
protect)
? Customize Operating Module (Brainstrom)
? Combine Operating Assumptions with Results in
Schedules (color code).
? Use the movement of Accounts Schedule to Link
to Statements
17
Copyright 2008, The World Bank Group. All Rights Reserved.
How I Organize Models.
? Title
? Project Data Sheet
? Opening Balance Sheet
? Management Assumptions
? Demand, Production & Operations
? Investment (CAPEX) Program
? Financing Options
? Output Statements
? Rate of Return Calculations
18
Copyright 2008, The World Bank Group. All Rights Reserved.
Project Data Sheet
19
20
Opening Balance Sheet
21
Management
Assumptions
2010 2011 2012 2013 2014
Cash Position 0.7 0.7 0.7 0.7 0.7 Months of Operating Expenses
Provision for Bad Debt 1% 1% 1% 1% 1% On Oustanding Receivables
Power Accounts Receivable 1.2 1.2 1.2 1.2 1.2 Months of Annual Revenues
Inventories 0.5 0.5 0.5 0.5 0.5 Months of Operating Expenses
Other Current Assets 50.0 60.0 60.0 60.0 60.0 Days of Operating Expenses
Interest Earned 6% 5% 5% 5% 5% Earned on Short-Term Deposits
Short Term Borrowings Percent of CAPEX
Accounts Payable 2.1 2.0 2.0 2.0 2.0 Months of Operating Expenses
Tax on Interest
Customer Deposits
0.065
0.065
0.065
0.065
0.065 Amount per New Sercice Connection
Taxes Payable
0.3
0.3
0.3
0.3
0.3 Months of Income Tax Chage
Interest Payable
1
1
1
1
1 Months of Interest Expense
Income Taxes 20% 20% 20% 20% 20% On Profit Before Taxes
22
Demand, Production &
Operation
23
Investment Program
24
FX Financing Schedule
25
Movement of Accounts
26
27
Copyright 2008, The World Bank Group. All Rights Reserved.
Focus on The Fund Flow
Statement
? Non-Conventional Format to Simulate
Decision Making of the CFO
? Incorporates a Financing Logic
? Incorporates Inherent Desirability of
Investments
? Point of Reference Where Solution is
Attained
Copyright 2008, The World Bank Group. All Rights Reserved.
29
1st Priority
2nd Priority
3rd Priority
4th Priority
5th Priority
Copyright 2008, The World Bank Group. All Rights Reserved.
Common Mistakes
Copyright 2008, The World Bank Group. All Rights Reserved.
Common Mistakes
1. The model becomes the End Goal, not the financial
results
2. Tries to do too many things, tariff structure, trend
analysis, volume forecasts, demand elasticity
3. Not properly organized
4. Cutting corners
5. Detailed breakdown/elaboration on non material
variables
6. Requiring inordinate amounts of data
7. Add historical presentation to projections
8. Goal seek for an independent variable
9. Investment and projection period
10. Escalating revenues with expenses
11. Accumulating cash
12. Linking to other workbooks
Copyright 2008, The World Bank Group. All Rights Reserved.
Discussion!!
32
doc_955486376.ppt
The “static approach” develops a set of key assumption that have one output, irrespective of whether it may or may not be realistically attainable.
Introduction to Financial
Modeling
Copyright 2008, The World Bank Group. All Rights Reserved.
Static vs. Dynamic
The “static approach” develops a set of key
assumption that have one output,
irrespective of whether it may or may not
be realistically attainable.
2
The dynamic approach assesses the
interaction of all essential variables
such as, technical, institutional and
financial and derives a solution
within the current political economy
and that important trade-offs
between objectives that may be in
conflict.
Static vs.
Dynamic
Copyright 2008, The World Bank Group. All Rights Reserved.
Dynamic Financial Modeling Features
? Basis for Key Financial and Policy Decisions
? Employs All Major Assumptions of the Case
(i.e... Financial, Operating, Regulatory,
etc...)
? Fully Integrates the Three Primary
Statements & All Operating Schedules
? Allows Quick Simulation of Key
Performance Variables
? Flexible, Powerful, yet Extremely Simple
Analytical Tool
? Guides to Find a Solution
Dynamic Funds Flow
Cash
Equity Infusion
Operations
Losses
Operations
Profits
Assets
Step 1: Simple Cash
Business
Dynamic Funds Flow
Dividends Paid
Cash
Equity Infusion
Operations
Losses
Operations
Profits
Assets
P
u
r
c
h
a
s
e
S
a
l
e
Receivables
Inventories
Sales
Taxes
Step 2:
Expanding
Operations
Dynamic Funds Flow
Dividends Paid
Cash
Equity Infusion
Operations
Losses
Bad Debts
Operations
Profits
Assets
P
u
r
c
h
a
s
e
S
a
l
e
Receivables
Inventories
Debt
Collections
Receivables
Sales
Retained
Taxes
Step 3: Extending
Credit & Marketing
Dynamic Funds Flow
Dividends Paid
Cash
Equity Infusion
Operations
Losses
Bad Debts Operations
Profits
Assets
P
u
r
c
h
a
s
e
S
a
l
e
Receivables
Trade Credit
Inventories
Debt
Debt Service
Interest
Collections
Receivables
Sales
Retained
Taxes
Step 4:
Raising
&
Servicing
Debt
Copyright 2008, The World Bank Group. All Rights Reserved.
Essentially a Giant Puzzle!!
9
But there’s
redemption
from all this
madness!
10
It’s Organization!!
Copyright 2008, The World Bank Group. All Rights Reserved.
Components of a Financial Model
? Income Statement
? Cash Flow
? Balance Sheet
? Investment
Program
? Borrowing
Summary
? Account
Movements
? Financial Mgnt.
Assumptions
? Installed Capacity
? Operating
Characteristics
? Revs. & Production
? Operating Expenses
Financial Module Operations Module
Dempasar Badung Klungkung Tabanan Gianyar
South Bali
Treatment Plant
Opening
Project Data
Mgnt. Assump.
Movement of Main Accounts
Balance Sheet
Cash Flow Statement
Consolidated Income Statement
Financing Borrowing Summary
Investment Program
Tariff Schedule
Connections
Tariff Schedule
Connections
Tariff Schedule
Connections
Tariff Schedule
Connections
Tariff Schedule
Connections
Production
Operating Exp. Operating Exp. Operating Exp. Operating Exp. Operating Exp.
Fixed Assets Fixed Assets Fixed Assets Fixed Assets Fixed Assets Fixed Assets
Operation Analysis Operation Analysis Operation Analysis Operation Analysis Operation Analysis
Operating Exp.
Gen & Overhead
Conceptual Design of Regional Company Financial Model
Xiaolangdi Multipurpose Dam Project, Phase II
Cost Allocation Table
Power Unit Water Supply F&S Control Consolidated
Capital Investment Costs 37.0% 29.0% 31.0% 97.0%
Operating Costs:
Materials Cost 100.0% 0.0% 0.0% 100.0%
Wages 54.4% 17.9% 27.7% 100.0%
Other Direct Costs 54.4% 17.9% 27.7% 100.0%
Works Maintenance 44.2% 21.8% 34.0% 100.0%
Reservoir Maintenance 100.0% 0.0% 0.0% 100.0%
Overhead 54.4% 17.9% 27.7% 100.0%
Sales & Miscellaneous 100.0% 0.0% 0.0% 100.0%
Administrative Expenses 54.4% 17.9% 27.7% 100.0%
Bad Debt Expense 0.0% 100.0% 0.0% 100.0%
Depreciation Expense 26.0% 29.0% 45.0% 100.0%
FX Loans Losses 26.0% 29.0% 45.0% 100.0%
Interest Charges 26.0% 29.0% 45.0% 100.0%
Income Taxes 100.0% 0.0% 0.0% 100.0%
Industrial Free Zone
Total Site Size Allocation 1997 1998 1999 2000 2001 m2
Site Size (m2) 472000 472000
Allowance for Green Area 70.00%
for Industrial Lots (m2)
Total Developed Area 330400 Completion Schedule 330400
of Which: Developer will Build Factories 63.00% 0.00% 26.00% 58.00% 100.00% 100.00% 208152
of Which: Tenant Will Build Factories 37.00% 90.00% 100.00% 100.00% 100.00% 100.00% 122248
Total Leaseable Area 288770 288770
of Developer Factory Buildings 80.00% 0 43296 96583 166522 166522 166522
of Industrial Lots 100.00% 110023 122248 122248 122248 122248 122248
Breakdown of Industrial Lots by Activity 122248 122248
for Free Trade Zone Area 40.00% 44009 48899 48899 48899 48899 48899
for Gaza Industrial Park Area 60.00% 66014 73349 73349 73349 73349 73349
Breakdown of Factory Buildings by Activity 166522 166522
for Free Trade Zone Area 38.00% 0 16452 36701 63278 63278 63278
for Gaza Industrial Park Area 62.00% 0 26843 59881 103243 103243 103243
Occupancy Rates
for Factories 0.00% 0.00% 50.00% 75.00% 95.00% 95.00%
for Industrial Lots 50.00% 75.00% 95.00% 95.00% 95.00% 95.00%
Water Consumption & Sales
Opening Consumption 1995 35,884
1996 1997 1998 1999 2000
Total Water Sales (P000) 469,240 539,626 633,295 692,824 705,434
Total Consumption 26,661,374 30,660,580 35,982,644 39,365,012 40,081,455
Total Connections 60,368 69,423 81,473 89,132 90,754
Percent Growth 68.2% 15.0% 17.4% 9.4% 1.8%
Growth Factor 1.68 1.93 2.27 2.48 2.53
Commecial Consumption
Annual Sales (P000) 184,206 211,837 248,607 271,976 276,926
Annual Consumption (m3) 10,466,242 12,036,178 14,125,418 15,453,207 15,734,456
Average Usage/Connection 1.90 1.90 1.90 1.90 1.90
Tariff/m3 17.60 17.60 17.60 17.60 17.60
Number of Connections 15,092 17,356 20,368 22,283 22,688
Percent Commercial 25% 25% 25% 25% 25%
Residential Consumption
Annual Sales (P000) 285,034 327,789 384,687 420,848 428,507
Annual Consumption (m3) 16,195,132 18,624,402 21,857,226 23,911,805 24,347,000
Average Usage/Connection 0.980 0.980 0.980 0.980 0.980
Tariff/m3 17.60 17.60 17.60 17.60 17.60
Number of Connections 45,276 52,067 61,105 66,849 68,065
Percent Residential 75% 75% 75% 75% 75%
Water District
Copyright 2008, The World Bank Group. All Rights Reserved.
Organizing Your Model
Key Considerations
? Who’s Perspective
? Currency Risk
? Profit Cost/Centers
? Implementation Stages
Copyright 2008, The World Bank Group. All Rights Reserved.
Some Useful Tips
? Use a Standard Template for All Models
? Years in Columns, Accounts/Categories in Rows
? Don’t Cut Corners (worksheets schedules
statements)
? Put all The Output Statements in One Sheet (pass
protect)
? Customize Operating Module (Brainstrom)
? Combine Operating Assumptions with Results in
Schedules (color code).
? Use the movement of Accounts Schedule to Link
to Statements
17
Copyright 2008, The World Bank Group. All Rights Reserved.
How I Organize Models.
? Title
? Project Data Sheet
? Opening Balance Sheet
? Management Assumptions
? Demand, Production & Operations
? Investment (CAPEX) Program
? Financing Options
? Output Statements
? Rate of Return Calculations
18
Copyright 2008, The World Bank Group. All Rights Reserved.
Project Data Sheet
19
20
Opening Balance Sheet
21
Management
Assumptions
2010 2011 2012 2013 2014
Cash Position 0.7 0.7 0.7 0.7 0.7 Months of Operating Expenses
Provision for Bad Debt 1% 1% 1% 1% 1% On Oustanding Receivables
Power Accounts Receivable 1.2 1.2 1.2 1.2 1.2 Months of Annual Revenues
Inventories 0.5 0.5 0.5 0.5 0.5 Months of Operating Expenses
Other Current Assets 50.0 60.0 60.0 60.0 60.0 Days of Operating Expenses
Interest Earned 6% 5% 5% 5% 5% Earned on Short-Term Deposits
Short Term Borrowings Percent of CAPEX
Accounts Payable 2.1 2.0 2.0 2.0 2.0 Months of Operating Expenses
Tax on Interest
Customer Deposits
0.065
0.065
0.065
0.065
0.065 Amount per New Sercice Connection
Taxes Payable
0.3
0.3
0.3
0.3
0.3 Months of Income Tax Chage
Interest Payable
1
1
1
1
1 Months of Interest Expense
Income Taxes 20% 20% 20% 20% 20% On Profit Before Taxes
22
Demand, Production &
Operation
23
Investment Program
24
FX Financing Schedule
25
Movement of Accounts
26
27
Copyright 2008, The World Bank Group. All Rights Reserved.
Focus on The Fund Flow
Statement
? Non-Conventional Format to Simulate
Decision Making of the CFO
? Incorporates a Financing Logic
? Incorporates Inherent Desirability of
Investments
? Point of Reference Where Solution is
Attained
Copyright 2008, The World Bank Group. All Rights Reserved.
29
1st Priority
2nd Priority
3rd Priority
4th Priority
5th Priority
Copyright 2008, The World Bank Group. All Rights Reserved.
Common Mistakes
Copyright 2008, The World Bank Group. All Rights Reserved.
Common Mistakes
1. The model becomes the End Goal, not the financial
results
2. Tries to do too many things, tariff structure, trend
analysis, volume forecasts, demand elasticity
3. Not properly organized
4. Cutting corners
5. Detailed breakdown/elaboration on non material
variables
6. Requiring inordinate amounts of data
7. Add historical presentation to projections
8. Goal seek for an independent variable
9. Investment and projection period
10. Escalating revenues with expenses
11. Accumulating cash
12. Linking to other workbooks
Copyright 2008, The World Bank Group. All Rights Reserved.
Discussion!!
32
doc_955486376.ppt