In this video we explore the many ways to document model assumptions from a technical perspective. We delve into the various Excel tools commonly used to document the inputs and assumptions; in-cell comments, footnotes, hyperlinks, hard-coded and linked dynamic text.
Video length: 9 min 20 sec
Templates
Download the Excel template for this video. Follow along with the video demonstration, and save your model. Note that the models in this module are not required uploads for this course, however, you are welcome to submit them if you’d like us to check you’re on the right track.
Additional Resources
If you signed up for the Online Financial Modelling Bundle then you will be receiving either a hard copy or ebook version of “Using Excel for Business and Financial Modelling” as well as “Financial Modeling in Excel for Dummies” by Danielle Stein Fairhurst included in your registration. If you’d like to upgrade to the bundle and complete all three courses, please contact us and we can upgrade your registration! Note that these books act as handy study companions to this course for additional supplementary reading and exercises. Purchase of the books is recommended, but not required to complete the course.
Transcript
Download the Transcript for this video.
Please note:
To move forward to the next video, you must mark this video complete. To mark the video complete, please watch the video or if you would like to skip the video, just click on the video time line right at the end to enable the ‘Mark Video Complete’ button. See instructions on the Student Dashboard here for more information.
In this video: Continuing on from Logical Nested Functions Part 1, we perform more practical exercises which apply the IF, AND and OR statements to real-life examples:
- Nesting IF statements to calculate volume pricing breaks
- Using an OR statement to identify account codes in a pre-defined range
- Nesting an AND function to show values within a Financial Year
Video length: 10 min 30 sec
Templates
Download the Excel templates for this video from Logical Nested Functions Part 1. Follow along with the video demonstration, and save your model. Note that the models in this module are not required uploads for this course, however, you are welcome to submit them if you’d like us to check you’re on the right track.
Additional Resources
See Logical Nested Functions Part 1.
Transcript
Download the Transcript for this video.
Please note:
To move forward to the next video, you must mark this video complete. To mark the video complete, please watch the video or if you would like to skip the video, just click on the video time line right at the end to enable the ‘Mark Video Complete’ button. See instructions on the Student Dashboard here for more information.
In this video we investigate logical functions such as IF, AND and OR which are commonly used in financial modelling, and are particularly useful when multiple functions are nested together in a formula. Nested formulas can get long and complicated very quickly, so in this section we look firstly at creating a simple logical function, and then cover some techniques for building nested formulas to ensure the syntax remains accurate (i.e. that the brackets and commas are in the correct place!)
This section contains a number of practical exercises:
- Using a basic IF statement to accept or reject the deal in a valuation example
- Nesting IF statements to evaluate maximum and minimum temperature ranges
Video length: 11 min
Templates
Download the Excel template for this video. Follow along with the video demonstration, and save your model. Note that the models in this module are not required uploads for this course, however, you are welcome to submit them if you’d like us to check you’re on the right track. You may also download the completed version of the exercises at the end if you wish to compare your work.
Additional Resources
If you signed up for the Online Financial Modelling Bundle then you will be receiving either a hard copy or ebook version of “Using Excel for Business and Financial Modelling” as well as “Financial Modeling in Excel for Dummies” by Danielle Stein Fairhurst included in your registration. If you’d like to upgrade to the bundle and complete all three courses, please contact us and we can upgrade your registration! Note that these books act as handy study companions to this course for additional supplementary reading and exercises. Purchase of the books is recommended, but not required to complete the course.
Transcript
Download the Transcript for this video.
Please note:
To move forward to the next video, you must mark this video complete. To mark the video complete, please watch the video or if you would like to skip the video, just click on the video time line right at the end to enable the ‘Mark Video Complete’ button. See instructions on the Student Dashboard here for more information.
In this video we apply the financial modelling best practice techniques we have learned so far, using links and correct cell referencing, we create a very simple one-page financial model together. We have the annual salaries for staff members forecast to work on the project, assuming each person works 260 days in the year (net of leave, sick days etc.) and assuming each person must work 60 days on a project, we can calculate the total staff cost of this project.
Video length: 4 min 30 sec
Templates
Download the Excel template for this video. Follow along with the video demonstration, and save your model. Note that the models in this module are not required uploads for this course, however, you are welcome to submit them if you’d like us to check you’re on the right track.
Transcript
Download the Transcript for this video.
Please note:
To move forward to the next video, you must mark this video complete. To mark the video complete, please watch the video or if you would like to skip the video, just click on the video time line right at the end to enable the ‘Mark Video Complete’ button. See instructions on the Student Dashboard here for more information.
In this video, we are given an introduction to absolute and relative cell referencing; (i.e. =$A$1 vs =A1), a basic yet critical skill for financial modelling best practice.
Video length: 3 min 40 sec
Templates
Download the Excel template for this video. Follow along with the video demonstration, and save your model. Note that the models in this module are not required uploads for this course, however, you are welcome to submit them if you’d like us to check you’re on the right track.
Additional Resources
If you signed up for the Online Financial Modelling Bundle then you will be receiving either a hard copy or ebook version of “Using Excel for Business and Financial Modelling” as well as “Financial Modeling in Excel for Dummies” by Danielle Stein Fairhurst included in your registration. If you’d like to upgrade to the bundle and complete all three courses, please contact us and we can upgrade your registration! Note that these books act as handy study companions to this course for additional supplementary reading and exercises. Purchase of the books is recommended, but not required to complete the course.
Transcript
Download the Transcript for this video.
Please note:
To move forward to the next video, you must mark this video complete. To mark the video complete, please watch the video or if you would like to skip the video, just click on the video time line right at the end to enable the ‘Mark Video Complete’ button. See instructions on the Student Dashboard here for more information.
In this video, we look at a simple existing Profit and Loss report. We create a summary of the P&L Statement by linking between sheets and then cut and paste input assumptions to relocate them together on one assumptions page.
Video length: 8 min
Templates
Download the Excel template for this video. Follow along with the video demonstration, and save your model. Note that the models in this module are not required uploads for this course, however, you are welcome to submit them if you’d like us to check you’re on the right track. You may also download the completed version of the exercises at the end if you wish to compare your work.
Additional Resources
If you signed up for the Online Financial Modelling Bundle then you will be receiving either a hard copy or ebook version of “Using Excel for Business and Financial Modelling” as well as “Financial Modeling in Excel for Dummies” by Danielle Stein Fairhurst included in your registration. If you’d like to upgrade to the bundle and complete all three courses, please contact us and we can upgrade your registration! Note that these books act as handy study companions to this course for additional supplementary reading and exercises. Purchase of the books is recommended, but not required to complete the course.
Transcript
Download the Transcript for this video.
Please note:
To move forward to the next video, you must mark this video complete. To mark the video complete, please watch the video or if you would like to skip the video, just click on the video time line right at the end to enable the ‘Mark Video Complete’ button. See instructions on the Student Dashboard here for more information.
