Master Reporting Automation with Google Sheets

Master Reporting Automation with Google Sheets

6 Hours

$28.99 CAD$116.00 CAD

You save 75%

108 Lessons (6h)

Your First Program
Preview

Introduction
Introduction5:01
Why Google Sheets, not Excel?3:00
Quick Example: How to Efficiently Automate Reporting1:52
Overview of Course Projects2:25
Learn and Practice the Key Formulas
Introduction1:10
Key Formula Combination - INDEX & MATCH7:02
Determining Year with TEXT2:07
Smart use of IF's - Dynamic SUMIF and AVERAGEIF7:51
Use of COUNTIF(S)3:38
Identify ROW & COLUMN Numbers2:14
RANK the Data1:52
Avoid Errors with IFERROR2:40
Improve Calculation Accuracy with AVERAGE.WEIGHTED2:20
Quiz 1: Key Formula Quiz
Learn and Practice the Key Formulas: Advanced Section
Introduction0:39
Pulling Data with INDIRECT & ADDRESS2:41
Use OFFSET for Dynamic Calculations4:34
IMPORTRANGE with INDEX & MATCH5:07
Advanced Formula: QUERY2:58
Advanced Formula: QUERY 23:44
Section Recap1:08
Quiz 2: Key Formula 2 Quiz
Putting Together the Project
Introduction2:43
Mapping Down the Metrics11:02
Defining the Week Periods and Week References3:59
Connecting the Formulas with INDEX & MATCH3:28
Setting Up Budget Connections and Calculations8:03
Formatting of Week Numbers1:53
Visual Formatting8:48
Quiz 3: Project Mapping Quiz
Adding the Forecasts
Introduction0:42
Defining Sheet Structure5:01
Setting Up Forecasting Formulas8:57
Advanced: Connecting Stable Metrics to Dataset11:48
Linking Targets with Actuals6:51
Defining Current Week through TODAY Formula3:09
Target Conversion from Weekly to Monthly5:28
Comparison between Targets and Budgets6:04
Notification for Reconciliation with Budgets8:40
Sections Recap1:19
Quiz 4: Forecasting Quiz
Building the Comparison Between Actuals and Forecasts
Introduction0:40
Defining Sheet Structure4:11
Connecting the Formulas2:48
Not Showing Data for Unpassed Weeks2:13
Calculating the Variances2:19
Conditional Formatting for Variances3:02
Conditional Color Notifications7:16
Final Touches1:25
Quiz 5: Comparison Building Quiz
Designing the Landing Page
Introduction0:27
What to Include in the Cover tab?7:00
Table of Contents3:05
Linking Key Information2:48
Use Your Brand Colours5:15
Section Recap1:32
Quiz 6: Landing Page Quiz
Reconciling Weekly Actuals with Monthly Budgets
Introduction1:03
Defining Sheet Structure5:57
Connecting the Data to the Tab5:16
Color Coding for Weekly Trends2:53
Color Coding for Monthly Budgets10:00
Final Touches1:39
Quiz 7: Actuals vs Budget Quiz
Setting Up Competitor and Market Size Tracking
Introduction0:56
Defining Sheet Structure1:39
Creating the Framework for Competitor Tracking3:08
Adding Formulas to Framework5:35
Estimating Competitor and Market Size10:27
Summary of Key Metrics8:34
Final Touches7:10
Connection with Other Tabs8:23
Sections Recap1:36
Quiz 8: Market Sizing Quiz
Professional and Effective Data Visualisation
Introduction1:41
Intro to the First Graph1:09
Data Visualisation Principles2:11
Graph I: Key Actuals with Targets17:55
Graph II: Monthly Actuals with Budgeted Forecasts12:16
Dynamic Table: Data Layout for Competitor Tracking10:57
Dynamic Table: Ranking for Competitor Tracking6:02
Graph III: Tracking Weekly Performance over Monthly Budget10:31
Final Touches0:47
Section Recap1:51
Quiz 9: Data Visualisation Quiz
Creating Dynamic and Reliable Data Connections
Introduction0:41
Status for Data Dump6:08
Dynamic Budget Connection with IMPORTRANGE4:15
Dynamic Budget Connection with QUERY4:13
Final Touches3:45
Quiz 10: Data Connections Quiz
Wrapping Up the Project
Introduction0:42
Adding Instructions1:39
Reviewing Links in Cover tab1:09
Final Touches on Whole File3:17
Protecting the Sheets3:29
Recap of Course Project1:51
Quiz 11: Project Wrap-Up Quiz
Further Development Tips and Tricks
Introduction0:49
Automating Data Dump through a Script2:37
Creating Multi-Level Reporting System3:15
Optimise Spreadsheet Performance1:40
Workaround for IMPORTRANGE Internal Error2:13
Manage Reporting Files through Scripts2:07
Section Recap1:07
Quiz 12: Tips and Tricks Quiz
Conclusion
Conclusion2:16
Bonus Lecture3:15

DescriptionInstructorImportant DetailsRelated Products

Solve Any Business Modeling Task with 6 Hours of Content on Effective Data Visualization

Mariel AimMariel Aim | Head of Business Analytics in a High-Growth Technology Firm 4.7/5 Instructor Rating: ★ ★ ★ ★ ★ ★
Mariel Aim works for the European fastest growing technology firm Bolt as the Head of Business Analytics where she's responsible for the whole business analytics and financial planning domain of a business vertical with over €3B valuation. Prior, she has a background in consulting (specifically M& A) and holds an MBA in Economics with a sub-focus on data science.

Description

All the work you're doing in a repetitive manner in Google Sheets or Excel could be automated. It requires no coding skills, add-ons, or special tools - all you need to know is how to execute advanced formula combinations that will do all the automation for you. This course focuses on teaching you the right skill set, so you could solve any business modeling task you might ever experience. You will learn the key formulas, practice them, build a complex but rewarding project, and then try to solve the challenges on your own. The course is designed to give you advanced-level skills that you will feel comfortable executing later in your own work.

4.7/5 average rating: ★ ★ ★ ★ ★ ★

Access 108 lectures & 6 hours of content 24/7
Make your files look professional & easy-to-track
Create formula combinations that will save your hours of time
Minimize manual reporting & focus on value-creating tasks
Learn tips & tricks to effective data visualization
Create an automated business forecasting framework that works

Specs

Important Details

Length of time users can access this course: lifetime
Access options: desktop & mobile
Redemption deadline: redeem your code within 30 days of purchase
Experience level required: beginner
Have questions on how digital purchases work? Learn more here

Requirements

Google account

Terms

Unredeemed licenses can be returned for store credit within 30 days of purchase. Once your license is redeemed, all sales are final.

Your Cart

Your cart is empty. Continue Shopping!

Processing order...