SkillSoft Explore Course – Lambers, Inc.

BUY NOW! STARTING AT ONLY $299!

IT Professional Curricula Enterprise Database Systems Solution Area Data Visualization A Complete and Comprehensive Guide to Excel

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.

Asset ID	it_dacgexdj_07_enus
Course Type	Video Course
Course Category	Enterprise Database Systems Solution Area

Objectives
Complete Guide to Excel 365: Pivot, PowerPivot, & Financial Modeling 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

Objectives

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

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