Course Code: 19121

Excel 2021/2019 / 2016 Formulas and Functions - Advanced

Class Dates:
1/24/2024
11/10/2023
12/13/2023
Length:
1 Days
Cost:
$295.00
Class Time:
Technology:
Office
Delivery:
Instructor-Led Training, Virtual Instructor-Led Training

Overview

  • Course Overview
  • This 1-day course covers the advanced excel formulas, functions and types of financial analysis. This Advanced Excel training course builds on the knowledge that the user is already proficient and/or has taken the Excel Formulas Basic course and looking to take their skills to an advanced level. Course topics will include:

    1) finding the perfect, most suitable function
    2) methods of function entry
    3) functions using relative, absolute, and named references
    4) reusing functions (copy, autofill, drag and more!)
    5) Functions by type
    - Multiple and cross sheet functions
    - Date functions
    - Text(String) functions
    - Logical functions
    - Lookup functions
    - Aggregate functions
    - Rounding Functions
  • Audience
  • This course is intended for experienced excel users with a basic understanding of functions and formulas

Prerequisites

Course Disclaimer

*Course cost listed does not include the cost of courseware. Please contact us at learn@vtec.org or 207-775-0244 for additional pricing information, or if you have any questions. Course to run is subject to a minimum enrollment. Course may run as a virtual instructor led course if the minimum enrollment has not been met

Course Details

  • Implementing Basic Business Formulas
  • Pricing formulas - Price markups, Price Discounts, Break-even point
  • Financial formulas - Sales ratios, Cost of goods sold, Gross margin, Net margin, Fixed-asset ratios
  • Inventory Formulas - Inventory ratios, Inventory management formulas, Liquidity formulas
  • Building descriptive statistical formulas
  • Understand descriptive statistics
  • Counting items - COUNT ( ), COUNT A ( ) , COUNTBLANK ( ),COUNTIF ( ) COUNTIFS ( ) Functions
  • Calculating Averages
  • Calculating extreme values
  • Working with rank and percentile
  • Calculating measures of variation
  • Working with frequency distributions
  • Building inferential statistical formulas
  • Understanding inferential statistics
  • Sampling data
  • Determining whether two variables are related
  • Working with probability distributions
  • Determining confidence intervals
  • Hypothesis testing
  • Applying regression to track trends and make forecasts
  • Choosing a regression method
  • Using simple regression on linear data
  • Using simple regression on nonlinear data
  • Working with an exponential trend
  • Working with a power trend
  • Using poly-nominal regression analysis
  • Using multiple regression analysis
  • Building Loan Formulas
  • Understanding the time value of money
  • Calculating a loan payment
  • Loan payment analysis
  • Working with a balloon loan
  • Calculating interest costs, part 1
  • Calculating principle and interest
  • Calculating Interest costs, part II
  • Calculating cumulative principle and interest
  • Building a loan amortization schedule
  • Calculating the term of a loan
  • Calculating the interest rate required for a loan
  • Calculating how much you can borrow
  • Working with investment formulas
  • Working with interest rates
  • Understanding compound interest
  • Nominal versus effective interest
  • Converting between the nominal rate and effective rate
  • Calculating the future value
  • Calculating the future value of a lump sum
  • The future value of a series of deposits
  • The future value of a lump sum plus deposits
  • Working toward an investment goal
  • Building discount formulas
  • Calculating the present value
  • Discounting cash flows
  • Calculating the payback period
  • Calculating the internal rate of return
  • Analyzing data with tables
  • Sorting a table
  • Sorting on part of a field
  • Filtering table data
  • Referencing tables in formulas
  • Excel's table functions
  • Analyzing data with PivotTables
  • Working with PivotTable subtotals
  • Changing the value field summary calculation
  • Creating custom PivotTable calculations
  • Using PivotTable results in a worksheet formula
  • Using Excel's business modeling tools
  • Using what-if analysis
  • Working with Goal Seek
  • Working with scenarios
  • Solving complex problems with Solver
  • Some background on Solver
  • Loading Solver
  • Using Solver
  • Adding constraints
  • Saving a solution as scenario
  • Setting other Solver options
  • Making sense of Solver's messages
  • Displaying Solver's reports