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: August 22, 2023
9 Reviews (44 ratings)

Reviews

3
Member's Profile
Brushes over power query topics at a surface level. Would have liked to see this specific to just excel so there was a little more depth. Overall took away very little, but it was a free credit.
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.
4
Member's Profile
Good course but it was more advanced than I expected. Need to know a bit about Power Query and pivot tables in order to get the most out of this.
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
Great course. Matches my industry experiences with data management. Very easy to follow and practical.
4
Anonymous Author
Good course that provides many tips and tricks that cause common error in data models.
5
Anonymous Author
Excellent overview of construction data models and a good peek into Power Query
5
Anonymous Author
Methodical way to set up models. Will take the Power Query session.
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 .
Instructor for this course
  Course Overview CPE
Course Syllabus
INTRODUCTION AND OVERVIEW
  Introduction to Excel Modeling 101: 10 Dos and Don'ts0:32
  Wrong Model1:25
  Instructor Introduction 1:23
  Other Similar Courses1:30
  What you will get from this course?0:43
Excel Modeling
  Excel Modeling1:14
  What makes a great model2:02
  Business Case: How to calculate a 5-week DSO?1:00
  10 Dos and Dont's - No. 15:53
  10 Dos and Dont's - No. 25:43
  10 Dos and Dont's - No. 34:11
  10 Dos and Dont's - No. 44:36
  10 Dos and Dont's - No. 54:10
  10 Dos and Dont's - No. 62:17
  10 Dos and Dont's - No. 72:08
  10 Dos and Dont's - No. 81:46
  10 Dos and Dont's - No. 93:35
  10 Dos and Dont's - No. 101:58
Conclusion
  Takeaways 1:40
  Next Course0:28
CONTINUOUS PLAY
  Excel Modeling 101: 10 Dos and Don'ts48:12
SUPPORTING MATERIAL
  Slides: Excel Modeling 101: 10 Dos and Don'tsPDF
  Excel Modeling 101: 10 Dos and Don'ts Glossary/ IndexPDF
  Workbook: Session 6 - Sample fileXLSX
  Workbook: Session 6 - Modeling 101 (Answer)XLSX
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam