Complete Guide to Excel 365: Pivot, PowerPivot, & Financial Modeling
Excel Office 365 | Expert
- 9 videos | 1h 14m 42s
- 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 courseimport 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 relationshipsauto-detect relationships using the PivotTable feature, and illustrate how foreign key relationships between parent and child tables are detected and dynamically reflected in pivot tablesuse PowerPivot to detect and manage relationships, create data models, and inter-operate with traditional Excel pivot tablescontrast 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 flowscompute 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 flowsperform 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 instrumentsummarize the key concepts covered in this course
IN THIS COURSE
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
PEOPLE WHO VIEWED THIS ALSO VIEWED THESE
Course Final Exam: Business Analyst