Instructor for this course

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 Concept: 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 30, 2020


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

Education Provider Information

Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
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 Syllabus
  0:32Introduction to Excel Model Makeover: Reducing Prep Time from 1 Hour to 1 Minute
  1:09Recap: An Excel Model that did It All Wrong
  1:13Instructor Introduction
  1:24Comparison with Other Similar Courses
  0:37What You Will Get from This Course?
Business Case: DSO Model
  1:21What it takes to prepare the DSO file?
  4:55How was the old DSO file prepared?
  0:54How to calculate a 5-week DSO?
  1:04What makes a model great?
Excel Model Makeover
  1:43Modeling Overview
  1:23Model Design 1: Parameters
  1:17Model Design 2: Data Input
  8:09Model Design 3: Data Processing - Data Flow Map
  3:40Model Design 3: Data Processing - Assign a Period Range
  3:40Model Design 3: Data Processing - Rename a Location
  1:22Model Design 3: Data Processing - Sum by Location
  3:56Model Design 3: Data Processing - Calculate the 5-week Sales
  4:13Model Design 3: Data Processing - Get # of Weeks and # of Days
  0:55Model Design 3: Data Processing - Calculate the DSO
  7:51Model Design 3: Data Processing - Exclude a Project
  2:05Model Design 4: Data Output
  3:31Model Design 5: Automation
  6:21Model Design 6: Validation
Model Testing
  3:56Model Testing: Does it Reduce Prep Time from 1 Hr to 1 Min?
  0:30Next Course
  1:08:51Extreme Excel Modeling Makeover: Reduce reporting time from 1+ hour to 1 minute or less
  PDFSlides: Excel Model Makeover: Reducing Prep Time from 1 Hour to 1 Minute
  PDFExcel Model Makeover: Reducing Prep Time from 1 Hour to 1 Minute Glossary/ Index
  XLSXWorkbook: Session 7 - Sample file
  XLSXWorkbook: Session 7 - From 1 hour to 1 min (Practice)
  XLSMWorkbook: Session 7 - From 1 hour to 1 min (Answer)
  XLSXWorkbook: Project
  ZIPWorkbook: 10-ITD revenue