BTerrell Group Blog

Tony Zhang

Recent Posts

Sage 300 ERP (AccPac): 1099 Report Using PowerPivot

Posted by Tony Zhang on Fri, Jan 24, 2014

The other day I was asked by one of my controller clients:

“Is there a way to run a vendor transaction report that shows the vendor name, the sum of the payments made to the vendor, and the 1099 flag?" 

He continued, "I want to be able to review a report of all vendor payments and see if they are currently flagged for 1099 reporting or not.  That way, if I see a vendor that I think should be flagged but isn’t, then I can research it.”

If you use Sage 300 ERP and report 1099, I highly recommend you to read Chris Firra’s recent blog: Common Questions Re:Generating 1099-MISC Forms from Sage 300 ERP. This will give you a good understand of how 1099 works in Sage 300 ERP.

As quoted from Chris’s blog, “Unfortunately, there is no standard report that shows the detail of invoices that make up a 1099 amount.”

This blog presents the solution using PowerPivot. In the following screenshot, the payments are filtered by year/month (time dimension), vendor’s 1099 code and the transaction’s 1099 code. In case there is a question, you can research by following the payment batch-entry number AND the invoice batch-entry number…quickly.

Sage 300 ERP: PowerPivot 1099 Report

 

In the following example, the payment in 1621-31 was $7,400, but only $6,900 is applicable to 1099 code 1, when checking invoice entered in 6773-13.

1099PowerPivot2 resized 600

1099PowerPivot3 resized 600

Finally, the user can compare - and update if necessary - the number in the 1099/CPRS Inquiry as shown in the screenshot below:

1099PowerPivot4 resized 600

I’d also like to point out that this report is based on the same data model used by the AP aging report, with 1099 code fields added to the Vendor and Payment tables, as show below.

1099PowerPivot5 resized 600

Like to have such a report on your hand to improve the productivity? Don't hesitate to contact the experts at BTerrell Group!

 

Other Related Blog Posts

PowerPivot Series

Tags: Sage 300 ERP, PowerPivot

Sage 300 ERP (AccPac): Project Analysis Using PowerPivot

Posted by Tony Zhang on Wed, Jan 08, 2014

This blog is a continuation on the Sage 300 ERP business intelligence journey using PowerPivot. I will focus on the project analysis in this blog.

If you use Sage 300 ERP Project and Job Costing module, then you might like a report like this:

PowerPivot Project Analysis Report using Sage 300 ERP data

The report replicates the Sage 300 ERP Contract Summary report, with additional information like PM Cost Left to Spend while providing the flexibility and performance improvements brought by using Excel PowerPivot. For example, this report allows you to select projects that are not consecutive by holding the CTRL key and click the projects you like from the “Project” slicer circled above, which is impossible when using the standard report as show below:

Sage 300 ERP Contract Summary Report

I highlighted the numbers in the Excel report above and the Crystal report below to show they are the same.

Sage 300 ERP Contract Summary - Crystal Reports

Sage 300 ERP Contract Summary Totals - Crystal Reports

The next report is meant to analyze the project cost. Notice that both original cost and current cost estimates are presented together with the actual cost – a more comprehensive analysis comparing with the “Detailed Costs” as show in the following screenshots, where only one of them is presented in the report.

Sage 300 ERP - Project Cost Report using PowerPivot

Sage 300 ERP: Detailed Costs Report

Again, I highlighted the numbers in the Excel report above and the Crystal report below to show they are the same.

ProjectAnalysis7 resized 600

The last example is the standard “Transaction History” report and the PowerPivot version of it. Again, the point here is the flexibility and the performance gain from using PowerPivot.

ProjectAnalysis8 resized 600

ProjectAnalysis9 resized 600

Sage 300 ERP: Transaction History Report Using PowerPivot

I hope this blog is helpful as you continue to understand PowerPivot’s position and the level of professional services we provide here at BTerrell Group. Feel free to view the report in static form here to grasp the look and feel. Please “Scroll to the first sheet” to see those three reports.

ProjectAnalysis11 resized 600

 

Other Related Blog Posts

 

 

 

Tags: Sage 300 ERP, PowerPivot

Financial Dashboards in Sage 300 ERP Using PowerPivot

Posted by Tony Zhang on Fri, Dec 20, 2013

This blog is a continuation on the Sage 300 ERP business intelligence journey using PowerPivot. I will focus on the financial statements in this blog.

Let’s begin by looking at the tools. Many tools are used to prepare financial statements, namely Income Statements, Balance Sheets and Cash Flow Reports. In the Sage 300 ERP (formerly Accpac) world, the three, Excel based, tools widely used are: FR (Financial Reporter), Sage Insight and Sage Intelligence.

FR is included with Sage 300 ERP at no additional cost. FR reports on GL data, however does not support reporting on multiple companies and does not have a distribution function.

Sage Insight (now Spreadsheet Analyst) is a comprehensive solution for financial reporting, bugeting and distribution – via email or shared folder. This tool conceptualizes row and colum groups and features like roll-up, sheet order by and column order by.

Sage Intelligence was introduced within Sage 300 ERP's version 5.6 and offers out-of-box reporting templates and tools like, Financial Report Designer, which comes in two options – Layout Generator and Add-in.


The three options in designing a Sage Intelligence report are:

• Excel functions like v-lookup, which matches and indexes pulled data into a hidden worksheet
• Layout generator
• Add-in

In deciding which one best suits your work environment, I recommend checking out the FAQ in this link. Pay attention to Q7 and Q11. I, you use these tools or other tools for financial reporting, I would like to hear your comments. If you are unsure about which tool you need, please contact me to discuss your requirements and I'd be glad to help.

PowerPivot is not designed for Financial Reporting. Microsoft’s solution to such requirements, after dismissing FRx, is Management Reporter, which is similar to Sage Insight with the concept of row group, column group, consolidation and the distribution function.

With this foundation being provided, PowerPivot is great for financial reporting. You may find live examples here. Below is an example using Sage 300 ERP Sample Company, Inc. PowerPivot’s main benefit is the flexibilty and performance.

PowerPivot

Sage 300 ERP

Financial Reporting

I hope this blog is helpful as you continue to understand PowerPivot’s position and the level of professional services we provide here at BTerrell Group. Feel free to view the report in static form here to grasp the look and feel.

Other Related Blog Posts


Tags: Sage 300 ERP, Financial Reporting, PowerPivot

Sage 300 ERP: Operational Data Analysis Using PowerPivot - Wrap-Up

Posted by Tony Zhang on Thu, Dec 05, 2013

This is my final blog wrapping up our series on Sage 300 ERP operational data analysis using PowerPivot. Follow this link to interact with the workbook. Please note, you need Excel 2013 Professional Plus and will need to enable the Power View add-in for the interaction with the “Sales Dashboard”, as shown in the first screenshot below.

As you work with the slicer, please note that some of the slicers are linked to more than one report, as shown in the second screenshot below.

Below, are the worksheets included in the workbook and the links to the blogs about them. Follow this link for a static view.   

  1. Inventory Control
  2. ABC Item Sales
  3. ABC Item Valuation
  4. SO
  5. Aged SO
  6. Sales History
  7. Sales Analysis
  8. PO Action
  9. Aged PO
  10. Purchase History
  11. Purchase Analysis
  12. Item Forecast

 

Screenshot 1:

Sage 300 ERP Power Pivot
Screenshot 2:

Sage 300 ERP Power Pivot

I hope you've enjoyed this series of blogs, and that they've helped you with using PowerPivot to analyze, manipulate and report on Sage 300 ERP data.

In the next blog, I will talk about payroll analysis using PowerPivot.

Tags: Sage 300 ERP, PowerPivot

Sage 300 ERP (Accpac): Inventory Forecast Using PowerPivot

Posted by Tony Zhang on Fri, Nov 22, 2013

Sage 300 ERP (formally known as Accpac) “Location Details” UI provides item quantity information by location. This number comes from the ICILOC table and updates in real time when you create or update a PO and SO and when you receive or ship items. In ICILOC, each item and location combination creates a record within the table. The records include columns such as:

  • Quantity on Hand (QOH)
  • Quantity on Sales Order (QOSO)
  • Quantity on Purchase Order (QOPO)
  • Quantity Committed, and
  • Quantity Available to Ship
Often these features are used to check inventory levels for certain items at different locations.

Unfortunately, there is not a report or UI in Accpac if you want to know your inventory levels at a specific date. In this blog, I share with you a solution to this problem using PowerPivot.

First, I added the PO and SO header and detail tables into the data model. Then, I built the relationship between the time dimension, the sales order date and the expected shipment date. Now, when a specific date is selected, the quantity is calculated in the filter context of the sales order date or expected shipment date. In the screenshot below, the IC QOH, IC QOSO, IC QOPO pull from ICILOC, so they don’t change with the date selection. The rest columns are calculated columns and are time sensitive and filtered by the date selection.

Sage 300 ERP Inventory Forecasting

To get a sense of the look and feel of this report, click here. I will share this report in my next blog where I will wrap up our PowerPivot series on operation management.

Related Content in this Series

Tags: Sage 300 ERP