External Sources creating and maintenance:
The following allows you to create or define excel
sources that the cost center’s sub-accounts can export and/or import from
external excel files.
The leaf center’s sub-account holds dollar amounts spread
optionally across up to 13 periods or 12 months.
Spreadsheet Export/Import Forms:
5
4
3. Steps to adding a Excel Import Source.
Click above button.
If you want to populate data from an Excel Sheet, then you need to define the Excel source/file here.
When you activate the import from Excel to a Sub-Account, the application needs to know
from which worksheet (ex: Budget)
in the Excel file (Ex: Budget for Center 3 sub-account 1000.xls)
and the Cell id (B1, A1...).
To avoid tedious work, you can predefine a Template (see menu Add/Edit -> Template Form), so that A2 is always the yearly amount, A1 is month 1, A2 is month 2 ... etc.
For the new SourceID below, make sure that the file name, and target/source fields are correct on the rigth side of this form,, i.e. see item 1 an 3.
There are TWO ways to link to your existing Excel forms….
METHOD 1:
Click on the [?] besides the sub-account line. This will bring up the transaction or details of this account, broken down by months/periods.
The [X] Excel icon below and the [ç Import from Excel] and the [àExport to Excel File] will allow you to transfer any field to and from the application and your Excel forms (ex: PO, Invoice, Personnel, Other systems).
By clicking the [X] Excel Icon, you will be able to select the external Excel file, and also select from the templates that indicate the mapping of Excel worksheet cells to our applications database fields.
It is metadata driven!
The Excel mapping templates are creates using the menu …
The Excel mapping to our database form. You enter the sheet and cells, the pull down [Target Fields] allow you to select the mapping.:
You can then create a standard mapping for all you diverse Single Personnel Excel (indicating Bill’s monthly salary, as it changes), or a PO for some purchase, or any other Excel that is affecting the amounts on a sub-account for a particular Cost Center. You can reuse one Excel to populate different Sub-Account on different Cost Centers. The Excel to our database is a many-to-many relationship.
For example, suppose we have a invoice that affects cost center 14 as follows:
We want to link and import to our budgeting application, but use the excel sheet to utilize it’s custom form for data entry , or perhaps for personnel calculations or inventory, or any other extension that involved more details than the budgeting people care to know. We add these extra details in an excel set of templates. We then define the template in our application (see above), and link our accounting application (BudgIt) to it.
The Total filed is defined as “TOT” in the above excel sheet called “Invoice” , then the template within our application must have ..
Then we can import it to the account/sub-account for cost center 14 in the year 2002:
Click this to import the invoice’s total of $1,610.93.
METHOD 2: You can export a playground submission (a section of your budget which you wish to alter) to a MS Excel file.
Alter the amounts and later import these changes back to a playground/scenario submission.
If you already have a spreadsheet for, say office staff for example,
you would use this utility to set up the spreadsheet, then create a separate worksheet that
feed the fields that we have generated and will import from.
The application allows you to do this, thought we believe you can alter sub-sections of budget more easily within the application.
Submissions Spreadsheet Import Export:
A submission is a group of budget modification transaction or base amount for a Center and its sub-account. It is in playground, batch scenario mode. You can get a report showing this submission's budget impact without promoting it to budget phase production. Since it is in a playground mode, you can export it and make amount changes to it using Excel formulas as you may wish.
When done you can import these changes to update the original submission.
You can go back and forth as much as you want.
When you decide to promote it to production, a check is done against production to insure that no other user was involved with this set transaction in case the submission was extracted from a subset of the production data. In other words you are safe.
Anyway, you need to export a submission that exists in the session key setting for the current year/company/budget phase by …
Then…
Follow the steps, 1 - export, 2 - use Excel, 3-import it
back
1 3
2
2. Will launch the Excel application for you using the submission excel extract's file name automatically. After you make your changes using Excel, remember to either close it or save it, if you want to later import it back to the submission.
Do one step at a time, otherwise you'll lose changes in the submission or in an existing spreadsheet. If you export a submission, and then alter the submission within BudgIt, and then also import that extracted submission, some data will be lost in your interim step.
Having a whole bunch of external files is hard to keep track of for anyone!
Here's the automatic view of the submission if you hit the Step 2 button:
