• 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.

Cash Discount Calculation using date,amount and logical arguments

Status
Not open for further replies.
nitesh

nitesh

New Member
In my Industry we give 100 rupee cash discount per 1 ton cement sale within 10 days and 60 rupee cash discount per ton if the customer pays within 17 days. So every month for all customers we need to check opening balance and the payment received against billed invoice and then validate as per days. It is a hectic and manual process and often errors creep in and we suffer heavy losses due to the same.Can i have an excel format with formula that can automate my cash discount as per the input data.
 
Hi, nitesh!

Without your workbook and worksheets structure it's risky to suggest you a solution, so consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.

Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!
 
Please find the sample file uploaded on the following link


https://www.dropbox.com/s/jrdkkuxt1nc4uyj/CD%20CALCULATION.xlsx
 
Hi Nitesh,

Please describe the data in the file you uploaded.

Specifically, describe how you calculated the cash discount amounts, listing your business / calculation rules. Knowing how you would manually calculate the discounts will allow us to determine the correct formula(s).


Some questions I have are:

If a customer buys 10 tons for $10,000 on Feb 1, and pays $2,000 on Feb 5 (which is within 10 days), how do you determine the cash discount? Do you calculate how many tons equates to the $2,000?

I am assuming that the cost of cement varies over time. How do you factor that in your cash discount determinations?


Once you describe all your business rules, we should be able to help you.


Cheers,

Sajan.
 
Hi Nitesh,


Thanks for the sample file..

including manual examples of desired output
Click to expand...

Your complete data is fully MANUAL and none of the cell contains any formula.

So its really hard to determine.. what exactly need to calculate..


Please re-upload with little bit detail explanation.. :)


Regards,

Deb
 
Hi, Nitesh!


Looking at your uploaded file I do agree with Sajan & Debraj Roy's comments.


But despite of this, I think that with that data it'd be very difficult -if not impossible- to do something accurate since:

- there are previous month balances that might be within the 10 or 17 days period

- the data seems to be taken from the financial customer account but there's no indication of the relation of column I (Collected) against column G (Sold)

- I tried to manually check the CD Amounts of column K at rows 15 and 16, and I couldn't find out to which amounts of column G were related too

- specifically with the amount of 87000 at row 16 that matched with the 87000 at row 14 of column G, the 1980 value doesn't correspond neither to the 100/ton (1500 in this case) nor to the 60/ton (90 in this case)


So I recommend you to:

a) follow the suggestions of previous comments

b) check your data to elaborate and describe how were calculated a couple of values

c) ask you IT department to provide the relation sell/collect tied to an invoice number or something alike


Regards!
 
Yes very true the complete process as of now is a manual one....first we subtact the total opening balance from the first collection and compare the value with the invoice.If its equal or more than the invoice we go for the date check up viz(if the collection is between 10 days he is liable for cd @ Rs.5 per ton and if within 17 days @ Rs.3 per ton) and beyond 17 days no cd will be given.The same process need to be repeated against all invoices and collection for particular customer in that month.
 
Hi, nitesh!

Would you please write an explanation of how do you obtain the 2 CD amount calculations of rows 15 and 16?

Regards!
 
Hi SirJB7,

It is interesting that you have to coax the OP to provide details about his problem so that you can help him...! Something is out of balance there!


-Sajan.
 
@Sajan

Hi!

You make me read again the whole topic, and I tried that not 1 but 3 times! Actually something's very out of balance.

Regards!
 
Hi SirJB7,

One conclusion could be that the OP does not understand / know the rules to apply... after all, he did say that the current process results in "heavy losses".


Perhaps to avoid further losses to his company, we should suggest how his company should operate!!


-Sajan.
 
Guys Thanks for your patience in understanding the problem.....Actually the whole process is done by my accounts team.....I will get back to you with the complete operating formula and link up file....about the CD calculation....Currently I am running SAP software for the complete process but still some modules are manually accounted...Due to my personal interest in Excel I wanted to know if it could be done in Excel accurately.......
 

hofiy​

You should open a new thread for Your challenge.
This thread is over 10 years ... young.
This thread is closed now.
 
Status
Not open for further replies.
Back
Top