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

Excel    |    Expert
• 9 videos | 1h 14m 42s
• Includes Assessment
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

• 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.
• 3.  Detecting Relationships and Foreign Keys
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.
• 4.  Using PowerPivot for Data Modeling
In this video, you will use PowerPivot to detect and manage relationships, create data models, and inter-operate with traditional Excel pivot tables.
• 5.  PowerPivot: Detecting and Visualizing Relationships
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.
• 6.  Modeling Fixed-Income Instruments
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.
• 7.  Computing Bond Yields
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.
• 8.  Performing Net Present Value (NPV) Analysis
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.
• 9.  Course Summary

## 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

Rating 3.9 of 21 users (21)
Rating 5.0 of 1 users (1)

## PEOPLE WHO VIEWED THIS ALSO VIEWED THESE

Rating 4.4 of 417 users (417)
Rating 4.5 of 2242 users (2242)
Rating 4.5 of 380 users (380)