Do you also want to improve your PowerPoint skills? Here is my course on Slide Writing: link.firmlearning.com/slides Interested to sign-up to my e-mail newsletter? Here you go: link.firmlearning.com/e-mail
Thank you for the well-organized and high quality content!! I have worked 2 internships in the past and had to dive in to gigantic excel sheets everyday. I was able to complete my work but I never had the chance to learn the fundamental techniques on how to build a model from scratch. I am so glad that I discovered your channel and I am definitely benefiting from your videos! Hopefully, I can start my own project at my upcoming internship and I look forward to more of your videos!
Hi Xinyuan, thank you for your feedback, very happy to hear the material is helpful! Will for sure create more videos on Excel in the future! :) Best! Heinrich
Heinrich, your content is amazing. I have watched all your videos on Excel and PowerPoint and they were extremely helpful. Would love to see more content on Excel and Powerpoint. Best regards from Egypt :)
I wanted to be a Management Consultant, but MBB have all rejected me...for now. I'm happy to come across your channel and learn some more about the skills that I'll need when the right time comes. Prost, my friend!
sehr cool. schreibe grad meine Abschlussarbeit im MBA in Form eines Business Plans und bald steht das Finanzkapitel an, für die Liquiditätsplanung versuch ich mich mal an deinem Modell :)
Dear Heinrich, thanks for your enthousiastic and very interesting videos. For this video about SUMIFS I suggest that you use the pyramid principle: first show us the end result (final model) and the purpose of it. Thereafter take the deep dive into Excel details. In the current presentation the viewer is wondering 'where is this all leading to?'. Let me be kind and charge you nothing for this advice 😉 Good luck and I am looking forward to new videos. Paul from the Netherlands
Hi Heinrich, I wanted to thank you for helping a girl out. I re-created the whole model from scratch to learn how you do this and actually learned a lot from the process, getting to focus on the more minor details (e.g. using the helper line for the months - real cool!). Please keep up the amazing work.
Would love to see a video on how you approach market sizing comparing top-down vs. bottom up. Also re other videos could be focused around tools and general structural approach you would take to the top 10 sorts of problems BBM are asked to consult for. Eg new market entry, top line growth etc using MECE and pyramid principle frameworks. Thanks!
Thanks for your suggestion, appreciate it! Yes, will think about how to share some of the problem solving methodology of MBB firms in future video (MECE, hypothesis-driven consulting techniques, top-down communication etc.). Though of course I cannot disclose any specific proprietary IP of my former employer. Stay tuned for future videos! :) Best, Heinrich
Hello Heinrich, i just copy pasted the method for my work :D It is great, maybe a Video about Sumifs vs Pivot Tables/ Charts pros and cons would be great :D
This is great for a beginner, but I’ve never met a colleague that couldn’t do this and way more. The key for today’s world is automating the data import process to be able to run a “model” like this. If you’re just beginning you will need to learn this too.
Great content. Thank you very much! Could you also make a video on how excel tools should look like? How to make them easy to follow. When to use frames, gaps, shades, colors? When using a new tab etc. This would be super helpful as well.
2:15 on MS official explanation about sumifs. 3:40 these are the arguments we saw before: first with sum_range, the range of criteria, and then the specific criteria. 4:25 highlight the month column as the criteria column. 5:30 what if, we want not only the months but the customers as well. 5:45 the easiest way is to remove duplicate. 6:20 then you insert with paste special >> transpose. 6:30 now we use sumifs function with 2 dimensions/ criterias. 7:45 now we need to keep the row constant. 8:10 now we highlight it and copy it exactly the same way. 8:30 sum up these values (how the mouse uses to collect the cells) 9:10 use case: financial model by using sumifs. for purpose of financial planning. 10:20 build this with formula. 11:10 this is how people work today in company. 15:00 in real work, these input may be much more complicated or you, yourself need to build a whole independent model. 16:45 if this is empty: formula 18:00 the model is based on daily value, so here also derive from daily value. 18:45 take the value of respective month. 20:20 this is not daily value, so we want only on this specific date and the next condition, we highlight the date value. 21:55 we want the sum of these two numbers. 24:00 the advantage is if we want to make changes, we can directly alter.
Very helpful video!! Would also like to learn some of the tips you use to speed up the process(seems like one can only use keyboard to finish all the tasks much faster). Thank you for the kind sharing~~
Thank you Victor, appreciate it. Yes, using shortcuts / keyboard in Excel is indeed important to start increasing your speed. Have added that to my ideas list for future videos to talk about that as well! :) Best, Heinrich
I'd like to add two recommendations: First: -cash flow inflows always in positive. -cash flow outflows always in negative -Use only sum function. Second: -When working with big models, never take the whole column when using sumif o sumifs functions if you don't want to make the model too slow. Use colum ranges with a size you think you won't never reach and try to insert new data instead of add so the ranges can grow automatically. Thank you very much for your videos, I wish I could have seen content like this in my first years of career.
I'm spoiled for choice - you have way too many great videos one after another. Very helpful!! Can you also please make a video on what in your view are the next growth industries and markets? I work in impact investing and I think it's the next big thing if done correctly - but I would love some validation.
Hi Ramsha, thank you for your kind reply! Always great to hear that the videos are helpful. Thanks also for the video suggestion, will add it to my video idea list. Best, Heinrich
Hello Heinrich, I would like to thank you for your enthusiastic videos and the work you do on this channel! I highly appreciate it. I love those excels videos by the way !! I wonder if this simple model is just support of the use of the formula. Indeed I thought, in order to know what cash you'll have at the end of the day, we needed to add some depreciation/tax line to forecast it properly (it confuses me a little). I may be wrong and the model you present may be a simplified view of the amount of cash you'll have. Continue with such great work. Mathieu from France
Hi Mathieu, Yes thank you! Fully agree, for a "real" liquidity model you will usually need much more lines for different cash-relevant positions. With my current employer we have a similar model with about ~50 lines. Depreciation is usually not a cash flow-relevant position though. Just wanted to showcase the overall setup, as you can easily expand this to as many lines you need. Best! Heinrich
Hi thank you for always replying back to me. I have an urgent question that I hope you could answer. In your SUMIFS example, how would the formula look if you only wanted to add up the numbers each month that were greater or equal to a certain amount? I hope this makes sense! Thank you again!
Excellent video . I have a Plant Financial MIS report would like develop a model to summarize it monthly and prepare a Live Dashboard which changes as we enter values. Can you please make video for the same
Hi Manojkumar, thanks for your comment. Well that sounds very specific, since this video I already created a video on dashboards though. Maybe check it out? Will create more Excel content in the future so stay tuned! Best, Heinrich
Maybe add a video with one and two dimensional EXCEL DATA TABLE functions to show different scenarios of for example a DCF model and sensitivity to one or two variables.
Hey man i loved your channel seriously. I am coming to germany in 2021 sep and i want to work in consulting firm in the begineeing of my professional career.
Well as a Data Warehouse Engineer i can tell you, Heinrich, that that is very bad financial modelling at 07:00 throwing away the duplicates. You cant redo that when you get the source data a second time effortlessly. Every BI tool can do those things. And honestly i dont know why Analysis for Office does not get more respect from business guys. for me it is the perfect tool. A datawarehouse in the background, slim excel on top. all i want. No need for stupid stuff and dashboards like Qlik, Tableau etc. I even used a data warehouse, analysis for office (excel) and thinkcell for automatic powerpoint generation 8 years back...... one push of a button, meeting preparation done, just had to do the story to tell then Oh man, the more I look at this video I realize, this is exactly the type of my clients. Operative guys in logistics, financial analysts, controllers etc. doing sumifs all day. But the essence of doing that stuff is, that it is on a short term basis only and only useful for single point analysis only. It is not on a recurring basis. Since stuff changes again and again. Structures change (Business Units etc.), months values change (u see Heinrich cheat with rows/cols as "1"-"12" for months lookups and end days. I know that struggle why I went into Data Warehousing. I dont consider it a good modelling, if the model cant be automated for years to come.
Hi, Yes, understand where you are coming from. Fully agree that depending on the use case, other setups, potentially directly building on BI tools etc., can be much more appropriate. Still, what I experience is that in a large majority of cases, people will want you to work fully contained in Excel without dependencies to other interfaces / software or else. The reason is that in large organizations, usually only few people have both the access to the tools you describe and the knowledge of how to use them. Therefore, you risk building a model that you then cannot send around to others (except for read-only) and cannot jointly work on with your clients / colleagues, as they are not familiar with the tools that you are describing. Also, isn't Analysis for Office a SAP thing? Many companies do not work with SAP. Independent from that, working fully contained in Excel is the lowest common denominator as everybody has some level of Excel skills and therefore will be able to work with and later also alter your model. Also, many of these models are not supposed to be used "for years to come" but rather to calculate a business case for a new product, prepare financial plans for an M&A transaction etc. In these cases, the lifespan of these models is rather a couple of months than years, which is why the long term implications you are talking about are not that relevant. But again, fully understand and agree that other approaches do make a lot of sense as well, depending on the use case. Best, Heinrich
@@FirmLearning Hi, yeah i understand, that what you showed is not a full-length financial model. I know those cash liquidity models from reports I have to design on a regular basis at clients. And always those things are then rolling models with forecast and rather complex. And they also focus on basic topics only with limited characteristis to dril down. Yes, Analysis for Office is an SAP thing. But this is not so important, because Excel can read from any Database or Views via Data import. I m just from the SAP world thats why i took it as example. What i wanted to stress is, to make sure, that the denominator is NOT the Excel model itself, but the database underneath. Where a "single source of truth" is kept in the data warehouse (this is btw the whole use case of a data warehouse in a company, to have modeled KPIs that everyone can agree on (part of governance in a company if you do it right). Otherwise every department is doing their own stuff in Excel models and coming up with new KPIs that a new guy or other department doesnt understand ... I'm saying, that the Database itself has to be the basis, where everyone should work on. And trust me, this is standard since at least 30 years in corporates now :) And exactly this is what i dont understand in large corporates, where the systems are there and even fully built and used. And then managers send around excerpts of reports from the data warehouse in offline excel sheets??? this is real bullshit. In Analysis for Office (or other tools), when the access is there, you need 2 clicks to get to the data and even can send around a polished workbook and just click "refresh" and have the latest actuals. This is so basic, but still in 2020 offline Excel sheets get sent around. This shows that people just have no clue how to use their tools and then build large constructs for reporting and modeling around an easy topic. I have seen positions for working students, doing stupid work, really stupid work redoing models over and over each month, instead of automating in 2 clicks.
I just visited your udemy profile. I would be highly interested in a course that deals with excel-skills necessary for top tier consulting. Maybe this course is already in the making? ;)
Have indeed thought about it, though still lots of work to do. Also want to use UA-cam to play around a bit what teaching format for the videos works best. Will of course let you know on this channel if I release a full course on this topic in the future! Best, Heinrich
@@FirmLearning I agree. I've taken your Skillshare powerpoint course and it was excellent. Would like to see a consulting specific excel course. I've taken financial modeling courses but those are very finance specific, rather than operationally focused. Would like a course on this topic. keep up the great content.
Hi Simon, I usually jump around the table with "Ctrl" + the respective arrow key. This way you can jump to the end and then copy the formula. Best! Heinrich
Hallo Heinrich, sehr informatives Video. Ich habe eine banale Frage: Wie schafft man es 01.12.2021 bis 31.12.2021 in Excel einfach aufzulisten, weil wenn ich es einfach runterziehe wird das Jahr immer +1 hochgerechnet, ich möchte aber dass die Tage mit +1 hochgerechnet werden? Wäre mega dankbar für eine kurze Antwort, beste Grüße aus Dortmund
In the Liquidity Overview, the IF F4, shouldn't that be more like iF(F4="";E13;F4+E13) ? I mean, I will have the money that is in my bank account, and I took a loan F4.. or is that an actual bank statement value, so you can update the projection in time with actual values from the bank?
Hi Nicola, thanks for your comment and watching. Yes, will probably release a full video course on Excel at some point in the future with lots of templates, excercises etc. :) Best! Heinrich
How long do you think will Excel remain the standard, now that there are alternatives like SQL+R or Python and tools like Tableau? How important is it to learn these alternatives?
Hi, do not see SQL / R replace Excel, believe the use cases are quite different for these technologies. Tableau is starting to gain some traction, but still far away from replacing Excel. Excel is here to stay ;) Best! Heinrich
SUMIF is often more flexible then Pivot tables (you need to manually update pivot tables etc.) - but of course pivot tables are great as well. Thanks for watching! Best, H
Do you also want to improve your PowerPoint skills? Here is my course on Slide Writing:
link.firmlearning.com/slides
Interested to sign-up to my e-mail newsletter? Here you go:
link.firmlearning.com/e-mail
Thank you for the well-organized and high quality content!! I have worked 2 internships in the past and had to dive in to gigantic excel sheets everyday. I was able to complete my work but I never had the chance to learn the fundamental techniques on how to build a model from scratch. I am so glad that I discovered your channel and I am definitely benefiting from your videos! Hopefully, I can start my own project at my upcoming internship and I look forward to more of your videos!
Hi Xinyuan, thank you for your feedback, very happy to hear the material is helpful! Will for sure create more videos on Excel in the future! :) Best! Heinrich
Heinrich, your content is amazing. I have watched all your videos on Excel and PowerPoint and they were extremely helpful. Would love to see more content on Excel and Powerpoint. Best regards from Egypt :)
Hi Omar, thanks for your comment. Check out the new video I released on Excel today :) Best, Heinrich
Awesome! Thank you very much :)
I would like to see more Excel content in the future!
Thanks appreciate it! :) Yes will observe how this video is received and then potentially make more in the future. Best, Heinrich
Alot of small gems in this video, thanks!
I wanted to be a Management Consultant, but MBB have all rejected me...for now. I'm happy to come across your channel and learn some more about the skills that I'll need when the right time comes. Prost, my friend!
Very happy to hear that the video has been helpful. Lots of success to you for the future Ivan! Best, Heinrich
Very helpful Excel tutorial for financial modeling, thanks.
Thanks, glad it helped!
sehr cool. schreibe grad meine Abschlussarbeit im MBA in Form eines Business Plans und bald steht das Finanzkapitel an, für die Liquiditätsplanung versuch ich mich mal an deinem Modell :)
Hi Daniel, das klingt doch spannend - ganz viel Erfolg dir!! LG, Heinrich
Please upload more Excel Skills! Really insightful;)
Thank you!!
Dear Heinrich, thanks for your enthousiastic and very interesting videos. For this video about SUMIFS I suggest that you use the pyramid principle: first show us the end result (final model) and the purpose of it. Thereafter take the deep dive into Excel details. In the current presentation the viewer is wondering 'where is this all leading to?'. Let me be kind and charge you nothing for this advice 😉 Good luck and I am looking forward to new videos. Paul from the Netherlands
Hi Paul, thanks for your feedback. Yes, that makes total sense. Will take this into account for future Excel videos! ;) Best, Heinrich
Great video Heinrich, the liquidity model was simple but powerful, a great tool for businesses!
Also a McK alum but from the Analytics support team, missed doing this so much haha 😆 thanks for this video!!!
Great to see fellow alumni here in the comments!! Thanks for watching! :) Best, Heinrich
Hi Heinrich, I wanted to thank you for helping a girl out. I re-created the whole model from scratch to learn how you do this and actually learned a lot from the process, getting to focus on the more minor details (e.g. using the helper line for the months - real cool!). Please keep up the amazing work.
Super happy to hear it is helpful - thanks for your kind comment Allegra! All the best to you and lots of success in 2022! Best, Heinrich
Would love to see a video on how you approach market sizing comparing top-down vs. bottom up. Also re other videos could be focused around tools and general structural approach you would take to the top 10 sorts of problems BBM are asked to consult for. Eg new market entry, top line growth etc using MECE and pyramid principle frameworks. Thanks!
Thanks for your suggestion, appreciate it! Yes, will think about how to share some of the problem solving methodology of MBB firms in future video (MECE, hypothesis-driven consulting techniques, top-down communication etc.). Though of course I cannot disclose any specific proprietary IP of my former employer. Stay tuned for future videos! :) Best, Heinrich
Enlightening use of sumifs in planning
Much appreciated
Thank you Arjun, super happy to hear the video is helpful! Best, Heinrich
Hello Heinrich, i just copy pasted the method for my work :D It is great, maybe a Video about Sumifs vs Pivot Tables/ Charts pros and cons would be great :D
Many thanks for this video!
Thank YOU for watching Ruben, much appreciated! Best, Heinrich
thanks for sharing. hope to see more excel skill relevant videos :)
Thanks, will continue creating them in the future :) Best! Heinrich
This is great for a beginner, but I’ve never met a colleague that couldn’t do this and way more. The key for today’s world is automating the data import process to be able to run a “model” like this. If you’re just beginning you will need to learn this too.
Hi, thanks - yes agree it is beginner content! Best, Heinrich
Great content. Thank you very much!
Could you also make a video on how excel tools should look like? How to make them easy to follow. When to use frames, gaps, shades, colors? When using a new tab etc.
This would be super helpful as well.
Hi Simon,
Thanks for your comment. Yes this is indeed a great topic! Might pick that up in a future video. Stay tuned!
Best, Heinrich
2:15 on MS official explanation about sumifs.
3:40 these are the arguments we saw before: first with sum_range, the range of criteria, and then the specific criteria.
4:25 highlight the month column as the criteria column.
5:30 what if, we want not only the months but the customers as well.
5:45 the easiest way is to remove duplicate.
6:20 then you insert with paste special >> transpose.
6:30 now we use sumifs function with 2 dimensions/ criterias.
7:45 now we need to keep the row constant.
8:10 now we highlight it and copy it exactly the same way.
8:30 sum up these values (how the mouse uses to collect the cells)
9:10 use case: financial model by using sumifs. for purpose of financial planning.
10:20 build this with formula.
11:10 this is how people work today in company.
15:00 in real work, these input may be much more complicated or you, yourself need to build a whole independent model.
16:45 if this is empty: formula
18:00 the model is based on daily value, so here also derive from daily value.
18:45 take the value of respective month.
20:20 this is not daily value, so we want only on this specific date and the next condition, we highlight the date value.
21:55 we want the sum of these two numbers.
24:00 the advantage is if we want to make changes, we can directly alter.
This is great content - would love to see more of this!
Thanks! Yes, have some more planned for the future! :) Best, Heinrich
This really help me in my case, thanks
Super happy to hear that - thank you Rio! Best, Heinrich
Great Job! I was familiar with the function used but isolated onto 1 sheet. Referencing another sheet isn't something use commonly. Thanks again!
Happy to hear it helps, thanks Tony! Best, Heinrich
Great content.
Thank you for sharing
Appreciate it! Thanks for watching! Best, Heinrich
great content!! thank you for this!
Super happy to hear that - thank YOU for watching Holly!! Best, Heinrich
Very helpful video!! Would also like to learn some of the tips you use to speed up the process(seems like one can only use keyboard to finish all the tasks much faster). Thank you for the kind sharing~~
Thank you Victor, appreciate it. Yes, using shortcuts / keyboard in Excel is indeed important to start increasing your speed. Have added that to my ideas list for future videos to talk about that as well! :) Best, Heinrich
I'd like to add two recommendations:
First:
-cash flow inflows always in positive.
-cash flow outflows always in negative
-Use only sum function.
Second:
-When working with big models, never take the whole column when using sumif o sumifs functions if you don't want to make the model too slow. Use colum ranges with a size you think you won't never reach and try to insert new data instead of add so the ranges can grow automatically.
Thank you very much for your videos, I wish I could have seen content like this in my first years of career.
Hi Gejolito,
Thank you, good and valid advice. Thanks for watching!!
Best, Heinrich
I'm spoiled for choice - you have way too many great videos one after another. Very helpful!!
Can you also please make a video on what in your view are the next growth industries and markets? I work in impact investing and I think it's the next big thing if done correctly - but I would love some validation.
Hi Ramsha, thank you for your kind reply! Always great to hear that the videos are helpful. Thanks also for the video suggestion, will add it to my video idea list. Best, Heinrich
Great information thanks you
Super happy to hear that - thanks Margaret! Best, Heinrich
Very helpful indeed!
Thanks for watching Natalia!! Best, Heinrich
This is really helpful! Hope you can create a course teaching excel like the presentation course you created. 👍
Hi Xingyi, thank you for your comment! Indeed planning to release something like that in the future :) Best, Heinrich
Great and insightful. Thank you
Happy to hear that, thank you Teddie! Best, Heinrich
Hello Heinrich,
I would like to thank you for your enthusiastic videos and the work you do on this channel! I highly appreciate it.
I love those excels videos by the way !!
I wonder if this simple model is just support of the use of the formula. Indeed I thought, in order to know what cash you'll have at the end of the day, we needed to add some depreciation/tax line to forecast it properly (it confuses me a little). I may be wrong and the model you present may be a simplified view of the amount of cash you'll have.
Continue with such great work.
Mathieu from France
Hi Mathieu,
Yes thank you! Fully agree, for a "real" liquidity model you will usually need much more lines for different cash-relevant positions. With my current employer we have a similar model with about ~50 lines.
Depreciation is usually not a cash flow-relevant position though.
Just wanted to showcase the overall setup, as you can easily expand this to as many lines you need.
Best! Heinrich
Hi thank you for always replying back to me. I have an urgent question that I hope you could answer. In your SUMIFS example, how would the formula look if you only wanted to add up the numbers each month that were greater or equal to a certain amount? I hope this makes sense! Thank you again!
Thats just amazing. Will definitely apply it , my god
Thank you for watching! :) Best, H
Excellent video . I have a Plant Financial MIS report would like develop a model to summarize it monthly and prepare a Live Dashboard which changes as we enter values. Can you please make video for the same
Hi Manojkumar, thanks for your comment. Well that sounds very specific, since this video I already created a video on dashboards though. Maybe check it out? Will create more Excel content in the future so stay tuned! Best, Heinrich
Maybe add a video with one and two dimensional EXCEL DATA TABLE functions to show different scenarios of for example a DCF model and sensitivity to one or two variables.
I'd rather leck besic excel then English skills, like this sir here
Hey man i loved your channel seriously. I am coming to germany in 2021 sep and i want to work in consulting firm in the begineeing of my professional career.
Hi Sarthak, thanks for the comment. this sounds great - lots of success to you!! Best, Heinrich
Well as a Data Warehouse Engineer i can tell you, Heinrich, that that is very bad financial modelling at 07:00 throwing away the duplicates. You cant redo that when you get the source data a second time effortlessly. Every BI tool can do those things. And honestly i dont know why Analysis for Office does not get more respect from business guys. for me it is the perfect tool. A datawarehouse in the background, slim excel on top. all i want. No need for stupid stuff and dashboards like Qlik, Tableau etc. I even used a data warehouse, analysis for office (excel) and thinkcell for automatic powerpoint generation 8 years back...... one push of a button, meeting preparation done, just had to do the story to tell then
Oh man, the more I look at this video I realize, this is exactly the type of my clients. Operative guys in logistics, financial analysts, controllers etc. doing sumifs all day. But the essence of doing that stuff is, that it is on a short term basis only and only useful for single point analysis only. It is not on a recurring basis. Since stuff changes again and again. Structures change (Business Units etc.), months values change (u see Heinrich cheat with rows/cols as "1"-"12" for months lookups and end days. I know that struggle why I went into Data Warehousing.
I dont consider it a good modelling, if the model cant be automated for years to come.
Hi,
Yes, understand where you are coming from. Fully agree that depending on the use case, other setups, potentially directly building on BI tools etc., can be much more appropriate.
Still, what I experience is that in a large majority of cases, people will want you to work fully contained in Excel without dependencies to other interfaces / software or else. The reason is that in large organizations, usually only few people have both the access to the tools you describe and the knowledge of how to use them. Therefore, you risk building a model that you then cannot send around to others (except for read-only) and cannot jointly work on with your clients / colleagues, as they are not familiar with the tools that you are describing. Also, isn't Analysis for Office a SAP thing? Many companies do not work with SAP. Independent from that, working fully contained in Excel is the lowest common denominator as everybody has some level of Excel skills and therefore will be able to work with and later also alter your model.
Also, many of these models are not supposed to be used "for years to come" but rather to calculate a business case for a new product, prepare financial plans for an M&A transaction etc. In these cases, the lifespan of these models is rather a couple of months than years, which is why the long term implications you are talking about are not that relevant.
But again, fully understand and agree that other approaches do make a lot of sense as well, depending on the use case.
Best, Heinrich
@@FirmLearning Hi, yeah i understand, that what you showed is not a full-length financial model. I know those cash liquidity models from reports I have to design on a regular basis at clients. And always those things are then rolling models with forecast and rather complex. And they also focus on basic topics only with limited characteristis to dril down. Yes, Analysis for Office is an SAP thing. But this is not so important, because Excel can read from any Database or Views via Data import. I m just from the SAP world thats why i took it as example. What i wanted to stress is, to make sure, that the denominator is NOT the Excel model itself, but the database underneath. Where a "single source of truth" is kept in the data warehouse (this is btw the whole use case of a data warehouse in a company, to have modeled KPIs that everyone can agree on (part of governance in a company if you do it right). Otherwise every department is doing their own stuff in Excel models and coming up with new KPIs that a new guy or other department doesnt understand ... I'm saying, that the Database itself has to be the basis, where everyone should work on. And trust me, this is standard since at least 30 years in corporates now :)
And exactly this is what i dont understand in large corporates, where the systems are there and even fully built and used. And then managers send around excerpts of reports from the data warehouse in offline excel sheets??? this is real bullshit. In Analysis for Office (or other tools), when the access is there, you need 2 clicks to get to the data and even can send around a polished workbook and just click "refresh" and have the latest actuals. This is so basic, but still in 2020 offline Excel sheets get sent around. This shows that people just have no clue how to use their tools and then build large constructs for reporting and modeling around an easy topic. I have seen positions for working students, doing stupid work, really stupid work redoing models over and over each month, instead of automating in 2 clicks.
I just visited your udemy profile. I would be highly interested in a course that deals with excel-skills necessary for top tier consulting. Maybe this course is already in the making? ;)
Have indeed thought about it, though still lots of work to do. Also want to use UA-cam to play around a bit what teaching format for the videos works best. Will of course let you know on this channel if I release a full course on this topic in the future! Best, Heinrich
@@FirmLearning I agree. I've taken your Skillshare powerpoint course and it was excellent. Would like to see a consulting specific excel course. I've taken financial modeling courses but those are very finance specific, rather than operationally focused. Would like a course on this topic. keep up the great content.
Hello, is there a download link for this data set?
Hi Heinrich, excellent video! How do you copy the formular to the very end of your table using a short cut?
Hi Simon, I usually jump around the table with "Ctrl" + the respective arrow key. This way you can jump to the end and then copy the formula. Best! Heinrich
Great content. I'd love to see a business case with a tornado diagram. Are you up for that?
Thanks Robert! Also for the video suggestion :) Best, H
Hallo Heinrich, sehr informatives Video. Ich habe eine banale Frage: Wie schafft man es 01.12.2021 bis 31.12.2021 in Excel einfach aufzulisten, weil wenn ich es einfach runterziehe wird das Jahr immer +1 hochgerechnet, ich möchte aber dass die Tage mit +1 hochgerechnet werden? Wäre mega dankbar für eine kurze Antwort, beste Grüße aus Dortmund
Hi Heinrich, is there a shortcut to clear all contents of multiple cells altogether at the same time as you do @6:29?
In the Liquidity Overview, the IF F4, shouldn't that be more like iF(F4="";E13;F4+E13) ?
I mean, I will have the money that is in my bank account, and I took a loan F4.. or is that an actual bank statement value, so you can update the projection in time with actual values from the bank?
Hi, yes indeed, the idea is that you update it with the real bank statements every day. Best! Heinrich
Great content, very helpful. Could you upload the document without solution so that we can try it again? Thanks for your time
Hi Nicola, thanks for your comment and watching. Yes, will probably release a full video course on Excel at some point in the future with lots of templates, excercises etc. :) Best! Heinrich
How long do you think will Excel remain the standard, now that there are alternatives like SQL+R or Python and tools like Tableau? How important is it to learn these alternatives?
Hi, do not see SQL / R replace Excel, believe the use cases are quite different for these technologies. Tableau is starting to gain some traction, but still far away from replacing Excel. Excel is here to stay ;) Best! Heinrich
You need to know that many softwares are runing on Excel (mainly VBA based), so Excel will, IMHO, be still used in the future.
Isn’t it faster to use pivot table instead of SUMIF?
SUMIF is often more flexible then Pivot tables (you need to manually update pivot tables etc.) - but of course pivot tables are great as well. Thanks for watching! Best, H
Hi. Do you have these course on excel?
Is the SUMIFS use limited to financial models? Can it be used in simple excel calculations or analysis?
Hi, you are absolutely right you can use sumifs in all kinds of models! Best, Heinrich
Can you share this excel file
I will try to do this if I have some time in the future!
对制造业无情的嘲笑。。。。哈哈哈
or just build a pivot table :D
Yes Pivot tables can be super useful for sure. Thanks for watching! Best, H