BTerrell Group Blog

Power BI: Right Time and Right Place with Sage 300 ERP Data

Posted by Tony Zhang on Mon, Mar 17, 2014

We all know the adage: being at the right time at the right place and with the right people is important to success. How do we translate this adage into the language of business intelligence?  A good data model needs to have dimensions to represent time, location and people.

Power BI includes Power Map, a tool that helps address these requirements. Power Map can plot more than a million rows of data in 3-D, visually on Bing maps from an Excel worksheet table or the Data Model in Excel, and helps to gain new understandings by allowing you to visualize your data in geographic space and seeing time-stamped data change over time. As shown in the following example, as the time plays, the gallon quantity sold is plotted by location.

Power Map

Last but not the least, Power Map helps to capture screenshots and build cinematic, guided video tours that can be shared broadly, engaging audiences like never before. Tours can be exported to video and shared that way as well. The following is an example that visualizes the quantity sold across the country and over time, by leveraging the time and location dimensions in the Power BI data model, connected with Sage 300 ERP data.

In summary, Power Map helps you to map data, discover insights and share stories in the dynamics of time and places.

Willing to turn your great idea into insight to your organization using the Power BI platform? Don't hesitate to contact the experts at BTerrell Group for help in making that happen!

Tags: Sage 300 ERP

Can QuickBooks automate inter-company transactions?

Posted by Brian Terrell on Mon, Mar 10, 2014

In the last 4 weeks, I have visited with two prospects frustrated by QuickBooks' inability to manage multiple related companies.  Both prospects provide financial management services to ten or more related entities, and the relationship between entities leads to many inter company transactions.  Quickbooks complicates multi entity accounting engagements because it was just not designed to solve these types of accounting challenges.

Inter Company TransactionsHow does QuickBooks make life tough for accountants who must account for many related entities?  For example, QuickBooks does not account for more than one company per database file.  Therefore, accountants waste lots of time by logging in and out of each company many times a day.  Imagine having 75 related entities and entering transactions in some or all of these on a daily basis.  Because they are small companies (or they wouldn’t be using QuickBooks at all), one doesn’t stay in each company very long before it is time to close that company’s file and open another.  Over the course of a day or a week or a month, accounting departments waste a lot of time just opening and closing data files.

Also, QuickBooks does not automate update and balance due to/due accounts.  Many related companies share transactions, and these create inter company loans on each entity's books.  For example, one company may pay a bill on behalf of another company (or several other companies), which then requires the corresponding loan accounts on all entities’ trial balances be updated and balanced.  Accountants must log into each of the affected companies to make these entries and analyze the inter company accounts.  When someone misses just one of these transactions, the discovery usually occurs long after the fact when the details of the transaction are forgotten.

"QuickBooks may lock accountants into the archaic role of historian and out of the much more progressive and valuable role of trusted advisor"

Finally, QuickBooks does not report across multiple company databases.  And, as mentioned above, the program cannot accommodate multiple entities’  data in one data file.  So, how does one consolidate two or more entities?  Most use Excel for this task, which requires a lot of exporting or printing to a file followed by significant cutting and pasting.  In addition, the required elimination entries in the last phase of the reporting project require lots of mental energy and provide lots of opportunities for error.  Once the accountant completes the consolidating statement, he or she must begin the distribution process which invariably requires standing at the copier/collator/stapler and/or converting finished Excel spreadsheets to PDF before emailing those off to the appropriate distribution list.

Most can handle these tasks for two or three companies.  However, if one accounts for much more than this, then he or she will have a major daily and month end administrative task that has very little to do with debits and credits.  Because they have less time for analysis, QuickBooks may lock accountants into the archaic role of historian and out of the much more progressive and valuable role of trusted advisor.  Trusted advisors guide business owners and decision makers toward decisions leading to more and greater profits whereas historians suffer through copying and collating yesterday’s news.

Sage 300 ERP and Intacct Cloud Financials remove every single frustration of accounting for multiple related entities.  To find out more about these excellent applications, please contact our office for a free consultation!

Get Free ERP Consultation


Tags: Sage 300 ERP, Intacct, quickbooks

Windows XP and the Meaning of Life (or End of Life)

Posted by Chris Firra on Fri, Mar 07, 2014

Windows XP

You may have read or heard by now that Microsoft has announced that the end of life for both Windows XP and Office 2003 officially will be April 8, 2014.  Both of these products became mainstays of business computing, and Windows XP, in particular, has had an astounding lifespan (for an operating system) of nearly 13 years.  Despite the fact that Microsoft released 3 subsequent major versions of its Windows operating system, (Windows Vista, Windows 7, and Windows 8), WinXP had continued to hold more than 38 percent of the operating system market share for personal computers as late as a year ago.  But now, this king of PC operating systems is almost dead…

Or is it?  Windows XP computers will not suddenly stop functioning on April 8.  So, what does end of life actually mean?  

RIP Windows XP

To begin, this means that Microsoft will no longer will support Windows XP.  Translation: technical assistance will no longer be available from Microsoft.  More importantly, there will be no more updates available (automatic or otherwise) to correct bugs or to plug security gaps.  Internet-connected machines running WinXP may be vulnerable to new threats.  Additionally, other applications from Microsoft, like Office, will not maintain compatibility with Windows XP.  It follows that software from all other vendors that rely on Microsoft products will follow suit.

It's not surprising that IT departments are accelerating the retirement of computers with Windows XP in favor of new machines with the newest operating systems, like Windows 8.1, and applications like Microsoft Office 2013.  We are even noticing replacement of what are often the oldest workstations in the building, those in the accounting department.  

A bit of shock and confusion usually accompanies anything unfamiliar, like a new computer.  (If you've used Windows 8, were you at first stumped by trying to find the start menu?)  Ultimately, this is usually at least partially offset by the benefits of new features, like Windows 8.1's Storage Spaces, the much improved File Explorer, and Office 365 OneDrive.  Yes, despite what you may have heard, Windows 8.1 is actually more stable, more secure, and more feature-rich than Windows XP.  There is no reason to hang on to WinXP, if you don't have to.

Windows XP3

The same holds true for the business and accounting applications that run on Windows operating systems.  Sage will soon release Sage 300 ERP 2014, which will feature full support for Windows 8.1 and Office 2013.  From all indications, it will be faster in a number of areas and bring many new features, which will be covered in future articles.  

So, if you are hanging on to the past, now is the time to join the future by transitioning from Windows XP and Server 2003, and then planning your upgrade to Sage 300 ERP 2014.  The best way to get started is to contact us today!

Contact Us

Tags: Sage 300 ERP

Power BI: Get the Most Out of Your Sage 300 ERP System

Posted by Tony Zhang on Fri, Feb 28, 2014

How much value you can get out your ERP system? It depends on how it is integrated with your business strategy and how it is being used to measure and improve the business process.

You have a strategy, a destination statement and probably a balanced scorecard. The next is about the execution and to keep on track of the goals set for financial, customer, internal business processes and learning and growth. Many times, you know what measure should look like and you know your ERP system has the data to feed to the measure. However, you could face challenges like being able to transform the data into useful information or distributing the information across the border of ERP. Here is where business partners like BTerrelll Group could step in and make you the hero by turning your idea into actionable dashboards and reports.

For instance, speaking of the improvement of the internal business process, here's one way that we can help Sage 300 ERP users using Power BI. Specifically, the following graph presents the inventory movement that helps to identify the overstock by category, item, product class, item-vendor, and location or any combination of these fields. The orange line below represents the inventory quantity balance, the blue line represents the purchase and gray is for sales. This shows that sales and purchases tracked each other pretty well, but the inventory appears overstocked across time. Digging further, when selecting category 67, as in the second graph, it shows the category start its inventory activities since 2010-07 and the purchase and sales keep growing, however, the inventory balance is managed at a relatively low level. So we learned it is well managed than other categories. And investigation can go on to help improve the business, actually the turnover calculated in another worksheet tells this as well.

Inventory Trend Analysis

Inventory Trend Analysis

What else you can get out of Sage 300 ERP? The following graph shows the distribution of 60K+ G/L postings across a day in the past three years done by over 30 accountants. This information can be used to help to identify a better time window for things like data warehouse data refresh and backup.

Posting Count by Hour and Minute resized 600

Willing to turn your great idea into insight to your organization using the Power BI platform? Don't hesitate to contact the experts at BTerrell Group!

Tags: Sage 300 ERP

What You Measure Is What You Get: Power BI for Sage 300 ERP

Posted by Tony Zhang on Mon, Feb 17, 2014

If you agree with the headline of this article, then you need some sort of Business Intelligence (BI) tool to help to build all kinds of measures and share them across your organization to help make informed decisions.

Love or hate it, Excel is the most used tool in the business world for data modeling and analysis. However, with Excel, you have to deal with challenges like version control, ETL (extract, transform and load) and visualization. Power BI is Microsoft’s answer to those challenges. In addition, there are many other exciting features like mobile access and Q&A user experience.

If you are a decision maker and happen to use Sage 300 ERP to manage your business, you might like to have something like what's shown below to share with your investors, to hold your line managers accountable and to have all users talking from a single version of the truth.

PowerBI for Sage 300 ERP

Given the value that all BI vendors claim to bring to your business, you likely still want to look at the price tag. Would Power BI be the cost effective platform to build all your measures and to share across the organization given a $40/month/user price tag?

What I’d like to point out is you don’t need to buy everyone a Power BI license. If the user just consumes the report and the file size is less than 10MB, the $7/month/user SharePoint online license will work. At minimum, you need at least one Power BI license to se tup things like manage data sources and schedule the data refresh as the screenshot below.

PowerBI for Sage 300 ERP

Willing to bring insight to your organization using the Power BI platform? Don't hesitate to contact the experts at BTerrell Group to help!

Tags: Sage 300 ERP