Overview
This document describes how to run the “Budget Import” process and the required format of the budget data to be imported.
The “Budget Import” process takes data from a CSV or TXT
file, in the format listed later in this document, and creates or replaces
budget records in the DPHS database.
The file can only contain budgets for a single year, and if importing to
a multi-property database, budgets for a single property. Budget amounts can be net (monthly) or
cumulative (YTD). Budgets for different
years and/or properties must be imported separately from different files.
This utility also allows you to create Daily Revenue
Budgets from the file. The system will calculate the Daily Revenue budget
amount from the monthly amounts provided in the import file.
How To Run Budget Import
To run the “Budget Import” process, you must log all users out of the database. Before importing budgets, it is recommended that you back up the database. This provides the best recovery protection in the event of a problem with the import process.
From the General Ledger Utilities menu choose Budget Utilities > Budget Import . The following screen will appear:
“Import
File Format” - Enter the format
of the budget file to be imported – You have two options CSV or TXT.
“Path and File – Budgets"- Enter the full path and name of the CSV or TXT file containing budget data. This includes the drive, directory, any subdirectories, file name, and extensions. The program verifies that the file entered exists.
“Path and File – Errors” - Enter the full path and name of a file to be created which will contain all error messages generated from the “Budget Import” process. Print this file after the “Budget Import” process has completed. EXAMPLE: c:\temp\error.txt
“Fiscal Year”- Enter the fiscal year for which budgets are being imported. This year is inserted into the budget record in the database if it does not already exist.
“Budget
Type” - Enter
the budget type that is to be created or updated with the imported data. This can be an existing budget type or you
can create a new one. No verification
is performed on the budget type record.
Please note that specific budget types are used to tie the budget
figures to the financial statements.
The budget types are referenced in Statement File Maintenance under
Definitions. In order for the financial
statements to show the imported budget figures, then the budget type entered
during the import process must match the budget type in the statement
definition.
“Amounts in Net or Cum?” If the import file containing budget data has net amounts (amounts for each individual month), enter “N”. If the import file containing budget data has cumulative amounts (amounts that are accumulated through the months), enter “C”.
(See the import file
examples on the last page)
“Property” - Enter the property ID for the budget. This field is accessible only if you are running a multi-property database. No verification is performed for valid property ID, so check your entry carefully.
“Insert Property into Account” Enter “Y” or “N” as to whether or not the import process should insert the property ID into the G/L account number. This field is accessible only if you are running a multi-property database. If the ASCII file does not contain the property ID, enter “Y”. If the ASCII file does contain the property ID, enter “N”.
After supplying information in all accessible fields, press <F2> to continue. The following screen will appear. If you wish to create daily revenue budgets, enter Yes and press F2 to continue.
Please Note: Daily Revenue Budgets will only be created for accounts flagged as Daily Revenue Accounts.
The following screen will appear where you can select whether the Daily Revenue Budgets are Dollar (D) or Unit (U) Budgets, as well as the Fiscal Year to which these Daily Budgets should be imported.
Press F2 to continue to begin the Import process.
The import process verifies your entries and starts the import, or displays error messages if problems were detected with your entries.
While the import process is running, two status windows are
displayed. The Create Budgets window displays the record counts for Budget Header Added, Budget Detail Added,
and Errors. The other shows the total amount of Daily Budget Accounts that have
been imported.
When the process is complete, the following message
appears: “Budgets import completed.” Click OK and you
will be returned to the Main DPHS screen.
Creating and Replacing Budgets
The budget file in the database is a two level file composed of:
- The budget header: - budget type (used in the financial statement definition), account number and budget entry method.
2. Budget detail: - budget figures by fiscal year.
The “Budget Import” process first checks to see if the budget header exists. If it does not, it creates one. If it does exist, it does nothing to the record. The process then checks to see if the budget detail record exists. If it does, it replaces the record. This allows you the flexibility of running the budget process multiple times, or at different times in the year.
NOTE: Once the budget data has been imported, it is not possible to back out the data. The imported data must be manually deleted, or the database restored from a backup.
Verifying the Import
There are several steps to take in verifying the import process after completion.
1. The first is to print the error file specified when running the process. This lists all errors encountered by the program. It does not list system errors (generated by the operating system). If the error log file does not exist, there were no errors encountered by the import process.
2. The second step is to start up the database and print one of the budget reports and/or lists.
Errors
This section lists error messages generated by the import process. It does not list all operating system errors (hardware problems, memory problems, network errors, typographical errors, etc.) The process generates two types of errors. The first occurs during the entry phase of the import process. The second occurs during the execution of the import process.
General
Rules
- Each field must be separated by at least one space.
- If no information exists for a certain month, use a zero to indicate this; do not leave any numeric fields blank.
- Numeric fields need a decimal point only if cents are present. Budget amounts do not need commas, however these are acceptable.
- Do not exceed the maximum length of value for a field as the import process rejects the record and immediately stops processing.
- Negative signs may precede or trail the number.
- Credits are entered as negative numbers. Debits are entered as positive numbers.
Below is an example
of a budget import spreadsheet.
|
Set your column widths to 16 in your spreadsheet. You must save the spreadsheet as a Text (Tab Delimited)(.txt) file. |
||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
|
Acct # |
Jan |
Feb |
Mar |
Apr |
May |
Jun |
Jul |
Aug |
Sept |
Oct |
Nov |
Dec |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Note: |
Note: |
Note: |
Note: |
Note: |
Note: |
Note: |
Note: |
Note: |
Note: |
Note: |
Note: |
Note: |
|
Format |
Format |
Format |
Format |
Format |
Format |
Format |
Format |
Format |
Format |
Format |
Format |
Format |
|
column |
column |
column |
column |
column |
column |
column |
column |
column |
column |
column |
column |
column |
|
as |
as |
As |
as |
as |
as |
as |
as |
as |
as |
as |
as |
as |
|
text |
numeric |
numeric |
numeric |
numeric |
numeric |
numeric |
numeric |
numeric |
numeric |
numeric |
numeric |
numeric |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Note: You may add an
additional column if you have 13 fiscal periods. |
|
|
|
|
||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Net Examples: |
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
00101 |
10 |
5 |
15 |
9 |
27 |
13 |
4 |
19 |
26 |
7 |
35 |
25 |
|
00102 |
1.1 |
0.76 |
42.75 |
5.62 |
13.45 |
2.75 |
15 |
9.6 |
6.2 |
10 |
11.3 |
|