AR – Customer Refunds (NEW)

If there are refunds, please move on to step 2.

Locate the Refund Folder located at Santanna Energy ServicesAccounting - DocumentsGeneralAccounts Payable4. Refunds then the appropriate month. 

Right Click and Copy the newest REFUNDS folder within the month and paste to get a copy of that folder.  

Rename the folder and file within appropriately.

1. Log into EOS
2. Work Queue
3. Work Queue again
4. Open the "Comment" carrot and click on "Add Comment"

I find it easier to go in daily and assign the refund WQ to myself and change the date to the appropriate Wednesday.

To do this, click the carrot to an individual WQ.  Review the comment to see if a refund is being requested.  The ones from Denise are typically refunds.  

1. Change Assignee to your name
2. Change date to appropriate Wednesday
3. DO NOT ADD COMMENT - click on the checkmark to save.

If you need to add a comment - add it AFTER you assign the WQ.  Those 2 functions do not work together.

You'll want to have your refund spreadsheet in one screen and your EOS window in another.

1. Open the first refund "carrot" in EOS.  
2. In your spreadsheet - use the Wednesday date as the date.
3. SESID = Reference Number in EOS WQ
4. Copy and paste what's after Comment Detail Text and place it in Reason Column in your excel spreadsheet.

1. Next - click on SES ID within the WQ (2 in the graphic above).  It will open the account in another window.
2. Go to Services Tab.
3. Copy the Utility Account Number and place it in UAN Column in your excel spreadsheet.

1. Go to Billing Tab in EOS and use Billing information to complete customer demographics in your spreadsheet.

For Refund amount - use what is in the Comment text but use a positive number - not a negative.

You'll also want to use the shorthand of the utility - The utility can be found to the left of the above graphic - by the hand.  In this instance it would be DTEMIG.

Transaction Number would be CUSTREF_ followed by the SESID. Vendor Number is for AP only.

Once the data is transferred into the spreadsheet - go  back to the WQ in EOS and add a comment.  "Submitted to AP for processing on MM/DD/YY"

Then you can put the WQ in process by pressing the play button. 

Add comment below tagging Taylor to let her know that Refunds are ready to be processed.

The Refund Request spreadsheet location is displayed above. Go to the current month file folder and then the most recent day folder and open the Excel file. The example is for the file named "202304 26 REFUNDS.xls"

Import templates can be found in 

Accounting>AccountsPayable>Refunds

Open the Import Vendors workbook and save as "YYYYMMDD Intacct Vendor Import" in the month you're working on.

Open the Import Bills workbook and save as "YYYYMMDD Intacct Bills Import" in the month you're working on.

Open the Import Vendors file and update with the appropriate values.

Copy and paste the following values from the REFUNDS file to the import vendors file:

Copy "LAST NAME" to "NAME", "CONTACT_NAME", and "PRINT_AS"

Copy"ADDRESS1" to "ADDRESS1"

Copy "CITY" to "CITY"

Copy "ZIP" to "ZIP"

BILLING_TYPE = B

TAXABLE = F

STATE = the state the Utility is located in

COUNTRY = US

Save the file "YYYY MM DD intacct import vendors"

Import the vendor file into Intacct

Accounts Payable > Vendors > Import

Click "Choose File" and select your file to import (2020 06 26 intacct import vendors) and click "Import"

Once the import is complete, you will receive an email notifying you if it was successful.

Verify that the customers were imported into Intacct by navigating to the Vendors screen and clicking on the "Include one-time" check box at the top.  Then click on the down arrow next to the "Vendor ID" to sort them in descending order.  The new vendors you just added should display at the top of the list

Go back to the "YYYY MM DD REFUNDS.xls" file and enter in the new Vendor ID for each customer that was just created.

You need to edit each of the vendors that were just created.  Click "Edit to the far left of the first Vendor that is displaying.  Select the check box next to "One-time use" and then click on the "Additional information" tab.

In the Additional Information tab, Change the "Type ID" to "Customer Refund". Then click on the "Payment information" tab.

Change the "Preferred payment method" to "Check Delivery" and select the checkbox next to "Enable Check Delivery Service". Click on Save.
Repeat this process for each of the Vendors that were just added.

Visually verify the changes that were made to the vendors.

Open the file "YYYY MM DD intacct import bills"

NOTE:  Customers from EOS will usually receive a REFUND that is for a price adjustment.  Customers from GMS will usually receive a CASHOUT which is us returning money to them that they have already paid.  A REFUND will go to the Sales Price Adjustment account (48300) and a CASHOUT will go to the Suspense account (99999).

Copy and paste the following values from the REFUNDS file to the import bills file:

"Transaction Trace" to "BILL_NO"

"Vendor ID" to "VENDOR_ID

"POSTING_DATE" = current date

"CREATED_DATE" = current date

"DUE_DATE" = current date

"LINE_NO" = 1

"ACCT_NO" = 48300 Sales Price Adjustments for REFUNDS

  = 99999 Suspense for CASHOUTS (Credit Balance Refunds)

"LOCATION" = the state the Utility is located in

"DEPT_ID" = CS

"REFUND AMOUNT" to "AMOUNT

"DESCRIPTION" to Description from Paola.

"Utility" to "APBILLITEM_CUSTOMERID"

"Vendor ID" to "APBILLITEM_VENDORID"

"APBILLITEM_EMPLOYEEID"   = BM-1006 

"APBILLITEM_ITEMID" = 100 (gas) OR 200 (electric)

**IF THE UTILTY AMEILG IS LISTED AS UTILITY-CHANGE IT TO AMEILG_TRANS**

check for and delete all spaces at the end of the bill name and address lines

Save the changes to file "YYYY MM DD intacct import bills" file.

Import the bills file into Intacct

Accounts Payable > Bills  > Import

Click "Choose File" and select your file to import (YYYY MM DD intacct import bills) and click "Import"

Once the import is complete, you will receive an email notifying you if it was successful.

Refresh the Bills page and visually verify the bills that were just imported.

if you entered a CASHOUT it should look like the bill below with the Account of 99999--Suspense

An AR Adjustment needs to be entered for the Utility.  Navigate to Accounts Receivable > Adjustments

Enter a Debit Memo using the same information from the previous bill you entered.

Post the Adjustment in Intacct.

In EOS navigate to:

Operations > Accounting > Adjustment Upload

Drag and Drop the entire Refund.xls file to the Upload area

Send an email to Paola letting her know the refunds have been entered.

Add comment below tagging Paola so that she can complete the comment upload.

In Intacct navigate to Accounts Payable > Registers > Check

Enter the following filter criteria:

Start Date = Date last Check Register was run

End Date = blank

Checking account = JPMC Refund Acct

Show for = All States

Select the Show details checkbox

Select the Show reconciliation status checkbox

Reconcile state = All

Click the "View" button at the top right to run the report.

Click the Export button and select CSV

Download the file and save it as an Excel Workbook to:

Open the most recent Check Register MMDDYYYY.xls file located here:

Complete the following steps in the Excel file to prepare an upload file

1.  Use column D "Bill No" to separate out the EOS customer #.  Copy column D to a new blank column (O).  Highlight column O and click on the Data menu at the top, then click the Text to Columns icon.  A popup wizard will display.  Select Delimited and then click Next.

In the next step of the Wizard select the checkbox next to Other and type in an _ in the box to the right.  This will separate your text where there is an _.  Click Next.

In the last step of the Wizard click the Finish button.  The text will now be separated into 2 columns or more. If any of the Bill Numbers ended in and _2 or _A, that data will be split into a 3rd column.  

Delete column Q if there is data in it.  Rename your new column Billing Account Number.

Remove Transfer cutomers:  Filter column O for CASHOUT (which is used for Transport customer refunds).  Delete the rows that have CASHOUT and then remove the filter on column O.

2.  To use a date field in a concatenate formula it must be formatted as text.  We need to use the Date column B but it is not text.  To make it text enter the following formula in column A (which is a blank column):

=TEXT(B3,"mm/dd/yyyy")

3.  Now we are ready to use the data in the file to concatenate together the following sentence:

SES issued Refund check # ________ on DATE in the amount of $_________. 

Copy and paste the formula below to cell Q3:

=CONCATENATE("SES issued Refund check #",H3," on ",A3," in the amount of $",I3)

Copy the formula to the entire column Q.  Highlight column Q and copy and paste the values to get rid of the formulas. (You may also need to change the font to Calibri size 11 for column Q).

4.  Add a new column after column O "Billing Account Number" and label it CUSTOMER NUMBER.  This field is required in the EOS import template and it can be pulled from the original refund request file where it is labeled Customer ID.

In the Check Register report do a vlookup in Column P(Customer Number) to lookup the Billing Account Number in the Refund Request Spreadsheet and return the Customer ID/Customer Number.

Copy paste the values returned from the vlookup.

Use =VLOOKUP(O3,Refund file you need. highlight column B&C in that spreadsheet,2,FALSE)

=VLOOKUP(O3,           ,2,FALSE)

In File Manager go to Accounting > General> Accounts Payable > 4. Refunds

Open the file COM-US_BatchCommentTemplate.xls

Create a copy of the file by doing File > Save a Copy , and name the file: Refund Comment Upload MMDDYYYY.xls

Carefully copy the following 3 columns from the Check Register to the Comment Upload template:

Customer Number

Billing Account Number

Comment 

Verify that each row is fully populated.

In EOS navigate to Operations > Account Maintenance > Comment Upload

Drag and drop the excel file Refund Comment Upload MMDDYYYY.xls to the upper right corner of EOS and the file will upload.

Once the file has uploaded you need to move the Refund Comment Upload MMDDYYYY.xls file to the folder 4. Refunds > EOS Comment Upload > IMPORTED

Add comment below tagging Taylor so that she can complete the Refund Checklist

Accounting-General>Accounts Payable>Refunds>2023

Add this week's refund customers to the spreadsheet.

Fill in the columns below

Bill Create Date

EOS ADJ Upload Date

Check Number