Introduction to Supply Chain Analytics using Microsoft Excel

Explore descriptive analytics with a fictional supply chain case study in Excel, quantify line rejects, and estimate parts per eight-hour shift.

Explore descriptive analytics in Excel by using formulas to compute max, min, range, median, mean, and quartiles, and examine how outliers affect the data distribution through nonparametric statistics.

Explore quartiles as measures of data dispersion, defining quartiles from minimum to maximum in Excel, identifying the median at quartile two and interquartile range as quartile three minus quartile one.

Compute quartiles in Excel for lines A, B, and C, including quartile four (max), quartile zero (min), quartile two (median), and the interquartile range with a single formula.

Explore how to create and interpret box and whisker plots in Microsoft Excel, comparing multiple data series, identifying medians, means, outliers, and distribution quickly.

Explore data visualizations in Excel by creating histograms, a type of bar chart with equal-sized bins, to analyze frequency distributions and customize bin width.

Explore how Excel histograms reveal distribution shapes as sample size grows, adjust bin size and width, and identify the normal distribution or bell curve as a common underlying probability distribution.

Shift from non parametric to parametric statistics by using mean and standard deviation to model a normal distribution for inferring population parameters from samples.

Shift from data to probability distributions using histograms and samples; apply inferential statistics, normal distribution parameters, mean and standard deviation, for supply chain analytics.

Examine the normal distribution, its mu and sigma, and how the mean and standard deviation shape the curve, with 68.3%, 95.5%, and 99.7% within 1–3 sigma in Excel.

Explore descriptive analytics in Excel pt 2 by computing the sample mean and sample standard deviation, applying the three-sigma range, and comparing observed values to expected population limits.

Use Excel's norm inverse function to determine the value below which a chosen population percentage lies, given the mean and standard deviation, and explore the middle third range.

Add line means to estimate the department output, then combine normal distributions by sqrt of sum of squared standard deviations; know that variance equals the square of the standard deviation.

Sum normal distributions in Excel by adding line means, compute each line’s variance, then take the square root of the sum of variances to obtain the department’s standard deviation.

Calculate process yield across a series of production lines by applying the mean divided by (1 minus the reject rate) to determine input needs and department output.

Use the triangular distribution for bounded, uncertain variables like lead times and production yields; compute the mean as (a+b+c)/3 to support new product forecasting, supplier lead times, and project planning.

Understand the cumulative distribution function of the triangular distribution to compute probabilities and service levels. Apply the a, b, c parameters and the four cdf cases to determine outcomes.

Explore the inverse CDF of a triangular distribution to target cycle service level, using A, B, C and 95% CSL to set production.

Build a triangular inverse CDF in Excel using A, B, and C for min, max, and most likely values, with an if-based formula to pick the distribution branch.

Access the free downloadable formula sheet for the triangular distribution, including the cumulative distribution function, mean, variance, and median, to extend your analysis in data-scarce scenarios.

Master descriptive analytics to answer what happened using mean, median, and range. Explore parametric and nonparametric tools, normal distribution, and Excel analytics like Norm.dist and Norm IV.

Apply descriptive analytics to a two-machine case study; build histograms, compute average and standard deviation, and use parametric statistics such as norm inverse and three-sigma bounds, for 1,000 final pieces.

Use predictive analytics on a four-year quarterly sales case study—elemental clothes dryer, absolute zero ice maker, signature stove, and intellectual refrigerator—to forecast next year's demand, noting seasonal and growth patterns.

Explore four major demand forecasting families—qualitative forecasting, time series analysis, causal analysis, and simulations—then focus on time series analysis to extrapolate past trends into future demand.

Explore time series analysis to forecast sales data by examining baseline level, trend, seasonality, and random variation. Learn static and cumulative methods as easier approaches to time series forecasting.

Discover how to enable and use Excel add-ins—analysis toolpak and solver—for time series analysis, descriptive statistics, and regression in predictive analytics.

Analyze time series data in Excel using a baseline model, compute mean and standard deviation, and project quarterly dryer sales for 2023 between 718 and 902.

Master a quick shortcut in Excel's data analysis tool to generate descriptive statistics for your baseline analysis, including mean, standard deviation, min, max, median, skewness, and kurtosis.

Analyze seasonality in ice maker sales, compute annual averages and seasonal indices, deseasonalize data, and generate seasonal forecasts by applying indices to the deseasonalized mean in Excel.

Generate upper and lower forecast estimates for seasonal sales in Excel using the center value and quarterly seasonality indices to create quarterly ranges.

Introduce the Cartesian coordinate system and the line equation y = a + b x, explain the y-intercept and slope, and illustrate rise over run with examples for trend analysis.

Use linear trend analysis and regression to model past signature stove sales in Excel. Interpret R squared and forecast next year's quarterly sales with a simple equation.

Explore how regression analysis uses past data to predict future outcomes, compare linear, exponential, and other models via R-squared, and interpret independent and dependent variables in Excel.

Analyze seasonality and growth in quarterly sales using Excel by converting data to column format, computing seasonal indices, deseasonalized data, fitting a linear trend, and forecasting with seasonality and growth.

Review analysis methods for baseline, seasonal, and trend data to forecast periods. Use central tendency, dispersion, seasonal indices, deseasonalized data, and regression to generate forecast ranges and caution on extrapolation.

Explore launch points in predictive analytics, from descriptive statistics and trend lines to moving averages, causal analysis, and exponential smoothing.

Explore prescriptive analytics practice problems to sharpen predictive analytics skills using a furniture sales case study, building run charts and forecasting the next 12 months.

Discover prescriptive analytics in supply chain using Excel solver, from the economic order quantity algebraic formula to linear programming and optimization, guiding the best action under constraints.

Explore how a purchasing manager uses prescriptive analytics to determine the economic order quantity for titanium bolts, balancing fixed ordering costs and annual holding costs to minimize total inventory cost.

Discover how the economic order quantity (EOQ) minimizes total inventory costs by balancing fixed ordering costs and holding costs, while recognizing its analytical, not perfect nature.

Explore the economic order quantity (EOQ) to minimize total relevant costs by balancing holding and ordering costs using the square root formula.

Learn to compute the economic order quantity (EOQ) in Excel using a case study, balancing holding costs and ordering costs to minimize total relevant costs.

Learn to build an EOQ data model in Excel, explore how changes in annual demand and order quantity affect ordering and holding costs, and use solver for optimization.

Learn to use Excel's solver to minimize total relevant costs by selecting an objective and non-negative variable constraints in a nonlinear programming model for order quantity decisions.

Explore mathematical programming as a management tool to optimally decide economic order quantity (EOQ) with Excel solver, covering objective function, decision variables, and constraints, plus linear, nonlinear, and evolutionary algorithms.

Explore the economic production quantity (epq) model, balancing setup and holding costs to minimize total cost as items are produced in batches with a finite production rate and simultaneous demand.

Explore the economic production quantity model with periodic, incremental production and a constant rate, and apply it to a steel shaft case to compute epq, Tmax, and Imax.

Apply the economic production quantity model in Excel by building a data model, calculating Imax, holding and ordering costs, and validating results with a solver-based optimization.

Explore how adding a minimum days between orders constraint to the economic order quantity model using solver increases annual costs, and how ordering more frequently could save money.

Explore a case study on maximizing profits for Snackies' three nut products using Excel solver. Build a data driven analytical model that handles ingredient costs, percentages, and supply constraints.

Practice building an Excel model to maximize profit in a product mix problem using linear programming and sumproduct, with regular, deluxe, and holiday blends and supply constraints on nut pounds.

Compute profitability for the product mix by subtracting costs from selling price, then apply a sumproduct approach to multiply cost per pound by nut usage and optimize under supply constraints.

Use Excel solver to maximize profitability by solving a linear program with nonnegative, integer constraints and nut supply limits, revealing the optimal holiday blend quantities.

Practice building your own prescriptive models in Excel and explore sensitivity analysis, including binding versus non-binding constraints, shadow prices, and Excel solver reports to boost your prescriptive analytics.

Apply prescriptive analytics to determine the optimal mix of birdbaths and large planters to maximize profits under shaping, painting, and firing hours using Excel solver.

Explore supply chain analytics with Microsoft Excel and apply tools and concepts to solve organizational problems. Enjoy lifetime access and the opportunity to continue learning and refine your skills.

Discover a bonus lecture by Ray Harkins that highlights quality engineering, Lean Six Sigma, business finance, metallurgy, engineering drawings, design thinking, and leadership courses.