Financial Modeling

1
Financial Modeling

• Getting Started

2
What is Modeling?
Model
Blueprint
3
What is Modeling?

• If Carle had modeled its new parking garage it
  might have seen that the elevator from the
  Medivac Helipad on the roof to the Emergency Room
  on the ground floor was not big enough to
  accommodate a gurney.

4
What is Financial Modeling?

• A financial model illustrates relationships using
  real (realistic) numbers so that it can answer
  what if? questions or make projections.

Financial TheoryAccounting Financial
Relationships
Financial Model
5
Model

• A Model specifies the relationship between inputs
  and outputs.

Output
Inputs
6
Deterministic Model

• A Deterministic Model generates the same output
  every time you calculate
• What happens to the Future Value if the interest
  rate increases?

7
Stochastic Model

• A Stochastic Model uses random inputs and gets a
  different output every time you calculate.
• Now what happens to Future Value?

rN(3,2)
8
Attributes of Good Models

• Realistic
• Error-Free
• Flexible
• Easy to use
• Easy to understand

9
Attributes of Good Models

• Realistic
• Assumptions, relationships, and inputs must be
  realistic so that the outputs are useable.
• Example The SP model that assigned AAA ratings
  to sub-prime mortgage derivatives
• constrained the price change in houses to be
  positive.
• had no demographic parameter

10
Attributes of Good Models

• Error Free
• harder than it looks

11
Attributes of Good Models

• Flexible
• This is a two edged sword. Develop the model to
  be easy and error free, then add elements of
  flexibility. Experience will tell you when a
  model gets too complicated and should be
  segregated into separate models for separate
  purposes.

12
Attributes of Good Models

• Easy to use
• Use clear labels and descriptions

vs
13
Attributes of Good Models

• A financial model is only as good as the analyst
  using it.
• The Harvard Interest Rate Model

14
Course

• This course was created in response to feedback
  from corporate recruiters.

Stays with you
Training
Education
Stays with the corporation
15
Course Structure
16
Course Structure

• Models
• Quizzes
• Projects

17
Models

• In each class we will develop a model or portion
  thereof
• Each model will be given a due date (generally
  noon on the day following the class)

18
Models

• Models returned with comments
• You are encouraged to resubmit corrected models
  for 80 of full marks.
• The due date for re-submissions is noon on the
  day following the next class.
• There are no re-re-submissions

19
Note

• We will frequently work a model from class to
  class.
• If you do not have the previous model completed
  then you will have nothing with which to work
• If you miss a class remember that the template
  and class powerpoint in on Compass.

20
Submit models through the course web page
21
Submission

• http//www.business.uiuc.edu/ finance/FinancialM
  odeling/
• Submissions are automatically time and date
  stamped and off loaded when due.

22
Quizzes

• There are several in-class Quizzes throughout the
  course
• These are due at the end of the class period.
• Quizzes are designed to test your modeling skills
  under pressure there are no re-submissions.

23
Projects

• Presentation of a model developed
• As an extension of a class model
• For another class (so long as you have permission
  from the instructor)
• For an internship or other work (so long as you
  have permission from the company for whom the
  model was developed)
• Presentations are peer evaluated.

24
Professionalism

• Professionalism requires collaboration, not
  cheating.
• You are encouraged to help each other succeed,
  but not by doing their work for them.
• I expect each student to develop their models
  independently. Copying anothers model is
  considered cheating and will result in a grade of
  0.

25
Professionalism

• Professionalism requires decorum
• While in class you will work on Financial
  Modeling.
• Students working on games, email, networking, or
  assignments for other classes will be asked to
  work elsewhere.

26
Financial Modeling

• Some notes before we begin

27
Finance vs Modeling

• The assumption is that you know the finance
• If you need to refresh your memory
• Financial Markets (Oltheten Waspi)
• Corporate Finance
• Financial Modeling (Sengupta)

28
Excel Issues
Its not a bug,its a feature
29
Mathematics

• Excel uses the IEEE Double Precision Floating
  Point number format (15 decimal places
• Thus 1/3 1/3 1/3 0.999999999999999
• If you want 1/3 1/3 1/3 1 you are now out
  of balance.

Institute of Electrical and Electronic Engineers
30
Data vs Format

• Data can be formatted to look like anything
• 5 is stored as 0.05
• 1 September 2008 is stored as the number 39,692
  and can be used in further calculation.
• September 1, 2008 is stored as September 1,
  2008 and must be manipulated as text.

31
Excel Versions

• Excel 2007 is not an upgrade of Excelit
  differs substantially from earlier versions
• BIF Trading Lab
• Excel 2007 is the default
• Excel 2003 must be started and then filled with a
  file

32
Excel 2007

• Buries functionality behind "user friendly"
  graphics
• 65535 is rendered as 100,000
• VBA is inconsistent with previous versions.

33
Excel 2007

• Saves files as .xlsx, xltx, or xlsb all of
  which are incompatible with previous versions of
  excel.
• Use Save as and save as .xls before submitting

34
Excel 2007

• Interview topic
• Ask which version the company uses

35
Multiplication Tables

• Exercise 1

36
Using Excel

• Formatting Your Workspace

37
Toolbars

• Customize your toolbars
• Whenever you find yourself using the same command
  again and again
• Add the command to your toolbar
• Learn the shortcut

38
Fin490.xla
Save all worksheets with the focus on cell A1

• .xla is an excel add-in
• Download fin490.xls from Compass

Fin 490 Format Menu
39
Fin490.xla

• On your own Computer
• .xla is saved as xla and placed in the xlstart
  subdirectory of your computer
• C\Program Files
• Microsoft Office
• Office 11
• Xlstart Fin490.xla

40
Fin490.xla

• In the classroom/lab
• .xla can be parked on your flash drive and
  opened whenever you work

41
Customizing Fin490.xla

• Open Fin490.xls
• AltF11 to open VBA
• Click on Modules/Headings

Font color is Indigo
' Keyboard Shortcut CtrlShiftT '
Selection.Font.Bold True Selection.Font.Colo
rIndex 55 With Selection.Interior
.ColorIndex 45 .Pattern xlSolid
End With
Interior color is light orange
42
Customizing Fin490.xla

• In Fin490.xls
• Pick your colors and edit the VBA
• Test by using CtrlShiftT
• Format/ Sheet/ Hide
• Add Modified by --- and date
• Save as Fin490.xla

.xla is an addIn
43
Fin490.xla

• Fin490.xla is an add In
• When you open Fin490.xls you have a spreadsheet
  that does stuff
• When you open Fin490.xla you have an add in that
  does the same stuff but you see no spreadsheets

44
Financial Modeling

• Getting Started