In our final video for this course, we complete the last element of our model by adding a simple best case, base case, worst case scenario analysis to our model using a data validation drop-down box.
Video length: 10 min
Templates
Follow along with the video demonstration, and the written instructions. It is a requirement of this course that you submit this model for review on completion and your model will be checked for accuracy.
Please save your model after finishing this video, submit it to us for review in the final lesson of this course if you would like a certificate. Ensure that you have completed the quiz questions for each module to complete the requirements for this 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 return to our case study, and add two charts to draw meaning and add a visual element to our financial model.
Video length: 6 min
Templates
Open your case study you saved in the last module. Continue by following along with the video demonstration, and the written instructions. It is a requirement of this course that you submit this model for review on completion and your model will be checked for accuracy. Please save your model, and make sure that your numbers match those in the video before moving to the next one.
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.
This video contains three practical exercises to demonstrate how many conditional formatting options which can add extra meaning to an otherwise boring-looking financial model.
- Automatically highlighting large variances in reporting
- Graphically show the magnitude of values on a report using data bars
- Adding visual interest to a table of data using colour scales
Video length: 8 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. You may also download the completed version of the exercises at the end if you wish to compare your work.
Additional Resources
See the section on “Conditional Formatting” in Chapter 7, page 191 of Using Excel for Business and Financial Modelling
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 investigate how the Excel data validation tool can control what data can be entered into your model, reducing input error and making your model more user-friendly. We explore how to restrict entry to numbers instead of text, and then use a List to create drop-down boxes which can later be used to create scenario analysis in our financial model.
Video length: 6 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. 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 explore how to apply the three methods of protection to a financial model:
- Protect the file using a password, or read-only recommended
- Protect the structure of the model so that sheets cannot be hidden, or more importantly, unhidden
- Lock cells and protect the sheet so that cells containing important formulas cannot be overridden, and data entry can only be made into the input cells specified by the modeller
Video length: 9 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.
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.
