Sage 100 Consultant Tip – How to Create Calculations with Crystal Reports

Question:  Mike, we own Sage 100 (formerly MAS90) and use Crystal Reports. How can we do a summary calculation at the bottom to add up multiple lines on a Sales Order using Crystal Reports. We follow your Sage 100 Consultants Tip blogs and find your series of tips on Crystal Reports very useful!

Answer:  The Sage 100 Crystal Reports calculation feature has many applications.  In this case, we’ll review the 3 steps to add multiple lines in Sales Order.  If you think you are not taking advantage of the power of Crystal Reports, please add your comments at the end of this blog.

NOTE: We advise that you consult with your certified Sage 100 ERP consultant before making any changes to your Sage 100 ERP system. If you don’t currently work with a Sage 100 consultant, contact us, call 760-618-1395 or email us and we are available to help. Sign up for more helpful Sage 100 consultant tips & tricks below!

Sage 100 Consultant Tip: 3 Steps to Create Crystal Reports Calculations

STEP 1 – In Crystal Reports, use the Running Totals Fields from the field explorer. On the Crystal Reports field explorer right click on Running Totals Fields and select New.

Sage 100 consultant tip

STEP 2 – Enter in the Running Total Name, from the left side scroll down and select the field to summarize and click it to the right. You can leave the defaults, and click OK.

Sage 100 consultant tips and tricks

STEP 3 – The new Running Total Field will now be available and you can insert into the report, please note you must insert the field into a footer for the Summary calculation to work.

Sage 100 tips

We hope this was helpful. There are many other applications to help save time throughout Sage 100 for Crystal Reports calculations. If you have any questions, please contact us, call 760-618-1395 or email us and we will be happy to help.

Check out other related Sage 100 Crystal Reports blogs:

Written by Mike Renner:  Partner of WAC Solution Partners – Greater Los Angeles, a Sage 100 ERP consultant and support provider based in Indio, CA.

Mike is an expert on ERP and fund accounting and supports Abila Fund Accounting and Sage 100 ERP with over 25 years in the accounting software industry. Mike is also a Sage Certified Trainer and a recognized leader in the design, implementation and support of ERP systems, including Sage.

Specialties:  Sage 100 ERP (formerly Sage MAS90 / MAS200), Abila MIP Fund Accounting™, Acumatica, QuickBooks Pro Advisor

 

Abila MIP Fund Accounting™ Consultant Success Story with CVEP

October 26, 2017/in Abila MIP Fund Accounting™, Non Profit /by Mike Renner

Coachella Valley Economic Partnership (CVEP) is a nonprofit economic vitality organization devoted to attracting, retaining and expanding business and developing a high-skilled workforce to enhance the economy and quality of life in the region.

Albia consultant nonprofit MIP.pngWAC Solution Partners Greater Los Angeles – Helping Businesses to Grow Effectively

As providers of accounting software, implementation and support, we offer a wide range of solutions designed to fit businesses and non-profits of all sizes. Our combination of products and services allow you to better manage your accounting and to make insightful decisions about your company’s future.

EXPERTISE WITH LOCAL ROOTS AND NATIONAL REACH

This is a success story that embraced persistence, partnership and always working with and looking out for our customers best interest.

We are very involved with the Coachella Valley business community. In 2014, a local CPA named Kelli Cox, was working with CVEP on their financial statements. CVEP had laid off their Controller. Kelli didn’t have time to do many of the accounting tasks they were requiring. She knew we were Abila MIP Fund Accounting™ Certified Consultants and brought us in to meet with Lesa Bodnar, Chief of Staff of CVEP.

Kelli recommended us to take over many of the accounting tasks due to our knowledge and expertise. CVEP also wanted additional training on Abila MIP Fund Accounting™ software for their staff. Mike Renner trained the staff on General Ledger, Accounts Payable and Accounts Receivable. Laurie Renner went onsite weekly to work on the Abila MIP Fund Accounting™  software, to make accounting entries and at the end of the month reconciled their bank statements. She also worked with Kelli to make sure the financial statements and board reports were ready for the board meetings. Laurie has been working with this client ever since. It’s an ongoing success story for 3 years now.

Some of the comments from the Chief of Staff – Lesa Bodnar are:

How to Install a Workstation Set up Sage 100, 2016

Question: We are a Sage 100 2016 user, how can we install Workstation setup on one of our networked computers?

Answer: First of all on the server that the Sage 100 2016 program is installed you will need to do a Share of the folder above the MAS90 folder. Example parent folder of the MAS90 folder might be called Sage 2016. You will want to right click on that folder, share it and give full permissions for the users that will be accessing Sage 100.

You will then log onto the Workstation that you will be running Sage 100 on and do a mapped drive to the parent folder, such as M:\Server1Sage 2016 which should then give you access to the folder. You will want to click down to the MAS90wksetup folder and then run the Setup.exe program as an Administrator by right clicking on the program icon.

blog1

The program will run on the screen as shown below:

blog2

You will click the Next button:

blog3

Click the next button to run as Complete:

blog4

Click the Install button:

blog5

You will next want to install the Wks Update Standard, right click and install as Administrator, and follow installation until finished:

blog6

blog7

Once the program finishes you can find the new Sage 2016 icon in the Program files and can copy to the Computer Desktop:

blog8

If you have questions please contact us at www.caserv.com or 760-618-1395. Please stay tuned for more helpful Sage 100 ERP tips!

Sage Alerts and Workflow can change your life

Sage Alerts & Workflow is a Sage add-on product that allows users to generate queries depending on the information that is being tracked. For example, if a user wanted a list of open invoices from clients that are over 30 days due and wants the list generated once every 15 days, Sage Alerts can generate the list and send it to one or multiple departments via email automatically. Users can also customize the categories that they wish to include in the query. For example, they can include the name or ID code of the sale representative of a specific transaction, the payment code method that was used for the transaction, and which pricelist was used, just to name a few.

Currently I am working with a client who uses Sage Alerts. One of the queries that they use includes all inventory items whose quantity on hand minus the quantity on any current sales order is less than or equal to 0. Another query, which is scheduled to run every Monday at 8AM, is to gather a list of clients who have open invoices that are over 30 days old. For this same client, I am currently trying to create a query that will compare sales from this current year to prior year based on a specified date range.  Then the query will identify those customers whose incoming sales revenue compared to the prior year is less than $3,500.00.

Sage Alerts & Workflow is also compatible with Customer Relationship Management (CRM), Human Resource Management Systems (HRMS), Enterprise Resource Planning (ERP) software, to name a few.

For more questions regarding the acceptance of credit cards through your Sage ERP software, contact your WAC Solution Partner for more information.

Written by Stephanie Piller, WAC Solution Partners- Midwest.

How to use Sage CRM correctly for the simple tasks.

One item I usually feel is one of the most important and most rudimentary parts of a CRM is capturing the “communications”.  When I talk about communications with regard to Sage CRM, I mean all the things you as a user should be entering to track the interaction with your client.

Some examples include, follow up calls, document dropping any proposals or documents regarding the client, filing key email communications, tracking birthdays or anniversaries, track thank you cards sent.  Anything relevant that takes place between you and the client should be tracked.

How do you properly document these items in your CRM.

7K0A0032DO:

  • Enter the communication on the day they were scheduled, sent, completed, emailed etc. Don’t get me wrong, feel free to schedule something for yourself of which you may not get to until a week later.  But then change the date in the CRM to the completed date so it reflects accurately.  Sage CRM tracks tasks with a start date and a due date.  This is very useful for projects or things that have a drop dead date to be completed.
  • File emails but not ALL. In outlook your emails have a thread.  You should file the most relevant (usually the last one in the series) which will contain the thread of the previous emails.  No need to file 12 separate emails of people saying thank you and I’ll send it right over.  Just file the relevant ones that have the information that would be helpful to anyone reviewing or managing this client account.
  • Schedule follow ups immediately or they will get lost in your mind or notes. At least if they are scheduled in the CRM, they will be staring you in the face until you get to them.  In Sage CRM, these notes turn RED when they are“overdue.”.  You don’t want too many red items because they will tend to creep up on you.

7K0A0021DON’T:

  • Enter Nothing! Don’t do this.  It helps no one, especially you when it comes to maintaining your service to a client.
  • Put it off- if you don’t enter the communication or follow up details immediately ( or as close as possible), we all know it will not get done
  • File all emails. As mentioned above, you do not need every single communication you have had with someone to be saved for review.  This could cause the clients communication diary to be too cumbersome to sift through for important information.

Client Story:

I have been working with a client for the past few years with their Sage CRM product.  They have grown in usage of this product yet some things were not as important to them as others.    When a new employee came on, I trained her on all the basics of CRM and how to use it.  I always train with the company in mind and make sure to train the way they are using it or should be using the CRM based on my history with them.

The new employee started a bad habit immediately of which to this day she is still using.  I’ll call her Sherry.  Sherry was in charge of follow ups on old customers who haven’t placed an order in X amount of time and/or cold calling for purchased lists.  We all know this kind of follow up takes a lot of patience and multiple reaches before anything MIGHT come of it.  Sherry decided to track the follow ups in one single communication.  For example she may call 15 times within one month and not get through to the customer.  Instead of logging 16 separate communications showing that she called at this time or this day, she decided to lump them into one communication.  At a glance it looks like she only tried one.  It doesn’t show the history of calls she is making.  On top of that, it is hard to report on amount of calls she is making per day, week, or month, if they are bulked.   Sherry was trained multiple times on the correct way, per her company, to log these calls yet it hadn’t changed in many months.

The best practices for any CRM user are the ones that work, can be reported on to show they work, and get results.  Use your CRM correctly but most importantly, USE IT!

For more information on Sage CRM, contact Kari-Ann Ryan, Certified Sage CRM

What’s up with Amazon tax?

Repost from Avalara

Almost every internet retailer asks this question at some point: “Do I have to charge sales tax?”

Answering the online sales tax question is far more complex than it used to be. A good point of reference is Amazon. You may have a far different business than Amazon, but many states have made it the focal point of new sales tax laws. Those laws impact many remote sellers; even possibly you.

 Amazon tax in 2016

Amazon currently collects tax on sales shipped to 28 states: Arizona, California, Colorado, Connecticut, Florida, Georgia, Illinois, Indiana, Kansas, Kentucky, Maryland, Massachusetts, Michigan, Minnesota, Nevada, New Jersey, New York, North Carolina, North Dakota, Ohio, Pennsylvania, South Carolina, Tennessee, Texas, Virginia, Washington, West Virginia, and Wisconsin.

Many of these states have enacted click-through or affiliate nexus legislation that presumes an out-of-state seller has nexus (an obligation to collect sales tax) when certain conditions are met. These laws are designed to get the big players, like Amazon, to collect and remit sales tax. However, the laws apply to many other retail and ecommerce companies besides Amazon. For example, click-through nexus policies require out-of-state sellers to collect and remit sales tax when they compensate residents for sales made via links on their websites. Under affiliate nexus policies, other ties to in-state businesses may trigger a sales tax obligation.

Determining whether or not you have nexus in a given state can be tricky because state policies vary; for example, in California, click-through nexus is triggered if a business generates more than $10,000 in in-state sales during the preceding four quarterly periods, while in Connecticut, it’s triggered if a business generates more than $2,000 in the same time frame. It’s best to consult with a tax advisor before entering new markets or expanding online advertising, as a mere click on a website may trigger nexus in a new state.

Amazon doesn’t collect tax in the remaining 22 states, most of which don’t have click-through or affiliate nexus policies. However, Arkansas, LouisianaMaineMissouriRhode Island, and Vermont have all enacted remote sales tax laws — variations on a theme that all work to create a sales and use tax obligation for many out-of-state sellers, including Amazon. In order to avoid triggering nexus in these states, Amazon no longer permits residents to participate in its associates program.

But you may have to comply in these states. The Vermont Department of Taxes stresses that retailers with no physical presence in Vermont are presumed to have nexus for sales tax if they have agreements with residents to refer customers that resulted in sales in excess of $10,000 in the previous year.

Perhaps the most interesting recent news is that Amazon will be required to collect sales tax in Washington, D.C. beginning on October 1. The company doesn’t have a physical presence in the District, and the District has not enacted click-through or affiliate nexus. So what gives? Neither Amazon nor the District will comment. It could be a voluntary collection on Amazon’s part — it’s made such agreements in the past, usually in states where it plans a future physical presence. And indeed, The Washington Post reports that Amazon is looking for a good spot for a brick-and-mortar bookstore in the nation’s capital. If you do business in the capital, you will want to stay tuned to see what the law means for you.

 The future of online shopping: stores without walls

In the past year, Amazon has opened brick-and-mortar bookstores in Portland, Oregon, San Diego, California, and Seattle, Washington. It will soon have stores in the Boston and Chicago areas, and CEO Jeff Bezos told shareholders in May that there are plans to open more, though he provided no details.

Amazon isn’t alone. Savvy e-tailers from Birchbox to Warby Parker are opening traditional retail stores. Smaller internet businesses are also experimenting with pop-up stores.

If you’re thinking of taking your business to new channels, keep in mind it could impact your tax obligations. The more brick-and-mortar stores internet retailers open, the more places they give themselves a physical presence and an obligation to collect sales tax.

 State and federal efforts to change nexus policies

A handful of states are instituting economic nexus policies. This means that remote sellers may have to collect and remit sales tax in these states simply for making sales there. Economic nexus policies in Alabama and South Dakota have triggered lawsuits, and both states are prepared to argue their cases before the United States Supreme Court. If this happens, the Court could overturn Quill Corp. v. North Dakota, the 1992 decision responsible for the physical presence precedent still followed today.

Meanwhile, federal lawmakers are striving to find a solution that will satisfy states and businesses when it comes to laws governing sales tax and remote sales. Four pieces of legislation are currently kicking around Capitol Hill: the Marketplace Fairness Act, the Remote Transactions Parity Act, the Online Sales Simplification Act, and the No Regulation without Representation Act.

Knowing where you have nexus and staying up-to-date on changing policies is crucial. Even though state laws vary widely, keeping tabs on Internet giants, like Amazon, can be a good way to see which way the tax wind is blowing. For the latest and greatest, check out where are we with federal online sales tax legislation?

READ NOW

Submitted by Avalara

Designing Financial Statements Correctly for use with Sage Intelligence Reporting

In all my years of working with financial information I have found two ways to hide information (something a business with integrity would never want to do).  The first is illegal which is to not actually report the information.  The second is legal and is to bury it with detail.  When there is too much data to review or the data is not summarized so it is easy to process, important information can be missed.

When I design income statements for my clients, I attempt to group all of the expenses into five to seven categories. It does not matter if the company has fifty accounts in their chart of accounts or five hundred or more.  By carefully grouping expenses into meaningful categories we can quickly review the income statement to find areas that require additional research.

Sage Intelligence Reporting and Microsoft Excel work very well for designing financial statements. Sage Intelligence helps a user create an income statement that has just a few categories, but at the same time allows for any or all categories to be expanded to show all the accounts contained in that category.

As recently as last month, my client, who is running Sage 300 2014 and I were able to find significant issues that needed to be immediately addressed.  In just a couple of minutes we were able to find the issue by starting with the summary view of the report and expanding it to see the detail.  By effectively designing the report, it was an efficient use of time to begin with the summary view and “work backwards” to easily find the problem.

Cleaning up AR transactions using Sage 300 Write-Off process

Companies sometimes have customer entries in Accounts Receivable that need to be cleared from the ledger because they are non-recoverable.  This includes transactions such as bad debts, debt forgiveness (e.g. interest charges) and small over/under payments.  Often the transactions sit on the ledger for a while because it is troublesome to correct them.

In Sage 300, there are a couple of processes that can be used to clear these items.  In situations where there are multiple entries to clear at one time, and where a set of criteria can be defined for doing that, the write-off function may be the answer.

First, review the Accounts Receivable account sets to make sure the Write-Offs General Ledger account is the correct one for each.  Write-Offs is the expense account to which amounts are debited when you write off transactions and balances; it is often called ‘bad debt’. The Create Write off Batch screen automatically produces transactions for this account.

Mary Blog may 4th pic 1

Either now or before posting the final results, print the Accounts Receivable Aged Trial Balance for the customers and transaction types used in the Write Off selection process, to use in verifying the results.  In this example, the Aged Trial Balance report used is for customer 1200, Ronald Black, and the transactions that will be written off are outstanding transactions on/before 10/31/2019:

Mary Blog May 4th pic 2

Mr. Black’s account has three transactions that will be part of the write-off:  a payment and two invoices.

Go to Accounts Receivable Periodic Processing and choose “Create Write-Off Batch”.  Complete the screen selections as required to gather the appropriate transactions.  The choice for the “type of transaction” to write off include outstanding transactions, overdue transactions, overdue balances, outstanding balances and outstanding retainage.  In this example, “outstanding transactions” is chosen.

Choose the Account Type; Ronald Black’s account is Balance Forward, so that is selected.

Choose the document types to include in the process; at least one must be selected.  In this example, “all” is chosen, though it could have been limited just the types of transactions outstanding.

Choose either “amount” or “percentage” as the “Select By”, and enter non-zero values in the “From” and “To” fields.  When creating a batch to write off small balances, enter a range where the values represent the limits the company wishes to write off.  In this example, to include the outstanding cash, which shows as a negative number, the “From” is set to that value, and the “To” to the value of the highest invoice value for the customer.   (Note:  it is not necessary to use the exact amounts for the range, only that they be set to allow the desired range of documents to be captured).

In the bottom portion of the screen, choose up to 4 different criteria to select the customers who will be included.  Leave all as (None) to gather data for all customers who meet the other criteria, or enter the appropriate range.   Selections may be done by a number of customer-related variables – use the drop downs to review and choose the criteria required.  In this example, only Customer Number 1200 (for Ronald Black) is chosen.

Mary Blog may 4th Pic 3

Note that there is an “optional fields” tab.  This may be used to further scope the transactions that will be included in the Write-Off batch.  This example does not use optional fields so none are selected.

Once all criteria are set, select “Process”.  A batch of transactions meeting the criteria is created and posted as an open batch with a description of “Write Off” in the Accounts Receivable Adjustments batch list.  Each transaction being written off is a separate entry in the adjustment batch.  The batch may be reviewed and transactions edited as needed, and then posted as a normal adjustment batch.  If the batch is incorrect, it can be deleted and the process started over.

The following shows one entry from the write-off on Ronald Black’s account:

Mary Blog May 4th Pic 4

Written by Mary Clark, Consultant

WAC Solution Partners- New England

Know your Business Inside AND Out with Sage Alerts and Workflow

Sage KnowledgeSync offers an incredible add-on product that allow users to monitor their Sage ERP systems. Sage Alerts & Workflow can be used to create automatic alerts to notify users of events such as late shipping, when inventory is below a certain amount, list which customers are overdue by ‘x’ amount of days, and more! This software also allows users to send emails through Sage Alerts using their Microsoft Exchange, Gmail, Hotmail, or Yahoo accounts. Sage A&W allows users to send instant messages, SMS, fax, and create dashboards.  It can automatically send generated data to any internal or external department member at customizable schedule times. Attach the appropriate reports to emails when the desired event is triggered. Users can schedule and modify any of the hundreds of pre-configured events already in Sage Alerts or create your own.

Most recently, I have been training a client in using Sage Alerts & Workflow with their Sage 300 ERP system. We trained on scheduling basic events and modifying them as necessary to better fit this client’s needs and make the most of the software. We have scheduled specific events and query triggers to send notifications to specified email addresses. One example includes generating a list of customers who are more than 30 days overdue on an open invoice. Another example is being notified when inventory is below 0 and generating a list of items with negative quantities on hand.

Sage Alerts & Workflow can also be used to monitor a wide variety of other Sage products such as Sage HRMS and Sage CRM. This software can help users interact with their business systems more efficiently by sending an alert the moment a desired event happens. Become better in tune with your business with Sage Alerts & Workflow.

Our clients have been very happy with the system and we continue to share it with other clients who are not taking advantage of it yet.

Written by Stephanie Piller, Administrative Executive

WAC Solution Partners- Midwest

Processing Sales Tax Credit Notes in Sage 300

This process can be used to correct an AR or OE invoice where the customer was incorrectly charged sales tax.  If the customer has not paid the invoice, then you will only need to process a credit note.  If the customer has paid the incorrect invoice, then your will may need to take an additional step of refunding the credit balance after you post your credit note.

Warning

  • If you are using OE and AR credit notes, make sure to change number prefix of either the OE credit note number or the AR credit note number in the modules options setup. If you don’t, you run the risk of having duplicate numbers because by default both OE and AR credit notes start with “CN”.  You can prevent this issue by simply adding an addition character to one of the prefix numbers i.e. CRA.Image1
  • Even if the source invoice originates from the OE module, you should consider only processing “Sales Tax Only Credit Memos” from the AR module. This way you will not impact inventory.

Preparation

We need to setup an AR Item for processing the credit note.  If your invoice originated from AR, you could use the items on the original invoice instead.  For this example we will setup an AR item called “TAX” (Sales Tax Credit).  When we setup the AR item TAX, the GL accounts for Revenue won’t matter as the posting will both debit and credit the same account resulting in 0.00 net effect.  In this example we used the sales tax liability account but you could setup a special GL clearing account for this type of transaction.

Image2

Processing the credit Note

  1. Review the original invoice to obtain your subtotal before taxes and total tax amount. In this example: $100.00 with a sales tax of $8.25.

Image3

  1. Create a new credit note in AR and be sure to document what you are doing by entering both a batch description and transaction description. This may be important for identifying your corrective action later.  Make sure you are using the transaction type Item and document type of Credit Note as shown below.

Image4

  1. You can enter the original invoice number in the “Apply-To Document or post as an open credit to the customer account. If the invoice has been paid in full, it will create an open credit.

Image5

  1. Add Line items as follow:
    1. Enter the Item No. TAX and the unit price equal to the Subtotal before tax ($100.00). Press the add button and insure the sales tax is calculated properly.  Note: You will need to add/save the line before you will be able to verify the tax calculation.  Image6
    2. Enter a second Item No. TAX and a -1 in the quantity field and the same unit price of $100.00. Set the tax class to Nontaxable.  In this example it is represented by the 2.  Add/save this line and verify that there is not tax calculated for this line.  Image7
    3. Review the invoice totals tab: Notice the resulting entries will give you a credit memo for the sales tax only. Image8
    4. When posting this entry, you will receive a warning that the tax amount for the document exceeds the total distribution net of tax amount.  Close this warning and post your batch.  Image9
    5. This will result in a tax only credit note of $8.25 This entry can be verified by printing the batch journal report or review the batch in the GL module.
  • The GL entry:
  • Debit your GL Tax Liability account(s) 8.25
  • Credit your GL Accounts Receivable account <8.25>

Image10

If the customer has already paid the original incorrect invoice in full, you can create an AR Refund Entry to refund the balance back to the customer.  Note: Sage 300 will allow posting adjustments against payments; however, this should not be used as these type of entries will to post to bank services for bank account reconciliation.

 

Blog Article By: Mike Davidson, WAC Solution Partners Utah

Sage 300 Application, Technical, Implementation, and SEM Migration Consultant,