CenterPoint® Accounting - Create a CenterPoint Budget in Microsoft® Excel

Related Help

Create a CenterPoint Budget in Microsoft® Excel Video - Duration 16 min 43 sec

View or Print as PDF for CenterPoint Accounting for Agriculture

View or Print as PDF for CenterPoint Accounting and CenterPoint Fund Accounting

Creating or modifying budgets in Microsoft Excel is a popular process because the features in Microsoft Excel can make the process more efficient. If you want to perform budgeting within Microsoft Excel, CenterPoint can create a file to use as a starting point. Once the file is created, it will provide you with a working file to develop your budget. Once the budget is complete, the spreadsheet’s values can easily be imported into CenterPoint. The information within CenterPoint allows you to perform actual-to-budget comparative reporting.

For more information on creating a budget within CenterPoint (vs. creating it in Microsoft Excel), please refer to the Understanding and Using Budgets topic.

Step A: Create a New Budget

Step B: Prepare the Excel Spreadsheet

Step C: Working with the Excel Spreadsheet

Step D: Importing the Excel Spreadsheet into CenterPoint

This document uses the following scenario as an example: A company wants to enter a budget for 2021 that includes budgeting by profit center. They would like to enter their budgeting figures into Excel and bring that information back into CenterPoint so that they can do Actual to Budget Comparisons

Create the Budget

  1. Select Setup > Budgets.
  2. Select File > New Budget.
  3. In the Budget Name box, enter Imported Yearly Budget.
  4. Select your Company.
  5. In the From/To boxes enter the date range of 01/01/2022-12/31/2022.
  6. In the Balance From box, select December 2021.
  7. Select the Entry Mode tab, select either Open Entry or Journal Offset to Cash.
Note: The import will not import activity if the Default Entry Mode is set to Detailed Transaction Entry.
  1. Click OK.

Prepare the Excel Spreadsheet

In order to prepare the spreadsheet, you have to have something unique about your accounts. The accounts either have to have a unique account number, abbreviation or name. This also includes any inactive accounts.

  1. Once the blank budget is created, select File > Export/Import Data > Prepare Excel Spreadsheet. A wizard will walk you through each step of the process. Click Next on the Create a new budget import screen.

  1. .In the Specify the Excel Spreadsheet you wish to use to import data screen, in the Budget Import Folder box, select which folder to store the spreadsheet in. The first time you use the import process, the folder will be your Documents folder. The last folder used will display.
  2. Select Create a New Spreadsheet and enter a name, for example Alpine Sports 2023 Budget Import.
  3. Choose which format to use for the Excel file. The default, Excel Workbook (*.xlsx) should be used for all computers that have a version of Microsoft Office higher than 2003. If you are using a much older version of Excel that stores files as .xls files instead of .xlsx then change the option of what type of file to create. Note: When the file is created, it will be located in your users document (or my documents) directory on your computer. Click Next.

  1. Select the date range . If importing annual budget amounts, only select January (or the first fiscal month) of the Budget Year. Although this is normally an entire fiscal or calendar year, any date range may be selected. Normally, these dates would correspond to the dates used when the budget was created. Click Next.
  2. The Specify Mapping Options screen allows you to determine which information is unique. In our example, our accounts have unique account numbers and our profit centers are unique by name. So, in the Map accounts using box select Account Number and in the Map Profit Centers using box, select Name.

  1. In our example, we will not be budgeting by any other management details, so we will not define what is unique for the other management details. Click Next.

  1. In the Select the columns to display screen, specify any additional columns of data you'd like to include in the spreadsheet. When the spreadsheet is created it will be created with a list of accounts down the left-hand side. In our example, that list of accounts will be repeated once for each profit center you select. Since we selected in the Sample Database that the Account Numbers were unique, that is what is going to display for each account. We are going to add the Account Name by moving it to the right- hand side and positioning it under the Account Number. The Up and Down arrows to the right will allow you to adjust the order of the columns. Click Next.

  1. In the Specify how the data is sorted on the spreadsheet screen, choose the order that you want the columns to appear. You can only sort by columns that you have selected to display. The arrow buttons on the right will allow you to move a selected column up or down in the order. Click Next.
  2. In the Included Information screen, you can filter for specific management details, by clicking in the Selected Criteria box. The Filter screen allows you to filter for specific accounts and profit centers to include in this budget. For example, if you are just budgeting for Income Statement activity, then you would only want to select Income Statement accounts and not include Balance Sheet accounts. In our example, filter by Account Type and select Revenue, Cost of Goods, and Expenses to filter for only Income Statement activity. Then select the Product Sales, Memberships, Service Sales, Returns & Allowances, and Freight Income accounts and filter by All profit centers. Click OK.

Select Only include rows that match all of the production detail specified to display only the selected in the Filter screen.Click Next.

  1. The Review Import screen will display the CenterPoint Import Specs tab with the information used to define the import and the CenterPoint Import Data tab with the information that CenterPoint will send to the Excel spreadsheet.

  1. Click Next.

Working with the Excel Spreadsheet

  1. Open the spreadsheet in Excel as you would any other spreadsheet.
  1. The first tab will display all of the Import Specifications that you selected when you were creating the spreadsheet. You will not need to make any changes on this tab.
  2. The second tab, labeled CenterPoint Import Data is where you will enter your budget values.
  3. On the left margin, the list of accounts repeated once for each selected profit center will display.
  4. There will be a column for each of the periods that you selected in your date range when you created the spreadsheet.
  5. Enter your budget values into the appropriate row and column.
  6. Formulas can be used in the cells. CenterPoint will import the resulting value, not the formula.
  7. Unused or blank rows can be deleted.
  8. Additional tabs that perform calculations and point a cell on the CenterPoint Import data tab to a cell in another tab can be added. CenterPoint will only be looking at the CenterPoint Import Data tab when it performs the import so it will ignore all other tabs.
  9. After all of your budget values are entered, save the budget and exit Excel before continuing to the next step.

Importing the Excel Spreadsheet into CenterPoint

  1. Select Reporting Tools > Budgets. On the Open a Budget screen, select thebudget that was created in the Create the Budget section above, and click OK.
  2. Click OK at the Budget Properties screen.
  3. Select File >Export/Import Data > Import Data from Spreadsheet.
  4. The following screen will display if your budget is set to the Account Entry or Detailed entry mode:

  1. In order to continue with the import, you will need to change to either the Journal Entry Offset to Cash or Open Entry mode.
  2. Click Next.
  3. Select the spreadsheet to import. Only those spreadsheets created or prepared from CenterPoint will be displayed in the list. Click Next.
  4. Select the Replace existing budget data check box to remove and replace all data in the existing budget with the data imported from the Excel spreadsheet rather than adjusting the existing budget with the imported data. This selection will improve the efficiency of importing budget data from an Excel spreadsheet.
  5. Review the spreadsheet and verify that everything is correct. The values on the CenterPoint Import Data tab can be modified. Then click Import to bring the budget numbers into CenterPoint or click Cancel to exit without importing the budget.
  6. Click OK at the Import Complete message. The imported budget numbers will be displayed in the Budget screen. Once the data is imported, it can be modified within CenterPoint in the same manner as budget numbers that were entered manually.

Document: 3192

Concept Link IconSee Also