You are previewing this course
Each course is broken down into short video tutorials taught by award-winning instructors. To access the entire course:
Login Redeem a voucher
Financial Modeling Basics
What is Best Practice?
Introduction and the semantics of what constitutes "best" in "best practice". We consider a good model has four key attributes: Consistency, Robustness, Flexibility, and Transparency.
An explanation of the reason models should be consistent, and key elements of a workbook that should be consistent.
An explanation of why models should be materially free from error.
User should consider what inputs should be variable and how they should be able to vary.
Example of methods to control how inputs can be varied.
An explanation of why models must be clear, concise, and fit for the purpose of key decision makers. Modelers should consider the logical flow of a model at the outset as well.
Separation of Inputs + Calculations
How to design your model so that it separates the inputs and calculations.
Go over the elements that should be incorporated into a workbook.
A summary of Keyboard Shortcuts.
Layout tips can assist in building a financial model in Excel which is easy to navigate through and understand.
Layout tips for updating workbook names, adding hyperlinks, dates and freezing panes.
General tips and options to change in Excel when laying out a typical worksheet in a financial model.
Layout Tips for using the RANDBETWEEN function to generate random numbers for testing and labelling units.
Formatting tips to improve the look and keep the layout of the template consistent.
Highlight the difference between formats and styles in Excel, why they are important and when to apply them.
Formats vs. Styles Part 2
How to import styles into a workbook.
It is important how numerical data is presented in Excel. Learn how to effectively use number formatting.
Understand the coding in Custom Number Formatting.
How to use Custom Number Formatting.
How to use Custom Number Formatting, continued.
When to use conditional formatting, and how to effectively apply it.
How to use Work in Progress (WIP) Flagging.
How to effectively assign and use range names in an Excel worksheet.
How to use Range Names in a formula.
How to use Range Names in a formula, continued.
When and where to use hyperlinks to aid end user navigation.
Understand how to restrict what end users type into a cell using Data Validation, with examples of lists and whole numbers.
Understand how to restrict what end users type into a cell using Data Validation, with examples of text length and custom validation.
Highlight the situations where checks are useful, and how the different checks fit into 3 categories. Error Checks, Sensitivity Checks, and Alert Checks.
How to create a prima facie error check.
How to create a sensitivity check and where to use one.
The check that checks for everything else sensitivity and error checks don't.
Viewing, Linking, Protection, and Version Control Tips
Printing and Viewing Tips
The steps to take to ensure that the model looks as intended when printed.
The proper habits to adopt when linking cells together, and the reasons why.
The proper steps to take to protect the integrity of the model.
The naming conventions to adopt and the habits that should be practiced.
An overview of the key functions used for financial modeling in Excel.
Illustration and issues with the IF function.
Illustration and issues with the SUM function.
Illustration and issues with the SUMIF function.
Illustration and issues with the SUMIFS function.
Illustration and issues with the SUMPRODUCT function.
An advanced example of the use of SUMPRODUCT.
Don't Use HLOOKUP and VLOOKUP
Why not to use HLOOKUP and VLOOKUP functions.
Illustrations of the use of the INDEX function.
Illustration and issues with the MATCH function.
Illustrations of the use of INDEX MATCH.
Illustration and issues with the LOOKUP function.
Illustration and issues with the LOOKUP function continued.
Illustration and issues with the OFFSET function.
Illustration and issues with the OFFSET function continued.
A final look at the illustration and issues with the OFFSET function.
Illustrations and issues with the MOD function.
Illustrations and issues with the EOMONTH function.
Illustration and issues with MAX and MIN.
An overview of what we have covered in the Financial Modeling Basics course.
Sorry, it looks like there are no lessons that match your filter.