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: Inventory Costing Automation and Day End Processing

Posted by Chris Firra on Tue, Oct 15, 2013

Businesses that use Sage 300 ERP for managing their inventory and sales orders face a question of how and when to process the costing aspects of their inventory transactions.  In Sage 300 ERP, inventory quantities (on-hand, on P/O, on S/O, and committed) are updated as soon as a transaction is posted, however, costing has typically been aggregated into a once-a-day batch process. The application has always had a user interface called DAY END PROCESSING residing within the Inventory Control Periodic Processing collection that is used to perform a large number of processing functions.  Those functions include:

  • Calculating costing data for all inventory-related transactions that have occurred since the last Day End Processing.
  • Producing G/L journal entries from the transactions processed.
  • Updating Inventory Control Posting Journal.
  • Updating Inventory Control Statistics
  • Activating and posting future sales and purchase orders that have reached their order date.
  • Removing quotes and purchase requisitions with expiration dates that have been reached.
  • Updating sales commissions
  • Creating Accounts Receivable invoice batches from posted Order Entry transactions.
  • Updating statistics and history in Order Entry and Purchase Orders.

Sage 300 ERP Day End Processing

As the name implies, Day End Processing was designed to be run at the end of a business day.  However, many companies require access to inventory values on a more frequent basis, and consequently run Day End Processing two or more times a day.  This is particularly true for companies with 24-hour operations.  In response, Sage introduced the option of performing the costing function upon posting each inventory transaction.  They managed to do this without locking up or negatively impacting other users that were entering and posting inventory transactions. The option is configured within the Inventory Control Options Costing Tab, which also allows for the option of concurrently creating the related subledger transactions.

Sage 300 ERP: Day End Processing with each inventory transaction

When costing during posting is enabled and the G/L Integration configurations are configured to create G/L Transactions by “Creating and Posting a New Batch”, the General Ledger inventory balances are effectively maintained in real time.

Sage cautions that if you cost inventory during posting, sales order and purchase order posting will naturally take longer.  On the flip side, Sage development guru, Stephen Smith, argued in a 2011 blog article that the delay during posting is minor.  Nevertheless, for many users whose primary function is to enter or process sales orders, the additional delay is not acceptable. 

The impact of the “Cost on Posting” performance penalty is a function of the number of order entry users and the number of inventory transactions processed.  I believe that companies should at least try out the “Cost on Posting” option to determine whether posting times are acceptable.  If the option is found to be inefficient, companies can revert to costing during day end processing.  There are other alternatives to obtaining frequent inventory value updates.

One such excellent alternative is to utilize Process Scheduler, an application from 3rd party developer, Orchid Systems.  Process Scheduler can be configured to automatically run Day End Processing at pre-set intervals, multiple times per day. 

Orchid Systems Process Scheduler with Sage 300 ERP

Additionally, Process Scheduler can automate other system processes, such as posting all subledger and General Ledger batches, so that G/L balances are completely up-to-date upon completion.  Even further, after Day End Processing and posting of all batches is completed, Process Scheduler can kick-off daily reports.  The amount of labor and frustration saved each day can be enormous.

If you would like to learn more, let us know.  We’d be happy to help you decide what’s best for your company.

Free Consultation

Tags: day end processing, Sage 300 ERP, inventory costing, Orchid Systems, process scheduler

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

Troubleshooting Sage 300 ERP Issues with RVSpy

Posted by Kevin Yu on Thu, Oct 10, 2013

When troubleshooting issues with Sage 300 ERP or integrated 3rd party applications, a great tool to use is RVSpy.

To run RVSpy, go to Start > All Programs > Sage > Sage 300 ERP 2012 > Tools > RVSpy. Below is an example of the RVSpy window:

RVSpy Window Example resized 600

RVSpy is very useful because it records all API calls to the Sage 300 ERP views. You can choose to output to the RVSpy window or write it to a file. When outputting to the RVSpy window, I like to change the number of lines to 9999 so have a higher likelihood of capturing any errors if they do not occur at the end.

RVSpy4 resized 600   RVSpy

Be sure to turn RVSpy Off when the error occurs.

Turn RVSpy Off

By doing so, the problem will be near the bottom of the log file.

Please note that while RVSpy is On, Sage 300 ERP processes will run significantly slower. If you are able to determine exactly when an error occurs, you can leave RVSpy off and turn it on right before the error happens to speed up the process.

Want more tips and tricks like these? Subscribe to the BTerrell Group blog (in sidebar on left).

Tags: Sage 300 ERP, RVSpy

A Brief Sage 300 ERP Retrospective

Posted by Chris Firra on Wed, Oct 02, 2013

Sage 300 ERP, formerly known as Sage Accpac ERP, has a long history that is older than even Sage Software (which was founded in 1981).  The software's origin can be traced back to 1976, when Don Thompson, Ted Comfoltey, Keith Wales, and Norm Francis formed the Basic Software Group.  The original product, marketed as Easy Business Systems, was first designed for the CP/M operating system, the original dominant operating system for microcomputers.  Here's a short recap of what happened afterward:

1983

Easy Business Systems ported to MS-DOS following the introduction of the IBM PC in 1981

1985

Computer Associates acquires Easy Business Systems and allows it to operate as an independent business unit.

1987

CA/Accpac International rebrands the accounting software product as Accpac Plus, and it becomes the dominant small business accounting software in Canada and one of InfoWorld's 100 top-rated products.

AccPac Plus Accounting 1987      AccPac Plus Account 1987 Screenshot

1994

The first Windows version is released as CA-Accpac/2000, designed to utilize a client-server deployment.  The early versions  are developed in COBOL and proprietary Computer Associates development tools, and feature a macro creation tool.

Computer Associates Accpac/2000 1994

2001

CA/Accpac International releases version 5.0 rebranded as Accpac Advantage Series.   The release introduces a multi-tiered architecture with a core business layer developed in C and a user interface layer developed in Visual Basic.  This hastens the development of 3rd party applications and custom interfaces.

Accpac Advantage Series 2001    Accpac Advantage Series Logo 2001

2002

CA/Accpac International announces Accpac Online, a pioneer in “cloud” and subscription based accounting software.

Accpac Online Logo 2002

2003

CA/Accpac International introduces Accpac CRM, which later becomes Sage CRM, a fully-integrated CRM solution that runs completely in a browser.

2004

Sage Software acquires Accpac subsidiary from Computer Associates

Accpac box by Sage Software

2006

Sage releases the rebranded Sage Accpac ERP  Version 5.4 with over 267 new features and enhancements.

Sage Accpac ERP 2006

2008

Sage releases Sage Accpac ERP version 5.5 with performance advancements and new IFRS compliance.

2009

Sage releases the slightly renamed product, Sage ERP Accpac Version 5.6 is released as a bundled Suite that includes Sage Intelligence Report Manager.

Sage ERP Accpac Version 5.6 2009

2010

Sage introduces Sage ERP Accpac version 6.0 and the development of new user interfaces based on the Google Web Toolkit and SDATA.

Sage ERP Accpac v6.0 2010

2012

Sage releases the rebranded Sage 300 ERP 2012 release that builds upon Sage’s web initiatives and responds to customer requested functionality.

2013

Sage 300 ERP 2012 Product Update 1 includes new features like Company Colors and enhancements in both Payroll and Inventory Control modules.  Additionally, Sage introduces a new Sage Intelligence Designer Add-In.

Coming Soon

Sage 300 ERP 2014 is anticipated to be released by the end of October, 2013.  Sage 300 ERP continues to evolve to meet the challenges faced by SMBs, with emphasis on deployment flexibility, connectivity, and fast access to critical information.

 

BTerrell Group has been helping customers in Texas, Arkansas, Louisiana and New Mexico implement Sage 300 ERP/Accpac for over 20 years. We've got a team of experienced ERP professionals who love to help customers solve business issues with technology. Let us know how we can help you!

Yes! I Want a Demo  of Sage 300 ERP

 

Tags: Accpac, Sage 300 ERP