Matt, this is the most Holistic video series on data analytics, all the videos I have checked out have never followed this approach, I really appreciate what you have created here. Thank you so much.
I appreciate how organic this video is. Hearing you talk about ideas that you come across gives us a picture of what kind of questions we should be asking ourselves. You also addressed being cautious with the analysis i.e. the "elevator pitch".
That was my sincere hope, so I'm glad to hear you found value there. So many instructionals are so sterile and direct, I wanted to try to give a more real-life scenario like we were working together on a project.
Although I am from Nigeria, But I had decided after these video series that, the name of my first born son will be 'Matt'. Thank you for these videos, thank you and thank you again .I can confidently say I now do know excel now! Although there are still more to learn. Matt pls we do need the forecasting videos. Thank you and thank you again!
Matt, thank you for this excellent series. The candid nature of your data exploration is refreshing. There are always so many ways to chop it up, and learning the approach others take can open up other creative avenues for data analysis. Very nicely presented.
This is really great content. I love that you went through the entire process. Your on-the-fly thinking is really amazing. I would love your insight about going into in-depth analysis when the request is for a simple quarterly summary. This type of in-depth analysis might take you an hour or less, but it would take me half a day. What is the benefit of dedicating so much time to an exploration that wasn't requested? I imagine when you deliver the original request, then a follow-up request might come for an explanation. I have never worked in industry so I am just curious how this all works.
Very important question, and an insightful one as well. My reaction is, as it is to most things, it depends. It depends on the timing you were given, the urgency of the request, and your comfort level with the stakeholder that you can anticipate their questions. As a baseline, it is completely acceptable to "do what was asked". Full stop. But, you will get questions, because that's the name of the game. Over time, if you have ambitions to move up, you need to show initiative and understanding beyond the surface level and start to go through this exercise where you're 2,3,5 steps ahead and can have this pretend dialogue with the stakeholder and anticipate questions. It may take you half a day at the start, but eventually it'll just be part of the process and you'll get faster and more precise. Don't let your current experience dictate, or worse, limit your push to get better. Hope that made sense!
This is pretty much a lot to take in one go. but, I kind of get the whole idea. might need to go back again to grasp it eventually. but, this is great.
Great, Matt! The format of watching you work and think as you go was very helpful. Also appreciate you calling out your keystrokes - I learned some cool new stuff! I did get lost at one point though, at about 27:09 timestamp. I don't understand the formula for cell O41. I thought to forecast Q2 2021 sales, it would be the Q2 2020 sales plus 4% which I thought would be C41*(1+J41). Please explain again why it's C41/(1-J41).
Good question, and the answer is it's not! I don't know why I did that to be honest. Mathematically it works out almost the same and a lot of times I'll see people use these formulas interchangeably, but there are slight differences. Base *(1+growth rate) is correct and I actually use that in the 4th video. Base/(1- growth rate) implies the base is, in this case, 96% of the forecast value and forecast is 100%, not actually 4% higher, but 4%pts higher... which is different. All this to say, it was swaggy napkin math that is often approximate. Not a good excuse, but that's it.
Thanks, Matt for the valuable content. And sure you may upload videos on forecasting and other useful techniques to help us make a more useful analysis
Thank you so much for taking the time to do this tutorial so clear, it has been most helpful. I just have one question on the forecasting section. When you first calculated the growth percentage, that percentage is with respect to Q1 2020, such that volume on Q1 2020 multiplied by (growth percentage + 1) returns the vol on Q1 2021. so, following this idea, the forecasted vol on Q2 2021 should be Q2 2020 multiplied by (growth percentage + 1), but the way you did it, as you explain on 27:30, is assuming that the growth percentage is with respect to 2021 and not 2020. I just want to know if this was a mistake or i'm missing something.
Thanks for the question - yeah I did a bunch of stuff here and not all of it was explained very well. I think the part you're referring to I was attempting to say if we experienced X% YoY growth in Q1, then we could reasonable assume that same growth would continue into Q2. So, by applying this growth (from Q1) to Q2 of 2020 we get a projection of what Q2 could look like if we'd trended out Q1 growth. I go on to show the variances against that expectation and likely this is where it's not super clear why I did that, but basically we're wanting to see if the growth from Q1 persisted through Q2, which it did not - it changed. Which...is normal. Hopefully that helps, but if not please let me know!
I completed a advance excel course on udemy before coming to this project, and I'm still getting overwhelmed 😅. I think I will need to give this project few couple of repeat views to understand better.
@@mattbrattin Seeing you using keyboard shortcuts was little difficult for me to understand what's really happening, and that exploration with pivot table was really confusing for me. But that's not on you, that's me, I'm a slow learner and that's why I said it will take me some repeat views to understand better. And I'm not taking it as a bad thing, its actually good. Nothing which is worth learning is easy. I'll come back again and again on this project series, I think there's a lot to learn for me here as aspiring data analyst.
@@Mr.Potato23 I appreciate the feedback, this is helpful! And you're right, nothing worth learning is going to come easy, so you're putting in the work and I know you'll get there!
Are you still able to do the Forecasting with Excel video you mentioned here? I'm following along this series and it's been awesome so far! Would love to follow more of your videos, Your a great instructor !
Matt, at 24:01, I could not see the remaining code as your video was blocking it. Can you please shed more light on that. The whole concept of GETPIVOTDATA is totally new to me
Dang, all this time and I didn't catch that! Sorry about that, yeah it's similar to how syntax referencing a normal data table will call out the table name and columns in whatever naming conventions you've provided. Main difference is there's more dimensionality involved in a GETPIVOTDATA formula, meaning it's more flexible to reference specifics that would otherwise need to be calculated. The best way to play around with this is to have a pivot table and off to the side anywhere NOT in the pivot table, set an = and just start moving the selection around a pivot table and see how the syntax changes with your reference. That will tell you a lot about the different moving pieces and those you can change around to make for more sophisticated references. Hope that helps!
Thank you so much Matt that was fantastic. I really got surprised by your analytics, as a professional analyst of course it is easy for you to find the ratios and some feature engineering inorder to get a deep insight. As a beginner how can I get skilled in working with numbers and this kinda ratios? I always end up doing Elevator Analysis. How can I change it?
Glad you enjoyed it! We all have to start somewhere and I think the simple fact that you're asking the question is a huge step in the right direction - many people never even get to the point they realize elevator analytics is a thing and that's what they're doing. I would say try not to focus on learning a bunch of ratios - think about data/values and the relationship they might have with other values. Maybe they are correlated? Maybe one drives the other? But if you turn them into a ratio you'll be taking a peek and can determine whether anything interesting is happening. It all starts with being curious and just playing with the numbers you have. Over time you'll get a sense for which relationships in the data are worth digging into. You're on the right track!
Thanks for the work that you're doing Matt. I am finishing my Google Data Analytics Certification this week. It took me three months. I also to Databases and SQL for Data Science using Python courses from IBM. If I may ask, "Is there any way to add this Excel project to GitHub like what we do with other code-based projects like SQL and Python.?"
This is an awesome content. I like how your mind works and how you break things down. Your channel is really a great resource. You just gained a subscriber and a fan. Keep up the good work man. Do you have plans of making a similar video series but on SQL/Tableau?
Glad to hear it! I do have plans to expand into other tools in time. For now I'm very focused on going deep on Excel and data analytics career content.
Good day, Thank you for these projects series Please I'm using also trying to do the projects as well using the file but anytime I try to save my file after adding the pivot chart, it comes with an error, what could be wrong?
In this series I do not do any forecasting. The capstone of my Analytics Career Access program practicum is a big forecasting project, but I have on my content list plans to do a little forecasting off of this data set as well, hopefully in the not too distant future.
At 30:30 I try to explain it a little better, but re-watching this I see I'm moving pretty fast! Basically if we apply actual year- over- year growth from the first quarter, what would we expect the second quarter to do. This is one way to forecast that and compare to actual results to spot anomalies
@@SDbhagtisongs so if Q1 of prior year sold 100 widgets and Q1 of this year sold 105 widgets, that's a 5% growth rate. Now, if Q2 of last year sold 200 widgets and I want to forecast Q2 of this year, one method would be to take my Q1 growth rate (5%) and apply it to last year's Q2 (200). So, it would be 200*(1+5%) = forecast of 210. so if actual comes in higher or lower, I know we grew faster or slower than in Q1. Does that help?
Matt, why am I getting different total clients value at 41:55??? My formulas are correct and everything was correct till I reached this point. Here is an image postimg.cc/NKR3RPfp
The image looks truncated - can you show the formulas in one of the cells for LATAM? I suspect maybe the range is shortened to what would be row 106 in my example which would get the totals you're seeing in your screenshot.
Thanks for the work that you're doing Matt. I am finishing my Google Data Analytics Certification this week. It took me three months. I also to Databases and SQL for Data Science using Python courses from IBM. If I may ask, "Is there any way to add this Excel project to GitHub like what we do with other code-based projects like SQL and Python.?"
Matt, this is the most Holistic video series on data analytics, all the videos I have checked out have never followed this approach, I really appreciate what you have created here. Thank you so much.
I appreciate how organic this video is. Hearing you talk about ideas that you come across gives us a picture of what kind of questions we should be asking ourselves. You also addressed being cautious with the analysis i.e. the "elevator pitch".
That was my sincere hope, so I'm glad to hear you found value there. So many instructionals are so sterile and direct, I wanted to try to give a more real-life scenario like we were working together on a project.
Although I am from Nigeria, But I had decided after these video series that, the name of my first born son will be 'Matt'. Thank you for these videos, thank you and thank you again .I can confidently say I now do know excel now! Although there are still more to learn. Matt pls we do need the forecasting videos. Thank you and thank you again!
Hah, such an honor! I'm glad to hear the videos made a positive impact for you. Cheers!
Video 3. Leaning a lot. Man you should have way more Likes than that.
Hey glad to hear it! Yeah I've not been consistent enough to build a big following, but still try to at least put out good stuff when I do!
Matt, thank you for this excellent series. The candid nature of your data exploration is refreshing. There are always so many ways to chop it up, and learning the approach others take can open up other creative avenues for data analysis. Very nicely presented.
Hey glad to hear it @Eric Robbins - I am glad my transparency here was helpful to see.
This is really great content. I love that you went through the entire process. Your on-the-fly thinking is really amazing. I would love your insight about going into in-depth analysis when the request is for a simple quarterly summary. This type of in-depth analysis might take you an hour or less, but it would take me half a day. What is the benefit of dedicating so much time to an exploration that wasn't requested? I imagine when you deliver the original request, then a follow-up request might come for an explanation. I have never worked in industry so I am just curious how this all works.
Very important question, and an insightful one as well.
My reaction is, as it is to most things, it depends.
It depends on the timing you were given, the urgency of the request, and your comfort level with the stakeholder that you can anticipate their questions.
As a baseline, it is completely acceptable to "do what was asked". Full stop. But, you will get questions, because that's the name of the game.
Over time, if you have ambitions to move up, you need to show initiative and understanding beyond the surface level and start to go through this exercise where you're 2,3,5 steps ahead and can have this pretend dialogue with the stakeholder and anticipate questions. It may take you half a day at the start, but eventually it'll just be part of the process and you'll get faster and more precise.
Don't let your current experience dictate, or worse, limit your push to get better.
Hope that made sense!
@@mattbrattin Thanks! Great answer.
This is pretty much a lot to take in one go. but, I kind of get the whole idea. might need to go back again to grasp it eventually. but, this is great.
So true. It's a great video but actually a lot so I'll keep coming to it. Thanks Matt!
Great, Matt! The format of watching you work and think as you go was very helpful. Also appreciate you calling out your keystrokes - I learned some cool new stuff!
I did get lost at one point though, at about 27:09 timestamp. I don't understand the formula for cell O41. I thought to forecast Q2 2021 sales, it would be the Q2 2020 sales plus 4% which I thought would be C41*(1+J41). Please explain again why it's C41/(1-J41).
Good question, and the answer is it's not! I don't know why I did that to be honest. Mathematically it works out almost the same and a lot of times I'll see people use these formulas interchangeably, but there are slight differences.
Base *(1+growth rate) is correct and I actually use that in the 4th video.
Base/(1- growth rate) implies the base is, in this case, 96% of the forecast value and forecast is 100%, not actually 4% higher, but 4%pts higher... which is different.
All this to say, it was swaggy napkin math that is often approximate. Not a good excuse, but that's it.
Great video Matt! Subscribed and now onto part 4!
Nice, glad to hear it!
16:50 he said “don’t do that”. That’s exactly what I did on my own as a newbie 😂
We all been there!
Thanks, Matt for the valuable content. And sure you may upload videos on forecasting and other useful techniques to help us make a more useful analysis
Glad you found this valuable! I am looking forward to dropping more new content soon
I appreciate You, Matt.. Can You give us Basic Forecasting videos?
This is on my list - need to get to it! Hopefully soon!
Loading the video tonight so I can crush excel on my flight to NOLA!
Get it!
Thank you so much for taking the time to do this tutorial so clear, it has been most helpful. I just have one question on the forecasting section. When you first calculated the growth percentage, that percentage is with respect to Q1 2020, such that volume on Q1 2020 multiplied by (growth percentage + 1) returns the vol on Q1 2021. so, following this idea, the forecasted vol on Q2 2021 should be Q2 2020 multiplied by (growth percentage + 1), but the way you did it, as you explain on 27:30, is assuming that the growth percentage is with respect to 2021 and not 2020. I just want to know if this was a mistake or i'm missing something.
Thanks for the question - yeah I did a bunch of stuff here and not all of it was explained very well. I think the part you're referring to I was attempting to say if we experienced X% YoY growth in Q1, then we could reasonable assume that same growth would continue into Q2. So, by applying this growth (from Q1) to Q2 of 2020 we get a projection of what Q2 could look like if we'd trended out Q1 growth. I go on to show the variances against that expectation and likely this is where it's not super clear why I did that, but basically we're wanting to see if the growth from Q1 persisted through Q2, which it did not - it changed. Which...is normal. Hopefully that helps, but if not please let me know!
I completed a advance excel course on udemy before coming to this project, and I'm still getting overwhelmed 😅. I think I will need to give this project few couple of repeat views to understand better.
That is helpful feedback to know! Are there any specific areas you find that are more confusing or that I could do a better job of breaking down?
@@mattbrattin Seeing you using keyboard shortcuts was little difficult for me to understand what's really happening, and that exploration with pivot table was really confusing for me. But that's not on you, that's me, I'm a slow learner and that's why I said it will take me some repeat views to understand better. And I'm not taking it as a bad thing, its actually good. Nothing which is worth learning is easy. I'll come back again and again on this project series, I think there's a lot to learn for me here as aspiring data analyst.
@@Mr.Potato23 I appreciate the feedback, this is helpful! And you're right, nothing worth learning is going to come easy, so you're putting in the work and I know you'll get there!
This is absolute gold! Thank you and certainly looking forward to your other content!
Glad to hear it! Cheers
Are you still able to do the Forecasting with Excel video you mentioned here? I'm following along this series and it's been awesome so far! Would love to follow more of your videos, Your a great instructor !
Like a video specific to forecasting methods in excel?
@@mattbrattin like you mention at 31:45
Maybe going through some of the most common forecasting methods we will need to know starting a job as a Data Analyst?
@@kevinalexis9886 I did say that didn't I! Yes, I just added that to my content calendar and will aim to get something up!
@@mattbrattin Awesome thanks! Looking forward to it!
Matt, at 24:01, I could not see the remaining code as your video was blocking it. Can you please shed more light on that. The whole concept of GETPIVOTDATA is totally new to me
Dang, all this time and I didn't catch that! Sorry about that, yeah it's similar to how syntax referencing a normal data table will call out the table name and columns in whatever naming conventions you've provided. Main difference is there's more dimensionality involved in a GETPIVOTDATA formula, meaning it's more flexible to reference specifics that would otherwise need to be calculated.
The best way to play around with this is to have a pivot table and off to the side anywhere NOT in the pivot table, set an = and just start moving the selection around a pivot table and see how the syntax changes with your reference. That will tell you a lot about the different moving pieces and those you can change around to make for more sophisticated references.
Hope that helps!
Yes, it does. Thank you very much
make some forcasting method video
Waiting on a forecasting video
It's in my pipeline finally!
Do forecasting plz
Thank you so much Matt that was fantastic. I really got surprised by your analytics, as a professional analyst of course it is easy for you to find the ratios and some feature engineering inorder to get a deep insight. As a beginner how can I get skilled in working with numbers and this kinda ratios? I always end up doing Elevator Analysis. How can I change it?
Glad you enjoyed it! We all have to start somewhere and I think the simple fact that you're asking the question is a huge step in the right direction - many people never even get to the point they realize elevator analytics is a thing and that's what they're doing. I would say try not to focus on learning a bunch of ratios - think about data/values and the relationship they might have with other values. Maybe they are correlated? Maybe one drives the other? But if you turn them into a ratio you'll be taking a peek and can determine whether anything interesting is happening. It all starts with being curious and just playing with the numbers you have. Over time you'll get a sense for which relationships in the data are worth digging into. You're on the right track!
Thanks for the work that you're doing Matt. I am finishing my Google Data Analytics Certification this week. It took me three months. I also to Databases and SQL for Data Science using Python courses from IBM. If I may ask, "Is there any way to add this Excel project to GitHub like what we do with other code-based projects like SQL and Python.?"
Great question, I actually am just getting into GitHub myself (never too late!), so I'll need to follow up on this after I do a little digging.
11:47 LOL
This is an awesome content. I like how your mind works and how you break things down. Your channel is really a great resource.
You just gained a subscriber and a fan. Keep up the good work man. Do you have plans of making a similar video series but on SQL/Tableau?
Glad to hear it! I do have plans to expand into other tools in time. For now I'm very focused on going deep on Excel and data analytics career content.
Good day, Thank you for these projects series
Please I'm using also trying to do the projects as well using the file but anytime I try to save my file after adding the pivot chart, it comes with an error, what could be wrong?
Interesting - are you saving as an XLSX file? If you're by chance saving as CSV it'll wipe everything out...
@@mattbrattin yes, I saved it as an xlsx file
I'll go through your videos to confirm but if you didn't, would you mind doing some forecasting videos?
In this series I do not do any forecasting. The capstone of my Analytics Career Access program practicum is a big forecasting project, but I have on my content list plans to do a little forecasting off of this data set as well, hopefully in the not too distant future.
hi matt, i am not able to understand where you forecast 27:44, can you please clear this concept in more easy language ?
At 30:30 I try to explain it a little better, but re-watching this I see I'm moving pretty fast! Basically if we apply actual year- over- year growth from the first quarter, what would we expect the second quarter to do. This is one way to forecast that and compare to actual results to spot anomalies
@@mattbrattin sorry matt still don't get, i know i am bothering you, but hey can you do little calculation here....Thank you..
@@SDbhagtisongs so if Q1 of prior year sold 100 widgets and Q1 of this year sold 105 widgets, that's a 5% growth rate. Now, if Q2 of last year sold 200 widgets and I want to forecast Q2 of this year, one method would be to take my Q1 growth rate (5%) and apply it to last year's Q2 (200). So, it would be 200*(1+5%) = forecast of 210. so if actual comes in higher or lower, I know we grew faster or slower than in Q1. Does that help?
@@mattbrattin thanku matt, now I understand, really helpful, by the way your content is great....
I got lost as some point, buh picking up
28:20
Nice analysis but you are too fast
Sorry for that, noted!
Matt, why am I getting different total clients value at 41:55??? My formulas are correct and everything was correct till I reached this point. Here is an image postimg.cc/NKR3RPfp
The image looks truncated - can you show the formulas in one of the cells for LATAM? I suspect maybe the range is shortened to what would be row 106 in my example which would get the totals you're seeing in your screenshot.
@@mattbrattin Yes i forgot to select the 3 remaining rows. such a dumb mistake. anyway thanks for replying
Thanks for the work that you're doing Matt. I am finishing my Google Data Analytics Certification this week. It took me three months. I also to Databases and SQL for Data Science using Python courses from IBM. If I may ask, "Is there any way to add this Excel project to GitHub like what we do with other code-based projects like SQL and Python.?"