Connecting the Budget to a Spreadsheet

Many financial managers are accustomed to generating a budget using a spreadsheet.  EBMS allows the user to import budget values into the EBMS budget directly from a MS Excel spreadsheet.   Combining this import option with the ability to read EBMS from MS Excel allows the manager to calculate almost any type of budget without manually transferring values.    

Complete the following steps to import data from MS Excel to the EBMS budget:

1. Create budget values within a spreadsheet as shown below:
   
   It is recommended to create a sheet (labeled Totals in the example shown above) that contains the totals that will be passed to the budget within EBMS. Since EBMS addresses the spreadsheet cells by row and column numbers, this step will assist in maintaining the exact location. Notice that the budget calculations are done within the Budget tab of the spreadsheet example shown above.

2. Close the spreadsheet and open the budget list by going to Financials > Budget from the main EBMS menu.

3. Open a specific account by double clicking on the account line. The dialog is shown below:

4. Select the Excel Sheet formula from the Total Calculation option list as shown below. Review the Using Variables section for more details on these options.

5. The Excel formula template requires the following 4 references within the Excel spreadsheet:

   1. The sheet label which is found at the bottom of the Excel spreadsheet. The first sheet in the sample spreadsheet shown above is labeled Totals.

   2. Enter the Cell reference code. This code must be a simple column letter + row number such as A1, B5, etc

   3. Enter the file name. Note that the full path of the file must be entered including the file path and extension. Only Excel .XLS files are currently supported.

   4. Click on the Calculate button to test the connection. The Total Calculation template entries will not check for file and sheet entry errors or invalid row or column values.  

Repeat these steps for each value that you wish to copy from the spreadsheet to the EBMS budget list. Use the Change by GL Code wizard from the budget list dialog to set the Excel formula for a range of accounts. Enter the sheet and file settings for the entire range and then change the individual row and column values for each account. Review the Change a Range of Budget Accounts section for details in setting a range of accounts to Excel.

Review [Main] Excel Add-in > Overview  for more details in exporting EBMS data to the spreadsheet.