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

BG Commission

The attached file is having live data of BG Commission. BG Commission rate, BG amount is given.

If the status of BG is "Issue" then BG Commission to be calculated for those number of days i.e. from BG date to till date of Extension (AD column in attached file).

If the status of BG is"Extension" then BG Commission to be calculated for the number days between Original Validity Date and Extended Validity Date (AD - AC columns)

Now the requirement is if the status of BG is "Extension" then BG commission to be found financial year wise.

For example, against BG No:2657BG1432009 of attached file, Original BG date is 10-Jun-09 (FY 2009-10) and Original Validity date is 30-Apr-13 (FY 2013-14). This BG is extended till 31-Jan-17 (FY 2016-17).

Output (preferably in Pivot) shall be year wise. BG commission from May-14 to 31-Jan-17 shall be calculated FY year wise.

2013-14 2014-15 2015-16 2016-17
335days 365days 365days 306 days (till 31-Jan-17)
 

Attachments

  • BG Comm.xlsx
    28.1 KB · Views: 4
Could You use same terms with You case as used in Your sample file's headers?
Like maybe 'Original Issue/Extension' is same as the status of BG is"Extension".
 
Could You use same terms with You case as used in Your sample file's headers?
Like maybe 'Original Issue/Extension' is same as the status of BG is"Extension".
Yes. When a fresh BG is taken from Bank it's status would be "Issue". If it is extended after it's original Validity then against that BG number status will be changed to "Extension"
 
... hmm ...
Could You, suryasabniveesu, use same terms with Your case as used in Your sample file's headers?
... I tried to write that if file has header 'Original Issue/Extension' You, suryasabniveesu, would use same words (terms) here too.
It would be much quicker
that no need to try to find same meaning terms from both; here and file.
Words "Issue" & "Extension" are easy to find ...
but are the rest Your used 'terms'?
 
... hmm ...
Could You, suryasabniveesu, use same terms with Your case as used in Your sample file's headers?
... I tried to write that if file has header 'Original Issue/Extension' You, suryasabniveesu, would use same words (terms) here too.
It would be much quicker
that no need to try to find same meaning terms from both; here and file.
Words "Issue" & "Extension" are easy to find ...
but are the rest Your used 'terms'?

Header of Status column can be named as "Status". Depending on the string in this column, BG commission shall be calculated and year wise BG commission shall be published.

Hope I am clear.
 
I took snapshot ... of Your sample ... I hide no-use columns
and I tried to FIND some of Your terms

Your For example, against BG No:2657BG1432009 of attached file,
Screen Shot 2017-02-25 at 09.51.44.png
Original BG date is 10-Jun-09 (FY 2009-10) [OKAY]
Original Validity date is 30-Apr-13 (FY 2013-14). [Why not 01-May-13?]
This BG is extended till 31-Jan-17 (FY 2016-17). [Where/how comes?]
As You wrote: "Hope I am clear."
 
I took snapshot ... of Your sample ... I hide no-use columns
and I tried to FIND some of Your terms

Your For example, against BG No:2657BG1432009 of attached file,
View attachment 38972
Original BG date is 10-Jun-09 (FY 2009-10) [OKAY]
Original Validity date is 30-Apr-13 (FY 2013-14). [Why not 01-May-13?]
This BG is extended till 31-Jan-17 (FY 2016-17). [Where/how comes?]
As You wrote: "Hope I am clear."

When a new BG is taken, we take it for limited period. In this case, the BG was taken till its validity expires on 30-Apr-13. After it's expiry on 30-Apr-13 it is again extended till 31-Jan-2017.

In the sample file, we had to insert rows to calculate financial year wise BG commission.
 
... so that 'orage row' no matters, just make troubles!
If You could give 'crystal clear' information where & how
then this would be much quicker to do.
But if all even the smallest information have to dig out - then this will take time!
Do I have time to dig?
How about You?
 
No way ...
but You will get this ...
there are still something ... ex rates: 0,5 is high rate ... for me!
 

Attachments

  • BG Comm.xlsx
    42.2 KB · Views: 0
First of all thanks for your time. But I want distribution of BG commission financial year wise.

BG commission rate is 0.5% is correct.
 
1) 0.5 means 50% and 0.5% means 0.005 - any questions?
2) Explain Your term 'distribution of BG commission financial year wise'
... if You would like to get something else.
 
1) 0.5% means 0.005
2) In India financial year means 01st April to 31st March. So if a BG taken in 2009 and it's validity is till 2014 then BG commission to be found for every financial year starting from 2009-10.

I am able to calculate number of days since original BG taken and till date BG commission. Getting problem to calculate and get financial year wise break up
 
1) There were many times 0.5 ... that's 50% ... okay!
2) Now, 'FY' starts from 1/4/2018 with 1yr steps - okay?
3) As You have wanted to use 'only functions & Pivot' '=An Excel Question'
then You should split those rows which have to calculate to different 'FY's MANUALLY! ... It's not a problem!
 

Attachments

  • BG Comm.xlsx
    42.2 KB · Views: 4
Is this final output?

In pivot, total BG amount is considered instead of BG commission.

We did manual exercise to identify financial years. How BG commission automatically be calculated financial year wise.
 
1) Did You tried to notice this?
Screen Shot 2017-02-25 at 18.06.31.png
If not then those values are too GREAT!
( as I have tried to tell 0.5 = 50% and 0.5% is much smaller value! )
There are also other values as 0.5 ...
What is challenge? If You'll add more rows then do I have to do something? NO!

2) 'formula' "Extension" for row 2 is =G2*V2*DAYS360(AB2;AC2)/360
I didn't find 'better' value for G2!
 
Back
Top