BTerrell Group Blog

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: accounts payable, AP, Sage Accpac, Sage 300 ERP, AP aging

How to File a TWC Unemployment Report Using Sage 300 ERP or Accpac

Posted by Brian Terrell on Tue, Apr 30, 2013

by Brian Terrell

I built my business on automation, and often say I enjoy helping clients do more work with less effort. And, I sometime flirt with over automation, which occurs whenever an automation costs more to develop that it returns in savings. In other words, over automation results in doing less work with more effort. I feel disgusted when I fall into the over-automation trap; however, it occurs as an honest and overly optimistic outgrowth of technology curiosity that has other, not-so-easily measured benefits. I assure you that filing the Texas Workforce Commission (TWC) quarterly state unemployment tax report using Sage 300 ERP (formerly called Sage Accpac) does not fall into the category of over-automation.

Sage 300 ERP provides an out-of-the-box state unemployment report filing function, but I never used it. Shame on me…it might be even easier. However, since I can execute my method in minutes per quarter without thinking, I'll wait to read some other person's blog to get up to speed on the out-of-the-box Sage function. Until then, I'll just recite what I have done for more than 15 years (times three companies times four quarters per year).

Step 1 requires obtaining a TWC login for the state commission's self service web site. Note that I can file on behalf of companies that I do not own if properly authorized, and this helps the managed payroll service provider, bookkeeper or CPA firm provide this service for a client.

After setting up my online account, I print Sage 300 ERP's Payroll Quarterly Wage Report by Employee to Preview and export to Excel. use the "data only" format, and I export directly to the application. After choosing the Format and Destination, accept all of the default selections in the export function:

Export screenshot resized 600

This causes the report to open in Excel, whereupon I delete the first row and move the column headers in the new first row (the row that becomes the first row after I delete the original first row) one column to the left (overwrite that column header in Column A entitled TXSUTA). This places the column headers above the correct column heading.

Delete all columns except SSN, Last Name, First Name, Middle Name, and Total Earnings. Total Earnings, in this case, represents quarter-to-date total wages. Texas asks only for these five columns. I then move First Name and Middle Name to be between SSN and Last Name using the cut and paste command applied to the entire column. Next, I add up the Total Earnings column and tie it to the original report, which I leave open in the Preview window for this purpose. If the total ties to the report, delete the total and delete the first row. We no longer need the column headings and we no longer need the check total.

Excel Spreadsheet TWC Sage 300 ERP

Save the file under an appropriate name in an appropriate location. Remember, the wage information for the entire company should be carefully managed. Log in to the TWC Employer Services web site, choose the correct Employer record (if managing several employers), and upload the wage information from the file. If there are any records containing zeroes in the Total Wages row, the site will ask if it can delete those records. Answer yes and tie in the results to the check total, which may still be open in the Preview window. Follow instructions for filing the report and remitting the payment.

Payment remittance can be made by credit card or by bank transfer. Alternatively, the website can prepare a remittance advice that I can then print to paper and mail in with my check. The online payment or check must be remitted by the last day of the month following the end of the payroll quarter.

22 years ago, when I first began to use the product formerly known as Accpac, I wrote a special report to print the TWC continuation pages using a dot matrix printer. If you remember what a "continuation page" is or even what a dot matrix printer is, then you are a veteran. choose my words carefully here, as I am a veteran, too! However, my time as a payroll veteran draws nigh…please look forward to an upcoming post on why I believe fewer and fewer SMBs will be doing their own payroll over time.

Tags: accpac tips, Sage Accpac, Accpac, Accpac ERP, Sage 300 ERP

Manage User Authorizations in Multiple Companies in Sage ERP Accpac

Posted by Meredith Gooch on Wed, Apr 04, 2012

CodePartners recently developed an application for Sage ERP Accpac that allows you to manage user authorizations in multiple Accpac companies from one centralized location. The applicaiton shows all the companies defined in Sage ERP Accpac, along with activated modules, and allows selecting the Accpac Security Group to be assigned to the user for each module in each company. When the changes are saved, the application updates all Accpac companies respectively.

Check out the demo video below. This is a fantastic tool that can definitely save time and money!

Contact us today for more information about this add-on for Sage ERP Accpac.

Tags: CodePartners, Sage Accpac, sage erp accpac, multi-company security Accpac, multi-company security settings

Five Reasons to Choose Sage ERP Accpac (Soon Called Sage 300 ERP)

Posted by Meredith Gooch on Mon, Feb 13, 2012

Sage ERP Accpac (soon to be called Sage 300 ERP) is not for everyone. I know this sounds crazy coming from a company who believes in the product (otherwise we wouldn't have worked with the product for 20+ years...!), but it is true. Some companies are just too small for the breadth of Sage ERP Accpac and some large companies need an enterprise level solution like SAP. For many mid-market companies though, Sage ERP Accpac is precisely what they need. Here are five reasons we believe mid-market companies should choose Sage ERP Accpac (Sage 300 ERP):

One Unified Solution
Accurate, readily available information is imperative to driving daily business decisions. Unifying all of your data into one solution creates a true view of your business which is vital to growth and profitability.

Superior Architecture
Sage ERP adapts quickly as your business requirements evolve, and it is technically designed to provide your business with more control and flexibility. You can deploy all or part of the functions according to your requirements, one step at a time.

  • Scalable editions support 1-250+ users
  • Deployment neutral: on-site or on-demand (hosted cloud computing)
  • Database Options: Pervasive.SQL, Microsoft SQL, or Oracle.

Global Reach/Local Touch
An international presence is not only important for modern enterprises, it is essential. Sage ERP Accpac equips you with the tools required to maintain international currency standards and eliminate the complexities of multinational commerce.

Collaborative Ecosystem
Sage customers receive unmatched range of expertise, support, training, and collaboration through a unique ecosystem that includes Sage staff, programs, and resources; your local partner (BTerrell Group), training, and software user communities.

Proven, Reliable, and Experienced Available Functionality and Capabilities

  • Accounts Payable
  • Accounts Receivable
  • Advanced Budgeting
  • Alerts
  • Bank Reconciliation
  • Business Intelligence
  • Check and Form Printing
  • CRM
  • Document Management
  • EDI
  • EFT
  • Fixed Assets
  • General Ledger
  • G/L Consolidations
  • Human Resources
  • Intercompany Transactions
  • Inventory Control
  • Language Overlays
  • Manufacturing
  • Multi-Currency
  • Online
  • Payroll
  • Project and Job Cost
  • Return Material Authorization
  • Sage Sales Tax
  • Sales Analysis
  • Service and Maintenance Management
  • Transaction Analysis and Optional Field Creator
  • Warehouse Management

Still unsure? Give us a call today to talk through your business challenges and discover whether Sage ERP Accpac is the right move for you.

Tags: Sage Accpac, Sage ERP, Sage 300 ERP, reasons to choose Sage ERP Accpac, reasons to choose Sage ERP

Important Notice - U.S. Payroll Tax Update

Posted by Meredith Gooch on Tue, Mar 15, 2011

Important updates to tax withholding and magnetic media changes will be required for some states, along with a new federal Form 941 that will affect some businesses. Sage incorporated these changes into the Sage ERP Accpac March 31, 2011 updates, which will be available to download as of March 24, 2011. The following versions will be available:

US Payroll Customers: version 5.5M, Version 5.6I, Version 6.0D

In order to download the update, you must have a current Payroll Update Plan (PUP) or be a Priority Software Support (PSS) subscriber as of the tax table effective date. PUPs are only available via download.

If you hae any questions about this or any other Sage Accpac-related inquiries please contact us.

Tags: Accpac, Sage Accpac, US Payroll Tax Update, Accpac Payroll update, Accpac tax update, Accpac payroll, Accpac ERP