{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["RC Worksheet","Instructions","Tips & Tricks"],"not_rendered":{"charts":0,"images":10,"macros":1,"external_links":1,"defined_names":70,"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":"RC Worksheet","index":0,"n_rows":41,"n_cols":9,"rows_kept":41,"cut":false,"hidden":false,"merged":[[40,1,40,3],[38,6,39,6],[36,6,36,7],[37,6,37,7],[38,8,39,8],[37,1,37,3],[38,1,38,3],[26,3,33,3],[29,6,30,6],[29,8,30,8],[31,2,32,2],[32,6,32,7],[33,6,33,7],[34,6,34,7],[35,6,35,7],[39,1,39,3],[15,1,23,1],[15,6,16,8],[16,3,23,3],[21,2,22,2],[19,6,19,7],[20,6,20,7],[21,6,21,7],[22,6,22,7],[18,6,18,7],[23,6,24,7],[23,8,24,8],[26,6,27,8],[25,1,33,1],[9,3,9,4],[9,6,11,8],[10,3,10,4],[11,3,11,4],[12,1,12,8],[4,1,4,8],[5,1,5,2],[5,3,5,4],[5,6,7,8],[6,3,6,4],[7,3,7,4],[6,1,7,2],[1,1,1,2],[1,3,1,5],[1,6,1,8],[2,1,2,2],[2,3,2,5],[2,6,2,8]]}
[]
[null,"Case Name:",null,"",null,null,"Date:"]
[null,"Case Number:",null,null,null,null,{"$f":"TODAY()","v":"2015-07-01"}]
[]
[]
[null,"Name",null,"Self Attestation (Monthly)",null,{"$f":"Total_Income","v":0},{"$f":"IF(AND(F8=0,F7<=0),\"NO\",IF(OR(F8<20.00001%,F6>F7,F8=20),\"RC, use $\"&D36,\"Check The Work Number\"))","v":"NO"}]
[null,"",null,"KDOL (Monthly)",null,{"$f":"RC_KDOL_5","v":0}]
[null,null,null,"Compatibility %",null,{"$f":"IFERROR(SUM(F7,-F6)/F7,0)","v":0}]
[]
[null,null,null,"Self Attestation (Monthly)",null,{"$f":"Total_Income","v":0},{"$f":"IF(RC_TWN_14<>\"No Frequency\",IF(AND(F12=0,F11 <=0 ),\"NO\",IF(OR(F12<20.00001%,F10>F11,F12=20),\"RC, use $\"&D36,\"Not RC,  $\"& F11 & \" = Tier 3 amount\")),\"NO\")","v":"NO"}]
[null,null,null,"Work # (Monthly)",null,{"$f":"RC_TWN_13","v":0}]
[null,null,null,"Compatibility %",null,{"$f":"IFERROR(SUM(F11,-F10)/F11,0)","v":0}]
[]
[]
[]
[null,"Hourly Rate Calculator     Job 1","Hourly Rate of Pay","Monthly Amount",null,null,"KDOL Calculator\n(all entries in most recent qtr)"]
[null,null,null,{"$f":"+(((C17*C19)+((C17*1.5)*C21))*4.3)+C24","v":0}]
[null,null,"Hours Worked Per Wk",null,null,null,"Qtr Used:"]
[null,null,null,null,null,null,"Quarterly Amounts"]
[null,null,"Hours OT Worked Per Wk"]
[]
[null,null,"Monthly Amounts\n(Tips/Bonus/Etc.)"]
[]
[null,null,null,null,null,null,"Monthly Amount",null,{"$f":"ROUNDDOWN(SUM(G20:G23)/3,2)","v":0}]
[]
[null,"Hourly Rate Calculator     Job 2","Hourly Rate of Pay","Monthly Amount"]
[null,null,null,{"$f":"+(((C27*C29)+((C27*1.5)*C31))*4.3)+C34","v":0},null,null,"Work Number Calculator\n(last 30 days)"]
[null,null,"Hours Worked Per Wk"]
[null,null,null,null,null,null,{"$f":"TODAY()-30","v":"2015-06-01"},"to",{"$f":"TODAY()","v":"2015-07-01"}]
[null,null,"OT Worked Per Wk",null,null,null,"Frequency"]
[]
[null,null,"Monthly Amounts\n(Tips/Bonus/Etc.)",null,null,null,"Date ",null,"Amount"]
[]
[]
[null,null,null,"Total"]
[null,null,null,{"$f":"IF(D27<>\"\",D17 + D27,\"\")","v":0}]
[null,"Comments"]
[]
[null,null,null,null,null,null,"Monthly Amount",null,{"$f":"IF(Hidden_2<>\"\",ROUNDDOWN(Hidden_2,2),\"No Frequency\")","v":0}]
[]
[null,null,null,null,null,null,{"$f":"IF(COUNT(I32:I37)>0,AVERAGE(I32:I37),\"\")","v":""},null,{"$f":"IF(Hidden_1<>\"\",IF(RC_TWN_Frequency= \"Weekly\",PRODUCT(Hidden_1,4.3),IF(RC_TWN_Frequency= \"Bi-Weekly\",PRODUCT(Hidden_1,2.15),IF(RC_TWN_Frequency= \"Semi Monthly\",PRODUCT(Hidden_1,2),IF(RC_TWN_Frequency= \"Monthly\",PRODUCT(Hidden_1,1),\"\")))))","v":false}]

{"sheet":"Instructions","index":1,"n_rows":124,"n_cols":2,"rows_kept":124,"cut":false,"hidden":false,"merged":[[119,1,122,12],[123,1,123,12],[0,1,0,12],[42,1,42,2],[8,1,8,6],[14,1,14,12],[1,1,1,12],[2,1,2,12],[3,1,3,12],[4,1,4,12],[5,1,5,12],[7,1,7,12],[9,1,9,12],[10,1,10,12],[11,1,11,12],[12,1,12,12],[13,1,13,12],[47,1,47,12],[15,1,15,12],[16,1,16,12],[17,1,17,12],[29,1,29,12],[30,1,30,12],[31,1,31,12],[32,1,32,12],[43,1,43,12],[44,1,44,12],[45,1,45,12],[46,1,46,12],[97,1,97,12],[48,1,48,12],[60,1,60,12],[70,1,70,12],[72,1,72,12],[86,1,86,12],[87,1,87,12],[71,1,71,2],[49,1,49,9]]}
[null,"Reasonable Compatibility Tool - Instructions"]
[null,"Overview:  Reported income is considered Reasonably Compatible (RC) and verified, if the amount reported by the consumer is:"]
[null,"     1.)  Greater than the amount received in the data source (KDOL or the Work Number)"]
[null,"     2.)  Within 20% of the amount received in the data source (KDOL or the Work Number)"]
[null,"  "]
[null,"**Income is considered Reasonably Compatible if it is compatible with either interface.  Compatibility with both is not required.  "]
[]
[null,"General Instructions \nEnter the Case Name and Case Number at the top of the RC Tool.  \nThe date will auto-populate with today's date.  \nOne Tool is completed for each person with wages.  Enter this person's name in field:  B7.  "]
[null,"Reasonable Compatibility Calculation "]
[null,"Determining if the consumer’s self-attested (SA) income is reasonably compatible is at most a three step process.   "]
[null,"1.     Calculate the SA of income made by the consumer"]
[null,"2.      Calculate an income amount based on data found on KDOL and compare to the SA."]
[null,"3.      If not RC on step 2, calculate an income based on the data found on The Work Number and compare to the SA."]
[]
[null,"Step One:"]
[null,"Calculate the SA of income.  On the RC Tool in the left hand half of the lower section there are two sections where the SA can be entered.  They are labeled “Hourly Rate Calculator Job 1” and Hourly Rate Calculator Job 2”.  This allows the calculation of an SA for up to 2 jobs, to include overtime and monthly amounts for income from tips, bonuses, or other sources.    The total from this section will be reflected automatically in the upper section.  Note:  When a salaried amount is reported - enter this in the 'Monthly Amounts' field.  "]
[null,"Example:"]
[null,"Consumer reports they work 45 hours a week making $10 an hour at one job, and then works weekends waiting tables at a 2nd job, making $2.50 an hour, working 10 hours per week, and making $75 per week in tips."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"In this example, for Job 1:  Enter 40 hours in the “Hours worked Per Wk” section which calculates at the $10 hourly rate"]
[null,"Enter 5 hours of overtime in the “Hours OT Worked Per Wk” section.  This is calculated at 1.5 times hourly rate.  "]
[null,"For Job 2:  Enter 10 hours and the hourly rate of $2.50.  When entering tips, bonuses, etc, you must determine the monthly amount yourself.  In this example, tips of $75 weekly are reported, so you will convert this to a monthly amount by taking $75 per week, times 4.3 coversion factor.  This is a manual process.  Then enter the monthly amount of $322.50 into the calculator.    "]
[null,"As shown in the visual below, this amount is automatically entered into the RC Tool as the monthly Self-Attestation and is now used to make the comparisons to KDOL and if needed The Work Number.  "]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Step Two:"]
[null,"On the right half of the lower section is the “KDOL Calculator”.  "]
[null,"In order to complete this section, follow the following steps."]
[null,"1.   Check for income in the most recent full quarter in KDOL.   If income is found enter the amount for all jobs in this quarter for this consumer into the RC Tool.     There is no evaluation of this income, it is entered even if the job was reported to have ended, if it is a different employer than listed by the consumer, or any other factor.  All income found in this quarter is entered into the worksheet.  "]
[null,"Example:  On August 15th, the most recent full quarter would be 2nd quarter."]
[null,"2.  If there was no income in the most recent full quarter, then go back the 2nd most recent full quarter.  If income is found, enter the amount for all jobs in this quarter for the consumer into the RC Tool.  "]
[null,"Example:  On August 15th, the 2nd most recent full quarter would be 1st quarter."]
[null,"3.  Enter the quarter used in the Qtr Used for documentation purposes.  "]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"4. Check to see if RC standards have been met in the top section.  "]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"If the SA is reasonably compatible then use the consumer’s SA for the determination.  If not then you will need to go on to check The Work Number.  "]
[null,"Step Three:"]
[null,"Check the Work Number and see if there is information you can use.  If income is found on The Work Number you will obtain all pay dates and amounts from the most recent 30 days.  The calculator helps you find the last 30 days by displaying the date range based on today's date.  Enter the frequency of the pay checks, check dates and gross amounts.  Note: If less than 30 days of income is in The Work Number, use what is available to complete the RC test.    "]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"If the income is now found to be RC, then the consumer’s SA is used for the determination.  "]
[null,"See the example below."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"It is possible that the income will not be RC, but is verified via The Work Number in Tier 3.  The RC Tool will present to you the Monthly Amount that could be used in Tier 3.  If 30 days of income was available in The Work Number, you'll use Full Month Budgeting method and use the amount provided in the tool.  When less than 30 days income was available in The Work Number, you'll use the Partial Month Budgeting Method.  Compare the Monthly Amount in the tool to the client's self-attestation and use whichever is greater.  "]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Image the RC Tool \nAfter all earnings have been entered into the Reasonable Compatibilty Tool, image the worksheet to the Journaling Document Type using ImageNow.  "]
[]
[]
[]
[null,"Using the RESET Button \nTo clear all entries and start a new calculation - press the RESET button. It is important to do this after every action so as not to accidentally re-use information from the previous case.  "]

{"sheet":"Tips & Tricks","index":2,"n_rows":5,"n_cols":2,"rows_kept":5,"cut":false,"hidden":false,"merged":[[69,1,69,2],[70,1,70,12],[84,1,84,12],[85,1,85,12],[95,1,95,12],[68,1,68,12],[29,1,29,12],[30,1,30,12],[40,1,40,2],[41,1,41,12],[42,1,42,12],[43,1,43,12],[44,1,44,12],[45,1,45,12],[46,1,46,12],[47,1,47,9],[58,1,58,12],[28,1,28,12],[6,1,6,12],[7,1,7,12],[8,1,8,12],[9,1,9,12],[10,1,10,12],[11,1,11,12],[12,1,12,12],[13,1,13,12],[14,1,14,12],[15,1,15,12],[27,1,27,12],[5,1,5,12],[0,1,0,12],[1,1,1,12],[2,1,2,12],[3,1,3,12],[4,1,4,12]]}
[null,"Reasonable Compatibility Tool - Tips & Tricks"]
[]
[null,"More than 2 Jobs:  Calculate the self-attestation manually and total for all jobs.  Enter in the 'Monthly Amounts' section for Job 1.  Use the Comments to explain your actions.  "]
[]
[null,"  "]