This is all about how I use GP, based on what I learned in my investigations with clients...

Microsoft Excel Power Maps in Power BI

Posted by Belinda Allen on Jun 2, 2014 10:25:00 AM

This AWESOME Power BI tool allows you to “Visualize” your data not only in a map, but as a video over a period of time.  It allows you to SEE your data in ways you never even thought amount.  Take a tour with me of Elevator Complaints in New York City for Q1, 2014.

Power Map Elevator Complaints Q1-2014

 I am using this with Microsoft Dynamics GP Sales Order Processing Data in my own system.

Belinda, Smith & Allen Consulting of NYC, Microsoft Dynamics GP blogger shows Microsoft Power BI Power Maps!

Tags: Excel

Microsoft Dynamics GP – Make Excel Data refreshing easier (video)

Posted by Belinda Allen on Dec 16, 2013 10:00:00 AM

This is part 7 of a 7 part series of Microsoft Dynamics GP Dashboards for Distribution presentation I did at the GPUG Summit in Tampa, November 2013.

 

As promised, here you can see how to set Excel to perform data refreshes upon opening the file.

 Belinda Allen of Smith & Allen Consulting, Inc. in New York, NY speaks at the Microsoft Dynamics GP User Group (GPUG) Summit in Tampa, FL 2013.

Dashboard for Distribution Series:

Part 1:  Microsoft Dynamics GP - Excel AR Trial Balance

http://saci.com/blogs/belinda-the-gp-csi/bid/154096/Microsoft-Dynamics-GP-Excel-AR-Trial-Balance-with-video

Part 2:  Microsoft Dynamics GP - Excel AR Trial Balance with SQL 

http://saci.com/blogs/belinda-the-gp-csi/bid/154097/Microsoft-Dynamics-GP-Excel-AR-Trial-Balance-with-SQL-with-video

Part 3:  Microsoft Dynamics GP - Excel Power Pivot SOP Dashboard

http://saci.com/blogs/belinda-the-gp-csi/bid/154098/Microsoft-Dynamics-GP-Excel-Power-Pivot-SOP-Dashboard-with-video

Part 4:  Microsoft Dynamics GP – Inventory Item Reorder Dashboard

http://saci.com/blogs/belinda-the-gp-csi/bid/154099/Microsoft-Dynamics-GP-Inventory-Item-Reorder-Dashboard-with-video

Part 5:  Microsoft Dynamics GP – Open Purchase Order Table

http://saci.com/blogs/belinda-the-gp-csi/bid/154101/Microsoft-Dynamics-GP-Open-Purchase-Order-Table-with-video

Part 6:  Microsoft Dynamics GP – Why avoid 3D charts in Excel?

http://saci.com/blogs/belinda-the-gp-csi/bid/154104/Microsoft-Dynamics-GP-Why-avoid-3D-charts-in-Excel-video

Tags: Excel

Microsoft Dynamics GP – Why avoid 3D charts in Excel? (video)

Posted by Belinda Allen on Dec 9, 2013 10:30:00 AM

This is part 6 of a 7 part series of Microsoft Dynamics GP Dashboards for Distribution presentation I did at the GPUG Summit in Tampa, November 2013.

 In the video below I show why financial information should never be shown with a 3D chart.

Belinda Allen of Smith & Allen Consulting, Inc. in New York, NY speaks at the Microsoft Dynamics GP User Group (GPUG) Summit in Tampa, FL 2013.

Dashboard for Distribution Series:

Part 1:  Microsoft Dynamics GP - Excel AR Trial Balance

http://saci.com/blogs/belinda-the-gp-csi/bid/154096/Microsoft-Dynamics-GP-Excel-AR-Trial-Balance-with-video

Part 2:  Microsoft Dynamics GP - Excel AR Trial Balance with SQL 

http://saci.com/blogs/belinda-the-gp-csi/bid/154097/Microsoft-Dynamics-GP-Excel-AR-Trial-Balance-with-SQL-with-video

Part 3:  Microsoft Dynamics GP - Excel Power Pivot SOP Dashboard

http://saci.com/blogs/belinda-the-gp-csi/bid/154098/Microsoft-Dynamics-GP-Excel-Power-Pivot-SOP-Dashboard-with-video

Part 4:  Microsoft Dynamics GP – Inventory Item Reorder Dashboard

http://saci.com/blogs/belinda-the-gp-csi/bid/154099/Microsoft-Dynamics-GP-Inventory-Item-Reorder-Dashboard-with-video

Part 5:  Microsoft Dynamics GP – Open Purchase Order Table

http://saci.com/blogs/belinda-the-gp-csi/bid/154101/Microsoft-Dynamics-GP-Open-Purchase-Order-Table-with-video

Tags: Excel

Microsoft Dynamics GP – Open Purchase Order Table (with video)

Posted by Belinda Allen on Dec 2, 2013 10:00:00 AM

This is part 5 of a 7 part series of Microsoft Dynamics GP Dashboards for Distribution presentation I did at the GPUG Summit in Tampa, November 2013.

In the video below I create custom SQL Code (that you can download below the video) and create an Excel Table that will help me manage Purchase Orders and their Approvals in Microsoft Dynamics GP.

 

Click on the link below is the link for the code I SQL code I created in the first part of the Video.

PO SQL Code Download Here

Belinda Allen of Smith & Allen Consulting, Inc. in New York, NY speaks at the Microsoft Dynamics GP User Group (GPUG) Summit in Tampa, FL 2013.

Dashboard for Distribution Series:

Part 1:  Microsoft Dynamics GP - Excel AR Trial Balance

http://saci.com/blogs/belinda-the-gp-csi/bid/154096/Microsoft-Dynamics-GP-Excel-AR-Trial-Balance-with-video

Part 2:  Microsoft Dynamics GP - Excel AR Trial Balance with SQL 

http://saci.com/blogs/belinda-the-gp-csi/bid/154097/Microsoft-Dynamics-GP-Excel-AR-Trial-Balance-with-SQL-with-video

Part 3:  Microsoft Dynamics GP - Excel Power Pivot SOP Dashboard

http://saci.com/blogs/belinda-the-gp-csi/bid/154098/Microsoft-Dynamics-GP-Excel-Power-Pivot-SOP-Dashboard-with-video

Part 4:  Microsoft Dynamics GP – Inventory Item Reorder Dashboard

http://saci.com/blogs/belinda-the-gp-csi/bid/154099/Microsoft-Dynamics-GP-Inventory-Item-Reorder-Dashboard-with-video

Tags: Purchase Order Processing, Excel

Microsoft Dynamics GP – Inventory Item Reorder Dashboard (with video)

Posted by Belinda Allen on Nov 25, 2013 10:00:00 AM

This is part 4 of a 7 part series of Microsoft Dynamics GP Dashboards for Distribution presentation I did at the GPUG Summit in Tampa, November 2013.

 

In this video, I build an Excel Refreshable Report that shows when I need to reorder Inventory Items based on Item Quantities Maintenance and the Item Resource Planning Maintenance (Order-Up-To Level) in Microsoft Dynamics GP.  

 Belinda Allen of Smith & Allen Consulting, Inc. in New York, NY speaks at the Microsoft Dynamics GP User Group (GPUG) Summit in Tampa, FL 2013.

Dashboard for Distribution Series:

Part 1:  Microsoft Dynamics GP - Excel AR Trial Balance

http://saci.com/blogs/belinda-the-gp-csi/bid/154096/Microsoft-Dynamics-GP-Excel-AR-Trial-Balance-with-video

Part 2:  Microsoft Dynamics GP - Excel AR Trial Balance with SQL 

http://saci.com/blogs/belinda-the-gp-csi/bid/154097/Microsoft-Dynamics-GP-Excel-AR-Trial-Balance-with-SQL-with-video

Part 3:  Microsoft Dynamics GP - Excel Power Pivot SOP Dashboard

http://saci.com/blogs/belinda-the-gp-csi/bid/154098/Microsoft-Dynamics-GP-Excel-Power-Pivot-SOP-Dashboard-with-video

Tags: Inventory, Excel

Microsoft Dynamics GP - Excel Power Pivot SOP Dashboard (with video)

Posted by Belinda Allen on Nov 18, 2013 10:00:00 AM

This is part 3 of a 7 part series of Microsoft Dynamics GP Dashboards for Distribution presentation I did at the GPUG Summit in Tampa, November 2013.

 

In the following video, I create a Dashboard the shows me the following information:

  1. Top 10 Customers by Sales
  2. Top 10 Items by Sales
  3. Top 10 Items by Quantity
  4. Customer Balance

The three top 10 widgets can be displayed by posted, unposted, by document type, by date range, period, quarter, week year or day.  This video was created in Excel 2013 Professional Plus.

Download Victoria Yudin's view from her blog post by clicking the link below:

SQL view with all SOP line items in Microsoft Dynamics GP

 

Belinda Allen of Smith & Allen Consulting, Inc. in New York, NY speaks at the Microsoft Dynamics GP User Group (GPUG) Summit in Tampa, FL 2013.

 

Dashboard for Distribution Series:

Part 1:  Microsoft Dynamics GP - Excel AR Trial Balance

http://saci.com/blogs/belinda-the-gp-csi/bid/154096/Microsoft-Dynamics-GP-Excel-AR-Trial-Balance-with-video

Part 2:  Microsoft Dynamics GP - Excel AR Trial Balance with SQL 

 http://saci.com/blogs/belinda-the-gp-csi/bid/154097/Microsoft-Dynamics-GP-Excel-AR-Trial-Balance-with-SQL-with-video

Tags: Sales Order Processing, Excel

Microsoft Dynamics GP - Excel AR Trial Balance with SQL (with video)

Posted by Belinda Allen on Nov 11, 2013 10:00:00 AM

In this video, I create a Summary Aged Trial Balance for Receivables Management using a custom SQL view created by Microsoft Dynamics GP MVP, Victoria Yudin.  The view can be located on her blog posting below:

 

http://victoriayudin.com/2012/01/25/sql-view-for-current-receivables-aging-in-dynamics-gp/

Dashboard for Distribution Series:

 

Part 1:  Microsoft Dynamics GP - Excel AR Trial Balance

 

http://saci.com/blogs/belinda-the-gp-csi/bid/154096/Microsoft-Dynamics-GP-Excel-AR-Trial-Balance-with-video

 

 

Tags: Receivables Management, SQL, GP2013 Weekly Topics, Excel

Microsoft Dynamics GP - Excel AR Trial Balance (with video)

Posted by Belinda Allen on Nov 4, 2013 10:00:00 AM

This is part 1 of a 7 part series of Microsoft Dynamics GP Dashboards for Distribution presentation I did at the GPUG Summit in Tampa, November 2013.

 

In this video, I create a Summary Aged Trial Balance for Receivables Management with NO SQL work, No VBA’s, No Macros, No VLookups and No named ranges.

Belinda Allen of Smith & Allen Consulting, Inc. in New York, NY speaks at the Microsoft Dynamics GP User Group (GPUG) Summit in Tampa, FL 2013.

The formulas used in this video are:

  1. Amount:  =IF([@[Document Type]]="Payments                       ",-[@[Current Trx Amount]],IF([@[Document Type]]="Credit Memo                    ",-[@[Current Trx Amount]],IF([@[Document Type]]="Returns                        ",-[@[Current Trx Amount]],[@[Current Trx Amount]])))

  2. Days Due:  =IF([@[Document Type]]="Payments                       ",0,IF([@[Document Type]]="Credit Memo                    ",0,IF([@[Document Type]]="Returns                        ",0, DAYS(TODAY(),[@[Due Date]]))))
  3. Bucket: 

    =IF([@[Days Due]] <1,"Current", IF([@[Days Due]] <31,"0 - 30 Days",IF([@[Days Due]] <61,"30 - 60 Days",IF([@[Days Due]] <91,"60 - 90 Days","Over 90 Days"))))

Tags: Receivables Management, Excel

AP aging in Excel thanks to Victoria Yudin in Microsoft Dynamics GP

Posted by Belinda Allen on Aug 5, 2013 12:32:00 PM

A GPUG (Microsoft Dynamics GP User Group) member was asking about building a report in Excel that displayed open payables that actually matches GP.  There were several great responses to his question, but now I want to add my two cents!

My Friend and fellow Microsoft MVP, Victoria Yudin has made this process a lot easier for you.  She has a SQL view that will organize your open payables and age them for you.

This video shows how I use this view to build a quick Microsoft Dynamics GP Payables Management Aged Trial Balance in a few minutes.

 

If you haven’t visited Victoria’s blog, you should.  It can be found @ http://victoriayudin.com/ and this view in particular @ http://victoriayudin.com/2013/04/24/sql-view-for-current-payables-aging-detail-in-dynamics-gp/ 

 Thanks Victoria for sharing your hard work!

Belinda, from Smith & Allen Consulting, Inc. in New York City, uses a view by fellow Microsoft MVP Victoria Yudin to create a Payables Aged Trial Balance in Excel for Microsoft Dynamics GP.

Tags: Payables Management, Excel

Hangout: Building Dashboards with Microsoft Dynamics GP 2013 & Excel

Posted by Belinda Allen on May 28, 2013 3:00:00 PM

MSDynamicsWorld.com held a Google Hangout last month and I was lucky enough to be asked to be on the Panel.  If you missed, here's your chance to see it again!  (Yes, I copied this right from their channel!)



This panel discussion featured Mark Polino, author of the newly released book "Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013" along with Dynamics GP financial reporting experts Belinda Allen of Smith and Allen Consulting and Jared Hall, Program Manager at Microsoft. Learn more about Mark's new book and related topics around creating, managing, and distributing financial dashboards with Dynamics GP and Excel.

Some of the topics covered in our live conversation included:
* Dashboard Basics - who wants dashboards, what elements are a must-have, and what are the must-have skills of a dashboard developer?
* Excel 2013 dashboard fundamentals
* Running Excel Reports
* Managing dashboard requirements
* Drilling down: From Excel to GP
* Sharing and Collaboration Options

Jason Gumpart and MSDynamicsWorld.com is a great guy with great content.  Subscribe to their YouTube channel and to their site:

MSDynamicsWorld on YouTube

www.MSDynamicsWorld.com

MSDynamicsWorld.com holds a Google Hang out on Microsoft Dynamics GP and Excel with Jason, Jared, Mark and Belinda.

Tags: GP 2013, Excel