Categories

There are currently no items in your shopping cart.

User Panel

Forgot your password?.

Udemy BeyondFormulas: Complete MS Excel Techniques & Modeling


INTRO: Learn to solve problems in excel, instead of just formula syntax :
Why this Excel Course?
[optional] What this course covers: Modeling Approach & Excel Tools
[optional] Consultants, or Managers of analysts: take note
BEYONDFORMULAS - Download all course materials
COURSE HANDOUT - Accompanying slides for each lesson
232 pages

EXCEL EFFICIENCY: Be faster, more accurate, more impressive & less error-prone :
EXCEL EFFICIENCY Section Intro
EXCEL EFFICIENCY - Course materials
Keyboard shortcut game: Improve speed, save HOURS
What is "Modular Model Design" and why should you care?
Avoid embarrassing yourself: Reduce spreadsheet risk to preempt mistakes
Building a model for stakeholders? Save hours of rework - make it “client-ready"
[optional] ADVANCED: What to do if your model becomes really BIG or really SLOW

PROBLEM SOLVING BASICS: Forget Excel. Problem solve on paper first :
PROBLEM SOLVING BASICS Section Intro
Problem-solving 101 - the basic analytical problem solving process
Define the Problem: Find true north with a problem statement & audience
Case-study: Problem Statement for a Widget Manufacturing Company
Structure the Problem: Create the “Issue Tree” to break down & share your logic
Case-study: Creating the issue tree for the Widget Manufacturing Company problem
Prioritize: Find and prioritize the main business drivers
Plan: Plan every detail of each analysis to go from Issue tree → Plan of Attack
CaWiMak Case study - course materials
8 pages

DATA BASICS: How to collect, clean, import, format, parse and lookup :
DATA BASICS Section Intro
DATA BASICS - Course materials
Gather real data based on your Analysis & Data Plan
Deal with Bad Data - Process, transform & clean large structured datasets
Got data? Now what? Best practice for importing data into your model
Primer on structuring data & making it useful: Table or “Flat file” format
Merging/joining data using a Primary Key+INDEX+MATCH (instead of VLOOKUP)
Use TABLES - Interact with your data using Excel's secret data weapon
Using String formulas to parse & manipulate text fields in your data
Date & time parsing, conversion and manipulation
Use Dynamic Named Ranges to give your data & cells easy-to-remember names

DATA ANALYSIS: Summarize your data with Formulas and PIVOT TABLES :
DATA ANALYSIS Section Intro
DATA ANALYSIS - Course materials
The best aggregation formula for analyzing & describing your data
PIVOT TABLES: Everything you need to know to instantly summarize your data
INTERMEDIATE PIVOT TABLES: “Show Values As” options & Calculated fields
Dynamic dashboards & data merging using GETPIVOTDATA, Pivot charts & slicers
Extrapolate CaWiMak sales data to forecast national sales using FORECAST
Next-level LOOKUPS: Using “near match” lookups to group & categorize your data

EXCEL ENGINE ROOM: Create a single, logical model flow to drive everything :
EXCEL ENGINE ROOM Section Intro
EXCEL ENGINE ROOM - Course materials
Issue tree→Excel: Creating a back-of- the-envelope business model “engine”
Modeling the impact of time and economies of scale
Model adoption curves over time instead of modeling a simple growth rate
[Optional] ADVANCED: Create your own user-defined formulas with VBA. It's easy!
Apply adoption curves to a phased geographic rollout
Supply Chain modeling 101 (Part A): Shipping cost & manufacturing locations
Supply Chain modeling 101 (Part B): Shipping cost & manufacturing locations
ADVANCED: Create a VBA function to calculate the distance between two locations
Supply Chain 201 (PART A): Elegant Optimization? Just add DATA TABLES
Supply Chain 201 (PART B): Elegant Optimization? Just add DATA TABLES
Model Engine→Full multi-period P&L in just a few steps
RECAP: Of everything we've accomplished this section
Avoid public humiliation through Auditing and Error-checking

MODELING UNCERTAINTY: Amazing Scenario and Sensitivity analyses made dead simple :
MODELING UNCERTAINTY Section Intro
MODELING UNCERTAINTY - Course materials
You can’t predict the future, but you can glance at the range of possibilities
Upgrade your new P&L model into a scenario crunching machine
Use DATA TABLES to instantly get the answers to all of your scenarios at once
The star chart approach to understanding variable-level sensitivity

SLIDE-READY OUTPUT: Synthesis, Data visualization and instant presentations :
SLIDE-READY OUTPUT Section Intro
SLIDE-READY OUTPUT - Course materials
Create an OUTPUT tab to easily consolidate & manage all model output
Creating the charts you want, and saving your favorite charts with “templates”

CONCLUSION :
CONCLUSION: How to apply what you've learned to solve any new problem
COURSE HANDOUT - Accompanying slides for each lesson
232 pages