The course is presented as a case study course.
First, we start from building a short version Profit & Loss report based on system data. In this case, we teach students how to avoid hard-coded numbers and hard-linked sources in the report. Instead, we deploy some advanced formulas that make the report dynamic.
Second skill is about using pivot tables in reporting. It makes reporting much easier by slicing and dicing. This skill can differentiate rookies and gurus.
Third, we fine-tune the model by upgrading it into a dynamic model by including the assumption page, data page, reporting page, dashboard, and proof check.
Fourth, once reports are completed, we need to generate region-specific reports to distribute to regional managers. We can automate the whole report generating process through a programming language in Excel called VBA (Visual Basic Application).
Last, once we receive revised reports from regional managers, we need to consolidate them into the corporate P&L report. We teach methods at all levels: basic, intermediate and advanced.
Course Key Concept: VBA, Pivot table, Consolidate, Dynamic formulas, Dashboard, Validation.
Prerequisites
Basic Excel knowledge; be able to open one Excel file and add a new sheet, etc.
Learning Objective
- Recognize importance of building robust models by using dynamic formulas instead of hard-links.
- Discover benefits of using Pivot Tables in financial modeling.
- Identify 5 crucial elements in financial modeling.
- Discover how to build your own “robot” generating set reports by using macro.
- Explore multiple ways of consolidating financial reports: from basic, to intermediate to advanced methods.
Last updated/reviewed: March 15, 2024
(0) Reviews
(306 rating)Lesson Questions and Answers0 Questions
INTRODUCTION AND OVERVIEW
- Introduction to Top 5 Excel Skills: From Excel Rookie to Excel Guru 0:27
- Instructor Introduction 1:32
- The Vicious Cycle and How to Break It 1:32
- The Virtuous Cycle 1:12
- Course Objectives 1:30
SKILLS
- Skill 1: Dynamic Formulas 9:46
- Skill 2: Beyond Formulas 11:05
- Skill 3: The 5 Magic Pages in Modeling 15:58
- Skill 4: Distribute Reports 10:00
- Skill 5.1-Consolidate Reports: Basic, Intermediate, to Advanced 2:26
- Skill 5.2-Consolidate Reports: Basic, Intermediate, to Advanced 3:15
- Skill 5.3-Consolidate Reports: Basic, Intermediate, to Advanced 2:47
- Skill 5.4-Consolidate Reports: Basic, Intermediate , to Advanced 8:46
Wrap Up
- Takeaways 0:36
- Next Course 0:31
CONTINUOUS PLAY
SUPPORTING MATERIAL
- Slides: Top 5 Excel Skills: From Excel Rookie to Excel Guru PDF
- Workbook: Session 1 - Top 5 Excel Skills - Practice XLSM
- Workbook: Session 1 - Top 5 Excel Skills - Answer XLSM
- Top 5 Excel Skills: From Excel Rookie to Excel Guru Glossary/ Index PDF
REVIEW AND TEST
- REVIEW QUESTIONS quiz
- FINAL EXAM exam
Ask the instructor a question about this lesson