FINANCIAL MODELING
AND
FORCASTING
Prof Y.K.Weerakoon
Financial Modeling is useful for many
purposes
• Corporate: valuation, forecasting,
strategic planning, EVA measurement,
cash management, budgeting, ...
• Investments: securities analysis,
portfolio construction, analysis of risk-
return tradeoff, risk management, ...
• Derivatives: asset pricing, portfolio
insurance, Value at Risk analysis, ...
Who needs Financial Modeling
• Financial Controllers
• Managers
• Owners
• Financial analysts
• Present and potential investors
Uses of Financial Modeling
• Allows accurate decision making
• Less time consuming
• Easy to use
• Consistency in decision making
Financial Modeling Process
• Problem Conception
• Model Construction
• Flexibility Testing
• Output Summary
1.Problem Conception
“Understanding the purpose for a
model”
A spreadsheet is developed in order
to aid in the solution of a specific
problem.
Problem Conception Involves,
• Constructing a proper theoretical foundation
• Identifying, evaluating and documenting
assumptions
• Identifying input and output variables
Ex. What information must the user provide?
What results should the model produce?
Are there interim calculations?
2.Model Construction
It is the construction of a
spreadsheet.
It includes,
• Layout
• Prototyping
• Reporting
• Formatting
Layout
• Documentation area
a) Description of the problem
b) Solution
c) Instructions for using the model
• Input area
• Interim calculations
• Output area
Prototyping
Unique initial equations that tie the
inputs, interim and output areas
together
Verifying initial equation is crucial in
spreadsheet construction.
Reporting
• Display of pertinent results. A good
model displays in an easily
understood manner what the inputs
were, what interim values were
formed, and what results were
generated.
Formatting
• Include assigning the proper numeric
format, highlighting important results
and delineating separate spreadsheet
areas.
3.Flexibility Testing
• Does the model quickly adapt when
variables or assumptions change?
• Does the input area account for all the
necessary model inputs?
• Can a change in a prototype equation be
efficiently copied throughout the relevant
range?
• Can expansion or modification of the
input area be accomplished quickly?
• Does the model accurately account for all
facts of the problem?
• Is the model consistent throughout?
Output Summary
• After all the work of understanding
the problem, laying out a logical
model and generating a solution, this
solution must be presented in a well
written, competent fashion.
5 rules for financial modeling
• These rules will help you build better
spreadsheets
• Readibility, Logic, Simplicity—this is what
you should strive for
Rule 1: Try to put important variables
in one place
A B C D E F G H I
1 SAVING FOR COLLEGE
2 Interest rate 8% Critical parameters (sometimes called "value
3 Annual deposit 12,000.00 drivers") are in the upper left-hand corner. The
4 Annual cost of college 35,000 actual of saving for a college education is
5 discussed in Chapter 2.
In bank on birthday, Deposit or End of year
End of year
Birthday before withdrawal at before
with interest
deposit/withdrawal beginning of year interest
6
7 10 0.00 12,000.00 12,000.00 12,960.00
8 11 12,960.00 12,000.00 24,960.00 26,956.80
9 12 26,956.80 12,000.00 38,956.80 42,073.34
10 13 42,073.34 12,000.00 54,073.34 58,399.21
11 14 58,399.21 12,000.00 70,399.21 76,031.15
12 15 76,031.15 12,000.00 88,031.15 95,073.64
13 16 95,073.64 12,000.00 107,073.64 115,639.53
14 17 115,639.53 12,000.00 127,639.53 137,850.69
15 18 137,850.69 -35,000.00 102,850.69 111,078.75
16 19 111,078.75 -35,000.00 76,078.75 82,165.05
17 20 82,165.05 -35,000.00 47,165.05 50,938.25
18 21 50,938.25 -35,000.00 15,938.25
19
20 NPV of all payments 6,835.64 <-- =C7+NPV(B2,C8:C18)
Rule 2: Don’t hard-wire numbers
• Use formulas instead of numbers
Rule 3: Avoid using blank
columns to accommodate cell
“spillovers”
Rule 4: Make your Excel default one
sheet
Office Button
Excel Options
Rule 5: Turn off the “auto jump-down”
feature
• Excel default: when you hit Enter, the
cursor jumps down one cell
• In finance, we need to carefully look at
what we’ve entered into the cell …
• So turn this off (next slide)
Using EXCEL
In Financial Modeling
Excel Functions
• Financial Functions
• Math Functions
• Conditional Functions
• Text Functions
• Statistical Functions
Financial Functions
• NPV
Ex. The discount rate is 10%, cash flows are -
100,35,33,34,25,and 16 from year 0 to year 5. Calculate
NPV.
• IRR
Ex.i. Calculate IRR for above.
ii. See work sheet
• FV
i. Calculate FV of 5 annual deposits of Rs.1000 at
5%.Whan deposited beginning of the year, at the end of
the year.
• PV
i. Payment of 100 for 10 periods at 10%. Calculate PV if cash flow
occur at end of period and at beginning of period.
• PMT
i . Loan of Rs.1000 to be paid over 10 years at an interest rate of
8%. Calculate annual payment if happens end of period and
beginning of period.
• Rate
i. Initial payment Rs.600,number of periods 10,annual payment
100 calculate the rate if payment is made end of period and
beginning of period. Rate Vs. IRR
• NPER
i. You borrow Rs.1000 from a bank, it charges 10% interest. You
intend to pay Rs.250 per year. How long is to repay the loan.
Math Functions
• Exp
• Continuous compounding
FV= PVert
In Excel FV = PV *Exp(r*t)
PV=FV/ert
In Excel PV= FV*Exp(-r*t)
• LN
Discrete return =P1/P0-1
Continuous compounding
P0exp(r )= P1 exp (r) =P1/P0
The function that solves this equation is the natural
logarithm ln,
r=ln(P1/P0)
• When t = 1
P0exp(rt )= P1 exp (rt) =P1/P0
r= 1/t ln(P1/P0)
• Round, RoundDown, RoundUp, Trunc
• Sqrt
• Sum
• SumIf
• Sumproduct
Conditional Functions
• If
• VLookup
• Hlookup
Text Functions
• Text
• Left, Right, Mid,Len
Statistical Functions
• Average
• Covar
• Correl
• Frequency
• Intercept
• Median,Large and Rank
• Count, CountIf, CountA
