Weekly Tip's

1. Correct Journal Entries



2. Account Rollups



3. Macro Basics



4. Auto Populate Account Descriptions



5. Free Your Data With Excel Reports



6. Let Smartlist Alerts Warn You



7. Limit Records to Speed SmartList Processing



8. Reconcile Bank Account Daily



9. Speed up the Populate of the JE numbers on the Correct / Copy Journal Entry Screen



10. Track Use Tax in Payables or P.O.'s



11. Fix Your Auto Complete



12. Leverage the List View for Reporting



13. Sub-Ledger Reconcile - V10



14. Backup, Backup, Backup



15. Protect yourselft with Account Security



16. Budgets Can Now be Tracked across Years



17. Reduce the time it takes to create and distribute reports



18. Copy a Journal EntryUse Account Aliases



19. Prioritize Your Vendors



20. Use the new Historical Inventory Trial Balance Report



21. Business Alerts

Tip of the week from Brenda Hopkins – Week 22

Business Alerts

Business Alerts can be used to setup a notification when a set of defined conditions are met. These conditions can be defined on a specific database.

Business Alerts takes advantage of the query features of Microsoft SQL Server to monitor your data for specific conditions. For example, suppose that you want to be notified when a customer’s credit limit has been exceeded. In that case, you can use the Business Alert wizard to create a query that compares the credit limit amounts to the current amounts for your customers.

You can use the wizard to set up the schedule for the alert—that is, when, how often, and for how long you want Microsoft Dynamics GP to check whether the conditions you’ve defined exist. You also can specify a list of people to notify when those conditions occur.

You can create an unlimited number of business alerts, combining information from any of the tables in company or system databases. For example, you can compare the outstanding commissions per salesperson from the sales tables to commissions paid in the payroll tables.

Once you have created and enabled a business alert, the conditions will be checked according to the schedule you set up. Whenever the alert condition is found, an e-mail message is sent to the people or groups that you specified or a task is created for the user that you specified.

You can use any of the notification methods supported by your messaging system with Business Alerts. For example, you can notify alert recipients e-mail. You also can create tasks that will be displayed on their home pages and their task list whenever an alert condition is found.

Alert schedule and notification
Use the Schedule and Notification window to modify when, how often, and for how long Microsoft Dynamics GP will check whether the business alert conditions exist. Microsoft Dynamics GP will check your data for the conditions specified in the business alert formula at the intervals you set up here. You also can add or remove e-mail addresses or user IDs from the list of recipients for this business alert.

Tip of the week from Brenda Hopkins – Week 21

Use the new Historical Inventory Trial Balance Report

The new Historical Inventory Trial Balance (HITB) report is designed to help balance inventory to the GL regardless of the inventory valuation method.

The Historical Inventory Trial Balance (HITB) report shows your inventory value as of a specific date or date range and the value of the five different quantity types. You can use this report to tie your inventory balance to General Ledger based on a specific date.

If you want to use this report, you will need to use the HITB reset Tool for Microsoft Dynamics GP 10.0, Service Pack 2.

In order to track historical inventory information a new table was added in Dynamics GP 10.0, Service Pack 2. The tool will populate this table and it will also find a starting point where the value of your Inventory and General Ledger are the same.

Before running the tool, you will want to complete the process on a test company first.

Contact BroadPoint for help downloading this tool.

Tip of the week from Brenda Hopkins – Week 20

Prioritize Your Vendors

• Payables Management includes a 3 digit alphanumeric field used to prioritize vendors.



• A simple ABC is generally more effective than a bunch of 3 digit numbers
  For example: If you are a Newspaper Company ‘A’ could be used for highest priority vendors like a newsprint supplier.



• Use this to actively decide which vendors are critical now, not when a crisis hits.



• This field can be used to select vendors during he check selection process.
  
  Cards > Vendor > Options
  Payment Priority Field

Tip of the week from Brenda Hopkins – Week 19

Use Account Aliases

Account Aliases are shortcut names for accounts and are setup in Cards > Financial > Account.

In transactions, instead of entering an account, click the blue arrow next to the account tile and enter the alias. If you're using a long account structure, aliases provide a quicker method of data entry.

Tip of the week from Brenda Hopkins – Week 18

Duplicate a Journal Entry

Let’s say that last month you keyed a journal entry that was 100 lines long. Then next month, you find out that you need to enter this same journal entry again this month but with a few changes. Is there an easy way to key it again? Even with a few changes?

SURE!

By using the Copy a Journal Entry functionality found in Transactions>Financial>General>Copy.

In version 8.0 Microsoft introduced Journal Entry corrections and copies. You now have the ability to copy a journal entry, easily.

In Transactions->Financial->General, select Copy
You now have an option to Copy a Journal Entry.

You will need the Journal Entry number (you can look it up) and year of the entry you want to copy.

**The look up on the JE Number can be time consuming; it would be faster if you know the Journal Entry Number.

By default, Copy only works for transactions that originated in the General Ledger. However, the administrator can change this behavior.

Tools>Setup>Financial>General Ledger now has new check boxes to control this functionality.

Voiding/Correcting of Subsidiary Transactions
Mark this option to allow transactions originating in other modules to be copied in General Ledger.

Tip of the week from Brenda Hopkins – Week 17

Reduce the time it takes to create and distribute reports

SQL SERVER REPORTING SERVICES (SRS) helps summarize your data in Web-based charts and graphs. Build reports to support specific business activities, and share vital information across your organization.

Access reports easily from within Microsoft Dynamics GP or the Web. Let users log into the web site and access reports themselves, these reports can be printed or exported to excel.

ONLINE VIEWS offer forecasting insights that are easy to share across your organization or with designated vendors or contractors.

Microsoft SQL Server Reporting Services offers over 60 reports, you can use these sample reports either as-is or as templates for designing new reports.

Examples:

• General Ledger Detail and Summary Trial Balance Reports.
• Receivables Aged Trial Balance Report and Detail Historical Aged Trial Balance Reports.
• Payables Aged Trial Balance Report and Detail Historical Aged Trial Balance Reports.
• Open Purchase Orders Report: Purchase orders that have not been closed or canceled per vendor or for all vendors, including list views for the number, status, and date of each purchase order, assigned vendor, and line item detail.
• Open Sales Orders Report: Unposted sales orders for a range of customers, including list views for the sales order number and date, customer information, requested ship date, amount remaining, assigned salesperson, and line item detail.
• Customer Profitability Report: Sales, total profit, and gross margin percentage per customer for the selected year. Expandable customer ID offers a view of sales, total profit, and gross margin percentage by document number.
• Inventory Value by Site Report: Bar graph of the value of inventory by site and the total inventory value for the company. Clickable inventory site bars display Inventory value by Site. Use the Item Number Report to view items and their total value for that site.
• Item Quantity Back Order Report: Quantity of items that are back ordered, quantities allocated to purchase orders, and item quantities available for all sites.port to view items and their total value for that site.
• Employee wage and Hour Report: Employee Pay Codes with hours and wage for each pay code.
• Employee Pay History Report: Select Pay Codes and Departments to include showing historical employee pay history.
• Employee Earnings Register: Snap shot of employee summary earnings.
• Department Wage and Hour Report: Select departments and Pay Codes to include, hours and total paid for each department.

Tip of the week from Brenda Hopkins – Week 16

Budgets Can Now be Tracked across Years

There are times when a fund or grant does not start on the first day of the fiscal year or end on the last day of the fiscal year. The life of the fund or grant sometimes also crosses over fiscal years. This feature provides the ability to budget based on a start and an end date in Microsoft Dynamics GP Release 10.0.

Not for Profit and Public Sector organizations often have the need to track funds and grants to associate expenses against money received. Funds are typically tracked as a segment of the General Ledger account segment in Microsoft Dynamics GP, and grants are often tracked as a Transaction Dimension Code within Analytical Accounting.

A few changes have been made to the budgeting windows in Microsoft Dynamics GP Version 10 to accommodate the ability to enter budget start and end dates. The Budget Maintenance window was updated with these changes.

Cards > Financial >Budgets

To enter start and end dates for a budget, you will need to select the Date Range in the Based On field.

If Fiscal Year is selected in the Based On field, budget functionality works as before, and no budget start and end date can be entered.

Budgets created in the Single Account Budget Maintenance window, using the Budget Wizard for Excel or in Analytical Accounting Budget Maintenance can also be based on a fiscal year or date range and allow for start and end dates to be entered for budgets.

Tip of the week from Brenda Hopkins – Week 15

Protect yourself with Account Security

• Account Security limits the accounts that users can access.
• It requires setup via Organizational Structure.
• If you don’t set up Organizational Structures prior to turning this on, it appears that the chart of accounts has been wiped out.
• Know what you are doing before activating this.

Account security is designed to work with the Organizational Structure feature to limit access to certain accounts. Some organizations have a very complex account structure and it makes picking the right account easier if you limit a department to only be able to see their own accounts.

Organizational Structure provides information to define as many as four layers of structure within your business, and then to set up specific divisions, departments or other organizational groups. You can create relationships among different groups. You can assign accounts and users to different parts of the organizational structure.

Account Security is turned on with a check box in Tools->Setup->Company->Company.

You can see in the example below that this user is only allowed to see Department ‘200’ only.

Tip of the week from Brenda Hopkins – Week 14

Backup, Backup, Backup

If you are not backing up use the built in backup functionality in Microsoft Dynamics GP. This won’t get everything, you will still need to backup custom reports and forms, the Integrations Manager database and FRx reports, but backing up the database is a good start.

Dynamics GP 9.0 and 10.0 allows you to schedule backups from within the application. You'll need to be logged in to GP as an administrator and you'll need to be on the server.

Go to File> Backup [v9] / Maintenance > Backup [v10]

Enter the system password if you've set one.
When the backup window opens, pick Schedule.

• Name the backup schedule
• Pick the database to backup or backup the system databases
• Enter the backup path (this must be a path on the local server)
• Select the days to backup and the time. The time is entered is in a 24 hour format
• Pick how often to delete old backups
• Click Save. SQL Agent MUST be running when you click save
  
  

  

This process creates a job in the SQL agent with the information entered. You'll still need to move the backups off the server for real protection, that's easy with today's portable hard drives. There's no excuse for not backing up your Dynamics GP installation now.

Tip of the week from Brenda Hopkins – Week 13

Sub-Ledger Reconciler

Having issues reconciling the Payables Management or Receivable Manage sub-ledgers to the General Ledger? Version 10 has a new feature that helps with this.

Benefits:

• Automatically match transactions for users
• Identify any transaction discrepancies between the sub-ledger and the General Ledger
  

The Feature DOES NOT automatically do any of the below task to balance the sub-ledgers to the General Ledger:

• Fix imbalances between the sub-ledgers and the General Ledger
• Correct posted transactions
• Back out posted transactions
• Void existing transactions
• Recreate new adjusting transaction
  

Reconcile to GL

You can select one or more Accounts Receivables or Accounts Payable Accounts from the General Ledger that hold the posted outstanding balances. The balance of all the accounts entered in this window is the amount to which the Sub-ledger balance is reconciled against.

For example you could have the Accounts Payable account and the Purchase Discounts Available Accounts.

Reconcile Process

The balances Include:

• Beginning balances
• The difference between the beginning balances
• Transactions entered during the date range specified
• The total ending balance
• The difference between the ending balances

Microsoft Dynamics GP matches transactions from Payables or Receivables Management to the distribution line in the General Ledger.

• Transactions that exist in both ether sub-ledger and General Ledger
• Transactions that exist in either the sub-ledger or General Ledger, but not both
• Transactions that potentially match, but don’t meet all the matching criteria

Reconcile Report

Analysis of the reconciliation results is an important aspect of the process. In order to provide the appropriate tools to perform an analysis the results are sent to an Excel spreadsheet. Excel provides the typical functionality of an spreadsheet.

You can save each spreadsheet to document your exceptions and how they were handled by adding a comment to your Journal Entry noting the action taken to fix the problem.

Tip of the week from Brenda Hopkins – Week 12

Leverage the List View for Reporting


Have you ever scrolled through all report windows for a particular module looking for that special report that you know is there but you just can't find it? Something like the Historical Stock Status which is under Activity instead of History.

You can now search for a report, including all the options you have set for various reports.
V9 Select Reports >Report List
V10 Home >Administration>All Reports List

A box will come up with all the reports in the system. You can limit this list to various modules, search for reports and sort the list on the different criteria. From the Actions menu you can create new report options, review the option selections for a particular report and even import/export reports.

Tip of the week from Brenda Hopkins – Week 11

Fix Your Auto Complete


Auto Complete finishes your entries for you.

It’s great until you misspell something and are stuck with it forever.

To get rid of it, start typing until the error shows up in Auto Complete.

Right click and hit Remove from List.

Tip of the week from Brenda Hopkins – Week 10

Track Use Tax in Payables or P.O.’s

If you are not charged sales tax by a vendor and need to self-remit use tax for a P.O. use these steps:

Let the system calculate Sales Tax.
Offset the tax amount in the Trade Discount field.

Point the trade discount Amount to an accrued use tax account.
Point the sales tax Amount to the appropriate expense or tax account.
The Vendor gets paid properly because of the credit and tax gets recorded.

Use SmartLists to track and report use tax data for later remittance [Company > Tax Detail]

Tip of the week from Brenda Hopkins – Week 9

Speed up the Population of the JE numbers on the Correct / Copy Journal Entry Screen

Looking up Journal Entry corrections is slow because of the number of transactions.

GP provides an index for that table that can speed look-ups.

A small SQL Script to apply a non-clustered index to the GL20000 and GL30000 tables and this solves the problem.

Before running this Script make sure that you have a complete backup copy of the database that you can restore if a problem occurs.

1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

2. Click Connect.

3. On the File menu, point to New, and then click Query with Current Connection.

4. Copy the following code in the query pane.

CREATE NONCLUSTERED INDEX [SEE_GL30000PerformanceCustom] ON [dbo].[GL30000]
( [Back_Out_JE] ASC,
[HSTYEAR] ASC ) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [SEE_GL20000PerformanceCustom] ON [dbo].[GL20000]
( [Back_Out_JE] ASC,
[OPENYEAR] ASC) ON [PRIMARY]
go

5. In the database list, click the company database that you want to use, and then click Execute.

6. Repeat step 5 for each company database in which the problem occurs.

Tip of the week from Brenda Hopkins – Week 8

Reconcile Bank Account Daily

• With today’s internet access, there is no reason not to balance the bank account daily.


• Daily balancing provides up to date cash position, float information and speeds month end processing since bank balancing is simply one more day.


• Download your bank info from your bank’s website.


• Start bank reconciliation process and reconcile it to zero.


• DON’T push the reconcile button.


• Save the reconciliation and come back tomorrow.

Depending on the size of your company and if you own Electronic Reconciliation module you have different options so that everyone can Reconcile their Bank Account Daily.

Option 1:
Use the Electronic Reconciliation module and get a daily file from your bank.

Option 2:
Using your bank's website download the daily transactions and manually clear the files in the Bank Reconciliation module.

Steps:

1. Go to your bank's website, get today's posted balance and print out the daily transactions back to when you last got your statement.


2. Start a bank reconciliation process in Microsoft Dynamics GP.


3. Enter today's posted balance for the bank statement ending balance and use today's date for the dates.


4. Check off cleared items and balance the statement to a zero difference.


5. When finished, click OK. DON'T CLICK RECONCILE! Clicking OK will save the reconciliations.


6. On the next day, repeat these steps for 1 day's worth of transactions.


7. At month end, verify the totals with the statement and then click reconcile. The month end bank reconciliation process is now minutes, not hours.
   

Hints:

1. There may be a few days at month end where you can't reconcile into the next month until you have received the final statement. In this case you can either rely on the website as the final reconciling document or have a few days to make up early in the next month. Both options have problems, I've seen banks have website issues that cause the statement and website to not match, but this is pretty rare. However, delaying a few days tends to break the nice daily balancing rhythm you develop. There's no perfect option, pick whichever one fits your business requirements.


2. Make sure you know where you left off from the day before. Often bank websites won't let you print just one day, you have to print the whole screen which can overlap days.
   
   
   
   

Tip of the week from Brenda Hopkins – Week 7

Limit Records to Speed SmartList Processing

If you look at the bottom of a Smartlist it tells you the number of records returned (notated in the bottom left). If that's a round number, like 1,000, you've probably hit the limit (notated in the bottom center) and don't have all the records you need.

This can be changed on a case by case basis, go to the Search button and change the Maximum Records field. For instance on the Account Transaction if you know you will have under 5,000 records you can change the Maximum Records to show 5,000. You can then save this view as a Favorite and it will always search for the new maximum amount.

You can also make a global adjustment to limits, go to the Microsoft Dynamics GP menu > Tools > Setup > System > Smartlist Options. For instance change the Maximum Records to 5,000. This will be the new default for the Account Transaction SmartList.

You can also mark what fields you want to default in the SmartList View.

This can be used to limit the number of records that default in until you have the correct information then you can manually change the Maximum afterwards on a case by case basis. This can be helpful with performance if you have these limits too high to begin with.

Tip of the week from Brenda Hopkins – Week 6

Let SmartList Alerts Warn You

How Many Customers are Over their Credit Limit
1) Setup your Smartlist

2) Pick Favorites and name the Favorite, example ‘Customer Over Credit Limit

3) Click Add and select ‘Add Favorite and Reminder’

4) Select ‘Number of Records’, example ‘ Greater Than 0’

5) You can set reminders based on the number of records or the total of the column

6) Click OK

Specific Customer Balance was Over $1,000
1) Setup your Smartlist

2) Pick Favorites and name the Favorite, example ‘Astor Suite > $1000.00’

3) Click Add and select ‘Add Favorite and Reminder’

4) Select ‘Total of Column’, example ‘ Customer Balance > $1000.00’

5) You can set reminders based on the number of records or the total of the column

6) Click OK

You can setup a reminder for an existing Smartlist
Go To Tools >> Setup >> User Preferences >> Reminders button

Click on New; select the Smartlist Favorite to see all the existing Smartlist options to choose from.

When you delete the Favorite, it deletes the reminder

Reminders appear on the Home Page

Tip of the week from Brenda Hopkins – Week 5

Free Your Data with Excel Reports



The new Excel reports in Microsoft Dynamics GP 10 are really pretty cool and you don't need MOSS 2007 to use them. You can use a network share to let your organization use the Excel reporting features.



To setup the default reports you need to do 2 steps, deploy the connector and deploy the reports.

In GP 10 pick Microsoft Dynamics GP >Tools-Setup > System > Reporting Tools Setup.

Step 1 - Go to the Data Connections Tab

• In the System Level Data connections box enter a shared network location to deploy the connectors to.
• Do the same thing for the User Level Data connections. This can be a local directory.
• Check Enable Data Connection Deployment.
• Check Deploy Data Connections for all existing companies if you want to run Excel reports off all your companies. Otherwise, when you deploy, you'll have to pick a company.

Click Run Deployment and Print to the screen the reports for review

Step 2 - Go to the Reports Library Tab

In the System Level Reports box enter a shared network location to deploy the reports to, this is where users will access the shared excel reports and network security can applied to this folder.

Do the same thing for the User Level Data connections. This can be a local directory.

• Check Enable Excel Report Deployment.
• Check Deploy Excel Report for all existing companies if you want to run Excel reports off all your companies. Otherwise, when you deploy, you'll have to pick a company.

Click the Run Deployment button and Print to the screen the report to view.

How to access the Shared Excel Reports

1. You can navigate to the shared location on the Network and open one of the Excel reports.
   
2. Within Dynamics GP, go to the Navigation Pane on the lower left and pick Administration. Click on Other Reports and wait for them to load.

Pick a report and double click it or Click on VIEW. The report opens nicely (and quickly in Excel).
You may need to enable data connections. If a box pops up, enable data connections and proceed on.

In Excel 2007 the report shades every other row and provides filter handles on each column.