1 / 15

Excel Financial Modeling

Excel Financial Modeling. Day 1 Nick Crain. Students in this class…. Beginners Want introduction of excel functionality Really this is covered by the Training Team Intermediate Want to do analysis, instead of just calculations Want exposure to best practices Advanced

audra
Download Presentation

Excel Financial Modeling

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel Financial Modeling Day 1 Nick Crain

  2. Students in this class… • Beginners • Want introduction of excel functionality • Really this is covered by the Training Team • Intermediate • Want to do analysis, instead of just calculations • Want exposure to best practices • Advanced • Want exposure to industry practices and prep for Investment bank internship/job.

  3. Rough Schedule • Day 1 – Modeling Intro • Excel Shortcuts (this should be a review from the Training Team demos) • Excel Financial Functions • Characteristics of a good model • Day 2 – Analysis • Lets use the Model to Answer Questions • Solving Models • Sensitivity / Scenario Analysis • Day 3 – Advanced Topics (4 – 7 PM) • 4:00 – 5:30 Session • Macros & VBA for non-programmers • 5:30 – 7:00 Session • Macros & VBA for programmers (some experience)

  4. A Word About Shortcuts • Proficient Excel users don’t use the mouse. • Learning the keyboard shortcuts can reduce modeling time by 25% or more.

  5. Moving around the Page: • Ctrl + arrows: Move to end of “block” • Ctrl + PgUp/Down: Cycle though worksheets Filing In Values / Editing: • Ctrl + R: Fill in formulas to the Right • Ctrl + D: Fill in formulas downward • Shift + arrows: Select multiple cells • F2: Edit a cell (no mouse click) • F4: Anchor a value

  6. First Example • Bond Calculations: CBS Corp • Face Value: 100 • Coupon: 7.625%, Fixed • Non-callable • Maturity: 01/15/2016 • Yield 5.937% • What is the clean price? • What will you actually pay for the bond? • How Sensitive is this bond to interest rates?

  7. “Hard” Coding • Making choices about a models inputs or assumptions which are coded directly into the spreadsheet. • Speed vs. Flexibility

  8. Absolute and Relative Cell Referencing • Referencing takes inputs for a formula from another cell. If we copy, or move that formula, the reference comes with it. • Relative Reference: • The reference moves as we copy or move the formula. • Absolute Reference: • Indicated by $ in the formula • Will remain anchored to a particular column or cell. • We can also do this by Naming • Shortcut for this is F4

  9. The IF() Statement • IF (logical_test, value_if_true, value_if_false) • Can also be nested: • IF(logical_test, IF (logical_test, value_if_true, value_if_false), value_if_false) • Any more than two nested use a lookup function instead. • Useful for: • Avoiding Hard coding • Real Options Problems • Monte Carlo Simulation.

  10. The Scooter Problem • David-Harleyson Motorcycles is deciding whether to enter the Scooter market. • If you where an executive in this company, what would you want to know before making a decision to pursue the project?

  11. Things to keep in mind • Format spreadsheets to “tell your story”. • Put the results up front. • Use highlighting and colors to indicate logical divisions in the spreadsheet, but avoid a “Skittles” spreadsheet. • Use conditional formatting to highlight salient results. • Document your spreadsheet • Clearly separate out inputs and assumptions • Use Cell Comments • Use a model description worksheet (optional)

  12. Things to keep in mind • Error Avoidance / Debugging • Hard Code only when absolutely necessary. • Use Names, especially for input variables. • For calculations or data in a series (e.g. years), line up years from sheet to sheet. • When identity’s exist, use them to calculate a quantity twice as a check. • Check for errors using an example you know the answer for.

  13. Lookup() Functions • Lookup() • Vlookup(), Hlookup()

More Related