Complete Guide to Excel 365: Pivot, PowerPivot, & Financial Modeling

Excel Office 365    |    Expert
  • 9 Videos | 1h 18m 12s
  • Includes Assessment
  • Earns a Badge
In this course, you'll recognize how relational data - data with schema and clearly-defined column names - can be imported into Excel as a set of data tables. This data can be sliced-and-diced using classic Excel pivot tables or the more robust PowerPivot add-in. Now a standard part of Excel, this add-in vastly expands the tool's capabilities with some serious entity-relationship modeling and big data analysis. Excel has powerful capabilities to detect relationships across models and infer foreign key relationships between parent and child tables. Creating, visualizing, and modeling such relationships is an important aspect of working with relational databases. As you'll recognize, you can now accomplish much of that from within Excel using PowerPivot. In this course, you'll use some classic yet powerful worksheet functions that have ensured Excel's use in Wall Street for decades. These functions can be used to compute the yield of a bond and the present and future values of a set of cash flows. They can also be used to perform complex operations on settlement dates and compounding rates of financial instruments.

WHAT YOU WILL LEARN

  • discover the key concepts covered in this course
    import data in relational (tabular) form, create pivot tables by attempting to link multiple tables, and specify issues that can arise if pivot tables are created without accurately capturing relationships  
    auto-detect relationships using the PivotTable feature, and illustrate how foreign key relationships between parent and child tables are detected and dynamically reflected in pivot tables  
    use PowerPivot to detect and manage relationships, create data models, and inter-operate with traditional Excel pivot tables  
    contrast the auto-detection of relationships performed in PowerPivot vs. in traditional pivot tables, visualize foreign-key relationships, and use entity-relationship diagrams in PowerPivot  
  • model fixed-income instruments, such as bonds using Excel's financial functions, use the EDATE() function to compute settlement dates, the EFFECT() function to find effective compounding rates, and the FV() function to compute the future value of a stream of cash flows  
    compute the yield of a bond using the YIELD() function, demonstrate the inverse relationship between prices and yields, and use the PV() function to find the present value of a series of cash flows  
    perform NPV (net present value) calculations using both the Excel NPV() function and hand-rolled formulas that take into account the discount rates and coupons of a fixed income instrument  
    summarize the key concepts covered in this course

IN THIS COURSE

  • Playable
    1. 
    Course Overview
    2m 3s
    UP NEXT
  • Playable
    2. 
    Using Relational Data Models
    11m 21s
  • Locked
    3. 
    Detecting Relationships and Foreign Keys
    10m 3s
  • Locked
    4. 
    Using PowerPivot for Data Modeling
    10m 15s
  • Locked
    5. 
    PowerPivot: Detecting and Visualizing Relationships
    11m 18s
  • Locked
    6. 
    Modeling Fixed-Income Instruments
    10m 17s
  • Locked
    7. 
    Computing Bond Yields
    10m 15s
  • Locked
    8. 
    Performing Net Present Value (NPV) Analysis
    7m 21s
  • Locked
    9. 
    Course Summary
    1m 50s

EARN A DIGITAL BADGE WHEN YOU COMPLETE THIS COURSE

Skillsoft is providing you the opportunity to earn a digital badge upon successful completion of this course, which can be shared on any social network or business platform

Digital badges are yours to keep, forever.