Entering budgets into Microsoft Dynamics SL is a bit of a problem. We have developed a system at Boyer & Associates, called Budget Import, which allows the user to import an Excel spreadsheet used to create the budget, avoiding user error in converting or entering the data, and allowing the data to be inserted quickly and easily. It consists of two screens and allows for significant flexibility in how the source file is formatted, while remaining simple to setup.

Currently in Dynamics SL, the standard Budget Maintenance screen (01.250.00) only allows you to access a single account and subaccount at a time:

Budget Maintenance screen

That gives you good control over each line in the budget, but consider that a company with 50 accounts (a tiny list) and 4 subaccounts (smaller than most) could have 200 records in its budget. Suddenly, entering the data is no longer a trivial exercise. Also, this screen does not allow you to easily see your overall budget nor to compare different accounts.

For this reason, budgets are more commonly created in Excel. Excel allows the user to build their budget as a whole, seeing the overall totals (and subtotals as desired) and how those values are divided among the accounts. But, once the budget has been completed and validated, the user is left having to copy it into SL line by line. Done by hand, that will take a good deal of time and is vulnerable to error.

It is possible to use transaction import to get around manually typing in the values, but, as powerful a tool as that can be, it requires a narrow file definition. Converting an Excel budget spreadsheet into a simple comma separated file that can be used by transaction import can take a great deal of effort. Plus, every time you manipulate data manually, the possibility of creating errors exists.

Budget Import alleviates the current frustrations with entering budgets. The first of the two screens in this system is the setup screen:

Boyer & Associates Budget Import Set Up

This screen tells the system how to interpret information in the source file by defining its significant formation. The field position defines the column where the specific needed value is located. The screen will disable periods not used according to GLSetup (hence period 13 being disabled in this screen shot) and defaults column numbers in their basic order. The user can change these values as needed to make the definition fit their spreadsheet. For example, if a quarterly subtotal was used in the budget, it will be ignored during the import because that column will not be specified as important. Similarly, subtotals for groups of accounts or subaccounts will be ignored if no value is put into the Account column in the spreadsheet (indicating that the row should be skipped). The number of Header lines is specified in the Header Lines field, since those lines cannot be expected to respect the blank account column rule.

The remaining values on the screen are defaults for the import screen (Default File and Default Ledger) and define rules for importing a text file (Text Delimiter and Text Qualifier). The system will import either directly from Excel or from a text file (separated either by comma, semi-colon, or tab or fixed length). Text can be qualified either with a single or double quote. This short list of fields allows the user to quickly and easily define the rules for how the file is constructed and will accommodate any layout used for creating the budget with only 2 imposed rules – the budget for each account-subaccount pair must be on a single line and the account must be left blank for rows that are to be skipped.

The other screen, Budget Import Utility, is the one that actually does the work:

Boyer & Associates Budget Import Utility

The fiscal year defaults from the GLSetup record, and the company defaults from the active company. Ledger ID and File type default from the Budget Import Setup Record. Any of these values can be changed; the first 3 are the remaining key fields for the budget record, and the file type controls the type of file being imported. That option allows the user a method for handling a situation where Excel is causing problems. If the computer being used doesn’t have Excel installed or a version conflict is causing problems, importing a text file will still work. The browse button allows the user to select the file to be imported.

Once the settings are correct, pressing the Begin Processing button quickly imports the data directly from the spreadsheet used to define the budget. No fuss, no muss, no matter how complex the spreadsheet is with the budget.

If you have any questions regarding our Budget Import system or would like more information, please contact us at any time.

For additional tips and tricks regarding Microsoft Dynamics SL and ERP products, subscribe to our blog.