Microsoft Applied Excel For Statistical Analysis & Business Decision
Time & Location
About the Course
In this course, you will learn a variety of useful Excel tools and functions to create effective models. Gain new insights into the hidden analysis power of Excel and learn how to harness modeling functions during this practical modeling course. You will learn to utilize useful tools and functions, and create models that deliver accurate, relevant information related to efficiency, forecasting, data mining and a host of other business and reporting apps.
USING EXCEL EFFICIENTLY
• Summary of Time Saving Techniques
FORMULAS, FUNCTIONS, RELATIVE AND ABSOLUTE ADDRESSING
• Efficient ways to calculate efficiently individual formulas and functions or simultaneously thousands of cells
NAMING CELLS AND RANGES
• This is the most common unknown secret of Excel
• You can use those names in formulas, to replace values or cell references
• Naming cells and ranges is a game changer when you use spreadsheets
CHARTS IN EXCEL
• Instant chart, column line and other charts
• Advance Charting Techniques
• Formatting charts, adding data to charts, 3-D design and adding pictures to charts; Animated Charts
IF AND VLOOKUP FUNCTIONS
• Simple IF and nested IF functions
• Developing nested functions to cope with multiple conditions
TEXT IN EXCEL
• Text to Columns
• Convert Text to Columns as an easy way to separate simple cell content, such as first and last names, into different columns.
• Text Manipulations
◦ Text functions such as LEFT, RIGHT, FIND and advanced features
◦ Flash fill
• Replace many of your concatenate formulas, the text functions of left, right, and find with Flash fill
• Descriptive Statistics, which are used to analyze the basic features of the data
• Using all functions from average through standard deviation and frequency distributions and charts
• Data Analysis
◦ Excel add-in to run all the Descriptive statistics in a single click
◦ Regression is used for forecasting and Datamining.
◦ Will practice the application of regression functions and charts
WHAT-IF ANALYSIS — BASIC ANALYTIC DECISION-MAKING TECHNIQUES
• This part is important for Decision Making Techniques using Excel. We harness the power of Excel to solve complicated analytic problems with the effective tools.
• Goal Seek is used to get a particular result when you are not certain of the starting value. It can be applied to many applications. One example is the break-even point of an investment.
SENSITIVITY ANALYSIS AND DATA TABLES ONE WAY TABLE
• Powerful part of Excel; used for a variety of applications of what-if analysis. It will show you how by changing certain values in your formulas you can affect the result of your formula.
USING SCROLL BARS FOR SENSITIVITY ANALYSIS
• Using scroll bars to change values is an impressive presentation tool
• The Solver as Multi Variable "Goal Seek" Substitute When the Goal Seek is not sufficient and you want to use more than one changing cell
• The Solver — Optimizer
• The Solver Add-in is an optimizing too that finds the best way to allocate limited resources, such as materials, machine time, people, money, or anything else in scarce supply. The optimal solution could be maximizing profits, minimizing costs or other objectives. Typical problems can be investments, manufacturing and scheduling or distribution systems.
WORKING IN 3 DIMENSIONS
• Multi Page Budgets
• When you have several locations, periods or departments and you have their budgets on different sheets — you will be able to sum all these sheets into a Totals sheets, going the third dimension.
• Build 3-D formulas
FINDING INFORMATION WITH LOOKUP FUNCTIONS
• Simple and complex lookups
DATABASES IN EXCEL
• Sorting Data, Auto Filter, Data Forms, Grouping and Outlining Data and Subtotals
PIVOT TABLES — SUMMARIZING BUSINESS AND INFORMATION
• Instead of analyzing countless records, a pivot table can combine your information and show a summary in a few clicks. You can also move columns to rows or vice versa.
• Excel Financial Tools
• NPV and IRR and Unconventional Financial Functions: XNPV and XIRR
• Frequently Used Financial Functions: Amortization tables and Accounting Depreciation Functions
BOOST THE POWER OF EXCEL WITH MACROS
• Automating repetitive tasks
• Record macros to repeat the same operations
• Learn how to create your own macros and functions
• Use the Visual Basic Editor
• Improve your macros
- 4 or more Course Participants$598+$77.74 HST$598+$77.74 HST0$0
- 2 - 3 Course Participants$798+$103.74 HST$798+$103.74 HST0$0
- Course Calendar Date Pricing$598+$77.74 HST$598+$77.74 HST0$0
- 1 on 1 Training$998+$129.74 HST$998+$129.74 HST0$0