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: June 9, 2021

5 Reviews (16 ratings)Reviews

4
Anonymous Author
Good course that provides many tips and tricks that cause common error in data models.
4
Anonymous Author
Very good course. I would recommend learning about Data Query functions first if you are not familiar with them. I was not and was a little lost when he spoke about them. I will now go back and learn about them and possible watch this one again to tie it all together.
3
Member's Profile
Course should have been longer and more detailed. It presented the topics briefly and followed up with examples that were not detailed enough to allow the concepts to be fully understood.
5
Anonymous Author
This is an excellent course on Excel modelling - the learnings from this course can be implemented right away in your current models at work.
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