BTerrell Group Blog

Tony Zhang

Recent Posts

Getting the Most Out of Sage 300 ERP data with Spreadsheets

Posted by Tony Zhang on Wed, Mar 26, 2014

Spreadsheet AnalystOver the years, BTerrell Group has helped our clients improve their productivity using software systems and financial reporting. One of the products we really like is Spreadsheet Analyst (formerly known as Sage Accpac Insight), which is a terrific complement to Sage 300 ERP. Not only does this product help meet enterprise reporting requirements very well, but brings a lot of value to our customers. To help customers get the most value out of Spreadsheet Analyst, our own Chris Firra  has developed an in-depth training curriculum that has helped numerous customers along the way.

Spreadsheet Analyst handles the financial reporting in Excel nicely and is especially good for multiple companies with large amounts of data – the typical  in enterprise reporting – thanks to the data warehouse it provides. Feel free to call BTerrell Group to discuss how we might leverage Spreadsheet Analyst to help you improve your business processes, or to provide the following training to your employees.

Spreadsheet Analyst Training Agenda

Session 1 - Spreadsheet Analyst Report Authoring I.

  • Spreadsheet Analyst concepts and terminology
  • Spreadsheet Analyst report and template design
    • Creating new reports from templates and other new reports
    • Defining Row Groups and Columns
    • Controlling report behaviour using Sheet Order By and Column Order By functions
    • Report formatting functions
  • In session exercise
  • Independent exercise

Session 2 – Spreadsheet Analyst Report Authoring II.

  • Sorting and grouping techniques
  • Creating custom drill-down sheets
  • Using named ranges in report design
  • Using pivot tables in report design
  • Workbook navigation and controls
  • In session exercise
  • Independent exercise

Session 3 – Spreadsheet Analyst Report Authoring III.

  • Best practices for report and version control
  • Report distribution methods
    • Defining Control Sheets for distribution
    • Using Workbook Generator
    • Configurations for email distribution
    • Configurations for file publication and report archival
  • In session exercise
  • Independent exercise

Session 4 – Spreadsheet Analyst Data Warehouse.

  • Understanding the data warehouse architecture
  • Managing Schedulers
  • Managing and modifying Data Filters
  • Methods for adding new tables
    • Master data
      • Accounts
      • Roll-ups
    • Transactional data
    • Calendars
  • In session exercise
  • Independent exercise

Session 5 – Importing data and Advanced Authoring.

  • Using Write-Back workbooks to import data
  • Developing budgeting templates and generating budget write-back workbooks
    • Budgeting functions
    • Spread methods
  • Optimizing report performance
  • In session exercise
  • Independent exercise

Session 6 – Capstone Exercise.

  • Joint exercise

 Contact Us


Tags: Sage 300 ERP

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

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

Sage 300 ERP Payroll: Creating Dashboards Using PowerPivot

Posted by Tony Zhang on Fri, Feb 14, 2014

If you use Sage 300 ERP Payroll, you might like to have a dashboard to show how many years your employees have worked for your company,and in which place and position, like shown below. In addition, you also might like to analyze the payroll date in Excel as well, like the example in this link.

Analyze Payroll with PowerPivot1 resized 600

One day, one of my clients had an issue with the US Social Security tax calculation. With the help of the PowerPivot data model, I can quickly spot the problem across the year and all employees, as highlighted in the following screenshot.

Analyze Payroll with PowerPivot2 resized 600

Like to have such a report on hand to improve your productivity? Don't hesitate to contact the experts at BTerrell Group!

Tags: Sage 300 ERP, PowerPivot