Excel-Based &Native Budgeting for Dynamics GP

1. Excel-Based &Native Budgeting for Dynamics GP Bob McAdam | Tribridge Zubin Gidwani | Dynamic Budgets

2. Agenda • New budgeting items – GP 2010 • Combined Budgets into a ‘Master’ • Budget Transactions • Watchout for these Gotchas • SQL Scripts for Quick Budget Exports • Excel Tips and Tricks • Q&A

3. Combining Budgets

4. Budget Transactions

5. Budget Transactions

6. Budget Transactions

7. Budget Transactions

8. Budget Transactions

9. Budget Transactions

10. Watchout for these Gotchas What’s the difference between these two screens?

11. Budget Transactions If the Range is blank you are editing the single account in focus only From the Budget Transaction Entry screen are you making direct edits and calculation method edits to a single account?

12. Watchout for these Gotchas If the Range is blank you are editing all accounts in the entire budget From the Budget Maintenance screen can you make direct edits and calculation method edits to a single account?

13. Watchout for these Gotchas If you want to abort an edit to an account which button should you press to exit the screen?

14. Watchout for these Gotchas If you want to abort an edit to an account which button should you press to exit the screen?

15. Lock Your Budgets to Minimize Unwanted edits

16. Watchout for these Gotchas Which of these two are the source and destination budgets? If the {Combine with BudgetID} is Locked and you elect to delete it after combining, will it do so?

17. Watchout for these Gotchas When importing from Excel what will GP do with Budgeted Accounts which do not exist in the Chart of Accounts?

18. Moral of this Story: • When in Doubt, hit SAVE • Create budget Exports Frequently and Often • Use a SQL dump to get the data out faster • Restrict Security access to: • Budget Maintenance and • Single-Account Budget Maintenance Windows • Lock your budgets to minimize unwanted “Edits” • Try not to shoot yourself in the foot in Production • Test in Test if unsure…

19. SQL Script for Budget Export The Native Export Budget to Excel process can be very, very slow with large budgets. The following script will allow you to do an extract of thousands of accounts in a matter of seconds. Disclaimer: SQL Scripts can damage or destroy data. Use these scripts with caution on a test system first. Backup your database before using. Use the following script is at your own risk. We take no responsibility for any harm or data loss caused by these scripts nor do we warranty the validity of the results. The script is provided to assist your budgeting activities, and must be independently validated against core system reports should you attempt to use these for final data extraction.

20. SQL Scripts for Budget Export Budget data is natively stored in DynamicsGP as one row per period amount, the first view joins the budget header (GL00200) and details (GL00201)tables with the GL00100 & Gl00105 to provide full account strings and account names CREATE VIEW vw_BudgetData as SELECT TOP (100) PERCENT 1 AS SOURCE, CAST(DB_NAME() AS char(5)) AS CompanyDB, a.BUDGETID, d.YEAR1 AS Year, a.PERIODID, a.BUDGETAMT, a.ACTINDX, RTRIM(b.ACTNUMST) AS ACTNUM, c.ACTDESCR FROM dbo.GL00201 AS a INNER JOIN dbo.GL00105 AS b ON a.ACTINDX = b.ACTINDX INNER JOIN dbo.GL00100 AS c ON b.ACTINDX = c.ACTINDX INNER JOIN dbo.GL00200 AS d ON a.BUDGETID = d.BUDGETID ORDER BY a.BUDGETID

21. SQL Scripts for Budget Export Budget data is natively stored in Dynamics GP as one row per period amount. This second view is a crosstab function to display 12 periods of budget values for one account in a single row (record). Create VIEW vw_BudgetCrossTab as SELECT TOP (100) PERCENT CompanyDB, BUDGETID, RTRIM(BUDGETID) + ' ' + ACTNUM + ' ' + ACTDESCR AS ASSUMPTION, Year, ACTINDX, ACTNUM, SUM(CASE PERIODID WHEN 0 THEN BUDGETAMT ELSE 0 END) AS P00, SUM(CASE PERIODID WHEN 1 THEN BUDGETAMT ELSE 0 END) AS P01, SUM(CASE PERIODID WHEN 2 THEN BUDGETAMT ELSE 0 END) AS P02, SUM(CASE PERIODID WHEN 3 THEN BUDGETAMT ELSE 0 END) AS P03, SUM(CASE PERIODID WHEN 4 THEN BUDGETAMT ELSE 0 END) AS P04, SUM(CASE PERIODID WHEN 5 THEN BUDGETAMT ELSE 0 END) AS P05, SUM(CASE PERIODID WHEN 6 THEN BUDGETAMT ELSE 0 END) AS P06, SUM(CASE PERIODID WHEN 7 THEN BUDGETAMT ELSE 0 END) AS P07, SUM(CASE PERIODID WHEN 8 THEN BUDGETAMT ELSE 0 END) AS P08, SUM(CASE PERIODID WHEN 9 THEN BUDGETAMT ELSE 0 END) AS P09, SUM(CASE PERIODID WHEN 10 THEN BUDGETAMT ELSE 0 END) AS P10, SUM(CASE PERIODID WHEN 11 THEN BUDGETAMT ELSE 0 END) AS P11, SUM(CASE PERIODID WHEN 12 THEN BUDGETAMT ELSE 0 END) AS P12 FROM vw_BudgetData GROUP BY CompanyDB, Year, BUDGETID, ACTNUM, ACTINDX, ACTDESCR ORDER BY CompanyDB, Year, BUDGETID, ACTNUM

22. Excel Tips & Tricks • Run a Chart of Accounts extrtact and do a vlookup of budget account numbers to ensure they exist in the COA, as GP will not warn you that unrecognized accounts were skipped during import. • Compare your Financial Report of Imported Budget Values to your Import Writeback file total. • Never underestimate how creatively your users can break your locked and protected budget templates. • Provide a skip or exclude functionality with conditional totals to allow for alternative scenarios, references, and line item visibility for your calculations.

23. Excel Tips & Tricks Conditional Total (only include lines which do not have characters in the Skip Column) Total =SUMIF($P8:$P19,"",C8:C19)

24. Thanks for joining us! • Please fill out your evaluation • CPE credit is available for GPUG Summit 2011 • Follow-up questions? • See you in Houston at Convergence 2012 next March • See you in Seattle for Summit 2012 • Browse out to www.gpug.com for more information

25. Feel Free to Contact Us! • Bob McAdamTribridge • bob.mcadam@tribridge.com • Zubin Gidwani Dynamic Budgets • zgidwani@dynamicbudgets.com • 720.515.1565