Office 365 Xlookup function

Have you ever needed to use multiple lookups to place data in a spreadsheet?  Do you get tired of having to create multiple versions of the same lookup formula just because you need more than one piece of data?  If so, the XLookup function in Office 365 may be just what you need.  Xlookup has many more features than the old Vlookup and Hlookup, but this article will focus on the ability to return multiple pieces of data.

Xlookup may return multiple values in adjoining cells, either across or down.  Say you have an employee list with the employees id, first name, last name and phone number and you want to retrieve all the data in one formula.  Xlookup can do that.

For example, say you have an employee table is in cells q4:t53 and has the following attributes, id number, first name, last name and phone number and cell b3 three holds the id you want to retrieve.   The formula to return the first name, last name and phone number is =XLOOKUP(b3,q4:q53, r4:t53).  B3 is the value to search.  Q4:Q53 is the ID column in the employee database that will be searched and r4:t53 asks to return three pieces of data.

Xlookup is a nice function, but unfortunately current it is only available in the Office 365 versions of Excel and not the Office 2019 or earlier versions.

 

Sage Intelligence Reporting – Sage 300 Financial Ratio Report

Is the data your company needs to grow and run efficiently readily available? One of my favorite report writers is Sage Intelligence Reporting. Out of the hundreds available, I recently had a client find a very interesting report that will help many of you.

The Financial Ratio Report in Sage 300 provides many of the standard financial ratios like Profit Margin, Return on Assets, Gross Margin %, etc. Not only does it display the actual data, but it also allows the user to enter targets so you know how you are tracking compared to your plan.

The report generates the ratios in the current year and will report on the previous four years if desired, making it easy to see a five year trend.
Another area of the report features the year over year changes on revenue, net income, assets and liabilities. This is just a small example of what is reported.
For most of the small sized companies I work with, there is more information than they really need, but if it becomes confusing, we can very easily remove some items.
One last feature is that the report allows the user to select the five most important metrics of the thirty-three that the report can produce.

This tool, if used properly, can easily be configured to help companies improve their performance.

Happy Holiday Scam – son (Season)

Unfortunately, scammers to do not take off for the holiday season.  In fact, it appears the scams increase!  Last week a scam surfaced around the country.  A number of bomb scares were reported requesting a bit coin payment.  Since none of the “bomb” were detonated, this appears to have been just a scam.

Bomb scares are not the only scams during the season.  Just in the last few days I received a phone call from someone that reported they were receiving information about my business computer having problems.  I was too busy to play with them, so I did the right thing and just hung up.  Other companies that you have no relationship with are not getting information about your computer.

Also, with tax season being just around the corner, the IRS does not notify you of any tax problems with a phone call.  They ALWAYS send a letter in the US mail.

The only institutions that may call, email or text you are your bank and credit card companies.  Normally you will only receive these notifications if you sign up for them.  Just to be extra cautious, do not reply to the notification, but call your bank or credit card company via the number on your statement or their website.  If it is a legitimate concern, they will forward you to the correct person to resolve your issue.

Again, if you get an unsolicited phone call about any computer issues, just hang up.  The same goes for tax issues.  If you get a strange pop up on your computer, shut it down immediately.  If it will not let up shut down your computer, unplug it! and contact your IT department or a computer professional.

Unfortunately, the holiday season is a busy time for scammers.  Don’t let the business of the season allow you to let down your guard.  Happy Holidays and be careful!

 

 

 

 

 

 

Keeping Your Software Up To Date

Most software companies will support old versions of their software for a limited amount of time.  Sage, our main vendor, only supports two prior versions of their software for Sage 300.  A new version of Sage 300 comes out just about every year.  This is not uncommon as most software vendors have a similar policy.  Therefore, when Sage releases the 2019 version they will soon thereafter discontinue support for the 2016 version.  Old unsupported versions will most likely still work fine in the short term, but they come with a risk.

Recently one of our clients was hit by a virus.  Fortunately they have good backups and were able to mitigate the damage.  They decided to update their server and all of their workstations.  Support for their accounting software had stopped a few years back due to changes by Microsoft.  When they installed all the new hardware they encountered a number of issues with the old software.  They now are facing an unplanned upgrade.   They were perfectly happy with the old software, but there is always the risk that it will not run properly with new hardware.

Now we do not recommend that you always have to upgrade to the latest version and we continue to support old software even when it no longer supported by the vendor, but we are limited in what we can do.  Technology changes quickly and we do not want to hold a company back from using newer, faster and easier computer equipment and software, because they have an old accounting or ERP system.  If you are currently using unsupported software we recommend replacing it as soon as possible.  Definitely within the next year or two.

What you will find out when purchasing a new system is that many software companies are moving to a selling on a subscription basis.  This will significantly reduce the first year cost of the software, but future years many have a larger cost.  This is the way that Sage now sells most of its products and it is the way they sell Sage 300.  Interestingly, the future yearly subscription price is about the same as the old annual maintenance fee.  There is an activation fee in the first year and of course the cost of setting up the software, moving data and training users. We have not found a client that would pay more under the new pricing plan than if they were to purchase the software under the old method and keep paying for their annual maintenance.

The moral of the story is you can either plan to upgrade your software or wait for a disaster and be forced to upgrade.  We prefer working planned updates.  It is less stressful for you and easier too!

SALES TAX AFTER THE SUPREME COURT WAYFAIR CASE

I want to thank Joseph F Bigane, III, CPA, MST for the following article on the resent supreme court rules on sales taxes.   Joe is one of the most knowledgeable person on this topic and he wrote this article for particularly for small businesses.

SALES TAX AFTER THE SUPREME COURT WAYFAIR CASE

On June 21, 2018 the world of collecting sales tax in jurisdictions all over the United States changed forever.  The US Supreme Court held that it was no longer required that a seller must have a physical presence in a jurisdiction in order for that state (or subdivision thereof) to impose an obligation on sellers to collect the jurisdiction’s sales tax.  The dust has not yet settled and many jurisdictions are still determining how and when they will impose this duty.  Further, different states have different taxes, which for this article will all be referred to as “sales tax.”  Finally, the rules of South Dakota, the state in the Wayfair case, established a floor of $100,000 or more than 199 transactions in the prior year as the minimum activity in the state in order for the duty to collect to be imposed.  Other states may have different minimums but it is anticipated that they will not be smaller.

Having to collect a State’s sales tax is referred to as having “nexus.”  This is not a new term.  Before the Wayfair decision, nexus was achieved by having some form of physical presence in the State.  Wayfair did not change that; it merely expanded it to include the concept of economic nexus – doing enough business in the state over the internet or by other, non-physical means to exceed a determined floor.  If the business had physical presence nexus before this decision then it has been exposed to the obligation to collect sales tax for a time period predating Wayfair.  Such a situation should be discussed with a professional that can help you “get squared away.”

Remote sellers who now have nexus in states outside their home states are going to have to consider the following issues:

  1. determine how much sales dollars was earned in each state in your last fiscal year and

your current fiscal year

  • sales are made to the location to which physical goods are shipped, not

to where they are billed

  • sales of services have special rules, but generally will be sourced to where

they are received or, if point of receipt unknown, where they are billed

  • revenue subject to sales tax will vary by state. It may or may not include:
  1. freight and shipping
  2. installation
  • training
  1. warranties
  2. determine if each state taxes your particular goods and/or services are the

goods/services that you sell exempt by state law

  • are the goods/services that you sell exempt to some or all of your buyers
  • if your buyers are exempt, you will need to obtain from them the appropriate

state exemption certificate

  1. do you sell by drop shipment
  • these sales will now be counted to determine if you exceed a state’s

floor and either tax or an exemption certificate will need to be collected

 

Unfortunately, the states do not at this time have a common set of laws stating what is and what is not taxable or exempt.  So, sales into one state may be taxed while sales into a different state may not.  Some states tax most services (SD, NM) while other states have a list of “enumerated services” which they tax.  Here, too, there may be wide differences in the definition of what a service does or does not entail.  If you sell or license software you will need to determine if the state considers it canned or custom or both.

 

While there is no fifty state determination at this point, nor is there any serious indication that the federal Congress will intervene to provide a national solution, there are many states which are setting up their rules to be applicable as of October 1, 2018 or January 1, 2019.  So, you do not have the luxury of putting this issue off.  Collecting sales tax is an administrative burden but the money is collected from your customer and does not come out of your bottom line.  If you fail to properly collect and remit taxes, and the state(s) ultimately find and audit you, then the money (tax, penalties and interest) will come out of your bottom line.

 

So now is the time to talk to your professionals and determine in what states you will have to register as a tax collector and how you will determine the amount of tax you will need to collect for each transaction.  While a certain number of states are members of the Streamlined Sales Tax Agreement and can provide a simplified method for registering and filing, it may still be necessary for you to invest in sales tax software to determine the correct amount to charge for each transaction.

 

TPAC 2018

This past February was the Third-Party Advantage Conference (TPAC) held in Vancouver, British Colombia, Canada. I had the opportunity to visit this beautiful city and participate in the conference that brings together a great group of products and people on the Sage 300 ERP platform. Sage Business Partners, Development Partners, and customers from all over the globe were in attendance to discover what products are available on the market to enhance an end user’s Sage 300 experience.

I was in Vancouver the week before TPAC to attend a pre-conference training session for AutoSimply, one of the featured third-party exhibitors at the conference. The two-day training session discussed how to utilize the Manufacturing Order Module (version 2018) within Sage 300. The Manufacturing Order Module allows end users to create Bills Of Material (BOMs), manufacturing order entries, returns, and receipts. Other modules that were discussed, which can be added to Sage 300 in addition to the Manufacturing Order Module, includes Manufacturing Lots for lot control operations; Production Planning for budgeting and forecasting; and Shop Floor Control for tracking manufacturing operations such as runtime and operation downtime. AutoSimply also discussed their Inventory Barcode Manufacturing product which allows end users to scan compatible barcodes and automatically upload the information to their Sage 300 ERP. End users can also create Purchase Order (PO) Receipts, PO Returns, Order Entry (OE) Shipments and OE Returns from their handheld barcode scanning device.

At the end of the two-day AutoSimply training session, I was awarded a Certificate of Completion from AutoSimply.

I also attended a conference session regarding new features coming to the next version of Sage Alerts & Workflow led by Donald Farber of Vineyardsoft, a developer of the product. The next version of Sage Alerts & Workflow will be web based for convenient user access along with modern web screen designs.

Sage Alerts & Workflow is a query reporting software that notifies end users of events that users without Sage Alerts could only “wish they had known sooner.” These events include lists of: customers with open invoices due over ‘x’ days ago; customers whose credit cards expire next month; open sales orders that have not been shipped yet and are past the expected ship date; orders that have been shipped to the customer but have not been invoiced, etc. These reports can be set to automatically run on certain time intervals such as “Daily at 9am” or “Every 4th Friday of the Month,” and sent via email, text, or fax to persons of different departments and management levels internally and externally according to a business’ needs. Sage Alerts & Workflow is a great tool for users to keep track of their business from all angles.

Overall, TPAC 2018 was a successful and enlightening event to attend, to forge new relationships and learn about different products that operate alongside Sage 300. I look forward to attending again next year!

Sage 300 Over Applied Cash Receipts

Sage 300 allows a user to apply a payment to invoices for more than the amount of the payment.  A payment that is not fully applied, displays as a negative value on the A/R Aged Trial Balance.  A payment that is over applied, displays as a positive value.  Typically, this would not be an issue, however, the apply feature for credits and the entry of credit memos can only be used against invoices, and not against the over applied payments.

If the over applied payment is from a check receipt, the check can be reversed in Common Services – Bank Transactions – Reverse Transactions, but if the payment was received via credit card, through Sage Payment Solutions, that transaction cannot be reversed.

If the customers balance is correct, no action needs to be taken. When the next payment is received, it may be applied to that over payment record.  Only new payments can be applied against the over payment, and it can only be completed at the time payment is received.

If the balance is incorrect, a credit memo should be created, but it cannot be applied against the over applied payment.  The customer balance will be correct, but the aged receivables will show a credit balance and an equal over applied payment balance.  In order to clean up your records, a zero dollar receipt can be entered and applied to both the over applied payment and the credit memo.  This will remove both transactions from the A/R Aged Trial Balance.

If you have this problem, please feel free to contact me at Michael.Ericksen@wacptrs.com.

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.

Why Sage Payment Solutions for Credit Card Processing?

Sage Payment Solutions is a payment solution software that allows users to accept payment transactions. It includes an online portal for users to easily access when out of the office and allows users to keep track of their statements and transaction activity.

Currently I use Sage Payment Solutions with Sage 300 ERP 2017 accounting system. In Sage 300, once I enter an invoice for the customer, I can click “charge” directly from the invoice entry screen and the Sage Payment Solutions window will pop up, allowing me to complete the credit card transaction instantly. The payment transaction is quick and simple. Sage 300 specifically can save more than 20 credit card accounts per customer.

Sage Payment Solutions allows users to accept payments from their desktop, laptop, smartphone, and tablet for quick transactions at the point of sale at or away from the main business location. This system accepts Visa®, MasterCard®, American Express®, Discover®, Diners Club®, JCB, Debit/ATM, and private label cards as well. The payment hits our bank in approximately two business days.

There are many different processing companies out there.  What makes Sage Payment Solutions a better option for Sage ERP customers?  Quick, easy, one system integration.  Not only do you have access at your fingertips all within one system to process, but you have one Vendor to deal with giving you leverage on your rates.

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.

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.