Intro to Data Analytics using Excel

Explore how big data, a very large dataset, can be analyzed in real time to reveal patterns, trends, and the three Vs: velocity, volume, and variety.

Data analytics turns big data into insight by using qualitative and quantitative techniques to identify patterns, ask the right questions, blend data visualization with statistical techniques to predict the future.

Explore descriptive analytics, predictive analytics, and prescriptive analytics to understand past data, forecast future trends, and guide data-driven decisions in business contexts.

Explore how to assess the gender pay gap at a company using HR data in Excel, including data structure, variables, and how asking the right questions guides analysis.

Learn to calculate overall and gender-specific averages in Excel using average and averageifs, with named ranges, to investigate pay inequality and deepen data-driven questions.

Compute standard deviation (sample) of salaries to reveal how pay varies, compare women and men, and show women's salaries cluster tightly with fewer outliers while men exhibit a wider range.

Use minimum and maximum in Excel to identify outliers, compare salary distributions by gender, and apply percent rank inc to explore division-level pay gaps.

Calculate the average salary by division and gender to reveal pay disparities, using averageifs to avoid div/0 errors. This analysis reveals pay gaps, including no women in executive role.

Analyze salary data with descriptive analytics in Excel to compare men and women across divisions, excluding executives, and reveal a persisting gender pay gap of about five percent.

Explore how to build and interpret a histogram in Excel to visualize salary distribution, set buckets and increments, and assess data skew and normality.

Implement Pareto analysis in Excel using a pivot table to identify which divisions drive the most salaries and explore gender pay disparities.

Compute standard deviation to reveal variance across divisions, highlighting executive and admin pay volatility, then apply Prideaux 80/20 analysis to identify the divisions driving most salary disparities.

learn to perform a pareto analysis in Excel by building a pivot table with running total and running percent to identify sales, production, administration, and service as salary drivers.

Complete the Pareto analysis with a pivot table and a combo chart on a secondary axis to compare total salaries and percentages, highlighting divisions with pay disparities.

Explore how to prepare data for correlation analysis by converting gender to numeric values, enabling accurate assessment of salary differences using Excel.

Learn to calculate the correlation between gender and salary in Excel using the correlation function and interpret a weak negative relationship, around 0.3, across data and within administration.

Explore data analysis in excel by asking more questions, testing hypotheses, and using alternative data slices like maternity leave and gender gaps to uncover insights.

Explore how to use a scatter plot and a trend line in Excel to analyze sales and cost of goods sold, assess correlation, and predict future costs with regression.

Learn to enable the Excel Analysis ToolPak, run a regression with defined y and x ranges, and interpret outputs like correlation and R-square to assess model fit.

Explore p-values and significance in regression, interpret sales and intercept p-values, and use the line equation to predict next month's sales from a strong model with r=0.96 and r^2=0.92.

Create a simple linear model to forecast cost of goods sold from sales using y = mx + b, with a 32 percent slope, and compare projections to actual results.

Explore regression analysis with the 95 percent confidence interval, using intercept and slope to bound forecasts for cost of goods sold and sales and identify outliers.

Investigate how turnover might explain the gender salary gap by moving beyond gender and division, using a pivot table to compare divisions with higher than average turnover.

Learn to create a pivot table in Excel, add a calculated field for turnover percent from hire and end dates, and analyze turnover by division and year.

Analyze turnover by division and pay variance to see why sales and production rise while administration remains lower, and learn to slice data in different ways to reveal hidden relationships.

Explore turnover by division with a gender split to reveal higher turnover among women than men across departments, and consider maternity leave as a potential contributing factor.

Investigate the relationship between turnover and the pay gap, including maternity, test tenure-based wage hypotheses, and examine how junior and senior hires influence salary levels in real data analytics.

Use a pivot table to compute the average tenure from hire and end dates, creating a months-with-company metric with a rounded formula and blanks for ongoing employees.

Calculate and compare employee tenure using a pivot table in Excel, analyzing gender and maternity leave effects on average tenure.

Explore how maternity leave affects tenure and turnover, test wage-gap theories, and apply Excel counts to quantify leave and non-return rates.

Investigate how maternity leave policy drives turnover and wage disparity, citing Family Medical Leave Act and a Department of Labor study on paid family leave in California and New Jersey.

Quantify recommendations using predictive analytics to estimate costs and savings, showing how maternity leave policy could reduce turnover and improve decisions.

Compute the cost of employee turnover using Excel by comparing actual leave to national averages, estimating replacements costs at six months of salary, and totaling 3.1 million over four years.

Calculate maternity turnover cost by division using count of nonreturning post maternity leaves, then multiply the division's average salary divided by 12 by the average cost per employee.

Propose an eight-week maternity policy with paid leave and downtime; estimate costs using average weekly salary and downtime at half the paid leave, totaling about $353k.

Calculate how a revised maternity-leave policy lowers turnover from 81% to 30%, estimate costs using average salary and months, and reveal four-year net savings and pay-gap implications.

Learn to calculate ROIC from savings divided by policy costs, and build data tables in Excel to analyze scenarios for maternity policy outcomes.

Learn to build a data table for what-if analysis in Excel, using row input cell, column input cell, and conditional formatting to compare maternity leave and turnover.

Create a simple yet dynamic dashboard that summarizes analytics findings on one page and lets users explore how changing assumptions alter outcomes.

Build an interactive Excel dashboard by adding a spin button to adjust turnover cost in months, linking it to calculations of average salary, months, and employees.

Recalculate turnover and maternity leave costs in the dashboard by applying the revised turnover rate, combining paid leave with downtime costs, and compute net and yearly savings.

Explore building an interactive Excel dashboard with form controls, scroll bars, and spin buttons to adjust weeks and percentages, link cells, and illustrate maternity policy cost savings.

Apply what you've learned by analyzing your data and asking more questions. Explore dashboards and visualizations in Excel and Power BI to deepen data analytics.