Most of us have experienced the pain and agony of having to hand-key a very large, multiple line journal entry. You add G/L account number after G/L account number, debit after debit, and credit after credit. You get to the end of the very long journal entry press the save button only to find that your very long, multiple line journal entry is out of balance. So you start your quest. You go line by line comparing the numbers you keyed against your source documentation to ensure that all of the numbers have been entered correctly. If you’re lucky, you’ll find the data entry error in time so that the last thing left to eat in the cafeteria for lunch isn’t a cold grilled cheese sandwich.
What could possibly be worse than that? Having to do that same journal entry month after month or even worse, week after week. If you are not fortunate enough to have your payroll company generate your “gross to net” payroll entry in a file that can be uploaded into your accounting system, you know the shear horror that can overtake you having to make that journal entry every payday. If you have a very large company with many departments, that entry could potentially take several hours to complete.
Acumatica Cloud ERP has two ways to assist you with the dreaded long and laborious journal entry. Recurring Transactions can be created and scheduled and then generated every month to combat having to hand key a different variation of the same journal entry month after month. Existing journal entries can be assigned to Recurring Schedules and then generated at the necessary intervals to prevent having to hand key large or even small recurring journal transactions.
Acumatica Cloud ERP also gives users the ability to upload transactions into the detail lines of most forms in the application. Anywhere in a grid where you see that particular symbol, it means that you can upload contents into that form using a simple Excel template.
The first thing that needs to be done is to create the Excel template for uploading your data into the detail lines section of the journal transactions form. To create the template, navigate to an existing entry using the Batch number lookup field.
Even a casual Acumatica user knows that the columns on the details line grid can be modified to show only the columns necessary for data entry. After modifying the columns, click the square with the X in it. This will open the Excel application (you must have Excel installed on the computer that you working on) with the column headings and data from the grid.
Now you have an Excel spreadsheet with the columns necessary for creating the detail lines of a journal transactions. To use this template, delete the data from every row except row 1 of the spreadsheet and click File>Save As to save your template and give it a name. My suggestion would be something like Journal Transaction Template.
Enter or copy and paste the data for the distribution of your journal entry into the template that you have created. There is no limit to the number of lines that you can add to the template as long as the debits and credits net to zero. Once your spreadsheet is filled out and ready to upload it should be saved. It is not necessary to keep a “file” of all of your transaction spreadsheets because the application does it for you.
Now it is time to create the Journal Transaction header so that you can upload the detail lines file. Log into Acumatica and navigate to Finance>General Ledger>Work Area>Journal Transactions. Click the + sign to begin a new Journal Transaction. A new batch number will be generated once the header is saved so enter your transaction date (your Post Period will display based on the transaction date). The Branch and Ledger will default. Enter a description for this journal transaction in the Description field. Hit the save button to save the header information.
Click the folder with the arrow pointing upward to select the file that contains the detail lines to upload. The application will allow you to browse to the file location.
Click Browse and navigate to the location of the file that you wish to upload and double click on that file. Click the Upload button to upload the file into the application.
A box entitled Common Settings will appear click OK.
Another box entitled Columns will appear, click OK and the file will upload.
Once your detail lines have been uploaded, you can release your journal transaction.
For more handy Acumatica tricks that will make your work life happier, contact our Acumatica specialist, Brett at 301.360.0001.
BRETT MARTIN | Business Software Consultant
Brett has over 15 years of experience in accounting holding positions of Controller, Assistant Controller, and Accounting Manager in various industries including federal government contracting, not for profit bio-tech, real estate, motion picture distribution, and motion picture production. In addition, Brett has 10 years of experience implementing accounting systems such as Acumatica, Microsoft Dynamics NAV, JAMIS Prime ERP, JAMIS Financials, Deltek Costpoint, and Deltek GCS Premier. Prior to KTL, he worked as a Senior Business Consultant for JAMIS Software Corporation implementing JAMIS Prime ERP and JAMIS Financials. Brett has studied accounting and computer information systems at Morehouse and Montgomery Colleges.