{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Bank Statement Analysis"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":3,"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":"Bank Statement Analysis","index":0,"n_rows":63,"n_cols":31,"rows_kept":63,"cut":false,"hidden":false,"merged":[[33,1,33,2],[24,7,25,8],[28,3,29,3],[32,1,32,2],[13,7,15,8],[17,1,17,2],[18,1,18,2],[29,1,29,2],[26,1,26,2],[25,1,25,2],[13,1,13,2],[22,1,22,2],[24,1,24,2],[23,1,23,2],[28,1,28,2],[19,1,19,2],[28,9,28,15],[30,9,30,15],[29,9,29,15],[24,9,24,15],[23,9,23,15],[26,9,26,15],[9,1,9,2],[10,9,10,15],[9,9,9,15],[11,9,11,15],[11,4,11,6],[11,1,11,2],[10,1,10,2],[14,9,14,15],[12,1,12,2],[27,1,27,2],[14,4,14,6],[14,1,14,2],[27,9,27,15],[17,7,17,8],[18,7,19,8],[22,3,24,3]]}
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"NSF Count","Bank Statements Submitted"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"COUNTA($C$39:$C$62)","v":0},{"$f":"COUNT($D$39:$D$62)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Updated:",null,null,null,null,null,"Bank Statement Duration"]
["Bank Statement Analysis Form",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"November 23rd, 2021",null,null,null,null,null,{"$f":"IFERROR(VLOOKUP(E12,$V$11:$Y$19,2,FALSE),\"\")","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"v4.4",null,null,null,null,null,"Profit and Loss Duration"]
["General Loan Information",null,null,null,null,null,null,null,null,"Comments",null,null,null,null,null,null,null,null,null,null,null,{"$f":"IFERROR(VLOOKUP(E12,$V$11:$Y$19,3,FALSE),\"\")","v":""}]
["Date:"]
["Loan Number:",null,null,null,"Documentation Type",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Doc Type Options","BKST duration","P&L Duration","Ownership","Employee Count","Business Type","Bank Statement Dropdown","Business Narrative Table","Product Business","Service Provider"]
["Borrower Name:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"24-Month Business Bank Statements",24,24,25,"0 Employees","Product Business","Ascending","0 Employees",0.25,0.2]
["Underwriter Name:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"12-Month Business Bank Statements",12,12,25,"1 - 10 Employees","Service Provider","Descending","1 - 10 Employees",0.5,0.4]
["Bank Name:",null,null,null,"Expense Ratio (No P&L Needed)",null,null,{"$f":"IF(ISNUMBER(SEARCH(\"Personal\",$E$12)),\"< Expense Ratio Not Required on Personal BKST (See Doc Type)\",\"\")","v":""},null,"Test Disclaimer",null,null,null,null,null,null,null,null,null,null,null,"24-Month Personal Bank Statements",24,0,25,"11-25 Empoyees",null,null,"11-25 Empoyees",0.7,0.6]
["Bank Account #:",null,null,null,null,null,null,null,null,"5 NSF permitted",null,null,null,null,null,null,null,null,null,null,null,"12-Month Personal Bank Statements",12,0,25,"> 25 Employees",null,null,"> 25 Employees",0.8,0.7]
[null,null,null,null,null,null,null,null,null,"Minimum Ownership percentage is 25%",null,null,null,null,null,null,null,null,null,null,null,"24-Month Co-Mingled Bank Statements",24,24,100]
["Business Information (Business Narrative Income Option)",null,null,null,"Business Ownership Requirements",null,null,null,null,"> Co-mingled requires 100%",null,null,null,null,null,null,null,null,null,null,null,"12-Month Co-Mingled Bank Statements",12,12,100]
["Number of Employees",null,null,null,"Minimum Ownership %",null,{"$f":"IFERROR(VLOOKUP(E12,$V$11:$Y$19,4,FALSE)/100,\"\")","v":""},{"$f":"IF(AND(G19>0%,G19<50%),\"MINORITY SHARE HOLDER\",\"\")","v":""},null,"> Ownership below 50% requires written authorization from Co-Owners the borrower(s) has access to business funds",null,null,null,null,null,null,null,null,null,null,null,"24-Month Profit and Loss (2 month BKST)",2,24,25]
["Business Type",null,null,null,"Business Ownership %",null,null,{"$f":"IF(AND(G19>0%,G19<50%),\"*Condition for access to business funds\",\"\")","v":""},null,"20% Income Tolerance",null,null,null,null,null,null,null,null,null,null,null,"12-Month Profit and Loss (2 month BKST)",2,12,25]
["Expense Ratio",{"$f":"IFERROR(IF(E15<>\"\",E15,INDEX($AD$12:$AE$15,MATCH($B$18,$AC$12:$AC$15,0),MATCH($B$19,$AD$11:$AE$11,0))),\"\")","v":""},null,null,null,null,null,null,null,"> Gross eligible deposits on bank statement worksheet must be within 80% of the Gross P&L Income"]
[null,null,null,null,"Income Validation Test",null,null,null,null,"> If a fail occurs, additional bank statement may be provided to bring the tolerance with 20%"]
["Income Information",null,null,null,"Non-Sufficent Funds",null,{"$f":"IFERROR(IF(AND(VALUE($V$5)=VALUE($W$5),C63<=5),\"Pass\",\"Fail\"),\"Fail\")","v":"Pass"},null,null,"Declining Income test measures for year over year decline."]
["Initial 1003 Income",null,null,{"$f":"IF(ISNUMBER(SEARCH(\"Personal\",$E$12)),\"< P&L Not Required on Personal BKST (See Doc Type)\",\"\")","v":""},"Ownership Percentage",null,{"$f":"IF(OR(G19=\"\",G19<G18),\"Fail\",\"Pass\")","v":"Fail"},null,null,"> If a fail occurs, but income appears stable proceed by using the most 12 month average income to qualify"]
["Gross P&L Income",null,null,null,"20% Tolerance",null,{"$f":"IF(B30=\"Missing Information\",\"Fail\",IF(ISNUMBER(SEARCH(\"Personal\",$E$12)),\"Pass\",IF(B20<>\"\",\"Pass\",IF(AND(B30>=-0.2,B30<=0.2),\"Pass\",\"Fail\"))))","v":"Fail"},null,null,"> When Income does NOT appear stable a loan denial may be the result"]
["Net P&L Income",null,null,null,"Declining Income",null,{"$f":"IF(COUNTIF(E12,\"*12-month*\"),\"Pass\",IF(R38<(R48*0.8),\"Alert\",\"Pass\"))","v":"Pass"},{"$f":"IF(G25=\"Alert\",\"*Use most recent 12 month income\",\"\")","v":""}]
["Total Bank Statement Income",{"$f":"IFERROR(IF(ISNUMBER(SEARCH(\"Personal\",E12)),B27*V7,B27*V9),\"Missing Information\")","v":"Missing Information"},null,null,"Eligible Income",null,{"$f":"IF(COUNTIF($B$26:$C$30,\"Missing Information\")>0,\"Fail\",IF(COUNTIF($G$22:$G$25,\"Fail\")>0,\"Ineligible\",\"Eligible\"))","v":"Fail"},null,null,"Notes"]
["Monthly Bank Statement Income",{"$f":"IF(ISNUMBER(SEARCH(\"2 month BKST\",E12)),SUM($P$39:$P$62)/W5,IF(W5=V7,SUM($P$39:$P$62)/W5,\"Missing Information\"))","v":"Missing Information"},null,null,null,null,null,null,null,"> Each Business examined requires a specific and individual analysis"]
["Expense Ratio",{"$f":"IFERROR(IF(AND(ISNUMBER(SEARCH(\"Personal\",$E$12)),B20=\"\"),\"N/A\",IF(B20<>\"\",B20,1-($B$25/$B$24))),\"Missing Information\")","v":"Missing Information"},null,null,null,null,null,null,null,"> Business Ownership Percentage and Expense Ratio need to be input in LOS (LendingQB)"]
["Qualifying Income",{"$f":"IFERROR(IF(ISNUMBER(SEARCH(\"Personal\",$E$12)),MIN(B23,B27),IF(B20<>\"\",MIN(B27*(1-B20)*G19,B23),MIN((B23),B27,B25*$G$19/MAX(V7,V9)))),\"Missing Information\")","v":"Missing Information"},null,{"$f":"IF(G25=\"Alert\",\"*See declining income section below\",\"\")","v":""},null,null,null,null,null,"> Bank Statements in an individuals name need to be accompanied with Business Accounts or loan is considered Co-Mingled"]
["Income Tolerance",{"$f":"IFERROR(IF(ISNUMBER(SEARCH(\"Personal\",$E$12)),\"N/A\",IF(OR(E15<>\"\",B20<>\"\"),\"Not required\",((B27*V9)-$B$24)/$B$24)),\"Missing Information\")","v":"Missing Information"},null,null,null,null,null,null,null,"> Income Documentation age requirement is 60 days from the Note Date"]
[]
["Qualifying Income (Declining Income Only)",null,null,null,"Ineligible Deposits Key (Optional)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Months"]
["Monthly Bank Statement Income",{"$f":"IF(AND(G25=\"Alert\",$B$36=\"Ascending\"),(SUM(P39:P50)/12)*$G$19,IF(AND(G25=\"Alert\",$B$36=\"Descending\"),(SUM(P51:P62)/12)*$G$19,\"Income Test Passes\"))","v":"Income Test Passes"},null,null,"Large deposit: Explanation needed",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,1]
["Qualifying Declining Income",{"$f":"IF(G25=\"pass\",\"Income Test Passes\",IF(ISNUMBER(SEARCH(\"Personal\",$E$12)),MIN(B23,B33),IF(B20<>\"\",MIN(B33*(1-B20),B23),MIN((B23),B33,B25/MAX(V7,V9)))))","v":"Income Test Passes"},null,null,"Transfers",null,null,null,null,null,null,null,null,null,null,null,null,"Declining Income Test",null,null,null,2]
[null,null,null,null,"Rent income",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,3]
["Bank Statement Order","Ascending",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,4]
["Bank Statement Information",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Most Recent 12 Months",null,null,null,5]
["Month","Date","NSF","Total Deposits","Ineligible Deposits",null,null,null,null,null,null,null,null,null,null,"Eligible Deposits",null,{"$f":"IF($B$36=\"ascending\",SUM(P39:P50),SUM(P51:P62))","v":0},null,null,null,6]
[{"$f":"(IF(AND(B36=\"ascending\",ISNUMBER(SEARCH(\"12\",E12))),\"1 (Most Recent)\",IF(AND(B36=\"descending\",ISNUMBER(SEARCH(\"12\",E12))),\"12 (Oldest)\",IF(AND(B36=\"ascending\",ISNUMBER(SEARCH(\"24\",E12))),\"1 (Most Recent)\",IF(AND(B36=\"descending\",ISNUMBER(SEARCH(\"24\",E12))),\"24 (Oldest)\",\"\")))))","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"D39-SUM(E39:O39)","v":0},null,null,null,null,null,7]
[{"$f":"IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"2\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"11\",IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"2\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"23\",\"\"))))","v":""},{"$f":"IF(AND($B$36=\"ascending\",A40>0),IF(B39=\"\",\"\",EDATE(B39,-1)),IF(AND($B$36=\"Descending\",A40>0),IF(B39=\"\",\"\",EDATE(B39,1))))","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"D40-SUM(E40:O40)","v":0},null,null,null,null,null,8]
[{"$f":"IFERROR(IF($B$36=\"Ascending\",A40+1,IF($B$36=\"descending\",A40-1)),\"\")","v":""},{"$f":"IF(AND($B$36=\"ascending\",A41>0),IF(B40=\"\",\"\",EDATE(B40,-1)),IF(AND($B$36=\"Descending\",A41>0),IF(B40=\"\",\"\",EDATE(B40,1))))","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"D41-SUM(E41:O41)","v":0},null,null,null,null,null,9]
[{"$f":"IFERROR(IF($B$36=\"Ascending\",A41+1,IF($B$36=\"descending\",A41-1)),\"\")","v":""},{"$f":"IF(AND($B$36=\"ascending\",A42>0),IF(B41=\"\",\"\",EDATE(B41,-1)),IF(AND($B$36=\"Descending\",A42>0),IF(B41=\"\",\"\",EDATE(B41,1))))","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"D42-SUM(E42:O42)","v":0},null,null,null,null,null,10]
[{"$f":"IFERROR(IF($B$36=\"Ascending\",A42+1,IF($B$36=\"descending\",A42-1)),\"\")","v":""},"",null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,null,null,null,null,11]
["","",null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,null,null,null,null,12]
["","",null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,null,null,null,null,13]
["","",null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,null,null,null,null,14]
["","",null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,"Previous 13-24 Months",null,null,null,15]
["","",null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,{"$f":"IF($B$36=\"ascending\",SUM(P51:P62),SUM(P39:P50))","v":0},null,null,null,16]
["","",null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,null,null,null,null,17]
[{"$f":"IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"12 (Oldest)\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"1 (Most Recent)\",IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"12\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"13\",\"\"))))","v":""},"",null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,null,null,null,null,18]
[{"$f":"IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"13\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"12\",\"\"))))","v":""},{"$f":"IF(AND($B$36=\"ascending\",A51<>\"\"),IF(B50=\"\",\"\",EDATE(B50,-1)),IF(AND($B$36=\"Descending\",A51<>\"\"),IF(B50=\"\",\"\",EDATE(B50,1)),\"\"))","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,null,null,null,null,19]
[{"$f":"IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"14\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"11\",\"\"))))","v":""},"",null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,null,null,null,null,20]
[{"$f":"IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"15\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"10\",\"\"))))","v":""},"",null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,null,null,null,null,21]
[{"$f":"IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"16\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"9\",\"\"))))","v":""},"",null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,null,null,null,null,22]
[{"$f":"IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"17\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"8\",\"\"))))","v":""},"",null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,null,null,null,null,23]
[{"$f":"IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"18\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"7\",\"\"))))","v":""},"",null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,null,null,null,null,24]
[{"$f":"IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"19\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"6\",\"\"))))","v":""},"",null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[{"$f":"IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"20\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"5\",\"\"))))","v":""},"",null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[{"$f":"IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"21\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"4\",\"\"))))","v":""},"",null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[{"$f":"IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"22\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"3\",\"\"))))","v":""},"",null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[{"$f":"IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"23\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"2\",\"\"))))","v":""},{"$f":"IF(AND($B$36=\"ascending\",A61>0),IF(B60=\"\",\"\",EDATE(B60,-1)),IF(AND($B$36=\"Descending\",A61>0),IF(B60=\"\",\"\",EDATE(B60,1))))","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[{"$f":"IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"12\",$E$12))),\"\",IF(AND($B$36=\"ascending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"24 (Oldest)\",IF(AND($B$36=\"descending\",ISNUMBER(SEARCH(\"24\",$E$12))),\"1 (Most Recent)\",\"\"))))","v":""},{"$f":"IF(AND($B$36=\"ascending\",A62>0),IF(B61=\"\",\"\",EDATE(B61,-1)),IF(AND($B$36=\"Descending\",A62>0),IF(B61=\"\",\"\",EDATE(B61,1))))","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"D62-SUM(E62:O62)","v":0}]
["Total",null,{"$f":"SUM(C39:C62)","v":0},{"$f":"SUM(D39:D62)","v":0},null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(P39:P62)","v":0}]