Budgeting 101 Zubin Gidwani Dynamic Budgets Founder GPUGsummit
Budgeting 101 Zubin Gidwani, Dynamic Budgets, Founder #GPUGsummit
Zubin Gidwani Founder, Dynamic Budgets • Experience • Eight years working with Microsoft Dynamics GP • Reformed Assistant Controller and Financial Systems Manager - public company experience • Developed budgeting solution for Microsoft Dynamics GP - Top 50 Add On Products 2013 • Microsoft Dynamics GP solution • Microsoft Dynamics GP 9 – 2015: General Ledger, budgeting, analytical accounting, field service, project accounting, fixed assets, Wenn. Soft, Altec Doc. Link, implementation, upgrades, security, audit • Quiver of Reporting Tools: Excel Get. Pivot and Pivot. Tables; Microsoft SQL Server Management Studio, Microsoft FRx/Microsoft Management Reporter, Jet Reports Essentials, Biz. Net Software, Accountable Software BI Reporting • GPUG involvement • Active with GPUG since 2007, formerly on the GPUG Board of Advisors • Content Steering Committee for Microsoft Convergence and GPUG Summit • Inaugural GPUG ‘All-Star Award’ recipient #GPUGsummit
Poll Questions Your Experience with GP Budgeting 1. No exposure to GP Budgeting 2. Have worked with GP Budgeting in the past Expected Involvement with the Budget Module 1. General understanding so I know what my staff are doing 2. Will be hands on managing the data 3. Need to report budget data and want to understand how its managed Role 1. Executive 2. Controller / Accounting Manager 3. Budget Administrator 4. Financial Planning and Analysis 5. Department Manager 6. Other #GPUGsummit
Today’s Agenda for this audience… • • What does GP native budgeting allow us to do Step by Step instructions with GP Demo 1. Creating a budget 2. Password protecting a budget 3. Entering / editing budget values in GP 4. Single Account Maintenance / Budget Maintenance 5. Budget transaction entries 6. Applying calculation methods 7. Importing / exporting values to/from Excel 8. Exporting faster with SQL views 9. Combining budgets • • Features added in GP 2010 & GP 2013 Avoiding Budgeting Blunders #GPUGsummit
Budgeting in Dynamics GP Budget functionality within ERPs is limited to entering and editing period values Budget maintenance is reserved as an administrative function The application interface is not positioned or suitable for field staff to enter data in a distributed fashion #GPUGsummit
Budgeting in Excel with GP’s native export template #GPUGsummit
Budgeting with custom Excel templates Conditional Total (only include lines which do not have characters in the Skip Column) Total =SUMIF($P 8: $P 19, "", C 8: C 19) #GPUGsummit
Budgeting Platform Selection Criteria: • Excel based solution, or a more controlled environment? • Beware of the cost of per user licensing • Amount / cost required effort to install and configure (before even populating baseline data) • Budgeting and Reporting Single software Suite or 2 Best of breed solutions? • If being sold a Suite ask the vendor for separate quotes 1. Reporting only 2. Reporting + Budgeting #GPUGsummit
Benefits of Using Budgeting Software vs Excel: • Save Financial Analysts weeks of effort for by eliminating budget template management (3 weeks of a $90 K financial analyst = $10 K) • Save budget managers days of delays and accounting disruptions with transaction-level self-service lookups. #GPUGsummit
Case Study: Tools to simplify budgeting IT Manager needs to budget for annual software maintenance agreements • Which vendors? • How much did I spend last year? • In which month is it due? #GPUGsummit
Demo of Dynamics GP’s native Budgeting Functionality • Step by Step instructions with GP Demo 1. Creating a budget 2. Password protecting a budget 3. Entering / editing budget values in GP 4. Single Account Maintenance / Budget Maintenance 5. Budget transaction entries 6. Applying calculation methods 7. Importing / exporting values to/from Excel 8. Exporting faster with SQL views 9. Combining budgets #GPUGsummit
<<Live Demo>> #GPUGsummit
Features introduced in GP 2010 #GPUGsummit
Budget Transactions Post by Batch with User and Date Stamp #GPUGsummit
Budget Transactions (Audit Trails for Edits) Data entry is done by entering +/- adjustment amounts from the base values, there is no direct ability to enter the new number… #GPUGsummit
Budgets Transaction vs Importing via Excel GP supports the ability to Import budgets from Excel and Export Budgets to Excel. Imports bypass the Audit Trail functionality of the budget transactions but get the job done more efficiently for bulk edits. #GPUGsummit
Budget Transaction Edit Report #GPUGsummit
Budget Transaction Inquiry #GPUGsummit
Budget Transactions inquiry only works if you checkmark Maintain History first before posting a batch #GPUGsummit
Budget Transactions Notes (Journal Entry or Batch) #GPUGsummit
Combining Budgets #GPUGsummit
Pay Attention when choosing to delete the sub budget Which of these two are the source and destination budgets? #GPUGsummit
Preventing Budgeting Blunders #GPUGsummit
Top 10 things NOT TO DO with GP Budgeting 1) Never click Delete 2) Don’t allow access to Budget Maintenance / Single Account Budget Maintenance windows 3) Don’t assume functions behave consistently in similar Budget windows 4) Don’t EVER calculate single account changes from the Budget Maintenance Screen 5) Don’t assume all budget lines imported from Excel 6) Don’t experiment in production 7) Don’t ignore password protecting final budgets 8) Don’t forget to make Excel Backups 9) Don’t waste time with the Excel Export Wizard 10) Never underestimate the “creativity” of your staff #GPUGsummit
1) Never Click Delete When closing the Budget Maintenance screen, Delete will delete the entire Budget, not just the account in focus. There is no undo… when in doubt hit save… Microsoft Connect Product Enhancement Request: http: //tinyurl. com/cobveso #GPUGsummit
1) Never Click Delete continued… Single Account Budget Maintenance Deletes the account’s budget vs. unsaved changes Microsoft Connect Product Enhancement http: //tinyurl. com/cobveso Request: #GPUGsummit
2) Don’t allow access to Critical Windows • No one other than the Budget Manager and perhaps the GP admin needs access to: • Budget Maintenance • Single-Account Budget Maintenance • Budget Transactions • There are just too many ways to delete or corrupt the data, even if you are familiar with these windows #GPUGsummit
3) Don’t Expect Consistency In the Budget Transaction Entry screen Calculate does nothing with No Range specified If the Range is blank you are editing the single account in focus only Microsoft Connect Product Enhancement http: //tinyurl. com/86 fwmch Requests: #GPUGsummit
3) Don’t Expect Consistency 4) Don’t calculate single account changes from Budget Maintenance Window In the Budget Maintenance Window Calculate with No Range specified will affect ALL accounts If the Range is blank you are editing all accounts in the entire budget Microsoft Connect Product Enhancement http: //tinyurl. com/86 fwmch Request: #GPUGsummit
5) Don’t assume the Excel import was successful No validation or Error Logs When importing from Excel GP does not alert you when it skips unrecognized accounts. Microsoft Connect Product Enhancement Request: http: //tinyurl. com/82 l 49 nd #GPUGsummit
5 a) Don’t assume the Excel import was successful **Bonus Tip** If it goes too fast it probably didn’t work Don’t improvise your own Budget Import Template GP is picky and it wants its exact format Import from Excel with GP’s native export template Requirements: • The first 3 header rows exactly as generated by the system, with date range • Account Number, Description (can be blank), and Beg Bal Through Total. • Column totals in the last row • In GP 2010 SP 2 or lower leaving off the date range would reset your budget year to 1900 and cripple FRx/MR Columns #GPUGsummit
5 b) Don’t Assume GP Crashed while Exporting the Budget **Extra Bonus Tip** When exporting from Excel GP has a tendency to open the Save As pop-up window in the background and locks the application. Workaround: 1. Show Desktop. 2. Click on the GP program icon 3. Then you’ll see the Save As pop-up. Microsoft Connect Product Enhancement Request: http: //tinyurl. com/7 yjncb 7 #GPUGsummit
6) Experiment in TEST, not Prodution • Try not to shoot yourself in the foot in Production • Test in Test if unsure… #GPUGsummit
7) Don’t Overlook the Padlock Lock Your Budgets to Minimize Unwanted edits Restrict access to the Budget Maintenance and Single Account Budget Maintenance to only those who need the access Assign a password to prevent unintentional budget edits / deletions #GPUGsummit
8) Don’t forget to Backup Make Excel snapshots of your budget • Create budget Exports Frequently and Often • Save to Sharepoint or some other protected file repository • Save with datestamps in the names Why? • No need to admit guilt WHEN you corrupt or delete your budget • No need to ask IT to restore last night’s backup into test • **No need to export from test and re-import into production Why Not? • Do you really want ask the whole company to roll back to yesterday’s version of GP? #GPUGsummit
9) Don’t Waste Time Export Budgets Faster Native GP Excel Export: 8 minutes to export 7, 500 accounts This script: 10, 000+ records < 1 second Have your analysts or IT staff review the sample report I published on the Share My Code site. http: //tinyurl. com/ko 43 uvk #GPUGsummit
10) Never underestimate Wreck it Ralph How did they break that? If you Budget in Excel Templates: Never underestimate your staff’s ability to: • Unlocked excel templates/cells/formulas • Butcher formulas • Invent new GL codes • At the end of the day its all about data integrity • Budget Admins rarely have the time to be analytical if they have to be watchdogs and copy and paste jockeys • Consider an enterprise budgeting application! #GPUGsummit
Budgeting Survival Guide • 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… #GPUGsummit
Questions? Zubin Gidwani Dynamic Budgets zgidwani@dynamicbudgets. com 650 -332 -6651 #GPUGsummit
- Slides: 39