Advanced Fund
Management Solutions

September 2006
Hi Everyone,

If you receive Purchase Orders from other departments, across town, or across the state, consider importing them from an Excel worksheet. The information is prepared once, and quickly imported without error. See how in this issue. You may not have considered using and reporting units to aid in financial management analysis-read how you can! As always, if you have any questions regarding an Accufund® issue, please contact me at (318) 253-8556, or email me at jgarv@afmss.com

Thanks,
Judy Garver

Importing Purchase Orders

You may have offices on the other side of the building, across town, or in other remote locations that need to generate purchase orders and get them into AccuFund. However, they do not have access to AccuFund to enter the POs themselves. Rather than have you key in all of the PO information yourself, you can have them create an import file in Excel, e-mail it to you and then you can import it quickly and easily. This article shows you a typical format that you could use in your own situation.

It all starts with the Excel input file. Shown below is a simple example of a worksheet that contains three entries (the first row is for optional column titles that will not be imported). For clarity, these column titles match the field names AccuFund uses for the information shown in the cells immediately below. These same titles are seen when you setup the import in the PO Import Wizard. Rows two and three are for the same vendor, and have the same PO number (1008). Since those two items have the same PO number, they will both be included in the same PO after the import. Row four contains a single item PO for Office Max.

The PO import we are using requires that you identify the vendor by either its name, or by lookup. The name is used here and is entered here in column E (exact spelling is required!). You will probably want to include the other columns shown at a minimum, and other columns may be added if desired. You must show quantity and unit price. You cannot just enter the total amount for each row by itself (however, the extension of quantity and unit price will appear on the imported PO).

The name of this Excel spreadsheet is "PurchaseOrders". If you need to keep the import files from your various departments separately, you might want to give them unique names (in this case it could be "ResearchPurchaseOrders" since this is from the research department.

Once you have established your import file format and have the departments providing them to you, the next step is creating the Import definition in the PO Import Wizard.

We recommend you open the PO Import Wizard by going to Browse Purchase Orders and then click on the "Import" button. The initial splash screen will appear. Just click on the "Next" button.

Select the "By Purchase Order" option, as shown on the next screen at right: Click on the "OK" button.

This next screen shows that we have saved an import definition called "Department POs". You'll want to do that, too. However, the first time you will have to choose the "New Import Definition" option.

Click on the "OK" button.

The form at right opens: It indicates that an Excel spreadsheet will be used. The date format is defined as "Default", and the file name and worksheet that contains the purchase orders is specified. Since our worksheet contains the optional column titles, we're starting the import on row 2. Click on the "Next" button.

The fields that match the information in our worksheet have been highlighted and moved to the right pane with the "Insert" button. At least the Name field or the Lookup field must be moved to the right pane (and correspond to the correct column in the worksheet). As each field is added, it is given a worksheet column letter-make sure it matches the one in the worksheet! If not, the "Change" button can be used to make adjustments.

Click on the "Next" button. You'll see a form that verifies your column choices. Click on the "Finish" button. The Wizard will open Excel (if you don't already have the worksheet open) and the import will occur!

If you look at the bottom of the Browse Purchase Order form, as shown below, you will see our two imported Purchase Orders.

This import can be an easy way to input your departmental POs. They might as well submit the information in a way you can use anyway!

Posting and Reporting Units

The AccuFund Accounting System is capable of recording units and reporting them with the Reports/Form Designer and the Financial Report Writer. This article shows how you can extract more useful information from your system than you might have previously thought. For example, you are used to tracking how well you are doing to budget. You compare the actuals that an account has accumulated (expenses or revenues, usually) against the budget for a particular month or year. The variance then tells you and your managers how things are going. Measuring variance and managing in this manner is a timeless and extremely useful tool. However, have you ever thought of how you might be managing based on units? Units can be numbers of something, such as hours worked, meals served, or number of sheets of plywood purchased. The sample report below, which shows only one account, reveals that the revenue account is budgeted to accrue $600 for the month of September. The accrued revenues are $500. There is a shortfall of $100. The revenue could have been from the sale of tickets, or other merchandise, and about 83% of the anticipated revenue has been achieved. But, is that the whole story?

The report also shows that there were actual unit sales of 150 units, however, budgeted sales were for 300 units. In other words, sales of unit were 50% of budget, giving a unit variance of 150 units. This may indicate that there is another dimension to our financial analysis that has not been properly considered. Are we farther from making the budget dollars than we think? Do we really need to double our units sales this month?

You may be wondering from where the actual and budget units have come, and how they are entered in AccuFund. This can give us some additional information on the usefulness of this type of reporting. The actuals were recorded through cash receipts when the items (or units) were sold. They could have been meal tickets, concert tickets, days of conference room rental, or any other item. Those units were posted to the account shown above right along with the dollar amount of the sale, and reside on the account as actual units. Previously, the same revenue account was budgeted with estimated sales units for the same period of time. This was done with a journal entry that looks like this:

Note that its posting type is "Budget" and that the "Has Units" box is checked. The "Has Units" option allows us to enter units along with the account distribution information at the bottom of the form. Since we are posting units, it is not necessary to post dollar amounts, unless you want to place budget dollars on the account in the same transaction.

When estimating (budgeting) the number of units of something that will be sold and reported, it should be with the intent of making the best estimate possible. Then, when a report, such as the one in this article, indicates that there is an anomaly (an apparent mismatch between dollar and unit variances), it is time to apply some management attention to the situation. Perhaps the initial unit sales assumptions were incorrect. If so, their rationale should be examined. If the rationale is incorrect, it could be affecting management decisions in other financial areas.

If this type of insightful analysis might be useful to you, contact us for more information!

Did You Know...?

Reports/Forms Designer. When creating a General Ledger Activity type of report, you may select information to appear on the report that comes from the account tables (this includes classifications, descriptions and demographics, for example). Organization information, such as vendors, that are involved in account transaction may also be included. Detailed debit and credit information for any transaction may be included, and this means actuals, budgets, encumbrances, and other posting types (and their units) may be included. This is only some of the information that can be placed in a report in only a few minutes!

Cash Receipts. When entering a cash receipt for a customer, the system automatically looks at the customer history to see what module is recording that the customer owes you money. Tabs on the form then appear for the appropriate modules (such as property tax, or utility billing). The payment may then be easily applied to the correct item, or even a new item.

Fixed Assets. Assets are comprised of components. Each component, may be marked to post to General Ledger, or not. This can allow you to record assets that may already have their amounts included in the General Ledger asset account.