This course is presented in four parts.

First, the course introduces a business case of a financial model that allegedly “did it all wrong”. The model requires over 30 steps and over 1 hour just to report on a simple 1-measure report. This becomes a great candidate for this Model Makeover course.

Second, the course lists what makes a model great and how to achieve them:

  • Accuracy
  • Simplicity
  • Robustness

Third, we illustrate the full skills of building the fully overhauled model that would only take 1 minute to generate the report with only 2 steps. The whole process includes:

  • Data sourcing
  • Data processing
  • Report generating
  • Automating, and
  • Validating

Last, the course evaluating the newly built model to determine how well it meets the requirements of a great Excel model.

Course Key Concepts: Power Query, Excel modeling, DSO, Parameters, Data input, Data processing, Data output, VBA, Validation.

Learning Objectives
  • Explore how to make master the full skills of designing, implementing and evaluating a financial model.
  • Recognize 3 elements to make a great financial model.
  • Discover how to build a dynamic connection to data files through Power Query.
  • Identify and control the financial model with pre-defined parameters in Power Query.
  • Discover the distinguish methods to append a new table to the bottom (as rows) or to the right (as columns).
Last updated/reviewed: August 5, 2023
2 Reviews (22 ratings)

Reviews

5
Anonymous Author
Lenny does a good job setting the foundation for a solid model to be accurate and efficient.
5
Anonymous Author
Very engaging and innovative. I need to take more courses from this instructor
Prerequisites
Course Complexity: Intermediate

Basic Excel and Power Query knowledge; Example: be able to open one Excel file and connect to external data files, etc;
Recommended prerequisite: Power Query (Part 1): Ultimate Data Transformation
Other Excel Model Makeover courses by Lenny Wu.
Excel Model Makeover 2: Reducing Data Entry from 2 Hours to 2 Minutes
Excel Model Makeover 3: Reducing Update Time from 3 Hours to 3 Minutes

Education Provider Information
Company: Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact: For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Questions and Answers1 Question
There are no questions.         Answers to Frequently Asked Questions (FAQs)
Member's Profile
Stephen Pruski
(Manager at UTHSCSA) | Nov 20, 2020
Instructor for this course
  Course Overview CPE
Course Syllabus
INTRODUCTION AND OVERVIEW
  Introduction to Excel Model Makeover: Reducing Prep Time from 1 Hour to 1 Minute0:32
  Recap: An Excel Model that did It All Wrong1:09
  Instructor Introduction 1:13
  Comparison with Other Similar Courses1:24
  What You Will Get from This Course?0:37
Business Case: DSO Model
  What it takes to prepare the DSO file?1:21
  How was the old DSO file prepared?4:55
  How to calculate a 5-week DSO?0:54
  What makes a model great?1:04
Excel Model Makeover
  Modeling Overview1:43
  Model Design 1: Parameters1:23
  Model Design 2: Data Input1:17
  Model Design 3: Data Processing - Data Flow Map8:09
  Model Design 3: Data Processing - Assign a Period Range3:40
  Model Design 3: Data Processing - Rename a Location3:40
  Model Design 3: Data Processing - Sum by Location1:22
  Model Design 3: Data Processing - Calculate the 5-week Sales3:56
  Model Design 3: Data Processing - Get # of Weeks and # of Days4:13
  Model Design 3: Data Processing - Calculate the DSO0:55
  Model Design 3: Data Processing - Exclude a Project7:51
  Model Design 4: Data Output2:05
  Model Design 5: Automation3:31
  Model Design 6: Validation6:21
Model Testing
  Model Testing: Does it Reduce Prep Time from 1 Hr to 1 Min?3:56
Conclusion
  Takeaways1:12
  Next Course 0:30
CONTINUOUS PLAY
  Extreme Excel Modeling Makeover: Reduce reporting time from 1+ hour to 1 minute or less1:08:51
SUPPORTING MATERIAL
  Slides: Excel Model Makeover: Reducing Prep Time from 1 Hour to 1 MinutePDF
  Excel Model Makeover: Reducing Prep Time from 1 Hour to 1 Minute Glossary/ IndexPDF
  Workbook: Session 7 - Sample fileXLSX
  Workbook: Session 7 - From 1 hour to 1 min (Practice)XLSX
  Workbook: Session 7 - From 1 hour to 1 min (Answer)XLSM
  Workbook: ProjectXLSX
  Workbook: 10-ITD revenue ZIP
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam