Reporting Budget Information on your FRX reports:
Many people are
unaware that Solomon supports unlimited budgets, which can be stored internally
within the software, as a unique ledger id.
When you have your budget information stored within Solomon, you can
access this information on your FRX reports.
This allows you to compare actual vs budgets
for multiple years in a trended report.
The are three
main methods for getting budgets information into FRX for reporting purposes:
1) manually complete the budget maintenance screen (01.260), 2) import the budget information into Solomon
via the transaction import, or 3) use
the Frx Forecaster module which integrates to Solomon.
This document
will address in an overview manner, each of these three options.
Option 1:
Manually enter the budget information into Solomon.
This option is
clearly the easiest, but the most time consuming. In the General Ledger module, there is the
Budget Maintenance screen. This screen
looks like this:
The data in the
screen is just fill in the blanks for defining the
budget for each account/ sub account combination which you are budgeting. The unique ledger id field will need to be
previously set up for this 2005 budget example. The ledger id is defined in
screen 01.31
A couple notes
on creating ledger id’s for budgets. The
ledger id’s always are budget type: budget, and are
always set up as balancing is NOT required.
Although Solomon
has some neat options for speeding up data entry such as creating equal budget
amounts / period given the total budget’s annual amount for an account/ sub
account combination, entering budgets manually is a slow and laborious process. If you are budgeting 50 accounts for three
sub accounts, then you will be entering: over 1950 separate entries. With these many entries, there is plenty of
room for data entry errors.
Option 2: Import the information into the Budget
Maintenance screen using the Transaction Import tool.
Many people
forget that as a standard feature of Solomon, you can to importation into just
about every data entry screen in Solomon.
This is a great feature, but unfortunately it is greatly underused by
people. Since we can see that manually
entering monthly totals is a big data entry job, the idea of auto importing
budget info is very exciting.
To accomplish
this import, you will need to follow these steps
Create a control
import file by using Generate Control Macro tool. This tool is located under
the Utility Menu. When you launch this
program, this screen appears:
Complete this
screen as follows: In the first field,
screen, hit the F3 key an select the screen id’s for the budget maintenance 0125000. Going to the next field: control file type,
set it initially to Simple, and then go the field number 3, control file
name. I recommend you name your file as
a combination of the screen number and the type of control file ( ie 0125sim.ctl or 0125int.ctl).
For a
simple control file, this screen should look like:
After setting up
this screen , click on the button OK, which will
actually generate the control file settings.
In this first situation the control file which is created looks
like:
I recommend you
repeat this process a second time, and set the field number 2, to the
intelligent level, so that this second screen looks like:
This second
control file, the intelligent version, looks something like this:
Because Solomon
has the ability to create with the customization manager module, unique data
entry screens, the control macro generator is really powerful. It is actually opening and reading the
current settings for the budget screen, including any customizations performed
on this screen, and records the displayed fields, in the normal tab sequence or
order for field movement.
I recommend you
generate both the simple and the intelligent versions of this macro for learning
experiences. We can optionally use
either import control file in the later parts of this import process, as they
are generally interchangeable.
Once you have
created the import control files for the budget maintenance screen, you can use
the transaction import utility. This
tool allows you to import a text files (csv format)
with your budget data directly into Solomon.
One of the advantages of this import is that all of the normal data
entry screen validations are preformed, minimizing the problem of importing bad
data.
Unfortunately,
the import file must be in a specific format to match the fields identified on
the 0125sim.ctl file created earlier.
Since many people budget using a spreadsheet program like Excel, the
challenge is getting the budget columns realigned to match the required import
layout format.
We can see the
logic for the import layout from the 0125sim.ctl file:
Here is an
example of an Excel file with budget information in it, where I am displaying
only the first 4 periods of the 12 monthly budget buckets.
|
Budget |
cacct |
csub |
cledgerid |
cfiscyr |
cbdgtrvsndate |
cannbdgt |
cbdgtper |
cbdgtper |
cbdgtper |
cbdgtper |
|
Budget |
3009 |
000000 |
bud05 |
2005 |
10/3/2005 |
12000 |
1000 |
1000 |
1000 |
1000 |
|
Budget |
3011 |
000000 |
bud05 |
2005 |
10/3/2005 |
24000 |
2000 |
1000 |
3000 |
2000 |
When creating
the csv file, this is a file saved as option, where
the output is the csv option. Also, please note, the first line is
displayed for information of column headings, and it is not exported. ( Note: the column headings like cacct
are best read by dropping the c character, ie acct)
The transaction
import module is located off the Utility menu.
Select Utility > Transaction Import to see this form:
Complete the
columns shown as follows:
Data file
name: this is the location and file name
for your budget data.
Data file type:
always set to Aacii
Screen: Select 01250sim
Control
file: Select 0125sim.ctl
Output log file
name: this is a temp file which you will
need to create. I usually just create a
simple text file like c:\temp\error.txt
Run Minimize:
change to unchecked position
I recommend you
next click on the options button a the bottom and
change the screen settings as shown:
Click the ok button
to return to the main Transaction import screen (98.500.00).
Now you test the
import process by clicking the begin processing button. Because we set up the mode to be in the edit
only option in screen 98.500.01
The import file
will be read either until the end or 3 errors are identified. I find that this step is usually an
iterative process, where I have errors in the import file which need to be
corrected before a successful import.
If you have
import errors in your data, then the Edit Errors screen will be useful in
finding and correcting mistakes.
This Edit Errors
screen looks like:
The top half of
this screen displays the import logic and error messages, and the lower half of
this screen displays the import file data,
On the top half of the screen, as the last section, you will see a recap
of the number of errors encountered during the import. You are looking for recap of ‘0 errors
encountered’.
Once you resolve
any import errors, you are ready to do a final update. This is done by going
into the main transaction import screen 98.500.00 and selecting the button opton: Options and
set this screen to look like this:
Click Ok and
then click the Begin processing button on screen 98.500.00
Your new budget
information will be loaded into the General ledger per the import file
specifications.
If required you
can make manual corrections directly from the budget maintenance screen:
Option 3:
Frx Forecaster
is a sister product to Solomon. This
product is created by the same software developers who wrote Frx. This product provides a very robust budgeting
system via a web browser interface
FRx
Forecaster makes it easy for midmarket companies to
immediately realize the benefits of implementing a budgeting and planning
application. In as little as two weeks, companies can implement a Web-based
budgeting solution that saves time and money throughout the year. FRx
Forecaster gives companies immediate control of budgeting and confidence that
it is realistic and accurate. They can monitor the status of their business on
a daily basis, and respond to new business opportunities and challenges.
Budgeting
and forecasting is easy with FRx Forecaster's Plan Screens: a set of input
tools that automate personnel, capital, operational expense and revenue
planning.
Drill down and across multiple planning dimensions and detail levels with FRx
Forecaster's online reports. The system offers the most quick and
cost-effective way to distribute reports to hundreds of remote users.
Reorganize your company's chart of accounts in minutes with FRx Forecaster's
Rollups feature.
Safeguard the integrity of your budget and forecast data with security
assignments and routings.
Distribute data across multiple Cost Centers with Allocations, or convert
foreign amounts with Currency Translations.
Manage your budgeting progress centrally and keep all your users updated on the
latest budget developments with email reminders, bulletin board posting and
global planning instructions.
Once
the budget data is in FRX Forecaster you can report via your FRX reporter app
or the FRX Forecaster module.