Microsoft Excel specialists


Excel training webinar

See dates for all webinars
Cut price 50-70% with passport
Contact us






  WHAT YOU GET

Course manual
& Excel files

10 minutes’
hotline time

Excel expertise
28 years’
specialisation

PivotTables, Power Pivot, Power Query and Power BI

PowerPivot for Finance


     Duration: 60 minutes  
      Format: Interactive webinar  

"Business intelligence (BI) transform[s] raw data into meaningful and useful information. It allows business users to make informed business decisions with real-time data that can put a company ahead of its competitors."

Static, stagnant reports are becoming a thing of the past: Microsoft's new Power Pivot add-in delivers the tools to generate interactive reports that allow incredible insight for quick, confident decision-making. Delve into your data like never before with the brand-new DAX formula language and perform some seriously powerful number-crunching. If you work on the cutting edge and want to implement the next generation of powerful analytical tools, this webinar series was designed for you.

The Power Pivot and Power Query series is in 7 parts. Each part is a one-hour webinar. Attend any or all. Book all 7 for 10% off. See Power Query agenda

  YOUR POWER-PACKED AGENDA
PowerPivot for Finance – Part 1:
Get started with Power Pivot and calculated columns
Meet the Power Pivot add-in, import external data and discover how you can sidestep gnarly VLOOKUPs to consolidate multiple data sources into a single PivotTable report. Begin learning the new DAX formula language and create calculated columns to serve as new PivotTable label fields and Slicers.

  • Set up and install Microsoft's PowerPivot add-in
  • What is PowerPivot
  • Import mass data into PowerPivot
    Quiz
  • Create a PowerPivot PivotTable from multiple-source data tables
  • Use related columns to side-step VLOOKUP formulas
    Quiz
  • Create calculated columns with DAX functions
  • Hide columns to de-clutter the PivotTable Field list
    Quiz
PowerPivot for Finance – Part 2:
Build Power Pivot measures and use new DAX functions
Leave behind clunky, unwieldy PivotTable calculations and messy workarounds: start building Power Pivot measures and using the myriad new and wonderful DAX functions. Discover how time intelligence functions make quick work of frustrating fiscal year calculations, and open the door to a whole new world of analytical possibilities.

  • Add Measures (PowerPivot's supercharged calculated fields) to a PivotTable
  • Learn how Measures are calculated
    Quiz
  • Use one Measure across multiple PivotTables
  • Discover new functions such as CALCULATE which turns any formula into a conditional calculation
    Quiz
  • Use time intelligence functions to calculate YTD for financial year
  • Compare values from various time periods with DATEADD
    Quiz
PowerPivot for Finance – Part 3:
Build an interactive variance report
Create a report to compare monthly and YTD sales figures between this year and last year. Along the way, discover time-intelligence traps (and the trick to outwitting them), add interactivity so users can decide whether to view values in thousands or millions, and whether they want to report on sales, costs or profits. Finally, add coloured KPIs to clearly indicate whether performance was good, bad or just plain ugly.

  • Define base measures for your report
  • Learn best practices for Power Pivot measures
  • Add a Timeline for user-friendly date selection in XL13
  • Protect time-intelligence functions from discontinuous dates
    Quiz
  • Use an unlinked Slicer to toggle a report to thousands or millions
  • Use an unlinked Slicer to change the reporting measure from sales to profits or costs
    Quiz
  • Highlight Key Performance Indicators with KPI icons
  • Build intermediate measures to create more flexible KPIs
PowerPivot for Finance – Part 4:
Combine PivotTable power with formula flexibility: CUBE functions
Powerful and useful though they may be, PivotTables have always suffered from extreme formatting inflexibility. If you want to insert rows, add intermediate calculations and format to suit your heart's desire – or if you're just finding Power Pivot measures intimidating and want to calculate in a familiar environment – meet Excel's CUBE functions, which give you the best of both worlds. Retain the interactivity of Slicers but relax those formatting restrictions to create the report of your dreams. *This webinar isn't limited to Power Pivot users – anyone with an OLAP data source can harness the power of CUBE functions.

  • Introduction to CUBEs, dimensions, members and values
  • Convert a Power Pivot PivotTable to CUBE formulas
  • Recreate KPIs with conditional formatting in XL10
    Quiz
  • Extract a Slicer selection with CUBERANKEDMEMBER
  • Provide Slicer selection feedback to users
    Quiz
  • Build a member expression to generate a CUBESET
  • Evaluate a measure for multiple members with CUBEVALUE
PowerPivot for Finance – Part 5:
Build a powerful, dynamic cash flow report
Learn advanced Power Pivot concepts, such as defining a separate table of headings and explicitly allocating records to those headings within your PivotTable. Determine the local filter context of a cell to perform different calculations in certain portions of the PivotTable, and turn off +/- expansion for items where it's simply not appropriate.

  • Import and arrange the data
  • Use a headings table to structure a PivotTable report
  • Allocate records to specific PivotTable headings
    Quiz
  • Expand and re-filter to CALCULATE starting and ending balance
  • Use IF logic to perform different calculations on certain PivotTable rows
  • Wrap fields in VALUES to assess current filter context
  • Handle Grand Total rows with COUNTROWS and VALUES
    Quiz
  • Disable expansion for certain PivotTable items
  • Use filter functions like ISFILTERED and HASONEFILTER to determine filter context
PowerPivot for Finance – Part 6:
Enhance your cash flow report
Add all the bells and whistles to your cash flow report, such as expandable financial years, or the option to choose how many previous time periods to view with a simple Slicer. Amend function arguments based on filter context, and finally, summarise your dynamic cash flow report in an easy-to-understand waterfall PivotChart for better data insight.

  • Enable expansion of financial years to individual months
  • Amend a DATEADD argument based on filter context
    Quiz
  • Calculate the current time period offset
  • Use a Slicer to display only the last X time periods
  • Create new label fields to accommodate different sort orders
    Quiz
  • Make an interactive waterfall PivotChart for data insights

Prerequisites

You should be familiar with creating and manipulating PivotTables.
Excel's PowerPivot add-in is only available for Excel 2010/2013.
For attending advanced Power Pivot webinars (part 3, part 4, part 5 and part6), you should be comfortable with the topics covered in the Power Pivot introduction webinars (parts 1 and 2), such as defining relationships, creating calculated columns and measures, manipulating filter context, and using DAX functions such as CALCULATE and DATESYTD.


  PRESENTATION FORMAT

Live webinar format

See the presentation on your PC screen and hear the presenter either from your PC speakers/headphones or on the telephone.

Type questions on the chat line during the presentation. Questions will be answered by the co-presenter.

Stay engaged with multiple-choice quizzes.

Test out the webinar technology with one of the free Excel 10-minute tips webinars.

Presenters

Each webinar has a main presenter and a co-presenter, who share the presentation and question-answering. Having two makes it more engaging, and different viewpoints expressed makes it clearer.

There are a number of different AbleOwl presenters, all of whom are Excel specialists.

Excel versions

Unless specified otherwise, all webinars cover all current Excel versions from 2007 onwards. Any differences among versions are pointed out.

  COMPARISON OF ABLEOWL WEBINARS TO OTHERS'

Webinar characteristics AbleOwl Others
Two presenters share the teaching, so one can answer queries, and the dialogue between makes the webinar more engaging. ***** *
You’re encouraged to interact with the presenters through quizzes and chat-box discussions: you’re not a passive observer. **** **
Presenters are Excel specialists, can give additional insight and answer any queries. ***** ****
Each different presenter brings his/her own personality and humour(?). **** **
You discover not only Excel parts, but also how to structure and standardise, like learning not only words, but grammar too. ***** *
You get a step-by-step PDF manual sent in advance to preview and review. ***** *
You can take advantage of expert post-webinar email support to help you get the full benefit from your new skills. ***** *
You are provided a clear and consistent learning path, without duplication, by the depth and breadth of webinar series topics. ***** ***
You can purchase multiple vouchers at considerable savings. **** **

  TESTIMONIALS

                                 

"Great. I was able to follow really easily and it flowed along logically. Good pace. Good to have the 2 presenters i.e. who can “chat’ about what’s being done, giving 2 points of view etc."
Debbie, Financial Accountant, Hamilton, New Zealand

"I would like to send you a thank you for the clearly presented and well paced webinar I took part in yesterday. This was my first ever webinar and also my first interaction with your company and found it to be a beneficial and enjoyable experience. I look forward to my next webinar and your tips etc."
Fiona, Office Manager, Brisbane, Australia

"The rapport between Paul and Grant was great and makes it more interesting than a one way presentation."
Maureen, Accountant, Taupo, New Zealand


  COURSE CANCELLATION POLICY
 
Cancel up to five working days before the webinar and receive a full refund. All cancellations must be notified in writing, that is, by email, fax or post. Another delegate may be substituted at any time.