Complete Guide to Excel 365: What-If Analysis, Solver, & Analysis ToolPak

Excel    |    Expert
  • 12 videos | 1h 35m 51s
  • Includes Assessment
  • Earns a Badge
Rating 4.6 of 213 users Rating 4.6 of 213 users (213)
Excel's Scenario Manager allows you to create named scenarios with different values for key variables. These scenarios can be quickly examined and summarized using the What-If Analysis tool. In this course, you'll use GoalSeek and Solver, both of which are mathematical tools. Goal Seek comes in handy when solving quadratic, cubic, or other equations. Solver is much more powerful and allows complex constrained optimization problems to be easily defined and addressed. As you'll recognize, Solver's interface, used for specifying objective functions and constraints, is intuitive and easy to use. Next, you'll use several heavy-duty statistical techniques in Analysis ToolPak. These range from the creation of correlation and covariance matrices, hypothesis testing, and F-test and T-test interpretations to ANOVA, random and periodic sampling, and the construction and analysis of linear regression models.

WHAT YOU WILL LEARN

  • Discover the key concepts covered in this course
    Use the what-if analysis feature in excel, create scenarios based on specific cell values, assign names to these scenarios, and toggle between them, summarize a range of outcomes across scenarios by performing what-if analysis, and interpret the returned results  
    Use goal seek for solving simple univariate optimization problems, such as solving univariate equations where a target output cell must be set to a specific value by changing a single input cell
    Enable the solver add-in and use it to perform complex multi-variable optimization, specify objective functions to min/max or specific values, and specify non-negativity conditions and solution methods for contraints
    Perform simple statistical analysis of two-dimensional data, compute measures of central tendency (mean, median, mode) and dispersion (standard deviation and variance), define bessel's correction, and calculate skew and kurtosis  
    Enable analysis toolpak and use it to compute correlation and covariance matrices, interpret the results, and recognize the link between covariance and variance
  • Implement hypothesis testing using the analysis toolpak, perform the two-sample f-test for variance equality and two-sample t-test for equality of means, and interpret the significance level (alpha), test statistic, and p-value
    Implement anova (analysis of variance) using analysis toolpak to analyze variances within and between groups  
    Use analysis toolpak for histogram analysis and descriptive statistic computing, compute ranks and percentiles, and perform both random and periodic sampling  
    Generate random numbers drawn from various distributions using analysis toolpak, apply normal, bernoulli, poisson, and other distributions, specify population parameters, such as mean and variance, and recognize why sample mean and variance might differ from them  
    Perform linear regression using analysis toolpak, interpret the results of regression including r-square, p-values, and t-statistics of individual regression coefficients, and identify the benefits of using analysis toolpak over worksheet functions such as linest(),slope(), and intercept()  
    Summarize the key concepts covered in this course

IN THIS COURSE

  • 2m 20s
  • 11m 23s
    In this video, you will learn how to use the What-If Analysis feature in Excel to create scenarios based on specific cell values, assign names to these scenarios, and toggle between them. You will also learn how to summarize a range of outcomes across scenarios by performing What-If Analysis and interpret the returned results. FREE ACCESS
  • Locked
    3.  Using Goal Seek
    7m 17s
    In this video, you will use Goal Seek to solve simple univariate optimization problems, such as solving univariate equations where a target output cell must be set to a specific value by changing a single input cell. FREE ACCESS
  • Locked
    4.  Using Solver for Complex Optimization
    7m 44s
    To enable the Solver add-in:1. On the File tab, choose Options.2. On the Add-Ins tab, in the Manage box, select Excel Add-ins and then click Go.3. In the Add-Ins available box, select the Solver Add-in check box, and then click OK.To use Solver:1. On the Data tab, in the Analyze group, click Solver.2. In the Solver Parameters dialog box, click the Set Objective button.3. In the Set Objective dialog box, click the cell that contains the objective function coefficients.4. To minimize the objective function, click Min. To FREE ACCESS
  • Locked
    5.  Calculating Simple Descriptive Statistics
    11m 34s
    In this video, you will perform simple statistical analysis of two-dimensional data, compute measures of central tendency (mean, median, mode), dispersion (standard deviation and variance), define Bessel's correction, and calculate skew and kurtosis. FREE ACCESS
  • Locked
    6.  Analysis ToolPak: Analyzing Matrices
    10m
    During this video, you will learn how to enable the Analysis ToolPak and use it to compute correlation and covariance matrices, interpret the results, and recognize the link between covariance and variance. FREE ACCESS
  • Locked
    7.  Using Analysis ToolPak for Hypothesis Testing
    11m 36s
    In this video, you will learn how to use the Analysis ToolPak to conduct hypothesis testing, including the two-sample F-test for variance equality and two-sample t-test for equality of means. You will also learn how to interpret the significance level (alpha), test statistic, and p-value. FREE ACCESS
  • Locked
    8.  Implementing ANOVA Using Analysis ToolPak
    7m 50s
    In this video, you will use the Analysis ToolPak to implement ANOVA (Analysis of Variance) to analyze variances within and between groups. FREE ACCESS
  • Locked
    9.  Analysis ToolPak: Analyzing Data Characteristics
    9m 28s
    During this video, you will learn how to use Analysis ToolPak for histogram analysis and descriptive statistic computing, compute ranks and percentiles, and perform both random and periodic sampling. FREE ACCESS
  • Locked
    10.  Generating Random Numbers
    5m 7s
    Find out how to generate random numbers drawn from various distributions using Analysis ToolPak. Apply normal, Bernoulli, Poisson, and other distributions. Specify population parameters, such as mean and variance. Recognize why sample mean and variance might differ from them. FREE ACCESS
  • Locked
    11.  Performing Linear Regression
    9m 42s
    In this video, learn how to perform linear regression using Analysis ToolPak, interpret the results of regression including R-square, p-values, and t-statistics of individual regression coefficients, and identify the benefits of using Analysis ToolPak over worksheet functions such as LINEST(),SLOPE(), and INTERCEPT(). FREE ACCESS
  • Locked
    12.  Course Summary
    1m 48s

EARN A DIGITAL BADGE WHEN YOU COMPLETE THIS COURSE

Skillsoft is providing you the opportunity to earn a digital badge upon successful completion on some of our courses, which can be shared on any social network or business platform.

Digital badges are yours to keep, forever.

YOU MIGHT ALSO LIKE

Course Exploring essential functions in Excel 365 (2023)
Rating 4.9 of 7 users Rating 4.9 of 7 users (7)
Course Performing data analysis and forecasting in Excel 365
Rating 4.5 of 319 users Rating 4.5 of 319 users (319)
Course Complete Guide to Excel 365: Validating, Cleaning, & Performing Lookups on Data
Rating 4.6 of 538 users Rating 4.6 of 538 users (538)

PEOPLE WHO VIEWED THIS ALSO VIEWED THESE

Course Finding the Quality in Your Data
Rating 4.3 of 303 users Rating 4.3 of 303 users (303)
Course Business Reporting: Getting Started with Power BI Desktop for Data Analysis
Rating 4.6 of 1028 users Rating 4.6 of 1028 users (1028)
Course Complete Guide to Excel 365: Using Formatting, Styles, & Themes
Rating 4.7 of 446 users Rating 4.7 of 446 users (446)