Instructor for this course
more

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

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

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 Syllabus
INTRODUCTION AND OVERVIEW
  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?
Conclusion
  1:12Takeaways
  0:30Next Course
CONTINUOUS PLAY
  1:08:51Extreme Excel Modeling Makeover: Reduce reporting time from 1+ hour to 1 minute or less
SUPPORTING MATERIAL
  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
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM