10-DEC_20

Download the data and solution file that Imke uses in PPF Webinar #17

10-DEC_20

In this video, Imke takes us through carrying out advanced scenario modelling in Power BI, with a focus on Power Query.   The scenario here is that we want to model Shipping/Marketing/Overhead costs as calculate sales revenue/net profit. These depend on a number variables including the version of the product (Standard/Premium - each of them has a different set of fixed and variable costs) and the sensitivity to drivers such as price, quantity and exchange rates.  Using a series of simple tables in excel as inputs, Imke shows how to produce: 

• Sensitivity Analysis charts in Power BI showing how sensitive net profit is to a % change in Sales of the Premium/Standard product 

• Tornado charts showing the specific changes (in either direction) for a particular driver: e.g. showing how much net profit increases or decreases if price increases or decreases by 5%. 

• Having different scenarios (with the excel input defining which drivers can be flexed and by how much under each scenario) selectable through a slicer to show the impact of each scenario on net profit. 

• A monte carlo simulation with the number of iterations (e.g. 1000 or 10000) defined through a Power Query parameter, and the probability of the various driver values  - in this case exchange rates - calculated using actual historical exchange rates from Yahoo Finance.   

Some of the Power Query features that Imke demonstrates using this example include:  

• Creating custom functions e.g. to calculate total costs/net profit under various sensitivities which can be called in a repeatable fashion 

• Using tables as parameters into a custom function so that they be applied on any query in the specified format • Creating a ‘fn’ query which is a library of all the custom functions that can be invoked 

• Creating a chain of referenced queries to split out the logic into clearly sequenced steps 

• Building a statistical profile of a driver or output  – showing the range standard deviation/probability profile of values

26-NOV-20

Download the solution file showing Nearest Neighbours that Mark used in his webinar

26-NOV-20

In this session Mark covers:

· Reviewing a standard ‘star schema’ data model used to filter metrics (in this case risk and return) by dimensions/attributes (e.g. company name)

· Introducing a table that has all the “nearest neighbours” for each company (i.e. the competitor companies closest to them, either geographically or by size/nature) into the data model and seeing the challenges with introducing a many:many relationship/bi-directional filters (e.g. model ambiguity)

· Building a matrix visual in Power BI with risk/return values for each company as well as it’s nearest neighbours (including how to deal with values that don’t make sense e.g. totals of averages!)

· Considering alternative approaches to the Many:Many relationship including building your own bridge table, creating inactive relationships (activated on demand) and using TREATAS

· Looking at other uses of this pattern and why TREATAS might be the cleanest solution to this challenge.

· Creating additional Risk/Return measures and applying conditional formatting to highlight favourable vs unfavourable changes.

…plus much more!

12-NOV-20

This is the source data (Excel format) and PBIX solution file for Bob Duffy's session on Cashflow modelling (12-NOV-20)

12-NOV-20

The cashflow report is a bit more complex than initial financial reports as it draws components from both Profit and Loss and Balance Sheet often with built in reconciliation processes.

In this session, Bob will introduce design patterns and options for preparing and presenting the financial cash flow statement for an enterprise. Items covered are:

• Modelling Cashflow Rules

• Dynamic Calculations in      meta data

• Perfecting “pro forma”      cashflow templates

• Making the cashflow      dynamic

• Empowering Excel for      cashflow reporting and analytics

Bob has been involved in taking Financial Reporting from paper based to highly dynamic analytical models in Microsoft BI for many large enterprises in Ireland. Throughout his 30 year career he has worked with many teams of financial accounts and followed the evolution from MI to data warehousing to enterprise BI to self-service analytics.

Bob Duffy is a SQL Server MVP from Dublin, Ireland who is MCA, MCM and Analysis Services “Maestro” certified.

Bob used to work for Microsoft Consulting Services a Senior Consult and subject matter expert for BI/data. He now works at the Prodata SQL Centre off Excellence in Dublin, Ireland where he helps enterprise customers with cloud architecture, design, performance tuning, benchmarking and of course enterprise financial reporting from ERPs into PowerBI

29-OCT-20

In this session Chris will show you how to extend, build & Connect the Power Platform to Dynamics 365 First party applications in a way that makes sense…..By using LEGO as the example. Turn your Field service app into a facilities management solution, turn your marketing app into a MRM solution! The world of possibilities is endless with the opportunity provided to you within this gorgeous stack of products.

15-OCT-20

In this session, Belinda covers:

· A history of the Dynamic products family

· An overview of Dynamics BC – it’s features and who it is aimed at

· A look at the tiles and visuals within the Dynamics BC portal (including embedded Power BI reports!)

· Integration with Outlook – the ability to see context-sensitive Dynamics BC information in an Outlook pane and even create documents (e.g. invoices/purchase orders) directly from an email

· Using the Dynamics BC connector in Power BI to get data

· Working with pre-built BC Power Automate templates for approval workflows

· Using the PowerApps connector to create CRUD apps

Plus much more!

01-OCT-20

In this session, Julia provides an intro to Dynamics 365 FinOps, covering:

· Where Dynamics 365 FinOps sits alongside the other areas of D365 (e.g. Customer Engagement, Business Central as well as modular/AI/Mixed Reality components)

· An overview of the modules/workspaces included as part of D365 FinOps including Accounts Payable, Receivable, General Ledger, Revenue Recognition and Tax Management

· Working with tiles and tables within a Finops workspace

· Using the task recorder to perform common/repetitive activities

· A look at the embedded Power BI reports within D365

· Financial Statement reporting within D365 FinOps with drill-down into individual transactions/documents

· Integration between FinOps and Excel – exporting filtered/incremental data using templates, making changes in Excel and publishing back to D365

· Exporting entities to various formats using the Data Management module and integration with Common Data Service (CDS)/Dynamics 365 CE using Data Integrator

· The D365 Finops connector for Power Apps (Canvas Apps) and Power Automate

· PowerApps embedded within D365 FinOps workspaces for non-standard processes or tasks that cross multiple areas.

· Connecting to D365 FinOps data in Power BI (With an embedded PowerApps visual) via the oData connector

· Integration between D365 FinOps and Power Virtual Agent (PVA) – chat bots which allow you to perform tasks (e.g. raising a Purchase Order), ask questions of your finance data and even utilise other Power Platform actions (e.g. kicking off a Power BI Dataset refresh!)

· Model-driven PowerApps and Optical Character Recognition (OCR) to e.g. read/process scanned PDFs

· AI Models within Power Automate for finance tasks (e.g. receipt processing)

17-SEP-20

Business depends on the income statement as an indication of past and ongoing performance; however, in its paper form, it limits the reader from uncovering insights that lie beneath. In this session we will learn how mirroring the familiar statement and adding dynamic features improve engagement and help the Accountant tell a great data story.

Mark is an Accounting and Analytics Manager at Industrial Finishes and Systems, Inc. (IFS) located in Eugene Oregon. Since 1958, IFS, has brought product and system solutions to Auto Body, Recreational Vehicle, Wood Product, and Aerospace industries. He is responsible for financial data modeling and data storytelling both internal and external to the business. Outside of IFS, Mark enjoys speaking with local finance groups and blogging at StoryBI.com. Mark is an Oregon Certified Public Accountant with a Finance degree from Western Washington University.

03-SEP-20

It can be a struggle to build reporting that cleanly meets the needs of all levels of user.  Chris will walk through a real example built while working for Microsoft’s Finance org and give tips on how to take advantage of Power BI features to build reports that guide users to depth without weighing down the Executive views.

After spending 10 years in Finance and Accounting, Chris moved into his current role as a Sr. Program Manager for Microsoft's Power BI Customer Advisory Team with a primary focus on front end reporting and user experience.  In his day job he spends time working with enterprise customers to help skill up report authors and guide design for high impact reporting.  Check out his blog at www.alluringbi.com for tips and tricks for the Power BI Creator

20-AUG-20

Slides, datasets and completed solution files for PPF Webinar # 9 - Visual Cashflow Statements in Power BI

20-AUG-20

One of the first thing we all learn in Business 101 is that Cash is needed to operate a business.  We also learned that being Profitable does not mean you have cash in the bank.

​

Have you ever really looked at the traditional cash flow statement?  It’s supposed to provide information about gross receipts and gross payments.  Does it really do that?

Attend this session and see Belinda present alternatives to the traditional cash flow statement and cash flow analysis using Microsoft Power BI.

1. Start the process of new ways to visualize the movement of your cash

2. Create a plan that can provide information to your team that might improve cash flow

3. Provide information to that person in your company who is always asking for the bank balance.

​

Belinda Allen is a Microsoft Most Valuable Professional (MVP) for Power BI.  Belinda is currently a Microsoft Power Platform Trainer, Speaker, and the Co-Author of 2 books on Business Intelligence and Microsoft Dynamics GP.

06-AUG-20

The comments feature in Power BI is great but you'll probably hit its limitations quite quickly - e.g. that you can't tie the comments to specific data points, apply row Level security to them and can't extract them from Power BI into anywhere else.

The solution? Use the native PowerApp visual in Power BI linked into SharePoint and have a full data-point-specific commentary solution built in minutes.

Samuel Woldu-Desta shows how to build this solution from scatch in minutes!

23-JUL-20

In this captivating, demo-packed session, Jese shows how to build a financial forecasting tool in Power BI by utilising PowerApps, Power Automate and SharePoint Online.

Some of the features shown include:

- Using What-if parameters in Power BI for creating dynamic forecasts

-Development from scratch of a single-screen PowerApp with drop-downs, list data from SharePoint and text boxes for commentary to support the forecast multiplier for each account

- Building collections in PowerApps to hold values and read/write to and from the screen.

-Setting Variables in PowerApps to identify an incremental index for the list item

- Use of the PATCH and ForAll functions in PowerApps to write back to the SharePoint list (used as a data source for the Power BI Report)

- Integration with Power Automate to automatically refresh the Power BI Dataset once a forecast is submitted

- Embedding a PowerApp form within a Power BI report

- Use of bookmarks in Power BI to toggle the visibility of the PowerApp embedded within the report

Download the resources (including the Power BI and PowerApps files used) at https://github.com/jesenavaranjan/Pow...

​

​Visit Jese's PowerChannel YouTube at https://www.youtube.com/user/JeseNavar

9-JUL-20

In under an hour Rory demonstrates:

- Setting up/managing environments for Power Apps - Creating a new solution for your CDS database

-Creating CDS Entities and fields within your new solution

- Creating a form and a model-driven app on top of the CDS Data

-Creating a canvas app from the CDS data including a PowerApp form that allows you to attach/upload an image for each expense

-Customising the look and feel of the canvas app

-Creating a new screen in the canvas app for a full-screen view of the image attached to any given expense

-Creating variables in PowerApps and populating/retrieving them

- Obtaining the Environment URL from PowerApps

-Bringing the CDS data into Power BI for a reporting view

​

Visit www.dataspinners.co.uk and youtube.com/dataspinners to learn more from Rory!

6-JUL-20

This session on tips and tricks for finance reporting, presented by Rishi Sapra ,is broken down into a series of bite-size videos covering:

​

Tip #1: Use an Account Hierarchy

Tip #2: Build up Measure Chains and Format them

Tip #3: Show Periods (MTD/QTD/YTD) dynamically

Tip #4: Have a table with all your FS lines (including totals)

Tip #5: Tell a story with the visuals

Tip #6: Finance Custom Visuals/Add-ins go a long way

​

These tips are based on the same Income statement example below (also available at aka.ms/pbiincomestatement)

11-JUN-20

- Integrate with multiple accounting systems (e.g. Xero, Sage), bringing all the disparate data into a single, conformed, centralised data model 

- Utilise pre-built Reporting templates on your data with Power BI/Power Apps integration, report tooltips, AI visuals and financial DAX measures already developed 

- Write-back to numerous data sources including SQL Server, Analysis Services and Oracle 

-Intercompany consolidation across entities including the ability to deal with local to Group currency conversions and eliminations. 

- Visual Planning with 6 Custom visuals available on AppSource (many of which have a free version) , including Small multiple variance analysis 

-Allocation from top-level planning changes to detailed rows, including use of smart formulas. 

-Data-point specific commentary with Rich Text formatting, links and attachments Plus Much More!

28-MAY-20

Take Power BI to a whole new level with Power On BI. Just some of the incredible features are: 

- Fixed format financial statement reporting without the need for Paginated Reports 

- Real-time Write-back directly to the Analysis Services cube or Power BI Premium dataset that sits behind your finance reports: Make changes to versions of data and see the updates immediately! 

- Planning allocations/spreads: Use smart formulas for allocating sub-total changes through to individual lines proportionally 

-Data-point specific commentary (Rather than just on a visual/report page) 

-Goal-seek: Work out the specific attributes/dimensions to vary and have it automatically work out the changes to achieve an overall total/target  

Plus many more!

22-MAY-20

In this comprehensive overview of Power BI (Desktop and service) applied to a finance scenario, learn how to take advantage of all the functionality that Power BI has to offer including:]

- Automatically consuming files in a folder

- Storytelling with data

- Dynamics graphs and measures

- Waterfall charts and tables

- Row Level security

- Analysing data with Excel

- Reporting commenting and subscriptions

- Q&A And much more!

This fun and engaging session is delivered as a role play based on real-life examples of how Power BI can deliver all the functionality that finance (and non finance!) teams need to be efficient!