{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Overview","Authority RAG Thresholds","Metric Definitions","Instructions","Inputs","Results"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":1,"defined_names":7,"comments":4,"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":"Overview","index":0,"n_rows":45,"n_cols":3,"rows_kept":45,"cut":false,"hidden":false,"merged":[[29,1,29,8],[31,2,31,8],[33,2,33,8],[44,1,44,8],[35,2,35,8],[36,1,36,8],[38,1,38,2],[39,1,39,8],[40,1,40,8],[41,1,41,8],[18,1,18,6],[22,1,22,8],[23,2,23,8],[24,2,24,8],[5,0,5,1],[11,1,11,8],[12,1,12,8],[13,1,13,8]]}
[]
["Adult Skills Financial Evaluation Template"]
["Overview Sheet"]
["OFFICIAL"]
["All Checks OK"]
[]
[]
[]
[]
[null,"Overview"]
[]
[null,"The Adult Skills Financial Evaluation Tool should be completed by the provider. The provider may also be required to complete a seperate assesment to include information on the prospective Lead Organisation, Immediate Parent organisation, Ultimate Parent organisation, including any other guarantor, and all relevant subcontractors as defined in the Contracting Authority's procurement documentation."]
[null,"Organisations are responsible for ensuring correct completion of this evaluation (or evaluations if more than one is to be submitted) and provision of all relevant supporting information (year-end accounting data etc.).  They will also be responsible for responding promptly to any clarification questions raised by the WMCA after initial submission, providing further supporting information as required."]
[null,"Organisations selected to deliver the procured contract will be required to provide annual updates throughout the contract period with latest annual accounting data or more regularly depending on the agreed reporting schedule."]
[]
[null,"Provider Instructions"]
[]
[null,"Overview"]
[null,"Numeric or narrative data must only be entered in the yellow input cells. For detailed guidance, please refer to the \"Instructions\" tab"]
[]
[null,"Sheet Colour Coding"]
[]
[null,"For each relevant organisation for whom you are providing FRA data, information should be provided in the 2 groups of separately-coloured tabs:"]
[null,"Blue tab -       Inputs","Financial information in relation to the last two financial periods must be entered into the \"Input\" tab. "]
[null,"Black tab -       Results","Explanatory comments / mitigations should be entered in the \"Results\" tab."]
[]
[]
[null,"Sheet Input Logic"]
[]
[null,"All entries should made in line with the classification of items under the applicable reporting standards (i.e. IFRS, FRS 102, Charities SORP) to most fairly represent the entity's financial statements. Organization’s financial accounts and supporting information should be in English and in UK Sterling. If the original documents are not in English, please provide copies of the originals and a certified translation into English. If the figures in the accounts are not expressed in UK Sterling, then copies of the original accounts shall be provided together with a UK Sterling rate equivalent copy utilising the pound exchange rate applicable on the date of auditor issue of the financial statement."]
[]
[null,"Income Statement","Losses should be entered as (negative values), profit should be entered as positive values. "]
[]
[null,"Balance Sheet:","All figures, whether assets or liabilities should be entered as positive values."]
[]
[null,"Credit Score","The credit score will be sourced by the WMCA."]
[null,"Annual Contract Value: Contracting Authority to confirm the value and does not require provider input."]
[]
[null,"Results tab"]
[]
[null,"The tool will automatically transfer data relating to the organisation, and its performance against stipulated Ratios and Metrics ."]
[null,"Please use yellow input fields (column K) to comment on each organisation's performance against Ratios.  As a minimum you must provide commentary where in any period the organisation is ranked Inadequate (Red) or Flag as Risk (Amber).  Your commentary should include (i) Reasons for the Inadequate (Red) or Flag as Risk (Amber) rating  (ii) Mitigating activity that the organisation has undertaken / will undertake to manage this risk (iii) Update on current position (reflecting that Accounts data is historical).  As relevant, latest Management Accounts or other data can be supplied if this supports explanations in relation to (i), (ii) or (iii)."]
[null,"Adult Skills Financial Evaluation Tool Limitations"]
[]
[null,"The Contracting Authorities and provider should be aware that the Adult Skills Financial Evaluation Tool does not have the functionality to pro-rate ratios where the user enters interim accounts. In these cases, ratio calculations should be annualised in the commentary section provided in the relevant assessment tabs. Interpretation of interim results should reflect the seasonality of earnings and cash generation in a business among other factors."]

{"sheet":"Authority RAG Thresholds","index":1,"n_rows":22,"n_cols":21,"rows_kept":22,"cut":false,"hidden":false,"merged":[[1,2,1,9],[1,12,1,14],[3,12,3,14],[3,17,3,18],[15,2,16,9]]}
[]
[null,null,"1. Financial Risk Rating",null,null,null,null,null,null,null,null,null,"2. Recommended Funding Limits (RFL)",null,null,null,null,"3. Credit Score"]
[]
[null,null,"Score","Gearing",null,"Solvency",null,"Profitabilty",null,"Rating",null,null,"Recommended Funding Limits (RFL)",null,null,null,null,"Credit Score",null,"Risk Rating","Pass/Fail"]
[null,null,100,"=0",0,">=2.0",2,"> = 9",10,"Outstanding",null,null,null,"Providers RFL as a percentage of turnover on cumulative contract value","Max £",null,null,71,100,"Very Low Risk","Pass"]
[null,null,90,"<10",10,">=1.8",1.8,">= 8",9,"Outstanding",null,null,null,null,null,null,null,51,70,"Low Risk","Pass"]
[null,null,80,"<20",20,">=1.6",1.6,">= 7",8,"Outstanding",null,null,"Inadequate",0,0,null,null,30,50,"Moderate Risk","Pass"]
[null,null,70,"<30",30,">=1.4",1.4,">= 6",7,"Good ",null,null,"Satisfactory",1.1499999999999999,1000000,null,null,21,29,"High Risk","Fail"]
[null,null,60,"<40",40,">=1.2",1.2,">= 5",6,"Good ",null,null,"Good",1.25,null,null,null,1,20,"Very High Risk","Fail"]
[null,null,50,"<50",50,">=1.0",1,">= 4",5,"Satisfactory",null,null,"Outstanding",1.5]
[null,null,40,"<60",60,">=0.8",0.8,">= 3",4,"Satisfactory"]
[null,null,30,"<70",70,">=0.7",0.7,">= 2",3,"Inadequate"]
[null,null,20,"<80",80,">=0.6",0.6,">= 1",1,"Inadequate"]
[null,null,10,"<90",90,">=0.5",0.5,">= 0",0,"Inadequate"]
[null,null,0,">=90 or Negative",90,"<0.5",0.5,"<0",0,"Inadequate"]
[null,null,"*Receiving a score of zero in any individual metric will limit the overall evaluation to a maximum of 'Satisfactory'."]
[]
[]
[null,{"$f":"240/3","v":80},"240-300","Outstanding"]
[null,{"$f":"180/3","v":60},"180-230","Good"]
[null,{"$f":"120/3","v":40},"120-170","Satisfactory"]
[null,0,"<=110","Inadequate"]

{"sheet":"Metric Definitions","index":2,"n_rows":8,"n_cols":5,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Metric No.","Metric","Formula / Calculation","Metric Definition "]
[null,1,"Solvency","Current Assets / Current Liabilities","Current Assets - Obtained from the balance sheet. \nCurrent Liabilities - Obtained from the balance sheet."]
[null,2,"Gearing","Total Debt (*Long Term Debt + Short Term Debt) / Net Worth *100","Total debt - long-term debt and other debt. To include bonds, bank loans, overdrafts, pension obligations, finance leases and director loans.\nTotal Equity – This is the value of equity as per the balance sheet, typically represented as Total Assets minus Total Liabilities."]
[null,3,"Profitability","Operating surplus/(deficit) / Total Revenue * 100","Operating surplus/(deficit) – income less direct operating expenses and administration costs\n\nTotal Revenue – taken from the statement of profit and loss"]
[null,4,"Recommended Funding Limits (RFL)","WMCA Total Contract Value (Including Existing Contracts) / Revenue\n\nResults compared against RFL limits per rating","Providers RFL as a percentage of turnover on cumulative contract value:\n\nOutstanding 150%\nGood 125%\nSatisfactory 115% (Up to a maximum of £1m)\nInadequate 0%"]
[null,5,"Cashflow change","(Current month cash-Previous month cash) / (Previous month’s cash)","Current month cash – from balance sheet (current month) \nPrevious month cash – from balance sheet (previous month)"]
[null,"* Where Long Term Debt = Non-current liability on the company’s Balance sheet"]

{"sheet":"Instructions","index":3,"n_rows":34,"n_cols":2,"rows_kept":34,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Adult Skills Financial Evaluation"]
[null,"Instructions Sheet"]
[]
[]
[null,"Overview"]
[]
[null,"Cells A5:C15 are to be populated from the provider 2 most recent years annual accounts. The only cells that require editing on the 'Inputs' tab are those shaded yellow."]
[]
[null,"Step 1 - In cell B5, begin by inputting the “Organisation name”."]
[]
[null,"Step 2 – C5:D5, please enter the period of accounts with the most recent in cell C5."]
[]
[null,"Step 3 – C6, enter Company Registration number."]
[]
[null,"Step 4 – C7, enter estimated annual contract value."]
[]
[null,"Step 5 – C8:D8, enter turnover recorded on the statement of profit and loss for each corresponding period."]
[]
[null,"Step 6 – C9:D9, enter Net Worth recorded on the balance sheet for each corresponding period. (Net asset value or total equity)."]
[]
[null,"Step 7 – C10:D10, enter total current assets recorded on the balance sheet for each corresponding period."]
[]
[null,"Step 8 - C11:D11, enter total current liabilities recorded on the balance sheet for each corresponding period."]
[]
[null,"Step 9 - C12:D12, enter total cash and cash equivalents recorded on the balance sheet for each corresponding period."]
[]
[null,"Step 10 - C13:D13, enter net profit or loss after taxation recorded on the statement of profit and loss for each of the corresponding period."]
[]
[null,"Step 11 - C14:D14, enter short term debt recorded on the balance sheet for each corresponding period."]
[]
[null,"Step 12 - C15:D15, enter long term debt (total long-term liabilities) recorded on the balance sheet for each corresponding period."]
[]
[null,"Cell C17 is for WMCA to complete."]

{"sheet":"Inputs","index":4,"n_rows":18,"n_cols":6,"rows_kept":18,"cut":false,"hidden":false,"merged":[[16,5,16,6],[4,5,14,6],[5,2,5,3],[6,2,6,3],[1,1,1,3],[16,2,16,3]]}
[null,"SQ - Financial Assessment Inputs"]
[null,"Inputs entered into this tab will populate the \"Results\" tab. It is the users responsibility to check the outcomes on the Results tab for errors or inaccuracies in the Results data."]
[]
[null,null,"Most Recent Period"]
[null,null,"Year 1 (Most Recent Period)","Year 2",null,"Provider to Complete"]
[null,"Companies House Registration Number"]
[null,"Estimated Annual Contract Value (Cumulative annual amount for all WMCA contracts)"]
[null,"Turnover"]
[null,"Net Worth"]
[null,"Current Assets"]
[null,"Current Liabilities (Positive Values)"]
[null,"Cash and Cash Equivalents"]
[null,"Operating Surplus (Loss expressed as a minus value)"]
[null,"Short Term Debt/Borrowing"]
[null,"Long Term Debt"]
[]
[null,"Credit Score",null,null,null,"WMCA to Complete"]
[null,"All values to be expressed as positive values except where stated."]

{"sheet":"Results","index":5,"n_rows":163,"n_cols":13,"rows_kept":163,"cut":false,"hidden":false,"merged":[[90,7,90,10],[7,1,7,3],[7,7,7,10],[4,14,4,15],[6,7,6,10],[4,7,4,10],[19,9,20,10],[24,9,25,10],[29,9,30,10],[41,9,42,10],[45,9,46,10],[43,9,43,10],[9,7,9,10],[67,12,70,16],[21,7,22,7],[45,12,47,16],[41,7,42,7],[41,8,42,8],[41,12,43,16],[72,7,73,7],[45,7,46,8],[47,7,47,8],[72,8,73,8],[79,10,80,10],[56,1,56,3],[45,1,47,1],[45,2,46,2],[45,3,46,3],[72,1,75,1],[67,1,70,1],[4,1,4,2],[69,9,70,9],[69,10,70,10],[26,7,27,7],[26,8,27,8],[26,9,27,9],[26,10,27,10],[31,7,32,7],[31,8,32,8],[31,9,32,9],[31,10,32,10],[67,7,68,7],[67,8,68,8],[67,9,68,10],[72,9,73,10],[74,7,75,7],[74,8,75,8],[74,9,75,9],[2,7,2,10],[24,7,25,7],[24,8,25,8],[19,8,20,8],[29,7,30,7],[29,8,30,8],[19,7,20,7],[6,1,6,3],[29,1,32,1],[52,7,52,10],[77,12,80,16],[77,7,78,7],[77,8,78,8],[77,9,78,10],[69,8,70,8],[72,12,75,16],[56,12,56,16],[56,7,56,10],[69,7,70,7],[74,10,75,10],[79,7,80,7],[79,8,80,8],[79,9,80,9],[19,12,22,16],[29,12,32,16],[24,12,27,16],[21,8,22,8],[21,9,22,9],[21,10,22,10],[36,1,39,1],[19,1,22,1],[24,1,27,1],[84,1,87,1],[84,7,86,7],[84,8,86,8],[84,9,86,10],[84,12,87,16],[2,1,2,3],[36,7,37,7],[38,7,39,7],[38,8,39,8],[36,8,37,8],[36,9,37,10],[38,9,39,9],[38,10,39,10],[17,1,17,3],[34,1,34,3],[65,1,65,3],[82,1,82,3],[77,1,80,1],[41,1,43,1],[41,2,42,2],[41,3,42,3],[36,12,39,16],[9,12,9,16],[9,1,9,3]]}
[]
[null,"Adult Skills Financial Evaluation - Results Tab"]
[null,{"$f":"IF(Inputs!B5=\"\",\"\",Inputs!B5)","v":""}]
[]
[null,"Condition",null,"PQQ Outcome Thresholds",null,null,null,"Values Taken From Accounts and Indicative Annual Contract Value"]
[]
[null,"Overall",null,null,null,null,null,{"$f":"IF(K48=\"Fail\",\"Fail\",IF(H10=\"Inadequate\",\"Fail\",IF(H10=\"Satisfactory\",\"Pass\",IF(H10=\"Good\",\"Pass\",IF(H10=\"Outstanding\",\"Pass\",\"Missing Data\")))))","v":"Fail"}]
[null,"Maximum Contract Value",null,null,null,null,null,{"$f":"IF(H10=\"Inadequate\",0,IF(H10=\"Satisfactory\",MIN(1000000,Inputs!C8*'Authority RAG Thresholds'!N8),IF(H10=\"Good\",Inputs!C8*'Authority RAG Thresholds'!N9,IF(H10=\"Outstanding\",Inputs!C8*'Authority RAG Thresholds'!N10))))","v":{"$error":"#VALUE!"}}]
[]
[null,{"$f":"Inputs!C5","v":"Year 1 (Most Recent Period)"},null,null,null,null,null,{"$f":"F14","v":{"$error":"#VALUE!"}},null,null,null,null,"Potential Provider to input comments"]
[]
[null,null,null,null,"RAG Rating",{"$f":"_xlfn.IFS(AND(F15<=300,F15>230),'Authority RAG Thresholds'!$D$19,AND(F15<=230,F15>170),'Authority RAG Thresholds'!D20,AND(F15<=170,F15>110),'Authority RAG Thresholds'!D21,F15<=110,'Authority RAG Thresholds'!D22)","v":{"$error":"#VALUE!"}}]
[null,null,null,null,"Override",{"$f":"CONCATENATE(IF(AND(OR(F12='Authority RAG Thresholds'!D19,F12='Authority RAG Thresholds'!D20),OR(F20<=0,F25<=0,F30<=0)),\"Cannot be rated higher than Satisfactory due to low score on key ratio\",\"\"))","v":{"$error":"#VALUE!"}}]
[null,null,null,null,"Summary Rating",{"$f":"IF(ISNUMBER(SEARCH(\"Inadequate\",F13,1)),\"Inadequate\",IF(ISNUMBER(SEARCH(\"Satisfactory\",F13,1)),\"Satisfactory\",F12))","v":{"$error":"#VALUE!"}}]
[null,null,null,null,"Total score",{"$f":"SUM(F20+F25+F30)","v":{"$error":"#VALUE!"}}]
[null,null,null,null,"Ratio","Ratio Score"]
[]
[null,"Key Ratios:"]
[]
[null,"Solvency","Current Assets Less Than 80% of Current Liabilities","Inadequate",{"$f":"IFERROR(Inputs!C10/Inputs!C11,\"Incomplete\")","v":"Incomplete"},{"$f":"IF(E20=\"Incomplete\",\"Incomplete\",_xlfn.IFS(E20<'Authority RAG Thresholds'!G15,0,AND(E20>='Authority RAG Thresholds'!G15,E20<'Authority RAG Thresholds'!G13),10,AND(E20>='Authority RAG Thresholds'!G13,E20<'Authority RAG Thresholds'!G12),20,AND(E20>='Authority RAG Thresholds'!G12,E20<'Authority RAG Thresholds'!G11),30,AND(E20>='Authority RAG Thresholds'!G11,E20<'Authority RAG Thresholds'!G10),40,AND(E20>='Authority RAG Thresholds'!G10,E20<'Authority RAG Thresholds'!G9),50,AND(E20>='Authority RAG Thresholds'!G9,E20<'Authority RAG Thresholds'!G8),60,AND(E20>='Authority RAG Thresholds'!G8,E20<'Authority RAG Thresholds'!G7),70,AND(E20>='Authority RAG Thresholds'!G7,E20<'Authority RAG Thresholds'!G6),80,AND(E20>='Authority RAG Thresholds'!G6,E20<2),90,E20>='Authority RAG Thresholds'!G5,100))","v":"Incomplete"},null,"Current Assets","Current Liabilities","Result"]
[null,null,"Between 80% and 119% of Current Liabilities","Satisfactory"]
[null,null,"Between 120% and 159% of Current Liabilities","Good",null,null,null,{"$f":"Inputs!C10","v":0},{"$f":"Inputs!C11","v":0},{"$f":"+H22/I22","v":{"$error":"#DIV/0!"}},{"$f":"_xlfn.XLOOKUP(F20,'Authority RAG Thresholds'!C5:C15,'Authority RAG Thresholds'!J5:J15)","v":{"$error":"#N/A"}}]
[null,null,"More than 160% of Current Liabilities","Outstanding"]
[]
[null,"Gearing","Total Debt More than 60% of Net Worth (or Negative)","Inadequate",{"$f":"IFERROR(((Inputs!C15+Inputs!C14)/Inputs!C9)*100,\"Incomplete\")","v":"Incomplete"},{"$f":"IF(E25=\"Incomplete\",\"Incomplete\",_xlfn.IFS(OR(E25>=90,E25<0),0,AND(E25<90,E25>=80),10,AND(E25<80,E25>=70),20,AND(E25<70,E25>=60),30,AND(E25<60,E25>=50),40,AND(E25<50,E25>=40),50,AND(E25<40,E25>=30),60,AND(E25<30,E25>=20),70,AND(E25<20,E25>=10),80,AND(E25<10,E25>=0),90,E25=0,100))","v":"Incomplete"},null,"Total Debt (Long + Short Term Debt)","Total Equity","Result"]
[null,null,"Between 40% and 59% of Net Worth","Satisfactory"]
[null,null,"Less than 39% of Net Worth","Good",null,null,null,{"$f":"Inputs!C15+Inputs!C14","v":0},{"$f":"Inputs!C9","v":0},{"$f":"+H27/I27","v":{"$error":"#DIV/0!"}},{"$f":"_xlfn.XLOOKUP(F25,'Authority RAG Thresholds'!C5:C15,'Authority RAG Thresholds'!J5:J15)","v":{"$error":"#N/A"}}]
[null,null,"Less than 19% of Net Worth","Outstanding"]
[]
[null,"Profitability","Operating Surplus Less Than 2.9% of Total Revenue","Inadequate",{"$f":"IFERROR((Inputs!C13/Inputs!C8)*100,\"Incomplete\")","v":"Incomplete"},{"$f":"IF(E30=\"Incomplete\",\"Incomplete\",_xlfn.IFS(E30<0,0,AND(E30>=0,E30<1),10,AND(E30>=1,E30<2),20,AND(E30>=2,E30<3),30,AND(E30>=3,E30<4),40,AND(E30>=4,E30<5),50,AND(E30>=5,E30<6),60,AND(E30>=6,E30<7),70,AND(E30>=7,E30<8),80,AND(E30>=8,E30<9),90,E30>=9,100))","v":"Incomplete"},null,"Operating surplus/(deficit)","Total Revenue","Result"]
[null,null,"Between 3% and 4.9% of Total Revenue","Satisfactory"]
[null,null,"Between 5% and 6.9% of Total Revenue","Good",null,null,null,{"$f":"Inputs!C13","v":0},{"$f":"Inputs!C8","v":0},{"$f":"H32/I32","v":{"$error":"#DIV/0!"}},{"$f":"_xlfn.XLOOKUP(F30,'Authority RAG Thresholds'!C5:C15,'Authority RAG Thresholds'!J5:J15)","v":{"$error":"#N/A"}}]
[null,null,"More than 7% of Total Revenue","Outstanding"]
[null,null,null," "]
[null,"Additional Tests:"]
[]
[null,"Recommended Funding Limits","No contract award","Inadequate",{"$f":"$I$39*F37","v":0},0,null,"Contract Value","Total Revenue ","Result"]
[null,null,"Maximum Contract Value 115% of Turnover","Satisfactory",{"$f":"$I$39*F38","v":0},1.1499999999999999]
[null,null,"Maximum Contract Value 125% of Turnover","Good",{"$f":"$I$39*F39","v":0},1.25,null,{"$f":"Inputs!C7","v":0},{"$f":"Inputs!C8","v":0},{"$f":"+H39/I39","v":{"$error":"#DIV/0!"}},{"$f":"IF(J39<=_xlfn.XLOOKUP($H$10,$D$37:$D$40,$F$37:$F$40),\"Pass\",IF(J39>_xlfn.XLOOKUP($H$10,$D$37:$D$40,$F$37:$F$40),\"Fail\"))","v":{"$error":"#DIV/0!"}}]
[null,null,"Maximum Contract Value 150% of Turnover","Outstanding",{"$f":"$I$39*F40","v":0},1.5]
[null,null,null," "]
[null,"Cashflow change","Less than 50% of Turnover","Pass",null,null,null,"Current Year cash-Previous Year cash","Previous Year cash","Result"]
[]
[null,null,"More than 50% of Turnover","Fail on Capacity Grounds",null,null,null,{"$f":"Inputs!C12","v":0},{"$f":"Inputs!D12","v":0},{"$f":"(+H44-I44)/I44","v":{"$error":"#DIV/0!"}}]
[]
[null,"If Credit Score","Less than 30","Flag as Risk",null,null,null,"Credit Score",null,"Result"]
[null,null,"More than 30","Pass"]
[null,null,"More than 30","Pass",null,null,null,{"$f":"Inputs!C17","v":0},null,{"$f":"IF(Results!H48<='Authority RAG Thresholds'!$S$9,'Authority RAG Thresholds'!T9,IF(Results!H48<='Authority RAG Thresholds'!S8,'Authority RAG Thresholds'!T8,IF(Results!H48<='Authority RAG Thresholds'!S7,'Authority RAG Thresholds'!T7,IF(Results!H48<='Authority RAG Thresholds'!S6,'Authority RAG Thresholds'!T6,IF(Results!H48<='Authority RAG Thresholds'!S5,'Authority RAG Thresholds'!T5)))))","v":"Very High Risk"},{"$f":"IF(H48<'Authority RAG Thresholds'!R7,\"Fail\",IF(H48>='Authority RAG Thresholds'!$R$7,\"Pass\"))","v":"Fail"}]
[]
[null,"Fail Check"]
[null,"Flag as Risk check"]
[null,"Pass Check"]
[null,{"$f":"B10&\" Overall\"","v":"Year 1 (Most Recent Period) Overall"},null,null,null,null,null,{"$f":"IF(F15>110,\"Pass\",\"Fail\")","v":{"$error":"#VALUE!"}}]
[]
[]
[]
[null,"Year 2 (Previous Year)",null,null,null,null,null,{"$f":"F62","v":{"$error":"#VALUE!"}},null,null,null,null,"Potential Provider to input comments"]
[]
[]
[null,null,null,null,"RAG Rating",{"$f":"_xlfn.IFS(AND(F63<=300,F63>230),'Authority RAG Thresholds'!$D$19,AND(F63<=230,F63>170),'Authority RAG Thresholds'!$D$20,AND(F63<=170,F63>110),'Authority RAG Thresholds'!$D$21,F63<=110,'Authority RAG Thresholds'!$D$22)","v":{"$error":"#VALUE!"}}]
[null,null,null,null,"Override",{"$f":"CONCATENATE(IF(AND(OR(F60='Authority RAG Thresholds'!AG39,F60='Authority RAG Thresholds'!AG40),OR(F68<=0,F73<=0,F78<=0)),\"Cannot be rated higher than Satisfactory due to low score on key ratio\",\"\"))","v":{"$error":"#VALUE!"}}]
[null,null,null,null,"Summary Rating",{"$f":"IF(ISNUMBER(SEARCH(\"Inadequate\",F61,1)),\"Inadequate\",IF(ISNUMBER(SEARCH(\"Satisfactory\",F61,1)),\"Satisfactory\",F60))","v":{"$error":"#VALUE!"}}]
[null,null,null,null,"Total score",{"$f":"SUM(F68+F73+F78)","v":{"$error":"#VALUE!"}}]
[null,null,null,null,"Ratio","Ratio Score"]
[]
[null,"Key Ratios:"]
[]
[null,"Solvency","Current Assets Less Than 80% of Current Liabilities","Inadequate",{"$f":"IFERROR(Inputs!D10/Inputs!D11,\"Incomplete\")","v":"Incomplete"},{"$f":"IF(E68=\"Incomplete\",\"Incomplete\",_xlfn.IFS(E68<'Authority RAG Thresholds'!G15,0,AND(E68>='Authority RAG Thresholds'!G15,E68<'Authority RAG Thresholds'!G13),10,AND(E68>='Authority RAG Thresholds'!G13,E68<'Authority RAG Thresholds'!G12),20,AND(E68>='Authority RAG Thresholds'!G12,E68<'Authority RAG Thresholds'!G11),30,AND(E68>='Authority RAG Thresholds'!G11,E68<'Authority RAG Thresholds'!G10),40,AND(E68>='Authority RAG Thresholds'!G10,E68<'Authority RAG Thresholds'!G9),50,AND(E68>='Authority RAG Thresholds'!G9,E68<'Authority RAG Thresholds'!G8),60,AND(E68>='Authority RAG Thresholds'!G8,E68<'Authority RAG Thresholds'!G7),70,AND(E68>='Authority RAG Thresholds'!G7,E68<'Authority RAG Thresholds'!G6),80,AND(E68>='Authority RAG Thresholds'!G6,E68<2),90,E68>='Authority RAG Thresholds'!G5,100))","v":"Incomplete"},null,"Current Assets","Current Liabilities","Result"]
[null,null,"Between 80% and 119% of Current Liabilities","Satisfactory"]
[null,null,"Between 120% and 159% of Current Liabilities","Good",null,null,null,{"$f":"Inputs!D10","v":0},{"$f":"Inputs!D11","v":0},{"$f":"+H70/I70","v":{"$error":"#DIV/0!"}},{"$f":"_xlfn.XLOOKUP(F20,'Authority RAG Thresholds'!C5:C15,'Authority RAG Thresholds'!J5:J15)","v":{"$error":"#N/A"}}]
[null,null,"More than 160% of Current Liabilities","Outstanding"]
[]
[null,"Gearing","Total Debt More than 60% of Net Worth (or Negative)","Inadequate",{"$f":"IFERROR(((Inputs!D15+Inputs!D14)/Inputs!D9)*100,\"Incomplete\")","v":"Incomplete"},{"$f":"IF(E73=\"Incomplete\",\"Incomplete\",_xlfn.IFS(OR(E73>=90,E73<0),0,AND(E73<90,E73>=80),10,AND(E73<80,E73>=70),20,AND(E73<70,E73>=60),30,AND(E73<60,E73>=50),40,AND(E73<50,E73>=40),50,AND(E73<40,E73>=30),60,AND(E73<30,E73>=20),70,AND(E73<20,E73>=10),80,AND(E73<10,E73>0),90,E73=0,100))","v":"Incomplete"},null,"Total Debt (Long + Short Term Debt)","Total Equity (inc Long Term Debt)","Result"]
[null,null,"Between 40% and 59% of Net Worth","Satisfactory"]
[null,null,"Less than 39% of Net Worth","Good",null,null,null,{"$f":"Inputs!D15+Inputs!D14","v":0},{"$f":"Inputs!D9","v":0},{"$f":"+H75/I75","v":{"$error":"#DIV/0!"}},{"$f":"_xlfn.XLOOKUP(F25,'Authority RAG Thresholds'!C5:C15,'Authority RAG Thresholds'!J5:J15)","v":{"$error":"#N/A"}}]
[null,null,"Less than 19% of Net Worth","Outstanding"]
[]
[null,"Profitability","Operating Surplus Less Than 2.9% of Total Revenue","Inadequate",{"$f":"IFERROR((Inputs!D13/Inputs!D8)*100,\"Incomplete\")","v":"Incomplete"},{"$f":"IF(E78=\"Incomplete\",\"Incomplete\",_xlfn.IFS(E78<0,0,AND(E78>=0,E78<1),10,AND(E78>=1,E78<2),20,AND(E78>=2,E78<3),30,AND(E78>=3,E78<4),40,AND(E78>=4,E78<5),50,AND(E78>=5,E78<6),60,AND(E78>=6,E78<7),70,AND(E78>=7,E78<8),80,AND(E78>=8,E78<9),90,E78>=9,100))","v":"Incomplete"},null,"Operating surplus/(deficit)","Total Revenue","Result"]
[null,null,"Between 3% and 4.9% of Total Revenue","Satisfactory"]
[null,null,"Between 5% and 6.9% of Total Revenue","Good",null,null,null,{"$f":"Inputs!D13","v":0},{"$f":"Inputs!D8","v":0},{"$f":"H80/I80","v":{"$error":"#DIV/0!"}},{"$f":"_xlfn.XLOOKUP(F30,'Authority RAG Thresholds'!C5:C15,'Authority RAG Thresholds'!J5:J15)","v":{"$error":"#N/A"}}]
[null,null,"More than 7% of Total Revenue","Outstanding"]
[null,null,null," "]
[null,"Additional Tests:"]
[]
[null,"Recommended Funding Limits","No contract award","Inadequate",{"$f":"$I$39*F85","v":0},0,null,"Contract Value","Total Revenue ","Result"]
[null,null,"Maximum Contract Value 105% of Turnover","Satisfactory",{"$f":"$I$39*F86","v":0},1.1499999999999999]
[null,null,"Maximum Contract Value 115% of Turnover","Good",{"$f":"$I$39*F87","v":0},1.25]
[null,null,"Maximum Contract Value 150% of Turnover","Outstanding",{"$f":"$I$39*F88","v":0},1.5,null,{"$f":"Inputs!C7","v":0},{"$f":"Inputs!D8","v":0},{"$f":"+H88/I88","v":{"$error":"#DIV/0!"}},{"$f":"IF(J88<=_xlfn.XLOOKUP($H$10,$D$37:$D$40,$F$37:$F$40),\"Pass\",IF(J88>_xlfn.XLOOKUP($H$10,$D$37:$D$40,$F$37:$F$40),\"Flag As Risk\"))","v":{"$error":"#DIV/0!"}}]
[null,null,null," "]
[]
[null,{"$f":"B57&\" Overall\"","v":"Year 2 (Previous Year) Overall"},null,null,null,null,null,{"$f":"IF(F63>110,\"Pass\",\"Fail\")","v":{"$error":"#VALUE!"}}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Less Than 120% Current Liabilities","Fail",null,null,null,"Current Assets","Current Liabilities"]
[null,"If Current Assets","Between 120% and 170% of Current Liabilities","Pass but flag as a risk"]
[null,null,"More than 170% of Current Liabilities","Pass",null,null,null,200,145,{"$f":"+H163/I163","v":1.3793103448275863},null,{"$f":"+H163/I163","v":1.3793103448275863}]