Invoicing to the PENNY

[LaRetta]

Hi everyone,

A different post reminded me of an issue I need to resolve. I am working in the Credits piece so this is opportune time to seek assistance.

We receive 500+ purchase orders a month from various companies. Our computers never match on the final invoice total because each company rounds differently. Currently, Sales Reps have to add a "Rounding Adjustment" which adds/subtracts the difference. This prints as "Rounding Adjustment" on the Invoice below Shipping Charge and Payment and is restricted to +/- $1.00. I think I'm blowing it though because I get calls from stores because their Accountants want to APPLY this amount and ask 1) which product does it belong to, 2) is this a shipping adjustment or 3) they say it can't be applied only against the payment because the payment is a breakdown of the LineItems!! Some companies extend lineitems 2, 3, 4 and even 5 places; some round up, some down; and some truncate. Some extend lineitems out 3 decimals and only round the Invoice total itself to two. I was an Accountant, for goodness sakes! And I have NEVER understood how to resolve this issue properly since computers took it over.

It dawned on me today that FileMaker is quite capable of the flexibility and math required to pull this off. Answer: Establish 'pricing rules' for each Payer. Determine their rounding method and pull that into my Invoicing process. I want our Invoice to perfectly match our Payer. I know WHAT I want but not how best to pull it off. Our Product Price must look at this RULE and adjust accordingly when a LineItem is added. I have a Pricing file which holds special customer pricing, ie, if agreement in place that they pay $23.4999 for a product instead of the standard $23.50. I suppose the rules should be placed in this table (or an associated table?). I started to create a test file. But all I can come up with is one text field which will hold the final formulae using Evaluate(). But each piece of this formula must be User-determined by answering a series of questions first!! So each piece (question) must be a field??

Questions such as:

1) Customer rounds on each extended LineItem or only Invoice Total? LineItem, Invoice

2) If LineItem, customer extends to how many digits? 2, 3, 4, 5

3) Customer rounding method on extended LineItem? Round up/round down /Truncate

4) How many decimals in final rounding? 2, 3, 4, 5

5) Is there also rounding on Invoice total? Y/N

Do you see my dilemma? Some customers want to see the decimals out as far as they indicate on their Purchase Orders – which can be 5 places - we deal with some large chain stores. But I don’t want 23.00000 to display on customers who never use 5 places (we also have many customers who only deal in 2 decimals. I have actually gotten complaints about the unnecessary zeros! And I don’t want to create different layouts for each numeric format because that doesn't itself solve the problem of the final invoice total not matching!

I feel like I’m splitting hairs here. But the inconsistencies are driving me nuts. What I want to set in my LineItems is the specific data result which may be 23.00049, I think, instead of using field format on our end. Many of their computers are incapable of adjusting to FM … but FM is not incapable of adjusting to THEM. I envision User answering questions and filling in the parts of this rule but I have no idea where to begin. I have also tried to determine their rules and it’s not always easy. I’ve even asked a few companies and they don’t know – they say their computer just spits it out!!! ROFLMAO!! How are others handling this situation? And is it possible to devise a set of rules (per Customer) to resolve this issue? I appreciate any feedback I can get!!

LaRetta

[comment]

Interesting question. If it were me, I'd tell them to get lost. Why does my Invoice total have to match your Order total? Check your prices - did you get the agreed price? Is the total a correct calculation of Sum ( price * quantity ), to the nearest cent? Then shut up, pay the Invoice and move on.

Now back to the real world:

I may not be aware of ALL the options, but it seems you need 6 fields for customer's preferences:

ROUNDING:

1. Round line items (extended price) method: None/Normal/Ceiling/Floor

2. Round line items precision: (number of decimal places)

3. Round Invoice total method: None/Normal/Ceiling/Floor

4. Round Invoice total precision: (number of decimal places)

PRINTING:

5. Print line items precision: (number of decimal places)

6. Print Invoice total precision: (number of decimal places)

Example of calculating extended price:

Let ( [

extendedPrice = Price * Quantity ;

method = Customers::RoundLineItemsMethod ;

precision = Customers::RoundLineItemsPrecision

] ;

Case (

method = "None" ;

extendedPrice ;

method = "Normal" ;

Round ( extendedPrice ; precision ) ;

method = "Ceiling" ;

Ceiling ( extendedPrice * 10^precision ) / 10^precision ;

method= "Floor" ;

Floor ( extendedPrice * 10^precision ) / 10^precision ;

)

)

You would add 2 fields for printing alone, along the lines of:

"$" &

Case ( n >= 1000 ; Div ( n ; 1000 ) & "," )

&

Right ( Int ( n ) ; 3 )

& "." &

Right ( 10^precision & Round ( n ; precision ) * 10^precision ; precision )

where n is once the rounded line item extended price, and the rounded invoice total the second time.

[LaRetta]

YES!!! Houston? We have ignition!! It's perfect!!

There may be other exceptions but if so, I couldn't find them (and I worked it pretty hard)! Power Of (^) is really sweet! I had to create a test calc of 10^4 (and view the results) to understand the 10^Precision portion. Is Power Of() as simple as 10 * 10 * 10 * 10? It's quite, ummm, powerful! FM Help is a bit weak on it and I must have been playing hookie the day it was taught in school.

I haven't tried the printing aspect yet but can easily see it will work a treat also!! This solution is amazingly simple (ELEGANT) and it will save my sanity as well as releasing staff from the burden of constant adjustments on PO customers. I NEVER would have thought of any of this, Michael! Thank you so much!

LaRetta

[SlimJim]

Comment said Now back to the real world.

In the real world how many of your customers have any idea what you are talking about when you ask them their rounding rules? Perhaps you only have to deal with accountants.

Our accounts are littered with penny amounts which the customer hasn't paid because they recalculate the tax on the invoice after they receive it (I believe this is illegal in the UK but what can you do). However, their reasoning is sound! Customs and Excise give two methods of rounding for VAT on line-items which can lead to half a penny per item alterations in the total tax.

LaRetta: Good luck with getting this information out of them. I guess you will get quite a few duh! answers.

[LaRetta]

Hi Jim,

We don't have this issue on regular retail customers. It's only large chains (46% of our business) who send purchase orders ahead of time, ie, our Net customers with special pricing or discounts. But it would work on any customer who sends in what THEY think they owe. If there is logic there, we can match it. It's been quite easy to figure ... I used their prior PO to tell me. By viewing each invoice against each piece of the calculation (and changing the method via popup), their computer's *reasoning* pops into sight! At least (knock on wood), it's holding true so far! It will be easy to apply theory to a new customer on their first PO to glean their thinking and then maintain that preference in their customer Pricing Template.

I DID come across an instance where the LineItems were rounded down but their Invoice total was rounded UP!!! Go figure! Maybe the Developer was attempting to 'balance the books' him/herself!

Also, it will be very easy to 'change' a method because I've already got it in place. This is controlled in Browse remember ... not hard-coded into calculations. Let 'em change - we'll know how they got their figures!!

[comment]

I don't want to give you any ideas, and I am not even sure if this is a true story or an urban legend, but the story tells of a programmer who truncated billing to the nearest cent and diverted all fractions of cent to his account. Working for a phone company, or something of the kind, he managed to steal a few milions that way.

[LaRetta]

What has irked me is 1) bloating LineItems needlessly and 2) staff time spent on the process. These Rounding Adjustments generate 7,000 LineItems a year!! Talk about wasted fluff! Costs mount when you consider a) staff time wasted in entering an adjustment on original invoice, staff errors on entering them and another staff having to correct it, and c) staff would forget to enter the adjustment at all and mis-match took accounting time (sometimes requiring re-faxing corrected invoices or long-distance phone calls) etc. The efficiency of automating this 'seemingly' simple piece will save this business several hundred dollars a year. So Michael, your post has saved us several hundred dollars PER YEAR.

I had one executive once ... not from this business ... that scoffed when I told him one simple implementation of a certain process would save the company $300 a year. He said it was 'inconsequential.' So I informed him that, since it meant nothing to him, I had no problem with him tacking that onto my pay check instead. Bottom line ... he didn't transfer it to me and we ended up implementing the process.

So much money/time is wasted in administration on these 'inconsequential' issues. A printer that regularly jams can pay for a new printer that WORKS in only 3 months when you consider (what I call) staff-dink time! These are the inefficiencies that drive me insane and precisely why I'm in database management. Everyone thinks FM is just to get a job done. It isn't. It's most powerful use is data modeling - including staff monitoring and modeling efficiency of process. You see why I get excited about saving nanoseconds in one calculation?

Ok. I'll shut up. My passion is showing ...

LaRetta