Instructor for this course
more

In this comprehensive presentation, Excel expert David Ringstrom, CPA, demonstrates how many Excel functions and features can be used to create adaptable and easy-to-maintain budget spreadsheets. David explains how to separate inputs from calculations, build out a separate calculations spreadsheet, create both an operating and a cash flow budget, transform filtering tasks, preserve key formulas, and more.  

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He’ll draw to your attention any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.

Topics covered:

  • Understanding the use of the MONTH function to return the month portion of a date or month name.
  • Preserving key formulas using hide and protect features.
  • Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets.
  • Building formulas faster by way of the Use in Formula command.
  • Using range names to streamline formulas and bookmark key inputs within a workbook.
  • Understanding why it’s worthwhile to build out supporting schedules to break down calculations used in budgets.
  • Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
  • Building operating budgets quickly based on detailed supporting schedules that provide an audit trail.
  • Avoiding the complexity of nested IF statements with Excel’s CHOOSE function.
  • Accessing free downloadable budget templates that can be customized as needed.

Learning Objectives

  • Recognize the purpose of the Use In Formula command.
  • Discover what SUMIF returns if a match cannot be found.
  • Identify the data integrity improvement that the Table feature adds to VLOOKUP.
Last updated/reviewed: October 12, 2018

Included In Certifications

This course is included in the following Certification Programs:

17 CoursesCertified Excel Modeler

  1. Excel Efficiency: VLOOKUP
  2. Excel Efficiency: Logic Functions
  3. Excel Efficiency: Filtering and Formatting Data
  4. Excel Efficiency: Intermediate Pivot Tables
  5. Excel Efficiency: Auditing Spreadsheets
  6. Excel Efficiency: Minimizing Worksheet Errors
  7. Excel Efficiency: Workbook Links
  8. Excel Efficiency: What-If Analysis
  9. Excel Efficiency: Quick and Easy Financial Statements in Excel
  10. Excel Efficiency: Budget Spreadsheets
  11. Hands-On Excel: Waterfall Calculations
  12. Excel Efficiency: Table Feature
  13. Excel Efficiency: Intro to Macros Part 1
  14. Excel Efficiency: Intro to Macros Part 2
  15. Excel Efficiency: Excel Chart Speed Tips
  16. Excel Efficiency: Taming Large Spreadsheets
  17. Excel Efficiency: Internal Controls

5 Reviews (28 ratings)Reviews

4
Anonymous Author
I like the concept of this course. The course is long, had to pause the course and come back to it several times.
3
Anonymous Author
I like the ease of the way things were explained. I also like the way the course was structured.
5
Anonymous Author
This was very helpful. I have already started to use some of these tools into my daily use.
4
Member's Profile
Good use of the many techniques David has taught in his other lessons. Very useful.
5
Anonymous Author
This course was great. It taught me some useful things. Thank you very much.

Prerequisites

Course Complexity: Foundational

Program Prerequisites: Previous Experience with Excel Spreadsheets

Advance Preparation: None

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
  5:38Introduction to Excel Efficiency: Budget Spreadsheets
  6:32Create Range Names - Create from Selection
  8:46Create Range Names - Name Box
  8:43Table Feature
  7:05Use in Formula Feature
  9:01Supporting Schedule Formulas
  7:59Collections Ratio
  10:40Collections Ratio - MONTH Function
  7:28Incorrect Collections Amounts Approach
  9:39Collections Amounts - SUMIF Function
  8:21Protect Sheet - Supporting Schedules
  10:15VLOOKUP
CONTINUOUS PLAY
  1:40:07Excel Efficiency: Budget Spreadsheets
SUPPORTING MATERIALS
  PDFSlides: Excel Efficiency: Budget Spreadsheets
  PDFExcel Efficiency: Budget Spreadsheets Glossary/Index
  XLSBCash Flow Budget - Blank
  XLSXWorkbook: Excel Efficiency: Budget Spreadsheets
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM