Budget Data Load Process

Overview

There are Budget Data Load Templates for each type of budget model (AGYOPA, AGYOPB, AGYOPC, AGYREV and AGYTRF) provided by OBM. They are accessible through the myOhio.gov website: FIN Home > OBM Forms >OAKS Budget and Planning Module (BPM) > BPM Spreadsheet Upload Template 2018 Biennium.

Understanding Importing Line Items

The Budget Data Load Import process enables the user to import multiple AMTPER method line items at one time. This process imports line item data (ChartField strings) from a .csv file into the specified business unit, planning model, scenario, activity, planning center, and budget version. Keep the following considerations in mind when using this process:

• All locks to the model need to be released prior to running the engine and the model must be in the Released state.
• Only method amounts are updated (adjustment and allocation amounts are not updated).
• Target line items (ChartField strings) must have the method AMTPER (Amount Per Period) or, for new line items, allow the AMTPER method to be assigned.
• Any line items that have been manually deleted after the model was released will be reintroduced by the import process if the combination exists in the input .csv file.
• Deleting lines from the budget data load will not delete lines that already exist in BPM. It is necessary to go back to My Planning Workspace to revise or delete existing ChartField strings.
• This process locks the Planning Center. The Planning Center will automatically unlock when the upload process is completed.
• The system does not prevent multiple concurrent processes from writing data to the same planning center; if multiple import files for the same planning center are being processed, they should be run sequentially.

Tips for Using the Budget Data Load

• Always save the working version as an .xls or .xlsx document. When the final version is ready to load, save it as a .csv file.
• If corrections are needed after saving a .csv, go back to the .xls or .xlsx version to make the corrections and save as a new .csv to load.
• The navigation path to load the final .csv file is Planning and Budgeting > Planning and Budgeting Setup > Process Model > Budget Data Load
• For revisions where the row of data is no longer needed, it is necessary to delete the row, not just clear the data from the spreadsheet cells. Merely clearing the data and saving the file as .csv, still adds commas for that line, which will error when loading.
• The first row of the .csv file must be a header row that names the ChartFields, then the budget periods to be loaded, in order. DO NOT CHANGE THE HEADER ROW.
• The ChartField names must exactly match those specified in the activity definition for the model; and all of the model’s selected activity dimensions, including CURRENCY_CD must be included in the header row. Use headers provided – DO NOT CHANGE THE HEADER ROW.
• All alpha characters within the body of the spreadsheet must be in UPPER CASE.
• Each ChartField member in the .csv file must already exist in Budgeting and Planning. If new chartfields are needed for the upcoming biennium, follow the standard process to have the ChartFields created in FIN prior to usage in BPM.
• Budget period amounts should not contain currency symbols, commas, or decimals. All dollar amounts should be entered in whole dollars.
• Budget period amounts should be entered as values, not as formulas.
• If the value for a budget period is zero, it is necessary to enter zero because a blank value will result in an error. No fields can be left blank in the file.

Below are examples of data expected for each budget scenario.

• Future Years Expense Estimates (AGYOPA, AGYOPB, AGYOPC) Scenario

  Data Expected in each field on first tab
  Account	3 digit category level, beginning with '5'
  DeptID	3 digit + 'PC' if using single Planning Center; or 9 digit if multiple Planning Centers
  Fund_Code	Fund
  Program_Code	B' level program
  Product	ALI
  Dimention1	Initiatives. Choose from '001' - '099' for each row to tie initiatives with multiple rows together
  CURRENCY_CD	Always 'USD'
  2018A1	Full appropriation for current year, dollar amount, no comma or $ sign or blanks
  2019A1	Full appropriation for current year, dollar amount, no comma or $ sign or blanks

   

  Example row of data to use
  Account	DEPTID	FUND_CODE	PROGRAM_CODE	PRODUCT	CURRENCY_CD	DIMENSION1	2018A1	2019A1
  510	DAS103000	1280	3660B	100620	USD	001	55000	56000
  530	DAS103000	1280	3660B	100620	USD	001	45000	0
  560	DAS103000	1280	3660B	100620	USD	002	75000	80000

   

   

• Current and Future Years Revenue Estimates (AGYREV) Scenario

  Data Expected in each field on first tab
  Account	5-digit category level, beginning with '4'
  DeptID	3-digit Agency Code
  Fund_Code	Fund
  CURRENCY_CD	Always 'USD'
  2017A1	Revenue anticipated in current year 2017, dollar amount, no comma or $ sign or blanks.
  2018A1	Revenue anticipated in current year 2018, dollar amount, no comma or $ sign or blanks.
  2019A1	Revenue anticipated in current year 2019, dollar amount, no comma or $ sign or blanks.

   

  Example row of data to use
  Account	DEPTID	FUND_CODE	CURRENCY_CD	2017A1	2018A1	2019A1
  45500	DAS	1280	USD	54678	55000	56000

   

   

• Current and Future Years Transfer Estimates (AGYTRF) Scenario

  Data Expected in each field on first tab
  Account	6-digit category level, beginning with '8'
  DeptID	3-digit Agency Code
  Fund_Code	Fund
  CURRENCY_CD	Always 'USD'
  2017A1	Transfers In or Transfers Out anticipated in current year 2017, dollar amount, no comma or $ sign or blanks.
  2018A1	Transfers In or Transfers Out anticipated in current year 2018, dollar amount, no comma or $ sign or blanks.
  2019A1	Transfers In or Transfers Out anticipated in current year 2019, dollar amount, no comma or $ sign or blanks.

   

   

  Example row of data to use
  Account	DEPTID	FUND_CODE	CURRENCY_CD	2017A1	2018A1	2019A1
  810110	DAS	1280	USD	54603	55000	56000

   

   

• Current Year Expense Estimates (AGYCUR) Scenario

  • Agency will send the completed Budget Data Load spreadsheet and .csv files to the OBM Budget Analyst to upload.

  Data Expected in each field on first tab
  Account	3 digit category level, beginning with '5'
  DeptID	3 digit + 'PC' if using single Planning Center; or 9 digit if multiple Planning Centers
  Fund	Fund
  Program	B' level program
  Product	ALI
  Dimention1	Initiatives. Choose from '001' - '099' for each row to tie initiatives with multiple rows together
  CURRENCY_CD	Always 'USD'
  2017A1	Actual expenses anticipated for the current year, dollar amount, no comma or $ sign or blanks

   

  Example row of data to use
  Account	DEPTID	FUND_CODE	Product	Program	CURRENCY_CD	2017A1
  510	DAS	1280	100620	3660B	USD	55555

   

   

• Current Year Estimates by Appropriation (AGYAPR) Scenario

  • Agency will send the completed Budget Data Load spreadsheet and .csv files to the OBM Budget Analyst to upload.

  Data Expected in each field on first tab
  Account	3 digit category level, beginning with '5'
  DeptID	3 digit + 'PC' if using single Planning Center; or 9 digit if multiple Planning Centers
  Fund	Fund
  Program	B' level program
  Product	ALI
  Dimention1	Initiatives. Choose from '001' - '099' for each row to tie initiatives with multiple rows together
  CURRENCY_CD	Always 'USD'
  2017A1	Appropriation amount for current year, dollar amount, no comma or $ sign or blanks

   

  Example row of data to use
  Account	DEPTID	FUND_CODE	Product	Program	CURRENCY_CD	2017A1
  510	DAS	1280	100620	3660B	USD	55555

   

   

CSV File Requirements

The key to successful budget data loads is accuracy in completing the spreadsheet template. The .csv file must meet the following requirements:

• The first row of the .csv file must be a header row that names the ChartFields, then the budget periods to be loaded, in order. DO NOT CHANGE THE HEADER ROW – this will create errors.

• The ChartField names must exactly match those specified in the activity definition for the model; and all of the model’s selected activity dimensions, including CURRENCY_CD must be included in the header row. Again, DO NOT CHANGE THE HEADER ROW – this will create errors.

• Each ChartField member in the .csv file must already exist in Planning and Budgeting.

• Budget period amounts should not contain currency symbols, commas, or decimals. All amounts should be entered in whole numbers.

• If the value for a budget period is zero (0), the user must enter the number zero (0) because a blank value will cause errors.

• The entire spreadsheet must be formatted as text. The template is set up this way so that any leading zeros in the numbers entered will remain intact. It is a good idea to double check the formatting to avoid errors.

• All alpha characters in the field entries must be in upper case – capital letters.

Validations

During the import process, the system checks that the data in the .csv file meets various requirements for Budgeting and Planning, such as ChartField combinations. Only rows that pass the requirements are imported. View the details for rows that do not import by viewing the Budget Data Load Error Report.

The method ID default will be updated for ChartField combinations provided in the .csv file. The method ID will be updated to AMTPER if the method override flag is enabled.

Tips for Loading the .csv file into BPM

• Navigation path to load the final .csv file into BPM is: Planning and Budgeting > Planning and Budgeting Setup > Process Model > Budget Data Load

• When the user loads a .csv file to a budget version, it will:

• Replace any rows in the budget version with what is in the .csv file for rows that have the same ChartField strings that are on the .csv file.

• Add rows to the budget version with new ChartField strings that are in the .csv file that were not already in the budget version.

• Do nothing to the ChartField strings rows already in the budget version that are not on the .csv file.

• Deleting lines from the budget data load will not delete lines that already exist in BPM. It is necessary to go back to My Planning Workspace to revise or delete existing ChartField strings.

• This process locks the Planning Center. The Planning Center will automatically unlock when the upload process is completed.

• BPM does not prevent multiple concurrent processes from writing data to the same planning center; if multiple import files are being processed for the same planning center, they should be run sequentially.

• Both the .xls (or .xlsx) and .csv files can be saved to the location of the user’s choice. It is suggested that the files(s) be saved in a location that is easy to remember and access by the user.

Below are examples of the run control fields for each budget scenario.

• Run Control Fields for Future Year Expense (OPA, OPB, OPC) Scenario

  

   

  • Planning Model ID '2021_OPER_AGY' is the AGY model used for OPA/OPB/OPC scenarios.
  • Scenario '2021AGYOPA' is the OPA Scenario. Scenario '2021AGYOPB' is the OPB Scenario. Scenario '2021AGYOPC' is the OPC Scenario.
  • Activity 'AGYOPA' is the OPA Activity. Activity 'AGYOPB' is the OPB Activity. Activity 'AGYOPC' is the OPC Activity.
  • This example run control is for the 'ACCPC' planning center. (This will be different for each agency and planning center.)
  • Choose the working version to load the .csv file--typically it will be to Version One.
  • The version must already exist in BPM to be able to select it here in the run control. Create a version before running the Budget Data Load, if necessary.
  • Add the .csv file as the Source File.
• Run Control Fields for Transfer (TRF) Scenario

  

   

  • Planning Model ID '2021_OPER_AGY' is the AGY model used for TRF scenarios.
  • Scenario '2021AGYTRF' is the TRF Scenario.
  • Activity 'AGYTRF' is the TRF Activity.
  • This example run control is for the 'ACC' planning center. (This will be different for each agency.)
  • Choose the working version to load the .csv file (typically it will be to Version One).
  • The version must already exist in BPM to be able to select it here in the run control. Create a version before running the Budget Data Load, if necessary.
  • Add the .csv file as the Source File.
• Run Control Fields for Revenue (REV) Scenario

  

   

  • Planning Model ID '2021_OPER_AGY' is the AGY model used for REV scenarios.
  • Scenario '2021AGYREV' is the REV Scenario.
  • Activity 'AGYREV' is the REV Activity.
  • This example run control is for the 'ADJ' planning center. (This will be different for each agency.)
  • Choose the working version to load the .csv file--typically it will be to Version One.
  • The version must already exist in BPM to be able to select it here in the run control. Create a version before running the Budget Data Load, if necessary.
  • Add the .csv file as the Source File.
• Run Control Fields for Current Year Expenses (CUR) Scenario

  

   

  • Agencies do not have security access to the Planning Model ID ‘2021_OP_AGY_ADM.’ Send completed Budget Data Load spreadsheet and .csv files to the OBM Budget Analyst for uploading.
  • Planning Model ID '2021_OP_AGY_ADM' is the AGY model used for APR and CUR scenarios.
  • Scenario '2021AGYCUR' is the CUR Scenario.
  • Activity 'AGYCUR' is the CUR Activity.
  • This example run control is for the 'ACC' planning center. (This will be different for each agency.)
  • Choose the working version to load the .csv file--typically it will be to Version One.
  • The version must already exist in BPM to be able to select it here in the run control. Create a version before running the Budget Data Load, if necessary.
  • Add the .csv file as the Source File.
• Run Control Fields for Current Year Expense by Appropriation (APR) Scenario

  

   

  • Agencies do not have security access to the Planning Model ID ‘2021_OP_AGY_ADM.’ Send completed Budget Data Load spreadsheet and .csv files to the OBM Budget Analyst for uploading.
  • Planning Model ID '2021_OP_AGY_ADM' is the AGY model used for APR and CUR scenarios.
  • Scenario '2021AGYAPR' is the APR Scenario.
  • Activity 'AGYAPR' is the APR Activity.
  • This example run control is for the 'ACC' planning center. (This will be different for each agency.)
  • Choose the working version to load the .csv file--typically it will be to Version One.
  • The version must already exist in BPM to be able to select it here in the run control. Create a version before running the Budget Data Load, if necessary.
  • Add the .csv file as the Source File.