Master Reporting Automation with Google Sheets | Mariel Aim | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Master Reporting Automation with Google Sheets

teacher avatar Mariel Aim

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

96 Lessons (6h 34m)
    • 1. S1 L1 - Introduction to the Course

    • 2. S1 L2 - Why Google Sheets, not Excel?

    • 3. S1 L3 - Quick Example: How to Efficiently Automate Reporting

    • 4. S1 L4 - Overview of Course Projects

    • 5. S2 L1 - Learn and Practice the Key Formulas: Introduction

    • 6. S2 L2 - Key Formula Combination - INDEX & MATCH

    • 7. S2 L3 - Determining Year with TEXT

    • 8. S2 L4 - Smart use of IF's - Dynamic SUMIF and AVERAGEIF

    • 9. S2 L5 - Use of COUNTIF(S)

    • 10. S2 L6 - Identify ROW & COLUMN numbers

    • 11. S2 L7 - RANK the Data

    • 12. S2 L8 - Avoid Errors with IFERROR

    • 13. S2 L9 - Improve Calculation Accuracy with AVERAGE.WEIGHTED

    • 14. S3 L1 - Learn and Practice Key Formulas: Advanced Section

    • 15. S3 L2 - Pulling Data with INDIRECT & ADDRESS

    • 16. S3 L3 - Use OFFSET for Dynamic Calculations

    • 17. S3 L4 - IMPORTRANGE with INDEX & MATCH

    • 18. S3 L5 - Advanced Formula: QUERY

    • 19. S3 L6 - Advanced Formula: QUERY 2

    • 20. S3 L7 - Section Recap

    • 21. S4 L1 - Putting Together the Project

    • 22. S4 L2 - Mapping Down the Metrics

    • 23. S4 L3 - Defining the Week Periods and Week References

    • 24. S4 L4 - Connecting the Formulas with INDEX & MATCH

    • 25. S4 L5 - Setting Up Budget Connections and Calculations

    • 26. S4 L6 - Formatting of Week Numbers

    • 27. S4 L7 - Visual Formatting

    • 28. S5 L1 - Adding the Forecasts

    • 29. S5 L2 - Defining Sheet Structure

    • 30. S5 L3 - Setting Up Forecasting Formulas

    • 31. S5 L4 - Advanced: Connecting Stable Metrics to Dataset

    • 32. S5 L5 - Linking Targets with Actuals

    • 33. S5 L6 - Defining Current Week through TODAY Formula

    • 34. S5 L7 - Target Conversion from Weekly to Monthly

    • 35. S5 L8 - Comparison between Targets and Budgets

    • 36. S5 L9 - Notification for Reconciliation with Budgets

    • 37. S5 L10 - Sections Recap

    • 38. S6 L1 - Building the Comparison Between Actuals and Forecasts

    • 39. S6 L2 - Defining Sheet Structure

    • 40. S6 L3 - Connecting the Formulas

    • 41. S6 L4 - Not Showing Data for Unpassed Weeks

    • 42. S6 L5 - Calculating the Variances

    • 43. S6 L6 - Conditional Formatting for Variances

    • 44. S6 L7 - Conditional Color Notifications

    • 45. S6 L8 - Final Touches

    • 46. S7 L1 - Designing the Landing Page

    • 47. S7 L2 - What to Include in the Cover tab?

    • 48. S7 L3 - Table of Contents

    • 49. S7 L4 - Linking Key Information

    • 50. S7 L5 - Use Your Brand Colours

    • 51. S7 L6 - Section Recap

    • 52. S8 L1 - Reconciling Weekly Actuals with Monthly Budgets

    • 53. S8 L2 - Defining Sheet Structure

    • 54. S8 L3 - Connecting the Data to the Tab

    • 55. S8 L4 - Color Coding for Weekly Trends

    • 56. S8 L5 - Color Coding for Monthly Budgets

    • 57. S8 L6 - Final Touches

    • 58. S9 L1 - Setting Up Competitor and Market Size Tracking

    • 59. S9 L2 - Defining Sheet Structure

    • 60. S9 L3 - Creating the Framework for Competitor Tracking

    • 61. S9 L4 - Adding Formulas to Framework

    • 62. S9 L5 - Estimating Competitor and Market Size

    • 63. S9 L6 - Summary of Key Metrics

    • 64. S9 L7 - Final Touches

    • 65. S9 L8 - Connection with Other Tabs

    • 66. S9 L9 - Sections Recap

    • 67. S10 L1 - Professional and Effective Data Visualisation

    • 68. S10 L2 - Intro to the First Graph

    • 69. S10 L3 - Data Visualisation Principles

    • 70. S10 L4 - Graph I: Key Actuals with Targets

    • 71. S10 L5 - Graph II: Monthly Actuals with Budgeted Forecasts

    • 72. S10 L6 - Dynamic Table: Data Layout for Competitor Tracking

    • 73. S10 L7 - Dynamic Table: Ranking for Competitor Tracking

    • 74. S10 L8 - Graph III: Tracking Weekly Performance over Monthly Budget

    • 75. S10 L9 - Final Touches

    • 76. S10 L10 - Section Recap

    • 77. S11 L1 - Creating Dynamic and Reliable Data Connections

    • 78. S11 L2 - Status for Data Dump

    • 79. S11 L3 - Dynamic Budget Connection with IMPORTRANGE & TRANSPOSE

    • 80. S11 L4 - Dynamic Budget Connection with QUERY

    • 81. S11 L5 - Final Touches

    • 82. S12 L1 - Wrapping Up the Project

    • 83. S12 L2 - Adding Instructions

    • 84. S12 L3 - Reviewing Links in Cover tab

    • 85. S12 L4 - Final Touches on Whole File

    • 86. S12 L5 - Protecting the Sheets

    • 87. S12 L6 - Recap of Course Project

    • 88. S13 L1 - Further Development Tips and Tricks

    • 89. S13 L2 - Automating Data Dump through a Script

    • 90. S13 L3 - Creating Multi-Level Reporting System

    • 91. S13 L4 - Optimise Spreadsheet Performance

    • 92. S13 L5 - Workaround for IMPORTRANGE Internal Errors

    • 93. S13 L6 - Manage Reporting Files through Scripts

    • 94. S13 L7 - Section Recap

    • 95. S14 L1 - Conclusion

    • 96. S14 L2 - Bonus Lecture

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

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

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

Are there any course requirements or prerequisites?

  • At least intermediate level Excel / Google Sheets skills
  • Google account to build the reporting solution along with the 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

Meet Your Teacher

Teacher Profile Image

Mariel Aim


Class Ratings

Expectations Met?
  • 0%
  • Yes
  • 0%
  • Somewhat
  • 0%
  • Not really
  • 0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. S1 L1 - Introduction to the Course: In this course, I'm going to teach you how to automate all the business reporting you might have using Google Sheets. Whether it would be just monthly reporting to the management, weekly reporting, which requires a scalable and completely automated template for a hundreds of people's use or just a project for yourself to make your work more efficient. Hi, and welcome to the course mass reporting automation with Google Sheets. My name is my alive and I will be a lecture on discourse. Unfortunately, maturity of reporting the people doing excellent Google Sheets. It's very manual and requires repetitive work to be done over and over again on a regular basis. In a high majority of cases. That's because of lack of skills and not being aware about the fact that up to 99% of this work could be automated primarily through smart use of formulas, but also involving other related tooling such Python scripts. And that's why I have created this course. You can be the one automating all or Google Sheets or excellent work that you currently have done manually and can be the one leading the change in your company. This course focuses on teaching you with the right skill set. So you could solve any business modelling SaaS you might ever experience in the future. Synthetics you're going to learn in this course, and that's going to be limited only to report. It feels I don't want to feature to only execute some certain formative automation. I want it to be able to think outside of a certain suit or excites. We're going to learn how to structure will be recording file from scratch when C, During the data that we have, great impressive charts were a golf ball as methodology for guessing with the help of automation. And learn how to easily create the framework for a to tracking Bamaga sizes the mission. Not only are we learning how to read formulas, but we're also putting all our learnings to the actual pressure and utilizing the new skills to the maximum extent. So they were stick to us and you can keep all the simplest. Always come back to them when you have any data or have forgotten something. Let me put discourse it a context for you. You are the right person to take this course if you are either experienced financial analysts, financial controller, CFO, or business analysts interests in automating your company's reporting. Student who wants to get a job in financial modelling, business modelling or reporting area. And wants to boost your chances by adding new skills to the resume. Business professional who wants to increase the efficiency of reporting work and focus more time on things that matter. Or a person is pressing and pressing their managers and coworkers with their exceptional skills. And ideally, you use Excel or Google sheets already on a daily basis. If you're really motivated to learn a lot and create some x-ray effort than research along on your own. Then champing, the value we are going to create is enormous. 2. S1 L2 - Why Google Sheets, not Excel?: And other people have asked about why I'm using Google Sheets and Microsoft. Thanks. So I have worked with both of them quite extensively in the past. And by today, I prefer Google Sheets much for that microscopic. So both Microsoft Excel and Google sheets have quite similar core functionalities. Google Sheets was built from the ground up to be the Cloud-based alternative to Microsoft pixel. In recent years, it has become more and more popular with so many companies going overseas cloud based alternative from Microsoft x. So even though both of them have their ups and downs, let me tell you about six key reasons why Google Sheets is better than Exon. Number one, Google Sheets isn't Cloud, meaning that you're gonna work with their documents from any computer, tablet, or phone wherever you are. Number two, it says that she uses real time, meaning you won't lose your work done when excellent crashes or your computer shuts down. Whenever this happens, your work is already saved. Number three, you can build much more heavier fast and Google sheets. Then the next year, I remember when I was working in financial audit and my first crash all the time as a witch, I just lost my work quite frequently or it just had to wait for all the calculations to be processed by axial. And I just lost time. I had no more worrying about that in Google Sheets. I worked with much more heavier fast now that I worked back then, and I quite rarely experienced that kind of performance issues. Number four, you can easily link different files with each other and therefore build new fuzzy on top of the existing ones without losing the data connections. Let's say that you want to pull the data from file a to B, but the location of file a changes. In zoo, there's a high likelihood that you just lost your data connection and it's very hard to restore it in Google Sheets. You don't need to worry about this ever again, because if data connections won't break, number 5, everybody else can contribute to the faster the exact same time when you are working with the file and you see all the changes immediately. You don't have to send the files back and forth at them making changes or way for them to load. Number six, for personal users, google Sheets is completely free and no installs required. Microsoft Excel can be quite pricey and off to subscription-based, meaning that you have to be annually for the services. However, she sued, has pricing plans for companies, but eventually also provides you with other services such as Cloud Storage, videoconferencing, and so on. The main downside, the Google Sheets has stories XL. Instead, the Excel has some more advanced formula, dashboard configuration, and data visualization options. Meaning that technically you can build more complex dashboards with Excel than equal Bush's. However, such complexities required for only a very small number of cases. And considering gold upsides all in on Google Sheets has much better value proposition that Microsoft pixel. 3. S1 L3 - Quick Example: How to Efficiently Automate Reporting: To show just how efficient reporting out the Misha can be. Let's see a quick example of a dashboard. So we have here a dataset which shows the revenue, expenses, and profits for five products. I could just create a separate view to all of them and feeling the data manually. But I could also create that nice dashboard like this, which allows us to see all this data from only one view. Make it more professional and of course not fill in any data manually. I have a truck down here which shows you the Deaf or select the product and how it stacking with other products. In many cases, I see that people do at five different staffs to show such reporting. But we are using the drop-down to make the reporting more clearer and professional. I see that my product Sue is almost the best product in terms of absolute profit compared to all other products. At the same time, I do see that I have quite a lot of expenses. However, the margin still amazing. I see that Product 1 is performed the lowest in terms of absolute profits. However, the profit margin seems to be huge because you see that we have revenue of 421 thousand. Most of that is really profits. So it's really beneficial to sell a product one, this dashboard is really dynamic and creates a dynamic visualizations. So the user of this file gets immediately the understanding how to select a product is performing in serves a profit, revenue, expenses, and what's the best you can at anytime replace a dataset and immediately see the same view without any extra work. And B, there is no manual reporting involved. You can be sure that there are no mistakes in the numbers for sensitive as we have eliminated room for human error. This is only one example of how efficient reporting out the machine can be, but there's so much more to explore for the rest. Stay tuned unless out there, right. 4. S1 L4 - Overview of Course Projects: This course constructive the two main parts. In the first one, we're going to learn all the key formulas and their combinations that you need in order to build a completely automated pieces reporting tool or so on, any other business modeling task that you might ever experience. Those are the formulas without which I couldn't do my work. Addo, believe me, all of those come from my own experience, trial and error, for a very long period of time to make my work as efficient as possible. This allows us to focus much more time on other things and worry less about manual data manipulation every now and then. In a second key part of this course, we're going to build the completely automated business reporting tool. This is something super excited if you're that kind of person who just likes to build professional, good-looking, and well-motivated spreadsheets. That tool we're going to build takes away all the manual work that relates to the reporting itself and allows us to focus more time on creative and value to having aspects such as forecasting and competitive performance tracking. Together with continuous market size estimations, we will learn the secret to effective data visualization and how it's going to create the weekly reporting, we monthly forecasts that come from the budgets. And so much more, the valid self inclusive weekly basis state that together with the forecast for the upcoming weeks, we have almost semi automate it almost all the forecasting in this file through quite some complex formula combinations. We can compare a forecast with the actual piece of results. Compare our results with the monthly budgets, have some impressive crops and visualizations. Create the full framework for market size estimation together with examples how to research really state that. And of course, a cover page and the instructions step. And the main thing he said, this final break, it has very strong data connections and you don't have to perform any kind of maintenance work for this. You can easily just scale it for a huntress or thousands people to use. We'd have worrying about it too much. This is the tool you can customize and start using right away in your company. Or you can take your learning. So the next level and builds cool though aggregator files on top of this one and eventually move the whole global reporting system. So Google Sheets. Eventually, there is so much interesting that you can do with the skill set you're going to learn in this course. So chomping and I can't wait to get started. 5. S2 L1 - Learn and Practice the Key Formulas: Introduction: Welcome to the first technical section of this course. In this section, we're going to learn and practice all the key formulas that you benefit from in order to build a completely automated business reporting. So those are also the poem is help you to solve any business modeling task might face in the future. Making the data extraction dynamic for any situations allows us to build scalable spreadsheets and forget any manual data manipulation. This is a very handsome course, meaning that we're going to complete all the exercises altogether. You can access the exercise template and answers files in our next lecture. Please make a copy of it and save it to the preferred location in a Google tribe in case you're following along with an exercise and you think you know how to solve the problem, I strongly encourage you to pause the video and try to find the answer yourself before I show you the solution. And eventually, the more you practice and follow along, the more you are gaining from this course. So I truly recommend it to be really proactive in, in case of n equations just posted. So Q&A and we tried to find the answers together. Thank you. 6. S2 L2 - Key Formula Combination - INDEX & MATCH: In this lecture, I'm going to introduce it to the key formula that is the basis for any pieces modelling automation task in Google sheets or Excel. This is the index and match in case you haven't heard about this formula combination, then an index and match is more powerful and the vast version of a well-known VLookup. So what it does, it returns to the value or certain location in dataset looking for relevant metrics both horizontally in rows and vertically in columns. Unfortunately, this formula is so often the unutilized because it might seem complex and require some getting used to. But once it's clear, People aren't going back to the old VLookup to do the job. Let's take the dataset that we have visible here in our file. Let's say that we want to get the expenses for July 2019. This is exactly what index and match combination allows you to get. It will search for expenses metric in column B and the July 2019 metric in the respective row. And eventually comes down to one figure as matrix where both those metrics meet in the state area. So as you might see from them, name, index and match actually is two different formulas, index and match. Let's start by looking into the formula step-by-step. So let's also start with the first exercise. I want to show what the index test it requires it feeling that dataset there, which is the first part of the former. So this is the area where the answer to your question exist. So area without the defining column, the row names, and it returned to this area as a solution. So let me just show if I just select this whole area here. So without those columns and rows, and I just close it, it just returns me the exact same area which I just inserted. But I couldn't do it without this formula. So if I just put it like that, it just gives me an error. So like whenever you actually want to pull some kind of area of data, you just have to use index. Let's open this formula once again. The second metric that it actually wants is the row number and the third one is the column number. So basically, we want to narrow down the search for some kind of specific metric in the cell. So how do you know the row and the column number? Isn't this the whole point of index and match that the columns and rows are determined automatically. Of course it is. That's why we now get familiar with match. And let's go to the second exercise. Match formula finds the position of a certain metric in a list. Let's put this formula straight to the action. In this exercise, we want to get the position of the revenue metric in this dataset that you just type in the match formula. And you see that it has three inputs. It has the search metrics that you actually are looking into from the dataset. Then it has to range area where this metric actually locus. And then it actually also has a search type, which is whether you want to get the exact match or you want to get some. So, so much. So let's start off by just completing this formula. Let's select this searchable metric, revenue unless also selected date area. And we, as we know that we eventually want to look up also for the profit metric. I'm just going to fix it with dollar signs so it doesn't move when I copy the formula to the next row. And in this case, we want to have the exact match, which is going to be sera. And you see revenue is reportedly the first metric in a dataset, which is correct. So again, also copying this metric and basing it as formula. So not to mess up any formatting to here. And you see the profit is the third metric in a dataset, which is correct. So let's get more complicated and merge those two formulas together. So index and match. Let's find the revenue and profit for June 2020. So how are we going to get it? If we want to get the revenue and profit for only June 2020, then we do have an option that we're actually selecting the area under two in 2020 where to find this sensor. So I'm selecting this as a date area. And now we only have to find row number on which road the HITECH should locate. And how do we do that? Use the match formula. So that's why it's called index and match because you always look up for them together. So we're going to do the exact same thing as we did in the last exercise. We're going to select the revenue. We're selecting the date area, and we want the exact match. And here this it says that the revenue for June 2020 was 84000. And it's correct. So let me just also extend the formula to here. And we see that the profit for June 2020 was 10 K. And it is correct. So we have actually found all the right metrics. And let's get into the next level by searching the data both to rows and columns. In this exercise, we need to find the revenue and profit for three different months. In the previous formula, we already define what's right month where the lands for locates, which is not the most dynamic way how to do it. But now we have to look this up automatically to, as a first step, you need to define the dataset area. We're looking for the answer from the whole dataset. We're marking here the whole day Darya and increasing it because I know that I also want to pull the metric to other cells. I'm repeating the match they did before. So I'm looking up the revenue from this area. And now you see that it actually looks up, or for the column, how do we get this column number? So if you know how to solve the situation, feel free to pause this video and try it yourself. So I'm just going to need to use another match on top of that. And we tried to look up for me in this area, really the exact match, I forgot to freeze the row. So it doesn't actually move when I shifted down. I'm just looking it up. So in May 2019, the revenue was 55000, which is correct. What I also forgot to do previously is that they forgot to fix the column name. Now I can just copy it to all other cells as well and find the answer. You see in May 2019, the profit was 2000 negative. In December, the profit was 4 thousand positive and revenue was 71. So exactly as we have here. And for June, we see that the metrics are also correct. So we have successfully completed this exercise. We have pulled all the metrics from the dataset that we actually want to use dynamically. So here we are. We just learned the key formula which is the basis for all the reporting and automation we can do in galoshes. Index match can be combined with so many different formulas which we're going to learn in the next lecture to take the most out of the spreadsheet automation. 7. S2 L3 - Determining Year with TEXT: Next formula we're going to learn is sex. Sex formula is used to convert the number to add text according to specific format that should define. For example, it can convert the date, which looks like this. So proper month and year name, such as December 2020. It can be a very useful formula when used in the right places, which I'm going to show you later on. So the main problem with dates is that these can be formatted in so many different ways that they become quite complex to find with index match also, sex formula helps you to extract the month aims and your numbers from a date, which is very useful thing, especially in reporting for us. So let's try to do this exactly in our exercise sheet. In this exercise, Let's try to find the year of full months in a dataset. And first, let's write the in the text formula and see what it requires. It requires two inputs. The first one is actually input number, and there are other ways how you'd like to format it when working with dates, we have to play around with first letters of words, day, month, and year, and refer to the ones how we like to see as a result. So D stands for de, M for month and y for year. Let me first select the value of January 2019 that we're going to convert. And now I need to define the format as a quick example. Let's say that we don't want Chen nine seem to perform it that with such short year number, but we want to format it rather as Jan 2019. In this case, we need to start the quotation marks and right thing. And four y's, which accounts for three digits in a month, I four digits in a year. If we want this to be seen, we should use MM, MM, YY. So let's get back to the goal of this exercise. We only want to extract the year number of the state. For that, we basically need to remove the three M's in the formatting and add four y's. Let's copy it everywhere. And here we go. We have the years for each month. 8. S2 L4 - Smart use of IF's - Dynamic SUMIF and AVERAGEIF: Next to the hormones we're going to learn. Are they conditionally if statements? Conditional IF statements allow you to return date that the meat, the select the criteria in the dataset range. There are multiple different conditional IF statements such as salaries, which sounds on the date that that needs to select the criteria in that data. Strange average, if we stakes that are harder to fold the beat there, that needs to select the criteria in a dataset range and can't leave which calcium metrics that meet the selected criteria in a dataset rate. Let's go pursue up some closer. Some if an average, if let's try to find the sum of all revenue, expenses, profit for 2019 and 2020. How we would do that? We see that our criteria in this case is the year where the month belongs to either 2019 or 2020. So what we can utilize is the data from the last exercise. We already defined what this year number corresponding to the same month. And we also have the dataset area, exactly the similarly formatted as it is for the real dataset area. So let's start to build the formula. So how to do it, as I said, is that we have to use some if, if we want to use this formula. So we see that some IF requires to have three inputs. One is the range of the conditions, second is a criteria, and the third is where the data is taken, we should be summed. So our condition criteria, you said euro. So let's take a straight from a lost sales from the time being as they all correspond with dataset. So just in case I'm also freezing that because this is a good way in situations where we want to also copy it to the other cells that we're planning to do. Secondly, sub-criteria wishes the year number were trying to sum. So we can just select it from the above and just freeze the row. And the third is revenue for all those months, which we can just take from the dataset. And as you see that ordering, those metrics is the same as in dataset. So when I drag it below, it actually takes the expenses x the next row, profit as the next row and nothing will actually break. So let's check. So it says that for 2019 we had the revenue of 695 thousand. And yes, this is the correct answer and we can just easily copy to the 2020. But let's take it to the next level to keep your dash person calculations clean, you might not want to add the separate row for your dataset which defines the year number, how to integrate both of those formulas together? What happens if we just add the previous text formula inside the formula of some if. So, let's try basically what we need to do is that we need to replace the year area, add new text metric. Select the months here, freeze it as we don't want this month's area to move and define the year. However, it does give you the incorrect answer. But why is that? This is because the text function cannot act on a range. So how to solve this? There is an easier way. We can just combine it with the index formula. And you'll see we have everything now again, working index returns into the whole data range so you don't have to read separate rows. What happens if you combine those two formulas? You don't have to replicate the same formula for the day range of data. You could just do it with only one formula instead of using 24 formulas. So let's take it to the next level. So you see that when I actually pulled this formula down, it should take the right data because they are in the same order. So I just see that the expenses for 2019 were 700000 and the profit was also the same. However, I do not recommend to rely on that too much. Instead, you can make a dynamic while combining some myths last sum range, can you guess what index and match? So basically you need to replace this area with index match. So let's mark down the area where the answer lies. So this is this area, and now I only have to define which is the right row. So that's kinda easy. We have done it before. So we use the match. We look up for revenue. We just in case freeze it, VC comb. And we're looking up from here. We want the exact match. And here we have that answer. So let's see whether it gives exactly the same results for all of the metrics. And you see it works. So just in case that's also check the other formulas. So expenses for 2020 world, we're in 910 thousand? Correct. And the profit 154? Correct. However, you do see that we cannot use this formula from the profit margin because we don't want, really want to sum up all this. It doesn't actually lead to the right answer. So it kind of means that we need to perform the calculation separately if we want to see it. So basically how the profit margin is calculated, it is profit divided by revenue. So this is exactly what we need to do as well. We just need to write the profit to here to get the result and divide it by revenue, which was their metric that we had to divide with. So you see, now we have the weighted average profit margin. We can just in case calculated separately, which is also option, but we don't want to make it too easy, right? So it is correct. So I can just drag it to the new area and just in case calculated for this as well. Yes, it's correct. So how to have all those same metrics for the average? If so, that's kinda easy. Actually don't have to replicate the whole process again, while we can simply do, is that I can copy this area. Again just in case could basis values I recommend to have this as a common thing to do it just in case not to overwrite any kind of formatting given if it's not the risk, however, how to change all the SUMIFS governorship. So if you start to type been beverage IF formula, you see that the layout of the formula is exactly the same. So literally how to solve it? It is when we just replace all the SUMIFS with every shift. But we don't have to do it manually. We can just select whole area, go to Edit, Find and Replace, find some if replaced with average. If be sure that you're actually looking in from a specific range that you just selected. So this is currently correct for me. And you have to search from the formulas. So it actually only searches from the formulas, from the whole dataset and replace all. So you see that it actually just replaced 10 SUMIFS with average. If I knew, see what happened. All the data is now replaced. So just in case Let's see, 2019, the average revenue was the same as we had here. Let's just in case look all the same for the expenses as well and is correct. So you see you don't have to do all the things manually. You can also use more techniques and make your work much more efficient. So this former combination of conditional if-statements is really powerful when mixed with other formulas. And there are plenty of use cases how to utilize it. It just makes your life so much easier, efficient, and you don't need to worry about manual mistakes that might come from Jesus in a dataset or anything else. 9. S2 L5 - Use of COUNTIF(S): Another formula that is quite useful is counted. The format allows you to count of metrics or numbers based on the condition that you define as a criteria. So let's get straight to the point in this exercise, we have to find the number of months in the whole dataset where the profit was positive. So let's just start by looking into the formula. The formula has two inputs. First one is arranged where the conditions are searched, and second is the criteria. So what should be the range? It should be the whole profit area for all the months. So let's just select it. Freeze it just in case. And what should be the criteria? We're looking for months with positive profit. So it should be everything over 0. So we can use a comparison operator larger than 0 in the formula. And we also need to surround them with quotation marks. And here we have the answer. We have 16 months with positive profit in datasets. So we can just incase look whether it's true because we have 24 months in a dataset because we had to complete years. So it means that we must have eighth months where the profit was negative. So let's say 1, 2, 3, 4, 5, 6, 7, 8, and all the rest are actually positive. So it is correct. Let's take it to the next level. Let's say that we want to check how many months with positive profit we had in 2019 and how many in 2020. So as you might understand, it requires two conditions, and luckily we have great solution for that, which is countifs. So this formula has the exact same functionality as Countif, but you can just have multiple criteria sin, you can have two criteria at three criteria and five criteria, just how much as you need. Let's start off with the formula. So as a first step, we just need to insert the condition which searches for the months with positive profit. This is exactly the same as we did before. So I'm just selecting all the profit area, freezing it. And I think in everything above 0. So how will we define the year? Gaining guess. If you can, feel free to put the video to pause and try to solve it yourself. We can use the exact same method as we used in some myth and every shift. So let's add the text formula and convert the months to actually years. So I just need to write in the text, select the area for months in this dataset. Define this year. And you know, also need to put it to the index. So this is basically the criteria range. And now we now need to enter the criteria, which is the year number, 2019. And you see in 2019, it says that we had four months, we pass the profit. So let's just check. We had 1, 2, 3, 4. This is correct. So let's also extend it to the next row. And you see all the months into and 2020 were actually positive, which was correct. And you also see that this 16 sums up with the previous exercise, which is correct. So this is the power of if statements. They can be really strong when combining with other formulas. So I recommend to utilize them as much as required to make your reporting or modelling work more efficient. 10. S2 L6 - Identify ROW & COLUMN numbers: Next set of formulas we're going to learn our row and column. So those two formulas are beautiful, very basic, and very easy. But they can come really handy when using that right in the right places, which we're going to learn later on. So the row formula just return to the number of the row we're at. We can just type it in, had no input, and it returns to the row number you're currently at, which is 68. You can also link it to some certain cell and allow it to return the narrow number of that certain cell if you want to. So let's just try. And it is correct. But we want to have it as is and the same from column C. You see that if we define the column number in the cell, it returns five, which corresponds to the column e that we're at. It basically converts the alphabet two numbers. So let's count column a is 1, 2, 3, 4, 5, which is correct. Let me show you the best use case for this formula, which I use in almost every file that I create. Let's say that you have a table like this and you want to have dynamic route numbering for all the rows. Technically, you could just number the rows manually as 1, 2, 3, 4. But in this case, if you switch two rows, the numbering will not work anymore. It would just become a mess. When you use row formula is not the case. How it works is that you have to define the current row in formula and then just subtract minute one number, smaller number from that, which we have here 75. We have to subtract 74, and that's it. You have the dynamic numbering when all the rows now you can sort the data however you want to, and you see the numbering will keep as it is. It won't go off. Please keep in mind that it doesn't really work that well for dashboards. So tabs where you just keep adding new rows on top of the sorting. Because then the manual inserted number will just become incorrect after that. And you just have to keep adjusting that. But when you have the dataset on top of the tab, which most likely might be the case in majority of the cases, then it works just beautifully. 11. S2 L7 - RANK the Data: Next formula we're looking into is rank. As you might already understand from the formula. Then it basically ranks the figures in a dataset to some position. We have here the dataset of four countries, and we want to rank them based on their revenue starting from the largest. So rank formally is exactly what allows you to do that. For that, we need to insert a formula. And you see that it has three inputs. First one is a number that you're currently trying to rank, which as it is based on the revenue, it is revenue for the country. And then you need to define the whole dataset, which is all the revenue metrics for all the countries we want to compare against. Let's fix it as we want to track the formula to all countries later on. And then the third one is in which direction should the ranking be done, whether from the largest to smallest or smallest to largest. So the default option is from the largest to smallest, which were also after four. And if we're going to use the default option, then we don't need to write anything at 30 input there. If we want to rank it from smallest to largest, we need to write in one here. But let's close as is because we want to have the default option. You see that Russia is a third largest country in terms of revenue in the dataset, which is correct. Let's expand the formula to all the countries as well. And you see US is ranked as the first country in the dataset with the highest revenue, which is correct. South Africa being the second, Russia the third, and Germany the fourth. So just for the learning purposes, I'll add the third input to hear as well, which allows you to rank them from smallest to largest. I should see the ranking order changed, but we want to have it from largest to smallest. So let's return to the initial approach. 12. S2 L8 - Avoid Errors with IFERROR: Let me introduce it to a simple but one of the most useful formulas in the reporting of animation. This formula helps you to decrease the future maintenance work of all the dashboards, all the modelling, and all the reporting you might have through not allowing to show any errors in your files which might impact and ruin all the calculations in your file, or either just make your first look unprofessional. This formula is a fairer. Let's look directly at the use case of this formula. So let's say that you have a table of data and in the end you want to see the average of all the metrics, but something is broken. You see that the margin calculation for Italy is an error because you can't divide with sera and it impacts the average for all countries as average can't handle errors. How to fix that? It is easily differ. So all you need to do is to combine the IFERROR with this calculation. So you see that the first input is the calculation itself. And the second one is a value that will be returned in case there is an error. So I don't want this to be 0 as the margin really is not 0, but rather it should be an empty or marked otherwise. So in this case, I just like to apply NA, so not applicable as a result. To start, I see that it's not an empty cell with formula deleted, surround dancer with quotation marks. Similarly as we did for the COUNTIF function. And what we get as a result, the total average calculation is also not fixed and it doesn't anymore counting the country with errors. And that could easily track the formula to all other margin calculations as well, just in case to prevent any kind of fairs in the future. So really recommends you C5a in almost every formula that you use that might get that kind of errors. When we would be multiplying, dividing or using, for example, index and match, I literally can't count how many times in my interface I forgot to use this formula in my dashboards are reporting files and it just caused me so many eras which I still repairing it, my old files. I just kept going back and back and fixing one formula after another. And it eventually just gave me so much maintenance work, which I could have just not been doing if I would have used at Reich. And the beginning is so much harder to fix all those things in the later stages, then including it to the first form in the very beginning, especially if the KE phi users are management or other important stakeholders in a company. If you don't use a format and something breaks, you might just be wasting their time by passing them unusable dashboard. So make sure that you see for as much as possible when required. 13. S2 L9 - Improve Calculation Accuracy with AVERAGE.WEIGHTED: I'm going to introduce it to a formula which improves the accuracy of most of your financial leverage calculations. This is average weighted. Most likely you have used average in most of your average calculations. And he's finding many of the cases, however, in UDL. So as you might see from the formula, it calculates the weighted average for the whole dataset. And why it's important is that the regular average that you might use might be very skewed. And it doesn't take into account the weights of each metric, especially when working with ratios such as profit margin or anything similar. So you see from my example here is that the average profit margin for the dataset is 40. But you see that the top two countries in profit have significantly lower margin than 40. So you can't say that 46 correct to profit margin for the weighted-average dataset, saying that we have an average profit margin of 40% is literally incorrect and just a calculation error. We can't use this in reporting to any of our management members or investors or actually anybody. Let me show you how to solve it to an average weight, the formula. So you put in the formula, and similarly to average, you first define diversion rate. And the second input is the weights. So you want to add weight space and how much revenue those countries generate. Because margin is eventually dividing profit with revenue. So the weights area is their revenue area. This close the formula and here we go. Having a profit margin of 29% is much more logical than having a margin of 40% when SU up your top countries are trending below the figure significantly. And here we are. We have reached the end of the first practical section of this course. I hope that you learn some new things throughout this section. And we're going to end this with a quiz. So you can actually test out how much you really catched, promoted itself can practice and whatnot. If something gets fuzzy, you can always return back to the section. I'm reveal all the formulas and look for solutions. In the next section, we're going to continue practicing and learning goal that key formulas that you need for reporting automation. But this son were seeking to a next level and learning they're really advanced formulas. 14. S3 L1 - Learn and Practice Key Formulas: Advanced Section: Welcome to the second section of learning and practicing the key formulas require for reporting automation. Moreover, welcome to the Advanced section we will be considering are working with our exercise file, will be focusing on formalist of him to pull data from external datasets, new locations, or existing dataset. So now gone off them might be the formats that you might require all the time, but they're super-helpful to solve some specific tasks and hence take your dashboards. So next up, let's get started. 15. S3 L2 - Pulling Data with INDIRECT & ADDRESS: The first events section formula we're looking at is a combination of Internet and address. This is a formula combination that allows you to pull data from a certain cell to another cell. And I'll give you a heads up. This formula combination of indirect analysis is part of the most complicated formula we're going to fill in one of our brush to fill in sections. So let's look into the exercise. The exercise says, find the location reference of a highlighted cell, which is this one. And pull cell state that your new cell to hear. So easy, I guess we could just link this formula to hear. Sure, you can do that. But once you start to dynamically determined the data locations, you're going to run into a problem of not selecting the right cell. State that here also, there is a super good and highly advanced use case. Why do you use this formula instead of linking the cell separately, which I'm going to introduce you in one of the project billing sections. So let's just start this exercise backwards. What are the input fields for indirect? So we see that the indirect requires a cell referencing side. And how do we get out without linking the cell directly with the formula? We need an actress formula for that. So let's look into that. Addresses a formula which returns you a cell reference or an actress in sexual format, you need to nest address function inside the indirect to work properly, Let's look at the input fields for address, I see that the key fields are row number, column number, and also optionally the sheet name. So we know the sheet name, we see it here, but we need to determine the row and column numbers. Let's create both of them separately. So we already have learned how to return row and column numbers. And in this specific case, we want to retrieve the values for this specific cell. For that, we need to write the row formula and link the cell with the one. We state that we'd like to return. Same we do for column. And then we have all the required functions for address available. Let's put it in the formula. So we're just going to select the row number, then the column number. And then let's also add the sheet name, which is the 15 foot to the formula. This should be in the quotation marks. We see at our tab name is exercises. And we close it. And so we have use, use case for this formula. So before I can introduce this to you, but before, let's continue with learning all the rest of the formulas. 16. S3 L3 - Use OFFSET for Dynamic Calculations: Next formula we're looking at is offset. This formula returns a value that is shifted by a number of rows or columns from a specific cell. Obviously super-helpful formula that allows us to perform calculations. So let's look into a use case right away. So we want to find the Delta revenue for a period from January 2019 to a month that you can select from the drop-down. How to do that? The start is quite easy. We need to use the sum formula, which requires inserting a range which should be summed up. The start of the range is the cell for January 2019 revenue, so S3. But what's the ends? How to get this dynamic? So I can change the month from the drop-down without requiring the change the calculation, we need to use the offset formula for that. When we opened the offset formula, then we see that you can insert here as mandatory inputs, the starting cell, how many rows should be offset? How many columns should be offset, and so on. So our starting cell is C3, the same cell where we started our dataset. As we have the same metrics in the same row, we don't need to offset by any rows. So we're just skipping this metric. Otherwise we indicate that the result is in some other row, while actually it's not. But we do need to offset the cells by columns as our months are passed in different columns. So we need to figure out how much sales we need to move forward in the data. And how can we do that using the match formula. And I'm just in case closing this formula and we're doing the match formula calculation to here. So this basically shows how many months is this selected month later from January 2019. So as said, we need to do it by much. So the first thing we need to do is we need to match the mid-2009 scene From dataset. And we want an exact match. And you see here it is. And now what we can actually do is that we can replace this offset column by the cell and we can close it. So you see, now it's taking the sum of January to some period. How ever, if we sum up January to May, it doesn't really give you the exact answer that you're looking at. Because the revenue for select a period for us, it says that it's actually 313 thousands, while the sum is actually do a 155 thousand, which is not correct. So we can just double-check that. So if we take January to June, then it shows the exact data. And do you know why it's like that if you moves five cells forward, 12345 is not made 2019, you reach tune. So we need to deduct the starting from month from the dataset. You can either just deduct one from here or you can make it more dynamic for possible future changes. And you can just look up for January in this dataset. So you see, now we actually have the right result. You see that when we sum up January to May, it is the right sum that we also sum up manually. So if we now change it to, let's say August, it gives you the right answer. If we change it to November 2020. It also gives you the right answer. Offset this really Bauer from formula when US right? And I really recommend to use it right? Because it can do so much things automatically for you, which you might have done manually. And it doesn't make sense to do it like that. By the way, let me just quickly show you also how to create the truck down like that. That's really easy. You simply click on the cell where you want to add it. In this case, I already have my drop-down. You go to Data Validation and filling the criteria. In this specific example, I have manually graded list of months in a depth. But the easiest option is to just select the list from arrange, and click OK. So everything is fine visually. But why I haven't done it like that, you see that it actually converts all those months, due dates, and they really don't want to show it like that. So I just use my own approach, which actually shows me the proper names of the months, which is the preferred option for me. And then eventually you just format this area as you want this to be shown. 17. S3 L4 - IMPORTRANGE with INDEX & MATCH: Input range is probably one of the easiest formulas in Google Sheets, and probably also one of those which you have heard or used before in portraying just a formula which imparts a range of cells. They're spreadsheet from some other spreadsheets. So let's try it out right away. We have this dataset here, we choose should click Open. Let's try to import this area from A1 to Y5 to our file. So let's try to do it. So I see that this is actually the whole dataset. And this is actually to be honest, the same dataset as we have here, just we want to try out different techniques. So let's type in import range. And you see that it actually requires two inputs. One of them is the link name. The first of them is a spreadsheet URL where the imported data locates. And the other one is a combination of the tab name inside the file where the actual data is located with a specific cell range, which date that should be imported. And as you see from the example, then only this data is in quotation marks. This is again similar to what we saw in the COUNTIF formula, that you have to surround input areas with called Dacian marks if you don't link them from some other cell. So Let's just copy paste the link just to keep the same approach as we have in the example. Let's put it to the quotation marks and let's check the range input now. So the tab name is dataset. So let's just start typing it in with quotation marks. Dataset. Now you had to enter tap knee with an exclamation mark. That's how Google understands that you want to end the staff name and start to write in the range instead. And now let's check the range. It was from A1 to Y5. A1, Y5. And the quotation marks. And imported when you're connecting on external fights or a file for a first time, you're going to need to allow the access to the sheet. So in my case, it unfortunately didn't happen because I already have linked the files between. But you have to probably do it. So most likely you can just go over this one cell where you actually included the formula and allow hear that connection between the sheets. You can allow the connections only between the sheets where you have access to independent, whether it's the edit taxes, common taxes, or view access. So it basically allows it to see data from other files in your own file. So this is a onetime thing, no more doing that again. And here we are. We have successfully imported our dataset from one file to another. Okay, so let's take this to another level. Let's try to import only one row from this dataset with Gagnon, guess with formula, index and match. So how to do that? And also you actually see that we have the headers and the right column name already included. So it kind of feels that it makes things more complicated. Well, of course it does a bit more complicated, but it also gives them much more flexibility if you learn how to do it. So let's start typing the exact same formula as we had before. And I'm just gonna replace it here. I'm not entering that otherwise it would just ruin my formatting. It will just overwrite the old areas and like extent the columns and so on. So, and we only want to look at that date area. In this case, we don't want to actually look like whole dataset. We want to retrieve only this area which is from B2 to i 5. So we just need to replace here A1, we'd be two. So we actually exclude the header rows and the column. And let us replace this static spreadsheet with linking it to the cell because this is the thing that you should actually be doing. You don't need to over-complicate formulas. You just need to make them as dynamic as possible. So eventually when the link changes, you don't have to change it in all the formulas inside. You can just change link here and it's much more easier that way. So how to proceed? Do you remember how the index and match formula works? If you do know how it works and want to try it yourself, then feel free to pause the video right now and solve the challenge yourself. So we have to use the match. You see it's an exact same formula setup as for index and match just with external data areas. So eventually, this is going to be an index area. And now we're actually putting in the match area. So I'm writing in the match, well, we need to find is the profit. And now just copying this area to that area where this profit should be found. And I'm looking at from A2 to a5. A5, we want an exact match. And we have the data here. We have only profit metric important. And you'll see that it doesn't require any more to connect the Xi's as we already did it before. So let check and is correct. 18. S3 L5 - Advanced Formula: QUERY: We're now moving to our last formula, curie. So this is a polymer that might require a whole separate course semester it, but we're still going to learn the basics so you can start testing, exploring it yourself. I primarily just wants you to be aware of this formula. So cutie, Is that a regular Google Sheets formula is actually based in sequel, which is a database management language that is used to pull data from databases. It's not specifically a programming language, but it often serves as an extension to one curious considered to be the most powerful Google Sheets function, which can do the job of many other formulas that we have used. In this course. We're going to use the stream part data and play around with these core functionalities. But there's so much you can actually do it that once you learn the basics, just start Googling around and trying to see whether there's anything specific you'd like to achieve using this formula. So as part of this exercise, we would like to import data for three months from the base dataset that we have freeze on top rows. So let's just start off by trying it out. Let's write in curie, you see that it has two mandatory input areas and one optional. So the first area is the date area similar to any other date there we have used in those exercises. And the second one is security itself. However, the date, the area in this case is the whole dataset. So I'm just going to select that and just in case also limit thing with the dollar signs. And what you will see that their exercise says that we need to import data for January 2019, tune in 2019 and December 2020. So basically I wanted to do import the dataset, but only for those three months. So this is also what our layout kinda such as. So let's start off by that. So Curie in general can be quite complicated, but I'm going to demonstrate like very easy solution for that. How to extract only three columns every time when you're trying to call a curie, you need to type in select inode, so quotation marks as always. And then what you want to select. We want to select data for January 2019, June 2019, and December 2020. So I looked at which columns they are together with the defining column. Those are B, then C as where is the January, then also h, where the toonies, then also z, where the December is close to the quotation marks. And we can just ignore the header field because we don't really have a header. We actually want to import everything and we just close it. And you'll see we just have filtered only specific columns from the data. And it's so powerful, so easy formula and he does that kind of things. So there's plenty that you could do with this function. And you can always combine Curie with some other formers to make it as dynamic as you want to. 19. S3 L6 - Advanced Formula: QUERY 2: So let's take the Curie format to the next level. Let's rather import all the months where the profit was negative. So if you remember our exercise we counted, then we already found out that in this whole dataset of 24 months, we have 16 months where the profit is positive. So now we have to find out only those eight months where the profits was negative. So let's start. So logically, you have to type in the Curie formula. I mark the dataset in the area. Fix it with dollar signs so it doesn't really move. So what's next? How are we finding only those months where the profit was negative? Last time we imported the database and columns. So we have to do it again. So the thing is that Curie can easily read metrics by columns. It requires you to have a data format which has all the same metrics in the same column. And if you're planning to do any kind of filtering. So the column names have to be the metric names, and row names can be the database, not vice versa. So we don't need to rearrange actually our dataset for that. We can instead use a formula, a transpose. So let me just strive. So I'm just going to show what it does. It actually just transposes the dataset as you had before. So now you have all the metrics in columns and all the months in rows, which is exactly how the Curie works. So we can now just continue based on that and tried to use this dataset for filtering. So as always, we now need to write the select, which is the basis for everything. And as we now actually don't have any more column letters, we now have to use the column numbers in order to determine what are the actual metrics that we need to look into. So the month is column one, revenues column to expense this tree, profit is number 4 and profit margin is column five. So the first step, what they actually need to define when select is what metrics do we actually want to show in a dataset? So in our case, we want to actually see the month and also the profit. So we need to select the column one to be able to seed Month. And we also need to select the column for because we also wanted to see the profit because our question is, we need to import all the profit metrics for all the months for profit was negative. Now, what it basically does is very returns you only the date area, the months together with profit. But we don't want to see all the months. We want to see only where the product is negative. We need to apply a filter. We need to write where column four is less than 0. Because we want to see only the months where the profit was negative. And you see, we found eight months of those wishes exactly correct how we basically you're wanting to be. However, we see that the dataset is kind of like skewed. So what we now have to do, we only have to transpose the dataset back. And we had the right answer. We have successfully filtered out all the negative profit months from the whole dataset. So it's actually not that complicated. But you can see like with such short formula, you can do so much things. So iso might though the C carries a really powerful function and I do recommend to search more about it whenever you have some kind of data manipulation issue that you don't manage to solve any other way. So there is a high likelihood that Curie might do the work for you. 20. S3 L7 - Section Recap: And we have come to the end of the very first part of this course. Congratulations, we haven't learned all of the key formula, so the requirements for reporting automation. And we gained from the very beginning, we learned the power of the index and match and all these cases you might have, as well as combining it, We all that the formulas we learned the functionality of row, column indirect address if her average weighted and so many different formulas. And finally, we learned the basics of the most powerful Google Sheets function Curie, I hope that you follow the luck with the course and you're excited about what's coming up next. In the next section, we're starting to build our course project. And in this part, we're going to practice and utilize all those formulas that we just learned and even luck. Thank you and see you in the next section. 21. S4 L1 - Putting Together the Project: Hello and welcome back to the course. In this section, we're actually starting to put together a weekly reporting solution, which is also made it to the highest extent. We are going to utilize the formulas we've been covering the last two sections and really putting them to the practice. This allows you to really to tie everything that you learn together. And it gives you all the tools you might need in order to solve any kind of business modelling task you might ever experienced in the future before jumping into the practice, let me introduce it to this model and see what we're going to build. This is the fully automate a weekly reporting soil for Airbnb operations in the Netherlands. We have this nice covered SAB showing the key information on the latest weak performance. We have all the key data here, which had tried to keep relatively brief and for only three months for this project purposes, we actually can set the targets for each of the metrics, which is highly automated. I'm going to show you one formula which is doing a big part of this automation. You see that the only metrics you need to regularly fill in our estimate for number of days in a week and estimated cost sublevel for the week. Everything else is coming either two formulas or from your actual average historical performance. You can actually also compare your forecast with the actual, how accurate you have been and why do you need to adjust in order to be more precise, this file is connected with the monthly budget. So you can measure if your weekly results are trending together with what you have budgeted. And if you need to change some things to be more aligned with them, you will build a framework how to estimate market sharp you and your competitors and learn how to fill it in. You're going to build all those 3D looking crafts, visualizing your key data. And you're also going to add a tapper instructions, the area predicted them, which is the only source which you need to fill in with data. All the connections with their sheets will be automatic and we're going to have a separate sheet for the calculations. So your main dashboards look clean and you don't have so much data noise in them. And that's it. We're going to build this suit from the scratch altogether. So please be prepared to actually follow along all the way if possible. I really recommend you to use the money to or if you have one, because it's much easier to follow along if you have video one screen and you can do the actual work in another screen. If not, you can also speak the screen that works as well. Just to be clear, I'm not associated with Airbnb by any extent. This is not their actual reporting soil or they're not their operational or financial data. This is just an example. How does too, it might look like for them with a completely random dataset that I have put together. I just took those numbers from there. There's absolutely no truth inside. Let's start going. 22. S4 L2 - Mapping Down the Metrics: Let's get started. The very first thing we need to do is create an empty spreadsheet. For this, you can go to Google Drive, click New, go to Google Sheets. And from blank spreadsheet, you can just create it to a folder wherever you like. And I'm just going to name it course project. And the very first thing we need to do is to define the metrics to hold reporting file will be including because all the rest will be just surrounding those metrics. There are two ways how to approach this option. One is to map down all the metrics you want to track and then later hope that you can actually gather all this data. I don't really recommend doing that unless you don't have a corporation with a good data analytics or data engineering team who can definitely create them for you. Because eventually there's probably going to be something that you can't get. And if you have built some crafts or complex formulas on top of those, you have to start adjusting and it's just a waste of time. Second option is to check what metrics you already have in your accompany and built a look based on that. For our case, we already had the dataset. So I can build our reporting based on the inferiority have. So I can see that I have quite a few metrics here. And what I can do is I can just copy them for time being and just paste them to a new sheet. And I'm just going to call it Data tab. So to make it easy and get the metrics that tab, and we just use the transpose formula and take all the datasets from here. And now I'm just going to copy them over. We just values because I want to start rearranging them. And it's not really comfortable if you have the formula inside. And they already see that I don't need accounting days in a week. So I'm just deleting that. It's not really related to reporting layout in this case. And those were actually supposed to be an dates. So I'm just going to convert them to date. So for this project, let's assume that this is the main raw data that airbnb uses for tracking their performance. So we have here the active homes, new and removed homes from the platform. How many stays were done in the homes during the week? How many nights where available in the first place? How many customers do they have? How many customers were Charon's, how many there were new and also the financials, GMV, revenue and discounts and subsidies. So in case you're not familiar, this GMV short for cross merchandise volume, which shows the value of marketplace created by the company service. So in other words, what's the total monetary amount that customers have paid for the service, which in our case is equal to the number of nights booked and stayed through Airbnb multiplied with the price of the states. And when you think about it, you have kind of three categories of data points here. You have the section for homes, you have a section for customer state data, and you have the financial data. My recommendation is to always try to prove the metrics in easily viewable sections. So let's rearrange them and also rename them. This is Holmes. And I was just making some room here. So we understand where the metrics are standing. Just loop formatting. But this is clearly not everything we want to track. The good part is that we now have enough raw metrics to perform calculations on them and turn them into ratios, which allows us to track the performance in a relative way. So let's see, let's just create here a new area for ratios. And let's see what we have in mind. So the first ratio that comes into my mind is tracking how many states have had per home in this week. This requires us to divide the number of states. With active homes. And honesty, I actually would like to move this number of states that are Customers section because the number of states are very dependent on the customers. Like of course, they are also dependent on homes. But we can have homes, But we can't have number of states without customers. And I'm just going to extend it here. And if we already tracked this, we can also track how many available nice per home we've had. For that, we need to divide available nights together with active homes. If we have the number of all nights available in the platform and we have the nice occupied by customers. We can use this to calculate the utilization of our homes. We just dividing the number of stays with the number of available nights in the platform. We also would like to track the growth in number of states. And now I will just put it here. And here. But you see what happens. It goes through era because of the logical reason, you can't really divide one number with the metric. However, there is an option that you don't put here any kind of formula at all, which is like a good option. However, when somebody for some reason two strikes this formula and it might have nothing inside, you might just lose all the formulas. And we really don't want that. So there is a release a solution for that. And this is IFERROR. So you see, it doesn't show a formula here, but actually there is a formula. And this is a really good reminder that you will have to always use the for whenever possible. So just in case I'm also going to replace the formulas here. And I'm just going to remove everything here. So we're fine. Well, we can also do is that we can track the states for a customer to understand how much nights each customer we have is staying with us. For that. So we need to divide number of states with unique customers. And also not forget the fair. You see, it's really easy to forget that, but I promise you like your life will be much easier. Api actually use that and we can calculate the price of the average stay as we have the number of states and also the GMB. So you see getting smarter already from financial say we already have GMV revenue and the cost of all of these counters subsidies under this discount metric, we have, for example, amounts where we have made a discount to a customer of 10 percent as part of some campaign, but we still have paid the full amount of the homeowner. So in this case, customer bay 10 percent lists and we compensated this to the owner and those amounts are under here. So if we now think about the metrics that we can actually use in the financial sections. The first thing that comes into my mind is the commission rate, which will be calculated by dividing revenue, which EMB. And we can also calculate the discount cost ratio that GMB tracking as much as possible through ratios is useful to understand how the overall performance of the metric is trending. As an example, if we see that our costs are significantly increasing, we might think that it's a bad thing, but actually maybe R, G, and B is increasing with twice the speed. It will be hard to see. We track only the absolute numbers. But if we make it relative to other metrics, we can actually see the relative relationships, whether it's getting better or worse. So, so I'm adding two here, just a ratio of discounts and subsidies to GMB. So we have now the revenue and we had the cost. We can use those to calculate the cross profit. And as said, we should turn it into a ratio to understand how it's rendering. So dividing the cross profit with the GMB Because histamine ratio that we're tracking and we're getting a cross margin, which is quickly going to copy them here as well. So we now have all the financials here. But I know that they actually don't want to stop only with reporting across profit. I know that I have the data for OPEX in the budgets, which I have a link to the file it, but I can do it. So I already will include a place holder for metric of OPEX. Based on that, I can calculate the full cashflow metrics, which is the profit and loss. And it's ratio that GME. So this I know will be cross profit minus up x and this will be cashflow, the GME. And here we are. We have mapped down the whole list of metrics that will be used in a reporting tool. As you saw, we have now almost, nearly twice the number of metrics we had before from the dataset. You can actually make it even longer, as long as required, but a limited our project with only the most universal ones. And actually it's also smarter than not to not include every metric that comes to your mind because first, many of the metrics will never be used and they would just keep making your file heavier. And second, if you have so many metrics, most likely or reporting 5, we'll look at a bit unprofessional just because it's hard to find the most important metrics for the whole file. So keep your metric list as easy as possible and try not to add any overwhelming information as the last step. I actually don't want all those racial metrics to be on the bottom of this well, because eventually they are all part of those sections and the ratios like half of all the dataset. So what I'm gonna do, I'm just going to rearrange them for the time being. So I'm just going to put this days per home to hear available nights just below the available nights utilization. I'm going to put in-between here, the growth in number of states I'm going to put here stays per customer. I'm going to put to the bottom average right price just after that, because it's also very customer-related metric. So I'm going to give the GMB on the top because it is very main one, commission rate, I'm going to put below revenue. This I'm also going to put here, and actually all of them will just follow here. And now I can just delete all the area that I'm not going to need in my file just to make it a bit more shorter. So you see, we don't have too much metrics. However, we have all the necessary metrics to make all the decisions that we might need to. And that's actually the best part of that. You need to find a balance between having all the necessary metrics in the file, but at the same time not showing too little information. Eventually you just have to make sure that all the state that yet you have is relevant and use by the end users of the file. 23. S4 L3 - Defining the Week Periods and Week References: If you paid attention in the section where we learned all the key formulas, you might already expect how we're going to link the dataset in our Data tab. Do the step through index and match. We're definitely not going to keep the dataset sitting here as it is, because this leaves you only with our static reporting file. But we're building a scalable and fully automated tool which you can duplicate for as many countries, cities, or regions as you want. As you might remember, using index and match requires two inputs from which road to take that data and which column? We already have one input, which is the metric name, based on which we could pull the data because we didn't change anything. But we're also going to need the second one to define a week periods. Easy solution would be to just use the week start dates, a copy them to this tab, but it doesn't look very professional. Instead, we could convert the week start dates to a week number. And based on that, during the week numbers, Joe weak references. Let's see what I mean under that. So I'm going to create a new tab calculation. This is the tab where we're going to complete all the calculations to keep the main tabs as clean as possible. I'm going to copy our week periods to this step. I'm just going to format them as a date. And now I'm going to find them references start of the week. So how I usually like to reference the weeks is through defining a year and then adding a week number on backup that we didn't learn this formula before. But let me show how it works. I'm going to use year formula here. And then I'm going to convert the date OSA week number 2, the week formula. So we have all the components. I'm going to merge them now together to one formula. But just for the easy read purposes, I'm going to adjust this formula a bit to make sure that it's easy to understand that if refers to a year and week number combination. So I'll add and signs with additional text to the formula. And here it is. So you actually see this is a proper weak reference now, so it prefers to 2020 and weak second in that. So I'm just pulling this down. You see everything is working properly. However, it doesn't really work properly for the first week. And do you know why? Well, this is year 2019 and it's a date in 2019. However, if I'm just going to go back in time, then I actually see that majority of this week starting on the 30th of December was actually related to 2020. And that's why we're also including this in 2020 reporting. So it kinda means that they have some manually adjusted, but that's fine. If it's only for one metric, then there is no problem with that. But you just have to keep in mind to have a look anytime you use that kind of approach. So what I'm now doing is that I'm also going to connect the weak reference to this step. And it's quite easy. I'm just going to use the index match for that. So by index areas where the answer is, so this one and the match is basically start the week. So this one to here. I am also fixing them through dollar signs and I'm just in case extending it a bit bigger so to 30 because I really don't know, maybe I'm going to add some new data. And you know, I really don't like the fact that it gives mirrors. So I'm just going to put it into ether and mark that if there is no match, then just use error. 24. S4 L4 - Connecting the Formulas with INDEX & MATCH: We have all the weeks defined in the dataset and we can continue with the main tab. So I got back to the main SAP and what I'm gonna do is I'm going to add a new row on top of the start of the week. And I'm going to use this for the weak reference. Hello, I'm gonna do it. I'm using the index and match. And I'm going to look up everything based on the start of the week. I don't just going to copy everything here. And now I can replace all those static values in the active homes and many other roads here with the formulas. So I'm going to build the index and match formula, place all the static values in this dataset. So how I'm gonna do it? I'm going to use the index and match, and I'm going to select the date area, which is this area here, because we really don't need to start off the week and count up the days to be part of that. And this first part, I'm going to search by rows and I'm going to need to define everything by the weak reference. So I'm just selecting the weak reference, even though that here it is in column format. We want the exact match. And now as a second metric, we're going to need to find the actual metric. So I'm going to look up for the active homes. I'm going to freeze it because we're going to drag it in the next columns as well. And I'm just going to mark it here again with the exact match. And I'm choosing a source of putting it today for to prevent any kind of errors. So you see, we managed to pull it out. So the roast that we need to replace all of those. So I'm just going to drag it and bases formulas. So you see all the data remain that it is. So what i also going to need to replace is the number of stays. Unique. Customers, churn customers, new customers, cheer me. Revenue and discounts are subsidies. And as you see, we don't have any static data anymore in the dataset, which is a great solution. Because now we're literary, don't need to worry about anything here. If we want to scale this file to the new city or new country or anything, we can just replace this dataset or we can just update this dataset because we don't need to change anything here. For example, if I just remove everything, you see everything is gone and we also don't have any errors in the file. So if I for any kind of reason would I didn't know to lead some efforts from here and delete the dataset. Now, you see it starts to actually give errors and this is not what we're after for your dashboard will look unprofessional and this is not what we want to do. So I would just like revising everything and everything is fine again. So I really recommend you use the fairer like all the time, just in case to be on the safe side, though, you don't have even any room to get any kind of errors. 25. S4 L5 - Setting Up Budget Connections and Calculations: There is only one part left from linking the actions to the file. And that's going to be linking the OpEx we did tap. So the first thing we need to do is to get the monthly budget to this file here. And for the time being, I'm not going to make it too complicated. We have our budget here. I am for the time being, I'm just going to copy everything from here and base here as values. And then I see that the layout is not really as we use it for our dataset here. So I'm just going to copy everything and just based as transposed, delete this part here. And now we have it the exact same format as we have for the previous dataset. So the only metric that we're going to use from the special currently East topics. But you see, we don't have any OPEX here. We only have the GMB number of stays, revenue, this cancer subsidies, gross margin, sales and marketing and administrative costs. So luckily, I know that in my company we use says a market thing and admin costs as OPEX. So I can just easily calculated here and copy it here. So now we had opex metric right here, but we have it a monthly basis. Our weekly reporting is clearly on weekly basis. So can we even use this monthly data in a reporting? Well, that's going to be a slightly trickier, but there is a good workaround for that. Only thing that we need to do is basically divide our monthly cost with the number of days in that month and multiply this with seven, which is the number of days in one week. And we will get the weekly OPEX costs. In order to do this, I'm going to need two things. I need to define which week belongs to which month. And we're gonna need to number of days in each month. So I'm back in my calculation step and I'm going to divide the weeks between the months. And similarly, I can do this with text formula what we learned. So only thing I need to do, convert the state to the format of month and year. And we have the result. So you see that it works perfectly for January and February, but for the first week, similarly to the first week reference, it doesn't really work. So actually we know that this should be belonging in January 2020. So I'm just going to link it with the next cell. Second thing is counting the weeks in one month. So not to make this table to missing, I'm just going to create the new table here where I'm going to add all the months we have in the budget, taking them from here, copying them to hear. And I'm converting them to the proper month reference. Also going to name this as month reference. And we're going to do it the similar way. So sex. So now we have the same basis for everything and we can use the exact same referencing in all the file. So let's now finally count the number of days in each month. We could count it manually as well and say that January has 31 days in a month and so on. But we want to do it automatically for that, we could use today's formula, which we didn't learn before. But I'm going to show you how it works. In order to count the days in January, I will just enter the formula. First input is the end date, which is this metric here. And the second is a start date, which is in January. And you see we have 31 days in January, which is correct. So I also track the formula everywhere and see that it actually doesn't work in the last month. Similarly, as it wasn't before working with those two. So I just know for a fact that December has 31 days and then I'm just going to use it manually. And you only have to do this manually for the last item in the dataset. So I will now also link this data we did here. So we have the same basis. So I'm just going to attract these table 2 on the right side. And I'm going to add here the month reference. Actually we can just call it the reference. We don't need to make it too complicated. And similarly, I'm going to just using Lexend match, do Bu, the month reference based on a metric here. And just in case so, so putting into ether in a similar way that they're weak references put. So I'm just going to type it in error just in case. And I can just delete the old areas that we don't use in the file. So I will know just transfer those metrics to the Main tab. I will bool here demand reference and the count of days in one month. So how to get the month reference? Well, I can use the index match. So as we need month reference, we first need to select the area where the answer lies. And this is going to be, in this case, the month reference, because we're going to link some based on a weak references. And those are in this table. Nothing this. So we can just fix it and use match. So weak reference. And it's stereo where it looks out for dancer. So we're gonna do the same for the count of days. But in this case, we are looking based on the month reference, the count of days from here. So the answer lies here. And we need to match based on the month reference from here. So it's done. So let's first just get the monthly data. So the OPEX is here and we have to match it based on the month reference. So the opex for January 2020 is 55000, which is correct. So it's pulling the right number. And now let's divide this monthly cost with the number of days in a month. This allows us to get the monthly Opex cost and multiply this with seven to get the weekly cost. And here it is. So let's just drag it along. And let's leave it like it is. So one thing you can note is that this monthly costs doesn't exactly reconciled with the sum of weekly cost. We saw that there were OPEX was 55000 for the whole month. However, if we now actually sum all the January together, we actually see that the sum 62000, this is because actually in five weeks, there is 35 days. So it's basically convert that to the estimate of the full week basis. And actually for the high-level metric is definitely find that it's not exact number. It rather works as a high level indicator, not anything else. If this would be exact reporting and if you have the opportunity to get the date on a weekly level, you shouldn't definitely use that. But this works here is fine enough because we only need this to understand whether we're cash-flow positive or not. And here you go, we have the whole tap populated with the data. So just in case, Let's also rename this sheet to actually, and we are fine. 26. S4 L6 - Formatting of Week Numbers: One thing that I like to do is show proper week numbers for each of the weeks, such as this is the Week 1, this week two, and so on. So as our dashboard actually starts from the first week of the year, we can have an easy fix for that. And so this would account a formula. So count a council number of values in the range. And how we keep a counter properly is if we fix the first part of the range, we tell our signs and release the second part. So I'm just going to type here in the b1. In this case, the Start cell doesn't really move, but the second part actually moves. And we're going to add to the first part of the formula, type it in just weak and a. And now we can just extend it to the all areas. So you see, we four is actually a week for us. We see from the weak reference, week 7 actually is Week 7. So this approach works only when you actually start the reporting from the first week of the year, which might be the case. There is a proper formula to take that into account, which I'm going to show you on the screen. But we will not go over that in detail because it can be relatively complex. But I promised that we're looking into it in the later stage of the course. But what it does is that it presents you only the value after w and you can detect a week number from that. So let's go forward. I also want to show the short date on a row below, but I don't want to show it as a full date. So I'm just going to link the date to a new cell and I'm just going to change the formatting from here. So, uh, how I want this to be presented is that I want to see the month as a short name. We design. And then a day. And you see we have the short date reference now. 27. S4 L7 - Visual Formatting: So we have all our data flowing into that tab, but we don't want to leave our dashboard looking like that. So let's apply some formatting to the file. So as a very first step, I'd mark all the key metrics with bolt. This helps you to see which are the main metrics you need to mainly keep her EyeOn in the business. So this is only my own judgment. You can actually mark whichever metrics you feel like, but I'm going to select those. So what I'm additionally in doing, I'm also adding the borders around them. And I don't want to use a black for bordering because it might be very tough, but I'm going to use those as a next step. I'm also going to reformat all the numbers because we don't want to have our metrics in that format. So I know for a fact that the utilization, for example, is a percentage and it's fine if we actually don't show any decimal points. So I know that those metrics here, altogether with those actually are just numbers. But I want to have a separator for them, sucrose the number of states is also a percentage. And we want to see it with one decimal point. Stays per customer, as well as available nice per home. And stays per home are actually metrics which are best seen with two decimal points. Then we also have average stay price, which I would like to format as zeros. And we don't need to see any decimal points. The same actually happens with GMB revenue. This comes the subsidies, cross profit, OpEx, and cashflow. They are eras which are best shown us that commission rate, this Canson subsidies to G and B, cross margin and cashflow are actually also percentages. And those I would like to see with one decimal point. So it looks already much better. So as I had three separate categories of metrics in this tab, I'll just color code them differently so they are easier to track. I'm going to color all the metrics with some color. So let's have homes. As pink, customers, as orange and financials. Maybe we'd like green. And then I'm just going to see the metrics that are not the main metrics and what kind of customer that I'm going to just adjust it lighter. Let's just change it a bit. So the kind of far from the same metric group. But you see that there are different, the same I'm also going to do with the customers. So I'm just going to take this custom that to lighter. Let's go and adjust it a bit more. And the same I'm going to do with financials. What I like to do is that they liked to leave my header area nearly black. So I'm just going to overwrite those areas here. And black is a very universal color, so you can actually use it everywhere. I'm also going to apply the same color to the headers of the category name. And I'm going to draw it between areas just like gray because I just for some reason don't like white in the dashboards because this is the default color. And also you can see that the color is also likely so they match better. So I'm also going to just delete those areas here because we don't need that much space for the dashboards. I also like to do. Instead I add a narrow column here, which basically is most likely going to be around 20. And now I also have to review them borders once again. So the texts won't start immediately from the border of the file. So we have added here the weeks, but in case your file includes multiple years of reporting is such a set to define here also the year number, which I'm going to do just for the clarity purposes that this is actually year 2020. You can also do it automatically by just using reference here. Or you can just do it manually as well because it doesn't really change that often. I'm also going to merge this area here and quality the week lectures. And I'm just going to format this to be more clearer. We can even make it a bit bigger. And here I can actually merge this one as well and say that this is the data for Netherlands. I'm just going to move it to the center and remove the bolt. Those notes here are super useful when you're making brings receptor file and sending them to people. Then you don't need to clarify every time which region you're actually talking about. And they can see without DevOps, on their own. So there are few more things that I like to do. The First of all, I'd like to remove the grid lines because this makes everything much better. And I'm also going to need to add borders to hear, because otherwise you don't really see when all the areas are actually ending. I can also make them a little bit more narrow. So I'm just going to maybe seven to eight here. Much better. So the next thing I would like to do, we'd like to ask some empty dark rows under few metrics to basically act as borders. So for example, here, I want to clearly see where in the customer section the number of stays area actually ends and the customer state actually starts. So for that, I can just take this row, make it very tiny, maybe even two, and just use the same color that I used here. I don't want to add too much of them, but I definitely want to move it to here. I can just now copy it. And I also really want to highlight that this cancer subsidies area. So you can immediately see a where it actually locates. And you know, there is also a way to do the same thing with borders, but there is a good reason why to make it as a separate row. What's good about adding a new row instead of just adding a border, is that you can easily just copy and move this around just similarly as we did before here. In case you would add a border and you get rid of that later, you might start reformatting all over again, maybe like those areas here. And it will take extra time. Separate row kinda gives you the flexibility to just move it around. And I'm also going to add a little bit color-coding to the file. One of them goes to the cashflow metric. Two here. So I can just use the conditional formatting from here and use the color scale. I'm going to round it the round sera. And if it's negative, breccia red, and if it's actually really positive, then make it really clean. The second metric will be the growth of states, which I like to format in some other way. I'd like to add another rule, but I want to add it as a single color. So whenever the growth in number of states is greater than 0, so it's positive, actually doesn't add any kind of background. But if former US number itself a screen, and if it's actually smaller than 0, they'd actually inform us that as dark red, easy. So I'm just in case also adding more borders here because I just like to understand where my reporting file actually ends. And I'm also going to group those because we really don't need to see them all the time. I'm going to align all the weak data to the center. And we really don't need to have those as bold. And there is actually only one more thing to do. I'm going to freeze to adopt rows until here. Similarly, I'm going to do it for that with the first two columns. Even that this file actually fits my screen, I'm not sure whether it actually fits fully to yours. And you don't know if this dashboard actually fits fully to the end user screen. So it's better to be safe than sorry, because look when I don't freeze them and zoom in and just put it down, you don't actually see which week it is. So it's rather better to just be on the safe side from the very beginning and use it like that. And we have come to the end of the first project section of the course. I hope that you learn some new things that you can start implementing already right away. And until that time, I will see you in the next section. Thank you. 28. S5 L1 - Adding the Forecasts: Welcome to the new section of this course. And this is about creating an automated meant that the light shape for forecasting. We are going to forecast every single metric that we have included in our weekly actual step. And let me give you a hint in the end of this section, only two of those metrics are fully manual. Two inputs forecast for the rest of the matrix are either fully automated through formula or semi-automated by using for forecasting the previous week's results. Meaning that we're going to build a pretty complex formula using the learnings in the last two sections to execute that, let's get started right away. 29. S5 L2 - Defining Sheet Structure: First thing we're going to do is to define the tab structure. So we don't have to start building everything from scratch and a quite these wafer that would be just duplicate this tab here. So I'm just going to rename it to targets, and we will just remove everything that we don't need from this step. So we don't need the formulas in this file. And also we won't be requiring this color formatting in this area because recreating a new logic to this step. We will also rename the top-left corner of the file. We will also remove most of the borders, but not the ones which are below and above each category. So we're just going to remove those. I were putting here it back. So we're left with a blank template. One thing that I know for sure is that we won't be forecasting the same we said we already have in the past. Instead, we're going to add new periods to this file. So we have data for almost two months, January, and almost the whole February. So let's set over complicate this, and let's set the forecasts after the end of March 2020. This means that we're going to need to do changes in their calculation step. And I'm just going to make it easy. I'm going to just take the last state and I'm just gonna copy it like that. And you see the week starting actually from 30 of the March. We don't actually need that because this eventually already belongs to April. So I'm just going to remove it after that. And for those, I'm just going to extend the formulas as we have them and everything else is in the place. I'm also going to adjust this template. So I'm not starting my forecasting period from the first week of the year. So let's add the forecasting from beginning of February, which means from February 3rd. So in order to actually do that is I'm just going to delete all those areas. And though we added new data for additional four weeks. So I'm just going to add here for weeks. And I'm just going to extend this area here. So you see that the week referencing actually starts to give errors. And do you know why stat? Because week referencing actually comes from the data that, which doesn't really show all the periods in the forecasting. We actually have the change it today to data from the calculation tab. So I'm just going to need to rewrite the formula. I can basically just like rewrite those areas. So we're looking for answers from here. And this is where the start of the week is located. And now it works. Let me just extended the whole weeks. And I'm also going to use the same formula in the actual step, just in case because I actually know that this might eventually get us in trouble. I'm also going to just align everything to the right because it looks much better like that. And I'm also going to do the same here. So I'm also going to extend the month reference and counting days a month. But you see what happens. It gives an error and whites that is shows that from the calculation tab, it takes a date area from C2 to see 10. But actually, we have added new periods here, which means that it doesn't count in for the data range. So I just have to change that. So I'm just going to take this whole area and I'm going to rechange it as well. Locked. And we're good. I'm just going to need to replace everything here. And now we're good. And actually remember how in the last section we did the week Number Formatting. Now we cannot replicate this because reporting SAP doesn't start from week one. It actually starts from February, which is week six. So we have to use a formula combination which basically searches up whatever figures reported in a weak reference after W and returns the result to you. So you see that it combines four formulas, trim, right, Len and find. So I have to apologize because we're not going to look into this combination more further, but just write it down from the screen and feel free to play around in any similar use cases. But to repeat again, it returns to the value after W, which for us is the week number, which we then can combine together with the word weak in the outcome of it. So it has retrieved as a full week name. Let's copy it to all other columns as well. And here we go. Our template for forecasting is set up. 30. S5 L3 - Setting Up Forecasting Formulas: Let's now start with the forecasting. Eventually by the end of this lecture, we want almost all the key formulas to put into this tab. I'm going to close this top barrier here so we will have better visibility to the file. And we also don't need to have all those bold metrics here because we're going to define new formatting here. And let's start with the most important key metric, which is going to be the number of states. And this is for sure one of those metrics which we had to manually forecast, surrogate, and just make it bold. And I'm going to also add border here. We couldn't pulley from the budget as we did for opex because we have seasonality in the month and having a flood forecast between the weeks in a month, it wouldn't be in this case, the perfect solution. We want to be precise in this case. So let's just hard insert this number we're expecting. So let's look at actuals. So when I actually look at those metrics, I see that in Week 9, we actually had a trope. However, if I now look into our actual step, I see that actually Week 9 doesn't have five days in a week. It should be seven in order to be counted as a full week. Which means this is actually the ongoing week for now. And we shouldn't even look into that. So we can estimate that this is actually the latest complete week for our reporting. So just considering that we had 2.8 k number of states in the last week. So let's just hope that we're doing 3 thousand in the next week. And let's just say that it will be increasing by 2% every week. A hard estimate, we can now calculate the growth in stays merged with differ, as we learned in the last section. So you see, it also showed that we should be increasing 2% every week, which is correct. It's the same assumption that we used previously. So you see, we didn't also get any errors here this time. If we had number of states, we can just add estimate for the average stay price. Well, let's say around 80. And we can just use the same estimate in all the weeks. And based on that, we can calculate the GMI because the GMV is number of states multiplied with average stay price. If we would add the forecast to the commission rate, we can calculate the revenue. So let's just add a summation. So historically we have seen that it has been around, let's say just 14.5. So I'm just going to add here 14.5. I'm going to replace it everywhere. And now we can just calculate the revenue, which the GMV to the commission rate. And now if we add testament to the disgust and subsidies to GMB ratio, we can actually calculate the discount. So subsidies, as said, it's actually much harder to have everything calculated as ratios because it's much easier to actually put that into context, whether it's actually going great or not. And for the time being, we can just say that, Okay, safe bet that most likely our estimate will be around 5%. So we can just use that. And again, basis formulas not soon as many formatting and now we can just calculate the discount voucher subsidies ratio from multiplying GMV with the discounts and subsidies ratio to GMB. Based on that, we can calculate the gross profit, which is the revenue minus the Scantron subsidies. And we can also get the gross margin. She is gross profit divided, which CME. We can use the same formula to reach the OpEx, the same format that we actually use in the actual steps. So I'm just gonna copy it here and put it here. So let's just check 13 thousand. Yes, it seems to be correct. Coping it here. If we actually deduct Opex from the gross profit, we can reach the cashflow. And same. We can now estimate the cashflow, the GMV metric. Let's go forward. We have all the financial section now completed. And as I said, whenever possible, is useful to use ratios for forecasting. So instead of estimating the number of active homes, I rather we'll estimate the state per home, which when I actually look, should most likely be around 3.7. Let's just use the estimate. And we can just link to all the other places. And now as we have stays per home and number of states, we can calculate that in homes. By dividing number of say's, we stays for home. And the exact same thing we can actually do with available nights because basin available nice per home. We can actually calculate the available nights. So we can just estimate that this might be around 5.5. And we can just link it here. And based on that, we can now calculate the available nights by multiplying the active homes with available nights per home. Based on that, you can actually calculate the utilization per home. Similarly as we actually did induction step. So for that, we can just divide the number of stays with everything available that week. So you see how we're progressing. Everything is almost then you cannot forget the remote homes. We can just have random ones. Maybe 40 can just link it here. And based on that, you can estimate how much new homes you got when deducting one week's active homes from the previous and taking into account remove homes. And the rest of your results. Basically what it does is that it measures the difference between the active homes. It checks how much we have removed from our platform. So how much do we need that in addition to actually reach to that number? And we can also put it to the ether. And this time I actually bought it with 0. Because you see what happens here. If you don't use defer. It goes value. So we are actually using it here. And we have the Home section also completed. Similar approach can be used in the customer section. I can forecast stays per customer, which most likely can be, let's say around 2.2. And then based on that, calculate how much you need customers we could expect to have. We can do that by just dividing numbers stays with the states per customer. Estimate how much we will actually adjourn, which let's say is around 100. And then based on that, we can use the same approach to estimate the new customers. So it's also put it to the ether. So here it is. And all our metrics are now redefined. Theoretically, you could just leave it like that and just continue forecasting all the metrics in a way how we just did. I just checked our historical performance and then insert that they expect a metric for guests to here. However, there is a way to automate this forecasting even further and make this template to look at historical metrics, instead of you having to do it manually. Stay tuned for the next lecture. 31. S5 L4 - Advanced: Connecting Stable Metrics to Dataset: Hi and welcome back. We have probably runs the most complicated formula we're going to build during this project. But it will pay off big side. So remember when we were creating this template and always looking back for the historical performance of each metric and then filling it in manually, there is actually a formula combination that could do it for you. And this is the combination of indirect and address. So be clear actually to execute this formula, right, we're going to use a combination of 10 different formulas and we will list them all, each other to make the Mexico's right. But don't worry, we're gonna take this formula, the small basis, and go over everything step-by-step. So even if it actually seems a bit tough and maybe even a bit overkill, don't worry, this will be off big time. So I'm just reminding you what we did in the key formula learning section with indirect address formula, we use row number to identify the row number of the cell column, to identify the column number. And then we combined it into that formula to get the reference of that cell and indirect to get the value of this reference. So that's exactly what we're going to replicate in our forecasting tab. But we want to take the average of last three complete weeks actuals and return this as a value for us. So let's just start going. So let's just start with the first metric of states per customer. I'm just going to highlight it as being so we actually see where we are making the changes. And let's set the beginning, tried to define the location of the last actual week. So the last actual week, which was actually the complete week, was Week 8. So let's just try to make changes to this one. So I'm just going to remove formatting here. So we're looking on at this one. So for that, let's first identify the role of the cell and how we can do it within next image. So let's just look up for the week gate stays per customer metric. So I'm just going to define the whole area here. Lock it, use the match. So actually search for states per customer. Just going to freeze the column and look it up from here, except mesh. And we're now doing the same with a weak reference. So I'm just opening this era here. Looking up for the weak reference, freezing it, looking that from here, and closing it. So this is actually the states per customer metric itself. But let's find the row number. So the row is actually 26. And we can actually do the exact same thing for column. So I'm just going to define that, this formula here. So we actually start putting into metrics right away. So I'm just going to copy that, put it here. So we have the column. And now we're also defining a sheet which is going to be the actuals. And now we have to put it to the indirect. So actually retrieve this value. So you see, we actually retrieve the value of states per customer in here. So you see when I actually just put it back to the previous row, then you actually see is now 2.5. So basically what it's currently doing, it is just extracting the same metric in that cell. You see, remember what we actually were planning to do. We were planning to take the average of the last three weeks actuals. So how to do that? Well, we have to actually use the average formula. And we can't just use it as it is and we have to make it dynamic. So we have to use the average. But instead of average, I'm only going to use savage day. So it actually looks up for the numerical average values in the dataset just to be on the safe side. So we know that actually this one here isolate this complete week. So this is going to be the end of the average, but how to get the beginning? Well, I'm just going to make the copy from this one. Put it here. I will put already a colon here. So it actually starts from looking into, from this to here. But how to we now get it to two weeks before. So it doesn't really take the weak gait, but it actually takes the week 6. So that's actually easy. We're defining here the column number and you see is currently looking gov for the weak gait. But we have to deduct two from here. So it actually starts looking up to week six. And that's it. Now we have this Statesboro customer to buy CSU 9. And if we actually look at here. The average is 21, 29. So we seem to be on the right track. So I'm just gonna take it here. And just in case, Let's look whether those metrics here are alright, because we already see that there is some kind of error here, but we're going to deal with this later on. So actually we see that for week seven is 2.63 and is correct. For the week six is 2.46 and is correct. Let's also check this one to bind to. And it's correct. So why it actually now start to getting errors doesn't work like that because it doesn't really have anything to match with. Our actual step is actually like ending in week 9. And even if we actually add here any kind of new areas, then it just doesn't have the actual asked to rely on. So basically what we need to add to the actual stuff, we need to understand whether those weeks here actually fully past or they're not. So what we can actually do is that we can add a new line here and add here actual weak identifier. And I'm just going to mark here that those here are actually and this is actually not lecture. So what we can now use is that if this corresponds to actual, how to put it in quotation marks, then use this otherwise given a. So we now actually have to use the formula and we have to index whether this week reference actually stands for the actual weak identifier. Not going to just write it here for the purpose of peace. And then I'm just going to copy it later to the right place. So we actually have to find out whether this week ten is in the list. So eventually, which has helped to look out for those and match the column C. You see that I actually now skip this area because we have to look it per columns into rows. So look up for this week, I'm looking at as well from here. And you see that it actually gives error. So I also have to put this to the IFERROR statement. So we now check up whether this is anything than NA. So I'm now just going to copy this formula to here. So if this is anything except NA, then use this formula. Otherwise. Let's just write the POS for the time being just to test it out. So you see that it actually works. And you see all those periods where we had three complete weeks already underlying, then it actually uses the data. And for the other periods, it is the metric that we defined so past. So let's just change it and let's just estimate that whenever this is the future period, we're just going to use the previous metric here. So it always uses this as a forecast. And you see everything works. This formula ensures you that it will always had a forecast based in the last three weeks average. However, it requires that you will keep your eye on the forecast for every upcoming week. Because once you have new week's data coming in, your latest week forecasts won't change. And you can still compare you how accurate your All the next weeks will change. But those are the metrics that should be always suggested anyway when there is a problem. So this is basically just a semi-automated version. How to add the most likely ratios to the forecast to make it a bit easier for you. But you still have to be on top of all the forecasts that you're doing. That's why I call it the semi-automated formula. Automatically will provide you with a forecast. However, you have always a power to overrun it manually. And if you have built the formula properly, we can just copy the formula and basic to any other cell where we want to apply it. So let's just see stays per customer. If we have automated this metric, we can easily automate the average stay price. So you see it changed. We can also automate the available nice per home, The stays per home. So basically everything that you have manually forecasts. So also removed homes as a set number of states should always be in a manual. We can also do the churn customers. I'm going to just switch the layout a bit. Well, we can also forecast in a semi-automated way is to commission rates. And this constant subsidies or is also one metric we should remain manually input. Everything else is actually done just to make it more clearer. I'm just going to mark those metrics that were manually forecasting. I'm just going to add a border here. I'm going to make it a bit more red. And now what I can do, I can also apply some kind of formatting to dose metrics, but I want to make it lighter. Then you immediately know what are the metrics that are using this semi-automated formula and you can just make the changes all the way. So what I also want to do is I want to add different bordering to them, but not everywhere. I don't want those borders have overwritten to the top here. I also mark that those are actually all the target metrics. This helps you to actually navigate between the tabs more easily. And you always know whether those are target metrics or whether those are the actuals. So as you see that this last formula is a quite a heavy one, but it serves a great value as well. Thank you for staying tuned. I hope that you got excited from the challenges that we are completing and see you in the next lecture. 32. S5 L5 - Linking Targets with Actuals: Next thing we're gonna do is combining the targets with our actuals into one sheet. So basically what we want to do, we want the staff to show all our actions we have and four weeks where we don't have any actions. I wanted to see the targets. So you see that I have access from week 1, so week 9, however, this is not a complete week. At the same time, I have the targets from week 6 to week 13 covering exactly three months reporting periods. So I would just add additional four weeks into this period. We again get this problem that they're month reference doesn't work because we have only fixed a very small area here. We have only selected the area from C2 to see 10. However, we should replace that 10 with 14. So I'm just gonna make an easy fix. And I'm just going to replace it like that everywhere. And I'm just going to copy all the formulas to here. And those are actually that target periods. So now I have to link the targets from this sheet, this tab. So this is a moment where I encourage you to pause this video and check whether you can figure out how to link the targets from this step to the actual sheet. If you want the hint, they're not going to give you one. So in case you're still figuring it out, don't listen to it. You have to switch the formula, whether this is marked as actual or target. And the second hint is you can do it with the IFS permanent. So ifs formula is similar to if, but with IFS, you can identify multiple criteria for which the equation has to match and then define a desired outcome for each of the criteria with if there is only one option, if the criteria is met, then act as this, otherwise act as that. With IPFS formula, you can add to the formula multiple criteria. So let's check it out. I'll make the change directly to the Week 8, which is the overlapping both for actions and targets. And I'm just leaving this index formula in because eventually we're going to use it as well. Ifs. If this set to actual, then perform this formula that we were using before. However, if this is set the target, I'm also going to lock them with dollar signs so we can actually eventually just pull them downwards. This, you have taken data from the target step. And this is now exactly what we're going to build. We're going to use index for that if you didn't understand already. So this is the whole lookup area. And we're just going to type in the match. Similarly as we have done for all data metrics as well. So find up active homes, locket, searching from here. We want the exact match and find a weak reference. And here it is. I'm just in case also putting it to the fair. Like as we have learned, this is necessary. And now let's try that. If I change this to target, it changes. And this is exactly what we wanted. So eventually I will just copy all those index match formulas and I will replace them with this one. So eventually every time when you actually change this from actual the target, it will change and vice versa. So let me just find all the metrics that we want to replace. Let's just check it for the time being. So Week 7, 6, 76 is a correct? It is correct. And the week 13, active Holmes was 931. We should correct. So it seems to be pulling from the right place. So what we now need to do, I need to just take this formula and detect all the metrics where this is happening. So it's happening for all of them. It's happening for the number of states. For some of the metrics, you see that it's actually performing already calculations before. And this is because we have already put in that matrix, similarly, as we did in the targets. And this actually fine. I actually recommend to use the easy formulas, more comparison index match because then you can just change the formula here if you want to change the logic. Also is much more clearer to every user of the file who maybe is not that familiar with the index and match. So we can just also replace it here. And we can also replace a GME or revenue. And this cancer subsidies. And actually everything else should be working as fine. Yes, that's correct. So let's see what happens if I just change it the target, everything will change. If I just change this to target, everything change. If I change this to actual, then nothing happens because we really don't have any actions coming up. So I'm also going to do mi minor fixes to this step. So first of all, I'll separate weeks by months for the easy reading purposes. So it actually see that January is here. So I'm just going to select all the metrics here. And I'm going to add proper bordering for here. And I'm going to do the same for February. I will add a conditional formatting here. So if this is equal to actual, then you can mark it as read. Otherwise. Maybe let's make it a bit more darker. And we can have that in the same color so it's more unified. So that's right. And now I'm actually just going to freeze this area again. So actually I will see like this everytime here. I also added some bordering, so it will be a bit more better to read. So here we are. We have successfully imported the targets, do the actual step. 33. S5 L6 - Defining Current Week through TODAY Formula: So I want to show you how to automate switching the weeks from targets for actions whenever our new week parses. This is something that we won't be using in this specific template because the time of me recording the video is different from when you are taking this course. Otherwise, all those periods would be shown as actually because the time of this week has already passed. However, one of the key things that you should automate in order to have automated reporting in your own company. Otherwise, you would just need to adjust this metric manually to actually whenever the week passes, which is not bad. And we will be doing this in this specific template, but we can do it better. The solution for that is actually quite simple. You can do it with a easy formula and easily explained by defining what is the date today with today formula and see whether the date has already passed or not. And as we're doing this course at different signs, Let's use different dates in our formula. Check of which was the date of the last completes week Monday for you. I'll write it down to a cell here. For me, there was the 17th of August. In the next cell link this formula, and that's 7. This should give you the date of most recent Monday where the full week hasn't passed it. So actually both of those states should already be done. Because for me is currently 30th of August. However, this is the full complete week and this is not complete yet. So let's just try out the formula. If today is larger than this sit here, return, actual, otherwise return target. And you actually see that it will give actual to both of those weeks. However, we know for a fact that this is actually the complete week and this has actually not post. So how to adjust this? We don't need to compare today with the date here. We need to take this metric back by around six days because then we would just be lacking behind with one week or time. But six, because if on Monday we want to show that this complete week has passed, we have to go back six days behind in order today equation to be passed. If we can't actually seven, it won't work. So we actually have to deduct six from here. And you see if we can't actually five, then it also doesn't work. So it has to be six. So let's just show this sexual, which is correct. And this is not because this week actually hasn't passed. And if we follow this logic, we see that we're always getting the correct result for defining the weeks between actions and targets. So eventually, if you are building this report for your own company, you should include this formula to here and replace this with this metric here. So eventually would be like that. But for me, as 24th of February has always passed, it doesn't really work. So that's why we need to test this approach out under today's date. 34. S5 L7 - Target Conversion from Weekly to Monthly: We now have all our weekly target Stan and also integrated with their actual step. However, is that all we will do here. So you see, we have here the weekend forecasts. However, weekly forecasts are not that common in reporting as a monthly budget forecasting. And eventually, what's the point of having to forecasting lines if they don't match with each other. In order to align those two reporting lines, we should transfer part of the budgets to this file and create a comparison between them. So I know for a fact that our budgets are a monthly basis because we already have worked with them. So we have to convert our weekly targets to monthly targets and then eventually create the comparison between them. So let's start by adding budget forecasts to this tab. And I don't want to compare all the metrics. I want to be brief and compare only the very key metrics. And those are the number of stays and cross margin to understand whether our volumes and profitability are aligned between those data sources. So I'm just going to add few rows here. And I'm just going to call it, but at the figures. So I just have to merge those areas and then just connect them with index match from the date area. So we're looking at for the month reference. And now we're looking into the metric just in case. I'm also coping it here and here. So I'm just going to align them to the center. And I'm also going to reformat the number of states. And just in case checking that in February, we had 12.6 thousand number of states. Yes. Correct. So I'm going to apply the same formatting to here. And I'm just going to extend this area a bit larger. So I'm just going to copy all those metrics and copied here. And what we're gonna do here, we're going to convert those weekly metrics to monthly. So we're going to rename it conversion. So monthly. Luckily, we have almost done this before when we were converting the monthly OPEX figures to weekly. So let's do the beginning. We have to first sum all the number of sales in February and how to get it automatically. We can use the sum if. So, we select here the month area. We're just defining the week. And now we're selecting number of stays area. So in four weeks periods, which eventually is 28 days, because there's four weeks and seven days in a week, we have target that this number of, number of states. Now we have to move this amount here too, one day value. So we have to divide this amount by the number of days that it presents. So this is count how many weeks were included in this amount, which is four, and multiply this by 7. So basically, we have to use the COUNTIF counts for. So let's open this formula once again. So this is the data value, and now we have to multiply this by the count of days in one month, which is this one. And here is to extrapolate the number of the state for the month, which we can now easily copy also the next month and do the same also with the following month if they would have them in the file. So how we will get this metric for cross margin, we can't easily take the same approach using cross margin metric for cross margin eventually is the cross profit divided by the GMB. So what I can do now, I can take the same formula, paste it to the new row, replace a number of states with cross profit. So let's just do it. So cross profit is in a row 37. So this time actually had to review the formula. So I can now just copy it, but that the packets and do the same for here. However, we now don't have to use the cross profit, but we had to use the GMB, which is on the line 30. And it's working. We can just, just in case those to try it out. So if we divide this by this, it actually is nine by five. So we are done. 35. S5 L8 - Comparison between Targets and Budgets: So what we want to do additionally is setting up the actual comparison between the budgets and targets here. First, we're just going to calculate the difference between our metrics. So I'm just going to copy this area here. I'm just going to make a bit more room here. I'm just basing it here. So that's actually easy. I just need to deduct from this, this. And I had to do it for all of them. And additionally, we want to set up the color notifications, whether each month is actually matching with the budget forecasts, and whether the targets are off. For that, we can create an easy if condition for both metrics. If the difference compared to a total amount is in range of 5% for a number of states, for example, show targets met and if not, then not met. And for cross-matching, I will add a bit less room. If the difference is in range of 3%, then show targets met. And if not, then not met. So let me show what I mean. I'm basically adding here a conclusion area. So I'm just starting here the CIF metric. I'm also going to tie it in the apps. So this basically means that it doesn't matter whether it's over 5%, are under 5%. Then show targets Smith, otherwise not met. And the same for all the metrics. What I'm going to doing. In addition, I'm also going to add to the bottom the conclusion for each month. However, the conclusion has to take into account multiple conditions. What does show when all of them are met? If only one is met and none of them are met. Or what happens if the targets are not filled in and no comparison is made. To solve that, we have to use again the EFS formula. So I'm just going to add here targets met and not met. I don't just can separate this middle line. And here's going to be the conclusion area, just in case a more real reformatting. So as a first step, we need to do the exact same merging of the cells. So I'm going to merge those, this, the nonsense, this and this like that. So now I'm actually can account, can't, if in this period's going to lock the rows, how many were actually targets MET? And we're going to align it to centre. And I'm just also going to fix this column here. And we're now going to base a formula everywhere. So you see in February, one of the targets for us actually met and one was not met in March. None of them are actually met. However, I haven't replaced the criteria progress marching. So I don't want this to be that sensitive. I don't want this to be 50 percent, I want this to be 3%. And we also don't need to perform any calculation here. I just can replace the calculation with the metric here. So with this new approach, I actually see then all our targets are met for February, but one of the targets is not met for March. We can now write here the conclusion. So I'm just going to merge again those areas and write the conclusion using that EFS formula. So if this is equal to two sin right? On target Smith, if this is equal to one, the right, some target Smith. And if this equals to 2, then you can write not met as it's written here. If we don't have anything inserted here. So none is actually filled in. If this is actually equal to 0, then we have to write not filled. And just in case I'm also pulling this to IFERROR. And I don't want this to be shown as empty because it might trigger some errors. Sum. Now copying this two here, I'm just in case just looking over all the formulas. So it seems to be correct. And now we can turn those notifications into color-coding. So I'm adding a new row under here. And I'm linking it with a conclusion cell. But first of all, I'm just making it as merged area. So I'm linking it with this. And same for here. And I'm going to now use the conditional formatting. If the conclusion is that all targets MET, show both the text and background as green. If this is not met, then show as red. And if some targets are met, then show us light red. And the same will actually happen for not filled. And I'm just gonna make it a really narrow line. Painted texts to the same color as the background, just in case to avoid any unprofessional coloring for the future. And now we're done. 36. S5 L9 - Notification for Reconciliation with Budgets: It's so great that we have proper controls in place whether our weekly targets really match with budgets or not. However, to see that we have to every time count ourselves whether mostly the targets are properly set or not. And this might be easy when we have only two months out here. But in reality, we never have a reporting file with only two forecast periods in it. For that, we want to ease her life and create the full conclusion for the whole file. Whether the weekly targets match with the budgets are not. And that we want to have as a textual form of saying your targets match fully with the budgets or not. And as you might understand, it basically takes together the whole summary area of this file. So eventually what we want to do, we want this textual summary to be shown here. Whether your targets match. We want these to be the area for the summary. In order to do that, we're going to create a new area for our calculation sheet and define whether our forecast mostly have a match or not with COUNTIF. So I'm just going to add here a proper area. So what we need to do, Let's rename this area as reconciling targets with budgets. Have it like that. And now here, just gonna do a bit of formatting. I'm just going to add all the conclusions that we have in place. So which are all targets met, targets meant and not met. While we want to do is that we want to count how many of those we have in this target syria. And then based on that select, which is the most common one, the most popular one in the list. And then right here, here, what should be the notification which then should be presented in here. So let's see. Let's also add here the count and the message that we actually want to say. So first of all, let's just count. And we can find from those areas this result. And the same we can actually do for all of them. In order to determine which is the most common metric, we can define it by max formula. And then we can just combine it with index. And in the match lookup where dislocates. And you actually see if I'm just making it a bit better to track, it kind of shows that all the targets are met. Well, actually it's not correct. However, there is one nice way how to solve this with still using similar formula setup as we rather want to be more conservative in our conclusion. So when we have to decide whether between all the targets met and some targets met, we're going to go with some targets. And when we have to decide between some targets are met and not met, we're going to go with not met. And how to do this. We can just add row number here and divide it by a big number. I'm just going to also put it like that. So you see that the numbers actually became really huge. But mostly thing he said they'd actually now starts to take the more conservative approach. So whenever those two are equal, it always selects which is in the back of the list because row number is higher for that. And now we just have to add a message to every result that should show when each criteria is met. For example, if we have all targets met, we can write in your targets aligned fully with forecasts in the budget. If we have some targets met, we could say some targets doesn't meet the forecasts in a budget. You can actually rename them however you want to. But this is the way how I'm approaching this. And if we have none of the criteria met, then we could say your targets doesn't meet the forecast in the budget. So please revise either budgets are targets. And we're going to take the conclusion similarly to the previous formula. So this one. And now I want to show this conclusion in this step. So the only thing I'm gonna do, I'm just going to link this area. With this, and we have it done. And I'm also going to add some color coding to the metric which shows us read when the targets are not met. So I'm just going to add a new rule and I'm going to mark it as read. When it says doesn't fully meet the forecasts in a budget. And I'm going to do the same when the targets are not filled in. Because sometimes when none of those metrics are actually filled in, you get here the conclusion that is not filled in, and you have to act based on that. So I'm just going to add here. If here not filled, is anything equal or higher from one, just mark your targets are not filled in. Otherwise, you can just show this calculation. And this is actually a formula. And I can just use this one similarly in here. And I just want the text to be written as white. And I'm also going to do some final touches to the file. So first of all, I'm just going to add a border here. And also to here. You have to do them separately for some reason. And here I'm also going to group this area. And as you see that the plus and minus sign, how to open that is on the bottom. Then I just have to drag this to here. So you can just easily just close it. And we actually don't need those bottom areas here. It can be without them easily. So I'm just adding a border here. And we also don't need this one to be shown. So you see it will just remain like that. So I'm also going to freeze the target area here. Similar labs we did for the actual step. And what I'm also going to do is I'm going to have adhere and notification. And identification should basically say that this is actually a semi-automated that metric. So you can just say, use the preset formula in cases you expect no major changes from usual business. And also say that the preset formula indicates to the last three weeks average. So you can now just copy and basically added to all the metrics which had this semi-automated formula inside. And that's it. We have finalized our targeting tab. 37. S5 L10 - Sections Recap: Congratulations. You haven't matched arrived to the end of the targets second section and an amine sign created Su CHI sheets in the automated reporting. Fun, Let's go over what we have gone through so far. We have practicing mix and match to a very high extent, we, the purpose of you being able to write this formula almost during your sleep, we have learned how to transfer a small airplane datasets into a ratio based reporting to start, which really helped it to relatively track your performance. We practice how to come up with a fully automatic once you've been better, semi-automatic reporting for guessing method, which decreases the manual work for reporting. Ai really allows you to think through the most important metrics we have learned how to keep your dashboards simple, useful, professional, and automatic, all at the same time. And we have put together quite complicated formulas, such as grading formula from 10 different formulas. I hope that you have got some really good learnings from this course. And I really appreciate if you could share your learning. So, so with me and we potential other students as well. Thank you so much and see you in the next lecture. 38. S6 L1 - Building the Comparison Between Actuals and Forecasts: Welcome to a new section of the course. So far we have completed so Keats apps and our actions and targets. But if we already have both of them flowing each rep file, we should those to compare them. So actually understand how accurate we have been in the past. So in this section, we're going to build a comparison between the actual and the targets. We're going to have targets in the first column, actions in the second column. And then the difference between those in the next column. And we're going to have this for multiple weeks. Let's get started. 39. S6 L2 - Defining Sheet Structure: So the first thing we're like to do is to finding the data structure. In order to do that, we're going to make another copy of our actual step because this already includes the key layout, but we're going to have in a tab as well. And once this is done, we're naming it doesn't comparison and deleting everything that we don't need. So let's first delete the formulas. Then let's also delete the target and actual references from here. So we can also rename this one. And we're just going to extend it a bit as this is a weekly comparison. We also don't need the month grand princes or a number of days in a month. So we have now the key layout ready. However, I don't want to add comparison view in this case for all those weeks. First of all, we have our target setting starting only from Week 6. So I'm going to delete any previous weeks from the view. So this leaves us with eight weeks period. But for the purpose of exercising, we're not going to make our template that complicated and we're going to limit only for three weeks, let's say for two weeks where we already had the actuals. So week 781, week from the actual forecasting period where we really don't want to have any full week past. So let's just adjust. And I'm going to delete all of them as well. And now once we have decided on a weeks, we're going to use in this sheet, we can set up the template and you see the weak references are not really good. So I'm just going to take the formula that I have here. And I'm just gonna copy it here. So asset, we want to have three columns for each week. The target, the actual, and then the variance between those two. So I'll add two columns after every week. I'm also going to have to hear. And so here I want to paste the formatting to hear. But for that, I need to unfreeze rows. I also need to emerge this. So I'm just going to copy this and paste farm autonomy. So in this case, and now we're going to name it as target. This will be actual, and this will be the variance. Copy these two every week. And now I'm only going to add here the borders. And the same for here and here. So I'm just going to merge all this area and named the 2020 because this was the reference. And I'm going to delete the borders from here. So I would just also add bordering to here. And I'm going to merge those areas horizontally with. So what I'm going to also do is that I'm going to populate those areas here. So I'm just going to link it like that. I'm just going to copy it everywhere. And there's a last step. I also would like to use later that actually weak identifier, basically only to understand where it's actually show the data. And we're not just going to use data from actual step and take it here with index match based on the weak reference. And we are done. 40. S6 L3 - Connecting the Formulas: We have our tab or set up, and now we want to connect the targets and actual staff to this tab here. So basically, if the header here says targets, then pull data from the target step. If this says actually pull data from the actual step. And if you think about that, we already have this formula almost ready in actual step. So you see, it already says if this is actual copy data from Black-Scholes, and if this is target, then actually copulate that from the target step. So I'm just going to copy this formula and base it here. As a first thing, we need to just replace where those actions or length. So here, I'm also just going to freeze the row so we can easily just drag it down. We have an active homestay already here. So let's check Week 7. Active Holmes was 846. So I'm just going to check This is correct. So I'm just going to copy this to all the places and let's see what happens. So you see, we actually have all the targets populated, but our actions are missing. Do you know why it's so? Well, if you look at this formula, when this is actual, it takes data from the data deaf, but in the data that we only have the raw data, we don't have the ratios there, which we have calculated only in the actual step. So instead of taking a data from data, raw data that we actually just need to rewrite this index. So I'm just taking all the data area matching the metric that we have. I'm just also going to freeze the column, looking it up amongst metrics. And I'm also going to need the weak reference, which I mostly precinct to the row. And we have the data here. Let's try again whether it works. And it works. Let's just in case also look up some numbers. Let's try the number of states. So actuals in week 7 was this number 2.5. Okay. It's correct. And the targets were slightly arbitrary, 1000? It's correct. So it seems to be working. 41. S6 L4 - Not Showing Data for Unpassed Weeks: So as our formula seems to be correct, I'm just going to copy all these formulas to the weak gait as well. Just in case doing descending to check target for a weak gait for in gmb was almost 250 thousand? Is correct. And for actuals us 219 thousand. Correct. So I'm also going to copy it Week 9. So let's check that CME for week 9 in the targets was 254 thousand? Correct. But you see what happens now? We have the targets and actuals always the same. And this is because the Week 9 hasn't passed in the actual step yet. So luckily there is easy fix for that. We have already included here the actual weak identifier. So we can actually define if this week hasn't passed, don't show the date, the induction step. So what we need to do, I'm going to adjust the formula in the actuals. So I'm just going to drag it down and I'm going to add msb beliefs here. So I'm going to introduce a formula. And, and what it basically does, it just allows to define two if statements. So if this is actual and if this is anything, but NA, then don't show anything. So I'm just going to copy it everywhere to see whether it works properly. And it works. So basically, every time when this hasn't passed and this is actual, don't show any data. So I'm just going to also try it in here. So you see they'd actually now shows the data because this is actually a date. And here as well, this hack ensures you that you're not showing false comparison between the targets and actuals. And it also makes your dashboard to look more professional. 42. S6 L5 - Calculating the Variances: As you might have noticed, we have the data field for the targets and for the actuals, but nothing on their comparison yet. We're going to continue with that. Creating comparison between your actions and targets is important so you understand what is the level of your forecast accuracy. So you can be precise or in your business planning. Because eventually the more you know how your business will be trending in the future, the more confident you can be in the strategic decisions and all the plants are doing in relation to your business. So basically what we're gonna do is I'm going to add a simple formula to every row here, where I'm going to deduct our targets from our actual results. So it calculates how much we were missing in order to achieve the target. And luckily, I can use the same formula for all of the metrics. I'm only going to remove the conditional formatting from some of the tabs. So I'm just, I'm going to copy them here. And I'm going to replace the column letters. Same only, also going to copy them again. So when you see when I copy the same formulas to the last week where we don't have any actuals. I get all the differences the same value as the target. And I don't really like that. So how to solve this is to add any formula here. So if this is opposite value from the target, so minus this, show nothing otherwise, show the formula. So let's just check whether it works. I'm going to choose test it here. And now I'm going to copy the same to here. And the formula works. 43. S6 L6 - Conditional Formatting for Variances: So we have the variance is calculated. But I don't want to spend so much time in understanding whether the variance is actually worse than targeted. You can of course understand this by looking at the metric and then looking at actual and target as well. But I want this to be clear immediately. So there is an easy fix for that conditional formatting the variances when the actuals are performing worse than target that's colored them red. And if they are performing better than target, that color them green. However, I just can't randomly color all the negative values as red because some of the positive variances might also actually referred to the worst performance than targeted. As an example, the remote homes or that's too high spent, which just made a spent more money than we planned. So we have to reveal what we're going to mark and whatnot. So let's start by defining which metrics should go read when there are below sera. And if you look at that, those are actually all the metrics that are not about Sharon. So it's not removed homes, It's also not churned customers because we don't want this to be big, the same for the costs. We don't want to cause to be too big, but he said everything else. So now going to the Conditional Formatting and everywhere where the value is greater than 0, color only the text as dark green and the same if those are less than 0, think color them dark red. So let's see whether it makes sense. I'm just going to switch the coloring like a bit. So this is better. So if the cross profit was not performing as target that so we actually achieved lower gross profit, then he should be read, That's correct. Say for CI and be the same for number stays the same for active homes. And if the utilization is actually higher than it also should be positive and the same we stays per home and average price. So it seems to be correct. So what we're gonna do now, I'm just going to copy everything and we're just going to paste it here. So now you see that actually everything on the color-coding Gosset came along. So just in case coloring all the texts here as black. So once you actually change the formatting is should be fine. And you'll actually see now when I also selected them, it shows that they had the previous conditional formatting in here, which I don't really want, so I'm just going to remove it. And one thing that we missed to do is okay, we just colored all of them. But what happens with Dawn, the churn metrics, those we actually have to format as well, but in the opposite direction. So I'm going to make a copy from here. I'm going to delete all the ranges. And now we're going to copy all the metrics that we just removed. So removed homes, churned customers all the costs and OPEX. And we have to actually also add that this is less than 0. Now what I'm going to make a copy of that. And as if this is greater than 0, then used to read one. And now we're again, cannot copy everything here. 44. S6 L7 - Conditional Color Notifications: We have now finalize the various formatting in a tab. But I now would like to take this even to the next level. So for now I know that if this metric is red is performing worse than plant. However, I'd like it to show explicitly when the metrics are really off from the actress and when I should pay even more attention, how I want to solve this is using the color notifications. So when the metric is really off, I want the color pop up next to the variance. And with the purpose of showing that your performance really was that as good as targeted. So in order to do that, I have to add additional column here. And unfortunately, it already messed up my bordering. So I'm just going for the time being, just delete all the borders here and put them back here. So I'm already also doing the bordering. And here I had a darker border. And also we didn't have any portraying here or here, or also not here. So now it's better. So I'm going to combine this formula. We differ as always, and I'm going to calculate the difference in metric, which is the variance divided with the target. And we're going to format this as a percentage. So I can just copy this to everywhere. However, you see that it works perfectly for the new homes, removed homes and so on. But I don't want it to really do the same for the percentage values. I would just link then we do variant cell and keep it as is, because I'm already calculating the percentage to make everything relative to each other, not taking into account the absolute values. I don't want to calculate the percentage out of percentage. And now I simply have to add color formatting to here. Similarly while we had before. So I want to use a benchmark of 15 percent. When the difference is higher than 15 percent, show me the color notification otherwise is not relevant. However, this 15 percent works only for the values which were calculated from absolute values. I want to see the notification earlier for the percentage values. For example, I want to see immediately when there is a big difference is in this band, which I also need to link with this. If I would apply our notification from 15 percent, I might miss cell the huge bending if I rely only on color-coding. So I will first map down only the metrics where I can allow 15 percent of difference, which are a majority of them, basically everything except utilization, commission rate and discounts and subsidies ratio, cross-matching and cashflow ratio. I'm just going to add them as value. And if this is actually greater than 15 percent, the show it is very positive metric. So you actually now see that it marked correctly that new customers is a positive one because it has grown a lot. However, that this cancer subsidies, which I previously used with negative sign is actually positive, which is not the case. So luckily there is an easy fix for that. So all of those metrics that we before Mark separately, so removed homes, churn customers and so on. We actually have to mark it with negative signs and also about the churn and removed toms and OpEx as well. So now it's better. So I'm now going to duplicate this rule and I'm replace it. If this is actually less than minus 15%, then actually color-coded as very negative value. So you see, now it's much better. And now we actually have to mark all the metrics with percentage that we previously didn't mark. So I'm just going to select the utilization cross the number of states commission rate. This comes the subsidies ratio, cross margin and cash flow ratio. I'm going to add rule. And again, if this positive, so I actually in this time allow 2% of difference, colored them as positive. I'm going to do the same for negative values, so less than minus 2, and it's much better. I also have to track this up and it's working. So some of the things that I don't like now anymore, I don't like that those metrics are included here in the color formatting. So it just had to go here. And I just have to delete the F column from here. The same for here. And we'll start on the top. Now we just have to write them. And similarly, we should also do it here. So it's a bit of manual work, but eventually you will just end up with a much more cleaner file. And maybe few months from now, you are appreciating that you're actually did this work. So it doesn't actually feel that messy. They're just going to take this area here. We should just delete it. And you're going to add it here. And now it's much better to think that I'm now doing. I'm now making it much more narrow. So I'm going to add it maybe like four. And I'm going to color on the text as white and make it really small. And I'm also going to choose a font which looks really small. So maybe this one. So you see, now everything is working properly. I'm now also making the new columns here. I'm just going to merge all those areas. So now I'm going to copy it. Here. I'm just going to delete all the formulas inside. Copying it and pasting it here. This one, this one, I can merge this as we know. This already had the formulas. So now I can also copy the color formatting. I'm going to just change it to four. And this as well. And I'm just going to overwrite the bordering and here as well. And they also John like this formatting here. So I'm just going to delete it. And now we can just go over everything and validate whether it works as required. So we can check by just looking whether the variance and the color beside that are actually the same color. And it actually seems to make sense. 45. S6 L8 - Final Touches: We have reached the end of another section. So let me just do some final touches, but it already looks pretty decent, just in case extending this area a bit, also going to aligning it down. So what I now like to do, I would like to see what is the column size. And just for these purposes, I'm just going to add it everywhere. So when you actually switch between the tabs, it doesn't bother you. And you see that this area always looks the same. So you see now whenever you actually switch the tabs, they look similar. So one thing that I would like to do, I use this red color only for textures. So as this is not actual step, I'm just going to repaint them and I'm going to paint them together with the targets. And actually that's it. Congratulations, you have finished another section of the course where we learn how to create a proper comparison tab between your forecast and actions. You can use this methodology and creating any kind of reporting fast. Because in finance, you always have to be on top of the forecasts at the more precise or you are, the more value it can give to your actual business. I hope that you enjoyed this section and I can't wait to see already in the next one. Thank you. Hi. 46. S7 L1 - Designing the Landing Page: Welcome to a new section of this course. We already had treated taps and our file and we know that there's even more coming up. So make navigating in this file easier and to have a key sheets which shows us an answer points this file, we're going to create the landing page. So reporting file, which gives a brief overview of the file structure and narrative it to the viewer. 47. S7 L2 - What to Include in the Cover tab?: Asset, the color of the reporting file should give the viewer of this file the brief information what this file is all about and made the navigating inside the file easier. And we also don't want to overwhelm the Safavid information because we already have so much data in other tabs. And this SAP rather worse, is an aggregator of everything. So for the start, Let's just create a new tab and let's call it the cover tab. Let's just start mapping out everything that should be included in the landing page. I encourage you to pause the video now and think about it yourself as well. You already know what this file is all about. What are the things you believe a first-time viewer should get from the cover page. So let's start mapping them out together. First thing that comes into my mind is what this file, Lebanese new mirror doesn't know whether this is Daniel budget or maybe a market research. Well, for our case, as we have weekly data in this file, this week reporting file. Second thing I'd like to map down is for what region, country, or city this file is for, whether it's maybe global data or for something specific. For our case, we have it for the Netherlands. Third thing is what period this file is four because you don't know, maybe this file is outdated for us. The period starts from January 2020. Next thing I'd like to know is what data this file actually includes. We had three sheets, one for actions on, for targets, and third for the comparison. And then ideally I'd like to get a summary of key metrics for the latest complete week because this file is for weekly reporting. And just from the top of my head, it would be GMV, number of states. It's growth over the past week. And the cross margin. And the last thing, as I want these weekly targets here to match we always with the budgets. And they had a notification in that itself. I want this cover page to give immediate notification whether my targets mesh with the budgets or I need to adjust anything. And that's all the main things greatly out of this file. So we have mapped out here all the things I'd like to be presented in the landing page. So now we just need to distribute them across the sheet. So first of all, the landing page shouldn't be bigger than the computer screen, should feed to the most common laptop, which is around 13 inches. So you see that I actually have here the layout of the computer screen, which is 13 inches. So I'm just going to remove excessive rows and columns from the tab and make the area smaller. So everything from here. And I'm also going to remove everything from here because I know I'm gonna make it tighter. And now we come to the formatting and layout. I want the name of the file to be almost in the middle of the sheet. And then once a key information to be next to that inside like a box area. So let's drag it to the right places. Let's put it like here. Let's put it here for the time being. I want the periods to be under the filename superior. It goes here. The country name can remain here. And they wanted table of contents to be under here on the bottom and the notification whether the budgets are met. I want to be running across the step here. So I'm just going to delete it and I'm just gonna reframe the file. So I'm going to use more narrow areas. And they also usually likes to resize those areas here. What I've referred to do is that I use a lot of darker colors in my files just because it gives a nicer contrast to the files and it will match with any of the colors I'm going to combine here as well. So I'm just going to use darker colors here. This one. Yes. So I just colored the top and the bottom of this tab is nearly black. So I'm just merging all the area here which shows budget notification. And then we'll link it with other tab. Just going to align it in the center. And it will just make it slightly bigger. Here we can have the summary of last week actually. And we can just name them GMV, number of states, growth in stays, and gross margin. I can just make a slightly longer and I would just frame everything around. Now, I'm just going to use some