Please ensure Javascript is enabled for purposes of website accessibility
Excel Business Modeling - The In-Depth Street Smart Toolkit
0( 0 REVIEWS )
1 STUDENTS
7h 43m
Leon
Leon

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

Read more.
Course Skill Level
Intermediate
Time Estimate
7h 43m

Instructor

Leon
Leon

A highly skilled and experienced Chartered Accountant with over 20 years' international experience gained across a wide variety of industries ranging from Mining, Steel Manufacturing, Petroleum, Corporate Banking and a number of Service Industries. With a passion for flexible scenario financial modelling, forecast / budget process automation and possessing excellent technical problem solving skills, I have an in-depth understanding that data needs to be accurate and models needs to be robust


Only want this course? Buy this course for $199 $29 and keep lifetime access. Click here

About This Course

Who this course is for:

  • This course starts at the Intermediate Excel level. Only certain basic functions will be referred to and built 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 who are looking to vastly expand their knowledge and approach to enhance their productivity to become highly efficient.

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 databases, formulas and business models innovatively and efficiently.
  • Gain lifetime access and knowledge to course material and engage in exercise files.
  • An easy to understand walkthrough 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.

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.

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 skill set 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 of 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.

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 need to be robust, powerful and flexible?

The coursework is to the point and easy to follow and understand. Having over 20 years of experience designing and preparing models for large international corporations, I will enable you to formalize the knowledge for you to OWN! This course focuses 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 of this course contain 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 databases, 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 checking if there is an error in the formula using IFISERROR, and IF AND OR statements.
  • Work effectively with 100+ Excel SHORT CUT KEYS and customize 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 meet MULTIPLE CONDITIONAL criteria and apply 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 an 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!

Our Promise to You

By the end of this course, you will have mastered distinctive tips and tools to truly take your reporting and analytical skills to the next level and beyond.

10 Day Money Back Guarantee. If you are unsatisfied for any reason, simply contact us and we’ll give you a full refund. No questions asked.

Get started today!

Course Curriculum

Introduction
Introduction 00:00:00
Section 1 - Getting Started
Lecture 1: Setting Course Expectations 00:00:00
Lecture 2: Download Of Course Resources 00:00:00
Section 2 - Setting Up The Ribbon. Using SUBTOTALS, FIXED And RELATIVE Cell References
Lecture 3: Setting Up The Ribbon / Toolbar In Excel 00:00:00
Lecture 4: Writing Accurate And Robust Formulas With FIXED And RELATIVE Cell References 00:00:00
Lecture 5: Demonstrating The SUBTOTAL Functions Incorporating DATA VALIDATION Lists 00:00:00
Lecture 6: Adding Complexity Using SUBTOTAL 2 And 9 Incorporating DATA VALIDATION Lists 00:00:00
Section 3 - Using TEXT TO COLUMNS, CONCATENATE, And Extracting Characters From TEXT Strings
Lecture 7: Introduction To The TEXT TO COLUMNS Function And converting TEXT To Numbers 00:00:00
Lecture 8: Revealing The Practical Application Of The TEXT TO COLUMNS FIXED WIDTH Function 00:00:00
Lecture 9: Formatting Data Using The TEXT TO COLUMNS DELIMITED WIDTH Function 00:00:00
Lecture 10: Combine Text With The CONCATENATE Function, Displaying Different Approaches 00:00:00
Lecture 11: Extracting Characters From Text Strings In Excel, Using Various Techniques 00:00:00
Section 4 - Practical Application Using DATA VALIDATION LISTS And Mastering Powerful MACROS
Lecture 12: Learn How To Create DATA VALIDATION LISTS And How To Constructively Apply Them 00:00:00
Lecture 13: Demonstrating How To Use DATA VALIDATION LISTS In A Business Modelling Example 00:00:00
Lecture 14: Mastering The Techniques On How To Plan, Create, Record And Delete A MACRO 00:00:00
Lecture 15: Learn To Link Multiple MACROS Into A Single MACRO, And Creating MACRO Buttons 00:00:00
Section 5 - Using DATA FILTERS, The SORT Function And CONDITIONAL FORMATTING Effectively
Lecture 16: Learn To Use DATA FILTERS Effectively, As Well As DATA FILTERING BY COLOR 00:00:00
Lecture 17: Master The SORT Function Effectively Applying Multiple Colors And Criteria 00:00:00
Lecture 18: An Introduction To CONDITIONAL FORMATTING Where Cells Meet Specific Criteria 00:00:00
Lecture 19: We Study How To HIGHLIGHT The ENTIRE ROW Based On Specific CONDITIONAL CRITERIA 00:00:00
Section 6 - Mastering The VLOOKUP, HLOOKUP And IFISERROR Functions, The STREET SMART WAY
Lecture 20: An Introduction To The VLOOKUP FUNCTION, Reviewing Different Techniques 00:00:00
Lecture 21: Mastering How To Build UNIQUE VLOOKUP Formulas Quickly, The STREET SMART WAY 00:00:00
Lecture 22: An Introduction To The HLOOKUP FUNCTION, Practicing Different Strategies 00:00:00
Lecture 23: Conquer How To Build UNIQUE HLOOKUP Formulas Quickly, The STREET SMART WAY 00:00:00
Lecture 24: Learn How To Practically Apply The IFISERROR Function, Using Different Tactics 00:00:00
Section 7 - Using The CHOOSE, VLOOKUP CHOOSE, CHOOSE SUMIF And SUMIFS Functions Effectively
Lecture 25: An Introduction To The CHOOSE Function, Understanding The Logic And Application 00:00:00
Lecture 26: Master Another Application Of The VLOOKUP Function By Using The CHOOSE Formula 00:00:00
Lecture 27: We Will Study How To Make Use Of The CHOOSE Formula To Utilize Days Of The Week 00:00:00
Lecture 28: Gain How To Use The CHOOSE SUMIF And SUMIFS Functions Achieving The Same Result 00:00:00
Section 8 - We'll Apply PAYROLL Related Tasks, As Well As The EDATE And YEARFRAC Functions
Lecture 29: Grasping How To Use The TIME, LEFT, RIGHT, HOUR, ROUND And MINUTE Functions 00:00:00
Lecture 30: Calculate Normal Payroll Hours Adding A Break, Time And A Half And Double Time 00:00:00
Lecture 31: Converting DATE FORMATS, And Apply CONDITIONAL FORMATTING To Days Of The Week 00:00:00
Lecture 32: Calculating FUTURE DATES And PAST DATES, Using The EDATE And YEARFRAC Functions 00:00:00
Section 9 - Master And Practice The SUMIF, AVERAGEIF, SUMIFS And AVERAGEIFS Functions
Lecture 33: An Introduction To The SUMIF And AVERAGEIF Functions, Using A SINGLE CRITERIA 00:00:00
Lecture 34: Study The SUMIFS And AVERAGEIFS Functions Using FIVE MULTIPLE VARIABLE CRITERIA 00:00:00
Lecture 35: Learn How To Use The SUMIFS Function In A Financial Business Modeling Example 00:00:00
Section 10 - We'll Probe Two Of Excel's Most Powerful Functions, PIVOT TABLES And HYPERLINKS
Lecture 36: Learn How To Create And Use PIVOT TABLES, One Of Excel’s Most POWERFUL Tools 00:00:00
Lecture 37: We’ll Investigate How To Create MASTER PIVOT TABLES And Change The SOURCE DATA 00:00:00
Lecture 38: Demonstrating How To Master Worksheet Locations With HYPERLINKS, Effortlessly 00:00:00
Section 11 - Practical Application Of The INDEX And MATCH Functions Used In A Combination
Lecture 39: In The First Of Three Tutorials, We’ll Introduce The INDEX Function 00:00:00
Lecture 40: In The Second Of Three Tutorials, We’ll Introduce The MATCH Function 00:00:00
Lecture 41: Master INDEX And MATCH Functions Used In A COMBINATION To Build Powerful Models 00:00:00
Section 12 - We'll Create Powerful BUSINESS MODELS And Use Various Excel FINANCIAL Functions
Lecture 42: In This Tutorial, We Study The PMT, ABSPMT, RATE, NPER, PV And FV Functions 00:00:00
Lecture 43: Review Excel’s FINANCIAL Functions Against A DAILY MORTGAGE Repayment Schedule 00:00:00
Lecture 44: Using The TO THE POWER Function To Populate CASH FLOWS, The STREET SMART WAY 00:00:00
Lecture 45: Revealing How To Build Powerful Business Models Using The TO THE POWER Function 00:00:00
Section 13 - We Review Various D-Functions, The SUMPRODUCT, COUNTIF And COUNTBLANK Functions
Lecture 46: Demonstration On How To Assemble And Use The DSUM And SUMPRODUCT Functions 00:00:00
Lecture 47: Mastering The DAVERAGE Function, And How To Constructively Apply This Function 00:00:00
Lecture 48: Learn How To Assemble The DMIN And DMAX Functions, Using DATA VALIDATION LISTS 00:00:00
Lecture 49: Constructing The DGET Function, Using SINGLE And MULTIPLE Referencing Criteria 00:00:00
Lecture 50: Grasp How To Create COUNTIF (With WILDCARDS And TEXT) And COUNTBLANK Formulas 00:00:00
Section 14 - Creating IF STATEMENTS, NESTED IF STATEMENTS, And Using The IFISERROR STATEMENT
Lecture 51: Introduction On How To Construct A Simplified Conditional IF STATEMENTSimplified IF Statements 00:00:00
Lecture 52: Demonstrating How To Combine And Structure Multiple IF STATEMENTS Correctly 00:00:00
Lecture 53: Learn To Construct Complex IF STATEMENTS Using AND And OR Conditional Criteria 00:00:00
Lecture 54: Revealing The Constructive Application Of The IFISERROR Function 00:00:00
Section 15 - Hands-On Application Using GOAL SEEK And SOLVER, And Using EXCEL SHORTCUT Keys
Lecture 55: Revealing How To Use The GOAL SEEK Function In A Business Or Personal Scenario 00:00:00
Lecture 56: Learn And Master The DATA SOLVER Function, Using Multiple Variable Constraints 00:00:00
Lecture 57: Become Versed With 100+ EXCEL SHORTCUT Keys With Examples And Alternatives 00:00:00