Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Excel Business Modeling - The In-Depth Street Smart Toolkit.

Name: Excel Business Modeling - The In-Depth Street Smart Toolkit.
Rating: 4.9 (7 reviews)

A comprehensive guide to build sophisticated models, robust databases, and write powerful formulas with Excel 2007-2019.

Created byLeon Lamprecht

Last updated 8/2022

English

What you'll learn

* Build quick and easy, street smart financial dashboards, formulas and models with multiple scenarios from scratch.
* Learn to approach and apply Excel formulas in a logical and easy to use way, ensuring you stand out above the rest.
* Mastering distinctive tips and tools to truly take your reporting and analysis to the next level and beyond.
* How to practically apply the course material in a business environment, as well as in personal circumstances.
* Approach challenges from different and more efficient angles getting to the same results within half the time.
* Build robust data bases, formulas and business models innovatively and efficiently.
* Gain lifetime access and knowledge to course material and engage in exercise files.
* An easy to understand walk through video of each and every facility within your Excel arsenal.
* Master unique business modeling tips and tools that you won't find anywhere else!
* Practice and understand dynamic formulas like SUMIFS, CHOOSE, VLOOKUP, INDEX, MATCH, Excel's D-Functions and many many more.
* Mastering Macros, Pivot Tables, Hyperlinks, Goal Seek, Solver and Data Validation Lists.
* Access a comprehensive list of 100+ Excel shortcut keys illustrating the before and after scenario.
* Highly productive and engaging lessons from an experienced business modeler and Finance professional.

Course content

15 sections • 57 lectures • 7h 38m total length

Setting Course Expectations.2:14
Download of Course Resources.1:11

Setting up the Ribbon / Toolbar in Excel.3:29
Degree of Difficulty: 2 out of 4 (Intermediate)
In this tutorial, we'll review how to customize the EXCEL RIBBON / TOOLBAR, as well as using the "CTRL + F1" and "CTRL + SHIFT + F1" functions.
Writing accurate and robust formulas with FIXED and RELATIVE cell references.6:10
Degree of Difficulty: 2 out of 4 (Intermediate)
In this tutorial, we'll learn the importance of establishing proper FIXED and RELATIVE cell references, using a VLOOKUP formula as an example.
Demonstrating the SUBTOTAL functions incorporating DATA VALIDATION lists.9:17
Degree of Difficulty: 3 out of 4 (Advanced)
In this lesson, we'll review the various SUBTOTAL functions, and the impact hidden rows have on the result. We also incorporate DATA VALIDATION LISTS to demonstrate these functions.
Adding complexity using SUBTOTAL 2 and 9 incorporating DATA VALIDATION lists.9:22
Degree of Difficulty: 4 out of 4 (Expert)
In this example, we'll combine the SUMPRODUCT, SUBTOTAL, OFFSET and ROW functions to review the SUBTOTAL 2 and 9 functions, and the impact hidden rows have on the result.

Introduction to the TEXT TO COLUMNS function and converting TEXT to numbers.4:09
Degree of Difficulty: 2 out of 4 (Intermediate)
In this tutorial, we'll introduce the TEXT TO COLUMNS function converting TEXT data to numbers, reviewing how to correct an incorrect result in SUMIF and PIVOT TABLES as examples.
Revealing the practical application of the TEXT TO COLUMNS FIXED WIDTH function.3:58
Degree of Difficulty: 3 out of 4 (Advanced)
In this lesson, we'll practice the TEXT TO COLUMNS function using the COLUMNS WIZARD FIXED WIDTH, and demonstrate the changes using a PIVOT TABLE.
Formatting data using the TEXT TO COLUMNS DELIMITED WIDTH function.4:21
Degree of Difficulty: 3 out of 4 (Advanced)
In this example, we'll review the TEXT TO COLUMNS function using the COLUMNS WIZARD DELIMITED WIDTH, and practice how to disregard characters in a string, demonstrating the changes using a PIVOT TABLE.
Combine text with the CONCATENATE function, displaying different approaches.5:10
Degree of Difficulty: 2 out of 4 (Intermediate)
In this lesson, we'll introduce the CONCATENATE function and demonstrate different ways to combine TEXT fields in Excel. We'll also discuss the practical application of using the CONCATENATE function.
Extracting characters from text strings in Excel, using various techniques.10:49
Degree of Difficulty: 2 out of 4 (Intermediate)
In this lesson, we'll review Excel's built-in STRING, or TEXT functions, including the LEFT, RIGHT, MID, LEN, SUM LEN, LEN TRIM, LEN SUBSTITUTE and the TRIM function. We'll discuss practical application in using these functions in conjunction with VLOOKUP, PIVOT TABLES and SUMIF formulas as examples.

Learn how to create DATA VALIDATION LISTS and how to constructively apply them.7:29
Degree of Difficulty: 3 out of 4 (Advanced)
One of the most effective and efficient ways to construct robust and powerful models is to add drop-down menus, or DATA VALIDATION LISTS. In this tutorial we'll learn how to create them and let them work for you, as well as applying the CONCATENATE and VLOOKUP functions.
Demonstrating how to use DATA VALIDATION LISTS in a Business Modelling example.8:45
Degree of Difficulty: 4 out of 4 (Expert)
In this tutorial, we'll add complexity and look at examples of the practical application for DATA VALIDATION LISTS, and demonstrate scenarios of how to use them, in combination with the CONCATENATE, VLOOKUP, INDEX and MATCH functions.
Mastering the techniques on how to plan, create, record and delete a MACRO.12:42
Degree of Difficulty: 4 out of 4 (Expert)
In this lesson, which is the first of two tutorials, we'll look at the logical steps on how to plan, create, record and delete a MACRO.
Learn to link multiple MACROS into a single MACRO, and creating MACRO buttons.6:26
Degree of Difficulty: 4 out of 4 (Expert)
Following on from the previous lesson, we'll discover how to create buttons and assign MACROS to those buttons. We will also demonstrate how to link multiple MACROS into a single MACRO, how to edit a MACRO, and practice how to assign and run a MACRO from a keyboard shortcut key.

Learn to use DATA FILTERS effectively, as well as DATA FILTERING BY COLOR.5:14
Degree of Difficulty: 3 out of 4 (Advanced)
In this tutorial, we'll introduce DATA FILTERS to review sets of data meeting certain criteria as well as DATA FILTERING BY COLOR.
Master the SORT function effectively applying multiple colors and criteria.6:20
Degree of Difficulty: 4 out of 4 (Expert)
Following on from the previous lesson, we'll use the SORT functions applying MULTIPLE COLORS, and other criteria in a COMBINATION. We'll also explore the AUTOFILTER function based on various criteria.
An introduction to CONDITIONAL FORMATTING where cells meet specific criteria.6:31
Degree of Difficulty: 2 out of 4 (Intermediate)
In this lesson, we'll introduce CELL FORMATTING rules in Excel, to apply formats that we will set based on CONDITIONAL criteria. This means for example that cells will only be highlighted meeting a single, or multiple specific criteria.
We study how to HIGHLIGHT the ENTIRE ROW based on specific CONDITIONAL CRITERIA.5:46
Degree of Difficulty: 3 out of 4 (Advanced)
Following on from the previous tutorial, we'll add more complexity and review formatting rules HIGHLIGHTING the ENTIRE ROW based on specific CONDITIONAL criteria. We'll also discuss PITFALLS when using this function.

An introduction to the VLOOKUP FUNCTION, reviewing different techniques.5:37
Degree of Difficulty: 3 out of 4 (Advanced)
In the first of five tutorials, we'll introduce the VLOOKUP FUNCTION and practice different ways to retrieve values from one dataset and pull them into another dataset. We'll also discuss pitfalls when using the VLOOKUP function, as well as adding complexity as we progress with this module.
Mastering how to build UNIQUE VLOOKUP formulas quickly, the STREET SMART WAY.13:17
Degree of Difficulty: 4 out of 4 (Expert)
Following on from the previous lesson, we'll add complexity and look at the STREET SMART WAYS on how to build ROBUST and FLEXIBLE models using the VLOOKUP function in combination with other Excel tools and logic. We'll also discover how to incorporate TEXT in a combination with a VLOOKUP formula, and adding DATA VALIDATION LISTS to the equation.
An introduction to the HLOOKUP FUNCTION, practicing different strategies.6:52
Degree of Difficulty: 3 out of 4 (Advanced)
In the third of five tutorials, we'll introduce the HLOOKUP FUNCTION and practice different ways to retrieve values from one dataset and pull them into another dataset. We'll also discuss pitfalls when using the HLOOKUP function.
Conquer how to build UNIQUE HLOOKUP formulas quickly, the STREET SMART WAY.14:13
Degree of Difficulty: 4 out of 4 (Expert)
Following on from tutorial three, we'll add complexity and look at the STREET SMART WAYS on how to build POWERFUL models using the HLOOKUP function in combination with other Excel tools and techniques. We'll also incorporate DATA VALIDATION LISTS into this lesson.
Learn how to practically apply the IFISERROR function, using different tactics.6:49
Degree of Difficulty: 3 out of 4 (Advanced)
In the last tutorial covering this section, we'll introduce and demonstrate the IFISERROR function, using it together with the VLOOKUP function and practice how to use this effectively in building models.

An introduction to the CHOOSE function, understanding the logic and application.5:39
Degree of Difficulty: 3 out of 4 (Advanced)
In the first from four tutorials, we'll gain a basic understanding of the CHOOSE lookup function, reviewing the formula logic and how this is used.
Master another application of the VLOOKUP function by using the CHOOSE formula.10:02
Degree of Difficulty: 4 out of 4 (Expert)
In the second from four tutorials, we'll add complexity and combine the VLOOKUP and CHOOSE formulas to demonstrate how the VLOOKUP formula can be used "Back to Front", as well as in the traditional way.
We will study how to make use of the CHOOSE formula to utilize days of the week.9:39
Degree of Difficulty: 3 out of 4 (Advanced)
In our third tutorial covering the CHOOSE function, we'll use the CHOOSE function to determine the day of the week, the month of the year, the next day as well as the next working day, assuming that a working week is Monday to Friday.
Gain how to use the CHOOSE SUMIF and SUMIFS functions achieving the same result.11:45
Degree of Difficulty: 4 out of 4 (Expert)
In the fourth and final tutorial covering the CHOOSE function, we'll add complexity and build a financial scenario using the CHOOSE SUMIF function and compare that to the SUMIFS function, proving that we can get the same result using both methodologies.

Grasping how to use the TIME, LEFT, RIGHT, HOUR, ROUND and MINUTE functions.7:45
Degree of Difficulty: 4 out of 4 (Expert)
In this tutorial, with PAYROLL TIME SHEETS in mind, we’ll convert start and finish times into TIME PROPER, using the TIME, LEFT and RIGHT functions. We'll also deal with SHIFT WORK, where hours start before midnight and ending the next day, treating this as one shift.
Calculate normal payroll hours adding a break, time and a half and double time.9:05
Degree of Difficulty: 3 out of 4 (Advanced)
In this lesson, we'll calculate NORMAL HOURS, adding a BREAK, calculate TIME AND A HALF and DOUBLE TIME hours, taking into account a conditional MINIMUM paid hours per day.
Converting DATE FORMATS, and apply CONDITIONAL FORMATTING to days of the week.7:34
Degree of Difficulty: 3 out of 4 (Advanced)
In this lesson, we'll practice CONVERTING DATE FORMATS, calculating the DAY OF THE WEEK based on dates, and use an ALTERNATIVE method using a LOOKUP table. We will also apply CONDITIONAL FORMATTING to days of the week, using the WEEKDAY function.
Calculating FUTURE DATES and PAST DATES, using the EDATE and YEARFRAC functions.6:34
Degree of Difficulty: 4 out of 4 (Expert)
In this lesson, we'll demonstrate how to calculate FUTURE RETIREMENT DATES, as well as DATES IN THE PAST, and explore Excel's YEARFRAC function together with the EDATE function.

An introduction to the SUMIF and AVERAGEIF functions, using a SINGLE CRITERIA.9:28
Degree of Difficulty: 3 out of 4 (Advanced)
In the first of three tutorials, we'll analyze practical application of the SUMIF and AVERAGEIF functions, using a single criteria, and create flexibility combining these functions with DATA VALIDATION LISTS.
Study the SUMIFS and AVERAGEIFS functions using FIVE MULTIPLE VARIABLE CRITERIA.17:40
Degree of Difficulty: 4 out of 4 (Expert)
In the second of three tutorials, we'll add complexity and review the SUMIFS and AVERAGEIFS functions, using up to FIVE MULTIPLE VARIABLE CONDITIONAL criteria.
Learn how to use the SUMIFS function in a financial business modeling example.8:03
Degree of Difficulty: 4 out of 4 (Expert)
In our third and final tutorial covering this topic, we'll practice the SUMIFS function using a FINANCIAL MODELING example, including adding a variable Consumer Price Index (CPI) option.

Learn how to create and use PIVOT TABLES, one of Excel's most POWERFUL tools.10:29
Degree of Difficulty: 3 out of 4 (Advanced)
In this tutorial, we'll discuss and practice the power of PIVOT TABLES, using them to SCRUTINIZE large data sets in different ways. We'll also CUSTOMIZE layouts and demonstrate how datasets can be ANALYZED with a simple "Click and Drag". Furthermore we'll demonstrate how to change the SOURCE DATA, to ensure that our PIVOT TABLE always reflects the correct set of data.
We'll investigate how to create MASTER PIVOT TABLES and change the SOURCE DATA.12:54
Degree of Difficulty: 2 out of 4 (Intermediate)
Following on from the previous lesson, we'll reveal how to retrieve the BREAKDOWN of DATA from an existing PIVOT TABLE. We'll also explore where to insert PIVOT TABLES, how to use MASTER PIVOT TABLES effectively and CHANGING the SOURCE DATA, incorporating the VLOOKUP and CONCATENATE functions.
Demonstrating how to master worksheet locations with HYPERLINKS, effortlessly.12:56
Degree of Difficulty: 4 out of 4 (Expert)
In this tutorial, we'll explore how to create, edit and use HYPERLINKS in Excel, and practice using it so that we can quickly jump to different locations within our file, or to another workbook location. We will also look at how to create a "BACK BUTTON", which will refer us back to our "STARTING POSITION".

Requirements

* This course was created using Excel 2016 and Excel 2019. All tutorials will work in Excel 2010 - 2019, and most will work in 2007.
* A computer or laptop with Microsoft Excel 2007-2019 or Microsoft 365 (Some techniques might not be available on Mac, or apply to all versions of Excel).
* Basic knowledge of Excel is not covered in the course, however we'll review some essential formulas and functions throughout the course.

Description

Your Comprehensive Street Smart Toolkit for Life.

This course provides a comprehensive street smart toolkit to construct effective and valuable models for your professional life.

If you are looking to vastly expand your Excel knowledge, build strong analytical and business modeling skills, or enhance and monitor productivity, this course will set you up with the correct managerial skillset to stand out in a competitive market.

This course provides resources and equips you with:

* Provide a 'shortcut approach' to building models that are useful within everyday activities.

* An in-depth understanding on how to build and utilize sophisticated models.

* Efficiently construct flexible yet robust databases.

* Quickly write powerful Excel formulas to analyze data and text fields.

Whilst most Excel courses converge on what each formula does, this course will also provide you with the practical application of these formulas. It is compiled from 20+ years of international business experience, focusing on hands-on practical scenarios, cases and knowledge. Knowledge that you won't find anywhere else.

This course will educate you to approach and apply Excel formulas in a logical and easy to use way, and will master distinctive tips and tools to truly take your reporting and analytical skills to the next level and beyond.

Recent Student Review

"Leon's explanation of Excel and video tutorials are fantastic, his knowledge is obvious and it flows through into the course. I'm an experienced Excel user and this course was able to teach me beginner tricks I didn't know, as well as much more advanced formulas and functions to assist in a Financial Planning setting."

Connor B.

Why is this course the right course for you?

Do you have a genuine appetite for flexible business scenario modeling and process automation? Would you like to be more efficient in your career? Would you like to be able to possess excellent technical problem solving skills, and have an in-depth understanding that data needs to be accurate? Does your business or career need to apply and ensure that models needs to be robust, powerful and flexible?

The coursework is to the point and easy to follow and understand. Having over 20 years experience designing and preparing models for large international corporations, I will enable you to formalize the knowledge for you to OWN! This course focus on practical application to enhance your productivity and vastly expand your knowledge base.

As a highly skilled and experienced Chartered Accountant with over 20 years' international experience across a wide variety of industries, I encounter and solve business challenges on a daily basis. The contents in this course contains real-life problems that I have encountered over the last 20+ years, problems I had to solve the hard way. The beauty of this course is that training material is not limited to your work environment. It can also be applied to your personal circumstances, such as family budgeting, home loan repayment calculations and retirement planning. The only thing that limits you is your imagination!

By the end of this course, you'll be "street smart" and able to compile and analyze robust data bases, formulas and business models empowering you to:

* Have a thorough understanding of what Excel is capable of and how to approach tasks in a logical, quick and easy manner.

* Approach challenges from different and more efficient angles getting to the same result.

* Use DATA VALIDATION LISTS to create vibrant yet flexible drop-down lists in cells and practically apply these in combination with LOOKUP, MATCH and INDEX functions.

* Return values from a list using a given position or index using the CHOOSE formula in combination with SUMIF and LOOKUP functions.

* Learn how to split and combine text into different columns using TEXT TO COLUMNS and CONCATENATE functions, and how to practically apply these in combination with other formulas.

* Return aggregate results for supplied values with SUBTOTAL functions in combination with the SUMPRODUCT and OFFSET functions.

* Filter, analyze and display data using PIVOT TABLES and other tools.

* Convert and manipulate DATE and TIME functions (with payroll time sheets in mind) and apply multiple CONDITIONAL FORMATTING rules.

* Automate time consuming tasks using MACROS (including linking multiple MACROS) and other street smart formulas.

* Construct powerful IF and NESTED IF statements using multiple conditional criteria, including but not limited to check if there is an error in the formula using IFISERROR, and IF AND OR statements.

* Work effectively with 100+ Excel SHORT CUT KEYS and customizing your ribbon / toolbar to work more proficiently.

* Apply DATA FILTERS and ADVANCED FILTERS using SINGLE and MULTIPLE criteria.

* Write TO THE POWER formulas and apply these in NPV (Net Present Value) calculations.

* Replicate worksheets quickly, efficiently and apply HYPERLINKS.

* Build powerful LOOKUP formulas that are flexible yet robust - The Street Smart Way.

* Construct complex SUMIF, SUMIFS formulas that meets MULTIPLE CONDITIONAL criteria and applying these in business modeling.

* Write financial functions with PMT (payment), RATE (interest rate), NPER (period), PV (present value) and FV (future value) and compare that against a loan repayment schedule as an example.

* Apply multiple COUNT functions together with special characters using WILDCARDS, to count specific criteria in a data range.

* Find the correct unknown input where the output is known using the GOAL SEEK and SCENARIO MANAGER functions in a NPV (Net Present Value) business case scenario.

* Write formulas performing calculations on a range of data based on certain criteria, using the D-functions like DGET, DAVERAGE, DMAX and much more.

If you want to stand out in the real world, you have come to the right place!

Enroll now and take the next step in mastering your Excel skills. See you in the course!

Who this course is for:

* This course starts at Intermediate Excel level, only certain basic functions will be referred to, and builds up to a comprehensive understanding of complex functions, techniques and data models.
* Anyone looking to start or enhance their career in data analytics and business and / or financial modeling, enhancing the everyday skills of Engineers, Entrepreneurs, Commercial Managers, Financial Controllers, Bookkeepers, Financial Planners, Data Analysts, Stock Analysts, Stock Brokers, Company Directors, College / University students, and many more!
* Anyone looking to build strong modeling and business analytical credentials to stand out in a competitive environment.
* Excel users looking to vastly expand their knowledge and approach to enhance their productivity to become highly efficient.

Excel Business Modeling - The In-Depth Street Smart Toolkit.

What you'll learn

Explore related topics

Course content

Getting Started2 lectures • 3min

Setting up the Ribbon. Using SUBTOTALS, FIXED and RELATIVE cell references.4 lectures • 28min

Using TEXT TO COLUMNS, CONCATENATE, and extracting characters from TEXT strings.5 lectures • 28min

Practical application using DATA VALIDATION LISTS and mastering powerful MACROS.4 lectures • 35min

Using DATA FILTERS, the SORT function and CONDITIONAL FORMATTING effectively.4 lectures • 24min

Mastering the VLOOKUP, HLOOKUP and IFISERROR functions, the STREET SMART WAY.5 lectures • 47min

Using the CHOOSE, VLOOKUP CHOOSE, CHOOSE SUMIF and SUMIFS functions effectively.4 lectures • 37min

We'll apply PAYROLL related tasks, as well as the EDATE and YEARFRAC functions.4 lectures • 31min

Master and practice the SUMIF, AVERAGEIF, SUMIFS and AVERAGEIFS functions.3 lectures • 35min

We'll probe two of Excel's most powerful functions, PIVOT TABLES and HYPERLINKS.3 lectures • 36min

Requirements

Description

Who this course is for: