BTerrell Group Blog

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

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

Sage 300 ERP (Accpac): Using PowerPivot for Inventory Control

Posted by Tony Zhang on Fri, Aug 16, 2013

In my previous blogs, I shared PowerPivot solutions for Sage 300 ERP (Accpac) financials (AR, AP and Cash Flow Forecast). Starting in this blog, I’d like to tackle challenges from the operational side, starting with Inventory Control.

If you manage inventory, you might have developed and used KPIs (Key Performance Indicators) such as Inventory Turnover, Turn/Earn Index or Gross Margin Return on Inventory Investment (GMROII). GMROII is particularly helpful for retail business because “Retailers usually drive their business based on sales or margin. In a retailer where budgets and bonuses are based on sales, employees often achieve that by lowering the margin or putting too much stock in their stores. A high GMROII indicates a good balance of sales, margin, and inventory cost.”

In the Sage 300 ERP world, you might have used the I/C Slow Moving Items Report which “lists items that are not selling well, according to the criteria you select when you print the report.” The screenshot below shows the input to the report:

Sage 300 ERP: I/C Slow Moving Items Report

 Here's what the output looks like (below):

Sage 300 ERP: the I/C Slow Moving Items Report Output

You might also use the I/C Overstocked Items Report which calcutes the Annualized Trun and Earn Ratio.

Both reports provide useful information with numerous criteria to choose from. Still, you might like to analyze inventory using Microsoft Excel to be able to further manipulate the data and provide your own customized reports.. If that is the case, you might like to check out the sample in this blog which calculates the Turnover, Turn/Earn Index and GMROII. To keep the blog short, I am covering the Turnover calculation in detail, but only show the results of the Turn/Earn Index and GMROII calculations.

The first step is to define how to calculate those KPIs. In this example, I follow the turnover definition used by Sage 300 ERP which is to use quantity instead of cost of goods sold.

YTD and period-to-date turnover definitions

Accordingly, I created measures/calculated fields in PowerPivot:

Excel Calculated Fields

Excel Calculated Fields - Turnover YTD

The Excel PowerPivot report looks like this. I highlighted the calculated Turnover which had the same results from Sage 300 ERP.

Excel PowerPivot Turnover Report from Sage 300 ERP data

With Excel, I gain the additional flexibility to be able to roll up the turnover calculation to the  Item or Location level. I can define the KPI to present the Turn/Earn Index status. In this example, the KPI target is defined as an absolute value, but you could use a calculated field like last year’s Turn/Earn Index.

Excel KPI Turn/Earn Index Status

Moreover, I can change the period and get the inventory balance () for the selected period and the corresponding Turnover. I highlighted the quantity available at the end of May 2020 in green, and the actual quantity available in yellow.

Inventory Balance or Quantity Available Reporting

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 include ABC Analysis for Sales and Inventory Cost, and 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

See AR aging reports blog

See AP aging reports article

See Cash flow forecast blog article

Tags: Sage 300 ERP, inventory control, PowerPivot, Power Pivot, Turnover, Turn Earn Index, GMROII