STEPS:


How to confirm the data is matching with your Dynamic balance?

  1. Log in to Dynamics - verify the entity (top right corner - TRCA/TRUS/TESL)
  2. Navigate to the Accounts Receivable Module
  3. Select global transaction 
  4. Assign the deposit you are going to work with, please add your initials ex. (SM, for Sabrina Montero, CS for Cedric Santana).



Go to Global transactions


Filter by contains, type your customer’s name


Select all the invoices included in that customer


Download the data to start your work. Please click on the Icon.


  1. Create a file to save your different customers or data
  2. Please open the general template of TRIMAC ICG to work this type of data that the Icg team has already created

  4. When you open the document already downloaded you will find all open transactions for that customer


  1. Steps 2.0select all the data and convert to numbers (select the first one clicks on it and convert to number). Please select from customer reference 1 to customer reference 8. 


  1. Copy all the data and paste it into the template you created in the first workbook in Excel REMIT FROM (column B1) in the VLOOKUP TEMPLATE.


Go back to Dynamic and download the attachment, click on it/select your transaction/click open and download. Please see below:


The data (the customer info you downloaded from Dynamics, you will copy it and paste it to the 2nd sheet in excel (original back up).


Extract the BOLs numbers- how to find them? (Please see the first column in Suncor) 


copy and paste them into BOL column in the VLOOKUP up template


Extract the amounts- how to find this info? Go back to the Sheet in ORIGINAL BACK UP/select total value.


Copy and paste the values into the BOL sheet in Excel into the AMT column.

v



Identify positive values that are the amount of credit. Remove the negative sign using (find and select/replace/ place the (–) and select replace all). 




Make sure that the amount summary is the same you have in dynamics on your journals.



If you found duplicated BOLs please see instructions (go to insert/pivot table/ from table/range) and select all the data obviously on your BOL Column.


If you find BOLs with letters, please delete them.



BOLs information please place it on Rows pivot tablet and amount in Values.


  1. After the step #1.10 select all the information contained on rows table already created and paste it on the next sheet of the VLOOKUP template, this is to apply the formula. 


(You will paste this on the first column of the VLOOKUP template (BOLs column). Please see below:




  1. Select sum of amount from the BOL sheet and paste it on the column of paid in the VLOOKUP template. See attached




In the VLOOKUP template sheet select the formula (they look like characters, drag to the bottom to cover all your content, this will help you to find the BOLs numbers.




Then go to the sheet Remit from to give reference to your formula, YOU WILL find this in the column O, select the column A, set as general in excel and put your formula, if you want that it shows you the content in column O (=O, SELECT THE FIRST ONE and then drag to the bottom, this is in REMIT FROM SHEET).



  1. GO back to your VLOOKUP template and finally you will find your invoices.

If you have a duplicate BOL and that is not paying B, because it has a high variation, copy it and paste it into BOL2 the first invoice that is in column BOL1 to use as a reference and select send the invoice that belongs to that sheet. Copy the invoice from REMIT FROM SHEET and paste it into the BOL column in the VLOOKUP template creation. 

In column BOL1. Continue to the second to see if there is another.




After that process, please copy all your data from the VLOOKUP template and paste it to PMT to apply (Payment to apply) without the formula.




  1. Delete all the data in the VLOOKUP template, please repeat the process. 



The first A column as a general, instead of O this time is P (=p)



The process where you put the column A as general in the remit from and apply the formula with the O, you need to repeat this process till the end. Go to the VLOOKUP.


Sort A to Z


  1. The final ones you don’t find you need to put them in on account.
  2. If you find one that relates to other number, you need to split it.
  3. You can create new columns, then look for BOLs with spaces when you got them, select/ go to Data in excel/ text to column, then separate them by spaces.


  1. Some invoices that were separated were identified by their amount, apply the formula, but using the invoice reference column (H) and the invoices will appear complete in your VLOOKUP template. 
  2. Copy the BOLs numbers with the invoice’s numbers, copy and paste it on the column formula.
  3. Column D you cannot apply several invoices in the ARO3.
  1. Try to find any duplicated how (go to conditional formatting/highlight cells rules/ duplicate values/ok) this will show you the duplicated invoices. 



Organize alphabetically or by color to find the duplicated. Then calculate the values that belong to the same invoices. 



How to calculate values for duplicate invoices? Please see below:



In the column of BOL 2 please place the 2nd BOL from the first column 




if you have duplicated invoices, you just need to make a summary in the column of BOL 2 (=first value + second value) and repeat all the process.


The second BOL, you drag it to the second column (BOL2). 



When you find the subtotal, delete the blank file.



Go to the FORMULA column and apply to all.





Please update the data in the column variance.



Please update the data in the column variance.




If the variance is very high, you filter from highest to lowest and verify that another invoice has not been paid with that same amount.

Example:




Select your BOL from PMT to apply to request confirmation if that is the only BOL to which that high variance payment is being applied.

 

Go to Remit from sheet



You must manually change from Remit from sheet the B invoice (the one with the B) copy it and paste it into PMT TO APPLY.


And copy to PMT TO APPLY SHEET


Calculate totals. 


AR-03

Organize the data that is in on- accounted. Go to VLOOKUP-WORKING COPY



Copy and paste this information into PMT TO APPLY COLUMN.


Please apply the account that is relevant.

General journal please confirm, please see how.


On accounted values


Please copy this on a new sheet 


For the AR-03 Go to DYNAMICS/select AR-03/ DOWNLOAD.



Select this info from your VLOOKUP template.

Then start working on your AR-03.





Put all your PMT information to apply sheet / select invoices and paste them into Excel sheet AR-03.




AR-03




Select the amount /copy and paste them into Excel sheet AR-03.


In the credit column



Select your formula data to use it on customer reference 2


Then PUBLISH 




When you are done, please create a general journal with the difference.



Start section in Finance and operations and create your journal

Please wait…

Click on Show user-created only



Click new


Please type Caroa, for on Account




Enter your description




Download your AR-02 for On Accounted invoices.






Please go back copy THIS info and paste it into the AR-02.




Double check and publish…





Confirm




Please, make sure to attach all your backups… 





Then select your journal and select post: