Instructor for this course
more

The course is presented in three parts:

First, the course reveals the 3 environments forecasts and budgets are made and compare each one of them.

Next, the course presents 6 challenges in budget modeling and reporting. They include:

  • How to accommodate Operation Team’s need to work from Excel while all budgets are in the Planning system?
  • How to build an interactive budgeting model in Excel?
  • How to write into a PivotTable?

Last, we provide 6 solutions to the above 6 challenges in budget modeling and reporting. They include:

  • Building an interactive budgeting model that allows the Operation Team to make changes to forecasts and budgets and see the results instantaneously.
  • Building an “editable” PivotTable that allows Operation Team to put forecasts and budgets directly in the PivotTable report.
  • Consolidating Actuals, Forecasts and Budgets from different data sources by using Power Query.
  • Building integrated Slides from Excel Reports.

 

Course Key Concepts: Flat table, Cross table, Calculated field, Calculated item.

Learning Objectives

  • Discover and understand when and how to build an interactive budgeting model in Excel.
  • Identify reasons in building a PivotTable report vs a regular report with formulas.
  • Discover techniques on building an “editable” PivotTable.
  • Recognize and understand ways to report budgets from the System using a PivotTable.
  • Explore methods of consolidating Actuals and Budgets from multiple data sources.
Last updated/reviewed: February 05, 2021

Prerequisites

Course Complexity: Intermediate

Basic Excel knowledge
Excel Pivot Table: basics
Power Query: Ultimate Data Transformation
Example: be able to open one Excel file and connect to external data files, etc.
It is recommended to take other Excel Magic courses by Lenny Wu.
Excel Magic: Building Dynamic Formulas
Excel Magic 2: Building Your Own Report Generating “Bot”
Excel Magic 3: Automating Data Entry with Data Feeds
Excel Magic 4: Ultimate API Reporting in Excel

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 Magic 5-Building Interactive Budgeting in Excel Model
  1:38Forecasting and Budgeting Environment
  1:43Forecasting and Budgeting Challenges
  1:30Forecasting and Budgeting Challenges Cont'd
  1:14Instructor Introduction
  1:23Comparison with Other Similar Courses
  0:36What You Will Get from This Course?
Solution to Challenge No. 1
  3:00Building an interactive budgeting model
Solution to Challenge No. 2
  3:17Building a PivotTable report
Solution to Challenge No. 3
  2:32Building an “Editable” PivotTable-Part 1
  5:30Building an “Editable” PivotTable-Part 2
  2:52Building an “Editable” PivotTable-Part 3
  3:45Building an “Editable” PivotTable-Part 4
Solution to Challenge No. 4
  6:48Reporting from System using PivotTable
Solution to Challenge No. 5
  1:48Consolidating Actuals and Budgets-Part 1
  5:32Consolidating Actuals and Budgets-Part 2
  4:53Consolidating Actuals and Budgets-Part 3
Solution to Challenge No. 6
  3:10Building Integrated Presentation Slides
CONCLUSION
  1:30Takeaways
  0:25Next Course & Q&A
CONTINUOUS PLAY
  53:42Excel Magic 5: Building Interactive Budgeting Model in Excel
SUPPORTING MATERIAL
  PDFSlides: Excel Magic 5: Building Interactive Budgeting Model in Excel
  PDFExcel Magic 5: Building Interactive Budgeting Model in Excel Glossary/ Index
  XLSMWorkbook: Excel Magic 5: Building Interactive Budgeting Model in Excel
  XLXSWorkbook: Excel Magic 5: Building Interactive Budgeting Model in Excel
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM