Belinda Allen, Smith & Allen Consulting, Inc. (SACI) aka Belinda the GP CSI.  Microsoft Professional, MCT and MVP.describe the imagedescribe the image

Belinda supports lls.org

Follow Me

MICROSOFT DYNAMICS GP COMMUNITY

If you are not a member of the Microsoft Dynamics GP Community, you should be, I am a member.

There is loads of good information, including a forum that I actively answer.

Click on this link to go to the Microsoft Dynamics Community!

Search this Blog

Loading

Subscribe by Email

Your email:

Need Microsoft Dynamics GP Forms?

Purchase Deluxe Products

Click on the image above to find your compatible Microsoft Dynamics GP forms from Deluxe For Business.

Click HERE to download Brochure.

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

Current Articles | RSS Feed RSS Feed

Microsoft Excel Power Maps in Power BI

  
  
  
  
  

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!

Belinda (the GP CSI) Allen
Belinda@saci.com
917-445-9070
Tags: 

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

  
  
  
  
  

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

Belinda (the GP CSI) Allen
Belinda@saci.com
917-445-9070
Tags: 

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

  
  
  
  
  

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

Belinda (the GP CSI) Allen
Belinda@saci.com
917-445-9070
Tags: 

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

  
  
  
  
  

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

Belinda (the GP CSI) Allen
Belinda@saci.com
917-445-9070

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

  
  
  
  
  

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

Belinda (the GP CSI) Allen
Belinda@saci.com
917-445-9070
Tags: ,

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

  
  
  
  
  

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

Belinda (the GP CSI) Allen
Belinda@saci.com
917-445-9070

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

  
  
  
  
  

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

 

 

Belinda (the GP CSI) Allen
Belinda@saci.com
917-445-9070

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

  
  
  
  
  

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"))))

Belinda (the GP CSI) Allen
Belinda@saci.com
917-445-9070

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

  
  
  
  
  

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.

Belinda (the GP CSI) Allen
Belinda@saci.com
917-445-9070

Hangout: Building Dashboards with Microsoft Dynamics GP 2013 & Excel

  
  
  
  
  

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.

Belinda (the GP CSI) Allen
Belinda@saci.com
917-445-9070
Tags: ,
All Posts