Shipment Reconciliation

Big business relies on small business to get the job done.  Critical supply chain processes are often subcontracted to small businesses.  Konduit’s Transaction Editor (TEd) Service has been used by small businesses to keep up with the accounting demands of large clients.  I frequently get the comment that our tracking is better than their large client’s, and this saves them much time and money reconciling differences that would otherwise lead to delayed payments or disagreements.

A farm cooperative has been using Transaction Editor since 2006 to schedule and reconcile deliveries by over sixty member shippers primarily to a single large processing plant. During harvest season from September through July each year, shippers operate 24 hours a day, seven days a week, averaging 1250 shipments per month with a peak of over 5000 shipments in November. The system’s primary Table manages over 185,000 Rows of current and historical data.

The shipment reconciliation process starts with the farm cooperative generating a series of Load Numbers for a particular coop Member.  Included in the generated Load Number is a reference to the Member’s Sales Contract Number plus a sequential Load Sequence Number and a random Load Authorization Code.

When a driver arrives at the Member’s loading scale to receive some bulk material,  Member users log into a Member’s Portal and select from a displayed list of Member-specific Load Numbers.   This link presents them with a form they use to update a shipment record with weight and temperature readings for the Load.  Seal numbers, driver name, and various other information is also updated on the form.   The Portal allows them to print a bar-coded Bill of Lading for the driver to carry.

During transportation, bulk materials can often change in weight, volume, or quantity due to weather conditions, road conditions, vehicle conditions, or driver error.  At the destination, the consignee takes weight and temperature readings with their own systems.  At the end of the day, the consignee produces a file of these shipment measurements from their internal systems for the day’s deliveries and delivers this file to the farm cooperative for shipment reconciliation.

This shipment reconciliation is critical to the accounting that leads to payment or the filing of carrier loss claims.  The cooperative uses Transaction Editor to import the consignee’s daily file and a Report compares it with the Member shipment information.  Several custom Transaction Editor Reports allow the cooperative to  reconcile differences and settle commodity contracts. Their system is quite complex, yet the process of shipment reconciliation is flexible and forgiving.  Every Transaction Editor system includes a data change history audit trail, which forgives human error by allowing the User to investigate and recover from data editing mistakes.

Order Processing and Billing Software

I recently came across a project posted on a freelancer site looking for tight integration with QuickBooks.  The problem is that QuickBooks doesn’t allow data integration, and customization is limited to formatting the appearance of your invoice.  The older products used to use a format called IIF.  When that became public domain, QuickBooks changed their “interchange” format to QIF so that they could legally restrict its proprietary use.  That quickly devolved into another proprietary format, OFX, which was never widely adopted.  More recently, Intuit now allows importing of sales transactions using the Bank Statement interface which accepts any comma-separated (CSV) file with either three (signed) or four columns (debit/credit).   But this is not well known or documented.

QuickBooks provides basic “accounting” functions like receivables reporting, expense journaling, general ledger and financial statements.  But if your business has a complex pricing or estimating methodology, custom order processing and billing software will allow you to automate and codify your business processes without resorting to spreasheets, which is what most people start out doing.  Custom order processing and billing software allows you to print or email invoices, whose revenue transactions can later be imported into QuickBooks for financial accounting.  Importing sales transactions as Bank Statement deposits can be mapped to specific GL account types within QuickBooks by using predefined Descriptions for the transactions.

As your business becomes more successful, you can easily outgrow a  spreadhseet order processing and billing system.   This is where Konduit’s Transaction Editor Service can help you succeed.  For one affordable monthly fee we design the database, develop the application, host, train, and provide continuous support for your staff.  We help you to optimize your workflows that make your business run.

The Transaction Editor Service allows for easy changes and additions to data structures, so you don’t have to know all your needs up front.  We can start with what you do know, and as you work with the system, we refine the structures and workflows as needed.  This removes a tremendous amount of risk associated with communicating requirements of software development projects in advance.

Contact Konduit for a free Solutions Advisor consultation.

Database Design is Easy — Or Is It?

I came across a Request for Proposal from a government entity whose sole purpose was to conform to the data collection standards of a superior government entity.   The following entry is paraphrased for brevity.

Yes/No Questions. The “yes” response and the “no” response each has its own column in the spreadsheet tool.   To specify “yes” for a given question, the user should enter a “Y” in the given question’s “yes” column. To specify “no” for a given question, the user should enter an “N” in the given question’s “no” column.  One question on the form is an exception to this “standard” where there is only one column in the spreadsheet tool for this question, and users should enter a “Y” for “yes”  and “N” for “no”.

This was written by professionals who should be in a different profession.  I struggle with how to explain why this is fubar.  So here goes.

First of all, these are questions on a form, questions, mind you, with only two answers, “yes” or “no”.  Really.  How complicated can that be?  Apparently, very complicated.  If two responses (one in the Yes column and the other in the No column) are allowed, how will one know which response reflects the facts of the respondent?

The noted “exception” should actually be the “standard”.   Most likely, this was written by lawyers to allow for flexible interpretation of the law when enforcing compliance so as not to hurt anyone’s feelings.  Or maybe it exists to confuse the respondent, so they will give up filling out the form entirely to seek out other sources of assistance.  Whatever the reason, the person designing such forms should be in a different line of work because either their brain or their heart are not in it for the right reason.

Why is this important?  Say you wanted to report on the number of respondents who replied to a particular question.  Count the ones who respond “yes”.  Count the ones who respond “no”.  Simple, right?  OK.   Now calculate the percentage of respondents for each.  Well, if some put down “Y” in the Yes column AND “N” in the No column, the sum of the counts of each would be more than the total number of respondents.

Now, image you are a legislative leader and you are reading a compliance report based on these statistics in order to place a vote for a multi-billion dollar extension of the program.  You might see that 65% said “Yes” and 63% said “No” to a particular question on which you would like to base your decision.   Or maybe, the data analyst preparing the “required pie chart” report will recognize the data problem, and fudge the responses so the chart reflects HIS political preferences.   Democracy in action.

I have all the answers, its knowing the right the questions to ask that is the challenge.

For more examples of database development challenges, please visit my favorite website, www.dbdebunk.com.  These design problems are not limited to government entities.  There are plenty of examples in commercial enterprises.