Analyzing and forecasting data in Excel 365 (2024)

Excel 365 (2024)    |    Intermediate
  • 13 videos | 59m 2s
  • Includes Assessment
  • Earns a Badge
  • Certification CPE
Rating 5.0 of 1 users Rating 5.0 of 1 users (1)
Excel 365 is a powerful tool for data management tasks, such as consolidating, analyzing, and forecasting. This course offers a deep dive into these advanced features of Excel 365 to optimize how to handle data from different sources and get useful insights. First, discover the different What-If Analysis tools to evaluate different scenarios. Learn how to use the Scenario Manager tool to compare different data outcomes and delve into What-If Analysis with data tables to perform sensitivity analysis. The course further explores Goal Seek for achieving specific data outcomes and employs the Solver add-in for solving complex optimization problems. Next, discover different tools to forecast data in Excel. For example, use the Forecast Sheet tool for visual predictions. Financial analysis is not overlooked, as the course covers estimating loan-related costs using Excel functions such as PMT, IPMT, and PPMT. Excel also provides two intelligent tools to get insights on how to analyze your data. Discover the Analyze Data feature for AI-powered analysis, and get to grips with the Copilot tool for advanced data insights through natural language processing. Lastly, discover how to consolidate data from various sources for streamlining the process of analysis and reporting. Explore Power Query, an essential part of Excel for data importation, transformation, and efficient data source management, including maintaining connections and automating data refreshes. This course aligns with the objectives of Exam MO-211: Microsoft Excel Expert (Microsoft 365 Apps). Would you like hands-on practice? Go to the Course Contents pane and download the sample files to follow along with select videos.

WHAT YOU WILL LEARN

  • Discover the key concepts covered in this analyzing and forecasting data in excel 365 (2024) course
    Use the scenario manager tool to observe different outcomes
    Use data tables for what-if analysis in excel 365
    Use goal seek to modify a data variable and reach a target value
    Use the solver add-in to analyze constraints and variables and optimize data values
    Use the forecast sheet tool to display trends and predictions based on available historic data
    Forecast data with formulas in excel 365
  • Use the pmt, ipmt, and ppmt functions to estimate loan-related costs in excel 365
    Use the analyze data tool in excel 365
    Get data insights and add calculated columns with copilot for excel
    Use the consolidate feature in excel 365
    Use the power query tool in excel 365
    Manage data sources using the power query tool in excel 365

IN THIS COURSE

  • 1m
    This video outlines the key content covered in this Analyzing and forecasting data in Excel 365 (2024) course, including using the Scenario Manager and Data tables to evaluate different scenarios, forecasting data with formulas and the Forecast Sheet tool, and importing data with Power Query. FREE ACCESS
  • 3m 50s
    The Scenario Manager in Excel is a powerful tool for running what-if analyses. It enables users to define and store various sets of input values, called scenarios, that can be easily switched to observe different outcomes. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Perform what-if analysis by using Goal Seek and Scenario Manager. FREE ACCESS
  • Locked
    3.  Using data tables for What-if Analysis in Excel 365
    5m 3s
    Data tables are a great way of testing different what-if scenarios when analyzing data. Discover how to use data tables to create calculations using one or two variables and once the data table has been created, adjust the values and change the calculation options. FREE ACCESS
  • Locked
    4.  Finding a result with Goal Seek in Excel 365
    4m 7s
    The Goal Seek tool can be used to determine which variables need to change in order to reach a target value in a table obtained using formulas, without the need for manual adjustments to a formula. In this video, explore how to use the Goal Seek tool to modify data variables to reach a target value in Excel 365. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Perform what-if analysis by using Goal Seek and Scenario Manager. FREE ACCESS
  • Locked
    5.  Solving a problem in Excel 365
    5m 13s
    If a mathematical problem exists within your table and is subject to a range of constraints, the Solver add-in in Excel can be used to attempt an automatic solution. The desired value to be optimized, minimized, or adjusted in another manner should be selected, and the constraints influencing the outcome must be entered. FREE ACCESS
  • Locked
    6.  Using the Forecast Sheet tool in Excel 365
    4m 32s
    In Excel 365, use one-click forecasting to make predictions based on historical data. Learn how to adjust the timeline and seasonality settings, and make changes to the forecast data once the sheet has been inserted. In this video, explore how to use the Forecast Sheet tool to predict trends and generate a forecast based on time-based data already in a table or range. FREE ACCESS
  • Locked
    7.  Forecasting data with formulas in Excel 365
    5m 36s
    To calculate how many months it would take to reimburse a loan according to a fixed monthly payment and an interest rate, use the NPER function in Excel 365. In this video, learn how to combine the NPER function with the IF and AND functions and forecast data that meets one or more criteria. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Forecast data by using the AND(), IF(), and NPER() functions. FREE ACCESS
  • Locked
    8.  Estimating loan-related costs in Excel 365
    5m 34s
    If a loan has been taken out, Excel 365 can provide assistance in estimating and managing repayments. It is possible to perform calculations for total repayments per period, as well as interest and principal repayments. In this video, learn how to use the PMT, IPMT, and PPMT functions to estimate loan-related costs. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Calculate financial data by using the PMT() function. FREE ACCESS
  • Locked
    9.  Performing analysis with Analyze Data in Excel 365
    3m 14s
    The Analyze Data tool in Excel 365 can perform an analysis of a dataset to look for patterns in the data to make clever, personalized recommendations for you. In this video, you will explore how to use the Analyze Data tool in Excel 365. FREE ACCESS
  • Locked
    10.  Analyzing data with Copilot in Excel 365
    5m 21s
    Copilot for Microsoft Excel 365 acts as your AI-powered data analyst. In this video, you will learn how to use Copilot to get insights about your data in the form of charts and other objects. See how to highlight significant values and sort your data. Finally, discover how to add calculated columns to your table using Copilot. FREE ACCESS
  • Locked
    11.  Consolidating data in Excel 365
    5m 12s
    Data from multiple worksheets or workbooks can be extracted and combined into a single table using Excel 365's powerful Consolidate function. In this video, learn how to configure data consolidation options and how to add ranges from different worksheets. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Summarize data from multiple ranges by using the Consolidate feature. FREE ACCESS
  • Locked
    12.  Querying data sources with Power Query in Excel 365
    5m 3s
    With Power Query in Excel 365, import or connect to an external data source, such as a database, without affecting the original dataset. Next, shape the data in ways that meet your needs to load it into a query in Excel to create charts and reports. In this video, explore how to use Power Query in Excel 365. FREE ACCESS
  • Locked
    13.  Managing data sources with Power Query in Excel 365
    5m 16s
    In Excel 365, Power Query is a powerful tool to import data and then transform and reshape it as needed. Learn how to change a column’s data type, delete and add columns; and even combine data from two different sources, in order to get a fresh analytical view. In this video, explore how to manage data sources with Power Query in Excel. FREE ACCESS

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

Rating 4.3 of 32 users Rating 4.3 of 32 users (32)
Rating 3.9 of 21 users Rating 3.9 of 21 users (21)
Rating 4.5 of 449 users Rating 4.5 of 449 users (449)