Instructor for this course
more

The course is presented in five parts:

First, the course introduces a business case of an “accounting” model that unintentionally includes all the hurdles preventing fast data entry. The model takes a staff 1+ hour to update 1 vessel file, and what’s worse, it takes over 2 hours to generate up-to-date Status report for a fleet of 60 vessels! Next, the course will demonstrate how the author has overhauled the old model so that it:

  • Reduces the data entry and report time from 1 hour to 1 min, and
  • Reduces the integration time from 2 hours to 2 minutes!

Third, we draw a line between when to data entry and when not to. Generally speaking, we need to avoid data entry as much as possible, thanks to the technologies. However, if you do need data entry, only enter data at a minimum with the remaining filled in from data extracts. Fourth, we demonstrate the 7 Dos and Don’ts in data entry, reporting, and validations. Some of the best practices are:

  • Do NOT enter data in a summary table, making it hard to update
  • Do NOT enter invoice details in a cell note, resulting in double entry
  • Do NOT apply accounting debits or credits in entry form, which may confuse data entry clerks
  • Do NOT summarize on “moving targets”

Last, the course illustrates some advanced modeling skills on:

  • How to build validation and auditing checks?
  • How to “quickly” set up links to 60 vessel files?

Course Key Concepts: Data entry, Status report, Data extract, Pivot table, Debits and credits, Validation, Parameters, Conditional formulas, Conditional formatting.

Learning Objectives

  • Discover how to master the best practices of designing, modeling and validating an “accounting” model.
  • Recognize when to data entry and when not to.
  • Explore and understand 4 best practices in building a data entry model.
  • Identify 2 best practices in building an accounting summary model.
  • Discover methods to build validation and auditing functionality into your models.
Last updated/reviewed: November 8, 2020

1 Review (3 ratings)Reviews

4
Anonymous Author
A good example of how not to set up a spreadsheet. An additional comment - naming a range can be helpful vs A1:A15 in a formula.

Prerequisites

Course Complexity: Intermediate

Basic Excel knowledge
Example: be able to open one Excel file and connect to external data files, etc.
However it is recommended to take other Excel Model Makeover courses by Lenny Wu.
Excel Model Makeover: Reducing Prep Time from 1 Hour to 1 Minute

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:36Introduction to Excel Model Makeover 2: Reducing Data Entry Time from 2 hours to 2 minutes
  1:18Real Business Case: Charter Hire Overview
  1:10Instructor Introduction
  1:26How this course is compared with “other similar courses”?
  0:36What you will get from this course?
Data Entry Best Practices
  1:28Data Entry: The Basics
  3:28Data Entry Modeling Best Practices No. 1
  1:18Data Entry Modeling Best Practices No. 2
  2:53Data Entry Modeling Best Practices No. 3
  2:45Data Entry Modeling Best Practices No. 4
  2:23Data Entry Modeling Best Practices No. 5
  4:10Data Entry Modeling Best Practices No. 6
  2:58Data Entry Modeling Best Practices No. 7
Advanced Modeling Techniques
  3:21How to “quickly” set up links to 60 vessel files?
  2:13How to “flip” the signs for revenues or expenses as needed?
  3:44How to set up reconciliation checks?
Comparison of Old Model and Makeover Model
  6:07Test: How long does it take to enter one invoice for one vessel
  3:01Test: How long does it take to prepare summary for one vessel
  2:02Test: How long does it take to update Status Report for 60 vessels
  1:53Summary: Comparison of Old Model and Makeover Model
CONCLUSION
  1:31Takeaways
  0:29Next Course and Q&A
CONTINUOUS PLAY
  50:49Excel Model Makeover 2: Reducing Data Entry Time from 2 hours to 2 minutes
SUPPORTING MATERIAL
  PDFSlides: Excel Model Makeover 2: Reducing Data Entry Time from 2 hours to 2 minutes
  PDFExcel Model Makeover 2: Reducing Data Entry Time from 2 hours to 2 minutes Glossary/ Index
  XLSXWorkbook: Excel Model Makeover 2: Reducing Data Entry Time from 2 hours to 2 minutes
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM