Author: Michael

How to set the Aged AR Trial Balance default Aging Date

It’s always a pain to have to change the Default Aging Date field when printing the AR Aged Trial Balance by Job report. It’s an easy program change, but the Maxwell Management Suite requires adding a line of code to the standard CARRE0 program that prints that report. Here’s that line of code:

10041 IF X$(41,2)+X$(37,2)+X$(31,2)=FISCAL_YR.VAL$+FISCAL_PD.VAL$ 
THEN LET AGING_DATE.VAL$=X$(41,2)+X$(37,2)+X$(31,2)+X$(34,2)

This change will set the Default Aging Date to the current terminal date, only if the current terminal date is in the Period and Fiscal Year ending date selected for the report.

Reporting Actual Service Hours via SPD and SPI Prefixes

When trying to reconcile Actual Payroll Hours posted to Service Work Orders, Maxwell Management Suite software offers two Formatted Report Prefixes, SPD (History/Billed Tickets), and SPI (Current/Unbilled Tickets), that give you access to the Actual Payroll Hours spent on Work Orders.

The problem is field (F035) in the SPD/SPI Prefixes is called Actual Quantity/Hours, meaning Actual Hours shares the field with Actual Quantity. Actual Quantity is the quantity of material items used on that Work Order, for example, from an Inventory Material Requisition. So the question is, “How do you separate Actual Hours from Actual Quantity when trying to analyze Payroll hours?” Here are several solutions.

Using the Unit of Measure Field to determine if it is a Payroll transaction

The first method I used to isolate the Hours from the Quantity involved using the Unit of Measure (F023), which assmes all Payroll transactions have a Unit of Measure of “HR” (hours). I created a Formatter Field (F500) and tried to use this definition:

F035;IF F023="HR" THEN X7$="0"

Unfortunately, the program kept reporting a syntax error with that equation. A little detective work found the ‘code parser’ was converting the F023 into an invalid variable “J(-6)” and testing that numeric variable against a character string. The parser didn’t seem to like the “F023”. Here’s what Formatter Field Maintenance reported as the incorrect parsed code:

LET A=J(6);IF J(-6)="HR" THEN X7$="0"

So, remembering back to old days, found that Maxwell still kept a string array U$, where each element contained that particular field value (e.g. U$[23] is the Unit of Measure). So using that array allowed for a successful field definition. Essentially, the definition says, “assign the Actual Quantity/Hours (F035) to our variable, but if Unit of Measure (U$[23]) is NOT “HR” then zero out our variable (Actual Hours).” Note that X7$ is the current field. Here’s the completed definition that worked:

F035;IF U$[23]<>"HR" THEN X7$="0"

Using the Source Code Field to determine if it is a Payroll transaction

The problem with using the Unit of Measure field is that it is not completely accurate. Meaning, a Work Order could also contain subcontract hours that use the Unit of Measure of “HR”. The only other accessible field that signifies a Payroll cost, is the Ticket Actual Cost Source Field (F049). When the Payroll Journal posts the Actual Cost transactions to Service, it uses a “1” representing the Payroll Journal, plus the Journal Date, plus the Report ID, to form the Ticket Actual Cost Source Field. So the solution was to use the first digit of the field to find out if the transaction originated in Payroll. Here’s the definition that worked, and proved more accurate than using the Unit of Measure field:

F035; IF MID(U$[49],1,1)<>"1" THEN X7$="0"

One other note: With Version 6.5.3 of Maxwell Management Suite, the Formatter Field specification for Actual Quantity/Hours (F035) is only defined for a field length of 11, but is stated to be a length of 15 in the Data Dictionary. To make the above examples work, the “SPD 035” and “SPI 035” records in CSCOD and CSCFF need to be corrected to reflect the correct field length.

A better Unbilled Job Report

The Unbilled Job Report in Accounts Receivable Billing is designed to report Jobs that are unbilled.  The problem with the report is that it requires a link from the Job Cost Task Codes to the Job Billing Line Items (which is not always a reasonable approach).

A better method of determining your ‘unbilled’ status on a Job is to create a Job Cost Formatted Report with the Date Last Billed, Contract Amount, Amount Billed, and a field called Amount Unbilled that is calculated by subtracting the Amount Billed from the Contract Amount field.

Then a filter that shows Job where the Amount Unbilled is not equal to zero will provide a useful report.

Reporting Worker’s Compensation Earnings from Payroll Detail

Periodically, Payroll Insurance Groups are not setup correctly causing incorrect Worker’s Compensation Earnings on the Payroll Insurance Report. And you can just guess how Worker’s Compensation auditors get grouchy when earnings don’t “add up”!

Fortunately, the Formatted Reports PR2 prefix, Time Card Detail, gives you access to the various earnings categories for each and every timecard transaction. Here are those fields:

110 Regular Earnings 
111 Overtime Earnings 
112 Premium Earnings 
113 Sick Earnings 
114 Holiday Earnings 
115 Vacation Earnings 
116 Bonus Earnings 
117 Per Diem (non-taxable) 
118 Expense (non-taxable)
119 Misc Earnings 
120 Other Earnings (total of 113+114+115+116+117+118+119+Union Add-On Pay)
Union Add-On Pay (is 120-(113+114+115+116+117+118+119))

So, using the PR2 prefix, earnings can be reported not only by Insurance Group (Worker’s Comp Code), and can be sorted by Job if necessary. Very helpful if you have Jobs that are wrap-up (owner paid Worker’s Comp).

What does the Purchase History by Job Report really report?

Until today, I never understood what the Purchase History by Job Report really reported. After a little research I found that the title of that report is a bit misleading. It does not report purchases, but rather it reports Purchase Order lines that have been completely received and invoiced (via AP).

Here’s some examples. For stock item, the amount of a given Purchase Order line will not be reported until the full quantity ordered is both received and invoices. For a Lump Sum amount, the report will only reflect the amount when the whole lump sum amount is fully invoiced by the Vendor.

Any lines of a Purchase Order that are partially invoiced, or partially received, will not be reflected on the report.

I vote that the name of the report be changed to “Completed Purchase History by Job Report”.

How to add a new Cash Account

If you are starting a new bank (cash) account you will need to setup that account in the various modules of Maxwell Management Suite.

General Ledger

  • Add the new Account via General Ledger, GL Files Maintenance, Account Maintenance
  • Add the Account in General Ledger, GL Files Maintenance, Bank Rec Account Maintenance

Payroll

  • In Payroll, Payroll Files Maintenance, GL Posting Account Maint, change the Cash and Direct Deposit Accounts.
  • In Payroll, Employee Files Maintenance, Direct Deposit, Direct Deposit G/L Account Maintenance add the new account
  • When doing Pay Period Initialization, make sure you refer to the new Account.

Accounts Payable

  • When doing the Accounts Payable, Payment Processing, Check Printing, it will ask for the Cash Account. Once you enter the new Account it will default to that in the future.

Accounts Receivable

  • When you do the Accounts Receivable, Cash Receipt Journal, it will ask for the Cash Account. Once you enter the new Account it will default to that in the future.

That’s it. Now go process those checks!

How is line 1 of the Quarterly 941 calculated?

Recently, a Maxwell Management Suite customer asked how to verify the number of employees that printed on Line 1 of the Quarterly 941 Report. That line is labeled “Number of employees who received wages, tips, or other compensation for the pay period including:Mar. 12 (Quarter 1), June 12 (Quarter 2), Sept. 12 (Quarter 3), Dec. 12 (Quarter 4)”. Simply stated, the count is based on the number of paychecks issued during a “pay period” encompassing the 12th calendar day of March, June, September, and December.

But the trick, of course, is knowing the date range used for those “pay periods”. A quick look at the CPR941 program showed that for the 1st quarter, if an employee is paid weekly (pay cycle), they will be counted if they received a check dated from March 12 to March 18. Of course, each pay cycle tests for a different set of date ranges as follows:
* Weekly: 12th to 18th
* Bi-Weekly: 12th to 25th
* Semi-Monthly: 1st to 17th
* Monthly: 1st to 31st

One thing to note is if an employee received two paychecks in one of those date ranges, they will be counted twice.

So if your employees are paid weekly, verify line 1 of the 941 by printing the YTD Detail PR Register, for all employees, for March 12 to March 18 and count the number of checks that print on the report.

Is remote timecard collection possible?

When it comes to construction companies, I’ve noticed there are not many successful examples of remote timecard collection projects . Here are my thoughts on why there isn’t much ‘observable’ success, and what is necessary to actually implement a successful remote timecard collection strategy.

There are no simple answers as to why there very few examples of successful remote timecard collection projects. Most likely, it is because few companies are willing to bear the expense and effort to make the ‘culture’ changes necessary for such a project to succeed. It requires a company-wide effort, including a motivated management team, to insure success of something as delicate as a new time-keeping system!

Who, What, Where, When?

One of the difficult barriers in dealing with ‘remote timecard collection’ is the physical manner in which said devices will be used. A big hurdle is developing a process the field personnel will utilize. It involves the old who, what, where, when questions:

  1. Who does the recording of the data? Is it the employee, the foreman/supervisor, or an on-site administrator?
  2. What kind of device might succeed in recording the data? A scanner, a biometric device, a barcode reader, a handheld computer?
  3. Where does the recording of time happen? At the point the employee walks onto the site, in the foreman’s truck or job trailer, or at the actual point of where the work is happening?
  4. When does the recording of time happen? Beginning/ending of the shift, each time the ‘Task’ being accomplished changes, or at the end of the shift?

What device to use?

Another barricade to success is determining the technology (type of device) that might be used to collect the timecard data. When looking at the actual timecard collection devices, these questions should be addressed:

  1. Can the pertinent data be collected by the time-card device selected? At a minimum, you need the date worked, employee number, job/project number, task code (phase of work), and the number of hours (regular, overtime, premium).
  2. In what from is the data exported from the device? Can it be e-mailed? Put on a floppy? Sent wireless? Sent via the Internet?
  3. Can the device survive the environment it must work in? Cold, hot, dust, dropped, bumped, lost?
  4. What kind of money is budgeted for each device?

Note: I do think most time-card collection devices will output some kind of file, and even if that file can’t be directly imported into a payroll system, at least the data can be pre-processed and then imported. Actually, this is the area most easily controlled.

A successful example

An example of a successful data collection process involved a client that was motivated and willing to spend the necessary money to succeed. Here’s what made it work:

  1. A biometric device (a handprint device) was used to record employees coming into the job-site and leaving the job-site. Actually, because of the volume of employees, two handprint devices were used so the employees didn’t go berserk waiting in line to get in/out of the job-site.
  2. The time data was stored on a local computer (running the third-party application that monitored the biometric devices) at the job-site trailer then exported from that computer and emailed to ‘accounting’.
  3. The e-mailed data was put through a pre-process (custom code to overcome what the timecard collection output failed to do) step, and finally imported into the payroll system.

The hard questions

Ultimately, the big questions about implementing a timecard collection process centers on whether the relevant data, like the task (phase of work), can be captured, and can the company get the field employees to make that happen?

Finally, remember this–sometimes, the best timecard collection device is the simple paper and pencil.

Maxwell Management Suite Version 6.5 and 2007 Year-End

The lastest version of the Maxwell Management Suite Version 6.5 and the Year-End 2007 updates were shipped to customers this week.

Among the new features are:

  • Can cause each A/P Invoice to be print on its own separate check
  • Can group invoices on one check by use of a Group ID in Payment Selection
  • Can import Material Requisition transactions via DataLink Import Prefix IC2
  • Grid-style DataLink Import Work File Maintenance
  • DataLink Import can import groups of files at one time
  • Equipment Cost Rental and Billing sub-system
  • Fixed Assets made graphical
  • Fixed Assets Category added to G/L Account
  • Payroll enhanced EEO Reporting
  • Payroll accrual capability added
  • Recurring Pay items such as payment for car allowance
  • Drug Selection Report
  • Exclude certain Rate Codes from the Certified Payroll Report.
  • Report Prefix PRU (Union Fringe, Deductions, Agreements) added to Formatted Reports
  • Plain Paper 4-up Laser W2 printing
  • Numerous Service Management Dispatch Board enhancements including 2-week view
  • Delineate taxable and non-taxable portions in Service Contracts
  • SubContract ability to Generate Pay Requisitions
  • Multiple AIA formats in A/R Job Billing
  • Enhanced Operator Code control features

Resources:
Maxwell Management Suite
Maxwell Systems