Statistics for Business Analytics using MS Excel

Celebrate reaching a milestone in the Statistics for Business Analytics using MS Excel course. Stay motivated, and explore playback controls, subtitles, offline downloads, Q&A, AI assistant, and certificates.

Learn how to use essential Excel functions—sum, average, concatenate, and trim—to analyze project costs, create descriptive strings, and clean data, with practical examples and tips.

Learn to join tables with vlookup, perform exact matches with fixed lookup ranges, and use if, countif, countifs, sumif, and sumifs to analyze data.

master sorting and filtering data in excel, use custom sort for multi-level ordering, apply numeric and text filters, and set data validation with lists and range rules.

Learn to split text into columns in Excel using text to columns with delimited options, and remove duplicates by sorting on English marks to retain the top entries.

Master advanced filters in Excel to apply multiple criteria, output results to a new location, use wildcards, and extract unique records with selected columns.

Explore pivot tables in Excel to summarize sales data with rows and columns, showing counts and sums by salesperson, region, and date, using filters, slicers, and drag-and-drop fields.

Explore probability concepts and probability distributions, calculate probability in Excel, and study key laws and distributions such as Poisson, binomial, exponential, and normal.

Explore the basics of probability and its role as the foundation of business analytics and data science, describing sample space, events, and how likelihood informs decisions under uncertainty.

Learn to calculate probability in Excel with two classic cases—coin toss and dice roll—using countA for text sample spaces, absolute references, and converting results to percentages.

Analyze past project data in Excel to estimate month-by-month completion probabilities using countif and pivot table methods, with probabilities computed as counts over total and shown as percentages.

Explore the complementary law of probability and the addition law, using venn diagrams to relate p(a) and p(a^c), and handle both mutually exclusive and not mutually exclusive events.

Apply complementary and addition probability laws in Excel to calculate outcomes for dice rolls and project timelines, and visualize results with column charts.

Describe probability distributions as the mapping of outcomes to probabilities, visualize them, and learn to compute the mean, variance, and standard deviation using weighted sums.

Compute the mean, variance, and standard deviation in Excel from a probability distribution. Calculate the expected value and variance using sums of value times probability, then derive the standard deviation.

Explore discrete versus continuous probability distributions by defining the random variable, using coin toss and dice as discrete examples, and distinguishing probability mass from probability density distribution.

Explore practical use of predefined discrete and continuous probability distributions in business analytics with Excel, enabling quick identification, analysis, and prediction without extensive data calculations.

The discrete uniform probability distribution assigns equal probability to all outcomes, such as six die faces, and applies to totally random experiments like coins, dice, cards, or slot machines.

Learn the discrete binomial probability distribution, its two-outcome trials, and applications like coin tosses, defects, or email responses, with p, n, and x, plus software-based calculation.

Apply binomial distribution to a restaurant case: calculate exactly 20 customers from 100 people (p=0.17) using Excel's norm.dist, including cumulative probability, complementary law, and expected value.

Explore the discrete Poisson distribution, modeling event counts in a time or space interval with lambda as the mean and variance, applied to arrivals, orders, potholes, and workforce.

Learn to apply Poisson distribution in Excel to estimate monthly car demand, using lambda 13 and Poisson.dist to compute exact and cumulative probabilities for inventory decisions.

Explore continuous probability distributions by defining probability density function for a continuous variable, over intervals not points, and compute probabilities as the area under the curve.

Explore how the uniform continuous distribution assigns constant probability over an interval, with density 1/(B−A), and compute interval probabilities, mean, and variance using a chocolate bar example.

Explore the normal distribution, its bell curve, and how mu and sigma define its location and shape. Apply area under the curve and z-scores to practical business analytics with Excel.

Analyze a normal distribution with mean 4500 days and standard deviation 600 to estimate the 3650-day replacement probability and determine a 15% warranty threshold.

Understand the exponential distribution for the time between consecutive events, using lambda (arrivals per hour) and mu (1/lambda); applied to wait times, e.g., next airline customer within three minutes.

Calculate the cumulative probability that the next business class passenger arrives within three minutes using the exponential distribution, with unit conversion and Excel's exponential dist function to model waiting times.

Explore how probability and statistics drive business decisions by using sampling, statistical inference, and hypothesis testing in Excel, with real-world examples of customer demographics, call center quality, and product preferences.

Understand how sampling reveals business insights by selecting a representative population subset. Explore probability and non-probability methods, including random, stratified, cluster, multi-stage, and systematic sampling.

Use a sample to estimate population parameters, including mean, standard deviation, and proportion, via sample statistics and point estimation. Learn how interval estimates and confidence levels assess prediction reliability.

Generate random numbers with Excel's Rand, sort to shuffle 20,000 households, and select the first 50 as the sample to study income and voting for candidate A.

Compute point estimates for population mean from a 50-house sample in Excel, using mean, standard deviation, and sample proportion. Apply average, stdev.s, and countif to derive them.

Learn about sampling distributions of the sample mean and proportion, including their expected values. Understand their standard deviations and the central limit theorem for when normal approximations apply.

Explore how sample means converge to the population mean and how the standard deviation of sample means scales with sample size, illustrated through a dice example and central limit theorem.

Learn to construct interval estimates for population means and proportions from sample data, applying margins of error and 95% confidence, using z for known sigma and t for unknown sigma.

Learn to construct interval estimates for population mean and proportion in Excel by computing the standard deviation of sample means and applying a 95% two-tailed t approach.

Compute a 95% interval estimate for a proportion using the sample of 50 households (sample proportion 0.60), the standard deviation of proportion, and z-based boundaries.

Fix the desired interval width first, then calculate the required sample size using the sample standard deviation and degrees of freedom to achieve that precision.

Solve an end-to-end case using a 40 MBA student sample to compute 99% confidence intervals for the mean laptop price and the proportion willing to pay for premium speakers.

Explore how hypothesis testing helps make business inferences by proving or disproving theories. Learn how to frame null (h0) and alternate (ha) hypotheses and choose one-tailed or two-tailed tests.

Explore type 1 and type 2 errors within hypothesis testing, distinguishing between rejecting or not rejecting the null hypothesis, and weighing costs in practical statistical inference.

Identify the null and alternative hypotheses for the new packaging, compare one-sided and two-sided tests, and explain how the sampling distribution of X-bar informs whether to reject the null.

Learn how hypothesis testing uses p-values and alpha to evaluate whether the new packaging affects sales by comparing observed samples to the old distribution.

Learn to compute the p-value from the sample mean using t or z statistics, compare with alpha, and decide whether to reject the null in one- or two-tailed tests.

Explore the t distribution and z distribution formulas in Excel, including t.dist, t.dist.2t, t.dist.rt, t.inverse, t.inverse.2t, and norm.dist, to compute left, right, and two-tailed areas and corresponding values.

Explore normal distribution calculations in excel using NORM.DIST, NORM.S.DIST, and NORM.S.INV to find left-tail and two-tailed probabilities with a customizable mean and standard deviation.

Analyze a vaccination case study using a left-tail hypothesis test for population proportion to show a 0.0053 p-value and 99.9% confidence in vaccine effectiveness.

Assess a one-tailed t test for ecommerce sales to determine if a new design increases daily sales beyond 300, with 99% confidence.

Transform business problems into optimization models in Excel using Goal Seek, Scenario Manager, and Solver, then apply cases like transportation, price skimming, and customer lifetime value.

Explore Excel's what-if analysis with goal seek and scenario manager to optimize revenue from mango and chocolate shakes, using a 100-glass milk constraint.

Use Excel's solver with simplex method to maximize profit from mango, banana, and chocolate shakes under budget and space limits, yielding 950 with 8.33 mango and 13.33 chocolate shakes.

Learn when to use GRG nonlinear, simplex LP, or evolutionary methods in Excel Solver; distinguish linear versus nonlinear relationships and select the robust option for complex problems.

Solve a transportation problem to minimize total cost by allocating units from factories to shops. Use Excel solver and sum product to meet shop demands and optimize costs.

Analyze how price skimming sets a high initial price to capture high-valuation customers, then lowers prices to reach remaining segments as competition and learning effects unfold.

Explore how to maximize revenue with a four-quarter price skimming strategy in Excel, using perceived value, count-based demand, and solver to optimize prices.

Learn to calculate customer lifetime value by discounting future revenue with a discount rate and retention rate, compute net present value, and apply the method in Excel for pricing decisions.

Learn to calculate customer lifetime value and net present value in Excel by modeling 20 years of customers, retention rates, and discount rates, with end-of-year cash flows and NPV.

Explore predictive analytics by identifying relationships between variables and predicting key business outputs with a linear regression model, and master data pre-processing and interpretation of results.

Identify the business context and key factors to select relevant variables, gather data through primary research with stakeholders and secondary research from industry studies, because input quality drives outputs.

Identify required data from internal and external sources, request it, and perform quality checks to define variables; analyze cart abandonment by channel and cart value.

Learn to assemble data into a house pricing dataset with 506 observations and 19 variables. Identify price as the dependent variable and create a data dictionary with definitions and keys.

Explore univariate analysis by examining descriptive statistics for each variable, including mean, median, mode, range, quartiles, and standard deviation, with counts for categorical data.

Learn to perform univariate analysis in Excel with the data analysis add-in, using descriptive statistics to summarize mean, median, and mode for quantitative variables and identify missing values and outliers.

Identify and treat outliers in business analytics with Excel, using box plots, scatter plots, and histograms, and apply capping, percentile-based limits, exponential smoothing, or sigma rules to impute values.

Identify outliers via univariate analysis, then cap them with an upper limit. Apply Excel filters to replace hot rooms outliers with three times the largest normal value, 46.2, noting subjectivity.

Learn to handle missing values in business analytics by deleting rows, imputing with zero, mean, or median values, or segment-based and most frequent category imputation, guided by business knowledge.

Identify and treat missing values in Excel by filtering blanks, replacing with the mean of the n host beds variable, and pasting as values to ensure clean data.

Transform four distance variables into a single average distance using the mean method to represent employment proximity, then insert, calculate, and replace with the new variable in Excel.

Explore dummy variable creation to encode categorical data for regression, coding categories as 0 or 1 and using k minus one dummies to avoid ordinal interpretation.

Explore how to identify correlation between variables, interpret correlation coefficients, and use correlation matrices to manage multicollinearity in business analytics with Excel.

Learn to create a correlation matrix in Excel with the data analysis add-in, output to a new sheet, and interpret correlations, including crime rate and price (-0.46) and multicollinearity.