{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Disclaimer","Materiality Calculator","G-D Calculator","Categorisation - Unlisted Prop","Categorisation - Unlisted Infra","Risk Scaling - Hedge Funds"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":9,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Disclaimer","index":0,"n_rows":12,"n_cols":1,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Introduction and Disclaimer"]

{"sheet":"Materiality Calculator","index":1,"n_rows":14,"n_cols":16,"rows_kept":14,"cut":false,"hidden":true,"merged":[]}
["Materiality Calculator"]
[]
["Use this ready reckoner to plan your compliant G/D calculation."]
["Change the green cells."]
[]
[]
[null,null,null,"Portfolio Allocation","Adopted Scoring Approach"]
[null,null,"Unlisted Property",0.1,"Detailed",null,null,null,null,null,null,null,null,null,null,"Detailed"]
[null,null,"Unlisted Infrastructure",0.1,"Detailed",null,null,null,null,null,null,null,null,null,null,"Simple"]
[null,null,"Alternatives",0,"Simple"]
[null,null,"High Duration Bonds",0.1,"Simple"]
[null,null,"Total Exposure",{"$f":"SUM(D8:D11)","v":0.30000000000000004},{"$f":"SUMIF(E8:E11,\"=Simple\",D8:D11)","v":0.1}]
[]
[null,null,null,{"$f":"IF(D12<=10%,\"NO ISSUES APPLYING SIMPLE SCORING\",\"CANNOT APPLY SIMPLE SCORING APPROACH TO ENTIRE PORTFOLIO WITHOUT BEING FLAGGED\")","v":"CANNOT APPLY SIMPLE SCORING APPROACH TO ENTIRE PORTFOLIO WITHOUT BEING FLAGGED"},{"$f":"IF(E12<=10%,\"SCORING PLAN ACCEPTABLE\", \"SCORING PLAN REQUIRES FURTHER CHANGE TO COMPLY AND NOT BE FLAGGED\")","v":"SCORING PLAN ACCEPTABLE"}]

{"sheet":"G-D Calculator","index":2,"n_rows":154,"n_cols":8,"rows_kept":154,"cut":false,"hidden":true,"merged":[[148,3,148,4]]}
["Growth / Defensive Portfolio Scoring"]
["Key","Do not touch orange cells"]
[null,"Fill in the green cells"]
[null,"Select either Simple or detailed scoring approach"]
[]
[]
[null,"Australian Equities",null,"Allocation","Growth Score","Defensive Exposure"]
[null,null,"Australian Shares",0.25,1,{"$f":"100%-E8","v":0}]
[null,null,"Australian Small Cap Shares",0,1,{"$f":"100%-E9","v":0}]
[null,null,"Australian Micro Cap Shares",0,1,0]
[null,null,"Australian Low Vol Shares",0,1,0]
[null,null,"Australian Shares - any sector exposure",0,1,0]
[null,null,"Leveraged Australian Shares",0,1.3,-0.30000000000000004]
[null,null,"Other - Insert name",0,1,0]
[null,null,null,{"$f":"SUM($D$8:$D$14)","v":0.25},{"$f":"IF($D$15=0,0,SUMPRODUCT($D$8:$D$14,$E$8:$E$14)/$D$15)","v":1},{"$f":"IF($D$15=0,0,SUMPRODUCT($D$8:$D$14,$F$8:$F$14)/$D$15)","v":0}]
[]
[]
[null,"Global Equities",null,"Allocation","Growth Score","Defensive Exposure"]
[null,null,"Global Shares (all hedging levels)",0.25,1,{"$f":"100%-E19","v":0}]
[null,null,"Global Small Caps (all hedging levels)",0,1,{"$f":"100%-E20","v":0}]
[null,null,"Global Micro Cap Shares (all hedging levels)",0,1,0]
[null,null,"Global Low Vol Shares (all hedging levels)",0,1,0]
[null,null,"Emerging Market Shares (all hedging levels)",0,1,0]
[null,null,"Global Shares - any sector exposure",0,1,0]
[null,null,"Global Shares - any individual country",0,1,0]
[null,null,"Emerging Market Shares - any individual country",0,1,0]
[null,null,"Leveraged Global Shares",0,1.3,-0.30000000000000004]
[null,null,"Other - Insert name",0,1,0]
[null,null,null,{"$f":"SUM($D$19:$D$28)","v":0.25},{"$f":"IF($D$29=0,0,SUMPRODUCT($D$19:$D$28,$E$19:$E$28)/$D$29)","v":1},{"$f":"IF($D$29=0,0,SUMPRODUCT($D$19:$D$28,$F$19:$F$28)/$D$29)","v":0}]
[]
[]
[null,"Australian Private Equity",null,"Allocation","Growth Score","Defensive Exposure"]
[null,null,"Australian private equity - leveraged buyout",0,1,{"$f":"100%-E33","v":0}]
[null,null,"Australian private equity - growth equity",0,1,{"$f":"100%-E34","v":0}]
[null,null,"Australian private equity - venture capital",0,1,0]
[null,null,"Other - Insert name",0,1,0]
[null,null,null,{"$f":"SUM(D33:D36)","v":0},{"$f":"IF($D$37=0,0,SUMPRODUCT($D$33:$D$36,$E$33:$E$36)/$D$37)","v":0},{"$f":"IF($D$37=0,0,SUMPRODUCT($D$33:$D$36,$F$33:$F$36)/$D$37)","v":0}]
[]
[]
[null,"Global Private Equity",null,"Allocation","Growth Score","Defensive Exposure"]
[null,null,"Global private equity - leveraged buyout",0,1,{"$f":"100%-E41","v":0}]
[null,null,"Global private equity - growth equity",0,1,{"$f":"100%-E42","v":0}]
[null,null,"Global private equity - venture capital",0,1,0]
[null,null,"Other - Insert name",0,1,0]
[null,null,null,{"$f":"SUM(D41:D44)","v":0},{"$f":"IF($D$45=0,0,SUMPRODUCT($D$41:$D$44,$E$41:$E$44)/$D$45)","v":0},{"$f":"IF($D$45=0,0,SUMPRODUCT($D$41:$D$44,$F$41:$F$44)/$D$45)","v":0}]
[]
[]
[null,"Listed Property",null,"Allocation","Growth Score","Defensive Exposure"]
[null,null,"Australian LPT's",0,1,{"$f":"100%-E49","v":0}]
[null,null,"Global REITs",0,1,{"$f":"100%-E50","v":0}]
[null,null,"Regional REITs",0,1,0]
[null,null,"Other - Insert name",0,1,0]
[null,null,null,{"$f":"SUM(D49:D52)","v":0},{"$f":"IF($D$53=0,0,SUMPRODUCT($D$49:$D$52,$E$49:$E$52)/$D$53)","v":0},{"$f":"IF($D$53=0,0,SUMPRODUCT($D$49:$D$52,$F$49:$F$52)/$D$53)","v":0}]
[]
[]
[null,null,"Select scoring approach for unlisted property"]
[null,"Unlisted Property","Detailed","Allocation","Growth Score","Defensive Exposure","Approach Check"]
[null,null,"Australian unlisted property - simple / prescriptive",0,0.8,{"$f":"100%-E58","v":0.19999999999999996},{"$f":"IF(AND($C$57=\"Detailed\",D58<>0),\"Error: If you adopt the detailed approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Australian unlisted property - tier 1 risk",0.1,0.6,{"$f":"100%-E59","v":0.4},{"$f":"IF(AND($C$57=\"Simple\",D59<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Australian unlisted property - tier 2 risk",0,1,{"$f":"100%-E60","v":0},{"$f":"IF(AND($C$57=\"Simple\",D60<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Other - Australian - insert name",0,null,1,{"$f":"IF(AND($C$57=\"Simple\",D61<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Global unlisted property - simple / prescriptive",0,0.8,{"$f":"100%-E62","v":0.19999999999999996},{"$f":"IF(AND($C$57=\"Detailed\",D62<>0),\"Error: If you adopt the detailed approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Global unlisted property - tier 1 risk",0,0.6,0.4,{"$f":"IF(AND($C$57=\"Simple\",D63<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Global unlisted property - tier 2 risk",0,1,0,{"$f":"IF(AND($C$57=\"Simple\",D64<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Other - Global - insert name",0,null,1,{"$f":"IF(AND($C$57=\"Simple\",D65<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,null,{"$f":"SUM(D58:D65)","v":0.1},{"$f":"IF($D$66=0,0,SUMPRODUCT($D$58:$D$65,$E$58:$E$65)/$D$66)","v":0.6},{"$f":"IF($D$66=0,0,SUMPRODUCT($D$58:$D$65,$F$58:$F$65)/$D$66)","v":0.40000000000000008},{"$f":"COUNTIF(G58:G65,\"*Error*\")","v":0},"errors"]
[]
[]
[null,"Listed Infrastructure",null,"Allocation","Growth Score","Defensive Exposure"]
[null,null,"Australian listed infrastructure",0,1,{"$f":"100%-E70","v":0}]
[null,null,"Global listed infrastructure",0,1,{"$f":"100%-E71","v":0}]
[null,null,"Other - Insert name",0,1,0]
[null,null,"Other - Insert name",0,1,0]
[null,null,null,{"$f":"SUM(D70:D73)","v":0},{"$f":"IF($D$74=0,0,SUMPRODUCT($D$70:$D$73,$E$70:$E$73)/$D$74)","v":0},{"$f":"IF($D$74=0,0,SUMPRODUCT($D$70:$D$73,$F$70:$F$73)/$D$74)","v":0}]
[]
[]
[null,null,"Select scoring approach for unlisted infrastructure"]
[null,"Unlisted Infrastructure","Detailed","Allocation","Growth Score","Defensive Exposure","Approach Check"]
[null,null,"Australian unlisted infrastructure - simple / prescriptive",0,0.8,{"$f":"100%-E79","v":0.19999999999999996},{"$f":"IF(AND($C$78=\"Detailed\",D79<>0),\"Error: If you adopt the detailed approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Australian unlisted infrastructure - tier 1 risk",7.0000000000000007E-2,0.6,{"$f":"100%-E80","v":0.4},{"$f":"IF(AND($C$78=\"Simple\",D80<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Australian unlisted infrastructure - tier 2 risk",0.03,1,0,{"$f":"IF(AND($C$78=\"Simple\",D81<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Other - Australian - insert name",0,null,1,{"$f":"IF(AND($C$78=\"Simple\",D82<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Global unlisted infrastructure - simple / prescriptive",0,0.8,0.19999999999999996,{"$f":"IF(AND($C$78=\"Detailed\",D83<>0),\"Error: If you adopt the detailed approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Global unlisted infrastructure - tier 1 risk",0,0.6,0.4,{"$f":"IF(AND($C$78=\"Simple\",D84<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Global unlisted infrastructure - tier 2 risk",0,1,0,{"$f":"IF(AND($C$78=\"Simple\",D85<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Other - Global - insert name",0,null,1,{"$f":"IF(AND($C$78=\"Simple\",D86<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,null,{"$f":"SUM(D79:D86)","v":0.1},{"$f":"IF($D$87=0,0,SUMPRODUCT($D$79:$D$86,$E$79:$E$86)/$D$87)","v":0.72000000000000008},{"$f":"IF($D$87=0,0,SUMPRODUCT($D$79:$D$86,$F$79:$F$86)/$D$87)","v":0.28000000000000003},{"$f":"COUNTIF(G79:G86,\"*Error*\")","v":0},"errors"]
[]
[]
[null,null,"Select scoring approach for alternatives"]
[null,"Alternatives","Simple","Allocation","Growth Score","Defensive Exposure","Approach Check"]
[null,null,"Alternatives - simple / prescriptive",0.02,0.75,{"$f":"100%-E92","v":0.25},{"$f":"IF(AND($C$91=\"Detailed\",D92<>0),\"Error: If you adopt the detailed approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Emerging Markets Debt",0,0.75,{"$f":"100%-E93","v":0.25},{"$f":"IF(AND($C$91=\"Simple\",D93<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Hedge Funds",0,0.5,0.5,"OK"]
[null,null,"Alternative Risk Premia",0,0.5,0.5,"OK"]
[null,null,"Multi-Asset Funds",0,0.75,0.25,"OK"]
[null,null,"Risk Parity",0,0.75,0.25,"OK"]
[null,null,"Forestry",0,0.75,0.25,"OK"]
[null,null,"Agriculture",0,0.75,0.25,"OK"]
[null,null,"Commodities",0,0.75,0.25,"OK"]
[null,null,"Catastrophe bonds",0,0.75,0.25,"OK"]
[null,null,"Life Settlements",0,0.75,0.25,"OK"]
[null,null,"Insurance-Linked Strategies",0,0.75,0.25,"OK"]
[null,null,"Other - insert name",0,0.75,0.25,"OK"]
[null,null,null,{"$f":"SUM(D92:D104)","v":0.02},{"$f":"IF($D$105=0,0,SUMPRODUCT($D$92:$D$104,$E$92:$E$104)/$D$105)","v":0.75},{"$f":"IF($D$105=0,0,SUMPRODUCT($D$92:$D$104,$F$92:$F$104)/$D$105)","v":0.25},{"$f":"COUNTIF(G92:G104,\"*Error*\")","v":0},"errors"]
[]
[]
[null,null,"Select scoring approach for non-index credit"]
[null,"Credit","Detailed","Allocation","Growth Score","Defensive Exposure","Approach Check"]
[null,null,"Australian Investment Grade  (index  and less risky)",0,0,{"$f":"100%-E110","v":1}]
[null,null,"Australia - Other Credit - simple / prescriptive",0,0.7,{"$f":"100%-E111","v":0.30000000000000004},{"$f":"IF(AND($C$109=\"Detailed\",D111<>0),\"Error: If you adopt the detailed approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Australia - Other Credit - detailed",0,0.5,0.5,{"$f":"IF(AND($C$109=\"Simple\",D112<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Global Investment Grade (index and less risky)",0,0,1]
[null,null,"Global High Yield (index)",0,0.6,0.4]
[null,null,"Global Bank Loans (index)",0,0.7,0.30000000000000004]
[null,null,"Global - Other Credit - simple / prescriptive",0,0.7,0.30000000000000004,{"$f":"IF(AND($C$109=\"Detailed\",D116<>0),\"Error: If you adopt the detailed approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Global - Other Credit - detailed",0,null,1,{"$f":"IF(AND($C$109=\"Simple\",D117<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,null,{"$f":"SUM(D110:D117)","v":0},{"$f":"IF($D$118=0,0,SUMPRODUCT($D$110:$D$117,$E$110:$E$117)/$D$118)","v":0},{"$f":"IF($D$118=0,0,SUMPRODUCT($D$110:$D$117,$F$110:$F$117)/$D$118)","v":0},{"$f":"COUNTIF(G110:G117,\"*Error*\")","v":0},"errors"]
[]
[]
[null,null,"Select scoring approach for high risk fixed income"]
[null,"Fixed Interest","Detailed","Allocation","Growth Score","Defensive Exposure","Approach Check"]
[null,null,"Australian Government (index)",0,0,{"$f":"100%-E123","v":1}]
[null,null,"Australian Composite (index)",0.115,0,{"$f":"100%-E124","v":1}]
[null,null,"Australian Inflation-linked (index)",0,0,1]
[null,null,"Australian Other - low risk",0,0,1]
[null,null,"Australian Other - high risk - simple / prescriptive",0,0.7,0.30000000000000004,{"$f":"IF(AND($C$122=\"Detailed\",D127<>0),\"Error: If you adopt the detailed approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Australian Other - high risk - detailed",0,null,1,{"$f":"IF(AND($C$122=\"Simple\",D128<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Global Government (index)",0,0,1]
[null,null,"Global Composite (index)",0.115,0,1]
[null,null,"Global Inflation-linked (index)",0,0,1]
[null,null,"Global Other - low risk",0,0,1]
[null,null,"Global Other - high risk - simple / prescriptive",0,0.7,0.30000000000000004,{"$f":"IF(AND($C$122=\"Detailed\",D133<>0),\"Error: If you adopt the detailed approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Global Other - high risk - detailed",0,{"$f":"5/12","v":0.41666666666666669},0.58333333333333326,{"$f":"IF(AND($C$122=\"Simple\",D134<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,null,{"$f":"SUM(D123:D134)","v":0.23},{"$f":"IF($D$135=0,0,SUMPRODUCT($D$123:$D$134,$E$123:$E$134)/$D$135)","v":0},{"$f":"IF($D$135=0,0,SUMPRODUCT($D$123:$D$134,$F$123:$F$134)/$D$135)","v":1},{"$f":"COUNTIF(G123:G134,\"*Error*\")","v":0},"errors"]
[]
[]
[null,null,"Select scoring approach for enhanced cash"]
[null,"Cash & Cash-Enhanced","Detailed","Allocation","Growth Score","Defensive Exposure","Approach Check"]
[null,null,"Cash at Bank",0.05,0,{"$f":"100%-E140","v":1}]
[null,null,"Bank Bills, NCD's and Term Deposits",0,0,1]
[null,null,"Cash enhanced strategies - simple / prescriptive",0,0.2,0.8,{"$f":"IF(AND($C$139=\"Detailed\",D142<>0),\"Error: If you adopt the detailed approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,"Cash enhanced strategies - detailed",0,null,1,{"$f":"IF(AND($C$139=\"Simple\",D143<>0),\"Error: If you adopt the simple approach this cell must be zero\",\"OK\")","v":"OK"}]
[null,null,null,{"$f":"SUM(D140:D143)","v":0.05},{"$f":"IF($D$144=0,0,SUMPRODUCT($D$140:$D$143,$E$140:$E$143)/$D$144)","v":0},{"$f":"IF($D$144=0,0,SUMPRODUCT($D$140:$D$143,$F$140:$F$143)/$D$144)","v":1},{"$f":"COUNTIF(G140:G143,\"*Error*\")","v":0},"errors"]
[]
[]
[null,"Results"]
[null,null,"Test: Have you applied simple / detailed approach correctly?",{"$f":"IF((G66+G87+G105+G118+G135+G144)>0,\"No, review required\", \"Yes\")","v":"Yes"}]
[null,null,"Compliant test: ",{"$f":"IF(D58+D62+D79+D83+D92+D111+D116+D127+D133<=10%,\"Compliant\", \"Not compliant: more detail required otherwise you will be flagged.\")","v":"Compliant"}]
[]
[null,null,"Exposure Check:",{"$f":"SUM(Aus_Eq_Ref,D29,D37,D53,D66,D74,D87,D105,D118,D135,D144)","v":1},{"$f":"IF(D151<>100%,\"CHECK\", \"\")","v":""}]
[]
[null,null,null,"Growth","Defensive"]
[null,null,"Growth / Defensive Score:",{"$f":"ROUND(Aus_Eq_Ref*E15+D29*E29+D37*E37+D45*E45+D53*E53+D66*E66+D74*E74+D87*E87+D105*E105+D118*E118+D135*E135+D144*E144,2)","v":0.65},{"$f":"1-D154","v":0.35}]

{"sheet":"Categorisation - Unlisted Prop","index":3,"n_rows":58,"n_cols":15,"rows_kept":58,"cut":false,"hidden":true,"merged":[]}
["Unlisted Property Calculator"]
[]
[null,"Key","Do not touch orange cells"]
[null,null,"Fill in the green cells"]
[]
["Individual Property"]
[]
[null,null,null,"Level","Comment"]
[null,null,"Leverage (LVR):",0.35,{"$f":"IF(D9>50%,\"Failed Test A criteria\",IF(D9>30%,\"Failed Test B criteria\",\"\"))","v":"Failed Test B criteria"}]
[null,null,"Value-add (%1):",0.1,{"$f":"IF(D10>50%,\"Failed Test A criteria\",IF(D10>20%,\"Failed Test B criteria\",\"\"))","v":""}]
[null,null,"Sector exposure (%2): ",0.15,{"$f":"IF(D11>50%,\"Failed Test A criteria\",IF(D11>20%,\"Failed Test B criteria\",\"\"))","v":""}]
[]
[null,null,"Overall categorisation:",null,{"$f":"IF(COUNTIF(E9:E11,\"Failed Test A criteria\")>0,\"Tier 2 Risk\",IF(COUNTIF(E9:E11,\"Failed Test B criteria\")>1,\"Tier 2 Risk\", \"Tier 1 Risk\"))","v":"Tier 1 Risk"}]
[]
[null,null,"1 - value-add: non-income producing assets as a % of fund gross asset value"]
[null,null,"2 - % gross asset exposure outside of office, retail, industrial, and multi-family"]
[]
[]
[null,null,"Portfolio Calculator",null,null,"Investment 1","Investment 2","Investment 3","Investment 4","Investment 5","Investment 6","Investment 7","Investment 8","Investment 9","Investment 10"]
[null,null,null,null,"Position Size (% of Property Portfolio)",0.3,0.3,0.4,0,0,0,0,0,0,0]
[null,null,null,null,"Global Exposure (position level):",0.2,0.2,0,0,0.5,0.5,1,1,0.2,0.2]
[null,null,null,null,"Australian Exposure:",{"$f":"F20*(1-F21)","v":0.24},{"$f":"G20*(1-G21)","v":0.24},0.4,0,0,0,0,0,0,0]
[null,null,null,null,"Global Exposure:",{"$f":"F20*F21","v":0.06},{"$f":"G20*G21","v":0.06},0,0,0,0,0,0,0,0]
[null,null,null,null,"Leverage (LVR):",0.3,0.25,0.25,0.2,0.2,0.6,0.2,0.3,0.2,0.2]
[null,null,null,null,"Value-add (%):",0.3,0.15,0.15,0.5,0.2,0.1,0.2,0.2,0.4,0.2]
[null,null,null,null,"Sector exposure (%): ",0.15,0.15,0.15,0.15,0.15,0.15,0.15,0.15,0.15,0.15]
[null,null,null,null,"Leverage (LVR) Test:",{"$f":"IF(F24>50%,\"Failed Test A criteria\",IF(F24>30%,\"Failed Test B criteria\",\"\"))","v":""},{"$f":"IF(G24>50%,\"Failed Test A criteria\",IF(G24>30%,\"Failed Test B criteria\",\"\"))","v":""},"","","","Failed Test A criteria","","","",""]
[null,null,null,null,"Value-add (%) Test:",{"$f":"IF(F25>50%,\"Failed Test A criteria\",IF(F25>20%,\"Failed Test B criteria\",\"\"))","v":"Failed Test B criteria"},{"$f":"IF(G25>50%,\"Failed Test A criteria\",IF(G25>20%,\"Failed Test B criteria\",\"\"))","v":""},"","Failed Test B criteria","","","","","Failed Test B criteria",""]
[null,null,null,null,"Sector exposure Test:",{"$f":"IF(F26>50%,\"Failed Test A criteria\",IF(F26>20%,\"Failed Test B criteria\",\"\"))","v":""},{"$f":"IF(G26>50%,\"Failed Test A criteria\",IF(G26>20%,\"Failed Test B criteria\",\"\"))","v":""},"","","","","","","",""]
[null,null,null,null,"Overall Categorisation:",{"$f":"IF(COUNTIF(F26:F28,\"Failed Test A criteria\")>0,\"Tier 2 Risk\",IF(COUNTIF(F26:F28,\"Failed Test B criteria\")>1,\"Tier 2 Risk\", \"Tier 1 Risk\"))","v":"Tier 1 Risk"},{"$f":"IF(COUNTIF(G26:G28,\"Failed Test A criteria\")>0,\"Tier 2 Risk\",IF(COUNTIF(G26:G28,\"Failed Test B criteria\")>1,\"Tier 2 Risk\", \"Tier 1 Risk\"))","v":"Tier 1 Risk"},"Tier 1 Risk","Tier 1 Risk","Tier 1 Risk","Tier 2 Risk","Tier 1 Risk","Tier 1 Risk","Tier 1 Risk","Tier 1 Risk"]
[]
[]
[null,null,"Portfolio-Level Test",null,null,"Portfolio"]
[null,null,null,null,"Position Size (% of Property Portfolio)",{"$f":"SUM(F20:O20)","v":1}]
[null,null,null,null,"Australian Exposure:",{"$f":"SUM(F22:O22)","v":0.88}]
[null,null,null,null,"Global Exposure:",{"$f":"1-F35","v":0.12}]
[null,null,null,null,"Leverage (LVR):",{"$f":"SUMPRODUCT(F$20:O$20,F24:O24)","v":0.26500000000000001}]
[null,null,null,null,"Value-add (%):",{"$f":"SUMPRODUCT(F$20:O$20,F25:O25)","v":0.19500000000000001}]
[null,null,null,null,"Sector exposure (%): ",0.15]
[null,null,null,null,"Leverage (LVR) Test:",{"$f":"IF(F37>50%,\"Failed Test A criteria\",IF(F37>30%,\"Failed Test B criteria\",\"\"))","v":""}]
[null,null,null,null,"Value-add (%) Test:",{"$f":"IF(F38>50%,\"Failed Test A criteria\",IF(F38>20%,\"Failed Test B criteria\",\"\"))","v":""}]
[null,null,null,null,"Sector exposure Test:",{"$f":"IF(F39>50%,\"Failed Test A criteria\",IF(F39>20%,\"Failed Test B criteria\",\"\"))","v":""}]
[null,null,null,null,"Overall Categorisation:",{"$f":"IF(COUNTIF(F39:F41,\"Failed Test A criteria\")>0,\"Tier 2 Risk\",IF(COUNTIF(F39:F41,\"Failed Test B criteria\")>1,\"Tier 2 Risk\", \"Tier 1 Risk\"))","v":"Tier 1 Risk"}]
[]
[]
[null,null,"Portfolio-Look Through",null,"Portfolio Check",{"$f":"SUM(F20:O20)","v":1}]
[null,null,null,null,null,"Tier 1 Risk","Tier 2 Risk"]
[null,null,null,null,"Australian Unlisted Property:",{"$f":"SUMIF(F30:O30,\"Tier 1 Risk\",F22:O22)","v":0.88},{"$f":"SUMIF(F30:O30,\"Tier 2 Risk\",F22:O22)","v":0}]
[null,null,null,null,"Global Unlisted Property:",{"$f":"SUMIF(F30:O30,\"Tier 1 Risk\",F23:O23)","v":0.12},{"$f":"SUMIF(F30:O30,\"Tier 2 Risk\",F23:O23)","v":0}]
[]
[]
[null,null,"Final Score",null,{"$f":"IF($F$43=\"Tier 1 Risk\",\"Entire unlisted property portfolio categorised as Tier 1 Risk\",\"Portfolio look-through calculations apply\")","v":"Entire unlisted property portfolio categorised as Tier 1 Risk"}]
[]
[null,null,null,null,null,"Tier 1 Risk","Tier 2 Risk"]
[null,null,null,null,"Australian Unlisted Property:",{"$f":"IF($F$43=\"Tier 1 Risk\",F35,F48)","v":0.88},{"$f":"IF($F$43=\"Tier 1 Risk\",0,G48)","v":0}]
[null,null,null,null,"Global Unlisted Property:",{"$f":"IF($F$43=\"Tier 1 Risk\",F36,F49)","v":0.12},{"$f":"IF($F$43=\"Tier 1 Risk\",0,G49)","v":0}]
[]
[null,null,null,null,"Total Portfolio Score:",{"$f":"SUM(F55:F56)*60%+SUM(G55:G56)*100%","v":0.6}]

{"sheet":"Categorisation - Unlisted Infra","index":4,"n_rows":70,"n_cols":15,"rows_kept":70,"cut":false,"hidden":true,"merged":[]}
["Unlisted Infrastructure Calculator"]
[]
[null,"Key","Do not touch orange cells"]
[null,null,"Fill in the green cells"]
[]
["Individual Infrastructure"]
[]
[null,null,null,"Level","Comment"]
[null,null,"Leverage (LVR):",0.3,{"$f":"IF(D9>80%,\"Failed Test A criteria\",IF(D9>70%,\"Failed Test B criteria\",\"\"))","v":""}]
[null,null,"Price-based revenue exposure (%1):",0.3,{"$f":"IF(D10>50%,\"Failed Test A criteria\",IF(D10>20%,\"Failed Test B criteria\",\"\"))","v":"Failed Test B criteria"}]
[null,null,"Volume-based revenue exposure (%2):",0.25,{"$f":"IF(D11>100%,\"Failed Test A criteria\",IF(D11>80%,\"Failed Test B criteria\",\"\"))","v":""}]
[null,null,"Asset stage (%3):",0.2,{"$f":"IF(D12>50%,\"Failed Test A criteria\",IF(D12>20%,\"Failed Test B criteria\",\"\"))","v":""}]
[null,null,"Geographical exposure (%4):",0.3,{"$f":"IF(D13>50%,\"Failed Test A criteria\",IF(D13>20%,\"Failed Test B criteria\",\"\"))","v":"Failed Test B criteria"}]
[]
[null,null,"Overall categorisation:",null,{"$f":"IF(COUNTIF(E9:E13,\"Failed Test A criteria\")>0,\"Tier 2 Risk\",IF(COUNTIF(E9:E13,\"Failed Test B criteria\")>1,\"Tier 2 Risk\", \"Tier 1 Risk\"))","v":"Tier 2 Risk"}]
[]
[null,null,"1 - Price-based revenue exposure: % of revenue sources with market-based pricing"]
[null,null,"2 - Volume-based revenue exposure: % of revenue sources with volume-based pricing (i.e. patronage)"]
[null,null,"3 - Asset stage: non-income producing investments as a % of asset / vehicle gross asset value"]
[null,null,"4 - Geographical exposure: % revenues sourced from emerging markets (defined to be members of MSCI Emerging Markets Index)"]
[]
[]
[null,null,"Portfolio Calculator",null,null,"Investment 1","Investment 2","Investment 3","Investment 4","Investment 5","Investment 6","Investment 7","Investment 8","Investment 9","Investment 10"]
[null,null,null,null,"Position Size (% of Infra Portfolio)",0.3,0.3,0.4,0,0,0,0,0,0,0]
[null,null,null,null,"Global Exposure (position level):",0.2,0.2,0,0,0.5,0.5,1,1,0.2,0.2]
[null,null,null,null,"Australian Exposure:",{"$f":"F24*(1-F25)","v":0.24},{"$f":"G24*(1-G25)","v":0.24},0.4,0,0,0,0,0,0,0]
[null,null,null,null,"Global Exposure:",{"$f":"F24*F25","v":0.06},{"$f":"G24*G25","v":0.06},0,0,0,0,0,0,0,0]
[null,null,null,null,"Leverage (LVR):",0.5,0.6,0.5,0.2,0.2,0.6,0.2,0.3,0.2,0.2]
[null,null,null,null,"Price-based revenue exposure (%):",0.3,0.15,0.2,0.5,0.2,0.1,0.2,0.2,0.4,0.2]
[null,null,null,null,"Volume-based revenue exposure (%):",0.15,0.15,0.15,0.15,0.15,0.15,0.15,0.15,0.15,0.15]
[null,null,null,null,"Asset stage (%):",0.1,0.4,0.15,0.1,0.1,0.1,0.1,0.1,0.1,0.1]
[null,null,null,null,"Geographical exposure (%):",0.1,0.3,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1]
[null,null,null,null,"Leverage (LVR) Test:",{"$f":"IF(F28>80%,\"Failed Test A criteria\",IF(F28>70%,\"Failed Test B criteria\",\"\"))","v":""},{"$f":"IF(G28>80%,\"Failed Test A criteria\",IF(G28>70%,\"Failed Test B criteria\",\"\"))","v":""},"","","","","","","",""]
[null,null,null,null,"Price-based revenue exposure Test:",{"$f":"IF(F29>50%,\"Failed Test A criteria\",IF(F29>20%,\"Failed Test B criteria\",\"\"))","v":"Failed Test B criteria"},{"$f":"IF(G29>50%,\"Failed Test A criteria\",IF(G29>20%,\"Failed Test B criteria\",\"\"))","v":""},"","Failed Test B criteria","","","","","Failed Test B criteria",""]
[null,null,null,null,"Volume-based revenue exposure Test:",{"$f":"IF(F30>100%,\"Failed Test A criteria\",IF(F30>80%,\"Failed Test B criteria\",\"\"))","v":""},{"$f":"IF(G30>100%,\"Failed Test A criteria\",IF(G30>80%,\"Failed Test B criteria\",\"\"))","v":""},"","","","","","","",""]
[null,null,null,null,"Asset stage Test:",{"$f":"IF(F31>50%,\"Failed Test A criteria\",IF(F31>20%,\"Failed Test B criteria\",\"\"))","v":""},{"$f":"IF(G31>50%,\"Failed Test A criteria\",IF(G31>20%,\"Failed Test B criteria\",\"\"))","v":"Failed Test B criteria"},"","","","","","","",""]
[null,null,null,null,"Geographical exposure Test:",{"$f":"IF(F32>50%,\"Failed Test A criteria\",IF(F32>20%,\"Failed Test B criteria\",\"\"))","v":""},{"$f":"IF(G32>50%,\"Failed Test A criteria\",IF(G32>20%,\"Failed Test B criteria\",\"\"))","v":"Failed Test B criteria"},"","","","","","","",""]
[null,null,null,null,"Overall Categorisation:",{"$f":"IF(COUNTIF(F33:F37,\"Failed Test A criteria\")>0,\"Tier 2 Risk\",IF(COUNTIF(F33:F37,\"Failed Test B criteria\")>1,\"Tier 2 Risk\", \"Tier 1 Risk\"))","v":"Tier 1 Risk"},{"$f":"IF(COUNTIF(G33:G37,\"Failed Test A criteria\")>0,\"Tier 2 Risk\",IF(COUNTIF(G33:G37,\"Failed Test B criteria\")>1,\"Tier 2 Risk\", \"Tier 1 Risk\"))","v":"Tier 2 Risk"},"Tier 1 Risk","Tier 1 Risk","Tier 1 Risk","Tier 1 Risk","Tier 1 Risk","Tier 1 Risk","Tier 1 Risk","Tier 1 Risk"]
[]
[]
[null,null,"Portfolio-Level Test",null,null,"Portfolio"]
[null,null,null,null,"Position Size (% of Infra Portfolio)",{"$f":"SUM(F24:O24)","v":1}]
[null,null,null,null,"Australian Exposure:",{"$f":"SUM(F26:O26)","v":0.88}]
[null,null,null,null,"Global Exposure:",{"$f":"1-F43","v":0.12}]
[null,null,null,null,"Leverage (LVR):",{"$f":"SUMPRODUCT(F$24:O$24,F28:O28)","v":0.53}]
[null,null,null,null,"Price-based revenue exposure (%):",{"$f":"SUMPRODUCT(F$24:O$24,F29:O29)","v":0.21500000000000002}]
[null,null,null,null,"Volume-based revenue exposure (%):",0.15]
[null,null,null,null,"Asset stage (%):",0.21]
[null,null,null,null,"Geographical exposure (%):",0.16]
[null,null,null,null,"Leverage (LVR) Test:",{"$f":"IF(F45>80%,\"Failed Test A criteria\",IF(F45>70%,\"Failed Test B criteria\",\"\"))","v":""}]
[null,null,null,null,"Price-based revenue exposure Test:",{"$f":"IF(F46>50%,\"Failed Test A criteria\",IF(F46>20%,\"Failed Test B criteria\",\"\"))","v":"Failed Test B criteria"}]
[null,null,null,null,"Volume-based revenue exposure Test:",{"$f":"IF(F47>100%,\"Failed Test A criteria\",IF(F47>80%,\"Failed Test B criteria\",\"\"))","v":""}]
[null,null,null,null,"Asset stage Test:",{"$f":"IF(F48>50%,\"Failed Test A criteria\",IF(F48>20%,\"Failed Test B criteria\",\"\"))","v":"Failed Test B criteria"}]
[null,null,null,null,"Geographical exposure Test:",{"$f":"IF(F49>50%,\"Failed Test A criteria\",IF(F49>20%,\"Failed Test B criteria\",\"\"))","v":""}]
[null,null,null,null,"Overall Categorisation:",{"$f":"IF(COUNTIF(F50:F54,\"Failed Test A criteria\")>0,\"Tier B Risk\",IF(COUNTIF(F50:F54,\"Failed Test B criteria\")>1,\"Tier 2 Risk\", \"Tier 1 Risk\"))","v":"Tier 2 Risk"}]
[]
[]
[null,null,"Portfolio-Look Through",null,"Portfolio Check",{"$f":"SUM(F24:O24)","v":1}]
[null,null,null,null,null,"Tier 1 Risk","Tier 2 Risk"]
[null,null,null,null,"Australian Unlisted Infrastructure:",{"$f":"SUMIF(F38:O38,\"Tier 1 Risk\",F26:O26)","v":0.64},{"$f":"SUMIF(F38:O38,\"Tier 2 Risk\",F26:O26)","v":0.24}]
[null,null,null,null,"Global Unlisted Infrastructure:",{"$f":"SUMIF(F38:O38,\"Tier 1 Risk\",F27:O27)","v":0.06},{"$f":"SUMIF(F38:O38,\"Tier 2 Risk\",F27:O27)","v":0.06}]
[]
[]
[null,null,"Final Score",null,{"$f":"IF($F$55=\"Tier 1 Risk\",\"Entire unlisted property portfolio categorised as Tier 1 Risk\",\"Portfolio look-through calculations apply\")","v":"Portfolio look-through calculations apply"}]
[]
[null,null,null,null,null,"Tier 1 Risk","Tier 2 Risk"]
[null,null,null,null,"Australian Unlisted Infrastructure:",{"$f":"IF($F$55=\"Tier 1 Risk\",F43,F60)","v":0.64},{"$f":"IF($F$55=\"Tier 1 Risk\",0,G60)","v":0.24}]
[null,null,null,null,"Global Unlisted Infrastructure:",{"$f":"IF($F$55=\"Tier 1 Risk\",F44,F61)","v":0.06},{"$f":"IF($F$55=\"Tier 1 Risk\",0,G61)","v":0.06}]
[]
[null,null,null,null,"Total Portfolio Score:",{"$f":"SUM(F67:F68)*60%+SUM(G67:G68)*100%","v":0.72}]

{"sheet":"Risk Scaling - Hedge Funds","index":5,"n_rows":15,"n_cols":12,"rows_kept":15,"cut":false,"hidden":true,"merged":[]}
["Hedge Funds"]
[null,null,null,"Note: this template could be applied to any other sector where risk scaling is applied."]
["Key","Do not touch orange cells"]
[null,"Fill in the green cells"]
[]
[null,"Hedge Fund","Position Size","Strategy","Volatility Expectations","Drawdown Expectations","Notes to Support Expectations","Volatility-based Score","Drawdown-based Growth Score","Conservative Approach: Growth Score","Final Selected Growth Score","Rationale"]
[null,"Fund 1",0.2,"Equity Market Neutral",0.1,0.25,"- Volatilty based on manager target and realised\n- Drawdowns less extreme that GFC history as manager has a formalised DD control strategy",{"$f":"E7/12%","v":0.83333333333333337},{"$f":"F7/50%","v":0.5},{"$f":"MAX(H7,I7)","v":0.83333333333333337},0.8,"Volatility is considered the more reliable measure and a drawdown control strategy may not work in gapping markets."]
[null,"Fund 2",0.2,"Multi-Strategy",0.06,0.18,"- Volatilty based on a concervative assessment of historical as manager hasn't hit targeted volatility levels\n- Drawdowns account for the history of correlations between sub-strategies becoming higher in a crisis environment",{"$f":"E8/12%","v":0.5},{"$f":"F8/50%","v":0.36},{"$f":"MAX(H8,I8)","v":0.5},0.5,"Volatility is considered the more conservative measure."]
[null,"Fund 3",0.2,"CTA",0.12,0.35,"- Volatilty based on manager target and realised\n- Drawdowns based on GFC history for strategy scaled to manager volatility",1,0.7,1,1,"Volatility is considered the more conservative measure."]
[null,"Fund 4",0.2,"Long-short Credit",7.0000000000000007E-2,0.28000000000000003,"- Volatilty based on a concervative assessment of historical as manager hasn't hit targeted volatility levels\n- Drawdown accounts for experience during crisis of illiquidity",0.58333333333333337,0.56000000000000005,0.58333333333333337,0.6,"Volatility is considered the more conservative measure."]
[null,"Fund 5",0.2,"Tail Risk Hedge Fund",0.1,0.4,"- Volatilty based on manager target and realised\n- Drawdowns based on target manager premia spend",0.83333333333333337,0.8,0.83333333333333337,0,"Tail risk hedge funds can be categorised as 100% D."]
[]
[]
[null,"Portfolio Check",{"$f":"SUM(C7:C11)","v":1}]
[null,"Growth Score",{"$f":"SUMPRODUCT(C7:C11,K7:K11)","v":0.58000000000000007}]