Master Reporting Automation with Google Sheets

Name: Master Reporting Automation with Google Sheets
Rating: 4.1 (840 reviews)

Learn How To to Build A Completely Automated Reporting Tool & Solve Any Business Modeling Task You Might Ever Experience

Created byMariel Aim

Last updated 6/2021

English

What you'll learn

Advanced Google Sheets / Excel formulas that help you to solve any business modeling task you might ever experience
How to make your files look professional and easy-to-track
Creating highly complex formula combinations that will save you hours of time from automating manual work
How to minimise manual reporting and focus on value-creating tasks
Analytical skills on how to approach competitor analytics and market size estimation
Tips and tricks to effective data visualisation
Create an automated business forecasting framework that works
How to build a complete reporting system in Google Sheets with maximum automation
Ideas how to combine Google Sheets with (Python) scripts
Secrets how to impress your colleagues with your exceptional Excel skills

Course content

14 sections • 97 lectures • 6h 37m total length

Introduction5:01
Automate business reporting with Google Sheets using formulas and Python scripts. Build scalable templates, charts, and forecasting to reduce manual work and errors.
Why Google Sheets, not Excel?3:00
Quick Example: How to Efficiently Automate Reporting1:52
Overview of Course Projects2:25
Your Feedback is Valuable0:59

Introduction1:10
Learn to build automated business reporting in Google Sheets with dynamic data extraction and scalable spreadsheets through a hands-on course.
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
Learn how the row and column functions return the current row and column numbers, how to convert a column letter to a number, and how to implement dynamic renumbering.
RANK the Data1:52
Avoid Errors with IFERROR2:40
Improve Calculation Accuracy with AVERAGE.WEIGHTED2:20
Key Formula Quiz

Introduction0:39
Pulling Data with INDIRECT & ADDRESS2:41
Learn to pull data with indirect and address in Google Sheets. Explore how to dynamically reference cells by constructing row, column, and sheet inputs; apply to project building.
Use OFFSET for Dynamic Calculations4:34
IMPORTRANGE with INDEX & MATCH5:07
Advanced Formula: QUERY2:58
Advanced Formula: QUERY 23:44
Import and filter months with negative profit using the QUERY function in Google Sheets, then transpose data to align metrics and reveal eight negative-profit months.
Section Recap1:06
Key Formula 2 Quiz

Introduction2:43
Mapping Down the Metrics11:02
Create a metrics-driven Google Sheets file organizing data into homes, customers, and financials (GMV, discounts, subsidies), then build ratios to track performance and cash flow.
Defining the Week Periods and Week References3:58
Connecting the Formulas with INDEX & MATCH3:28
Setting Up Budget Connections and Calculations8:03
Formatting of Week Numbers1:53
Learn to generate accurate week numbers using a counting formula starting from the first week of the year, and format dates as compact month abbreviations for clear dashboards.
Visual Formatting8:48
Format the dashboard by marking key metrics, adding borders, color-coded categories, and applying numbers formatting and conditional formatting for a clear, shareable report.
Project Mapping Quiz

Introduction0:42
Develop an automated forecasting workflow in Google Sheets that covers every weekly metric, with only two manual inputs and the rest auto or semi-automatic using prior weeks' data.
Defining Sheet Structure5:01
Create a tarp structure by duplicating a template, remove unnecessary formulas, and extend forecasting weeks from February into March 2020, aligning week references and data ranges.
Setting Up Forecasting Formulas8:57
Advanced: Connecting Stable Metrics to Dataset11:48
Combine ten formulas to automate forecasting in Google Sheets, averaging the last three weeks' actuals and dynamically referencing past weeks within the dataset.
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
Forecasting Quiz

Introduction0:27
What to Include in the Cover tab?7:00
Table of Contents3:05
Linking Key Information2:48
Learn to isolate the latest week data in Google Sheets by combining max with index match, handle data gaps, and maintain naming consistency across metrics.
Use Your Brand Colours5:15
Apply Airbnb’s pink brand color to the sheet, copy its hex, and align sections with white backgrounds and consistent borders, including color and conditional formatting.
Section Recap1:32
Landing Page Quiz

Introduction0:56
Defining Sheet Structure1:40
Creating the Framework for Competitor Tracking3:08
Adding Formulas to Framework5:35
Estimating Competitor and Market Size10:27
Learn to estimate competitor and market size in the accommodation sector using Booking's active rooms and average daily price. Build a Google Sheets framework to track weekly trends and growth.
Summary of Key Metrics8:34
Final Touches7:10
Connection with Other Tabs8:23
Sections Recap1:36
Market Sizing Quiz

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:03
Create a dynamic table to rank competitors for tracking trends in a graph, and compute interim data with formulas, formatting, and monthly updates.
Graph III: Tracking Weekly Performance over Monthly Budget10:31
Final Touches0:47
Section Recap1:51
Data Visualisation Quiz

Requirements

At least intermediate level Excel / Google Sheets skills
Google account to build the reporting solution along with the course

Description

Learn to solve any business modeling task you might ever experience!

Have you ever felt that you're regularly repeating some tasks in Google Sheets or Excel?

Have you ever thought about the time you would save if you would automate those tasks?

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. I don't want to teach you only to execute some certain format of automation, I want you to be able to think outside of a certain tool - to literally be able to solve anything in the reporting automation or business modeling area. Everything we're learning along the course (which is a lot, really!) is only the start for you. I promise that after you complete the whole course, you will have tons of ideas on how to make your current work more efficient and automated.

This is a very hands-on course. 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. Please note that this is not a beginner-level course by design and by any means. However beginners are welcomed to take the course in case you're prepared to learn a lot by yourself in parallel and make some extra effort while taking this course.

What you can look forward to in this course:

Learn highly advanced and complex formula combinations
Build scalable and automated reporting tools that don't break
Build files that don't require any manual intervention for keeping them working
Learn how to make your files look professional and easy-to-track
Work out automated and semi-automated business forecasting methodology
Build a framework for market size estimation and competitor tracking (with real examples!)
Create impressive charts
Receive a fully functional business reporting template
Learn tips & tricks for future development (like using scripts)
Complete a lot of exercises
Save hundreds of hours of time with only you being required to take this course

Who this course is for:

Experienced Financial Analysts, Financial Controllers, CFOs or Business Analysts interested in automating their company's reporting
Students who want to get a job in financial / business modelling or reporting area and wants to boost their chances by adding new skills to their resume
Business professionals, who want to increase the efficiency of their reporting work and focus more time on things that matter
People interested impressing their managers and co-workers with their exceptional skills

Master Reporting Automation with Google Sheets

What you'll learn

Explore related topics

Course content

Introduction5 lectures • 13min

Learn and Practice the Key Formulas9 lectures • 31min

Learn and Practice the Key Formulas: Advanced Section7 lectures • 21min

Putting Together the Project7 lectures • 40min

Adding the Forecasts10 lectures • 58min

Building the Comparison Between Actuals and Forecasts8 lectures • 24min

Designing the Landing Page6 lectures • 20min

Reconciling Weekly Actuals with Monthly Budgets6 lectures • 27min

Setting Up Competitor and Market Size Tracking9 lectures • 47min

Professional and Effective Data Visualisation10 lectures • 1hr 5min

Requirements

Description

Who this course is for: