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: January 16, 2023
3 Reviews (21 ratings)

Reviews

5
Member's Profile
I found the editable pivot table interesting, but I am not certain I can imagine it as a tool for my organization, but I will think on it. There were many other nice tips and tricks. If the attendee is not familiar with power query they may want to take a course on that first. However, if you are familiar with the query and pivot tables the course is nice because it doesn't trouble with material that is too basic.
5
Anonymous Author
This instructor is quite good and gives great screenshots for reference. I learned several new ideas today that I didn't even know existed in Excel. Well done!
5
Anonymous Author
This course help me to run budgeting and presentation to the board. Lee thanks for sharing these fantastic knowledge, following your session am an expert.
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 series courses by Lenny Wu.
Excel Magic 1: Building Dynamic Formulas
Excel Magic 2: Building Your Report Generation "Bot"
Excel Magic 3: Automating Data Entry with Data Feeds
Excel Magic 4: Ultimate API Reporting in Excel
Excel Magic 5: Building Interactive Budgeting Model in Excel
Excel Magic 6: Excel's 7 Languages
Excel Magic 7: Creating Excel Charts like a PRO
Excel Magic 8: Transforming a Pro-forma into a Dashboard
Excel Magic 9: Building a Project Tracking “Gantt” Chart

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 Magic 5-Building Interactive Budgeting in Excel Model0:36
  Forecasting and Budgeting Environment1:38
  Forecasting and Budgeting Challenges1:43
  Forecasting and Budgeting Challenges Cont'd1:30
  Instructor Introduction 1:14
  Comparison with Other Similar Courses 1:23
  What You Will Get from This Course? 0:36
Solution to Challenge No. 1
  Building an interactive budgeting model3:00
Solution to Challenge No. 2
  Building a PivotTable report3:17
Solution to Challenge No. 3
  Building an “Editable” PivotTable-Part 12:32
  Building an “Editable” PivotTable-Part 25:30
  Building an “Editable” PivotTable-Part 32:52
  Building an “Editable” PivotTable-Part 43:45
Solution to Challenge No. 4
  Reporting from System using PivotTable6:48
Solution to Challenge No. 5
  Consolidating Actuals and Budgets-Part 11:48
  Consolidating Actuals and Budgets-Part 25:32
  Consolidating Actuals and Budgets-Part 34:53
Solution to Challenge No. 6
  Building Integrated Presentation Slides3:10
CONCLUSION
  Takeaways 1:30
  Next Course & Q&A0:25
CONTINUOUS PLAY
  Excel Magic 5: Building Interactive Budgeting Model in Excel53:42
SUPPORTING MATERIAL
  Slides: Excel Magic 5: Building Interactive Budgeting Model in ExcelPDF
  Excel Magic 5: Building Interactive Budgeting Model in Excel Glossary/ IndexPDF
  Workbook: Excel Magic 5: Building Interactive Budgeting Model in ExcelXLSM
  Workbook: Excel Magic 5: Building Interactive Budgeting Model in ExcelXLXS
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam