No video :(

EXCEL SKILLS - How to build Financial Model in Excel (liquidity forecast with SUMIFS formula)

Подписаться 241 тыс.

Просмотров 52 тыс.

50% 1

Excel skills are crucial for all business professionals. Financial modeling is a task not only relevant to Finance professionals but to all general managers. Learn how to build a financial model in Excel in this video. I will create a liquidity forecast in Microsoft Excel, based on the Excel formula SUMIFS. The SUMIFS formula is a powerful formula that can be used in many ways in financial modeling. I will teach you how to use SUMIFS. Excel skills enable you to process and analyze data and draw conclusions from them. Learn most important Excel skills for consulting in this video. I talk about the key Excel skills I learned as a McKinsey consultant, having created many financial models myself. These are essential Excel skills for consulting. It is great as an advanced Excel training and will share many great Excel tips, highly relevant to management consulting, including Excel shortcuts. Are you an MBB consultant yourself (McKinsey, BCG, Bain)? Let me know what your favorite Excel tips are!
Chapters
00:00 Introduction
01:37 Explanation of SUMIFS formula
09:04 Financial model that we will build
15:43 Building the financial model
24:31 Wrap-up
MY ONLINE COURSES*
Learn to make PowerPoint presentations like Top Management Consultants:
"CEO-level Presentation Skills - Slide Writing for Consulting"
link.firmlearn...
Everything you need to know about Blockchain and Cryptocurrency:
"The Complete Course on Blockchain and Bitcoin"
link.firmlearn...
Udacity - Learn the latest tech skills, great to learn how to code without technical background (e.g., as business student) - with this link, 50 USD discount on all courses
link.firmlearn...
NEWSLETTER
Sign-up to the Firm Learning e-mail newsletter to continue learning about Business and Management:
link.firmlearn...
BOOK RECOMMENDATIONS*
The Hard Thing About Hard Things - Ben Horrowitz
amzn.to/3aYseE8
Extreme Ownership: How US Navy Seals Lead and Win - Jocko Willink
amzn.to/3d32sAE
The McKinsey Way - Ethan Rasiel
amzn.to/38SQarl
Influence: The Psychology of Persuasion - Robert Cialdini
amzn.to/2wUI4Bd
How a Diet can be a Game Changer in Life - Marie Mirrlees
amzn.to/2MUyfIh
MY EQUIPMENT*
Camera: amzn.to/32GZ3o7
Lense: amzn.to/3fMIkUo
Lights: amzn.to/32F6tZ3
Microphone: amzn.to/2E2mpud
AUDIOBOOKS*
This is a link to the audiobook service Audible that I use every day. It helps me "read" 50+ books every year. The link gives you a free test month:
link.firmlearn...
SOCIAL MEDIA
Instagram: link.firmlearn...
Facebook: link.firmlearn...
NEWSLETTER
Sign-up to the Firm Learning e-mail newsletter to continue learning about Business and Management:
link.firmlearn...
* Links are Affiliate links. This means that I receive a revenue share whenever a purchase is made via the link. The price for you stays the same. Thank you for supporting my work at Firm Learning!
All views expressed on this channel are private opinions of this channel's creator Heinrich. This is NOT an official channel of any consulting firm.

Опубликовано:

18 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 107

@FirmLearning 4 года назад

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

@judyl.7811 Год назад

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.

@elenachen7093 4 года назад

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!

@FirmLearning 4 года назад

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

@omarahmedmohamed6711 3 года назад

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 :)

@FirmLearning 3 года назад

Hi Omar, thanks for your comment. Check out the new video I released on Excel today :) Best, Heinrich

@mattiaselisson55 5 месяцев назад

Alot of small gems in this video, thanks!

@Fortunaaufstieg09 4 года назад

Awesome! Thank you very much :) I would like to see more Excel content in the future!

@FirmLearning 4 года назад

Thanks appreciate it! :) Yes will observe how this video is received and then potentially make more in the future. Best, Heinrich

@dsd6538 3 года назад

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 :)

@FirmLearning 3 года назад

Hi Daniel, das klingt doch spannend - ganz viel Erfolg dir!! LG, Heinrich

@ivancruz4516 4 года назад

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!

@FirmLearning 4 года назад

Very happy to hear that the video has been helpful. Lots of success to you for the future Ivan! Best, Heinrich

@Gorlenius 4 года назад

Very helpful Excel tutorial for financial modeling, thanks.

@FirmLearning 4 года назад

Thanks, glad it helped!

@HowToAnalyst 2 года назад

Great video Heinrich, the liquidity model was simple but powerful, a great tool for businesses!

@mcdlr9197 4 года назад

Please upload more Excel Skills! Really insightful;)

@FirmLearning 4 года назад

Thank you!!

@lepetty14 4 года назад

Also a McK alum but from the Analytics support team, missed doing this so much haha 😆 thanks for this video!!!

@FirmLearning 4 года назад

Great to see fellow alumni here in the comments!! Thanks for watching! :) Best, Heinrich

@arrzun 3 года назад

Enlightening use of sumifs in planning Much appreciated

@FirmLearning 3 года назад

Thank you Arjun, super happy to hear the video is helpful! Best, Heinrich

@bl4643 4 года назад

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.

@FirmLearning 4 года назад

Hi, thanks - yes agree it is beginner content! Best, Heinrich

@willdoward 4 года назад

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!

@FirmLearning 4 года назад

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

@aryakhadjehzadeh Год назад

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

@rubenfsc 3 года назад

Many thanks for this video!

@FirmLearning 3 года назад

Thank YOU for watching Ruben, much appreciated! Best, Heinrich

@paolo7206 4 года назад

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

@FirmLearning 4 года назад

Hi Paul, thanks for your feedback. Yes, that makes total sense. Will take this into account for future Excel videos! ;) Best, Heinrich

@allegrarestellidellafratta5124 2 года назад

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.

@FirmLearning 2 года назад

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

@yu-minlin3632 4 года назад

thanks for sharing. hope to see more excel skill relevant videos :)

@FirmLearning 4 года назад

Thanks, will continue creating them in the future :) Best! Heinrich

@wangt618 3 года назад

Great Job! I was familiar with the function used but isolated onto 1 sheet. Referencing another sheet isn't something use commonly. Thanks again!

@FirmLearning 3 года назад

Happy to hear it helps, thanks Tony! Best, Heinrich

@bellelumm9352 4 года назад

Great content. Thank you for sharing

@FirmLearning 4 года назад

Appreciate it! Thanks for watching! Best, Heinrich

@riofernandoalexander6812 3 года назад

This really help me in my case, thanks

@FirmLearning 3 года назад

Super happy to hear that - thank you Rio! Best, Heinrich

@silverhalls 3 года назад

great content!! thank you for this!

@FirmLearning 3 года назад

Super happy to hear that - thank YOU for watching Holly!! Best, Heinrich

@simonrupprecht2710 3 года назад

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.

@FirmLearning 3 года назад

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

@NatalieVanVeenNatalia 4 года назад

Very helpful indeed!

@FirmLearning 4 года назад

Thanks for watching Natalia!! Best, Heinrich

@margaretwanjiru84 3 года назад

Great information thanks you

@FirmLearning 3 года назад

Super happy to hear that - thanks Margaret! Best, Heinrich

@lionmdd 3 года назад

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

@FirmLearning 3 года назад

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

@victorhuang7446 4 года назад

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~~

@FirmLearning 4 года назад

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

@sesu5604 Год назад

I'd rather leck besic excel then English skills, like this sir here

@selvd004 4 года назад

This is great content - would love to see more of this!

@FirmLearning 4 года назад

Thanks! Yes, have some more planned for the future! :) Best, Heinrich

@ramsha1330 4 года назад

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.

@FirmLearning 4 года назад

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

@wucool33 2 года назад

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!

@teddiemandala4907 3 года назад

Great and insightful. Thank you

@FirmLearning 3 года назад

Happy to hear that, thank you Teddie! Best, Heinrich

@mathieugiordano1003 4 года назад

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

@FirmLearning 4 года назад

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

@xingyipan7511 3 года назад

This is really helpful! Hope you can create a course teaching excel like the presentation course you created. 👍

@FirmLearning 3 года назад

Hi Xingyi, thank you for your comment! Indeed planning to release something like that in the future :) Best, Heinrich

@Gejolito 3 года назад

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.

@FirmLearning 3 года назад

Hi Gejolito, Thank you, good and valid advice. Thanks for watching!! Best, Heinrich

@v3r0x 4 года назад

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.

@FirmLearning 4 года назад

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

@v3r0x 4 года назад

@@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.

@nicolasballe 4 года назад

Thats just amazing. Will definitely apply it , my god

@FirmLearning 4 года назад

Thank you for watching! :) Best, H

@fib6156 2 года назад

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.

@sarthakgosain839 3 года назад

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.

@FirmLearning 3 года назад

Hi Sarthak, thanks for the comment. this sounds great - lots of success to you!! Best, Heinrich

@ucheonunkwo9592 Год назад

Hello, is there a download link for this data set?

@simonb8174 3 года назад

Hi Heinrich, excellent video! How do you copy the formular to the very end of your table using a short cut?

@FirmLearning 3 года назад

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

@KJ-zu7ft 2 года назад

Hi Heinrich, is there a shortcut to clear all contents of multiple cells altogether at the same time as you do @6:29?

@nicolaangelin2317 3 года назад

Great content, very helpful. Could you upload the document without solution so that we can try it again? Thanks for your time

@FirmLearning 3 года назад

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

@robc86 4 года назад

Great content. I'd love to see a business case with a tornado diagram. Are you up for that?

@FirmLearning 4 года назад

Thanks Robert! Also for the video suggestion :) Best, H

@Fortunaaufstieg09 4 года назад

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? ;)

@FirmLearning 4 года назад

Have indeed thought about it, though still lots of work to do. Also want to use RU-vid 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

@christianw.3550 3 года назад

@@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.

@peseweng1 2 года назад

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

@BNLien 7 месяцев назад

Hi. Do you have these course on excel?

@laci272 3 года назад

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?

@FirmLearning 3 года назад

Hi, yes indeed, the idea is that you update it with the real bank statements every day. Best! Heinrich

@Knowledge_Nuggies 3 года назад

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?

@FirmLearning 3 года назад

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

@mbtijanii6305 2 года назад

You need to know that many softwares are runing on Excel (mainly VBA based), so Excel will, IMHO, be still used in the future.

@shingav 3 года назад

Is the SUMIFS use limited to financial models? Can it be used in simple excel calculations or analysis?

@FirmLearning 3 года назад

Hi, you are absolutely right you can use sumifs in all kinds of models! Best, Heinrich

@mikih4074 4 года назад

Isn’t it faster to use pivot table instead of SUMIF?

@FirmLearning 4 года назад

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