This course can be tailored to your needs for private, onsite delivery at your location.
ASPE is an IIBA Endorsed Education Provider of business analysis training. Select Project Delivery courses offer IIBA continuing development units (CDU) in accordance with IIBA standards.
Select courses offer Leadership (PDU-L), Strategic (PDU-S) and Technical PMI professional development units that vary according to certification. Technical PDUs are available in the following types: ACP, PBA, PfMP, PMP/PgMP, RMP, and SP.
The need to advance in the world of decision making, analysis, marketing, accounting and finance has never been greater. Gain new insights into the hidden analysis power of Excel and learn how to harness modeling functions during this two-day, practical modeling seminar. Learn the array of useful tools and functions from a seasoned expert who has taught in MBA programs at the business schools of Columbia University, New York University, Temple and Rutgers, and quickly create models that deliver accurate, relevant information related to efficiency, forecasting, data mining and a host of other business and reporting applications.
You will leave the course proficient in power functions and techniques that others do not even know exist. You will be set to immediately begin implementing what you have learned to the benefit of your job and your career.
- Upcoming Dates and Locations
All Live Online times are listed in Eastern Time Guaranteed To Run
- Course Outline
1. Using Excel Efficiently — short review of Time Saving Techniques
2. Formulas, Functions, Relative and Absolute Addressing
- Efficient ways to calculate efficiently individual formulas and functions or simultaneously thousands of cells
3. 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
4. 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
5. IF Functions
- Simple IF and nested IF functions
- Developing nested functions to cope with multiple conditions
6. 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
- Descriptive Statistics
- Descriptive statistics are used analyze the basic features of the data
- Will show how to use Excel statistical functions
- 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
8. 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 simple tools provided by the software
9. Goal Seek
- Goal Seek is used to get a particular result when you're not too sure of the starting value. It can be applied to many applications. One example is the break even point of an investment.
10. Sensitivity Analysis and Data Tables One way Table
- Very powerful part of Excel; used for a variety 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.
11. Using Scroll Bars for Sensitivity Analysis
- Using scroll bars to change values is an impressive presentation tool
12. The Solver
- 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.
13. Working in 3 Dimensions
- Multi Page Budgets
- When you a number of locations, periods or departments and you have their budges on different sheets — you will be able to sum all these sheets into a Totals sheets going the third dimension.
- Build 3-D formulas
14. Finding information with lookup functions
- Simple and complicated lookups
15. Databases in Excel
- Sorting Data, Auto Filter, Data Forms, Grouping and Outlining Data and Subtotals
16. 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.
- Creating a pivot table is easy to learn.
17. Excel Financial Tools
- NPV and IRR and Unconventional Financial Functions: XNPV and XIRR
- Frequently Used Financial Functions: Amortization tables and Accounting Depreciation Functions
18. Boost the power of Excel with Macros
19. 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
- Who should attend
From CEOs to managers to entry-level assistants, this workshop benefits anybody who uses Excel on a regular basis to make decisions.You should attend this course if you:
- Make any quantitative or graphical presentations
- Are a decision maker at any level
- Spend more than 10 hours a month using Excel
- Do analysis in accounting, finance, marketing, sales, human relations or more