Description

For large jobs which extend over a long period of time from start to completion, progress payments and partial billing are a good and tested solution to improve cash flow and minimize the risk of a bad debt.

Cyrious's Progress Billing option through the % Complete sliders is an excellent and easy way to create and issue a progress-billed invoice. In fact, we have observed it is an enormous time-saver over alternative methods such as manually splitting orders. However, there are a number of situations which a company must anticipate and prepare for when setting out to use this capability.

We have been using this feature ever more regularly for more than two years now and have encountered a number of problems which we hope by sharing here we can help other users just embarking on the use of this great tool avoid or solve more efficiently. Please note that in the interest of brevity and readability we have not included source code for the macros. If any user would like the souirce code for the macros or the rpt files for the Crystal reports shown in this posting, we are happy to share these. Just email steve@acornsign.com or andyp@acornsign.com and we will send them to you.

Creating a progress bill with the % complete sliders is deceptively simple. Move the slider to the % you want to bill. What happens when you do that is Cyrious will create GL entries which move the % of the line item total price billed into an Income and Tax, if applicable, Account and reduce the WIP balance. Cyrious has a Crystal report which will create an invoice from these selections. For more complex invoicing – E.g. where you need to show invoiced amounts net of retainage, you will need to use a custom invoice report.

Cautions & Suggestions

It is important to note what does not happen with the sliders and some resultant rules and reports we have found important to adopt:

Establish A Numbering System For Progress Payment Invoices

1. Most companies log in and track their invoices by invoice number. Obviously, the Cyrious Invoice Number does not work for Progress Payment invoices because each one needs to have its own unique number. However, we wanted to keep the invoice as closely tied to our Cyrious data as possible; so the system we adopted was to concatenate the day of the invoice to the Cyrious Order number. To keep it simple, we gambled that we won't invoice the customer on the same day in different years and use the day's date as the addition, as shown below:

Amounts Due For Progress Payment Invoices Are Not In Standard Receivables Aging Reports. Contract For or Create Custom Reports.

2. Since the order is technically in WIP, the amounts due will not appear in any aging report. To track progress billed invoices and flag those which are 30,60, 90 days and more past due, you must create your own custom reports. Also, you must have a means of knowing which slider entries apply to which invoice - see item 3 below; The picture below shows an excerpt from the Receivable Report with entries for a single order still in WIP status with 8 Progress Billings netted for payment terms, payments and retainage.

If Progress Billed Invoices Exceed the Order Total, Correct Immediately at the time of Occurrence.

3.. Cyrious will not prevent invoicing more than the value of the order. This situation is easily created if a customer credit is introduced into an order which is Progress Billed. We have added a macro to alert users that this situation has been created so they can take effective action at the time of occurrence; Similarly, when an order has progress billed line items, a negative line item entry must be billed at 100% for receivable reports to be accurate. Additionally, the customer must be sent some form of Credit Notice or Credit Invoice so their records match the Cyrious records. Cyrious has no standard alerts to this situation so we also adopted a macro to alert the biller/user when this occurs which is triggered in our case when the Order Status is changed to Sale. The messages of both macros are shown below:

Make A Rule That All Slider Entries For a Progress Payment Invoice Be On The Same Day

4. Cyrious does not provide any handles to know which slider entries are to be attributed to which invoice. In other words there is no counter or invoice number associated with a progress billing. The only method we have found effective to isolate progress billings is the date on which they are made which is recorded in the EntryDateTime field of the Ledger. For this reason we have found it essential to have an internal rule that all progress billings must be started and completed on the same day;

Plan a Progress Payment Invoice Before Moving The Sliders

5A.. When creating multiple progress billing invoices, it is often important to have a report on how what % of each line item has already been invoiced, particularly if the billing instructions are created by a Salesperson with someone else making the entries. This avoids mistakes and confusion when the biller, for example, is instructed to bill x% of the line item only to find that more than that has been invoiced. An example of a partial Worksheet is shown below;

5B.. Progress billing errors are easy to correct but the correction can complicate reports. In other words, if a line item is invoiced at 50% and sometime later it is determined it should be 40%, simply move the slider back to 40%. But that move will create negative entries in the GL reversing the previous entries on another date. If the invoicing system is based on ledger dates, your reports might indicate that a negative $ invoice was sent. Because we want to avoid any ledger entries which are not tied to an invoice, we stress the importance of planning a progress payment invoice prior to making and saving the slider entries. For this we created a separate Excel planning sheet which shows all the line items, any progress payments already made, and a column for creating and previewing proposed slider entries. This permits the user to see the actual results – i.e. total $$ to be invoiced – before creating GL entries which might need to be reversed later. Below is a picture of the Excel file created by right-clicking on the Order's Print Selection of the Action Toolbar and selecting this report;

Make A Rule That Child Items Be Invoiced At The Same % As The Parent

6.. It is possible to create different percentage completions for parent and child items; but that scenario makes reporting and aging the resultant receivable a daunting challenge. For this reason we have established a rule that all child items must be invoiced at the same percentage age their parent. Fortunately, this is the Cyrious default and separate percentages for parent and child line items can be create only by un-checking the Default check box to the right of the child line item description. We wish there were a systems setting where we could remove that option; but a house rile that line items must have this field checked seems to have sufficed;

For Balance Due Report Use Custom Reporting Not The TransHeader Balance Due For Progress Billed Orders

7. Cyrious does not net the Progress Payment invoices against payments until the order is marked Sale. For this reason when there are deposits or payments made on orders with progress payments, the Cyrious Balance Due field will not be physically accurate. (We say physically because there are technical arguments about what the balance is before the Order is marked Sale.) You must create a custom Balance Due report from the Ledger.

Contract For Or Create Reports Which Show Progress Billing History

8.. We occasionally need to know what amounts have been progess billed, when and by whom to answer customer inquiries or other adminstrative requirements. A custom report was created for this purpose.

WIP Reports and WIP Balances Will Not Show Accurate Balances Unless Based on GL Entries. Revise WIP Reports to Reflect Progress Payment Invoices

9. If the amounts progress billed are not a material portion of WIP, the WIP balance is not an issue. For us, the amounts progress billed can range from 20% to 30% or more of the WIP; so to predict and/or plan monthly, quarterly, annual, etc. sales – we needed to rewrite the reports to show amounts progress billed and deduct them from the Order's Total Price for accurate forecasting. For this purpose we have created the concept “WIP Value”. which is the Total Price minus Progress Billings. The example below shows a sample report.

It is possible to retrieve Progress Billed data from Crystal and in fact its Grouping capabilities can make it easier. However, since we are pulling a lot of records deom the Ledger which then have to be grouped and summed, some reports can take an unacceptably long time to run. So, we have had to use the Crystal Command function to embed SQL to pull the data. Additionally, we found that we were rewriting the same code so often, we created a number of SQL functions to pull PB data - E.g. how much has been Progress Billed for an Order.

Below is the SQL code which retrieves the amount invoiced for an order. If you have some familiarity with SQL, you can modify this code to get most of what you want – I.e. to get the amounts invoiced on a specific date, Group the query by Ledger.EntryDateTime. Also, for this installation, the GLClasssificationType codes for Income and Taxes are in the 4000-4999 and 2005 ranges respectively, For installations where that is not the case, the correct numbers would need to be substituted.

Create Function dbo.ufnGetAmtProgressBilled(@THID numeric)
Returns FLOAT
AS
BEGIN
Declare @AmountBilled Float;
Select @AmountBilled = sum(Amount)
From Ledger LG with(nolock)
Where (
       LG.TransactionID = @THID AND
       (LG.GLClassificationType between 4000 and 4999
	      OR LG.GLClassificationType = 2005)
	  )
Group By LG.TransactionID
Return @AmountBilled
END
GO
Select
dbo.ufnGetAmtProgressBilled(139926)
DROP FUNCTION dbo.ufnGetAmtProgressBilled

For those who wish to use Crystal, the picture below with the tables and their linkages might prove helpful. The Salesperson and Project Manager tables are aliased Employee tables.

Contributor: Steve Gillispie, Acorn Sign Graphics

Date: 9/12/2017

Version: Control 6.1

You could leave a comment if you were logged in.