{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Instructions","Input - Capital","Input - Asset Quality","Input - Earnings","Input - Liquidity","Input - Qualitative","Summary"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":1,"defined_names":1,"comments":0,"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":"Instructions","index":0,"n_rows":49,"n_cols":7,"rows_kept":49,"cut":false,"hidden":false,"merged":[[45,2,45,5],[15,5,17,17],[42,2,42,5],[44,2,44,5],[30,5,30,20],[43,2,43,5],[18,5,20,17],[21,5,23,17],[24,5,26,17]]}
[]
[null,null,"Instruction Guide"]
[]
[null,null,{"$f":"G43","v":2023},"CUDIC DPS Calculator"]
[]
[null,null,"This is the CUDIC Differential Premium System (\"DPS\") Calculator which is intended to help credit unions to estimate their total DPS assessment scores and premium for a given assessment year."]
[null,null,"Please ensure that the year of this DPS Calculator corresponds with your desired assessment year."]
[]
[null,null,"The results produced by this DPS Calculator are based on data inputted by the user and are intended solely for informational and discussion purposes. They are not, and may not be relied on, as final or approved results"]
[null,null,"from BC Financial Services Authority (\"BCFSA\") or the Credit Union Deposit Insurance Corporation of British Columbia (\"CUDIC\")."]
[]
[null,null,"The following tabs for the quantitative risk metric calculations require input for each individual component of the metric. Scores are then calculated automatically based on the calculated ratio relative to the ratio's scoring range."]
[null,null,"(e.g., The Input - Capital tab requires inputs of Capital Base and Risk Weighted Assets in dollar amounts for Capital Adequacy Ratio to be calculated)."]
[]
[null,null,"The calculator provides two options for input of data and scoring. Depending on the possible types of input, each tab contains button(s) at the top-left corner with various functionalities. Below are all of the buttons with explanations on what they do:"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"NOTE:"]
[null,null,"This calculator spreadsheet is only able to take into account ONE INPUT MODE for the purpose of Total Score assignment. Only fill input data to ONE INPUT MODE and leave the other mode as it is. If you would like to switch input mode,"]
[null,null,"make sure to clear all input first to avoid data conflict that results in inaccurate Total Score assignment."]
[]
[null,null,"Below is the calculator's typical usage procedure:"]
[null,1,{"$f":"B33 & \".\"","v":"1."},"If there is an option to choose an input mode, select one of the input modes (Calculated Value or Individual Data input mode). If there is none, simply input data in the available input cells in yellow."]
[null,2,{"$f":"B34 & \".\"","v":"2."},"Enter the input values in the yellow cells in the appropriate table as labelled. Once input cells are filled, their respective ratios and scores are calculated automatically."]
[null,3,{"$f":"B35 & \".\"","v":"3."},"To delete any input, click 'Clear all input'. The button only clears input(s) in the currently active tab."]
[null,4,{"$f":"B36 & \".\"","v":"4."},"Once all tabs are completed, the 'Summary' tab will display the Total DPS Assessment Score."]
[]
[null,null,"The Summary tab outlines the scores for each of the risk metrics and combines them to estimate the Total DPS Assessment Score and Premium. "]
[null,null,"Note that there is no deposit insurance assessment calculated in this calculator. Total Score obtained from this calculator is to be used for ESTIMATION only, as CUDIC will calculate its assessments independently "]
[null,null,"based on audited year-end data. Finalization of the scoring ranges for the various risk metrics will take place closer to the date of DPS implementation in 2023, thereby allowing for the most current data to inform the appropriate ranges."]
[]
[null,null,"Please enter the following in cells highlighted yellow:"]
[null,null,"Assessment Year",null,null,null,2023]
[null,null,"Base Assessment Rate (bps)"]
[null,null,"Fiscal Year Start Month"]
[null,null,"Fiscal Year-End Insured Deposits"]
[]
[]
[null,null,"Classification:  Public"]

{"sheet":"Input - Capital","index":1,"n_rows":44,"n_cols":14,"rows_kept":44,"cut":false,"hidden":false,"merged":[[12,12,12,13],[17,12,17,13],[18,4,19,4],[18,7,19,7],[17,2,17,4],[17,5,17,7],[10,2,11,2],[10,3,11,4],[10,5,11,5],[10,6,11,7],[13,2,14,2],[13,3,14,4],[13,5,14,5],[13,6,14,7]]}
[]
[null,null,"Input data in cells highlighted YELLOW"]
[]
[null,null,{"$f":"\"For Assessment Year\" & \" \" & Instructions!$G$43","v":"For Assessment Year 2023"}]
[]
[]
[]
[]
[null,null,"Capital Risk Scoring",null,null,null,null,null,null,"Capital"]
[null,null,"Capital Adequacy Ratio (CAR)",null,null,null,null,null,null,"Capital Adequacy Ratio (CAR)"]
[null,null,"Ratio",{"$f":"$M$13","v":{"$error":"#DIV/0!"}},null,"Score",{"$f":"VLOOKUP(\"Y\",A21:E41,5,FALSE)","v":{"$error":"#N/A"}},null,null,"Credit Union Capital Base ($)",null,null,"6000-100-10"]
[null,null,null,null,null,null,null,null,null,"Risk Weighted Assets ($)",null,null,"6000-150-10"]
[null,null,"Leverage Ratio",null,null,null,null,null,null,"Year Ratio",null,null,{"$f":"N11/N12","v":{"$error":"#DIV/0!"}}]
[null,null,"Ratio",{"$f":"$M$18","v":{"$error":"#DIV/0!"}},null,"Score",{"$f":"VLOOKUP(\"Y\",B21:H31,7,FALSE)","v":{"$error":"#N/A"}},null,null,"Leverage Ratio"]
[null,null,null,null,null,null,null,null,null,"Credit Union Capital Base ($)",null,null,"6000-100-10",{"$f":"N11","v":0}]
[null,null,null,null,null,null,null,null,null,"Total Assets ($)",null,null,"1000-500-10"]
[null,null,null,null,null,null,null,null,null,"Risk Weighted Off-B/S Exposures ($)",null,null,"6030-200-40 + 6040-200-50"]
[null,null,"CAR Proposed Ranges",null,null,"Leverage Ratio Proposed Ranges",null,null,null,"Year Ratio",null,null,{"$f":"(N15)/(N16+N17)","v":{"$error":"#DIV/0!"}}]
[null,null,"From","To","Score","From","To","Score"]
[null,null,"≥","<",null,"≥","<"]
[{"$f":"IF(D$11>=C21,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(D$14>=F21,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},0.13,"& above",20,7.0000000000000007E-2,"& above",10]
[{"$f":"IF(AND(D$11>=C22,D$11<D22),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(AND(D$14>=F22,D$14<G22),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},0.12740000000000001,{"$f":"C21","v":0.13},19,6.5600000000000006E-2,{"$f":"F21","v":7.0000000000000007E-2},9]
[{"$f":"IF(AND(D$11>=C23,D$11<D23),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(AND(D$14>=F23,D$14<G23),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},0.12470000000000001,{"$f":"C22","v":0.12740000000000001},18,6.1100000000000002E-2,{"$f":"F22","v":6.5600000000000006E-2},8]
[{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},0.1221,{"$f":"C23","v":0.12470000000000001},17,5.67E-2,{"$f":"F23","v":6.1100000000000002E-2},7]
[{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},0.1195,0.1221,16,5.2200000000000003E-2,5.67E-2,6]
[{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},0.1169,0.1195,15,4.7800000000000002E-2,5.2200000000000003E-2,5]
[{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},0.1142,0.1169,14,4.3299999999999998E-2,4.7800000000000002E-2,4]
[{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},0.1116,0.1142,13,3.8899999999999997E-2,4.3299999999999998E-2,3]
[{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},0.109,0.1116,12,3.44E-2,3.8899999999999997E-2,2]
[{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},0.10630000000000001,0.109,11,0.03,3.44E-2,1]
[{"$error":"#DIV/0!"},{"$f":"IF(D$14<G31,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},0.1037,0.10630000000000001,10,"Below",0.03,0]
[{"$error":"#DIV/0!"},null,0.1011,0.1037,9]
[{"$error":"#DIV/0!"},null,9.8400000000000001E-2,0.1011,8]
[{"$error":"#DIV/0!"},null,9.5799999999999996E-2,9.8400000000000001E-2,7]
[{"$error":"#DIV/0!"},null,9.3200000000000005E-2,9.5799999999999996E-2,6]
[{"$error":"#DIV/0!"},null,9.06E-2,9.3200000000000005E-2,5]
[{"$error":"#DIV/0!"},null,8.7900000000000006E-2,9.06E-2,4]
[{"$error":"#DIV/0!"},null,8.5300000000000001E-2,{"$f":"C37","v":8.7900000000000006E-2},3]
[{"$error":"#DIV/0!"},null,8.2699999999999996E-2,{"$f":"C38","v":8.5300000000000001E-2},2]
[{"$error":"#DIV/0!"},null,0.08,{"$f":"C39","v":8.2699999999999996E-2},1]
[{"$f":"IF(D$11<D41,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},null,"Below",{"$f":"C40","v":0.08},0]
[]
[]
[null,null,"Classification:  Public"]

{"sheet":"Input - Asset Quality","index":2,"n_rows":35,"n_cols":32,"rows_kept":35,"cut":false,"hidden":false,"merged":[[26,2,26,3],[24,7,25,7],[16,2,17,2],[16,3,17,4],[16,5,17,5],[16,6,17,7],[19,2,20,2],[19,3,20,4],[19,5,20,5],[19,6,20,7],[23,2,23,4],[23,5,23,7],[11,14,12,19],[24,14,25,19],[24,4,25,4],[30,11,30,12],[24,11,24,12],[26,5,26,6],[29,14,31,17],[29,18,31,18]]}
[]
[null,null,"Input data in cells highlighted YELLOW"]
[]
[null,null,{"$f":"\"For Assessment Year\" & \" \" & Instructions!$G$43","v":"For Assessment Year 2023"}]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,1,2,3,4,5,6,7,8,9,10,11,12]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,10,11,12,1,2,3,4,5,6,7,8,9]
[null,null,"*Choose one of two input methods: Calculated values as input or Individual data as input",null,null,null,null,null,null,"Asset Quality - Calculated Values",null,null,null,null,"Asset Quality - Individual Data"]
[null,null,"Calculated values as input: input estimates for monthly ratios",null,null,null,null,null,null,"Non-performing Loans to Total Loans",null,null,null,null,"Non-performing Loans to Total Loans",null,null,null,null,null,{"$f":"IF(Instructions!$G$45=\"\", \"Month 1\", (IF(Instructions!$G$45=\"January\",TEXT(U9*28,\"mmmm\"),TEXT(U10*28,\"mmmm\"))))","v":"Month 1"},{"$f":"IF(Instructions!$G$45=\"\", \"Month 2\",IF(Instructions!$G$45=\"January\",TEXT(V9*28,\"mmmm\"),TEXT(V10*28,\"mmmm\")))","v":"Month 2"},{"$f":"IF(Instructions!$G$45=\"\", \"Month 3\",IF(Instructions!$G$45=\"January\",TEXT(W9*28,\"mmmm\"),TEXT(W10*28,\"mmmm\")))","v":"Month 3"},{"$f":"IF(Instructions!$G$45=\"\", \"Month 4\",IF(Instructions!$G$45=\"January\",TEXT(X9*28,\"mmmm\"),TEXT(X10*28,\"mmmm\")))","v":"Month 4"},{"$f":"IF(Instructions!$G$45=\"\", \"Month 5\",IF(Instructions!$G$45=\"January\",TEXT(Y9*28,\"mmmm\"),TEXT(Y10*28,\"mmmm\")))","v":"Month 5"},{"$f":"IF(Instructions!$G$45=\"\", \"Month 6\",IF(Instructions!$G$45=\"January\",TEXT(Z9*28,\"mmmm\"),TEXT(Z10*28,\"mmmm\")))","v":"Month 6"},{"$f":"IF(Instructions!$G$45=\"\", \"Month 7 \",IF(Instructions!$G$45=\"January\",TEXT(AA9*28,\"mmmm\"),TEXT(AA10*28,\"mmmm\")))","v":"Month 7 "},{"$f":"IF(Instructions!$G$45=\"\", \"Month 8\",IF(Instructions!$G$45=\"January\",TEXT(AB9*28,\"mmmm\"),TEXT(AB10*28,\"mmmm\")))","v":"Month 8"},{"$f":"IF(Instructions!$G$45=\"\", \"Month 9\", IF(Instructions!$G$45=\"January\",TEXT(AC9*28,\"mmmm\"),TEXT(AC10*28,\"mmmm\")))","v":"Month 9"},{"$f":"IF(Instructions!$G$45=\"\", \"Month 10\",IF(Instructions!$G$45=\"January\",TEXT(AD9*28,\"mmmm\"),TEXT(AD10*28,\"mmmm\")))","v":"Month 10"},{"$f":"IF(Instructions!$G$45=\"\", \"Month 11\",IF(Instructions!$G$45=\"January\",TEXT(AE9*28,\"mmmm\"),TEXT(AE10*28,\"mmmm\")))","v":"Month 11"},{"$f":"IF(Instructions!$G$45=\"\", \"Month 12\",IF(Instructions!$G$45=\"January\",TEXT(AF9*28,\"mmmm\"),TEXT(AF10*28,\"mmmm\")))","v":"Month 12"}]
[null,null,"Individual data as input: input FSR line items individually for each month to calculate monthly ratios",null,null,null,null,null,null,"Monthly Ratio (%)",null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I14*28,\"mmmm\"),TEXT(N13*28,\"mmmm\"))","v":"January"},null,null,null,null,null,null,null,null,{"$f":"(U16-U19+U20)/U24","v":{"$error":"#DIV/0!"}},{"$f":"(V16-V19+V20)/V24","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[null,null,null,null,null,null,null,null,1,null,null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I15*28,\"mmmm\"),TEXT(N14*28,\"mmmm\"))","v":"January"},null,null,"Delinquent Loans (>91 days) ($)",null,null,null,"Sum of","1220-160-40"]
[null,null,"Asset Quality Risk Scoring",null,null,null,null,null,2,null,null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I16*28,\"mmmm\"),TEXT(N15*28,\"mmmm\"))","v":"January"},null,null,null,null,null,null,null,"1220-260-40"]
[null,null,"Non-performing Loans to Total Loans (NPL to TL)",null,null,null,null,null,3,null,null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I17*28,\"mmmm\"),TEXT(N16*28,\"mmmm\"))","v":"January"},null,null,null,null,null,null,null,null,{"$f":"SUM(U14:U15)","v":0}]
[null,null,"Ratio",{"$f":"IF(ISNUMBER(L25),L25,AVERAGE(U13:AF13))","v":{"$error":"#DIV/0!"}},null,"Score",{"$f":"VLOOKUP(\"Y\",A27:E32,5,FALSE)","v":{"$error":"#N/A"}},null,4,null,null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I18*28,\"mmmm\"),TEXT(N17*28,\"mmmm\"))","v":"January"},null,null,"Total Allowances ($)",null,null,null,"Sum of","1230-100-20"]
[null,null,null,null,null,null,null,null,5,null,null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I19*28,\"mmmm\"),TEXT(N18*28,\"mmmm\"))","v":"January"},null,null,null,null,null,null,null,"1230-110-20"]
[null,null,"Commercial 4/5 Rated Loans to Total Loans (4/5s to TL)",null,null,null,null,null,6,null,null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I20*28,\"mmmm\"),TEXT(N19*28,\"mmmm\"))","v":"January"},null,null,null,null,null,null,null,null,{"$f":"SUM(U17:U18)","v":0}]
[null,null,"Ratio",{"$f":"IF(ISNUMBER(L31),L31,AVERAGE(U26:X26))","v":{"$error":"#DIV/0!"}},null,"Score",{"$f":"VLOOKUP(\"Y\",B27:H32,7,FALSE)","v":{"$error":"#N/A"}},null,7,null,null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I21*28,\"mmmm\"),TEXT(N20*28,\"mmmm\"))","v":"January"},null,null,"Property Acquired in Settlement of Loans ($)",null,null,null,null,"1000-320-10"]
[null,null,null,null,null,null,null,null,8,null,null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I22*28,\"mmmm\"),TEXT(N21*28,\"mmmm\"))","v":"January"}]
[null,null,null,null,null,null,null,null,9,null,null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I23*28,\"mmmm\"),TEXT(N22*28,\"mmmm\"))","v":"January"},null,null,"Total Loans ($)",null,null,null,"Sum of","1000-200-10"]
[null,null,null,null,null,null,null,null,10,null,null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I24*28,\"mmmm\"),TEXT(N23*28,\"mmmm\"))","v":"January"},null,null,null,null,null,null,null,"1000-210-10"]
[null,null,"NPL to TL Proposed Ranges",null,null,"4/5s to TL Proposed Ranges",null,null,11,null,null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I25*28,\"mmmm\"),TEXT(N24*28,\"mmmm\"))","v":"January"},null,null,null,null,null,null,null,null,{"$f":"SUM(U22:U23)","v":0},{"$f":"SUM(V22:V23)","v":0},0,0,0,0,0,0,0,0,0,0]
[null,null,"From","To","Score","From","To","Score",12,"Year Average",null,{"$f":"AVERAGE($M$13:$M$24)","v":{"$error":"#DIV/0!"}},null,null,"Commercial 4/5 Rated Loans to Total Loans",null,null,null,null,null,{"$f":"IF(Instructions!$G$45=\"\", \"Quarter 1\",IF(Instructions!$G$45=\"January\",TEXT(W9*28,\"mmmm\"),TEXT(W10*28,\"mmmm\")))","v":"Quarter 1"},{"$f":"IF(Instructions!$G$45=\"\", \"Quarter 2\",IF(Instructions!$G$45=\"January\",TEXT(Z9*28,\"mmmm\"),TEXT(Z10*28,\"mmmm\")))","v":"Quarter 2"},{"$f":"IF(Instructions!$G$45=\"\", \"Quarter 3\",IF(Instructions!$G$45=\"January\",TEXT(AC9*28,\"mmmm\"),TEXT(AC10*28,\"mmmm\")))","v":"Quarter 3"},{"$f":"IF(Instructions!$G$45=\"\", \"Quarter 4\",IF(Instructions!$G$45=\"January\",TEXT(AF9*28,\"mmmm\"),TEXT(AF10*28,\"mmmm\")))","v":"Quarter 4"}]
[null,null,">","≤",null,">","≤",null,10,"Commercial 4/5 Rated Loans to Total Loans",null,null,null,null,null,null,null,null,null,null,{"$f":"U29/U32","v":{"$error":"#DIV/0!"}},{"$f":"V29/V32","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[{"$f":"IF(D$17<=0,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(D$20<=G27,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},0,null,5,0,null,5,null,"Quarterly Ratio (%)",null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I16*28,\"mmmm\"),TEXT(N15*28,\"mmmm\"))","v":"January"},null,null,"Commercial 4/5 Rated Loans ($)",null,null,null,"Sum of ","1260-130-20"]
[{"$f":"IF(AND(D$17>C28,D$17<=D28),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(AND(D$20>F28,D$20<=G28),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"C27","v":0},2.3999999999999998E-3,4,{"$f":"F27","v":0},8.3999999999999995E-3,4,null,null,null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I19*28,\"mmmm\"),TEXT(N18*28,\"mmmm\"))","v":"January"},null,null,null,null,null,null,null,"1260-140-20"]
[{"$f":"IF(AND(D$17>C29,D$17<=D29),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(AND(D$20>F29,D$20<=G29),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"D28","v":2.3999999999999998E-3},4.7999999999999996E-3,3,{"$f":"G28","v":8.3999999999999995E-3},1.6899999999999998E-2,3,null,null,null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I22*28,\"mmmm\"),TEXT(N21*28,\"mmmm\"))","v":"January"},null,null,null,null,null,null,null,null,{"$f":"SUM(U27:U28)","v":0}]
[{"$f":"IF(AND(D$17>C30,D$17<=D30),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(AND(D$20>F30,D$20<=G30),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"D29","v":4.7999999999999996E-3},6.6E-3,2,{"$f":"G29","v":1.6899999999999998E-2},2.1999999999999999E-2,2,null,null,null,{"$f":"IF(Instructions!$G$45=\"January\",TEXT(I25*28,\"mmmm\"),TEXT(N24*28,\"mmmm\"))","v":"January"},null,null,"Total Loans (Personal and Commercial Loans and Leases) ($)",null,null,null,"Sum of","1000-200-10"]
[{"$f":"IF(AND(D$17>C31,D$17<=D31),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(AND(D$20>F31,D$20<=G31),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},6.6E-3,8.3999999999999995E-3,1,{"$f":"G30","v":2.1999999999999999E-2},2.7099999999999999E-2,1,null,"Year Average",null,{"$f":"AVERAGE($M$27:$M$30)","v":{"$error":"#DIV/0!"}},null,null,null,null,null,null,null,"1000-210-10"]
[{"$f":"IF(D$17>C32,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(D$20>F32,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},8.3999999999999995E-3,"& above",0,{"$f":"G31","v":2.7099999999999999E-2},"& above",0,8,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(U30:U31)","v":0}]
[]
[]
[null,null,"Classification:  Public"]

{"sheet":"Input - Earnings","index":3,"n_rows":44,"n_cols":29,"rows_kept":44,"cut":false,"hidden":false,"merged":[[54,22,54,23],[55,22,55,23],[45,22,45,23],[46,22,46,23],[47,22,47,23],[48,22,48,23],[49,22,49,23],[50,22,50,23],[51,22,51,23],[52,22,52,23],[53,22,53,23],[39,22,39,23],[40,18,40,20],[40,22,40,23],[41,22,41,23],[42,22,42,23],[43,22,43,23],[38,18,38,19],[39,18,39,19],[43,19,43,20],[43,17,43,18],[47,19,47,20],[48,19,48,20],[15,12,15,13],[35,18,35,19],[36,18,36,19],[37,18,37,19],[11,22,11,23],[13,22,13,23],[15,22,15,23],[17,22,17,23],[12,22,12,23],[16,22,16,23],[18,22,18,23],[20,22,20,23],[22,22,22,23],[24,22,24,23],[30,22,30,23],[32,22,32,23],[33,22,33,23],[34,22,34,23],[35,22,35,23],[36,22,36,23],[37,22,37,23],[38,22,38,23],[47,17,47,18],[48,17,48,18],[44,15,44,16],[45,15,45,16],[46,15,46,16],[47,15,47,16],[48,15,48,16],[49,15,49,16],[45,17,45,18],[44,17,44,18],[46,19,46,20],[43,15,43,16],[46,17,46,18],[45,19,45,20],[44,19,44,20],[28,18,28,20],[42,17,42,18],[42,19,42,20],[42,15,42,16],[55,15,55,16],[55,17,55,18],[54,17,54,18],[54,19,54,20],[49,17,49,18],[50,17,50,18],[51,17,51,18],[52,17,52,18],[53,17,53,18],[53,19,53,20],[49,19,49,20],[50,19,50,20],[51,19,51,20],[54,15,54,16],[50,15,50,16],[51,15,51,16],[52,15,52,16],[53,15,53,16],[55,19,55,20],[52,19,52,20],[19,2,20,2],[19,3,20,4],[19,5,20,5],[19,6,20,7],[14,18,14,19],[30,18,30,19],[15,18,15,19],[19,22,19,23],[23,2,23,4],[23,5,23,7],[24,4,25,4],[24,7,25,7],[28,12,28,13],[27,12,27,13],[16,2,17,2],[16,3,17,4],[16,5,17,5],[21,22,21,23],[23,22,23,23],[18,2,18,7],[15,2,15,7],[16,6,17,7]]}
[]
[null,null,"Input data in cells highlighted YELLOW"]
[]
[null,null,{"$f":"\"For Assessment Year\" & \" \" & Instructions!$G$43","v":"For Assessment Year 2023"}]
[]
[]
[]
[]
[]
[]
[null,null,"*Choose one of two input methods: Calculated values as input or Individual data as input",null,null,null,null,null,null,"Earnings - Calculated Values",null,null,null,null,null,"Earnings - Individual Data"]
[null,null,"Calculated values as input: input estimates for monthly ratios",null,null,null,null,null,null,"Net Operating Income (excl. CUDIC Assessments) to Average Assets",null,null,null,null,null,"Net Operating Income (excl. CUDIC Assessments) to Average Assets"]
[null,null,"Individual data as input: input FSR line items individually for each month to calculate monthly ratios",null,null,null,null,null,null,"Net Operating Income/Loss ($)",null,null,"3000-550-10",null,null,"Net Operating Income/Loss ($)",null,null,"3000-550-10"]
[null,null,null,null,null,null,null,null,null,"CUDIC Assessments ($)",null,null,null,null,null,"CUDIC Assessments ($)"]
[null,null,"Earnings Risk Scoring",null,null,null,null,null,null,"Average Assets (13 months) ($)",null,null,"1000-500-10",null,1,"Average Assets (13 months) ($)",null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O27*28,\"mmmm\"),TEXT(V26*28,\"mmmm\")) & \" \" & Instructions!$G$43-2","v":"1000-500-10 for September 2021"},null,null,10]
[null,null,"Net Operating Income (excl. CUDIC Assessments) to Average Assets",null,null,null,null,null,null,"Year Ratio",null,null,{"$f":"(N13+N14)/N15","v":{"$error":"#DIV/0!"}},null,2,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O15*28,\"mmmm\"),TEXT(V27*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", Instructions!$G$43-1, Instructions!$G$43-2))","v":"1000-500-10 for October 2021"},null,null,11]
[null,null,"Ratio",{"$f":"IF(AND(ISNUMBER($M$16),ISERR($S$29)),$M$16,$S$29)","v":{"$error":"#DIV/0!"}},null,"Score",{"$f":"VLOOKUP(\"Y\",A27:E32,5,FALSE)","v":{"$error":"#N/A"}},null,null,"Volatility of Net Operating Income to Average Assets",null,null,null,null,3,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O16*28,\"mmmm\"),TEXT(V16*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", Instructions!$G$43-1, Instructions!$G$43-2))","v":"1000-500-10 for November 2021"},null,null,12]
[null,null,null,null,null,null,null,null,null,"Net Operating Income/Loss ($)",null,null,{"$f":"\"3000-550-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I19","v":"3000-550-10 for 2022"},null,4,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O17*28,\"mmmm\"),TEXT(V17*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", Instructions!$G$43-1, Instructions!$G$43-2))","v":"1000-500-10 for December 2021"},null,null,1]
[null,null,"Volatility of Net Operating Income to Average Assets",null,null,null,null,null,1,null,null,null,{"$f":"\"3000-550-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I20","v":"3000-550-10 for 2021"},null,5,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O18*28,\"mmmm\"),TEXT(V18*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", Instructions!$G$43-1, Instructions!$G$43-1))","v":"1000-500-10 for January 2022"},null,null,2]
[null,null,"Ratio",{"$f":"IF(AND(ISNUMBER(M28),ISERR(S41)),M28,S41)","v":{"$error":"#DIV/0!"}},null,"Score",{"$f":"VLOOKUP(\"Y\",B27:H32,7,FALSE)","v":{"$error":"#N/A"}},null,2,null,null,null,{"$f":"\"3000-550-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I21","v":"3000-550-10 for 2020"},null,6,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O19*28,\"mmmm\"),TEXT(V19*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", Instructions!$G$43-1, Instructions!$G$43-1))","v":"1000-500-10 for February 2022"},null,null,3]
[null,null,null,null,null,null,null,null,3,null,null,null,{"$f":"\"3000-550-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I22","v":"3000-550-10 for 2019"},null,7,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O20*28,\"mmmm\"),TEXT(V20*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", Instructions!$G$43-1, Instructions!$G$43-1))","v":"1000-500-10 for March 2022"},null,null,4]
[null,null,null,null,null,null,null,null,4,null,null,null,{"$f":"\"3000-550-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I23","v":"3000-550-10 for 2018"},null,8,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O21*28,\"mmmm\"),TEXT(V21*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", Instructions!$G$43-1, Instructions!$G$43-1))","v":"1000-500-10 for April 2022"},null,null,5]
[null,null,null,null,null,null,null,null,5,"Average Assets (13 months) ($)",null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I19","v":"1000-500-10 for 2022"},null,9,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O22*28,\"mmmm\"),TEXT(V22*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", Instructions!$G$43-1, Instructions!$G$43-1))","v":"1000-500-10 for May 2022"},null,null,6]
[null,null,"NOI to Avg Assets Proposed Ranges",null,null,"NOI σ to Avg Assets Proposed Ranges",null,null,null,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I20","v":"1000-500-10 for 2021"},null,10,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O23*28,\"mmmm\"),TEXT(V23*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", Instructions!$G$43-1, Instructions!$G$43-1))","v":"1000-500-10 for June 2022"},null,null,7]
[null,null,"From","To","Score","From","To","Score",null,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I21","v":"1000-500-10 for 2020"},null,11,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O24*28,\"mmmm\"),TEXT(V24*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", Instructions!$G$43-1, Instructions!$G$43-1))","v":"1000-500-10 for July 2022"},null,null,8]
[null,null,"≥","<",null,">","≤",null,null,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I22","v":"1000-500-10 for 2019"},null,12,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O25*28,\"mmmm\"),TEXT(V25*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", Instructions!$G$43-1, Instructions!$G$43-1))","v":"1000-500-10 for August 2022"},null,null,9]
[{"$f":"IF(D$17>=C27,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(D$20<=G27,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},8.8999999999999999E-3,"& above",5,"Below",1E-4,5,null,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I23","v":"1000-500-10 for 2018"},null,12,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O26*28,\"mmmm\"),TEXT(V26*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", Instructions!$G$43-1, Instructions!$G$43-1))","v":"1000-500-10 for September 2022"},null,null,10]
[{"$f":"IF(AND(D$17>=C28,D$17<D28),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(AND(D$20>F28,D$20<=G28),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},7.4999999999999997E-3,8.8999999999999999E-3,4,{"$f":"G27","v":1E-4},1.5E-3,4,null,"Year Ratio",null,null,{"$f":"_xlfn.STDEV.S(N18/N23,N19/N24,N20/N25,N21/N26,N22/N27)","v":{"$error":"#DIV/0!"}},null,null,null,null,null,null,null,{"$f":"AVERAGE(U15:U27)","v":{"$error":"#DIV/0!"}}]
[{"$f":"IF(AND(D$17>=C29,D$17<D29),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(AND(D$20>F29,D$20<=G29),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},4.5999999999999999E-3,{"$f":"C28","v":7.4999999999999997E-3},3,{"$f":"G28","v":1.5E-3},4.4999999999999997E-3,3,null,null,null,null,null,null,null,"Year Ratio",null,null,{"$f":"(U13+U14)/U28","v":{"$error":"#DIV/0!"}}]
[{"$f":"IF(AND(D$17>=C30,D$17<D30),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(AND(D$20>F30,D$20<=G30),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},3.2000000000000002E-3,4.5999999999999999E-3,2,4.4999999999999997E-3,6.0000000000000001E-3,2,null,null,null,null,null,null,null,"Volatility of Net Operating Income to Average Assets",null,null,null,null,null,null,"Input table for Average Assets ($)",null,1,2,3,4,5]
[{"$f":"IF(AND(D$17>=C31,D$17<D31),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(AND(D$20>F31,D$20<=G31),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},1.6999999999999999E-3,3.2000000000000002E-3,1,6.0000000000000001E-3,7.4000000000000003E-3,1,null,null,null,null,null,null,null,"Net Operating Income/Loss ($)",null,null,{"$f":"\"3000-550-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I19","v":"3000-550-10 for 2022"},null,null,null,"Monthly data",null,{"$f":"Instructions!$G$43-'Input - Earnings'!Y30:Y30","v":2022},{"$f":"Instructions!$G$43-'Input - Earnings'!Z30:Z30","v":2021},{"$f":"Instructions!$G$43-'Input - Earnings'!AA30:AA30","v":2020},{"$f":"Instructions!$G$43-'Input - Earnings'!AB30:AB30","v":2019},{"$f":"Instructions!$G$43-'Input - Earnings'!AC30:AC30","v":2018}]
[{"$f":"IF(D$17<D32,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(D$20>F32,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},"Below",1.6999999999999999E-3,0,7.4000000000000003E-3,"& above",0,null,null,null,null,null,null,null,null,null,null,{"$f":"\"3000-550-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I20","v":"3000-550-10 for 2021"},null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O27*28,\"mmmm\"),TEXT(V26*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", \"Prior year\", \"Prior Year\"))","v":"1000-500-10 for September Prior Year"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"3000-550-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I21","v":"3000-550-10 for 2020"},null,null,null,{"$f":"\"1000-500-10\"&\" \"&\"for\"&\" \"&IF(Instructions!$G$45=\"January\",TEXT(O15*28,\"mmmm\"),TEXT(V27*28,\"mmmm\"))&\" \"&(IF(Instructions!$G$45=\"January\",\"Current Year\", \"Prior Year\"))","v":"1000-500-10 for October Prior Year"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"3000-550-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I22","v":"3000-550-10 for 2019"},null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O16*28,\"mmmm\"),TEXT(V16*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\",\"Current Year\", \"Prior Year\"))","v":"1000-500-10 for November Prior Year"}]
[null,null,"Classification:  Public",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"3000-550-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I23","v":"3000-550-10 for 2018"},null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O17*28,\"mmmm\"),TEXT(V17*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", \"Current Year\", \"Prior Year\"))","v":"1000-500-10 for December Prior Year"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Average Assets (13 months) ($)",null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I19","v":"1000-500-10 for 2022"},null,{"$f":"AVERAGE(Y32:Y44)","v":{"$error":"#DIV/0!"}},null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O18*28,\"mmmm\"),TEXT(V18*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", \"Current Year\", \"Current Year\"))","v":"1000-500-10 for January Current Year"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I20","v":"1000-500-10 for 2021"},null,{"$f":"AVERAGE(Z32:Z44)","v":{"$error":"#DIV/0!"}},null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O19*28,\"mmmm\"),TEXT(V19*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", \"Current Year\", \"Current Year\"))","v":"1000-500-10 for February Current Year"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I21","v":"1000-500-10 for 2020"},null,{"$f":"AVERAGE(AA32:AA44)","v":{"$error":"#DIV/0!"}},null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O20*28,\"mmmm\"),TEXT(V20*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", \"Current Year\", \"Current Year\"))","v":"1000-500-10 for March Current Year"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I22","v":"1000-500-10 for 2019"},null,{"$f":"AVERAGE(AB32:AB44)","v":{"$error":"#DIV/0!"}},null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O21*28,\"mmmm\"),TEXT(V21*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", \"Current Year\", \"Current Year\"))","v":"1000-500-10 for April Current Year"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & Instructions!$G$43-I23","v":"1000-500-10 for 2018"},null,{"$f":"AVERAGE(AC32:AC44)","v":{"$error":"#DIV/0!"}},null,{"$f":"\"1000-500-10\"&\" \"&\"for\"&\" \"&IF(Instructions!$G$45=\"January\",TEXT(O22*28,\"mmmm\"),TEXT(V22*28,\"mmmm\"))&\" \"&(IF(Instructions!$G$45=\"January\",\"Current Year\", \"Current Year\"))","v":"1000-500-10 for May Current Year"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Year Ratio",null,null,{"$f":"_xlfn.STDEV.S(U31/U36,U32/U37,U33/U38,U34/U39,U35/U40)","v":{"$error":"#DIV/0!"}},null,null,null,{"$f":"\"1000-500-10\"&\" \"&\"for\"&\" \"&IF(Instructions!$G$45=\"January\",TEXT(O23*28,\"mmmm\"),TEXT(V23*28,\"mmmm\"))&\" \"&(IF(Instructions!$G$45=\"January\",\"Current Year\", \"Current Year\"))","v":"1000-500-10 for June Current Year"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O24*28,\"mmmm\"),TEXT(V24*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", \"Current Year\", \"Current Year\"))","v":"1000-500-10 for July Current Year"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O25*28,\"mmmm\"),TEXT(V25*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", \"Current Year\", \"Current Year\"))","v":"1000-500-10 for August Current Year"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"1000-500-10\" & \" \" & \"for\" & \" \" & IF(Instructions!$G$45=\"January\",TEXT(O26*28,\"mmmm\"),TEXT(V26*28,\"mmmm\")) & \" \" & (IF(Instructions!$G$45=\"January\", \"Current Year\", \"Current Year\"))","v":"1000-500-10 for September Current Year"}]

{"sheet":"Input - Liquidity","index":4,"n_rows":42,"n_cols":20,"rows_kept":42,"cut":false,"hidden":false,"merged":[[7,14,7,19],[19,11,19,12],[15,11,15,12],[16,2,17,2],[16,3,17,4],[16,5,17,5],[16,6,17,7],[20,2,20,4],[20,5,20,7],[13,6,14,7],[10,2,11,2],[10,3,11,4],[10,5,11,5],[10,6,11,7],[13,2,14,2],[13,3,14,4],[13,5,14,5],[30,2,30,4],[31,4,32,4],[21,2,21,4],[22,4,23,4],[21,5,21,7],[22,7,23,7]]}
[" "]
[null,null,"Input data in cells highlighted YELLOW"]
[]
[null,null,{"$f":"\"For Assessment Year\" & \" \" & Instructions!$G$43","v":"For Assessment Year 2023"}]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,"Liquidity & Funding",null,null,null,null,"LCR"]
[null,null,"Liquidity & Funding Risk Scoring",null,null,null,null,null,null,"Liquidity Coverage Ratio (LCR)",null,null,null,null,"Quarterly Data","Q4 - 2023","Q3 - 2023","Q2 - 2023","Q1 - 2023","Avg"]
[null,null,"Liquidity Coverage Ratio (LCR)",null,null,null,null,null,null,"LCR ",null,"5100-150-20",{"$f":"T10","v":{"$error":"#DIV/0!"}},null,"5100-150-20",null,null,null,null,{"$f":"AVERAGE(P10:S10)","v":{"$error":"#DIV/0!"}}]
[null,null,"Ratio",{"$f":"M10","v":{"$error":"#DIV/0!"}},null,"Score",{"$f":"VLOOKUP(\"Y\",A25:E30,5,FALSE)","v":{"$error":"#N/A"}}]
[]
[null,null,"Securitizations to Total Deposits",null,null,null,null,null,null,"Securitizations to Total Deposits"]
[null,null,"Ratio",{"$f":"L16","v":{"$error":"#DIV/0!"}},null,"Score",{"$f":"VLOOKUP(\"Y\",B25:H30,7,FALSE)","v":{"$error":"#N/A"}},null,null,"Securitizations ($)",null,"2100-200-10 + 2100-210-10"]
[null,null,null,null,null,null,null,null,null,"Total Deposits ($)",null,"2000-250-10"]
[null,null,"Agent Deposits to Total Deposits",null,null,null,null,null,null,"Year Ratio",null,{"$f":"M14/M15","v":{"$error":"#DIV/0!"}}]
[null,null,"Ratio",{"$f":"L20","v":{"$error":"#DIV/0!"}},null,"Score",{"$f":"VLOOKUP(\"Y\",B34:E39,4,FALSE)","v":{"$error":"#N/A"}},null,null,"Agent Deposits to Total Deposits"]
[null,null,null,null,null,null,null,null,null,"Agent Deposits ($)",null,"2210-110-10",null,"Note"]
[null,null,null,null,null,null,null,null,null,"Total Deposits ($)",null,"2000-250-10"]
[null,null,null,null,null,null,null,null,null,"Year Ratio",null,{"$f":"M18/M19","v":{"$error":"#DIV/0!"}}]
[]
[null,null,"LCR Proposed Ranges",null,null,"Securitization Proposed Ranges"]
[null,null,"From","To","Score","From","To","Score"]
[null,null,"≥","<",null,">","≤"]
[{"$f":"IF(D$11>=C25,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(D$14<=G25,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},2,"& above",5,0,6.4699999999999994E-2,5,null,"Note"]
[{"$f":"IF(AND(D$11>=C26,D$11<D26),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(AND(D$14>F26,D$14<=G26),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},1.75,{"$f":"C25","v":2},4,{"$f":"G25","v":6.4699999999999994E-2},7.5899999999999995E-2,4,null,"For the purposes of the Agent Deposits to Total Deposits risk metric calculation, non-redeemable deposits with maturity dates of 12 months or greater will be subtracted from Line 2210-110-10. "]
[{"$f":"IF(AND(D$11>=C27,D$11<D27),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(AND(D$14>F27,D$14<=G27),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},1.5,{"$f":"C26","v":1.75},3,{"$f":"G26","v":7.5899999999999995E-2},9.0700000000000003E-2,3,null,"This information will be provided by the Credit Unions separate from the FSR data. "]
[{"$f":"IF(AND(D$11>=C28,D$11<D28),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"},1.25,{"$f":"C27","v":1.5},2,{"$f":"G27","v":9.0700000000000003E-2},0.1007,2]
[{"$f":"IF(AND(D$11>=C29,D$11<D29),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"},1,{"$f":"C28","v":1.25},1,0.1007,0.105,1]
[{"$f":"IF(D$11<D30,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(D$14>F30,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},"Below",{"$f":"C29","v":1},0,0.105,"& above",0]
[null,null,"Agent Deposits Proposed Ranges"]
[null,null,"From","To","Score"]
[null,null,">","≤"]
[null,{"$f":"IF(D$17<=D34,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},0,5.1499999999999997E-2,5]
[null,{"$f":"IF(AND(D$17>C35,D$17<=D35),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"D34","v":5.1499999999999997E-2},6.4799999999999996E-2,4]
[null,{"$f":"IF(AND(D$17>C36,D$17<=D36),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"D35","v":6.4799999999999996E-2},8.1199999999999994E-2,3]
[null,{"$f":"IF(AND(D$17>C37,D$17<=D37),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"D36","v":8.1199999999999994E-2},9.7500000000000003E-2,2]
[null,{"$f":"IF(AND(D$17>C38,D$17<=D38),\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},9.7500000000000003E-2,0.105,1]
[null,{"$f":"IF(D$17>C39,\"Y\",\"N\")","v":{"$error":"#DIV/0!"}},{"$f":"D38","v":0.105},"& above",0]
[]
[]
[null,null,"Classification:  Public"]

{"sheet":"Input - Qualitative","index":5,"n_rows":44,"n_cols":20,"rows_kept":44,"cut":false,"hidden":false,"merged":[[31,1,31,5],[13,2,15,3],[10,8,11,10],[10,11,11,11],[13,1,15,1],[13,4,15,4],[13,5,15,6],[24,1,24,6],[19,1,19,2],[25,1,25,5],[18,1,18,3],[18,4,18,6],[10,1,11,1],[10,2,11,3],[10,4,11,4],[10,5,11,6]]}
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"B27","v":"0 - Normal"}]
[null,"Click on input cells highlighted YELLOW and select from drop-down list",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"B28","v":"1 - Elevated"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"2 - Early Warning"]
[null,{"$f":"\"For Assessment Year\" & \" \" & Instructions!$G$43","v":"For Assessment Year 2023"},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"3 - Risk to Financial Viability or Solvency"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"B31","v":"4 - Future Financial Viability in Serious Doubt"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"B32","v":"5 - Non-Viability Insolvency Imminent"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Low"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Moderate"]
[null,"Qualitative Risk Scoring",null,null,"*",null,null,null,"Qualitative",null,null,null,null,null,null,null,null,null,null,"Above Average"]
[null,"Composite Risk Rating (CRR)",null,null,null,null,null,null,"Composite Risk Rating",null,null,null,null,null,null,null,null,null,null,"High"]
[null,"CRR Rating",{"$f":"IF(L10=0,\" \",L10)","v":" "},null,"Score",{"$f":"VLOOKUP(C11,B21:D24,3,FALSE)","v":{"$error":"#N/A"}},null,null,"Intervention Stage Rating"]
[]
[null,"Intervention Stage Rating (ISR)"]
[null,"ISR Rating",{"$f":"IF(L11=0,\" \",L11)","v":" "},null,"Score",{"$f":"VLOOKUP(C14,B27:G32,6,FALSE)","v":{"$error":"#N/A"}}]
[]
[]
[]
[]
[null,"CRR Ranges"]
[null,"Rating",null,"Score"]
[null,"Low",null,35]
[null,"Moderate",null,30]
[null,"Above Average",null,18]
[null,"High",null,0]
[null,"ISR Ranges"]
[null,"Rating",null,null,null,null,"Score"]
[null,"0 - Normal",null,null,null,null,35]
[null,"1 - Elevated",null,null,null,null,30]
[null,"2 - Early Warning",null,null,null,null,20]
[null,"3 - Risk to Financial Viability or Solvency",null,null,null,null,10]
[null,"4 - Future Financial Viability in Serious Doubt",null,null,null,null,0]
[null,"5 - Non-Viability Insolvency Imminent",null,null,null,null,0]
[]
[null,"*Enhancements to the BCFSA Supervisory Risk Assessment will be reflected in the Qualitative Risk Metrics upon implementation "]
[]
[null,"Classification:  Public",null,null,null,null,null,null,"Low",null,"0 - Normal"]
[null,null,null,null,null,null,null,null,"Moderate",null,"1 - Early Warning"]
[null,null,null,null,null,null,null,null,"Above_Average ",null,"2 - Risk to financial viability or solvency"]
[null,null,null,null,null,null,null,null,"High",null,"3 - Future financial viability in serious doubt"]
[null,null,null,null,null,null,null,null,null,null,"4 - Non-viability/insolvency imminent"]
[null,null,null,null,null,null,null,null,{"$f":"SUBSTITUTE(I36,\"_\",\" \")","v":"Low"},35]
[null,null,null,null,null,null,null,null,{"$f":"SUBSTITUTE(I37,\"_\",\" \")","v":"Moderate"},30]
[null,null,null,null,null,null,null,null,"Above Average ",18]
[null,null,null,null,null,null,null,null,"High",0]

{"sheet":"Summary","index":6,"n_rows":50,"n_cols":19,"rows_kept":50,"cut":false,"hidden":false,"merged":[[21,15,21,16],[30,7,30,11],[30,13,31,13],[25,15,25,16],[26,10,26,11],[26,15,26,16],[24,15,24,16],[24,10,24,11],[25,10,25,11],[30,15,31,16],[31,7,31,11],[27,15,27,16],[16,15,16,16],[19,10,19,11],[19,15,19,16],[20,10,20,11],[20,15,20,16],[15,10,15,11],[15,15,15,16],[5,0,6,5],[5,7,6,8],[5,10,6,11],[5,13,6,13],[5,15,6,16],[9,10,9,11],[9,15,9,16],[10,10,10,11],[10,15,10,16],[11,15,11,16],[14,10,14,11],[14,15,14,16],[5,14,6,14],[43,0,46,13],[37,15,37,18],[38,15,38,17],[33,7,33,12],[33,15,33,16],[41,3,41,8],[37,3,37,12],[38,3,38,8],[39,3,39,8],[38,10,38,12],[39,10,39,12],[40,10,40,12],[41,10,41,12]]}
[]
[]
[]
["BC Financial Services Authority"]
["DPS Assessment Calculation Particulars (Estimated)"]
["SUMMARY TABLE",null,null,null,null,null,null,"Range",null,null,"Actual Data",null,null,"Max. Points",null,"Score"]
[]
[null,null,null,null,null,null,null,"Min pts","Max pts"]
["Capital"]
["Capital Adequacy Ratio",null,null,null,null,null,null,{"$f":"'Input - Capital'!D41","v":0.08},{"$f":"'Input - Capital'!C21","v":0.13},null,{"$f":"'Input - Capital'!$D$11","v":{"$error":"#DIV/0!"}},null,null,20,null,{"$f":"'Input - Capital'!G11","v":{"$error":"#N/A"}}]
["Leverage Ratio",null,null,null,null,null,null,{"$f":"'Input - Capital'!G31","v":0.03},{"$f":"'Input - Capital'!F21","v":7.0000000000000007E-2},null,{"$f":"'Input - Capital'!$D$14","v":{"$error":"#DIV/0!"}},null,null,10,null,{"$f":"'Input - Capital'!G14","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,30,null,{"$f":"SUM($P$10:$Q$11)","v":{"$error":"#N/A"}}]
[]
["Assets Quality"]
["Non-Performing Loans to Total Loans",null,null,null,null,null,null,{"$f":"'Input - Asset Quality'!C32","v":8.3999999999999995E-3},{"$f":"'Input - Asset Quality'!C27","v":0},null,{"$f":"'Input - Asset Quality'!D17","v":{"$error":"#DIV/0!"}},null,null,{"$f":"'Input - Asset Quality'!$E$27","v":5},null,{"$f":"'Input - Asset Quality'!G17","v":{"$error":"#N/A"}}]
["Commercial 4/5 Rated Loans to Total Loans",null,null,null,null,null,null,{"$f":"'Input - Asset Quality'!F32","v":2.7099999999999999E-2},{"$f":"'Input - Asset Quality'!F27","v":0},null,{"$f":"'Input - Asset Quality'!D20","v":{"$error":"#DIV/0!"}},null,null,{"$f":"'Input - Asset Quality'!H27","v":5},null,{"$f":"'Input - Asset Quality'!G20","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,10,null,{"$f":"SUM($P$15:$Q$16)","v":{"$error":"#N/A"}}]
[]
["Earnings"]
["Net Operating Income (excluding CUDIC Assessments) to Average Assets",null,null,null,null,null,null,{"$f":"'Input - Earnings'!D32","v":1.6999999999999999E-3},{"$f":"'Input - Earnings'!C27","v":8.8999999999999999E-3},null,{"$f":"'Input - Earnings'!D17","v":{"$error":"#DIV/0!"}},null,null,{"$f":"'Input - Earnings'!$E$27","v":5},null,{"$f":"'Input - Earnings'!G17","v":{"$error":"#N/A"}}]
["Volatility of Net Operating Income to Average Assets",null,null,null,null,null,null,{"$f":"'Input - Earnings'!F32","v":7.4000000000000003E-3},{"$f":"'Input - Earnings'!G27","v":1E-4},null,{"$f":"'Input - Earnings'!D20","v":{"$error":"#DIV/0!"}},null,null,{"$f":"'Input - Earnings'!$H$27","v":5},null,{"$f":"'Input - Earnings'!G20","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,10,null,{"$f":"SUM($P$20:$Q$21)","v":{"$error":"#N/A"}}]
[]
["Liquidity & Funding"]
["Liquidity Coverage Ratio",null,null,null,null,null,null,{"$f":"'Input - Liquidity'!D30","v":1},{"$f":"'Input - Liquidity'!D26","v":2},null,{"$f":"'Input - Liquidity'!D11","v":{"$error":"#DIV/0!"}},null,null,5,null,{"$f":"'Input - Liquidity'!G11","v":{"$error":"#N/A"}}]
["Agent Deposits to Total Deposits",null,null,null,null,null,null,{"$f":"'Input - Liquidity'!$C$39","v":0.105},{"$f":"'Input - Liquidity'!$C$35","v":5.1499999999999997E-2},null,{"$f":"'Input - Liquidity'!$D$17","v":{"$error":"#DIV/0!"}},null,null,{"$f":"'Input - Liquidity'!$E$34","v":5},null,{"$f":"'Input - Liquidity'!G17","v":{"$error":"#N/A"}}]
["Securitizations to Total Deposits",null,null,null,null,null,null,{"$f":"'Input - Liquidity'!$F$30","v":0.105},{"$f":"'Input - Liquidity'!$F$26","v":6.4699999999999994E-2},null,{"$f":"'Input - Liquidity'!$D$14","v":{"$error":"#DIV/0!"}},null,null,{"$f":"'Input - Liquidity'!$H$25","v":5},null,{"$f":"'Input - Liquidity'!G14","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,15,null,{"$f":"SUM($P$25:$Q$27)","v":{"$error":"#N/A"}}]
[]
["Qualitative (Lower of CRR and ISR)"]
["Composite Risk Rating (CRR)",null,null,null,null,null,null,{"$f":"'Input - Qualitative'!C11","v":" "},null,null,null,null,null,35,null,{"$f":"MIN('Input - Qualitative'!F11,'Input - Qualitative'!F14)","v":{"$error":"#N/A"}}]
["Intervention Stage Rating (ISR)",null,null,null,null,null,null,{"$f":"'Input - Qualitative'!C14","v":" "}]
[]
[null,null,null,null,null,null,null,"Estimated Total DPS Assessment Score",null,null,null,null,null,100,null,{"$f":"SUM($P$12,$P$17,$P$22,$P$28,$P$31)","v":{"$error":"#N/A"}}]
[]
[]
[]
[null,null,null,"Assessment Calculation Table",null,null,null,null,null,null,null,null,null,null,null,"Scoring Table"]
[null,null,null,"Total Assessment Rate (TAR) (bps)",null,null,null,null,null,null,{"$f":"IF(P34>=R40,S40, IF(P34>=R41,S41,IF(P34>=R42,S42,IF(P34>=R43,S43,IF(P34<=P44,S44,0)))))","v":{"$error":"#N/A"}},null,null,null,null,"Assessment Score",null,null,"TAR (bps)"]
[null,null,null,"Year-End Insured Deposits",null,null,null,null,null,null,{"$f":"Instructions!$G$46","v":0},null,null,null,null,100,"to",85,{"$f":"0.85*Instructions!$G$44","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,84,"to",75,{"$f":"1*Instructions!$G$44","v":0}]
[null,null,null,"Estimated Premium",null,null,null,null,null,null,{"$f":"(K39/10000)*K40","v":{"$error":"#N/A"}},null,null,null,null,74,"to",65,{"$f":"1.2*Instructions!$G$44","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,64,"to",50,{"$f":"1.8*Instructions!$G$44","v":0}]
["Important Note\nThe results produced by this DPS Calculator are based on data inputted by the user and are intended solely for informational and discussion purposes only and are not, and may not be relied upon as final or approved results from the BC Financial Services Authority or the Credit Union Deposit Insurance Corporation of British Columbia.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,49,"to",0,{"$f":"2.5*Instructions!$G$44","v":0}]
[]
[]
[]
[]
[]
["Classification:  Public"]