Month: April 2011
Year End Process in Microsoft GP
This article explains the year end process in Microsoft GP, The posting procedure and the closing procedure for the following modules must be performed in the following order.
- Inventory
- Receivables
- Payables
- Fixed Assets
- Analytical Accounting
- General Ledger
It is highly recommended before proceedings with year-end process in GP take a backup of the company and the Dynamics database, detailed steps for each module are explained in the Microsoft KB articles which can be accessed through Partner/Customer source, but the simple steps would be as below (all the steps needs to be repeated for all the above said modules)
Steps to do the year-end
Year End Procedures – Inventory
KB Article – Inventory Year end Process
- Take a Company Database backup.
- Post all the transaction for the module for the year.
- Reconcile Inventory Quantities.
- Complete a Physical Inventory count and post any adjustments.
- Print any additional reports you’ll need for planning or for your permanent records.
- Make a backup of all Company data. (Go to Microsoft Dynamics GP >> Maintenance >> Backup).
- Close the year.
- Close the fiscal periods for the Inventory Series (optional).
-
Make a final backup.
Year End Procedures – Receivables
KB Article – Receivables Year end Process
- Take a Company Database backup.
- Post all the transaction for the module for the year
- Make a backup of all Company data. (Go to Microsoft Dynamics GP >> Maintenance >> Backup).
- Close the fiscal year in the Receivables Year-End Closing window
- Close all fiscal periods for the Sales series (optional)
-
Make a final backup.
Year End Procedures – Payables
KB Article – Payables Year end Process
- Take a Company Database backup.
- Post all the transaction for the module for the year.
- Print the Aged Trial Balance with Options Report to show the status of each Vendor Account.
- Print the Vendor Period Analysis report.
- Close All in the Payables Year-End Closing window
- Close all fiscal periods for the Purchasing series (optional).
- After you have completed the year-end closing procedures for all Sales and Purchasing modules, close the sales tax periods for the year.
- Make a final backup.
Year End Procedures – Fixed Assets
KB Article – Fixed Assets Year end Process
- Take a Company Database backup.
- Post all the transaction for the module for the year.
- Enter in all activity for the current fiscal year. This includes additions, changes, transfers and retirements.
- Run depreciation on all assets through the last day of the current fiscal year.
- Process the GL Interface (GL Posting) routine by going to Tools >> Routines >> Fixed Assets >> GL Posting.
- If required print FA reports.
- Verify that the Quarters are set up correctly for all fiscal years. Go to Tools >> Setup >> Fixed Assets >> Quarters.
- Perform the Fixed Asset Year-End Close routine by going to Tools >> Routines >> Fixed Assets >> Year End.
- Make a final backup.
Year End Procedures – General Ledger
KB Article – General Ledger Year end Process
-
Compete the posting procedure and the closing procedure (in the following order) for Inventory, Receivables, Payables, and Fixed Assets, if applicable.
NOTE: Year-end procedures for Payroll are only performed at the end of the calendar year.
- Post the final adjusting entries in the General Ledger module. If you must make any adjusting entries to allocate revenue, expenses, or depreciation to the year that you are closing, use the Transaction Entry window or the Quick Journal Entry window. If you wish to track your adjustments separately, use the Second Closing Period feature.
- Use SmartList to verify the posting type of each account. Go to Microsoft Dynamics GP >> SmartList >> Accounts. By
default, only the first 1,000 accounts will be displayed. If you have more than 1,000 accounts, double click on the box at the bottom where it says “First 1,000 accounts with no search criteria” and increase the maximum records (in red at bottom). Click the header of the Posting Type column to sort by posting type. Scroll down to the change from Balance Sheet to Profit and Loss. Verify that the last Balance Sheet and the first Profit and Loss accounts are correct. Note that Great Plains treats all unit and allocation accounts as Balance Sheet, but the category will be zero.
- Go to Reports >> Financial >> Accounts. Select All Accounts, then New. Type “all accounts” in the option field. If you are using 8.0 or later, put a check in the Inactive Accounts box. Select Destination >> OK >> Print. If you want to bring the balance forward, the account should be designated as Balance Sheet; if you prefer to close to a retained earnings account, the posting type must be set to Profit and Loss. You can make changes in the Account Maintenance window.
- Close the last period of the fiscal year (optional). Use the Fiscal Periods Setup window. If you use FRx, keep one period in the most recent historical year open.
-
Perform file maintenance on the Financial Series group of modules. Go to Microsoft Dynamics GP >> Maintenance >> Checklinks. Insert all of the financial series. Print the report to the screen.
Go to Tools >> Utilities >> Financial >> Reconcile. Reconcile the year you are closing.
- Verify the setup in the General ledger setup window. If you want to keep historical records, you must click to select the Accounts check box and the Transactions check box in the Maintain History area of the General Ledger Setup window.
- Make a backup. (Go to Microsoft Dynamics GP >> Backup).
- Print a final detailed trial balance.
- Print any required year-end financial statements, e.g. balance sheet, profit and loss statement, statement of cash flows, and statement of retained earnings.
- Set up a new fiscal year using the Fiscal Periods Setup window. Go to Tools >> Setup >> Company >> Fiscal Periods.
- Close the fiscal year. Click Tools >> Routines >> Financial >> Year-End Closing. Specify an account in the Retained Earnings Account field (this account is where the year’s profit or loss is closed to; if you want to distribute the retained earnings for the year to more than one account, you can specify an allocation account or a specific account segment). Specify the number that you want to use as the first journal entry number for the next fiscal year in the Starting Journal Entry field. Click Close Year to start the routine. If the progress window hangs at 50%, don’t reboot. It may take some time for it to close each account to retained earnings. As long as your hard drive is processing, let it continue. When the year-end closing routine is complete, the Year-End Closing Report is printed. This report lists the accounts that were closed and the transactions that were created to close those accounts. The Year-End Closing Report is part of the audit trail; save it for your company’s permanent records. The Year-End Closing Report cannot be reprinted.
- Close all fiscal periods for all series using the Fiscal Periods Setup window (optional).
- Adjust budget figures for the new year and print financial statements. You can adjust your budget using Excel-Base Budgeting, Budget Maintenance, or Single-Account Budget Maintenance. Print the Profit and Loss Statement to verify that profit and loss accounts were closed to the retained earnings account. Print the Balance Sheet to verify that balance sheet accounts indicate that the balances were brought forward. If you are using Advanced Financial Analysis to print your financial statements, you must update the report layout to reflect the current fiscal year. Click Reports >> Financial >> Advanced Financial. Select the financial statement from the Reports list | Open >>Layout. Double-click the first column heading. If the column type is set to Period Range, Year-to-Date, or Variable Year-to-Date, select the current fiscal year in the Year list | OK. Repeat for each column of each report.
-
Make a final backup.
Microsoft GP .ini Settings
SQL Script to find missing or deleted Journal No in Microsoft GP
Script
————————————————————————-
— Find Missing or deleted Journal Entry No in Microsoft GP
— Code by Venugopal G A
— mail me if you need query for other type of transactions or for any
— queries venuasg@gmail.com
————————————————————————-
If Object_Id(‘tempdb..#tempJV’) is Not Null
Drop table #tempJV
create table #tempJV
(
JVno int,
MJV int identity(1,1)
)
insert into #tempJV (JVno)
(select distinct JRNENTRY from GL20000 )
select wg.MJV as id_is_missing
from #tempJV wg
left join #tempJV ti on wg.MJV = ti.JVno
where ti.JVno is null
order by wg.MJV
SQL Script to Compare Bank Subledger balance to corresponding GL account balance
Script
—————————————————————————-
— Compare Bank Subledger balance to corresponding GL account balance in GP.
— Code by Venugopal G A
— mail me for any queries venuasg@gmail.com
—————————————————————————-
DECLARE @CHECKBOOKID VARCHAR(30)
SET @CHECKBOOKID = ‘FIRST BANK’ — CHANGE THIS TO YOUR CHECKBOOK ID
–Set Bank Type and get Bank Balance
Select ‘Bank’ as SubType, CURRBLNC as SubBal, 0 as GLBal
Into #SubtoGLCompare From CM00100 Where Chekbkid=@CHECKBOOKID
/*Get the GL total for this Account. Change the account below or optionally add additional
actnumbr parameters to tighten or broaden the account*/
INSERT #SubtoGLCompare (SubType,SubBal,GLBAL)
SELECT ‘Bank’ as SubType, 0 as SubBal, SUM(Perdblnc) AS GLBal
FROM GL11110 WHERE actnumbr_2 = ‘1100’ — Change this
Select SubType, sum(Subbal) as SubBal, Sum(GLBal) as GLBAL, sum(SubBal)-Sum(GLBAL) as Difference,
‘Difference is Subledger minus GL.’ as Instructions
from #SubtoGLCompare
Group by SubType
Drop Table #SubtoGLCompare
SQL Script to assign all items to all sites in Microsoft GP
Assign All Items to ALL Sites
——————————————————–
— Assign All Items to ALL Sites
— Code by Venugopal G A
— Below script will assign all the available inventory item
— to all available sites if they are not assigned already.
— mail me for any queries venuasg@gmail.com
——————————————————–
DECLARE @ITEM varchar(100)
DECLARE @LOCATION varchar(100)
DECLARE @ITEMAlreadyExist int
DECLARE ITEMMASTER CURSOR FOR
SELECT ITEMNMBR FROM IV00101
OPEN ITEMMASTER
FETCH NEXT FROM ITEMMASTER INTO @ITEM
WHILE (@@fetch_status -1)
BEGIN
DECLARE SITE CURSOR FOR
SELECT LOCNCODE FROM IV40700
OPEN SITE
FETCH NEXT FROM SITE INTO @LOCATION
WHILE (@@fetch_status -1)
BEGIN
SELECT @ITEMAlreadyExist = isnull(count(*), 0) FROM IV00102
where ITEMNMBR = @ITEM and LOCNCODE = @LOCATION
if @ITEMAlreadyExist = 0
BEGIN
INSERT INTO IV00102 VALUES(@ITEM,@LOCATION,”,2,”,0,0,0,0
,’01/01/1900′,”,’01/01/1900′,0,0,0,0,0,0,0,0,0,0,0,0
,’01/01/1900′,’01/01/1900′,’01/01/1900′,’01/01/1900′,0,”
,”,”,1,0,0,1,0,0,1,2,0,0,0,0,0,0,0,1,0,0,0,3,0,0,0,”
,”,”,”,”,”,”,”,1,1,”,1,1,0,1,1,1,0,0,0,0,0)
END
FETCH NEXT FROM SITE INTO @LOCATION
END
DEALLOCATE SITE
FETCH NEXT FROM ITEMMASTER INTO @ITEM
END
DEALLOCATE ITEMMASTER
SQL Script to assign all Items to a given Vendors
Assign All Items to a given Vendor
——————————————————–
— Assign All Items to a given Vendor
— Code by Venugopal G A
— mail me for any queries venuasg@gmail.com
— Change the vendor variable to your vendorid
——————————————————–
DECLARE @VENDORID VARCHAR(15)
DECLARE @ITEM VARCHAR(100)
DECLARE @ITEMDESC VARCHAR(100)
SET @VENDORID = ‘ACETRAVE0001’
DECLARE ITEMMASTER CURSOR FOR
SELECT ITEMNMBR, ITEMDESC FROM IV00101
OPEN ITEMMASTER
FETCH NEXT FROM ITEMMASTER INTO @ITEM, @ITEMDESC
WHILE (@@fetch_status -1)
BEGIN
INSERT INTO [IV00103] ([ITEMNMBR],[VENDORID],[ITMVNDTY],[VNDITNUM],[VNDITDSC])
VALUES(@ITEM,@VENDORID,1,@ITEM,@ITEMDESC)
FETCH NEXT FROM ITEMMASTER INTO @ITEM, @ITEMDESC
END
DEALLOCATE ITEMMASTER
Dynamics Tips 