What-if Analysis in Microsoft Excel (2010/2016) 2017-09-15T13:59:57+00:00

Project Description

Loading...
Welkin Course Codes: ANA0, ANA6

Course Features

What-If Analysis is the process of modifying cell values to see how the changes will influence the outcomes of formulas on the worksheet. Using the What-If Analysis tools in Microsoft Excel will allow you to use several different sets of values in one or more formulas to explore all various results. By taking Excel’s data analysis functionality to the next level, What-IF Analysis will help you make better business decisions. This course will go through the basic principles before working on more advanced and practical features including:

  • Working with Goal Seek
  • Creating Scenarios
  • Solving complex problems with Solver
  • Calculating multiple results by using data table
  • Applying data validation to cells
  • Use a Formula to apply Conditional Formatting/fusion_li_item]

On course completion you will have a good understanding of What-If Analysis and be able to use the tools in data analysis tasks.

  • Qualified and experienced instructors
  • Easy-to-follow, step-by-step hands-on approach
  • Best practices explained and demonstrated
  • Weekday and weekend classes available

Learn from the Experts

Get Professional Recognition

mos-certificate

Certify your Excel skills with Microsoft Office Specialist Excel certification. The certification is not a requirement for course completion but we highly recommend you to get your skills professionally recognized.

Remark: The course fee does not include the certification exam.

Further Study

After completing this course you may further develop your knowledge and skills with the CEF reimbursable course Business Analysis with Microsoft Excel.

Course Fee

$960

Course Duration

6 Hours

Course Prerequisites

Course participants should have a basic knowledge of Microsoft Excel.

Title of Award

Upon successful completion of the course, students will receive a Certificate of Achievement.

Schedule & Online Enrolment
Enquiry/Booking
What-if Analysis in Microsoft Excel 2016
COURSE INFODATES(dd/mm)TIMEDURATIONDAY(S)AMOUNT
ANA6-12C11C
 Central
 Cantonese / PC
11/12~12/12
1900-22006 hrs/
2 sessions
MON, TUE960.00
ANA6-45C11K
 Mongkok
 Cantonese / PC
11/1~12/1
1900-22006 hrs/
2 sessions
THU, FRI960.00
ANA6-51C9C
 Central
 Cantonese / PC
9/2~12/2
1900-22006 hrs/
2 sessions
MON, FRI960.00
ANA6-6A17K
 Mongkok
 Cantonese / PC
17/3~24/3
1000-13006 hrs/
2 sessions
SAT960.00
ANA6-6D24C
 Central
 Cantonese / PC
24/3~24/3
1000-1300
1400-1700
6 hrs/
2 sessions
SAT960.00
ANA6-51C28K
 Mongkok
 Cantonese / PC
27/4~30/4
1900-22006 hrs/
2 sessions
MON, FRI960.00
ANA6-24C8C
 Central
 Cantonese / PC
8/5~10/5
1900-22006 hrs/
2 sessions
TUE, THU960.00
ANA6-24C12K
 Mongkok
 Cantonese / PC
12/6~14/6
1900-22006 hrs/
2 sessions
TUE, THU960.00
ANA6-6A23C
 Central
 Cantonese / PC
23/6~30/6
1000-13006 hrs/
2 sessions
SAT960.00
ANA6-6A21K
 Mongkok
 Cantonese / PC
21/7~28/7
1000-13006 hrs/
2 sessions
SAT960.00
ANA6-35C8C
 Central
 Cantonese / PC
8/8~10/8
1900-22006 hrs/
2 sessions
WED, FRI960.00
ANA6-51C14K
 Mongkok
 Cantonese / PC
14/9~17/9
1900-22006 hrs/
2 sessions
MON, FRI960.00
ANA6-6A29C
 Central
 Cantonese / PC
29/9~6/10
1000-13006 hrs/
2 sessions
SAT960.00
What-if Analysis in Microsoft Excel 2016
Complete your Enrol
How to enrol/pay online

What-if Analysis in Microsoft Excel 2010
COURSE INFODATES(dd/mm)TIMEDURATIONDAY(S)AMOUNT
ANA0-45C14C
 Central
 Cantonese / PC
14/12~15/12
1900-22006 hrs/
2 sessions
THU, FRI960.00
What-if Analysis in Microsoft Excel 2010
Complete your Enrol
How to enrol/pay online

The what-if tools in Microsoft Excel allow you to experiment with your data to project future results. What-if analysis is used every day by those involved in data analysis and decision making. In this course you will learn how a number of tools such as Goal Seek, Data Tables, Scenarios, and Solver helps you explore various outcomes or solve complex problems without having to enter multiple sets of formulas.

  1. What-if Analysis Tools
    • Overview
    • Use scenarios to consider many different variables
    • Use Goal Seek to find out how to get a desired result
    • Use data tables to see the effects of one or two variables on a formula
    • Prepare forecasts and advanced business models
  2. Use scenarios to consider many different variables
    • Setting Up Your Worksheet for Scenarios
    • Create the first Excel Scenarios
    • Create more than one Excel Scenario
    • Show an Excel Scenario
  3. Use Goal Seek to find out how to get a desired result
    • Prepare the worksheet
    • Use Goal Seek to determine the interest rate
  4. Use data tables to see the effects of one or two variables on a formula
    • Create a one-variable data table
    • Add a formula to a one-variable data table
    • Create a two-variable data table
    • Speed up calculation on a worksheet that contains data tables
  5. Solver
    • Load the Solver Add-in
    • Define and solve a problem
    • Step through Solver trial solutions
    • Saving a Solution as a Scenario
    • Setting Other Solver Options
    • Making Sense of Solver’s Messages
    • Loading Solver
  6. Consolidate
    • How to consolidate data
  1. Data Validation
    • Validation Setting
    • Error Handling
  2. Remove Duplicates
    • Remove duplication of record from a range
  3. Text to Columns
    • Convert Text in cell into columns
  4. Conditional Formatting
    • Type of Conditional Formatting Rules
    • Using Values to create rule
    • Using Formula to create rule
    • Deleting Conditional Formatting
  5. New Features in Sort and Auto Filter
    • Applying a pre-installed custom sort
    • Creating a customized list and performing a custom sort
    • Using AutoFilter
    • Removing all AutoFilters from a worksheet
  6. Import Text File into Excel
    • What is a delimited text file?
    • Importing a delimited text file
  7. Protect Range in Worksheet
    • Password protecting cells and worksheets.
    • Hiding formulas
    • Un-hiding formulas
  8. Using Database Functions
    • Introducing Database Functions
    • Find the Sum of Specific Records using Paste Function
    • Find The Average Of Specific Records
  9. Format As Table
    • Create a table
    • Insert a table using a style of your choice
    • Delete a table without losing the data or table formatting
    • Delete a table and its data
  1. Working with Goal Seek
    • How Does Goal Seek Work?
    • Running Goal Seek
    • Optimizing Product Margin
    • A Note About Goal Seek’s Approximations
    • Performing a Break-Even Analysis
    • Solving Algebraic Equations
  2. Working with Scenarios
    • Understanding Scenarios
    • Setting Up Your Worksheet for Scenarios
    • Adding a Scenario
    • Displaying a Scenario
    • Editing a Scenario
    • Merging Scenarios
    • Generating a Summary Report
    • Deleting a Scenario
  3. Solving Complex Problems with Solver
    • Some Background on Solver
    • Loading Solver
    • Using Solver
    • Adding Constraints
    • Saving a Solution as a Scenario
  4. Setting Other Solver Options
    • Selecting the Method Solver Uses
    • Controlling How Solver Works
    • Working with Solver Models
    • Making Sense of Solver’s Messages
    • Displaying Solver’s Reports
  5. Calculate Multiple Results by using a Data Table
    • Data Table Basics
    • Create a one-variable Data Table
    • Add a Formula to a one-variable Data Table
    • Create a two-variable Data Table
    • Speed up Calculation on a Worksheet that contains Data Tables
  6. Apply Data Validation to Cells
    • Overview of Data Validation
    • How to handle a Data Validation Alert
    • Add Data Validation to a Cell or Range
    • Adding other types of Data Validation
  7. Consolidate Data from Multiple Worksheets in a Single Worksheet
    • Consolidate Data by Position
    • Consolidate Data by Category
    • Use a Formula to Consolidate Data
  8. Create an Excel Table in a Worksheet
    • Create a Table in the Style you want
    • Filter Data in an Excel Table
    • Use Structured References in Excel Table Formulas
    • Convert a Table to a Range
    • Delete an Excel Table in a Worksheet
  9. Split Text into different Cells
    • Applying Text to Columns
  10. Use a Formula to apply Conditional Formatting
    • Create conditional formatting rules with formula
    • Remove Conditional Formatting
    • Find and Remove the same Conditional Formats throughout a Worksheet
  11. Sorting and Filtering Data by Color
    • Overview of Sorting and Filtering Data by Color and Icon Set
    • Using Color effectively when Analyzing Data
    • Choosing the best Colors for your needs
    • Walking through some Examples
  1. Proceed with on-line registration or send the completed enrolment form (download) to us together with a cheque (made payable to Welkin Compuer Training) for the program fee.
  2. CEF applicants have to complete the CEF Application Form (download) and send it to us for certification. They will then send the form to the Office of the Continuing Education Fund, Room 916, 9/F, Kwai Hing Government Offices, 166-174 Hing Fong Road, Kwai Chung, New Territories, together with a photocopy of their HKID Card before course commencement.
  3. Applicants will receive application results from the Office of the CEF within 14 working days.

You may also be interested