Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Overall rating in financial risk matrix

Latest post Sun, Mar 8 2015 11:56 PM by bobbell. 3 replies.
  • Fri, Aug 12 2011 7:45 AM

    • Pragna
    • Top 200 Contributor
    • Joined on Thu, Jun 16 2011
    • Posts 6
    • Points 110

    Overall rating in financial risk matrix

    Hello all,  question - I am calculating an overall risk rating for accounts.  I am averaging values but if the value is zero in one cell, I would like to not include it in the averaging.  Can someone please help me how to set this formula up?  here is what I have for now -

    =AVERAGE(SUM((Q28*Q$22)+(R28*R$22)+(S28*S$22)+(T28*T$22)),P28)

    cols q,r,s,t have values of 1-2-3 which compute with percents in row 22 (percents represent the risk weighting of each factor.  However I am not applying a 1-2-3 to materiality.  Therefore when computing the overall rating of the financial account  I would like to take the risk factors of each category and include materiality.  Materiality for now has been defined as follows:

    • anything less than $500 = NA
    • $500 = $10,000 = small given a rate of .5
    • $10,000 to $100,000 = medium given a rate of .75
    • Greater than $100M = Large given a rate of 1.0

    the rates to materiality implies that if the account has a high balance, it should be accounted for in the overall rating at 100%, medium and small accounts to be discounted as noted.

    If there is another way to present the formula please let me know.

    Appreciate your help.  Thank you and Happy Friday!

    • Post Points: 21
  • Fri, Aug 12 2011 12:10 PM In reply to

    • Jim Cone
    • Top 10 Contributor
    • Joined on Tue, Jan 15 2008
    • Portland, Oregon - USA
    • Posts 243
    • Points 3,314

    Re: Overall rating in financial risk matrix

    Comments...

    You shoudn't use plus or minus signs and the Sum function together.
    Either...
    =Sum(6, 7, 8)
     -or-
    = 6 + 7 + 8

    The Average function ignores blank cells and cells with text but inclues zero values.

    Should you not apply "materiality" to each individual account before averaging them?

    What is P28?

    What exactly is your question?
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (Extras for Excel add-in:  convenience built-in)

     

     

    • Post Points: 37
  • Fri, Aug 12 2011 1:54 PM In reply to

    • Pragna
    • Top 200 Contributor
    • Joined on Thu, Jun 16 2011
    • Posts 6
    • Points 110

    Re: Overall rating in financial risk matrix

    I was trying to arrive at an overall rating of account balance significance and the various risk factors.  Instead of averaging I multiplied P28 which has the factor for account balance significance (large account =1, med=.75 and small=.5).

    I am good for now.  Thank you for your time.

    • Post Points: 5
  • Sun, Mar 8 2015 11:56 PM In reply to

    • bobbell
    • Not Ranked
    • Joined on Mon, Mar 9 2015
    • Posts 1
    • Points 5

    Re: Overall rating in financial risk matrix

    I am a master degree student on applied economics at Brazil and my thesis will be about smart beta strategies. I pretend to apply Equal Weigth, Mininum Variance, Most Diversified Portfolio and Equal Risk Contribution strategies on IBOV, the cap weighted index of Brazilian Financial Market.

    I've tried to found some codes on matlab or R of these strategies but I didn't have success. So I would like to awnser where can I find something that would help me to estimate the weights of the stocks ? It's hard for me doing all the code, because of that I'm asking for help.

    Another thing is about the covariance matrix. I want to test the results with the sample covariance matrix, risk metrics, shrinkage and maybe DCC GARCH. How about that? It's really necessary to use more than two methods?

    Hope you help me! I appreciate!

    • Post Points: 5
Page 1 of 1 (4 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.