BTerrell Group Blog

Sage 300 ERP (Accpac): Sales History Reporting Using PowerPivot

Posted by Tony Zhang on Mon, Sep 30, 2013

This blog is a continuation of the Sage 300 ERP (formerly known as Accpac) business intelligence journey using PowerPivot, and covers Sales History reporting.

Why bother to re-create the Sales History out-of-the-box report from Sage 300 ERP?

The very answer also applies to questioning why I would need Business Intelligence (BI) when I already have my reports. In some sense, it is like comparing financial accounting and managerial accounting – both provide information to support business decisions, but their consumer, format, span and depth are different. Reports usually require certain formats and are created for a set of period, such as fiscal year or period. On the other hand, BI is flexible in its format. Many times it has powerful visualization ability, and can be generated for any period of time such as daily, weekly or monthly. It is important for the BI solution to provide a holistic view of the business and be able to adapt to changes quickly.

As an example, I don’t like to break my train of thought during analysis by closing the report, re-selecting filters (if they are even available), and waiting for minutes if not hours to re-run the report. In addition, in the Sales History report case, I don’t want to limit the access to the report to my ERP users, I want the right person to access the right information anywhere, anytime and on any device.  I will discuss these (data stewardship and mobility) in the coming blogs after I have thoroughly covered Power BI.

Now let’s look at how the Power Pivot delivers the right information with ultimate flexibility.

In the 1st screenshot below, the grand total highlighted in green ties to the numbers in the 2nd screenshot which is from Sage 300 ERP's out-of-the-box Sales History Total Report, the invoice data highlighted in yellow ties to the numbers in the 3rd screenshot that from the Sales History Detail Report. This shows that the PowerPivot report had the right numbers, which gives me peace of mind that my reports will show accurate information. Moreover, without re-running the report, it is delivered in one shot.

PowerPivot Sales History Report:

Sage 300 ERP - Sales History PowerPivot Report

In addition to the out-of-box report filters as shown in the screenshot below, you can see by looking at the slicers circled in the screenshot above, PowerPivot allows us to filter the sales history by week, by item segment 2 and 3, and by category. It takes me minutes -- if not seconds -- to re-group the output to analyze the sales, COGS and margin by item category, customer group, territory, sales person and other dimension attributes, thanks to the Excel data model.

Sage 300 ERP: Sales History Report Input Screen

O/E Sales History Totals Report:

 O/E Sales History Totals Report

O/E Sales History Detail Report:

O/E Sales History Details Report

When I examined the report closely, I noticed that some sales had $0 cost, leading to a 100% margin. While this would be nice, it's certainly not accurate. This happened because the I/C item is set to use “Standard Cost” but the standard cost is set to $0. Plus, the data source used for the sales history report uses standard cost. To analyze the margin using actual cost, we need to change the data source. I will cover it the next blog.

Click here to get a static version of the reportto get a sense of the look and feel. I will share the actual report later after I complete other blogs in this series, which will cover Sales Analysis and Power BI, along with Purchase Analysis.

Stay tuned...

If you would like to learn more, don't hesitate to ask for a FREE 30-minute consultation from BTerrell Group...we'd be happy to help!

FREE 30 Minute Consultation

Related Content in this Series

Tags: Accpac, Sage 300 ERP, reporting, Sales History

Sage 300 ERP (Accpac): Using PowerPivot for Dynamic ABC Analysis

Posted by Tony Zhang on Fri, Aug 30, 2013

This blog is a continuation on the Sage 300 ERP business intelligence journey on Power Pivot, covering ABC analysis, an inventory categorization technique often used in materials management, for both the valuation (cost) and sales of the IC item.

Back in 2010, I was first amazed by the technique used for ABC analysis in Marco Russo's book, PowerPivot for Excel 2010: Give Your Data Meaning. Earlier this year, Gerhard Brueckl wrote a fantastic blog talking about making the ABC analysis dynamic. My blog uses the technique described in the later blog, which you can refer to for more technical details.

Now let’s look at the value that PowerPivot brings to the table. As is shown in the graph and the table below, 33.3% of the items accouns for about 80% of the sales.

By defining the ABC as below:

ABC Class

Low

High

A

0

0.5

B

0.5

0.8

C

0.8

1

We see that the highlighted item, A1-400/0, in the right side of the screenshot below, falls into class C item in term of sales in year 2019, but managed to grow to class B item in year 2020, and achieved class B in total.

ABC Analysis

Applying the technique to bring the ranking of the inventory cost and the sales together, we see A1-400/0 is ranked 16th in the inventory cost among other items and 3rd in total sales.

ABC Analysis Ranking Report

Click here to get a static version of the report to get a sense of the look and feel. I will share this report later after I go through other blogs in this series, which will cover OE Aged Orders and Power BI, and Purchase Analysis.

Stay tuned...

I’d like take this opportunity to thank to many great people for being so open in sharing their solutions to these real world problems, namely: Chris Webb, Rob Collie and Kasper de Jonge, Marco Russo and Alberto Ferrari, Gerhard Brueckl, and Jeffery Wang.

If you would like to get an unprotected version of the worksheet and/or would like to connect the worksheet your Sage 300 ERP system, don't hesitate to ask for a FREE 30-minute consultation from BTerrell Group...we'd be happy to help!

FREE 30 Minute Consultation

Related Content

See AR aging reports blog

See AP aging reports article

See Cash flow forecast blog article

See

If you would like to get an unprotected version of the worksheet and/or would like to connect the worksheet your Sage 300 ERP system, don't hesitate to ask for a FREE 30-minute consultation from BTerrell Group...we'd be happy to help!

"FREE

Related Content

See AR aging reports blog

See AP aging reports article

See Cash flow forecast blog article

" title=" Inventory control article" target="_blank"> Inventory control article

Tags: Sage 300 ERP, PowerPivot, Power Pivot, ABC Analysis, Sales History, Item Valuation, Inventory Categorization