One of the common requests received by our construction software consultants is for the modification and or development of work in process (WIP) reports. Some of our existing customers using Sage 300 Construction and Real Estate (aka Sage 300 CRE) think that it’s too difficult to create a WIP design right within the software itself. We’re here to tell you it’s not – and in this article, we’ll show you how it works.
Moving WIP Schedules Out of Excel
Most of our new clients maintain WIP reports outside of Sage 300 CRE for various reasons. But most think that it is too difficult to create a WIP report in the software because of the variability of information that is presented. I can attest as a former user responsible for frequent and timely production of WIPs, it is possible and often much easier than maintaining schedules outside the system in Microsoft Excel.
Very simply, if your organization is using the native functionality of Sage 300 CRE, then WIPs can be produced. Obviously, the supporting data used to generate the report(s) must be timely and accurate otherwise the reports will produce inaccurate information.
It’s All About Best Practices Workflow
It is key that a best practices workflow be implemented to insure the flow of information is posted in the system to support the reports. One of the biggest issues we encounter is un-posted activity in both contracts and job cost.
Another frequent issue relates to the timely processing of change orders to insure they are reflected both in the revised schedule of values and revised commitments. Timely posting and closing of both the contracts and job cost module is essential to guarantee that your data is being properly reflected. I often recommend using the month end closing workflow wizard to insure the aforementioned is addressed before final report generation.
Sage 300 CRE WIPs, Tips, and Tricks
It is not uncommon to have several versions of a WIP. Often I develop separate reports for internal reporting and external distribution to bonding companies, accountants, etc. When developing WIPs, data should be sourced from the Job Cost transaction records. The transaction file of job cost (Current.JCT) is a very powerful warehouse of information and provides almost limitless report and inquiry configuration options for WIPs and other analysis reports. Reports derived from the Master file (Master.JCM) may run faster, but do not provide date sensitivity due to their real time updating based on posting activity.
Another trick is to always attempt to build from the lowest record of the Job Cost hierarchy. Typically, this is the category. You can determine your organization’s hierarchy settings by referencing the JC Settings area of Job Cost.
By starting at the lowest hierarchical data entry source, it is possible to evaluate estimates, buyouts, commitments and costs which may affect the results calculated. It is always easier to aggregate transactions to a higher level, such as the job level, than to try and rebuild a report to accommodate details. I often build a report to reflect details and then summarize reports using Group Totals in Crystal Reports or Defining Totals in the Sage 300 CRE Report Designer. By building WIPs in this manner it is easy to “proof” the values from detailed activity to the highest level which is typically Job.
WIP Report Challenges to Consider
A WIP is probably one of the most challenging reports to compile due to the varied types of information used in the report which often include (but not limited to)
- Customer contract values and billings
- Supplier and subcontractor committed values
- Estimated and actual costs
- Derived values from a combination of formulas referencing the prior items
These are the source values for determining the cost to complete and/or cost at completion and cost in excess of billings and billings in excess of costs. While it sounds like it would be easy to reference this information seamlessly, the report sorting and print conditioning could adversely impact your results. If you are building a detailed Cost Code WIP to use as a supporting schedule for the Job WIP, the print conditioning will be different than that used to build a Job based WIP.
Always take care in insuring that all column and field references are using the appropriate print conditions or your presented and calculated results may be incorrect and hard to identify without detailed analysis.
I think of WIP designs as building blocks of information. As I develop the reports, I am always looking for the previous block of information that is pertinent and use that information (typically via formulas) to reference it in the evaluation process. Formula naming is critical in the building process. Using consistent naming conventions for source and aggregated data will make building the report easier. For example, I typically preface formula names by the level they represent (J=Job, CC=Cost Code, CA=Category) in order to quickly find and logically present data. This is especially true when developing “nested” formulas which can often be quite lengthy and hard to read once developed.
As a recap, I have found that the Sage 300 CRE transaction file is a preferable source of data in WIP development. It does take longer to design and for the reports to compile, but the ability to have time sensitive data and detailed versus summary results is a benefit that cannot be overstated. Additionally, once a WIP report is created, it can be copied and modified for the creation of other reports by leveraging the library of formulas and references created for the original source report.
Article by Paul Mink
Senior Consultant, Accordant Company
Need Help Customizing Your WIP Schedules?
Click below to get in touch. One of our consultants will follow up shortly to offer assistance.