Excel™ Fundamentals, Advanced Excel™ for Data Analysis & Charting Techniques

New York Institute of Finance, in conjunction with Wall St. Training, present the following course: This three-part course first focuses on how learning the fundamental building blocks of Excel so you can begin to take advantage and leverage all of Excel’s true capabilities. In order to efficiently build models and crunch large data dumps in Excel, one must master the basics before the advanced content. Learn relevant financial formulas, proper navigation, formatting of files and worksheets, creating calculations in cells, and linking between worksheets/tabs. Functions and tools covered in this course include: mathematical, financial, logic, date/time formulas; data manipulation; anchoring; data tables; and building a capstone model. Emphasis will be on using shortcut keys, simplifying steps, and manipulating data. You will leave with techniques you can use immediately, allowing you to work faster and with less effort.

Then we focus on how to effectively and efficiently utilize Microsoft Excel for data analysis. A financial analyst will not only use Excel to build financial models, but also to crunch a large data dump. Learn how to minimize as much manual labor as possible, thereby saving time and performing more detailed analysis quickly. Apply commonly-used formulas in new and different ways; uncover often over-looked Excel formulas; streamline number crunching and analysis via functions and tools including pivot tables, sumif, sum+if, transpose, working with arrays, vlook-up, subtotals, and regression analysis; enhance your spreadsheets with drop-down boxes, data validation techniques, automation of alternate row shading; take Excel to the next level with an introduction to building and automating simple macros and more!

''A Picture is Worth a Thousand Words'' – but what happens when you have the perfect image in your head but you can’t get Excel to graph it the way you want? Ever get annoyed at constantly having to go back into ''Source Data'' whenever you add an item to your data series? Or how about getting the perfect sized bar or line without resorting to using a ruler to literally draw it on! This course builds upon our Advanced Excel for Data Analysis course and focuses advanced charting & graphing techniques and how to properly integrate with PowerPoint. A critical, must-take course especially for professionals that have to create graphs in their presentations, reports and slides! As usual, we emphasize and teach all the best practices and focuses on our core Excel learning goal: automation, automation, automation! Leave nothing to chance, there is always a way to simplify and automate your charting & graphing approach. This jam-packed session includes: waterfall charts, football fields, dymamic ranges, and much much more! Learn the best practices of integrating into PowerPoint, when to embed, link (never) and copy as picture, as well as add to our Excel macros with a couple handy PowerPoint macros.

No sessions currently available. Contact client services to get the next available date.
CPE CREDITS
21.5
Investment bankers, mergers & acquisitions, leveraged finance and credit professionals. Private equity, buyout and venture capital professionals. Internal M&A and business development. CFO, VP Finance, Financial Analysts, & related functions. New hires and those being groomed for management.
No advance preparation required.
Proficiency in: Accounting & Financial Statements, basic Corporate Finance and valuation topics. Basic, general knowledge or interest in leveraged buyouts. Solid proficiency in Excel. Note: To maximize the value & productivity of this course, participants must be proficient and comfortable with Excel - a lack of Excel skills will hamper the ability to properly follow along and acquire the best practices and efficiencies that are presented. Financial calculator required.
Note regarding Computers:
EACH PARTICIPANT MUST BRING THEIR OWN LAPTOP TO CLASS.
Day 1 - Excel Fundamentals for the Finance Professional
Learning Objectives:
  • Learn basic features of Excel and how to properly navigate and format Excel files and worksheets
  • Learn basic functions and creating calculations in cells and linking between tabs (worksheets)
  • Introduction to basic data manipulation and realizing the power and capabilities of Excel
  • Learn relevant financial formulas and functions and how to begin maximizing Excel’s abilities

Learning Goals - Fundamental Excel Functions:

  • Mathematical functions: SUM, MAX, AVERAGE, MEDIAN, MIN
  • Financial functions: PV, FV, RATE, NPV, IRR
  • Logic Functions: IF, nested IF, CHOOSE, AND, OR
  • Date Functions: MONTH, DAY, YEAR, WEEKDAY, EO MONTH
  • Time Functions: HOUR ,MINUTE, SECOND, TODAY, NOW
  • Formatting: fills, copy formulas, paste special

Learning Goals - Intermediate Excel Techniques:

  • Data Manipulation: TEXT, CONCATENATE, ROUND
  • Anchoring and locking cell references
  • Data Tables: perform one-dimension and two-dimensional sensitivity analyses
  • Build simple capstone financial model that encompasses efficiencies, shortcuts and sensitivity analysis
  • Shortcuts and working with Add-ins

Day 2 - Advanced Excel for Data Analysis
Learning Objectives:
  • Learn how to minimize as much manual labor as possible in data analysis
  • Learn to use the most overlooked Excel formulas that will make your life easier
  • Learn powerful functions built in Excel that streamline your analysis
  • Learn how to build macros to automate common tasks

Learning Goals:

  • Learn the most useful and overlooked Excel shortcuts to make life easier!
  • What are the different ways to make your Excel worksheet into a model instead of just a flat analysis? Learn different “switches alternatives” (if, choose, offset)
  • Learn data validation techniques to dummy proof your model!
  • What is the XIRR function and how is it different from the basic IRR function?
  • Perform basic regression analysis using least squares approach
  • How do you perform one-dimension and two-dimensional sensitivity analyses using data tables?
  • Utilize the vlookup function to its fullest to streamline tedious lookup jobs
  • Pivot Tables: Everybody’s heard of it but who knows how to use it! Learn how to summarize and dissect large amounts of data for analysis!
  • Pivot Tables: Even better – add built-in and custom calculated fields to really use pivot tables to the max!
  • Utilize the sumif formula and sum+if array functions to simplify complex conditional calculations
  • Learn how to use the subtotal formula and function to minimize errors
  • Combine subtotal with AutoFilter options to easily crunch all sorts of data!
  • Automate alternate row shading in a table of data
  • Add some spice to your Excel analysis and models using drop-boxes

Day 3 - Excel Charting & Graphing Techniques & PowerPoint Integration
Learning Objectives:
  • Translate Excel analysis into meaningful charts and graphs to visually present your work
  • Master the skills necessary to create robust dynamic charts easily and effortlessly
  • Learn different techniques and best practices of integrating charts into PowerPoint
  • Advance beyond simple charting functions to create multi-layered graphs that combine and display multiple data sets and ideas simultaneously

Learning Goals:

  • Creating Price Volume chart with call-out box annotations with perfect alignment
  • Calculate and create dynamic moving average charts
  • Construct Indexed Stock Price History graph with automated information box
  • Build historical industry graph summarizing average, high low bars detailing valuation spreads
  • Construct combination charts and graphs including precise annotations and secondary axis formatting
  • Properly structure beta and volatility analysis and regression on multiple axis
  • Construct historical and projected linear regression graph with automated best fit lines
  • Assemble and understand logic behind “step charts” with X and Y Error bars to connect the dots
  • Create dynamic charts and graphics that automatically update as additional source data is added
  • Build Shares Traded at Various Prices graph with absolute perfectly sized and aligned graphs
  • Create simple column and cumulative column (or bar) chart (multiple stacked chart)
  • Learn how to create complex, combination charts such as double stacked charts
  • Go all out by building a “football field” valuation range chart that combines triple stacked charts with XY scatter plot to automate current stock price line
  • Construct waterfall chart that graphically summarizes sum-of-parts valuation
  • Learn best practices of bringing Excel charts and exhibits into PowerPoint
  • Avoid the forbidden linking between files and learn when to embed vs copy/paste as picture
  • Learn the fastest and best ways to work in PowerPoint without the mouse
  • Facilitate chart and graph placement in PowerPoint with our custom PPT macros

Clients who register for this course will receive a complimentary 4-month subscription to FT.com. The Financial Times is the world's most respected financial newspaper, providing a broad assessment on finance, business and the industrial sector. The move to the electronic version follows an ongoing review of our environmental responsibilities as a global business and as part of the Pearson group. FT.com also has features that are not available in hard copy, such as: Special Reports, Alphaville, editor blogs, education sections and much more! Subscriptions will start within 6-8 weeks of the start of class and are limited to one subscription per client. (Please note: as of May 1, 2011, the electronic subscription replaces the hard-copy 3-month Financial Times subscription.)

Lunch is included for all students taking day classes.