This chapter covers Range Names and consists of 2 topics.
Who is Dr. Wayne Winston and how can his Advanced Course help you?
0
+
Tutorials
0
+
Hours Of Content
0
+
Downloadable Files
Frequently Asked Questions
Unlock Lifetime Access to this Course
RANGE NAMES
- Range Name Tricks – This video shows how to use a named row in a formula and create ranges based on Excel formulas.
- Implicit Intersection – This video shows how to pick off an entry in an array by simply typing the row and column headings.
TEXT FUNCTIONS
This chapter covers Text Functions and consists of 4 topics.
- Clean Function – This video shows how to Use the CLEAN, FIND, and SUBSTITUTE functions to remove invisible characters from your data.
- Flash Fill – This video shows how to use FLASH FILL in Excel 2013 and 2016 to automatically perform the work of text functions
- Control + E and Flash Fill – In this video we show how Control + E makes Flash Fill perform better
- The TEXT Function – In this video we show how the TEXT function can ensure that a value in one cell is converted in a different cell to any desired number format.
AUDITING TOOLS, CIRCULAR REFERENCES, AND IF FUNCTIONS
This chapter covers Auditing Tools, Circular References, and IF Functions and consists of 5 topics.
- Auditing – This video shows how to use Precedents to see which cells are needed to compute a formula and using Dependents to find cells that are dependent on spreadsheet inputs.
- Circular Reference – This video shows how circular references can be used and resolved.
- Flatten Table – This video shows how to use IF statements to extract the data in a 20 row by 12 column table into 240 different rows.
- Error Trap – This video shows how to use the IFERROR function to remove error messages from your spreadsheets. It also covers the AGGREGATE function which allows you to ignore error values in calculations.
- IF Statements And Sales Commission Calculations Part Two – In this video we use VLOOKUP, MATCH, INDEX, And IF functions to model a complex sales incentive plan.
DATA TABLES, GOAL SEEK AND SCENARIO MANAGER
This chapter covers Data Tables, Goal Seek, and Scenario Manager and consists of 6 topics.
- One Way Data Table – This video shows how to use a One Way Data Table to determine how changing a single spreadsheet input affects multiple outputs.
- Two Way Data Table – This video shows how to use a Two way data table to determine how changes in two spreadsheet inputs affect a single output.
- Mortgage Data Table – This video shows how to use a Two way data table to determine how the monthly payment on a loan depends on the loan size and annual rate.
- Goal Seek – This video shows how Goal Seek can be used to determine the number of unit sales needed for a juice bar to break even.
- Goal Seek Finance – This video shows how to use Goal Seek to determine the annual rate on a loan that makes the monthly payment hit a desired value.
- Data Table Chart – In this video we show how to create a chart to summarize the numbers in a two-way data table.
CONDITIONAL COUNTS AND SUMS
This chapter covers Conditional Counts and sums and consists of 11 topics.
- SUMIFS With Duplicates –This video shows how the SUMIFS function and the Remove Duplicates functionality are used to calculate the sales each sales person made of each product.
- Subtotals –This video shows how SUBTOTALS are used to determine how much each salesperson sold in each region.
- Data Consolidate –This video shows how DATA CONSOLIDATE is used to combine data from several workbooks to calculate total sales of each product during each month.
- Database Functions –This video shows how the DCOUNT, DSUM, and DAVERAGE functions are used to compute conditional counts, sums, and averages. It includes doing conditional calculations based on an “OR” criteria.
- Computed Criteria –This video shows how Database functions are used with criteria based on an Excel formula.
- DGET Function –This video shows how to use the DGET function to pull the location of a transaction based on the date and ID code.
- Advanced Filter –This video shows how the Advanced Filter can be used to extract any subset of a spreadsheet’s data and copy this data to a new location.
- Conditional Counts and Sums With Non-Blanks – This video shows how to do conditional sums and counts where one criteria is that the cell does not contain a blank.
- Benford’s Law – In this video we show (amazingly) that for 70% of all countries the first digit in their population is 1,2,3, or 4!
- Finding the n’th Match – In this video we show how to find the n’th match to a word in a column.
- Analyzing NFL Play Effectiveness – In this video we show how to use text functions, IFERROR, COUNTIF, and AVERAGEIF functions to analyze the effectiveness of Adrian Peterson’s off tackle runs.
CONDITIONAL FORMATTING AND DATA VALIDATION
This chapter covers Conditional Formatting and Data Validation and consists of 7 topics.
- Formula Option Part One – This video shows how cells can be formatted based on a complex Excel formulas. For example, highlight in green, each quarter a company’s sales increase, and in red each quarter a company’s sales decrease.
- Formula Option Part Two – This video continues to show how to conditional format based on a formula.
- Custom Option – This video shows how Data Validation criteria can be set up with Excel formulas. For example, we show how to ensure that entering any non-numerical entry in column A creates an error message.
- Circle Invalid Data – This video shows how to ensure that data not meeting a desired criteria is circled.
- Highlighting New Hires – This video shows how to highlight employees who have just been hired.
- Conditional Formatting and Sports Part One – In this video we show how to highlight all baseball players who are good on HR’s, Stolen Bases, and Batting Average.
- Conditional Formatting and Sports Part Two – In this video we show how to use data bars, color scales, and icon sets to format sports data.
TABLES
This chapter covers Tables and consists of 10 topics.
- Worksheet Templates – This video shows that if you need a worksheet for each state set up in an identical format, how to easily use worksheet templates to set up the 50 needed worksheets.
- Three Dimensional Formula – This video shows how three dimensional formulas can be used to sum data across worksheets.
- Paste Special – This video shows how the Paste Special Menu can be used to transpose data and divide “spread out cells” in a worksheet by 100
- Tables Introduction – This video shows how making data a Table ensures that formulas update automatically when new data is added.
- Table Graphs – In this video we show how making data a Table ensures that charts update automatically when new data is added.
- Table Slicers – In this video we show how slicers can be used to “slice and dice” the data in Tables.
- Table Extended – In this video we show how tables make drop down boxes, VLOOKUPS, SUMIF formulas and NPV calculations automatically update when new data is added.
- Aggregate Function With Slicers – In this video we show how the AGGREGATE function is needed to ensure calculations based on Table Slicers are correct.
- Conditional Formatting Tables – In this video we show how to make Conditional Formats based on Formulas update automatically when new data is added.
- Copying Formulas Across Tables – This video shows how copying formulas across a table can be tricky.
PIVOT TABLES
This chapter covers Pivot Tables and consists of 14 topics.
- Value Field Settings and Show Values As -In this video we show how to summarize data as a Percentage of a Row or Column.
- Calculated Fields –In this video we show how to create a calculated field based on PivotTable columns.
- Slicers and Filters –In this video we show how slicers allow you to slice and dice sales by country and product.
- GETPIVOTDATA Function –In this video we show how GETPIVOTDATA is used to extract data from a PivotTable for use in reports and charts.
- Calculated Items –In this video we show how to create a calculated item based on rows in a PivotTable. For example, group together all cars made in Germany in a Calculated Item called Germany.
- Timelines –In this video we show how to slice and dice PivotTable calculations based on any subset of months, quarters or years. The functionality of this video is for Excel 2013 and Excel 2016 users.
- Analyzing Surveys and More Pivot Charts – In this video we show how to summarize a survey with a PivotTable and PivotChart.
- Creating Pivot Tables From Multiple Ranges – In this video we show how to create PivotTables from data in multiple worksheets and/or workbooks.
- Multiple Pivot Tables – In this video we show how to create many PivotTables based on a Filter.
- Data Model – In this video we show how Excel 2013 and 2016 users can use the Data Model to efficiently link data without using thousands of VLOOKUPS.
- Pivot Tables and the NBA Part One – In this video we show how to use Pivot Tables to analyze NBA players.
- Pivot Tables and the NBA Part Two – In this video we continue to show how Pivot Tables can be used to analyze NBA players.
- Why Are There Fewer Births On Weekends? – In this video we show that the lack of C sections on weekends explains why there are fewer births on weekends.
- Putting it all Together – In this video we show how to use IF, VLOOKUP, PivotTables, and AVERAGEIFS functions to solve a tax accounting problem.
DESCRIPTIVE STATISTICS
This chapter covers Descriptive Statistics and consists of 7 topics.
- Histogram Creation – In this video we show how to use a histogram to summarize Cisco’s monthly returns.
- Histogram Shapes – In this video we discuss symmetric, positively, and negatively skewed histograms.
- Descriptive Statistics – In this video we discuss measures of typical value, variation about the mean, and skewness.
- Rule of Thumb – In this video we discuss how to determine normal variation for any data set.
- More Statistical Functions – In this video we discuss the PERCENT, PERCENTRANK, LARGE, SMALL, and RANK functions.
- Taking a Random Sample – In this video we show how to take a random sample size of 50 from 285 NBA players.
- Multiple Objective Decision Making – In this video we show how to choose a job based on multiple objectives.
TREND CURVE, CORRELATION, AND REGRESSION TO THE MEAN
This chapter covers Trend Curve, Correlation, and Regression to the Mean and consists of 12 topics.
- Linear Regression Part One – In this video we show how to find the line best fitting a set of bivariate data.
- Linear Regression Part Two – In this video we show how the RSQ, SLOPE, INTERCEPT, and STEYX functions can be used to analyze a linear regression.
- Exponential Growth – In this video we show how exponential growth can be used to fit bivariate data where the graph gets steeper.
- Power and Demand Curves – In this video we show how to fit a constant elasticity or power demand curve.
- Polynomial Demand Curves – In this video we show how to fit a quadratic demand curve based on 3 data points.
- Learning Curves – In this video we show how to estimate a Learning curve which shows how the unit cost of producing a product drops as more of the product has been produced.
- Correlation – In this video we show how the concept of correlation can measure the strength of the linear association between monthly returns on six stocks.
- Estimating the Beta of a Stock – In this video we show how the SLOPE function can be used to quickly estimate the Betas of many stocks.
- Regression to the Mean – In this video we show how the idea of Regression to the Mean explains why NFL teams that do well in one season usually do not do as well the next season.
- Moving Average – In this video we show how to summarize quarterly Amazon.com revenues with a Moving Average Chart and smooth out the trend and eliminate seasonality.
- Finding a Good R Squared – In this video we show that an R Squared value of 0.99 is not necessarily that good.
- Finding the Best Curve to Fit Data – In this video we show how to determine which of the linear, exponential, and power curve best fit a bi-variate relationship.
MULTIPLE REGRESSION
This chapter covers Multiple Regression and consists of 12 topics.
- Multiple Regression Part One – In this video we show how multiple regression is used to predict computer sales per-capita based on education and income.
- Multiple Regression Part Two – In this video we continue from the previous example.
- Dummy Variables – In this video we show how to incorporate qualitative variables such as seasonality to predict auto sales.
- Introduction to Conjoint Analysis – In this video we show how conjoint analysis can determine what product attributes are valued by customers.
- Conjoint Analysis and Regression – In this video we show how an actual regression is used to illustrate conjoint analysis.
- Value Based Pricing –In this video we show how conjoint analysis is used to estimate the value of product attributes.
- Nonlinearities and Interactions – In this video we show how to include nonlinearities and interactions in a multiple regression analysis.
- What Makes NBA Teams Win – In this video we use multiple linear regression to show what makes NBA teams win.
- QB Rating – In this video we show how to use multiple regression to closely approximate the NFL’s QB Rating formula.
- Maximum Likelihood Estimation – In this video we show how Maximum Likelihood Estimation is used to estimate statistical parameters.
- Logistic Regression – In this video we show logistic regression can be used to predict dependent variables that can assume two outcomes (such as Live or Die after an operation.)
- The Windchill Index – In this video we use multiple regression with nonlinearities and interactions to reverse engineer the complex Windchill Index formula.
PROBABILITY
This chapter covers Probability and consists of 7 topics.
- Introduction to Probability – In this video we define the concept of an experiment, sample space, and solve some simple probability problems.
- Addition Rule of Probability and Mutually Exclusive Events – In this video we define mutually exclusive events and show how to compute P(A or B) for events A and B.
- The Rule of Complements – In this video we explain how to use the Rule of Complements to solve tricky probability problems.
- Independent Events – In this video we define independent events and show how easy it is to compute P(A and B) for independent events.
- Conditional Probability – In this video we show how to compute the probability of one event given that another event has occurred.
- Total Probability – Leading up to Bayes theorem, In this video we develop the Law of Total Probability.
- Bayes Theorem – In this video we show Bayes Theorem allows us to use information to update probabilities.
RANDOM VARIABLES
This chapter covers Random Variables and consists of 7 topics.
- Random Variables – In this video we show how to compute the mean, standard deviation, and variance of random variables.
- Binomial Random Variables – In this video we show how to compute probabilities involving repeated trials with two outcomes (success or failure).
- Binomial Mean Variance – In this video we discuss the mean, variance and standard deviation of a binomial random variable.
- Poisson Random Variables – In this video we show how to compute Poisson probabilities in situations where in a small length of time either 0 or 1 events occur.
- Normal Random Variables – In this video we show how to compute probabilities for the normal random variable.
- Central Limit Theorem – In this video we show how to use the normal random variable to approximate probabilities involving sums of >=30 random variables.
- Z Scores – In this video we show how Z scores are used to measure how “unusual” a data point is.
ARRAY FORMULAS
This chapter covers Array Formulas and consists of 16 topics.
- Introduction to Array Formulas and Functions – In this video we show how array formulas and functions can fill more than one cell.
- Simple Examples of Array Formulas – In this video we use simple examples to enhance your understanding of array formulas.
- Array Formulas and Text Functions – In this video we show how to use array formulas to find the total length of cells in a range or the length of the longest cell in the range.
- Finding Smallest Numbers in a Range – In this video we show how to find the 6 smallest numbers in a range.
- Emulating SUMIF and DSUM – In this video we show how array formulas can emulate the SUMIF and DSUM functions.
- Emulating SUMIFS – In this video we show how an array formula can emulate the SUMIFS functions.
- Emulating the OR Operator With Arrays – In this video we show how an array formula can select rows for a conditional sum based on an “OR” criteria.
- Creating the MEDIANIF Function – In this video we use an array formula to create a MEDIANIF function.
- Creating the STDEVIF Function – In this video we use an array formula to create a Standard Deviation If function.
- Conditional Products – In this video we show how the SUMPRODUCT function can be used to set up a criteria used to select rows in which columns are multiplied.
- Finding Unique Entries in an Array – In this video we use an array formula to find all unique entries in an array.
- Transpose Function – In this video we show how the TRANSPOSE function can be used to dynamically transpose a row of data into a column.
- Frequency Function – In this video we show how the FREQUENCY array function can be used to count the number of observations in a data set that fall into several bin ranges.
- Matrix Array Functions –In this video we show how to Multiply and Invert matrices.
- Trend Function –In this video we show how to use multiple regression and the TREND array function to predict US Auto Sales.
- Linest Function –In this video we show how the LINEST array function can be used to run a multiple regression which automatically updates when new data is added.
OFFSET AND INDIRECT FUNCTIONS
This chapter covers the Offset and Indirect Functions and consists of 13 topics.
- Offset Function –In this video we define the complex, powerful, and often misunderstood OFFSET function.
- Left Hand Lookup –In this video we use the OFFSET function to emulate a VLOOKUP when the look value is matched in the right-hand and not left-hand column of a spreadsheet range.
- Offset Cost –In this video we show how the OFFSET function can be used to create (in 2 minutes) thousands of formulas that create Phase I, II, and III costs for a drug.
- Offset Account –In this video we show how the OFFSET, MATCH, and INDEX functions can analyze tax data imported from a PDF.
- Most Recent Sales –In this video we show how the OFFSET function is used to return the most recent month of sales.
- Dynamic Range –In this video we show how to create a dynamic range that automatically expands when new rows or columns of data are added.
- Graphing the Last 6 Months of Sales – In this video we create a chart that always shows the last 6 months of sales.
- Indirect Function – In this video we define the complex, powerful, and often misunderstood INDIRECT function.
- Indirect Consolidate – In this video we use the INDIRECT function to consolidate data from any subset of cells in any subset of worksheets into a single summary.
- Indirect Range – In this video we use the INDIRECT function to efficiently copy formulas involving Range Names.
- Worksheet Names – In this video we show how to easily find all the worksheet names in a workbook.
- Nested Drop Down – In this video we show how to create a Nested Dropdown box where the entries in the second dropdown box depend on the value selected in the first dropdown box.
- Indirect Function and Stocks – In this video we show how to use the INDIRECT function to move monthly stock return data in 30 different worksheets to a single worksheet where the data can be analyzed.
OPTIMIZATION AND THE EXCEL SOLVER
This chapter covers Optimization and the Excel Solver and consists of 25 topics.
- Introduction to Solver Part One – In this video we introduce the concept of optimization and show how the Excel Solver can be used to find a profit-maximizing product mix.
- Introduction to Solver Part Two – This video builds upon the previous video on Solver Optimization.
- Introduction to Solver Part Three – This video builds upon the previous two videos on Solver Optimization.
- Transportation – In this video we show how to find the cost minimizing distribution strategy that ships products from plants to customers.
- Scheduling – In this video we show how to use the Excel Solver to find cost minimizing workforce schedules.
- Capital Budgeting – In this video we show how to choose the subset of 20 projects that maximizes NPV subject to limited resources.
- Fantasy Sports Part One – In this video we show how binary changing cells can be used to select Daily Fantasy Lineups.
- Fantasy Sports Part Two – This video is a continuation of the previous Fantasy Sports Example.
- Financial Solver – In this video we show how to use the Solver to solve for monthly mortgage payments and your needed annual retirement contribution.
- NFL Point Spreads – In this video we show how to predict winners of NFL games based on past scores.
- NFL Totals – In this video we show how to use scores of past NFL games to predict scores of future games.
- Pricing Model – In this video we show how to estimate the demand for lipstick and use Solver to find a profit-maximizing price.
- Warehouse Location – In this video we show how to locate one or two warehouses to minimize the distance shipments travel to customers.
- Finding Distances – In this video we give a complex formula that approximates (based on latitude and longitude of two locations anywhere on Earth) the distance between the two locations.
- Demand Sensitivity to Price Change – In this video we show how to determine for a given percentage price change, the change in demand needed to break even.
- Saving for 1 Year Salary – In this video we show how to determine how much money needs to be saved each year so that in 10 years we have accumulated one year of salary.
- Sampling Plan For Attributes – In this video we show how to use the Solver to determine a sampling plan that has a high chance of accepting a batch with a low fraction of defectives and a high probability of rejecting a batch with a high fraction of defectives.
- The Assignment Problem – In this video we show how to use the Solver to assign school bus drivers to bus routes.
- The Transshipment Problem – In this video we show how to minimize the cost of shipping a product from plants through warehouses to customers.
- Inventory Model – In this video we show how to use solver to solve a simple inventory problem.
- The Shortest Route Problem – In this video we show how to use the Solver to find the shortest route between two nodes.
- Hypothesis Testing For a Population Mean – In this video we show how Solver can be used to develop a hypothesis test to determine if students at a school have an average IQ larger than 100.
- Asset Allocation – In this video we use the Solver to allocate an investment portfolio to gold, REITs, stocks, t-bills, and bonds.
- Network Flow Model – In this video we show how to use the Solver to minimize the cost of sending material from one place to another.
- Cheapest Diet – In this video we use the solver to find the cheapest diet of McDonald’s foods that meet various nutritinoal contstraints
FORECASTING
This chapter covers Forecasting and consists of 11 topics.
- Trend and Seasonality – In this video we show how to estimate the trend and seasonal indices for monthly US airline miles.
- Ratio to Moving Average Part One – In this video we show how the Ratio to Moving Average Method is used to forecast time series in the presence of Trend and Seasonality.
- Ratio to Moving Average Part Two – In this video we conclude our discussion of the Ratio to Moving Average Method.
- Winter’s Method of Forecasting Introduction – In this video we show how Winter’s exponential smoothing method can be used to forecast in the presence of Trend and seasonality.
- Winter’s Method – Equations – This video is a continuation of the Winter’s Method of forecasting.
- Winter’s Method – Initialization – This video is a continuation of the Winter’s Method of forecasting.
- Winter’s Method – Implementation – This video is the implementation and conclusion of the Winter’s Method of forecasting.
- Credit Union Forecasting Part One – In this video we show how to incorporate seasonality and special factors in a forecast of a bank’s daily customer count.
- Credit Union Forecasting Part Two – In this video we finish our discussion of forecasting daily customer count at a bank.
- Predicting Software Sales – In this video we show how to use the Excel Solver to determine how seasonality, PC sales, and launch dates affect software sales.
- Forecasting Candy Bar Sales – In this video we show how to use the Solver to determine how our price, the competitor’s price, and whether the product is on display influences candy bar sales.
MONTE CARLO SIMULATION
This chapter covers Monte Carlo Simulation and consists of 14 topics.
- Introduction to Monte Carlo Simulation –In this video we introduce Monte Carlo Simulation. Monte Carlo can model the probability distribution of outcomes in the presence of uncertainty.
- The RAND and RANDBETWEEN Functions –In this video we illustrate the RAND and RANDBETWEEN functions which are the key to Excel simulations.
- Simulating a Discrete Random Variable –In this video we show how to simulate a discrete random variable.
- Simulating a Normal Random Variable –In this video we show how to simulate a normal random variable.
- Simulating a Baseball Inning –In this video we simulate the number of runs a baseball team scores in an inning.
- The Birthday Problem –In this video we use simulation to estimate the probability that if n people are in the room at least two people have the same birthday.
- Craps –In this video we show how to determine the probability of winning at craps.
- The Newsperson Problem –In this video we show how to determine an order or production quantity that maximizes expected profit in the presence of uncertain demand.
- Bidding –In this video we show how to determine a bid that maximizes expected profit given the uncertainty about competitor’s bidding behavior.
- Poker –In this video we show how to “draw” a five card poker hand and calculate the chance of getting two pair.
- Simulating the NBA Finals –In this video we show how simulation can be used to estimate the chance of each team winning the NBA finals.
- Gambler’s Ruin –In this video we answer the question, “If I have $2 and play an unfair game, what is the chance I get to $6 before going bankrupt?”
- Stock Problem –In this video we answer the question, “If a stock is equally likely to return 60% or -40% each year, is it a good stock?” You might be surprised!
- Winning Streak –In this video we show how to determine the average length of the longest winning streak for a team that wins 70% of their games.
ADVANCED SOLVER
This chapter covers Advanced Solver and consists of 17 topics.
- Simplex LP Solver Model – In this video we show how the Simplex LP solver finds an optimal solution.
- GRG Solver Model – In this video we show how the GRG solver finds an optimal solution to “smooth” nonlinear optimization problems.
- Evolutionary Solver Model – In this video we show how the EVOLUTIONARY solver finds an optimal solution to “non-smooth” nonlinear optimization problems.
- Locating One Warehouse – In this video we show how the GRG Solver optimally locates a single warehouse.
- Locating Two Warehouses – In this video we show how the GRG Multistart Solver optimally locates two warehouses.
- Assigning Workers to Jobs Part One – In this video we show how the Evolutionary Solver uses penalties to solve optimization problems using non-smooth functions such as IF and COUNTIF.
- Assigning Workers to Jobs Part Two – This video is the conclusion of the previous example of Assigning Workers to Jobs.
- Scheduling John Deere – In this video we show how John Deere used the Evolutionary Solver and the concept of target cell “penalties” to schedule the production of riding mowers.
- Traveling Salesperson – In this video we show how the Evolutionary Solver is used to solve sequencing problems.
- Estimating Elasticities – In this video we use the GRG Multistart solver to estimate product and cross product elasticities.
- Advanced Learning Curve – In this video we show how to fit customized learning curve models.
- Bin Packing Problem – In this video we show how to use the Evolutionary Solver to solve the classic Bin Packing Problem.
- Final Exam Scheduling – In this video we use the Evolutionary Solver to determine a final exam schedule that eliminates student conflicts.
- Discriminant Analysis – In this video we show how to use the Evolutionary Solver to develop a linear scoring rule that effectively classified data points into one of two groups.
- Cluster Analysis –In this video we use the Evolutionary Solver to show that every US city is demographically similar to either LA, Memphis, SF, or Omaha.
- Resolving Process Bottlenecks –In this video we use the evolutionary solver to find the minimum cost method of providing sufficient process capacity to meet demand.
- The Toyota Manufacturing Sequence Algorithm –In this video we use the DIF capabilities of the Evolutionary Solver to implement the Toyota goal chasing method for scheduling car production.
EXCEL 2016 CHARTS
This chapter covers Excel 2016 Charts and consists of 10 topics.
- Histograms – In this video we show how to use Excel 2016 to create much improved histograms.
- Pareto Charts – In this video we show how to use Excel 2016 to create much improved Pareto charts.
- Boxplots One Variable – In this video we show how to use Excel 2016 to create a Boxplot to summarize the results of the 1970 Draft Lottery.
- Boxplots More Than One Variable – In this video we show how to use Excel 2016 to create a Boxplot that summarizes multiple test scores at three high schools.
- Waterfall Chart – Time Series Data – In this video we show how to use an Excel 2016 Waterfall chart to summarize the progress of sales during a year.
- Waterfall Chart – Revenue to Profit – In this video we show how to use an Excel 2016 Waterfall chart to summarize how various cost centers reduce Company Revenue and Profit.
- Treemap Chart – Bookstore – In this video we use an Excel 2016 TreeMap or Mosaic chart to summarize hierarchical bookstore sales data.
- Sunburst Chart – Bookstore – In this video we use an Excel 2016 Sunburst chart to summarize hierarchical bookstore sales data.
- Sunburst Chart – Monthly Breakdown – In this video we use an Excel 2016 Sunburst chart to summarize quarterly and monthly sales data.
- Treemap Chart -Restaurant – In this video we use the SUMIFS functions to prepare restaurant sales data and then summarize the sales data with a TreeMap chart.
SUPPLY CHAIN
This chapter covers Supply Chain and consists of 14 topics.
- Inventory on Time – In this video we use optimization to find a minimum cost production schedule that meets demand on time.
- Inventory Modeling With Shortages – In this video we show how to create a linear solver model which allows for shortage costs as well as holding costs.
- Aggregate Planning – In this video we extend our Solver Inventory model to account for hiring and firing workers.
- Economic Order Quantity – In this video we discuss the famous EOQ formula, which recommends the cost minimizing order trading off holdings and ordering costs.
- Power of Two – In this video we show how Power of Two ordering policies simplify the coordination of ordering when several products are involved.
- Multiple Product Economic Order Quantity – In this video we show how coordinating ordering of multiple products can reduce supply chain costs.
- Quantity Discounts – In this video we show how to determine the optimal order quantity when large order quantities result in quantity discounts.
- Cycle Service Level – In this video we define Cycle Service Level (CSL) and show how to determine a reorder point and safety stock that results in a desired CSL.
- Fillrate, Reorder Point, and Safety Stock – In this video we define Fillrate and show how to determine a reorder point and safety stock that result in a desired Fillrate.
- Finding Distances Between 100 Cities – In this video we use a two-way data table to find the distances between each pair of 100 world cities.
- Locating Four Warehouses – In this video we locate 4 warehouses to optimally serve 100 cities.
- Jobshop Scheduling – In this video we show how to schedule jobs to minimize number of late jobs and total days jobs are late.
- Queuing – In this video we discuss how to compute the average number of people in line and the average time to spend waiting in line.
- Inventory Turns – In this video we show how to use Little’s Law to compute a firm’s inventory turns.
CHARTING
This chapter covers Charting and consists of 6 topics.
- Check Boxes and Chart Dashboards – In this video we show how to use CheckBoxes to control which series show in a chart.
- List Boxes and Chart Dashboards –In this video we show how to use List Boxes to control which series shows in a chart.
- Spinners –In this video we show how to create a Spinner that enables you to change the values of any number of spreadsheet inputs by clicking on your Spinners.
- Creating Charts With GETPIVOTDATA –In this video we use GETPIVOTDATA and the Excel Table feature to create dynamic charts based on a PivotTable.
- Sparklines Part One –In this video we use Sparklines to visually summarize daily customer counts in different bank branches in a single cell for each branch.
- Sparklines Part Two –In this video we use Win Loss Sparklines to visually summarize week by week NFL results.
POWER VIEW
This chapter covers Power View and consists of 5 topics.
- Introduction to Power View –In this video we introduce the cool charting capabilities of Power View by summarizing iced tea sales.
- Power View Tiles –In this video we show how to use Tiles to chart tea sales in a selected state.
- Power View Map –In this video we show how to use a map to summarize iced tea sales in each state.
- Power View Multiples –In this video we show how Power View Multiples quickly create a separate chart summarizing sales of each product sold.
- Power View Movies –In this video we show how to create a “movie” that shows how three US crime statistics change over time.
MACROS
This chapter covers Macros and consists of 10 topics.
- Inserting the Developer Tab – In this video we show how to make the Developer Tab appear on your ribbon.
- Inserting a List of Names – In this video we record a Macro that inserts a list of team members starting in cell A1. We also create a button to run the macro.
- Relative References With Macros – In this video we show how to use Relative References to make sure our list of names can be entered anywhere in our workbook.
- Using Macros to Format Cells – In this video we create a macro to apply a desired format to a range of selected cells and show how to run the Macro by clicking on a picture (in this case an Aircraft Carrier).
- Formatting a Range of Variable Size – In this video we show how to create a macro that formats a range of variable size.
- Using a Macro to Freeze Values – In this video we create a macro that coverts formulas in a selected range to numerical values.
- Using Macros For Reporting – In this video we create macros that enable us to toggle in a PivotTable between a view that shows sales for all customers and sales for only our top 20 customers.
- Creating a Border With a Macro – In this video we show how to create a macro that places a border around a range of selected cells.
- Personal Macro –In this video we discuss how to save a macro to your Personal Macro Workbook.
- Sorting Macro –In this video we show how to create a macro that will sort a list by last and first name.
Get the Advanced Course for Just $397
This is the advanced training of Dr. Winston. This course builds upon his 40 years of experience helping virtually all of the Fortune 500 companies through his books or in person training. 245+ videos, 500+ downloadable files covering 26+ hours of content. Just scroll up to see what each video covers. Give us a try and if at anytime in the first 30 days you are unsatisfied, just reach out and we will give you a 100% refund. No questions asked.
Why Should You Enroll in The Advanced Course?
Lifetime Access
After enrolling, you will get lifetime access to more than 245 videos covering 26+ hours of content and 500+ downloadable Excel files.
Watch On Demand
All of our videos are available to you 24/7 from anywhere, from any device. All you need is an internet connection.
30-Day Money-Back Guarantee
If at anytime in the first 30 days you are unsatisfied, just reach out and we will give you a 100% refund. No questions asked.
Learn Excel from an Award Winning Professor
Dr, Winston is the course creator for Harvard, and was asked by Microsoft to write the world’s best selling book on data analysis and business modeling. He has also won more than 40 teaching awards and is a 2 time Jeopardy! champion!
Perfect Course!
5/5
I’m on day 4 and already feeling more knowledgeable and empowered!
Jeff Skirvin
Amazing & Useful Content
5/5
I’ve enjoyed the training very much. I’ve been using excel for many years, but with Wayne’s training, I’ve been able to do things quicker which saves me time and allows me to complete my projects faster. I recommend this training. It’s awesome. Thanks Wayne.
Luis Arroyo
Best Course I've Taken
5/5
Best professor I’ve ever had. I’d take him for anything and think he could teach anything, but Decision modeling in spreadsheets was the best, most relevant (for a finance major like me at least) class I had as an MBA student. Hope you are well Professor Winston!
Todd Williams
Wayne Winston
- Author
Unlock Lifetime Access
Now For Just $397
Perfect Course!
5/5
I’m on day 4 and already feeling more knowledgeable and empowered!
Jeff Skirvin
Amazing & Useful Content
5/5
I’ve enjoyed the training very much. I’ve been using excel for many years, but with Wayne’s training, I’ve been able to do things quicker which saves me time and allows me to complete my projects faster. I recommend this training. It’s awesome. Thanks Wayne.
Luis Arroyo
Best Course I've Taken
5/5
Best professor I’ve ever had. I’d take him for anything and think he could teach anything, but Decision modeling in spreadsheets was the best, most relevant (for a finance major like me at least) class I had as an MBA student. Hope you are well Professor Winston!