Instructor for this course
more

The course is presented in four parts.

First, brief introduction to what is Excel modeling and how to make an Excel model great:

  • Accuracy
  • Simplicity
  • Robustness

Second part brings a not-so-great Excel model as a business case, and introduces a financial report that is supposed to calculated the DSO (Days Sales Outstanding) measure. By examination, this course demonstrates to the audience 10 DONTS in Excel modeling, including:

  • Do NOT copy/paste data multiple files and stack them up
  • Do NOT leave comparable data in different columns
  • Do NOT process data “one at a time”

Third, we demonstrate how to correct the problems and bring up the list of the 10 DOS, including:

  • Connect or link to source data rather than copy and paste
  • Keep comparable data in the same column
  • Keep related data processing in “one” table!

Last, the course culminates in summarizing the 10 Dos and Don’ts into 4 categories:

  • Data source
  • Data transformation
  • Formulas
  • Validation

Course Key Concepts: Power Query, Excel modeling, DSO, Group by, Meta-data, Parameters, Validation.

Learning Objectives

  • Discover to establish instant connection to multiple data sources rather than copy/paste and stack-up.
  • Explore how to structure a table with related data in 1 column for easy pivoting.
  • Explore how to build uniform formulas in Excel models to avoid errors.
  • Recognize effective ways of keeping source data, entry data, and formulas all separate in models.
  • Identify and understand how Power Query can help prevent dragging processed data from table to table.
Last updated/reviewed: September 27, 2020

1 Review (5 ratings)Reviews

5
Anonymous Author
Excellent overview of construction data models and a good peek into Power Query

Prerequisites

Course Complexity: Foundational

Basic Excel and Power Query knowledge.
Example: Be able to open one Excel file and connect to external data files, etc.

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 Modeling 101: 10 Dos and Don'ts
  1:25Wrong Model
  1:23Instructor Introduction
  1:30Other Similar Courses
  0:43What you will get from this course?
Excel Modeling
  1:14Excel Modeling
  2:02What makes a great model
  1:00Business Case: How to calculate a 5-week DSO?
  5:5310 Dos and Dont's - No. 1
  5:4310 Dos and Dont's - No. 2
  4:1110 Dos and Dont's - No. 3
  4:3610 Dos and Dont's - No. 4
  4:1010 Dos and Dont's - No. 5
  2:1710 Dos and Dont's - No. 6
  2:0810 Dos and Dont's - No. 7
  1:4610 Dos and Dont's - No. 8
  3:3510 Dos and Dont's - No. 9
  1:5810 Dos and Dont's - No. 10
Conclusion
  1:40Takeaways
  0:28Next Course
CONTINUOUS PLAY
  48:12Excel Modeling 101: 10 Dos and Don'ts
SUPPORTING MATERIAL
  PDFSlides: Excel Modeling 101: 10 Dos and Don'ts
  PDFExcel Modeling 101: 10 Dos and Don'ts Glossary/ Index
  XLSXWorkbook: Session 6 - Sample file
  XLSXWorkbook: Session 6 - Modeling 101 (Answer)
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM