Top 5 Excel skills: from Excel rookie to Excel guru
The course is presented as a case study course
Office Productivity,Microsoft Excel
Lectures -9
Resources -2
Duration -58 mins
30-days Money-Back Guarantee
Get your team access to 10000+ top Tutorials Point courses anytime, anywhere.
Course Description
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 Search Keywords
- VBA
- Pivot table
- Consolidate
- Dynamic formulas
- Dashboard
validation
Goals
What will you learn in this course:
- 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 consolidate financial reports: from basic, to intermediate to advanced methods
Prerequisites
What are the prerequisites for this course?
- Basic Excel knowledge
- Example: be able to open one Excel file and add a new sheet, etc.
Curriculum
Check out the detailed breakdown of what’s inside the course
Introduction
6 Lectures
- Dynamic Formulas: No hard-links 09:46 09:46
- Beyond Formulas: Pivot Tables 11:05 11:05
- The 5 Magic Pages in Modeling 15:58 15:58
- Macro 10:04 10:04
- Consolidate reports: Basic to Advanced 02:31 02:31
- Consolidate Reports: Basic To Advanced Part-II 08:46 08:46
Supporting Materials
2 Lectures
Instructor Details
Lenny Wu
Lenny Wu was a Lecturer at Tsinghua University in Beijing, China. He also sat as an Examiner for the International English Language Testing System (or IELTS) in the 1990s. On top of these, he is an exam trainer for various high profile exams, such as GRE (or Graduate Record Examination), GMAT (or Graduate Management Admission Test), TOEFL (or Test of English as a Foreign Language), and math contests such as Euler, Gauss and Pascal. He has published books on test taking strategies.
Lenny received his MBA, Finance from The George Washington University in Washington, DC, USA, and currently is a Senior Financial Analyst with an international engineering company in Vancouver, BC, Canada.
Lenny's motto at work is "Don’t work hard, work smart". Lenny has passion in teaching how to build your own "robot" into everyday office work. He has over over 22 years working with Microsoft Excel, and he is a modeling expert in Power Query, Power Pivot, and Power BI.
Lenny is a big proponent of OCR (One-click Reporting), and SSR (Self-service Reporting).
Course Certificate
Use your certificate to make a career change or to advance in your current career.
Our students work
with the Best
Related Video Courses
View MoreAnnual Membership
Become a valued member of Tutorials Point and enjoy unlimited access to our vast library of top-rated Video Courses
Subscribe nowOnline Certifications
Master prominent technologies at full length and become a valued certified professional.
Explore Now