{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Données","Bilan du comparatif","Paramètres"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":2,"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":"Données","index":0,"n_rows":84,"n_cols":9,"rows_kept":84,"cut":false,"hidden":false,"merged":[[3,1,3,6],[1,1,1,8]]}
[]
[null,"Comparatif d'un portefeuille de crédit avec ou sans recours à un fonds de garantie"]
[]
[null,"Comparatif simplifié à vocation illustrative "]
[]
[null,1,null,"Marge bancaire",null,null,null,null,"Commentaires"]
[]
[null,null,null,"Données"]
[]
[null,null,null,"Production de crédits à garantir",2000," FCFA"]
[]
[null,null,null,"Maturité des crédits",4,{"$f":"IF(E12=1,\" an\",\" ans\")","v":" ans"}]
[]
[null,null,null,"Marge bancaire",0.12,null,null,null,"Rendement des prêts - coût des capitaux (moyenne UMOA 2017)"]
[]
[null,null,null,"Contentieux indemnisés",0.02," par an ",null,null,"% sur le capital initial "]
[]
[null,null,null,"Quotité garantie",0.5]
[]
[null,null,null,"Commission",1.4999999999999999E-2,null,null,null,"Calculé sur le capital restant dû (début de période)"]
[]
[null,null,null,"Taux de récupération sur contentieux",0.1]
[]
[]
[null,null,null,"Résultats"]
[null,null,null,null,"Sans le FG",null,"Avec le FG"]
[]
[null,null,null,"Marge brute",{"$f":"-$E$10-(PMT($E$14/12,$E$12*12,$E$10)*$E$12*12)","v":528.04820146506518},null,{"$f":"-$E$10-(PMT($E$14/12,$E$12*12,$E$10)*$E$12*12)-(E10*LOOKUP(E12*12,Paramètres!B47:B51,Paramètres!I47:I51)*E18)","v":488.29820146506518}]
[]
[null,null,null,"Contentieux",{"$f":"$E$16*$E$12*$E$10","v":160},null,{"$f":"$E$16*$E$12*$E$10","v":160}]
[]
[null,null,null,"Taux brut de dégradation",{"$f":"LOOKUP(E12*12,Paramètres!B40:B44,Paramètres!I40:I44)","v":0.14883720930232558},null,{"$f":"LOOKUP(E12*12,Paramètres!B40:B44,Paramètres!I40:I44)","v":0.14883720930232558}]
[]
[null,null,null,"Récupération ",{"$f":"E30*E22","v":16},null,{"$f":"(G30-E34)*E18+E34","v":88}]
[]
[null,null,null,"Marge nette",{"$f":"E28-E30+E34","v":384.04820146506518},null,{"$f":"G28-G30+G34","v":416.29820146506518}]
[]
[null,null,null,"Variation",null,null,{"$f":"(G36-E36)/E36","v":8.397383421396809E-2}]
[]
[]
[null,2,null,"Trésorerie"]
[]
[null,null,null,"Données"]
[]
[null,null,null,"Durée d'une procédure contentieuse ",4," années"]
[]
[null,null,null,"Contentieux indemnisés",{"$f":"E16","v":0.02}]
[]
[null,null,null,"Coût des ressources",0.05]
[]
[null,null,null,"Quotité du contentieux indemnisé ",0.6,null,null,null,"Versé dès le fait générateur "]
[]
[null,null,null,"Résultats"]
[null,null,null,null,"Sans le FG",null,"Avec le FG"]
[]
[null,null,null,"Contentieux",{"$f":"$E$47*$E$12*$E$10","v":160},null,{"$f":"$E$47*$E$12*$E$10","v":160}]
[]
[null,null,null,"Gain de trésorerie ",null,null,{"$f":"ROUND(-E56*E18*E51*(1-POWER(1+E49,E45)),0)","v":10}]
[]
[]
[null,3,null,"Besoins en fonds propres"]
[]
[null,null,null,"Données"]
[]
[null,null,null,"Encours moyen de crédit  ",{"$f":"E10/2","v":1000},"FCFA",null,null,"Encours moyen sur la période "]
[]
[null,null,null,"Coût moyen des fonds propres ",0.13500000000000001,null,null,null,"en %"]
[]
[null,null,null,"Pondération du risque ",1,null,null,null,"% prévu dans le dispositif prudentiel pour les PME "]
[]
[null,null,null,"Pondération du risque avec FG",0.3,null,null,null,"% prévu dans le dispositif prudentiel pour les FG selon leur pondération"]
[]
[null,null,null,"Résultats"]
[null,null,null,null,"Sans le FG",null,"Avec le FG"]
[]
[null,null,null,"Pondération du risque",{"$f":"E69","v":1},null,{"$f":"E71","v":0.3}]
[]
[null,null,null,"Actifs de crédit pondérés",{"$f":"E65*E76","v":1000},null,{"$f":"(E65*G76*E18)+(E65*(1-E18)*E76)","v":650}]
[]
[null,null,null,"Besoins de fonds propres",{"$f":"11.5%*E78","v":115},null,{"$f":"11.5%*G78","v":74.75}]
[]
[null,null,null,"Coût des fonds propres / an ",{"$f":"E80*$E67","v":15.525},null,{"$f":"G80*$E67","v":10.09125}]
[]
[null,null,null,"Variation",null,null,{"$f":"(G82-E82)/E82","v":-0.35}]

{"sheet":"Bilan du comparatif","index":1,"n_rows":18,"n_cols":7,"rows_kept":18,"cut":false,"hidden":false,"merged":[[1,1,1,6],[3,1,3,6]]}
[]
[null,"Comparatif avec ou sans recours à un fonds de garantie"]
[]
[null,"Comparatif simplifié à vocation illustrative "]
[]
[null,1,null,"Bilan sur une génération de crédit"]
[]
[null,null,null,null,"Sans le FG",null,"Avec le FG"]
[]
[null,null,null,"Marge nette",{"$f":"Données!E36","v":384.04820146506518},null,{"$f":"Données!G36","v":416.29820146506518}]
[]
[null,null,null,"Gain de trésorerie sur cx indemnisés",null,null,{"$f":"Données!G58","v":10}]
[]
[null,null,null,"Coût des fonds propres / an ",{"$f":"-Données!E82","v":-15.525},null,{"$f":"-Données!G82","v":-10.09125}]
[]
[null,null,null,"TOTAL",{"$f":"E10+E14","v":368.5232014650652},null,{"$f":"G10+G14+G12","v":416.20695146506517}]
[]
[null,null,null,"Variation",null,null,{"$f":"(G16-E16)/E16","v":0.12939144621134591}]

{"sheet":"Paramètres","index":2,"n_rows":59,"n_cols":10,"rows_kept":59,"cut":false,"hidden":true,"merged":[]}
[]
[]
[null,"Tables de pilotage"]
[]
[null,"Durée",null,"Amortissement",null,null,null,null,null,"Taux commission flat"]
[null,null,null,"A1","A2","A3","A4","A5"]
[]
[null,12,null,1,null,null,null,null,null,0.01]
[null,24,null,0.5,1,null,null,null,null,1.2999999999999999E-2]
[null,36,null,{"$f":"1/3","v":0.33333333333333331},{"$f":"2/3","v":0.66666666666666663},1,null,null,null,1.4999999999999999E-2]
[null,48,null,0.2,0.45,0.7,1,null,null,1.7000000000000001E-2]
[null,60,null,0.15,{"$f":"1/3","v":0.33333333333333331},0.53,{"$f":"3/4","v":0.75},1,null,0.02]
[]
[null,null,null,1,0.67,0.33,null,{"$f":"AVERAGE(D14:F14)","v":0.66666666666666663}]
[null,null,null,2.5000000000000001E-2,2.5000000000000001E-2,2.5000000000000001E-2,null,{"$f":"D15+E15+F15","v":7.5000000000000011E-2}]
[null,null,null,{"$f":"D15/D14","v":2.5000000000000001E-2},{"$f":"E15/E14","v":3.7313432835820892E-2},{"$f":"F15/F14","v":7.575757575757576E-2},null,{"$f":"H15/H14","v":0.11250000000000002}]
[]
[null,"Tableau des encours fin d'année"]
[null,12,null,{"$f":"1-D8","v":0}]
[null,24,null,{"$f":"1-D9","v":0.5},0]
[null,36,null,0.66666666666666674,0.33333333333333337,0]
[null,48,null,0.8,0.55000000000000004,0.30000000000000004,0]
[null,60,null,0.85,0.66666666666666674,0.47,0.25,0]
[]
[null,"Tableau des encours moyens"]
[null,12,null,{"$f":"(1+D19)/2","v":0.5},null,null,null,null,{"$f":"AVERAGE(D26:H26)","v":0.5}]
[null,24,null,{"$f":"(1+D20)/2","v":0.75},{"$f":"(E20+D20)/2","v":0.25},null,null,null,{"$f":"AVERAGE(D27:H27)","v":0.5}]
[null,36,null,{"$f":"(1+D21)/2","v":0.83333333333333337},{"$f":"(E21+D21)/2","v":0.5},0.16666666666666669,null,null,{"$f":"AVERAGE(D28:H28)","v":0.50000000000000011}]
[null,48,null,{"$f":"(1+D22)/2","v":0.9},0.67500000000000004,{"$f":"(F22+E22)/2","v":0.42500000000000004},0.15000000000000002,null,{"$f":"AVERAGE(D29:H29)","v":0.53749999999999998}]
[null,60,null,{"$f":"(1+D23)/2","v":0.92500000000000004},0.7583333333333333,{"$f":"(F23+E23)/2","v":0.56833333333333336},0.36,0.125,{"$f":"AVERAGE(D30:H30)","v":0.54733333333333334}]
[]
[null,"Tableau des contentieux "]
[null,12,null,{"$f":"Données!E16","v":0.02},null,null,null,null,{"$f":"SUM(D33:H33)","v":0.02}]
[null,24,null,{"$f":"D33","v":0.02},{"$f":"D33","v":0.02},null,null,null,{"$f":"SUM(D34:H34)","v":0.04}]
[null,36,null,{"$f":"D34","v":0.02},{"$f":"D34","v":0.02},{"$f":"E34","v":0.02},null,null,{"$f":"SUM(D35:H35)","v":0.06}]
[null,48,null,{"$f":"D35","v":0.02},{"$f":"D35","v":0.02},0.02,0.02,null,{"$f":"SUM(D36:H36)","v":0.08}]
[null,60,null,{"$f":"D36","v":0.02},0.02,0.02,0.02,0.02,{"$f":"SUM(D37:H37)","v":0.1}]
[]
[null,"Taux brut de dégradation"]
[null,12,null,{"$f":"D33/D26","v":0.04},null,null,null,null,{"$f":"I33/I26","v":0.04}]
[null,24,null,{"$f":"D34/D27","v":2.6666666666666668E-2},{"$f":"E34/E27","v":0.08},null,null,null,{"$f":"I34/I27","v":0.08}]
[null,36,null,{"$f":"D35/D28","v":2.4E-2},{"$f":"E35/E28","v":0.04},{"$f":"F35/F28","v":0.12},null,null,{"$f":"I35/I28","v":0.11999999999999997}]
[null,48,null,{"$f":"D36/D29","v":2.2222222222222223E-2},{"$f":"E36/E29","v":2.9629629629629627E-2},4.7058823529411764E-2,0.1333333333333333,null,{"$f":"I36/I29","v":0.14883720930232558}]
[null,60,null,{"$f":"D37/D30","v":2.1621621621621619E-2},{"$f":"E37/E30","v":2.6373626373626374E-2},{"$f":"F37/F30","v":3.5190615835777123E-2},5.5555555555555559E-2,0.16,{"$f":"I37/I30","v":0.18270401948842876}]
[]
[null,"Commission"]
[null,12,null,{"$f":"Données!$E$20","v":1.4999999999999999E-2},null,null,null,null,{"$f":"SUM(D47:H47)","v":1.4999999999999999E-2}]
[null,24,null,{"$f":"Données!$E$20","v":1.4999999999999999E-2},{"$f":"Données!$E$20*D20","v":7.4999999999999997E-3},null,null,null,{"$f":"SUM(D48:H48)","v":2.2499999999999999E-2}]
[null,36,null,{"$f":"Données!$E$20","v":1.4999999999999999E-2},{"$f":"Données!$E$20*D21","v":0.01},{"$f":"Données!$E$20*E21","v":5.0000000000000001E-3},null,null,{"$f":"SUM(D49:H49)","v":3.0000000000000002E-2}]
[null,48,null,{"$f":"Données!$E$20","v":1.4999999999999999E-2},{"$f":"Données!$E$20*D22","v":1.2E-2},{"$f":"Données!$E$20*E22","v":8.2500000000000004E-3},{"$f":"Données!$E$20*F22","v":4.5000000000000005E-3},null,{"$f":"SUM(D50:H50)","v":3.9750000000000008E-2}]
[null,60,null,{"$f":"Données!$E$20","v":1.4999999999999999E-2},{"$f":"Données!$E$20*D23","v":1.2749999999999999E-2},{"$f":"Données!$E$20*E23","v":0.01},{"$f":"Données!$E$20*F23","v":7.049999999999999E-3},{"$f":"Données!$E$20*G23","v":3.7499999999999999E-3},{"$f":"SUM(D51:H51)","v":4.8549999999999996E-2}]
[]
[]
[]
[null,null,"Sans le FG","Avec le FG"]
[null,"Marge nette",{"$f":"Données!E36","v":384.04820146506518},{"$f":"Données!G36","v":416.29820146506518}]
[null,"Gain de trésorerie sur cx indemnisés",{"$f":"Données!E58","v":0},{"$f":"Données!G58","v":10}]
[null,"Coût des fonds propres / an ",{"$f":"-Données!E82","v":-15.525},{"$f":"-Données!G82","v":-10.09125}]
[null,"TOTAL",{"$f":"C56+C58+C57","v":368.5232014650652},{"$f":"D56+D58+D57","v":416.20695146506517}]