• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Total Discount value based on multiple criteria

P

paradise

Member
Dear All forum contributors,

My current problem is that my boss has given me the ledger in which there is the column of Date,Sales Bill,Sales Amount and Payment Received respectively in column B,C,D,E.He has also given me the condition/ criteria saying that you have to calculate the discount amount to be given in each bill if the condition fulfills which is in next sheet.Let me explain the terms & condition-

If the payment received within 15 days from Bill date then give discount of 15% of sales amount
If the payment received within 30 days from Bill date then give discount of 10% of sales amount
If the payment received more than 30 days from Bill date then give discount of 0% of sales amount

How to solve this problem in order to arrive total discount amount in solution sheet.

If you require any further info.pls do let me know.

I will be eagerly waiting for all top contributors and others for help.Below is attached excel workbook.

With Best Rgds,
paradise
 

Attachments

Hi, paradise!
You're not trying at all to make this topic looking like your nick, are you? :)
The date in column B, is the sale date or the payment date? Because one of the 2 dates is missing, hence your problem is unsolvable.
Regards!
 
Dear Sir SirJB7,
Column B date represents date for both Column D(Sales Amount) & Column E (Payment Received).If the value is in both Column D & Column E it means that in the same date both sales has been made as well as payment received.
There is no date missing and is the Universal standard format of any ledger except closing balance I have not shown here.

It is possible to solve and I am sure that someone might solve.

With Best Rgds,
paradise
 
Hi, paradise!
I had many close encounters with ledger formats and whenever either a sell operation was made or a payment was received the minimum information required was: date, amount, reference. Where reference usually is the involved document no. (bill, invoice, ...).
So for a client current account (or however it may be called elsewhere, i.e., the summary of debits and credits for that client) in your posted workbook at worksheet Question columns B: D, I understand this and please correct me if I'm wrong:
Column B: Date of sale
C: Bill no.
D: Sales amount
E: Payment amount
Now, how do you match column E against column D, i.e, how do you assign payments to sell operations?
I glimpse 2 scenarios:
1) Payment reference is missing: you don't know to what bill/s corresponds a payment
2) Sales and payment are entered in date order and should be applied regarding pending amounts
I opted for the 1st and so my comment of previous post since for the 2nd one the sell amount is 230630 and the payment amount is 235000.
If there's a 3rd or more, please elaborate and post/upload the manual output desired corresponding to the input data posted.
Regards!
 
Dear Sir SirJB7,
I have revised the ledger with assigning proper reference no.Pls find enclosed in attachment.In the same sheet but in right hand side,I have shown a manual calculation with target result.Pls note that the amount received might be bill to bill, on account, or advance.Hope from this revised ledger,you can solve my problem.This is very tough as I have tried earlier also but not succeeded.You have to assume that there is no opening balance i.e opening balance is Zero.Also as stated by you,it should not be on pending amount rather you should have to look on last payment date when the bill value with corresponding amount is being done nil.

If you require further info.then do reply to me.

With Best Rgds,
paradise
 

Attachments

Hi, paradise!
Thanks for the detailed explanation in the updated file. Will be giving a deeper look tomorrow and let you know if any success.
Regards!
 
Ok,I will be eagerly waiting for your prompt reply in this regard as this sort of case is mostly faced by many organisation and manual working is very tedious and time consuming,so I opt if any forum expert could be able to solve this case.This is actually related to finance in order to motivate the buyer to pay in time by giving them a cash discount,this sort of policy is generally adopted by the seller.

Anyhow, best of luck for your try.
With Best Rgds,
 
Hi, paradise!

Give a look at this link:
https://dl.dropboxusercontent.com/u/60558749/Total Discount value based on multiple criteria - Discount Calculation Revised (for paradise at chandoo.org).xlsx

Added ranges are ocher shaded. It builds a table with this fields (columns I: P):
- Bill No.
- Sum of sales
- Sum of payments
- Balance
- Last payment index
- Last payment date
- No. of days
- % Discount

Formulas:
I4: =SI(IZQUIERDA(D4;8)="Bill No.";VALOR(DERECHA(D4;LARGO(D4)-8));"") -----> in english: =IF(LEFT(D4,8)="Bill No.",VALUE(RIGHT(D4,LEN(D4)-8)),"")
J4: =SUMA(E$4:E4) -----> in english: =SUM(E$4:E4)
K4: =SUMA(F$4:F4) -----> in english: =SUM(F$4:F4)
L4: =J4-K4
M4: =SI(I4<>"";COINCIDIR(J4;K$4:K$13;1);"") -----> in english: =IF(I4<>"",MATCH(J4,K$4:K$13,1),"")
N4: =SI(I4<>"";INDICE(A$4:A$13;M4);"") -----> in english: =IF(I4<>"",INDEX(A$4:A$13,M4),"")
O4: =SI(I4<>"";N4-A4;"") -----> in english: =IF(I4<>"",N4-A4,"")
P4: =SI(I4<>"";BUSCARV(O4;Condition!D$4:E$6;2;VERDADERO);"") -----> in english: =IF(I4<>"",VLOOKUP(O4,Condition!D$4:E$6,2,TRUE),"")

Discount percentages in Condition!D4:E6 range.

Just advise if any issue.

Regards!

PS: If your clients have customers who give them amounts in advance for future operations, why don't you pass me their contact references and email addresses? Maybe I could imagine what to do with those cash advances... surely I'll do! :p
 
Dear Sir SirJB7

Refer to the excel workbook that you have sent.In that workbook in 'Question'sheet in 'O7' you have calculated no.of days to be 60 and corresponding last payment date to be 16/04/2013 which is wrong becoz last payment date of Bill No.42 would be 11/05/2013 & from this no.of days arrived is 85 days hence 0% is applicable.Secondly,last payment of Bill no.65 would be 16/05/2013 with no.of days to be 30days hence 10% discount is applicable instead of your calculation 11/05/2013 with no.of days to be 25days with 10% discount.

This flaws is found in your formula,if this is resolved then you are nearer to it.After this then you have to calculate discount value.

I have again attached an excel workbook and extra sheet where I have done again full workings from A16:H41 i.e below ledger.

Hoping that you may solve.

With Best Rgds,

PS:This is for my organisation where I work in preparation of various kinds of report for the mgmt.
 

Attachments

Hi, paradise!
Download again the updated file from same previous link.
Formulas changed:
K4: =SUMA(F$4:F4)+0,001 -----> in english: =SUM(F$4:F4)+0.001
M4: =SI(I4<>"";COINCIDIR(J4;K$4:K$13;1)+1;"") -----> in english: =IF(I4<>"",MATCH(J4,K$4:K$13,1)+1,"")
Please check it now.
Regards!
 
Dear Sir SirJB7,
I think now you got it what I mean to say.Now proceed towards calculation of discount value.Couple of things I would like to know in your formula what does that mean :
1.=SUM(F$4:F4)+0.001 >>>>> why you added 0.001 what does this mean ?
2.-999 in condition sheet >>>>> why you did so

Kindly resolve this issue and in next phase I will add Opening Balance value either Debit or Credit in ledger to ensure the complete workings of ledger.

With Best Rgds,
 
Hi, paradise!
VLOOKUP and INDEX functions work searching for exact values or for approximated values, if searched argument isn't found then #N/A is returned. Exact match is trivial to understand, so then go with approximated matching.
VLOOKUP only has non exact matching for ascending ordered lists (last argument TRUE instead of FALSE for exact) and INDEX has both ascending and descending ordered lists (last argument 1 or -1 instead of 0 for exact).
When using descending order the lowest higher value is returned and when using ascending order the highest lower value is used, e.g., if you have the numbers from 1 to 10 and search for 4.5 in ascending order you'll get a 4, and if you have from 10 to 1 searching for 4.5 will retrieve a 5.
With this criteria, the 1st entry in the range should be as low (in ascending, or as high in descending) so as that any argument searched enter in the range, that's why my -999 which could have been 0 too.
And regarding the +0.01 is to control border conditions and making search arguments fit in the proper bucket.
Regards!
 
You must log in or register to reply here.
Back
Top