4 Ways to Maximize excel based budgeting in GP

Do you report actual to budget variance and always have a difficult time getting your budget into GP? Below are some suggestions on how to best use excel based budgeting within GP. There are options for users who have budget workbooks outside of GP and just need to load the data as well as users that are just starting the budgeting process and how to build templates and get historical data out of GP for budgeting.

1. Create a budget template using excel based budgeting within GP for your budget cost centers.
   1. Go to Cards/Financial/Budget and select new using excel based budgeting
   2. If you create a blank template for each budget cost center you can export it each year and just update the periods across the top of the excel sheet to import back into GP
2. Importing budgets into GP from Excel
   1. Two options to use to get an existing budget template into GP
      1. If you have an existing budget template insert a blank budget template into your existing workbook and link you existing workbook to the GL load template (blank budget template). When the budget for that cost center is completed you can run import from excel and select that sheet to import.
         1. Maintenance on this tab annually would be to update the periods across the top and add any GL accounts that are needed.
      2. If you have an existing budget workbook you can export a blank excel budget template from GP and link this to all your separate budget workbooks.
         1. Once your budget is complete you can go into excel and update the links. This will refresh the budget data in your blank template and you can save the excel sheet and use the GP budget import from excel tool.
3. When you are creating a new budget within GP you have the following options with Excel
   1. Open Year Percent
      1. An open year percent template calculates a new budget on the actual balances of any year that hasn’t been closed by increasing or decreasing the open-year balances by a certain percentage.
   2. Other Budget Percent
      1. Other budget percent calculates amounts from another budget by increasing or decreasing by a set percentage for the new budget.
   3. Historical Year Percent
      1. Historical year percent calculates a budget based on a historical year’s actual balances. You can calculate a budget by increasing or decreasing the amounts by a specified percentage. This calculation method can be selected only if you’re keeping account history.
   4. Blank Budget Template
      1. Blank budget template creates a blank budget in Excel. A formatted worksheet will be created with accounts, descriptions and periods. However, no budget amounts will be included.
4. When you create a budget template using the excel based budgeting tool within GP pull in prior year actual data for variance
   1. When creating your new budget template in GP using the excel based budgeting you can select to pull prior years actual data into a new tab in your formatted excel worksheet.
   2. You can pull in historical and open year’s data and there is no limit to the number of years that you choose to pull in.
   3. You can use these tabs in your worksheet to build formulas in your budget template

Colleen Williams | COO

Colleen graduated with a Bachelors of Science Degree in Accounting from California State University San Bernardino.  She has extensive accounting experience over the past years working as staff accounting and controller.   Colleen has over 10 years Great Plains experience which includes many implementations for private companies.  She also has extensive experience with Business Intelligence, budgeting and report writing while working on BI360, QuickBooks, Oracle, PeopleSoft, Timberline and Solomon.  Colleen has worked in various industries that include: real estate, property management, timeshare sales, publishing and insurance.  These various industries have given her the necessary experience to handle large company consolidations and report writing, budgeting and forecasting, project accounting, sales invoice processing, and fixed assets.