BTerrell Group Blog

Tony Zhang

Recent Posts

Sage 300 ERP (Accpac): Purchase History and KPIs Using PowerPivot

Posted by Tony Zhang on Fri, Oct 18, 2013

This blog is a continuation on the Sage 300 ERP business intelligence journey using PowerPivot, and covers Purchase Analysis.

In the following Purchase History example, I created a KPI to compare the purchase unit cost with the number from the previous year.  The unit cost is greater than 105% from the previous year, so it's highlighted in red.  This screenshot shows a number as green if the number is within the target and yellow as a warning you are about to be out of the target.

I track this purchase KPI by vendor, vendor group, vendor payment term, item, item category, item segments, etc., by using this particular data model.  

10 18 1

10 18 2

In the end, I gain insight into the purchasing operations while also producing the same results as I do with the standard Purchase History (please see the following two screen shots).

10 18 3

10 18 4


I hope you found this blog 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 to grasp a better view of the look and feel.

Related Content in this Series


Tags: Accpac, Sage 300 ERP, KPI, Purchase history, PowerPivot

Sage 300 ERP (Accpac): Sales Analysis Overview Using PowerPivot

Posted by Tony Zhang on Fri, Oct 11, 2013

Designing reports which deliver insight into your business to help you control and improve business processes are monumental to improving your sales analysis.  Creating these reports and recommendations requires expertise in the following areas:

  • Business Process and Requirements
  • Data Modeling, understanding the current form of your data and the steps required to transition it into a usable format
  • Designing the Reporting Tool

In a perfect enviroment, having all three of these components is a luxury.  Often times, however, only having two still produces good results.  Even with creating accurate reports, developed by the end users using Crystal Reports or Excel, our clients find that from time to time they still need a little assistance in locating the right data table and fields in the ERP database or in the advanced reporting tool.  I often find many reporting tool vendors view the problem from the technical side and claim the tool is powerful and easy to use, which is true if  the data source structure is relatively simple.  Unfortunately, this is not the case in the ERP world, given the comlexity of the ERP data.  Hundreds, if not, thousands of tables and numorus data fields are designed for transactional processing and not for reporting and analysis.  More than a decade ago, data modeling techniques, such as Cube, were created to adddress challenges in reporting and analysis (one example is: SQL server analysis service (SSAS)). The problem is the SSAS data cubes are not easy to build and maintain, which translate to higher cost of ownership.  Below, I believe you will see why using the Excel Data Model, PowerPivot – the silbling of SSAS, is a viable option for you and your business.

In this blog, I demonstrate how to gain insight into your sales numbers in Sage 300 ERP using the Excel Data Model, and also make the point why working with a professional services member makes an impact in your workflow.

In my previous blog, I raised the question about the margin calucated in the Sales History Report.  Here, I have listed the report data source for the Sage 300 ERP Sales History Tables, OESHHD and OESHDT.  Since we do not have the actual cost information from the Sales History Table, we need to find it from a separate data table.  In this case, I used the Sale Posting Journal Tables, OEAUDH and OEAUDD, which, have 113 and 114 fields seperately.  As you can see, this information is right in front of us to match it to the right data fields.  To include more information  in my report, I developed the below query to add to the data model.

Query for matching fields

Once I added the query, “OE Transactions” highlighted below, into the data model, also shown below, I created relationships between the “OE Transactions” table to other dimensions.  Relationship is like a super VLOOKUP function.  For example, after I linked “OE Transactions” to the “Customers” dimension using the “Customer Number” field, as hightlight below, I can run the sales analysis margin by customer group, geography and etc.  After I linked the table to the time dimension, I can run the sales analysis margin by week or holiday.  Also, once I linked the table to the Item demension, I can run the sales analysis margin by item segements, category and etc. You can continue this as needed.

Sage 300 ERP Sales Analysis Reporting using PowerPivot: Creating relationships

With the data model in place, I created the Sales Analysis report to analyze the gross profit using actual cost by dragging and dropping the cells I choose.   Highlighted in red, is the actual cost where the COGS is 0.  Then, I can add additional calucations to the data model to continue improving the report.  For example, I calcuated the average selling price (ASP) by taking the price before the discount, as shown in the last column in the screenshot below.

Sage 300 ERP using PowerPivot: Sales Analytics Report

As you can see, the Sales History Report below produces the same results as compared to the Sales Analysis Report shown above.

Sage 300 ERP and PowerPivot: Sales History Report

I hope you found this blog 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 and grasp the look and feel.  

After completing additional blogs in this series, I will re-visit this report once again.  Be on the lookout for an overview of purchasing.

Related Content in this Series

Tags: Sage 300 ERP, Sales Analysis, PowerPivot

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 Consultation

Related Content in this Series

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

Sage 300 ERP (Accpac) AP Aging Report Using PowerPivot

Posted by Tony Zhang on Wed, Sep 18, 2013

Following my previous blog about creating AR aging reports using PowerPivot in Excel, I’d like to cover AP (Accounts Payable) in this blog.

The Excel data model for AP aging is similar to that of AR. The caveat is, unlike AR where you can use an invoice and/or document  number to join the invoice and receipts tables, in AP you have to combine vendor and invoice to join the invoice and payment tables since you could potentially have the same invoice number from different vendors.

Let’s take a look at the out-of-the-box AP aged payable report first. As the screenshot below shows, it is very sophisticated report.

Sage 300 ERP AP Report

Sage 300 ERP A/P Aged Payables Detail Report

When you use an Excel data model using PowerPivot to this table, you get the benefits of flexibility to let you look at things such as aging bucket and filter by a host of different factors. You also get an improvement in the report performance. As circled in the screenshots below, users can edit or add the aging bucket as needed. In the same worksheet, there is a statistics by document types update when you change the Cut-Off year and period.

PowerPivot Table for AP Aging Using Sage 300 ERP Data

 

AP Graphs and Statistics from PowerPivot Using Sage 300 ERP Data

Such a report can be uploaded to Office 365 and shared with others in your organization. In this example, I shared it with everyone.

Click here to get a static version of the report to get a sense of the look and feel. However, you cannot interact with the slicers, etc. from this version.

If you are interested in playing with the report, you can download a copy from this link. Please note, this report is developed in Excel 2013 Professional Plus and you need the same Excel version to interact with the worksheet. Also note that this worksheet is protected, so you'll be unable to modify it or connect with your Sage 300 ERP instance.

If you would like to get an unprotected version of the worksheet and/or connect to 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 Consultation

Tags: Sage Accpac, Sage 300 ERP, AP, accounts payable, AP aging

Sage 300 ERP (Accpac): OE Aged Orders using PowerPivot

Posted by Tony Zhang on Fri, Sep 13, 2013

This blog is a continuation on the Sage 300 ERP business intelligence journey using PowerPivot. This article covers OE Aged orders report, which can produce the same results as the out-of-the-box Sage 300 ERP O/E Aged Ordesr report, but with additional flexibility.

According to Sage, the O/E Aged Orders Report “lists all incomplete orders and their relative ages in the system. You can list all or a range of orders by order number, customer number, or currency (in a multicurrency system). You can select three aging categories. A fourth column prints orders that have been outstanding for more than the number of days in the last aging category.”

Sage 300 ERP OE Aged Orders Report

And here is the sample output:

Sage 300 ERP OE Aged Orders Output

Using PowerPivot, the same result can be produced as shown below:

PowerPivot OE Aged Orders Report

Now, for the magic of PowerPivot! After the data model is built and data returned to Excel, now you can add slicers to filter data by Salesperson, Territory, Ship-To Location, even the Ship-To Location’s optional field – UPSZONE, as highlighted below. And noticed the report is sorted by customer this time.

OE Aged Orders Report Sorted by Customer

With very little effort, the report can now be ordered by Item, which could be used by warehouse supervisors to plan the inventory.

PowerPivot OE Aged Orders Report sorted by item

 

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 Sales Analysis and Power BI, along with Purchase Analysis.

Stay tuned...

 

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 Consultation

Related Content

AR aging reports blog

AP aging reports article

Cash flow forecast blog article

Inventory control article

Dynamic ABC Analysis blog

 

 

Tags: Sage 300 ERP, OE Aged Orders, PowerPivot