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

Report format

Prisoner

Prisoner

New Member
Hi Everyone,

I a currently working on a report and need to finalize its format before presentation to my senior.

The report basically shows top borrowers of a financial services firm. I need to show the group with highest borrowings and the clients within that group. This is shown in the rank sheet of the working table. I need to adjust all the "non funded" balances by multiplying with the respective product factor, and then further adjust it, with the unused non-funded limits of those clients (given in clients limits sheet). The unused non-funded limits are derived by a formula (non-funded limits - non-funded outstanding) After this i need to bring the new adjusted non funded amount, in the rank table, with ranking based on the group's grand total. The product factor to be used is to be determined by the product type. I also need to show the ranking of the group as : 1, 2, 3 ...so on. within the pivot table.

Any advice is greatly appreciated. Thank you.
 

Attachments

  • Working File.xlsx
    18.4 KB · Views: 7
Hi Prisoner,
As I can see in your file, I found everything in order and fine (Assuming data is derived after all the arithmetical calculations as mentioned by you) . I am not able to understand what further you want in this data.
 
Hi Balli,

The non-funded balances in the data sheet are not adjusted for the limits and product factor. These need to be adjusted and then the pivot table refreshed to include the changes. Also how do I show the group ranking within the pivot table?
 
Hi Prisoner,

Please suggest I am correct or not? and please explain some point.

Suppose I am performing the calculation on Client ID 105:

1) adjust all the "non funded" balances by multiplying with the respective product factor:
  • 14000*1e2001 (i.e. 20%)
  • Result will be 2800
2) further adjust it, with the unused non-funded limits of those clients
  • This means what?
3) The unused non-funded limits are derived by a formula (non-funded limits - non-funded outstanding)
  • 30000-14000
4) i need to bring the new adjusted non funded amount, in the rank table
  • What will be this amount?
 
Hi Balli,

You are correct on point 1. This figure of 2800 then needs to be adjusted for unfunded unused limits as follows:

Unused limits of Client 105: 30,000 (unfunded limit) -14000 = 6000K

This unused limit needs to be added to the 2800: 6000 + 2800 = 8800K

This amount should go into the pivot table as opposed to 14000K earlier in the rank sheet.
 
Hi Prisoner,

As I understood, I have done the calculation in a new column ("New o/s Amount") in "DATA" Sheet and used it in Pivot Table. Please let me know, if this solve your purpose or not.

As you mentioned "Unused limits of Client 105: 30,000 (unfunded limit) -14000 = 6000K". I think it should be "16000", not 6000.

In your sheet ("Client Limits") the unfunded limit for all client is 30,000 so I used constant in formula (i.e. 30,000). If its different for different clients then it could be done by some formula.
 

Attachments

  • Working File.xlsx
    20.7 KB · Views: 7
Hi Balli,

Sorry, that was typo error. Thanks a ton for solving this!! However I am curious to know what the formula would be if each of the clients had a different credit limit?
 
Thanks, I have figured this out. The formula will change as follows, if the unused credit limits are different for each client:

=IF(D2="Non funded",(('Client Limits'!$D2-$E2)+(IFERROR(VLOOKUP(G2,'Non funded prd table'!$B$2:$C$7,2,0),0)*E2)),E2)

Cheers.
 

Attachments

  • Working File (2).xlsx
    21.3 KB · Views: 2
Hi Prisoner,
Yes, your formula is perfectly right. But your client ID should be in same order as in "DATA" sheet. If it is not in same order then you should use again vlookup to find that like:
=IF(D2="Non funded",((VLOOKUP(A2,'Client Limits'!$A$2:$D$21,4,0)-$E2)+(IFERROR(VLOOKUP(G2,'Non funded prd table'!$B$2:$C$7,2,0),0)*E2)),E2)

Thanks for reply.
 

Attachments

  • Working File - New.xlsx
    21.6 KB · Views: 8
Hi balli,

What if client ids in the main data sheet are repeated? A client can have both funded and unfunded exposures. How can the unfunded exposure be adjusted then ?
 

Attachments

  • Working File - duplicate ids.xlsx
    22.2 KB · Views: 3
Hi Prisoner,

As you can see, the formula will do calculation if the acct nature is "Non Funded". So if the acct nature for same client is "Funded" the formula will no nothing with current formula but what calculation or work you want to perform in this condition. Pleas clarify.
 
Hi Bali,

Please see my problem in Forum "Daily Transaction History".
Please solve my problem...
 

Attachments

  • Summery.xlsx
    13.1 KB · Views: 1
@JAMIR
Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!

PS: Please don't answer here at this thread.

PS2: And don't try to beat the crosspost Guinness record... you'd get banned much earlier than that.
 
You must log in or register to reply here.
Back
Top