Associate Billing with KWAR: Preparing to Use for Billing

** This article is for troubleshooting purposes for existing installs of this program. The program is no longer available for install. Please click here to learn about KWAR2 **

KW AR is a utility that will take a specially formatted Excel spreadsheet for Accounts Receivable, and automatically generate an Item Sales file for importing into AccountEdge. It then e-mails invoices out to agents according to the e-mail address on file in their AccountEdge Customer Card. The KW AR eliminates billing with Recurring Invoices inside AccountEdge.

Section 1: Preparing Items and Customer Cards in AccountEdge
Customer Cards
Every associate that you will be billing in KWAR must be set up with a customer card in AccountEdge, complete with a Card ID. The Card ID will have to be an exact match to the ID you enter on the billing spreadsheet. Most Market Centers use the 4 digit MORE ID for this field. If doing so, understand that Excel will drop any leading zeros. For any MORE ID starting in 0, either drop it and use just 3 digits or add a 1 in front to make 5 digits.

 

Items
Every item that you will invoice your associates must be set up as an Item in AccountEdge. The item number is an alphanumeric field. The item number will have to match exactly to what you enter on your spreadsheet. The item must be marked as something you sell and an account tied to it. Most items you sell are pass thru costs. The account number on the record will either be a liability account (example: Associate Tech fee), Expense Account or Other Income account. Nothing should be set up to hit a sales account in the 5-#### or 6-#### range.

 

Section 2: Preparing to Use the KW AR
There are four steps in preparing to use the KW AR:

1. Creating the AR Spreadsheet

2. Saving the AR Spreadsheet

3. Collecting AccountEdge Data

4. Collecting Mail Server Information

 

Step 1: Creating the AR Spreadsheet
The Accounts Receivable (AR) spreadsheet is simply an Excel Spreadsheet that holds all the information necessary to send agents an invoice and import that same invoice information into AccountEdge. This allows you to send invoices to many agents all at once instead of one by one. The format of the spreadsheet and the information contained within is very specific and must exist to successfully bill your agents. Generally speaking, items that you intend to bill agents for are the columns and each agent is a row. The sample spreadsheet is called SampleARSheet.xls and is attached to this article.

Row Format
Note: Do not use column J for billing. It is for descriptive use only. Each row must have the following columns:

Row Title Explanation

 

Column	Description
A # Required.	This column becomes part of your job number which is explained in Section 3.4 and corresponds to the Job Number you will set up in Section 1.2.4. If the Job Number you set up in Section 1.2.4 is 040106 then in this column you would enter 01 in this column and specifically not 040106.
B Name Required	Agent Name. This is the name of the Agent being billed however the actual name that appears on the Invoice is the Name that is entered in the AccountEdge Customer Card. If an agent is being billed for another agent’s items and you need that to be reflected on the invoice, enter the name of the other agent in Parenthesis after the name of the agent actually being billed. DO NOT include extra quote (“”) marks in the name field (e.g. nicknames). An example of a single agent being billed: John Doe An example of an agent being billed for another agent’s items: John Doe (Jane Doe)
C ID Required	AccountEdge Card ID. This is the Card ID for the AccountEdge Customer Card viewable in the Card File. This Card ID must match exactly what is in your Card File. If an agent is being billed for another agent’s items then the ID should be the ID of the agent who is actually being billed. One suggested format is to use the MORE ID as the Card ID. Keep in mind if the 4 digit MORE ID has a leader 0, that won’t show up in your spreadsheet so leave it off your Card ID.
D – I Follow column format rules Optional	These are items that you could bill agents for. You do not have to use all of them. Each cell (except columns A, B, C and J) contain either the quantity of items, if you are billing in quantity, or the amount of the item that you are billing for. The way you bill is determined by the how you have entered your column name. If you included an Item Price then you are billing by quantity. If you did not include an Item Price then you are billing by price.
J Special Column Required	This is a descriptive field. One use for it is to notate the Premier Vendor for an agent. Do NOT use this field for billing.
K – S Follow column format rules. Optional	These are items that you could bill agents for. You do not have to use all of them. Each cell (except columns A, B, C and J) contain either the quantity of items, if you are billing in quantity, or the amount of the item that you are billing for. The way you bill is determined by the how you have entered your column name. If you included an Item Price then you are billing by quantity. If you did not include an Item Price then you are billing by price. NOTE: If you wish to include an extra description for the item being billed for the person being billed then you can do this by including the text in parenthesis after the quantity or price. This description appears on the invoice line item after the Item Description specified in the column header separated. Example: Billed by quantity with a description: 2 (Includes October billing)

 

Column Format Rules:

Each item that you bill agents for must have the following information to properly construct a column header:

• Item Description - The description on the Invoice.

• Item Price -  The price per item when specifying quantities. The item price is not necessary if you are not specifying quantities.

• AccountEdge Item Number - Every item that you are billing for should have an Item Number in AccountEdge. The AccountEdge Item Number must be exactly as it appears in AccountEdge. This includes spelling and capitalization.

The format should read:

Item Description (Item Price)/AccountEdge Item Number
(If the value in the column is the quantity)

OR

Item Description /AccountEdge Item Number
(If the value in the column is the amount, not quantity)

NOTE: Be sure to include a forward slash (“/”) before you enter the AccountEdge Item Number. If you wish to bill in quantities for the item, add the Item Price in parenthesis, otherwise do not include the Item Price.

 

Examples:

 

A column header with billing by amount	A column header with billing by quantity, where price per item is $0.99
Office Rent/Rent	Black and White Copies (.99)/BW

 

 

Step 2: Saving the AR Spreadsheet

After you have completed and saved your spreadsheet, you must save it as a Comma Separated Values formatted file. Below are the instructions:

1. Click File at the top of the Excel window

2. Click Save As …

3. At the bottom of the Save As window, change the Save as type to “CSV (Comma delimited) (*.csv)

4. Be sure you save this somewhere you will remember.

5. Click the Save button. You will probably receive the following message: “(filename).csv may contain features that are not compatible with CSV (Comma delimited). Do you want to keep the workbook in this format?”

6. Click Yes.

7. When you are finished, close the spreadsheet. Click no when you are prompted to save any changes unless you actually have made changes since you “Saved As."

 

Step 3: Collecting AccountEdge Data

KW AR uses several sources of data from AccountEdge. The first is AccountEdge Customer Card. Every agent that you wish to bill for must exist in the AccountEdge Card file as Customers. This file is required. The second is a list of the items that you have set up in AccountEdge. This is used by KW AR to simplify editing of invoices for charges do not regularly occur.

 

You will use the following:

AccountEdge Customer Card File
AccountEdge Card File Summary
AccountEdge Items

 

AccountEdge Customer Card File
To export the AccountEdge Customer Card File do the following:

1. Open your AccountEdge Company File.

2. Click File in the upper left hand of the AccountEdge window.

3. Click Export Data

4. Click Cards

5. Click Customer Cards

6. A window titled Export Data should appear. Set the Export File Format to Tab Delimited and set the First Record to Header Record.

7. Click Continue.

8. Click Match All.

9. Click Export.

10. Save the file with the default name in the same location as the AR Spreadsheet CSV file.

 

AccountEdge Card File Summary
1. Open you AccountEdge Company File.

2. Click Reports in the top of your AccountEdge window.

3. Click Index to Reports.

4. Select the Card File tab.

5. Select Card List Summary from the list of reports.

6. Click the Send To button and choose Tab Delimited File.

7. Save the file with the default name in the same location as the AR Spreadsheet CSV file.

 

AccountEdge Items
1. Open your AccountEdge Company File.

2. Click File in the upper left hand of the AccountEdge window.

3. Click Export Data

4. Click Items

5. A window titled Export Data should appear. Set the Export File Format to Tab Delimited and set the First Record to Header Record

6. Click Continue.

7. Click Match All.

8. Click Export.

9. Save the file with the default name in the same location as the AR Spreadsheet CSV file.Section 1

 

AccountEdge Job Codes (for business centers)
To Add a Job Number, which will be applied to the items you bill your agents for, do the following:
1. Open you AccountEdge Company File.
2. Click Lists in the top of your AccountEdge window.
3. Click Jobs.
4. Click New.
5. Enter the Job Number and press OK.

 

Step 4: Collecting Mail Server Information

Obtain your outgoing, SMTP only, server information from your IT support staff or your Internet Service Provider. This information is required to email your invoices. If you are using your KLRW account, click here for setup information using KLRW (Gmail).

There is no provision in the KW AR for printing them instead. To print the invoices, you must first import them to AccountEdge.