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

Excel    |    Expert
  • 9 videos | 1h 14m 42s
  • Includes Assessment
  • Earns a Badge
Rating 4.5 of 353 users Rating 4.5 of 353 users (353)
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

  • 2m 3s
  • 11m 21s
    Learn how to import data in relational (tabular) form, create pivot tables by linking multiple tables, and specify issues that can arise if pivot tables are created without accurately capturing relationships. FREE ACCESS
  • Locked
    3.  Detecting Relationships and Foreign Keys
    10m 3s
    In this video, you will use the PivotTable feature to auto-detect relationships, and illustrate how foreign key relationships between parent and child tables are detected and dynamically reflected in pivot tables. FREE ACCESS
  • Locked
    4.  Using PowerPivot for Data Modeling
    10m 15s
    In this video, you will use PowerPivot to detect and manage relationships, create data models, and inter-operate with traditional Excel pivot tables. FREE ACCESS
  • Locked
    5.  PowerPivot: Detecting and Visualizing Relationships
    11m 18s
    Find out how to 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. FREE ACCESS
  • Locked
    6.  Modeling Fixed-Income Instruments
    10m 17s
    In this video, you will model fixed-income instruments, such as bonds using Excel's financial functions. You will 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. FREE ACCESS
  • Locked
    7.  Computing Bond Yields
    10m 15s
    During this video, you will learn how to compute the yield of a bond using the YIELD() function, apply the inverse relationship between prices and yields, and use the PV() function to find the present value of a series of cash flows. FREE ACCESS
  • Locked
    8.  Performing Net Present Value (NPV) Analysis
    7m 21s
    Find out how to perform net present value (NPV) 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. FREE ACCESS
  • 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 on some of our courses, which can be shared on any social network or business platform.

Digital badges are yours to keep, forever.

YOU MIGHT ALSO LIKE

Course Adding & arranging data in Excel 365
Rating 4.5 of 2813 users Rating 4.5 of 2813 users (2813)
Course Referencing data in Excel 365
Rating 4.6 of 416 users Rating 4.6 of 416 users (416)
Course Complete Guide to Excel 365: Working With Charts & Sparklines
Rating 4.7 of 576 users Rating 4.7 of 576 users (576)

PEOPLE WHO VIEWED THIS ALSO VIEWED THESE

Course Creating PivotTables and PivotCharts in Excel 365
Rating 4.4 of 438 users Rating 4.4 of 438 users (438)
Course Manipulating PivotTable data in Excel 365
Rating 4.4 of 330 users Rating 4.4 of 330 users (330)
Course Complete Guide to Excel 365: Validating, Cleaning, & Performing Lookups on Data
Rating 4.6 of 538 users Rating 4.6 of 538 users (538)