sql-ledger-users

[SL] wrong rebatte calculation in invoice/SO

[R. Q. <rq...@gm...>]

Hi,
a customer bought bought 180 pcs of an item at $ 81.50 with a rebatt of 7.5%
Normally the amount should be
180 x 81.50 x (100-7.5)/100 = 13569.75, so I am wondering why I am
getting 13,570.20 in sql-ledger invoice and sale order.
regards

Robert

Re: [SL] wrong rebatte calculation in invoice/SO

[Ashley J G. <agi...@pu...>]

On Mon, 3 Sep 2007, R. Quansah wrote:
> Hi,
> a customer bought bought 180 pcs of an item at $ 81.50 with a rebatt of
> 7.5% Normally the amount should be
> 180 x 81.50 x (100-7.5)/100 = 13569.75, so I am wondering why I am
> getting 13,570.20 in sql-ledger invoice and sale order.
> regards

Looks like the unit price is being discounted, then rounded to two decimal 
places, then the qty is multiplied out. You could probably call that a bug, 
but I suspect rounding is a matter of policy as much as mathematical 
correctness.

-- 
Regards,
	Ashley J Gittins
	web: 	http://www.purple.dropbear.id.au
	jabber: agi...@pu...

Re: [SL] wrong rebatte calculation in invoice/SO

[Jeff R. <je...@jr...>]

it's not

180 x 81.50 x (100-7.5)/100 = 13569.75

It's

180 x (RND,2,(81.50 x .925)) = 13570.20

The item is discounted first, then rounded then multiplied by your 
quantity. This allows you to discount one item while retaining other 
items on the same invoice at full price If you applied the discount to 
the sub total you'd lose the functionality of being able to discount 
individual items.

Jeff


Hi,
a customer bought bought 180 pcs of an item at $ 81.50 with a rebatt of
7.5% Normally the amount should be
180 x 81.50 x (100-7.5)/100 = 13569.75, so I am wondering why I am
getting 13,570.20 in sql-ledger invoice and sale order.
regards

Re: [SL] wrong rebatte calculation in invoice/SO

[Ashley J G. <agi...@pu...>]

On Tue, 4 Sep 2007, Jeff Roberts wrote:
> The item is discounted first, then rounded then multiplied by your
> quantity. This allows you to discount one item while retaining other
> items on the same invoice at full price If you applied the discount to
> the sub total you'd lose the functionality of being able to discount
> individual items.

That is incorrect.

There is nothing problematic with taking a line item, discounting it, 
multiplying out for qty and _then_ rounding, that would have absolutely no 
affect on other line items, and avoids that line item having a 
rounding "error". 

Additionally, it may be arguable that each invoice line should not be rounded 
before total as well, but that's not what Robert is noting here.

(btw, the math you expressed is exactly what I had said earlier - but I am 
guessing you were replying to Robert more so than my post).

-- 
Regards,
	Ashley J Gittins
	web: 	http://www.purple.dropbear.id.au
	jabber: agi...@pu...

Re: [SL] wrong rebatte calculation in invoice/SO

[Jeff R. <je...@jr...>]

Hi Ashley

I think I understand now, You're manually putting in 7.5% discount on 
the line as you create the invoice right?  If that's the case it could 
be argued that it would be better to multiply the quantity by the price 
then apply the discount ( but the difference is in your favour :-) ) 
Maybe a future revision could move the brackets around in the equation 
to do that.

If you're assigning a discount to a vendor on an item then it's a 
different story because the item is 'pulled' from the tables with a 
price that is dependant on the date, pricegroup of the customer, and any 
exceptions or overides in the pricing matrix and there is no way to 
correct a rounding error once the query has run other than to start 
carrying four or five digits on every price just in case there is a high 
enough quantity sold to make a more than marginal difference.

Jeff


Ashley J Gittins wrote:
> On Tue, 4 Sep 2007, Jeff Roberts wrote:
>   
>> The item is discounted first, then rounded then multiplied by your
>> quantity. This allows you to discount one item while retaining other
>> items on the same invoice at full price If you applied the discount to
>> the sub total you'd lose the functionality of being able to discount
>> individual items.
>>     
>
> That is incorrect.
>
> There is nothing problematic with taking a line item, discounting it, 
> multiplying out for qty and _then_ rounding, that would have absolutely no 
> affect on other line items, and avoids that line item having a 
> rounding "error". 
>
> Additionally, it may be arguable that each invoice line should not be rounded 
> before total as well, but that's not what Robert is noting here.
>
> (btw, the math you expressed is exactly what I had said earlier - but I am 
> guessing you were replying to Robert more so than my post).
>
>   

Re: [SL] wrong rebatte calculation in invoice/SO

[Robert S. <ro...@st...>]

Jeff Roberts wrote:
> Hi Ashley
>
> I think I understand now, You're manually putting in 7.5% discount on 
> the line as you create the invoice right?  If that's the case it could 
> be argued that it would be better to multiply the quantity by the price 
> then apply the discount ( but the difference is in your favour :-) ) 
> Maybe a future revision could move the brackets around in the equation 
> to do that.
>
> If you're assigning a discount to a vendor on an item then it's a 
> different story because the item is 'pulled' from the tables with a 
> price that is dependant on the date, pricegroup of the customer, and any 
> exceptions or overides in the pricing matrix and there is no way to 
> correct a rounding error once the query has run other than to start 
> carrying four or five digits on every price just in case there is a high 
> enough quantity sold to make a more than marginal difference.
>
> Jeff
>   
This has been a bit of a problem from a usability point of view.

SQL-Ledger rounds discounts before multiplying by qty. This causes no 
real problem when selling.

However the same thing happens when purchasing and here is where it 
causes problems.

Here's  where the problem arises, every supplier we have encountered 
rounds after multiplying by quantity, so when we it comes time for 
accounts to pay bills there if often a discrepancy between our purchase 
order and the suppliers invoice.

Dieters method of rounding first seems mathematically, however after a 
few years of encountering this I really think he should bite the bullet 
on this issue and round last, unfortunately this is one of those cases 
where 'everybody else is doing it'.

Kind Regards
Robert Stanford

Re: [SL] wrong rebatte calculation in invoice/SO

[Michael H. <mh...@it...>]

Any accountant will say that rounding should ALWAYS be the last step.


Thanks,

Michael


On Sep 5, 2007, at 3:17 PM, Robert Stanford wrote:

> Jeff Roberts wrote:
>> Hi Ashley
>>
>> I think I understand now, You're manually putting in 7.5% discount on
>> the line as you create the invoice right?  If that's the case it  
>> could
>> be argued that it would be better to multiply the quantity by the  
>> price
>> then apply the discount ( but the difference is in your favour :-) )
>> Maybe a future revision could move the brackets around in the  
>> equation
>> to do that.
>>
>> If you're assigning a discount to a vendor on an item then it's a
>> different story because the item is 'pulled' from the tables with a
>> price that is dependant on the date, pricegroup of the customer,  
>> and any
>> exceptions or overides in the pricing matrix and there is no way to
>> correct a rounding error once the query has run other than to start
>> carrying four or five digits on every price just in case there is  
>> a high
>> enough quantity sold to make a more than marginal difference.
>>
>> Jeff
>>
> This has been a bit of a problem from a usability point of view.
>
> SQL-Ledger rounds discounts before multiplying by qty. This causes no
> real problem when selling.
>
> However the same thing happens when purchasing and here is where it
> causes problems.
>
> Here's  where the problem arises, every supplier we have encountered
> rounds after multiplying by quantity, so when we it comes time for
> accounts to pay bills there if often a discrepancy between our  
> purchase
> order and the suppliers invoice.
>
> Dieters method of rounding first seems mathematically, however after a
> few years of encountering this I really think he should bite the  
> bullet
> on this issue and round last, unfortunately this is one of those cases
> where 'everybody else is doing it'.
>
> Kind Regards
> Robert Stanford
>
> ---------------------------------------------------------------------- 
> ---
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems?  Stop.
> Now Search log events and configuration files using AJAX and a  
> browser.
> Download your FREE copy of Splunk now >>  http://get.splunk.com/
> _______________________________________________
> sql-ledger-users mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sql-ledger-users