{"text_format":"1","workbook":"xlsx","n_sheets":22,"sheets":["Language","Casos 1-5 HACER","Caso 6 HACER","Anex 1y2","Anex 3","Anex 4","Anex 5","Anex 6&7","Cases 1-5 TO DO","Case 6 TO DO","Exh 1&2","Exh 3","Exh 4","Exh 5","Exh 6&7","Casos 1-5 FAZER","Caso 6 FAZER","Anexos 1&2","Anexo 3","Anexo 4","Anexo 5","Anexos 6&7"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":21,"comments":21,"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":"Language","index":0,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Casos 1-5 HACER","index":1,"n_rows":84,"n_cols":14,"rows_kept":84,"cut":false,"hidden":true,"merged":[[71,1,71,3]]}
[null,null,null,null,"PLANTILLA PARA LOS CASOS 1  a 5"]
[null,"Hipótesis en verde y negrita. Ver notas explicativas dentro de cada celda."]
[]
[null,"PyG Hipótesis","Hipótesis",null,null,1,2,3]
[null,"Número de piezas",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000}]
[null,"Precio de venta por unidad en euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":10},{"$f":"G6*(1+$C$8)","v":10}]
[null,"Coste por unidad en euros",8,null,null,{"$f":"C7","v":8},{"$f":"F7*(1+$C$8)","v":8},{"$f":"G7*(1+$C$8)","v":8}]
[null,"Inflación esperada",0]
[]
[null,"PyG (€ ’000)",null,null,null,1,2,3]
[null,"Ventas"]
[null,"Coste de las ventas"]
[null,"Margen Bruto"]
[null,"Gastos generales",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":20},{"$f":"G14*(1+$C8)","v":20}]
[null,"EBITDA",null,null,null,{"$f":"F13-F14","v":-20},{"$f":"G13-G14","v":-20},{"$f":"H13-H14","v":-20}]
[null,"Amortización",5,"Años"]
[null,"EBIT"]
[null,"Intereses",0.05]
[null,"Benef antes de impuestos"]
[null,"Impuestos",0.2]
[null,"Beneficio neto",null,null,null,{"$f":"F19-F20","v":0},{"$f":"G19-G20","v":0},{"$f":"H19-H20","v":0}]
[null,"Dividendos pagados",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},{"$f":"$C22*H21","v":0}]
[null,null,null,null,null,null,null,null,"Promedio"]
[null,"EBITDA  / Inversión inicial",null,null,null,{"$f":"F15/$E$31","v":-6.6666666666666666E-2},{"$f":"G15/$E$31","v":-6.6666666666666666E-2},{"$f":"H15/$E$31","v":-6.6666666666666666E-2},{"$f":"AVERAGE(F24:H24)","v":-6.6666666666666666E-2}]
[null,"RONA (EBIT / AN)",null,null,null,{"$f":"F17/E32","v":{"$error":"#DIV/0!"}},{"$f":"G17/F32","v":{"$error":"#DIV/0!"}},{"$f":"H17/G32","v":{"$error":"#DIV/0!"}},{"$f":"AVERAGE(F25:H25)","v":{"$error":"#DIV/0!"}}]
[null,"ROA (Beneficio / AN)",null,null,null,{"$f":"F21/E32","v":{"$error":"#DIV/0!"}},{"$f":"G21/F32","v":{"$error":"#DIV/0!"}},{"$f":"H21/G32","v":{"$error":"#DIV/0!"}},{"$f":"AVERAGE(F26:H26)","v":{"$error":"#DIV/0!"}}]
[null,"ROE (Benef / Equity)",null,null,null,{"$f":"F21/E37","v":0},{"$f":"G21/F37","v":{"$error":"#DIV/0!"}},{"$f":"H21/G37","v":{"$error":"#DIV/0!"}},{"$f":"AVERAGE(F27:H27)","v":{"$error":"#DIV/0!"}}]
[]
[null,"Balance resumido",null,null,0,1,2,3,null,null,"Hipótesis de NOF"]
[null,"NOF",0.2,"x Ventas",null,null,null,0,null,null,"Clientes",90,null,"NOF COMO % VENTAS "]
[null,"AF neto",0,"Capex",300,null,null,0,null,null,"Existencias ",10,null,{"$f":"(L30+L31*0.85-L32*0.85)/365","v":0.2}]
[null,"AN activo  neto",null,null,null,null,null,null,null,null,"Proveedores",30]
[]
[null,"Línea de crédito D-Corto",0,"x NOF",null,null,null,0]
[null,"Préstamos, D-Largo",3,"años",0,null,null,0]
[null,"D, Deuda total"]
[null,"E, Equity o Patrimonio",null,null,{"$f":"E31-E35","v":300}]
[null,"Financiación",null,null,{"$f":"E36+E37","v":300}]
[]
[null,null,null,"Caja en el balance (+ o -)"]
[null,null,null,"Aumento anual de caja"]
[null,null,null,"ECF = Aumento de caja + dividendos pagados"]
[]
[null,"CF para el accionista o ECF",null,null,0,1,2,3]
[null,"Beneficio neto"]
[null,"Variación de AN"]
[null,"Variación de Deuda",null,null,null,null,null,null,"Suma de CF"]
[null,"CF accionista",null,null,null,null,null,null,{"$f":"SUM(E48:H48)","v":0}]
[null,"TIR  =",{"$f":"IRR(E48:H48)","v":{"$error":"#NUM!"}}]
[]
[null,"FCF",null,null,0,1,2,3]
[null,"EBIT x (1-t)"]
[null,"Variación de AN"]
[null,"FCF"]
[null,"IRR  =",{"$f":"IRR(E54:H54)","v":{"$error":"#NUM!"}}]
[]
[]
[]
[null,"Riesgos. Análisis de sensibilidad",null,null,null,null,"CASO 2"]
[]
[null,null,null,"Riesgo Mayor"]
[null,"Escenarios","Base","Peor","Mejor","Rango"]
[null,"Precio",10,9,11]
[null,"TIR accionista",null,null,null,{"$f":"E64-D64","v":0}]
[null,"Coste de la máquina AF",300,330,270]
[null,"TIR accionista",null,null,null,{"$f":"E66-D66","v":0}]
[null,"Tamaño de NOF",0.2,0.25,0.15]
[null,"TIR accionista",null,null,null,{"$f":"E68-D68","v":0}]
[null,"Riesgo Total",null,null,null,{"$f":"E69-D69","v":0}]
[]
[]
[null,"TIR y nivel de Deuda",null,null,null,null,"CASO 3"]
[null,"Deuda","TIR","Aumento"]
[null,"60M€ o 20% del AF"]
[null,"120M€ o 40% del AF"]
[null,"180M€ o 60% del AF"]
[null,"240M€ o 80% del AF"]
[]
[null,null,null,null,null,null,"CASO 5"]
[]
[null,"CF para el accionista o ECF",null,0,1,2,3,"TIR","Suma de CF"]
[null,"Máquina nueva sin deuda (caso 1)"]
[null,"Máquina nueva con deuda (caso 3)"]
[null,"Máquina antigua CF (caso 5)"]

{"sheet":"Caso 6 HACER","index":2,"n_rows":57,"n_cols":17,"rows_kept":57,"cut":false,"hidden":true,"merged":[]}
[null,null,null,null,"Caso 6. Proyecto de costes. ¿Compra o Renting? HACER"]
[null,"Hipótesis en verde y negrita. Ver notas explicativas dentro de cada celda."]
[null,null,null,null,null,null,"Compra"]
[null,"P&L ","Hipótesis",null,null,1,2,3,null,"Renting"]
[null,"EBITDA",100,null,null,{"$f":"$C5","v":100},{"$f":"$C5","v":100},{"$f":"$C5","v":100},null,{"$f":"$C5","v":100}]
[null,"Amortización o pago renting",5,"años",null,null,null,null,null,85]
[null,"EBIT",null,null,null,null,null,null,null,null,null,null,"Renting"]
[null,"Intereses",0.05,null,null,null,null,null,null,null,null,null,-300]
[null,"Benef antes de impuestos",null,null,null,null,null,null,null,null,null,null,85]
[null,"Impuestos",0.2,null,null,null,null,null,null,null,null,null,{"$f":"M9","v":85}]
[null,"Beneficio ",null,null,null,null,null,null,null,null,null,null,{"$f":"M10","v":85}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"M11","v":85}]
[null,"Balance con Compra",null,null,0,1,2,3,null,null,null,null,{"$f":"M12","v":85}]
[null,"NOF",null,null,null,120,null,0]
[null,"AF (máquina)",null,null,300,null,null,0,null,null,null,null,{"$f":"IRR(M8:M13)","v":0.12858463526289921}]
[null,"AN activo  neto",null,null,{"$f":"E14+E15","v":300},{"$f":"F14+F15","v":120},{"$f":"G14+G15","v":0},{"$f":"H15+H14","v":0}]
[]
[null,"Línea de crédito D-Corto",0.8,null,null,null,null,{"$f":"$C18*H14","v":0}]
[null,"Préstamos, D-Largo",3,"años",180,null,null,0]
[null,"D, Deuda total"]
[null,"E, Equity o Patrimonio"]
[null,"Financiación"]
[null,null,null,"Caja en el balance (+ o -)"]
[null,null,null,"Caja generada CF"]
[]
[null,"CF accionista. Compra",null,null,0,1,2,3]
[null,"Variación de AN"]
[null,"Variación de Deuda"]
[null,"Beneficio neto (con compra)",null,null,null,null,null,null,null,"Suma de CF"]
[null,"CF accionista (Compra)",null,null,null,null,null,null,null,{"$f":"SUM(E30:H30)","v":0}]
[]
[null,"Rentabilidad requerida (K) = ",0.13,null,"VAN =",{"$f":"NPV(C32,F30:H30)+E30","v":0},"TIR =",{"$f":"IRR(E30:H30)","v":{"$error":"#NUM!"}}]
[]
[null,"CF accionista. Renting",null,null,0,1,2,3]
[null,"Variación de NOF"]
[null,"Cambios en línea de crédito"]
[null,"Beneficio, con renting",null,null,null,null,null,null,null,"Suma de CF"]
[null,"CF accionista (Renting)",null,null,null,null,null,null,null,{"$f":"SUM(E38:H38)","v":0}]
[]
[null,null,null,null,"VAN =",{"$f":"NPV(C32,F38:H38)+E38","v":0},"TIR =",{"$f":"IRR(E38:H38)","v":{"$error":"#NUM!"}}]
[]
[]
[]
[null,null,null,null,"Caso 7. Comparar Proyectos. RESUELTO"]
[]
["Caso","CF para el accionista o ECF",null,0,1,2,3,"TIR",null,"Suma de CF"]
[1,"Máquina nueva, sin deuda",null,-300,-28,92,332,0.1000188925132417,null,{"$f":"SUM(D47:G47)","v":96},null,null,null,null,null,null,"New machine, no debt"]
[2,"Riesgos. Escenario peor",null,-330,-89.8,45.2,312.2,-5.8751578537388438E-2,null,-62.400000000000034,null,null,null,null,null,null,"Risks Worst"]
[2,"Riesgos. Escenario mejor",null,-270,39.799999999999997,138.80000000000001,345.8,0.30027686560496547,null,254.40000000000003,null,null,null,null,null,null,"Risks Best"]
[3,"Maquina nueva con deuda",null,-120,0.79999999999999716,23.36,169.76,0.18267186571826977,null,73.919999999999987,null,null,null,null,null,null,"New machine with debt"]
["3b","Máquina nueva con deuda. Peor escenario",null,-150,-49,-23.92,137.48000000000002,-0.17459138654184225,null,-85.44,null,null,null,null,null,null,"New machine with debt, Worst case"]
["3c","Maquina nueva con deuda. Mejor escenario",null,-90,51.799999999999983,70.831999999999994,201.03199999999998,0.7522264968437915,null,233.66399999999996,null,null,null,null,null,null,"New machine with debt, Best case"]
[4,"Máquina nueva con inflación, sin deuda.",null,-300,-28,88,360.8,0.12249343497344056,null,120.80000000000001,null,null,null,null,null,null,"New machine with inflation, no debt"]
["4b","Nueva máquina con inflación y deuda",null,-120,0.79999999999999716,28.960000000000022,188.57600000000002,0.2340795222783163,null,98.336000000000041,null,null,null,null,null,null,"New machine with inflation and debt"]
[5,"Maquina vieja, con 0 deuda",null,0,-88,32,152,0.50859268357512954,null,96,null,null,null,null,null,null,"Old machine, with 0 debt "]
["5b","Maquina vieja, con deuda",null,0,8,28.160000000000004,52.16,"na",null,88.32,null,null,null,null,null,null,"Old machine, with debt"]
[6,"Renting vs Comprar",null,0,-15.84,8.16,32.159999999999997,0.71,null,24.479999999999997,null,null,null,null,null,null,"Renting vs Buy"]

{"sheet":"Anex 1y2","index":3,"n_rows":84,"n_cols":14,"rows_kept":84,"cut":false,"hidden":true,"merged":[[71,1,71,3]]}
[null,null,null,null,"Casos 1 y 2. Rentabilidad y riesgo de un proyecto. RESUELTO"]
[null,"Hipótesis en verde y negrita. Ver notas explicativas dentro de cada celda."]
[]
[null,"PyG Hipótesis","Hipótesis",null,null,1,2,3]
[null,"Número de piezas",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000}]
[null,"Precio de venta por unidad en euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":10},{"$f":"G6*(1+$C$8)","v":10}]
[null,"Coste por unidad en euros",8,null,null,{"$f":"C7","v":8},{"$f":"F7*(1+$C$8)","v":8},{"$f":"G7*(1+$C$8)","v":8}]
[null,"Inflación esperada",0]
[]
[null,"PyG (€ ’000)",null,null,null,1,2,3]
[null,"Ventas",null,null,null,{"$f":"F5*F6/1000","v":600},{"$f":"G5*G6/1000","v":600},{"$f":"H5*H6/1000","v":600}]
[null,"Coste de las ventas",null,null,null,{"$f":"F7*F5/1000","v":480},{"$f":"G7*G5/1000","v":480},{"$f":"H7*H5/1000","v":480}]
[null,"Margen Bruto",null,null,null,{"$f":"F11-F12","v":120},{"$f":"G11-G12","v":120},{"$f":"H11-H12","v":120}]
[null,"Gastos generales",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":20},{"$f":"G14*(1+$C8)","v":20}]
[null,"EBITDA",null,null,null,{"$f":"F13-F14","v":100},{"$f":"G13-G14","v":100},{"$f":"H13-H14","v":100}]
[null,"Amortización",5,"Años",null,{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60}]
[null,"EBIT",null,null,null,{"$f":"F15-F16","v":40},{"$f":"G15-G16","v":40},{"$f":"H15-H16","v":40}]
[null,"Intereses",0.05,null,null,{"$f":"E36*$C$18","v":0},{"$f":"F36*$C$18","v":0},{"$f":"G36*$C$18","v":0}]
[null,"Benef antes de impuestos",null,null,null,{"$f":"F17-F18","v":40},{"$f":"G17-G18","v":40},{"$f":"H17-H18","v":40}]
[null,"Impuestos",0.2,null,null,{"$f":"F19*$C$20","v":8},{"$f":"G19*$C$20","v":8},{"$f":"H19*$C$20","v":8}]
[null,"Beneficio neto",null,null,null,{"$f":"F19-F20","v":32},{"$f":"G19-G20","v":32},{"$f":"H19-H20","v":32}]
[null,"Dividendos pagados",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},{"$f":"$C22*H21","v":0}]
[null,null,null,null,null,null,null,null,"Promedio"]
[null,"EBITDA  / Inversión inicial",null,null,null,{"$f":"F15/$E$31","v":0.33333333333333331},{"$f":"G15/$E$31","v":0.33333333333333331},{"$f":"H15/$E$31","v":0.33333333333333331},{"$f":"AVERAGE(F24:H24)","v":0.33333333333333331}]
[null,"RONA (EBIT / AN)",null,null,null,{"$f":"F17/E32","v":0.13333333333333333},{"$f":"G17/F32","v":0.1111111111111111},{"$f":"H17/G32","v":0.13333333333333333},{"$f":"AVERAGE(F25:H25)","v":0.12592592592592591}]
[null,"ROA (Beneficio / AN)",null,null,null,{"$f":"F21/E32","v":0.10666666666666667},{"$f":"G21/F32","v":8.8888888888888892E-2},{"$f":"H21/G32","v":0.10666666666666667},{"$f":"AVERAGE(F26:H26)","v":0.10074074074074074}]
[null,"ROE (Benef / Equity)",null,null,null,{"$f":"F21/E37","v":0.10666666666666667},{"$f":"G21/F37","v":9.6385542168674704E-2},{"$f":"H21/G37","v":8.7912087912087919E-2},{"$f":"AVERAGE(F27:H27)","v":9.698809891580977E-2}]
[]
[null,"Balance resumido",null,null,0,1,2,3,null,null,"Hipótesis de NOF"]
[null,"NOF",0.2,"x Ventas",null,{"$f":"$C$30*F11","v":120},{"$f":"$C$30*G11","v":120},0,null,null,"Clientes",90,null,"NOF COMO % VENTAS "]
[null,"AF neto",0,"Capex",300,{"$f":"E31-F16+$C31","v":240},{"$f":"F31-G16+$C31","v":180},0,null,null,"Existencias ",10,null,{"$f":"(L30+L31*0.85-L32*0.85)/365","v":0.2}]
[null,"AN activo  neto",null,null,{"$f":"E31+E30","v":300},{"$f":"F31+F30","v":360},{"$f":"G31+G30","v":300},{"$f":"H31+H30","v":0},null,null,"Proveedores",30]
[]
[null,"Línea de crédito D-Corto",0,"x NOF",{"$f":"$C34*E30","v":0},{"$f":"$C34*F30","v":0},{"$f":"$C34*G30","v":0},{"$f":"$C34*H30","v":0}]
[null,"Préstamos, D-Largo",3,"años",0,{"$f":"E35-($E35/$C35)","v":0},{"$f":"F35-($E35/$C35)","v":0},0]
[null,"D, Deuda total",null,null,{"$f":"E34+E35","v":0},{"$f":"F34+F35","v":0},{"$f":"G34+G35","v":0},{"$f":"H34+H35","v":0}]
[null,"E, Equity o Patrimonio",null,null,{"$f":"E31-E35","v":300},{"$f":"E37+F21-F22","v":332},{"$f":"F37+G21-G22","v":364},{"$f":"G37+H21-H22","v":396}]
[null,"Financiación",null,null,{"$f":"E36+E37","v":300},{"$f":"F36+F37","v":332},{"$f":"G36+G37","v":364},{"$f":"H36+H37","v":396}]
[]
[null,null,null,"Caja en el balance (+ o -)",{"$f":"E38-E32","v":0},{"$f":"F38-F32","v":-28},{"$f":"G38-G32","v":64},{"$f":"H38-H32","v":396}]
[null,null,null,"Aumento anual de caja",null,{"$f":"F40-E40","v":-28},{"$f":"G40-F40","v":92},{"$f":"H40-G40","v":332}]
[null,null,null,"ECF = Aumento de caja + dividendos pagados",{"$f":"-E37","v":-300},{"$f":"F41+F22","v":-28},{"$f":"G41+G22","v":92},{"$f":"H41+H22","v":332}]
[]
[null,"CF para el accionista o ECF",null,null,0,1,2,3]
[null,"Beneficio neto",null,null,null,{"$f":"F21","v":32},{"$f":"G21","v":32},{"$f":"H21","v":32}]
[null,"Variación de AN",null,null,{"$f":"-E32","v":-300},{"$f":"E32-F32","v":-60},{"$f":"F32-G32","v":60},{"$f":"G32-H32","v":300}]
[null,"Variación de Deuda",null,null,{"$f":"E36","v":0},{"$f":"F36-E36","v":0},{"$f":"G36-F36","v":0},{"$f":"H36-G36","v":0},"Suma de CF"]
[null,"CF accionista",null,null,{"$f":"SUM(E45:E47)","v":-300},{"$f":"SUM(F45:F47)","v":-28},{"$f":"SUM(G45:G47)","v":92},{"$f":"SUM(H45:H47)","v":332},{"$f":"SUM(E48:H48)","v":96}]
[null,"TIR  =",{"$f":"IRR(E48:H48)","v":0.1000188925132417}]
[]
[null,"FCF",null,null,0,1,2,3]
[null,"EBIT x (1-t)",null,null,null,{"$f":"F17*(1-$C20)","v":32},{"$f":"G17*(1-$C20)","v":32},{"$f":"H17*(1-$C20)","v":32}]
[null,"Variación de AN",null,null,{"$f":"E46","v":-300},{"$f":"F46","v":-60},{"$f":"G46","v":60},{"$f":"H46","v":300}]
[null,"FCF",null,null,{"$f":"SUM(E52:E53)","v":-300},{"$f":"SUM(F52:F53)","v":-28},{"$f":"SUM(G52:G53)","v":92},{"$f":"SUM(H52:H53)","v":332}]
[null,"IRR  =",{"$f":"IRR(E54:H54)","v":0.1000188925132417}]
[]
[]
[]
[null,"Riesgos. Análisis de sensibilidad",null,null,null,null,"CASO 2"]
[]
[null,null,null,"Riesgo Mayor"]
[null,"Escenarios","Base","Peor","Mejor","Rango"]
[null,"Precio",10,9,11]
[null,"TIR accionista",0.1,-5.1319434967612376E-2,0.24481039833418961,{"$f":"E64-D64","v":0.29612983330180198}]
[null,"Coste de la máquina AF",300,330,270]
[null,"TIR accionista",{"$f":"C64","v":0.1},7.9008814853287923E-2,0.12455999895101888,{"$f":"E66-D66","v":4.5551184097730957E-2}]
[null,"Tamaño de NOF",0.2,0.25,0.15]
[null,"TIR accionista",{"$f":"C64","v":0.1},9.4386474155067512E-2,0.10632719227147946,{"$f":"E68-D68","v":1.1940718116411952E-2}]
[null,"Riesgo Total",0.1,-5.8751578537388438E-2,0.30027686560496569,{"$f":"E69-D69","v":0.35902844414235413}]
[]
[]
[null,"TIR y nivel de Deuda",null,null,null,null,"CASO 3"]
[null,"Deuda","TIR","Aumento"]
[null,"60M€ o 20% del AF",0.128]
[null,"120M€ o 40% del AF",0.14899999999999999,{"$f":"C75-C74","v":2.0999999999999991E-2}]
[null,"180M€ o 60% del AF",0.183,{"$f":"C76-C75","v":3.4000000000000002E-2}]
[null,"240M€ o 80% del AF",0.251,{"$f":"C77-C76","v":6.8000000000000005E-2}]
[]
[null,null,null,null,null,null,"CASO 5"]
[]
[null,"CF para el accionista o ECF",null,0,1,2,3,"TIR","Suma de CF"]
[null,"Máquina nueva sin deuda (caso 1)",null,-300,-28,92,332,{"$f":"IRR(D82:G82)","v":0.1000188925132417},{"$f":"SUM(D82:G82)","v":96}]
[null,"Máquina nueva con deuda (caso 3)",null,-120,0.79999999999999716,23.36,169.76,{"$f":"IRR(D83:G83)","v":0.18267186571826977},{"$f":"SUM(D83:G83)","v":73.919999999999987}]
[null,"Máquina antigua CF (caso 5)",null,0,-88,32,152,{"$f":"IRR(D84:G84)","v":0.50859268357512954},{"$f":"SUM(D84:G84)","v":96}]

{"sheet":"Anex 3","index":4,"n_rows":84,"n_cols":14,"rows_kept":84,"cut":false,"hidden":true,"merged":[[71,1,71,3]]}
[null,null,null,null,"Caso 3. Proyecto con deuda. RESUELTO"]
[null,"Hipótesis en verde y negrita. Ver notas explicativas dentro de cada celda."]
[]
[null,"PyG Hipótesis","Hipótesis",null,null,1,2,3]
[null,"Número de piezas",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000}]
[null,"Precio de venta por unidad en euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":10},{"$f":"G6*(1+$C$8)","v":10}]
[null,"Coste por unidad en euros",8,null,null,{"$f":"C7","v":8},{"$f":"F7*(1+$C$8)","v":8},{"$f":"G7*(1+$C$8)","v":8}]
[null,"Inflación esperada",0]
[]
[null,"PyG (€ ’000)",null,null,null,1,2,3]
[null,"Ventas",null,null,null,{"$f":"F5*F6/1000","v":600},{"$f":"G5*G6/1000","v":600},{"$f":"H5*H6/1000","v":600}]
[null,"Coste de las ventas",null,null,null,{"$f":"F7*F5/1000","v":480},{"$f":"G7*G5/1000","v":480},{"$f":"H7*H5/1000","v":480}]
[null,"Margen Bruto",null,null,null,{"$f":"F11-F12","v":120},{"$f":"G11-G12","v":120},{"$f":"H11-H12","v":120}]
[null,"Gastos generales",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":20},{"$f":"G14*(1+$C8)","v":20}]
[null,"EBITDA",null,null,null,{"$f":"F13-F14","v":100},{"$f":"G13-G14","v":100},{"$f":"H13-H14","v":100}]
[null,"Amortización",5,"Años",null,{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60}]
[null,"EBIT",null,null,null,{"$f":"F15-F16","v":40},{"$f":"G15-G16","v":40},{"$f":"H15-H16","v":40}]
[null,"Intereses",0.05,null,null,{"$f":"E36*$C$18","v":9},{"$f":"F36*$C$18","v":10.8},{"$f":"G36*$C$18","v":7.8000000000000007}]
[null,"Benef antes de impuestos",null,null,null,{"$f":"F17-F18","v":31},{"$f":"G17-G18","v":29.2},{"$f":"H17-H18","v":32.200000000000003}]
[null,"Impuestos",0.2,null,null,{"$f":"F19*$C$20","v":6.2},{"$f":"G19*$C$20","v":5.84},{"$f":"H19*$C$20","v":6.4400000000000013}]
[null,"Beneficio neto",null,null,null,{"$f":"F19-F20","v":24.8},{"$f":"G19-G20","v":23.36},{"$f":"H19-H20","v":25.76}]
[null,"Dividendos pagados",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},{"$f":"$C22*H21","v":0}]
[null,null,null,null,null,null,null,null,"Promedio"]
[null,"EBITDA  / Inversión inicial",null,null,null,{"$f":"F15/$E$31","v":0.33333333333333331},{"$f":"G15/$E$31","v":0.33333333333333331},{"$f":"H15/$E$31","v":0.33333333333333331},{"$f":"AVERAGE(F24:H24)","v":0.33333333333333331}]
[null,"RONA (EBIT / AN)",null,null,null,{"$f":"F17/E32","v":0.13333333333333333},{"$f":"G17/F32","v":0.1111111111111111},{"$f":"H17/G32","v":0.13333333333333333},{"$f":"AVERAGE(F25:H25)","v":0.12592592592592591}]
[null,"ROA (Beneficio / AN)",null,null,null,{"$f":"F21/E32","v":8.2666666666666666E-2},{"$f":"G21/F32","v":6.4888888888888885E-2},{"$f":"H21/G32","v":8.5866666666666674E-2},{"$f":"AVERAGE(F26:H26)","v":7.7807407407407408E-2}]
[null,"ROE (Benef / Equity)",null,null,null,{"$f":"F21/E37","v":0.20666666666666667},{"$f":"G21/F37","v":0.1613259668508287},{"$f":"H21/G37","v":0.15318744053282587},{"$f":"AVERAGE(F27:H27)","v":0.17372669135010707}]
[]
[null,"Balance resumido",null,null,0,1,2,3,null,null,"Hipótesis de NOF"]
[null,"NOF",0.2,"x Ventas",null,{"$f":"$C$30*F11","v":120},{"$f":"$C$30*G11","v":120},0,null,null,"Clientes",90,null,"NOF COMO % VENTAS "]
[null,"AF neto",0,"Capex",300,{"$f":"E31-F16+$C31","v":240},{"$f":"F31-G16+$C31","v":180},0,null,null,"Existencias ",10,null,{"$f":"(L30+L31*0.85-L32*0.85)/365","v":0.2}]
[null,"AN activo  neto",null,null,{"$f":"E31+E30","v":300},{"$f":"F31+F30","v":360},{"$f":"G31+G30","v":300},{"$f":"H31+H30","v":0},null,null,"Proveedores",30]
[]
[null,"Línea de crédito D-Corto",0.8,"x NOF",{"$f":"$C34*E30","v":0},{"$f":"$C34*F30","v":96},{"$f":"$C34*G30","v":96},{"$f":"$C34*H30","v":0}]
[null,"Préstamos, D-Largo",3,"años",180,{"$f":"E35-($E35/$C35)","v":120},{"$f":"F35-($E35/$C35)","v":60},0]
[null,"D, Deuda total",null,null,{"$f":"E34+E35","v":180},{"$f":"F34+F35","v":216},{"$f":"G34+G35","v":156},{"$f":"H34+H35","v":0}]
[null,"E, Equity o Patrimonio",null,null,{"$f":"E31-E35","v":120},{"$f":"E37+F21-F22","v":144.80000000000001},{"$f":"F37+G21-G22","v":168.16000000000003},{"$f":"G37+H21-H22","v":193.92000000000002}]
[null,"Financiación",null,null,{"$f":"E36+E37","v":300},{"$f":"F36+F37","v":360.8},{"$f":"G36+G37","v":324.16000000000003},{"$f":"H36+H37","v":193.92000000000002}]
[]
[null,null,null,"Caja en el balance (+ o -)",{"$f":"E38-E32","v":0},{"$f":"F38-F32","v":0.80000000000001137},{"$f":"G38-G32","v":24.160000000000025},{"$f":"H38-H32","v":193.92000000000002}]
[null,null,null,"Aumento anual de caja",null,{"$f":"F40-E40","v":0.80000000000001137},{"$f":"G40-F40","v":23.360000000000014},{"$f":"H40-G40","v":169.76}]
[null,null,null,"ECF = Aumento de caja + dividendos pagados",{"$f":"-E37","v":-120},{"$f":"F41+F22","v":0.80000000000001137},{"$f":"G41+G22","v":23.360000000000014},{"$f":"H41+H22","v":169.76}]
[]
[null,"CF para el accionista o ECF",null,null,0,1,2,3]
[null,"Beneficio neto",null,null,null,{"$f":"F21","v":24.8},{"$f":"G21","v":23.36},{"$f":"H21","v":25.76}]
[null,"Variación de AN",null,null,{"$f":"-E32","v":-300},{"$f":"E32-F32","v":-60},{"$f":"F32-G32","v":60},{"$f":"G32-H32","v":300}]
[null,"Variación de Deuda",null,null,{"$f":"E36","v":180},{"$f":"F36-E36","v":36},{"$f":"G36-F36","v":-60},{"$f":"H36-G36","v":-156},"Suma de CF"]
[null,"CF accionista",null,null,{"$f":"SUM(E45:E47)","v":-120},{"$f":"SUM(F45:F47)","v":0.79999999999999716},{"$f":"SUM(G45:G47)","v":23.36},{"$f":"SUM(H45:H47)","v":169.76},{"$f":"SUM(E48:H48)","v":73.919999999999987}]
[null,"TIR  =",{"$f":"IRR(E48:H48)","v":0.18267186571826977}]
[]
[null,"FCF",null,null,0,1,2,3]
[null,"EBIT x (1-t)",null,null,null,{"$f":"F17*(1-$C20)","v":32},{"$f":"G17*(1-$C20)","v":32},{"$f":"H17*(1-$C20)","v":32}]
[null,"Variación de AN",null,null,{"$f":"E46","v":-300},{"$f":"F46","v":-60},{"$f":"G46","v":60},{"$f":"H46","v":300}]
[null,"FCF",null,null,{"$f":"SUM(E52:E53)","v":-300},{"$f":"SUM(F52:F53)","v":-28},{"$f":"SUM(G52:G53)","v":92},{"$f":"SUM(H52:H53)","v":332}]
[null,"IRR  =",{"$f":"IRR(E54:H54)","v":0.1000188925132417}]
[]
[]
[]
[null,"Riesgos. Análisis de sensibilidad",null,null,null,null,"CASO 2"]
[]
[null,null,"Riesgo Mayor"]
[null,"Escenarios","Base","Peor","Mejor","Rango"]
[null,"Precio",10,9,11]
[null,"TIR accionista",0.1,-5.1319434967612376E-2,0.24481039833418961,{"$f":"E64-D64","v":0.29612983330180198}]
[null,"Coste de la máquina AF",300,330,270]
[null,"TIR accionista",{"$f":"C64","v":0.1},7.9008814853287923E-2,0.12455999895101888,{"$f":"E66-D66","v":4.5551184097730957E-2}]
[null,"Tamaño de NOF",0.2,0.25,0.15]
[null,"TIR accionista",{"$f":"C64","v":0.1},9.4386474155067512E-2,0.10632719227147946,{"$f":"E68-D68","v":1.1940718116411952E-2}]
[null,"Riesgo Total",0.1,-5.8751578537388438E-2,0.30027686560496569,{"$f":"E69-D69","v":0.35902844414235413}]
[]
[]
[null,"TIR y nivel de Deuda",null,null,null,null,"CASO 3"]
[null,"Deuda","TIR","Aumento"]
[null,"60M€ o 20% del AF",0.128]
[null,"120M€ o 40% del AF",0.14899999999999999,{"$f":"C75-C74","v":2.0999999999999991E-2}]
[null,"180M€ o 60% del AF",0.183,{"$f":"C76-C75","v":3.4000000000000002E-2}]
[null,"240M€ o 80% del AF",0.251,{"$f":"C77-C76","v":6.8000000000000005E-2}]
[]
[null,null,null,null,null,null,"CASO 5"]
[]
[null,"CF para el accionista o ECF",null,0,1,2,3,"TIR","Suma de CF"]
[null,"Máquina nueva sin deuda (caso 1)",null,-300,-28,92,332,{"$f":"IRR(D82:G82)","v":0.1000188925132417},{"$f":"SUM(D82:G82)","v":96}]
[null,"Máquina nueva con deuda (caso 3)",null,-120,0.79999999999999716,23.36,169.76,{"$f":"IRR(D83:G83)","v":0.18267186571826977},{"$f":"SUM(D83:G83)","v":73.919999999999987}]
[null,"Máquina antigua CF (caso 5)",null,0,-88,32,152,{"$f":"IRR(D84:G84)","v":0.50859268357512954},{"$f":"SUM(D84:G84)","v":96}]

{"sheet":"Anex 4","index":5,"n_rows":84,"n_cols":14,"rows_kept":84,"cut":false,"hidden":true,"merged":[[71,1,71,3]]}
[null,null,null,null,"Caso 4. Proyecto con inflación. RESUELTO"]
[null,"Hipótesis en verde y negrita. Ver notas explicativas dentro de cada celda."]
[]
[null,"PyG Hipótesis","Hipótesis",null,null,1,2,3]
[null,"Número de piezas",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000}]
[null,"Precio de venta por unidad en euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":11},{"$f":"G6*(1+$C$8)","v":12.100000000000001}]
[null,"Coste por unidad en euros",8,null,null,{"$f":"C7","v":8},{"$f":"F7*(1+$C$8)","v":8.8000000000000007},{"$f":"G7*(1+$C$8)","v":9.6800000000000015}]
[null,"Inflación esperada",0.1]
[]
[null,"PyG (€ ’000)",null,null,null,1,2,3]
[null,"Ventas",null,null,null,{"$f":"F5*F6/1000","v":600},{"$f":"G5*G6/1000","v":660},{"$f":"H5*H6/1000","v":726.00000000000011}]
[null,"Coste de las ventas",null,null,null,{"$f":"F7*F5/1000","v":480},{"$f":"G7*G5/1000","v":528},{"$f":"H7*H5/1000","v":580.80000000000007}]
[null,"Margen Bruto",null,null,null,{"$f":"F11-F12","v":120},{"$f":"G11-G12","v":132},{"$f":"H11-H12","v":145.20000000000005}]
[null,"Gastos generales",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":22},{"$f":"G14*(1+$C8)","v":24.200000000000003}]
[null,"EBITDA",null,null,null,{"$f":"F13-F14","v":100},{"$f":"G13-G14","v":110},{"$f":"H13-H14","v":121.00000000000004}]
[null,"Amortización",5,"Años",null,{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60}]
[null,"EBIT",null,null,null,{"$f":"F15-F16","v":40},{"$f":"G15-G16","v":50},{"$f":"H15-H16","v":61.000000000000043}]
[null,"Intereses",0.05,null,null,{"$f":"E36*$C$18","v":0},{"$f":"F36*$C$18","v":0},{"$f":"G36*$C$18","v":0}]
[null,"Benef antes de impuestos",null,null,null,{"$f":"F17-F18","v":40},{"$f":"G17-G18","v":50},{"$f":"H17-H18","v":61.000000000000043}]
[null,"Impuestos",0.2,null,null,{"$f":"F19*$C$20","v":8},{"$f":"G19*$C$20","v":10},{"$f":"H19*$C$20","v":12.20000000000001}]
[null,"Beneficio neto",null,null,null,{"$f":"F19-F20","v":32},{"$f":"G19-G20","v":40},{"$f":"H19-H20","v":48.800000000000033}]
[null,"Dividendos pagados",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},{"$f":"$C22*H21","v":0}]
[null,null,null,null,null,null,null,null,"Promedio"]
[null,"EBITDA  / Inversión inicial",null,null,null,{"$f":"F15/$E$31","v":0.33333333333333331},{"$f":"G15/$E$31","v":0.36666666666666664},{"$f":"H15/$E$31","v":0.40333333333333349},{"$f":"AVERAGE(F24:H24)","v":0.36777777777777781}]
[null,"RONA (EBIT / AN)",null,null,null,{"$f":"F17/E32","v":0.13333333333333333},{"$f":"G17/F32","v":0.1388888888888889},{"$f":"H17/G32","v":0.19551282051282065},{"$f":"AVERAGE(F25:H25)","v":0.15591168091168098}]
[null,"ROA (Beneficio / AN)",null,null,null,{"$f":"F21/E32","v":0.10666666666666667},{"$f":"G21/F32","v":0.1111111111111111},{"$f":"H21/G32","v":0.15641025641025652},{"$f":"AVERAGE(F26:H26)","v":0.12472934472934477}]
[null,"ROE (Benef / Equity)",null,null,null,{"$f":"F21/E37","v":0.10666666666666667},{"$f":"G21/F37","v":0.12048192771084337},{"$f":"H21/G37","v":0.13118279569892483},{"$f":"AVERAGE(F27:H27)","v":0.11944379669214496}]
[]
[null,"Balance resumido",null,null,0,1,2,3,null,null,"Hipótesis de NOF"]
[null,"NOF",0.2,"x Ventas",null,{"$f":"$C$30*F11","v":120},{"$f":"$C$30*G11","v":132},0,null,null,"Clientes",90,null,"NOF COMO % VENTAS "]
[null,"AF neto",0,"Capex",300,{"$f":"E31-F16+$C31","v":240},{"$f":"F31-G16+$C31","v":180},0,null,null,"Existencias ",10,null,{"$f":"(L30+L31*0.85-L32*0.85)/365","v":0.2}]
[null,"AN activo  neto",null,null,{"$f":"E31+E30","v":300},{"$f":"F31+F30","v":360},{"$f":"G31+G30","v":312},{"$f":"H31+H30","v":0},null,null,"Proveedores",30]
[]
[null,"Línea de crédito D-Corto",0,"x NOF",{"$f":"$C34*E30","v":0},{"$f":"$C34*F30","v":0},{"$f":"$C34*G30","v":0},{"$f":"$C34*H30","v":0}]
[null,"Préstamos, D-Largo",3,"años",0,{"$f":"E35-($E35/$C35)","v":0},{"$f":"F35-($E35/$C35)","v":0},0]
[null,"D, Deuda total",null,null,{"$f":"E34+E35","v":0},{"$f":"F34+F35","v":0},{"$f":"G34+G35","v":0},{"$f":"H34+H35","v":0}]
[null,"E, Equity o Patrimonio",null,null,{"$f":"E31-E35","v":300},{"$f":"E37+F21-F22","v":332},{"$f":"F37+G21-G22","v":372},{"$f":"G37+H21-H22","v":420.8}]
[null,"Financiación",null,null,{"$f":"E36+E37","v":300},{"$f":"F36+F37","v":332},{"$f":"G36+G37","v":372},{"$f":"H36+H37","v":420.8}]
[]
[null,null,null,"Caja en el balance (+ o -)",{"$f":"E38-E32","v":0},{"$f":"F38-F32","v":-28},{"$f":"G38-G32","v":60},{"$f":"H38-H32","v":420.8}]
[null,null,null,"Aumento anual de caja",null,{"$f":"F40-E40","v":-28},{"$f":"G40-F40","v":88},{"$f":"H40-G40","v":360.8}]
[null,null,null,"ECF = Aumento de caja + dividendos pagados",{"$f":"-E37","v":-300},{"$f":"F41+F22","v":-28},{"$f":"G41+G22","v":88},{"$f":"H41+H22","v":360.8}]
[]
[null,"CF para el accionista o ECF",null,null,0,1,2,3]
[null,"Beneficio neto",null,null,null,{"$f":"F21","v":32},{"$f":"G21","v":40},{"$f":"H21","v":48.800000000000033}]
[null,"Variación de AN",null,null,{"$f":"-E32","v":-300},{"$f":"E32-F32","v":-60},{"$f":"F32-G32","v":48},{"$f":"G32-H32","v":312}]
[null,"Variación de Deuda",null,null,{"$f":"E36","v":0},{"$f":"F36-E36","v":0},{"$f":"G36-F36","v":0},{"$f":"H36-G36","v":0},"Suma de CF"]
[null,"CF accionista",null,null,{"$f":"SUM(E45:E47)","v":-300},{"$f":"SUM(F45:F47)","v":-28},{"$f":"SUM(G45:G47)","v":88},{"$f":"SUM(H45:H47)","v":360.8},{"$f":"SUM(E48:H48)","v":120.80000000000001}]
[null,"TIR  =",{"$f":"IRR(E48:H48)","v":0.12249343497344056}]
[]
[null,"FCF",null,null,0,1,2,3]
[null,"EBIT x (1-t)",null,null,null,{"$f":"F17*(1-$C20)","v":32},{"$f":"G17*(1-$C20)","v":40},{"$f":"H17*(1-$C20)","v":48.80000000000004}]
[null,"Variación de AN",null,null,{"$f":"E46","v":-300},{"$f":"F46","v":-60},{"$f":"G46","v":48},{"$f":"H46","v":312}]
[null,"FCF",null,null,{"$f":"SUM(E52:E53)","v":-300},{"$f":"SUM(F52:F53)","v":-28},{"$f":"SUM(G52:G53)","v":88},{"$f":"SUM(H52:H53)","v":360.80000000000007}]
[null,"IRR  =",{"$f":"IRR(E54:H54)","v":0.12249343497344078}]
[]
[]
[]
[null,"Riesgos. Análisis de sensibilidad",null,null,null,null,"CASO 2"]
[]
[null,null,"Riesgo Mayor"]
[null,"Escenarios","Base","Peor","Mejor","Rango"]
[null,"Precio",10,9,11]
[null,"TIR accionista",0.1,-5.1319434967612376E-2,0.24481039833418961,{"$f":"E64-D64","v":0.29612983330180198}]
[null,"Coste de la máquina AF",300,330,270]
[null,"TIR accionista",{"$f":"C64","v":0.1},7.9008814853287923E-2,0.12455999895101888,{"$f":"E66-D66","v":4.5551184097730957E-2}]
[null,"Tamaño de NOF",0.2,0.25,0.15]
[null,"TIR accionista",{"$f":"C64","v":0.1},9.4386474155067512E-2,0.10632719227147946,{"$f":"E68-D68","v":1.1940718116411952E-2}]
[null,"Riesgo Total",0.1,-5.8751578537388438E-2,0.30027686560496569,{"$f":"E69-D69","v":0.35902844414235413}]
[]
[]
[null,"TIR y nivel de Deuda",null,null,null,null,"CASO 3"]
[null,"Deuda","TIR","Aumento"]
[null,"60M€ o 20% del AF",0.128]
[null,"120M€ o 40% del AF",0.14899999999999999,{"$f":"C75-C74","v":2.0999999999999991E-2}]
[null,"180M€ o 60% del AF",0.183,{"$f":"C76-C75","v":3.4000000000000002E-2}]
[null,"240M€ o 80% del AF",0.251,{"$f":"C77-C76","v":6.8000000000000005E-2}]
[]
[null,null,null,null,null,null,"CASO 5"]
[]
[null,"CF para el accionista o ECF",null,0,1,2,3,"TIR","Suma de CF"]
[null,"Máquina nueva sin deuda (caso 1)",null,-300,-28,92,332,{"$f":"IRR(D82:G82)","v":0.1000188925132417},{"$f":"SUM(D82:G82)","v":96}]
[null,"Máquina nueva con deuda (caso 3)",null,-120,0.79999999999999716,23.36,169.76,{"$f":"IRR(D83:G83)","v":0.18267186571826977},{"$f":"SUM(D83:G83)","v":73.919999999999987}]
[null,"Máquina antigua CF (caso 5)",null,0,-88,32,152,{"$f":"IRR(D84:G84)","v":0.50859268357512954},{"$f":"SUM(D84:G84)","v":96}]

{"sheet":"Anex 5","index":6,"n_rows":84,"n_cols":14,"rows_kept":84,"cut":false,"hidden":true,"merged":[[71,1,71,3]]}
[null,null,null,null,"Caso 5. Comparación de dos proyectos. RESUELTO"]
[null,"Hipótesis en verde y negrita. Ver notas explicativas dentro de cada celda."]
[]
[null,"PyG Hipótesis","Hipótesis",null,null,1,2,3]
[null,"Número de piezas",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000}]
[null,"Precio de venta por unidad en euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":10},{"$f":"G6*(1+$C$8)","v":10}]
[null,"Coste por unidad en euros",9,null,null,{"$f":"C7","v":9},{"$f":"F7*(1+$C$8)","v":9},{"$f":"G7*(1+$C$8)","v":9}]
[null,"Inflación esperada",0]
[]
[null,"PyG (€ ’000)",null,null,null,1,2,3]
[null,"Ventas",null,null,null,{"$f":"F5*F6/1000","v":600},{"$f":"G5*G6/1000","v":600},{"$f":"H5*H6/1000","v":600}]
[null,"Coste de las ventas",null,null,null,{"$f":"F7*F5/1000","v":540},{"$f":"G7*G5/1000","v":540},{"$f":"H7*H5/1000","v":540}]
[null,"Margen Bruto",null,null,null,{"$f":"F11-F12","v":60},{"$f":"G11-G12","v":60},{"$f":"H11-H12","v":60}]
[null,"Gastos generales",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":20},{"$f":"G14*(1+$C8)","v":20}]
[null,"EBITDA",null,null,null,{"$f":"F13-F14","v":40},{"$f":"G13-G14","v":40},{"$f":"H13-H14","v":40}]
[null,"Amortización",5,"Años",null,{"$f":"$E31*(1/$C$16)","v":0},{"$f":"$E31*(1/$C$16)","v":0},{"$f":"$E31*(1/$C$16)","v":0}]
[null,"EBIT",null,null,null,{"$f":"F15-F16","v":40},{"$f":"G15-G16","v":40},{"$f":"H15-H16","v":40}]
[null,"Intereses",0.05,null,null,{"$f":"E36*$C$18","v":0},{"$f":"F36*$C$18","v":0},{"$f":"G36*$C$18","v":0}]
[null,"Benef antes de impuestos",null,null,null,{"$f":"F17-F18","v":40},{"$f":"G17-G18","v":40},{"$f":"H17-H18","v":40}]
[null,"Impuestos",0.2,null,null,{"$f":"F19*$C$20","v":8},{"$f":"G19*$C$20","v":8},{"$f":"H19*$C$20","v":8}]
[null,"Beneficio neto",null,null,null,{"$f":"F19-F20","v":32},{"$f":"G19-G20","v":32},{"$f":"H19-H20","v":32}]
[null,"Dividendos pagados",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},{"$f":"$C22*H21","v":0}]
[null,null,null,null,null,null,null,null,"Promedio"]
[null,"EBITDA  / Inversión inicial",null,null,null,{"$f":"F15/$E$31","v":{"$error":"#DIV/0!"}},{"$f":"G15/$E$31","v":{"$error":"#DIV/0!"}},{"$f":"H15/$E$31","v":{"$error":"#DIV/0!"}},{"$f":"AVERAGE(F24:H24)","v":{"$error":"#DIV/0!"}}]
[null,"RONA (EBIT / AN)",null,null,null,{"$f":"F17/E32","v":{"$error":"#DIV/0!"}},{"$f":"G17/F32","v":0.33333333333333331},{"$f":"H17/G32","v":0.33333333333333331},{"$f":"AVERAGE(F25:H25)","v":{"$error":"#DIV/0!"}}]
[null,"ROA (Beneficio / AN)",null,null,null,{"$f":"F21/E32","v":{"$error":"#DIV/0!"}},{"$f":"G21/F32","v":0.26666666666666666},{"$f":"H21/G32","v":0.26666666666666666},{"$f":"AVERAGE(F26:H26)","v":{"$error":"#DIV/0!"}}]
[null,"ROE (Benef / Equity)",null,null,null,{"$f":"F21/E37","v":{"$error":"#DIV/0!"}},{"$f":"G21/F37","v":1},{"$f":"H21/G37","v":0.5},{"$f":"AVERAGE(F27:H27)","v":{"$error":"#DIV/0!"}}]
[]
[null,"Balance resumido",null,null,0,1,2,3,null,null,"Hipótesis de NOF"]
[null,"NOF",0.2,"x Ventas",null,{"$f":"$C$30*F11","v":120},{"$f":"$C$30*G11","v":120},0,null,null,"Clientes",90,null,"NOF COMO % VENTAS "]
[null,"AF neto",0,"Capex",0,{"$f":"E31-F16+$C31","v":0},{"$f":"F31-G16+$C31","v":0},0,null,null,"Existencias ",10,null,{"$f":"(L30+L31*0.85-L32*0.85)/365","v":0.2}]
[null,"AN activo  neto",null,null,{"$f":"E31+E30","v":0},{"$f":"F31+F30","v":120},{"$f":"G31+G30","v":120},{"$f":"H31+H30","v":0},null,null,"Proveedores",30]
[]
[null,"Línea de crédito D-Corto",0,"x NOF",{"$f":"$C34*E30","v":0},{"$f":"$C34*F30","v":0},{"$f":"$C34*G30","v":0},{"$f":"$C34*H30","v":0}]
[null,"Préstamos, D-Largo",3,"años",0,{"$f":"E35-($E35/$C35)","v":0},{"$f":"F35-($E35/$C35)","v":0},0]
[null,"D, Deuda total",null,null,{"$f":"E34+E35","v":0},{"$f":"F34+F35","v":0},{"$f":"G34+G35","v":0},{"$f":"H34+H35","v":0}]
[null,"E, Equity o Patrimonio",null,null,{"$f":"E31-E35","v":0},{"$f":"E37+F21-F22","v":32},{"$f":"F37+G21-G22","v":64},{"$f":"G37+H21-H22","v":96}]
[null,"Financiación",null,null,{"$f":"E36+E37","v":0},{"$f":"F36+F37","v":32},{"$f":"G36+G37","v":64},{"$f":"H36+H37","v":96}]
[]
[null,null,null,"Caja en el balance (+ o -)",{"$f":"E38-E32","v":0},{"$f":"F38-F32","v":-88},{"$f":"G38-G32","v":-56},{"$f":"H38-H32","v":96}]
[null,null,null,"Aumento anual de caja",null,{"$f":"F40-E40","v":-88},{"$f":"G40-F40","v":32},{"$f":"H40-G40","v":152}]
[null,null,null,"ECF = Aumento de caja + dividendos pagados",{"$f":"-E37","v":0},{"$f":"F41+F22","v":-88},{"$f":"G41+G22","v":32},{"$f":"H41+H22","v":152}]
[]
[null,"CF para el accionista o ECF",null,null,0,1,2,3]
[null,"Beneficio neto",null,null,null,{"$f":"F21","v":32},{"$f":"G21","v":32},{"$f":"H21","v":32}]
[null,"Variación de AN",null,null,{"$f":"-E32","v":0},{"$f":"E32-F32","v":-120},{"$f":"F32-G32","v":0},{"$f":"G32-H32","v":120}]
[null,"Variación de Deuda",null,null,{"$f":"E36","v":0},{"$f":"F36-E36","v":0},{"$f":"G36-F36","v":0},{"$f":"H36-G36","v":0},"Suma de CF"]
[null,"CF accionista",null,null,{"$f":"SUM(E45:E47)","v":0},{"$f":"SUM(F45:F47)","v":-88},{"$f":"SUM(G45:G47)","v":32},{"$f":"SUM(H45:H47)","v":152},{"$f":"SUM(E48:H48)","v":96}]
[null,"TIR  =",{"$f":"IRR(E48:H48)","v":0.50859268357512954}]
[]
[null,"FCF",null,null,0,1,2,3]
[null,"EBIT x (1-t)",null,null,null,{"$f":"F17*(1-$C20)","v":32},{"$f":"G17*(1-$C20)","v":32},{"$f":"H17*(1-$C20)","v":32}]
[null,"Variación de AN",null,null,{"$f":"E46","v":0},{"$f":"F46","v":-120},{"$f":"G46","v":0},{"$f":"H46","v":120}]
[null,"FCF",null,null,{"$f":"SUM(E52:E53)","v":0},{"$f":"SUM(F52:F53)","v":-88},{"$f":"SUM(G52:G53)","v":32},{"$f":"SUM(H52:H53)","v":152}]
[null,"IRR  =",{"$f":"IRR(E54:H54)","v":0.50859268357512954}]
[]
[]
[]
[null,"Riesgos. Análisis de sensibilidad",null,null,null,null,"CASO 2"]
[]
[null,null,"Riesgo Mayor"]
[null,"Escenarios","Base","Peor","Mejor","Rango"]
[null,"Precio",10,9,11]
[null,"TIR accionista",0.1,-5.1319434967612376E-2,0.24481039833418961,{"$f":"E64-D64","v":0.29612983330180198}]
[null,"Coste de la máquina AF",300,330,270]
[null,"TIR accionista",{"$f":"C64","v":0.1},7.9008814853287923E-2,0.12455999895101888,{"$f":"E66-D66","v":4.5551184097730957E-2}]
[null,"Tamaño de NOF",0.2,0.25,0.15]
[null,"TIR accionista",{"$f":"C64","v":0.1},9.4386474155067512E-2,0.10632719227147946,{"$f":"E68-D68","v":1.1940718116411952E-2}]
[null,"Riesgo Total",0.1,-5.8751578537388438E-2,0.30027686560496569,{"$f":"E69-D69","v":0.35902844414235413}]
[]
[]
[null,"TIR y nivel de Deuda",null,null,null,null,"CASO 3"]
[null,"Deuda","TIR","Aumento"]
[null,"60M€ o 20% del AF",0.128]
[null,"120M€ o 40% del AF",0.14899999999999999,{"$f":"C75-C74","v":2.0999999999999991E-2}]
[null,"180M€ o 60% del AF",0.183,{"$f":"C76-C75","v":3.4000000000000002E-2}]
[null,"240M€ o 80% del AF",0.251,{"$f":"C77-C76","v":6.8000000000000005E-2}]
[]
[null,null,null,null,null,null,"CASO 5"]
[]
[null,"CF para el accionista o ECF",null,0,1,2,3,"TIR","Suma de CF"]
[null,"Máquina nueva sin deuda (caso 1)",null,-300,-28,92,332,{"$f":"IRR(D82:G82)","v":0.1000188925132417},{"$f":"SUM(D82:G82)","v":96}]
[null,"Máquina nueva con deuda (caso 3)",null,-120,0.79999999999999716,23.36,169.76,{"$f":"IRR(D83:G83)","v":0.18267186571826977},{"$f":"SUM(D83:G83)","v":73.919999999999987}]
[null,"Máquina antigua CF (caso 5)",null,0,-88,32,152,{"$f":"IRR(D84:G84)","v":0.50859268357512954},{"$f":"SUM(D84:G84)","v":96}]

{"sheet":"Anex 6&7","index":7,"n_rows":57,"n_cols":17,"rows_kept":57,"cut":false,"hidden":true,"merged":[]}
[null,null,null,null,"Caso 6. Proyecto de costes. ¿Compra o Renting? RESUELTO"]
[null,"Hipótesis en verde y negrita. Ver notas explicativas dentro de cada celda."]
[null,null,null,null,null,null,"Compra"]
[null,"P&L ","Hipótesis",null,null,1,2,3,null,"Renting"]
[null,"EBITDA",100,null,null,{"$f":"$C5","v":100},{"$f":"$C5","v":100},{"$f":"$C5","v":100},null,{"$f":"$C5","v":100}]
[null,"Amortización o pago renting",5,"años",null,{"$f":"E15/C6","v":60},{"$f":"F6","v":60},{"$f":"G6","v":60},null,85]
[null,"EBIT",null,null,null,{"$f":"F5-F6","v":40},{"$f":"G5-G6","v":40},{"$f":"H5-H6","v":40},null,{"$f":"J5-J6","v":15},null,null,"Renting"]
[null,"Intereses",0.05,null,null,{"$f":"$C8*E20","v":9},{"$f":"$C8*F20","v":10.8},{"$f":"$C8*G20","v":7.8000000000000007},null,{"$f":"$C8*F18","v":4.8000000000000007},null,null,-300]
[null,"Benef antes de impuestos",null,null,null,{"$f":"F7-F8","v":31},{"$f":"G7-G8","v":29.2},{"$f":"H7-H8","v":32.200000000000003},null,{"$f":"J7-J8","v":10.199999999999999},null,null,85]
[null,"Impuestos",0.2,null,null,{"$f":"$C10*F9","v":6.2},{"$f":"$C10*G9","v":5.84},{"$f":"$C10*H9","v":6.4400000000000013},null,{"$f":"J9*C10","v":2.04},null,null,{"$f":"M9","v":85}]
[null,"Beneficio ",null,null,null,{"$f":"F9-F10","v":24.8},{"$f":"G9-G10","v":23.36},{"$f":"H9-H10","v":25.76},null,{"$f":"J9-J10","v":8.16},null,null,{"$f":"M10","v":85}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"M11","v":85}]
[null,"Balance con Compra",null,null,0,1,2,3,null,null,null,null,{"$f":"M12","v":85}]
[null,"NOF",null,null,null,120,{"$f":"$F$14","v":120},0]
[null,"AF (máquina)",null,null,300,{"$f":"E15-$F6","v":240},{"$f":"F15-$F6","v":180},0,null,null,null,null,{"$f":"IRR(M8:M13)","v":0.12858463526289921}]
[null,"AN activo  neto",null,null,{"$f":"E14+E15","v":300},{"$f":"F14+F15","v":360},{"$f":"G14+G15","v":300},{"$f":"H15+H14","v":0}]
[]
[null,"Línea de crédito D-Corto",0.8,null,null,{"$f":"$C18*F14","v":96},{"$f":"$C18*G14","v":96},{"$f":"$C18*H14","v":0}]
[null,"Préstamos, D-Largo",3,"años",180,{"$f":"E19-($E19/$C19)","v":120},{"$f":"F19-($E19/$C19)","v":60},0]
[null,"D, Deuda total",null,null,{"$f":"E18+E19","v":180},{"$f":"F18+F19","v":216},{"$f":"G18+G19","v":156},{"$f":"H18+H19","v":0}]
[null,"E, Equity o Patrimonio",null,null,{"$f":"E15-E19","v":120},{"$f":"E21+$F$11","v":144.80000000000001},{"$f":"F21+$F$11","v":169.60000000000002},{"$f":"G21+$F$11","v":194.40000000000003}]
[null,"Financiación",null,null,{"$f":"E20+E21","v":300},{"$f":"F20+F21","v":360.8},{"$f":"G20+G21","v":325.60000000000002},{"$f":"H20+H21","v":194.40000000000003}]
[null,null,null,"Caja en el balance (+ o -)",{"$f":"E22-E16","v":0},{"$f":"F22-F16","v":0.80000000000001137},{"$f":"G22-G16","v":25.600000000000023},{"$f":"H22-H16","v":194.40000000000003}]
[null,null,null,"Caja generada CF",{"$f":"-E21","v":-120},{"$f":"F23-E23","v":0.80000000000001137},{"$f":"G23-F23","v":24.800000000000011},{"$f":"H23-G23","v":168.8}]
[]
[null,"CF accionista. Compra",null,null,0,1,2,3]
[null,"Variación de AN",null,null,{"$f":"-E16","v":-300},{"$f":"E16-F16","v":-60},{"$f":"F16-G16","v":60},{"$f":"G16-H16","v":300}]
[null,"Variación de Deuda",null,null,{"$f":"E20","v":180},{"$f":"F20-E20","v":36},{"$f":"G20-F20","v":-60},{"$f":"H20-G20","v":-156}]
[null,"Beneficio neto (con compra)",null,null,null,{"$f":"$F$11","v":24.8},{"$f":"$F$11","v":24.8},{"$f":"$F$11","v":24.8},null,"Suma de CF"]
[null,"CF accionista (Compra)",null,null,{"$f":"E27+E28+E29","v":-120},{"$f":"F27+F28+F29","v":0.80000000000000071},{"$f":"G27+G28+G29","v":24.8},{"$f":"H27+H28+H29","v":168.8},null,{"$f":"SUM(E30:H30)","v":74.400000000000006}]
[]
[null,"Rentabilidad requerida (K) = ",0.13,null,"VAN =",{"$f":"NPV(C32,F30:H30)+E30","v":17.11686974191511},"TIR =",{"$f":"IRR(E30:H30)","v":0.18422796692630294}]
[]
[null,"CF accionista. Renting",null,null,0,1,2,3]
[null,"Variación de NOF",null,null,null,{"$f":"E14-F14","v":-120},{"$f":"F14-G14","v":0},{"$f":"G14-H14","v":120}]
[null,"Cambios en línea de crédito",null,null,null,{"$f":"F18-E18","v":96},{"$f":"G18-F18","v":0},{"$f":"H18-G18","v":-96}]
[null,"Beneficio, con renting",null,null,0,{"$f":"$J$11","v":8.16},{"$f":"$J$11","v":8.16},{"$f":"$J$11","v":8.16},null,"Suma de CF"]
[null,"CF accionista (Renting)",null,null,{"$f":"E35+E36+E37","v":0},{"$f":"F35+F36+F37","v":-15.84},{"$f":"G35+G36+G37","v":8.16},{"$f":"H35+H36+H37","v":32.159999999999997},null,{"$f":"SUM(E38:H38)","v":24.479999999999997}]
[]
[null,null,null,null,"VAN =",{"$f":"NPV(C32,F38:H38)+E38","v":14.66127104013662},"TIR =",{"$f":"IRR(E38:H38)","v":0.70555652443481698}]
[]
[]
[]
[null,null,null,null,"Caso 7. Comparar Proyectos. RESUELTO"]
[]
["Caso","CF para el accionista o ECF",null,0,1,2,3,"TIR",null,"Suma de CF"]
[1,"Máquina nueva, sin deuda",null,-300,-28,92,332,0.1000188925132417,null,{"$f":"SUM(D47:G47)","v":96},null,null,null,null,null,null,"New machine, no debt"]
[2,"Riesgos. Escenario peor",null,-330,-89.8,45.2,312.2,-5.8751578537388438E-2,null,-62.400000000000034,null,null,null,null,null,null,"Risks Worst"]
[2,"Riesgos. Escenario mejor",null,-270,39.799999999999997,138.80000000000001,345.8,0.30027686560496547,null,254.40000000000003,null,null,null,null,null,null,"Risks Best"]
[3,"Maquina nueva con deuda",null,-120,0.79999999999999716,23.36,169.76,0.18267186571826977,null,73.919999999999987,null,null,null,null,null,null,"New machine with debt"]
["3b","Máquina nueva con deuda. Peor escenario",null,-150,-49,-23.92,137.48000000000002,-0.17459138654184225,null,-85.44,null,null,null,null,null,null,"New machine with debt, Worst case"]
["3c","Maquina nueva con deuda. Mejor escenario",null,-90,51.799999999999983,70.831999999999994,201.03199999999998,0.7522264968437915,null,233.66399999999996,null,null,null,null,null,null,"New machine with debt, Best case"]
[4,"Máquina nueva con inflación, sin deuda.",null,-300,-28,88,360.8,0.12249343497344056,null,120.80000000000001,null,null,null,null,null,null,"New machine with inflation, no debt"]
["4b","Nueva máquina con inflación y deuda",null,-120,0.79999999999999716,28.960000000000022,188.57600000000002,0.2340795222783163,null,98.336000000000041,null,null,null,null,null,null,"New machine with inflation and debt"]
[5,"Maquina vieja, con 0 deuda",null,0,-88,32,152,0.50859268357512954,null,96,null,null,null,null,null,null,"Old machine, with 0 debt "]
["5b","Maquina vieja, con deuda",null,0,8,28.160000000000004,52.16,"na",null,88.32,null,null,null,null,null,null,"Old machine, with debt"]
[6,"Renting vs Comprar",null,0,-15.84,8.16,32.159999999999997,0.71,null,24.479999999999997,null,null,null,null,null,null,"Renting vs Buy"]

{"sheet":"Cases 1-5 TO DO","index":8,"n_rows":84,"n_cols":14,"rows_kept":84,"cut":false,"hidden":false,"merged":[[71,1,71,3]]}
[null,null,null,null,"TEMPLATE TO SOLVE CASES 1 to 5"]
[null,"Assumptions in bold and green. To do the calculations, see the notes inside of the cells."]
[]
[null,"P&L assumptions","Assumptions",null,null,1,2,3]
[null,"Pieces produced",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000}]
[null,"Unit selling price in euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":10},{"$f":"G6*(1+$C$8)","v":10}]
[null,"Unit cost in euros",8,null,null,{"$f":"C7","v":8},{"$f":"F7*(1+$C$8)","v":8},{"$f":"G7*(1+$C$8)","v":8}]
[null,"Expected inflation",0]
[]
[null,"P&L (€ ’000)",null,null,null,1,2,3]
[null,"Sales"]
[null,"Cost of Goods Sold"]
[null,"Gross Margin"]
[null,"Operating Expenses",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":20},{"$f":"G14*(1+$C8)","v":20}]
[null,"EBITDA"]
[null,"Depreciation",5,"Year"]
[null,"EBIT",null,null,null,{"$f":"F15-F16","v":0},{"$f":"G15-G16","v":0},{"$f":"H15-H16","v":0}]
[null,"Interest expense",0.05]
[null,"Earnings before tax",null,null,null,{"$f":"F17-F18","v":0},{"$f":"G17-G18","v":0},{"$f":"H17-H18","v":0}]
[null,"Tax ",0.2,null,null,{"$f":"F19*$C$20","v":0},{"$f":"G19*$C$20","v":0},{"$f":"H19*$C$20","v":0}]
[null,"Net Income",null,null,null,{"$f":"F19-F20","v":0},{"$f":"G19-G20","v":0},{"$f":"H19-H20","v":0}]
[null,"Dividends paid",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},0]
[null,null,null,null,null,null,null,null,"Average"]
[null,"EBITDA  / Initial investment ",null,null,null,{"$f":"F15/$E$31","v":0},{"$f":"G15/$E$31","v":0},{"$f":"H15/$E$31","v":0},{"$f":"AVERAGE(F24:H24)","v":0}]
[null,"RONA (EBIT / NA)",null,null,null,{"$f":"F17/E32","v":0},{"$f":"G17/F32","v":{"$error":"#DIV/0!"}},{"$f":"H17/G32","v":{"$error":"#DIV/0!"}},{"$f":"AVERAGE(F25:H25)","v":{"$error":"#DIV/0!"}}]
[null,"ROA (Net Income / NA)",null,null,null,{"$f":"F21/E32","v":0},{"$f":"G21/F32","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"},{"$f":"AVERAGE(F26:H26)","v":{"$error":"#DIV/0!"}}]
[null,"ROE (Net Inc  / Equity)",null,null,null,{"$f":"F21/E37","v":{"$error":"#DIV/0!"}},{"$f":"G21/F37","v":{"$error":"#DIV/0!"}},{"$f":"H21/G37","v":{"$error":"#DIV/0!"}},{"$f":"AVERAGE(F27:H27)","v":{"$error":"#DIV/0!"}}]
[]
[null,"Short Balance Sheet",null,null,0,1,2,3,null,null,"Assumptions for NFO"]
[null,"NFO (20% Sales)",0.2,"x Sales",null,{"$f":"$C$30*F11","v":0},{"$f":"$C$30*G11","v":0},0,null,null,"Receivables",90,null,"NFO AS % OF SALES"]
[null,"FA, Net ",0,"Capex",300,null,null,null,null,null,"Inventory",10,null,{"$f":"(L30+L31*0.85-L32*0.85)/365","v":0.2}]
[null,"NA, Net Assets",null,null,{"$f":"E31+E30","v":300},{"$f":"F31+F30","v":0},{"$f":"G31+G30","v":0},{"$f":"H31+H30","v":0},null,null,"Payables",30]
[]
[null,"Line of credit D-S",0,"of NFO",{"$f":"$C34*E30","v":0},{"$f":"$C34*F30","v":0},{"$f":"$C34*G30","v":0},0]
[null,"Loan D-L",3,"years",0,{"$f":"E35-($E35/$C35)","v":0},{"$f":"F35-($E35/$C35)","v":0},0]
[null,"D, Total Debt",null,null,{"$f":"E34+E35","v":0},{"$f":"F34+F35","v":0},{"$f":"G34+G35","v":0},0]
[null,"E, Equity"]
[null,"Total financing",null,null,{"$f":"E36+E37","v":0},{"$f":"F36+F37","v":0},0,0]
[]
[null,null,null,"Cash on balance sheet (+ or -)"]
[null,null,null,"Annual increase in cash"]
[null,null,null,"ECF = Increase of cash + dividends paid"]
[]
[null,"CF for the shareholder or ECF",null,null,0,1,2,3]
[null,"Net Income"]
[null,"Change in NA"]
[null,"Change in Debt",null,null,null,null,null,null,"Sum CF"]
[null,"Equity CF or CF shareholder",null,null,{"$f":"SUM(E45:E47)","v":0},{"$f":"SUM(F45:F47)","v":0},{"$f":"SUM(G45:G47)","v":0},{"$f":"SUM(H45:H47)","v":0},{"$f":"SUM(E48:H48)","v":0}]
[null,"IRR  =",{"$f":"IRR(E48:H48)","v":{"$error":"#NUM!"}}]
[]
[null,"FCF after tax",null,null,0,1,2,3]
[null,"EBIT x (1-t)"]
[null,"Change in NA"]
[null,"FCF after tax",null,null,{"$f":"SUM(E52:E53)","v":0},{"$f":"SUM(F52:F53)","v":0},{"$f":"SUM(G52:G53)","v":0},{"$f":"SUM(H52:H53)","v":0}]
[null,"IRR  =",{"$f":"IRR(E54:H54)","v":{"$error":"#NUM!"}}]
[]
[]
[]
[null,"Risks. Sensitivity Analysis.",null,null,null,null,"FOR CASE 2"]
[]
[null,null,null,"Biggest Risk"]
[null,"Scenarios","Base","Worst","Best","Range"]
[null,"Price",10,9,11]
[null,"IRR shareholder"]
[null,"Cost of Machine FA",300,330,270]
[null,"IRR shareholder"]
[null,"Size of NFO%",0.2,0.25,0.15]
[null,"IRR shareholder",null,null,null,{"$f":"E68-D68","v":0}]
[null,"Total Risk ",null,null,null,{"$f":"E69-D69","v":0}]
[]
[]
[null,"IRR  and Level of Debt",null,null,null,null,"FOR CASE 3"]
[null,"Debt","IRR","Increase"]
[null,"60M€ or 20% of FA"]
[null,"120M€ or 40% of FA"]
[null,"180M€ or 60% of FA"]
[null,"240M€ or 80% of FA"]
[]
[null,null,null,null,null,null,"FOR CASE 5"]
[]
[null,"CF shareholder or ECF",null,0,1,2,3,"IRR","Sum of CF"]
[null,"New machine without debt (case1)",null,-300,-28,92,332,{"$f":"IRR(D82:G82)","v":0.1000188925132417},{"$f":"SUM(D82:G82)","v":96}]
[null,"New machine with debt (case 3)",null,-120,0.79999999999999716,23.36,169.76,0.18267186571826977,73.919999999999987]
[null,"Old machine (case 5)",null,0,-88,32,152,{"$f":"IRR(D84:G84)","v":0.50859268357512954},{"$f":"SUM(D84:G84)","v":96}]

{"sheet":"Case 6 TO DO","index":9,"n_rows":57,"n_cols":13,"rows_kept":57,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,"Case 6. Cost reduction project.  Buy or Rent? TO DO"]
[null,"Assumptions in green and bold"]
[null,null,null,null,null,null,"Buy"]
[null,"P&L ","Assumptions",null,null,1,2,3,null,"Rent"]
[null,"EBITDA",100,null,null,{"$f":"$C5","v":100},{"$f":"$C5","v":100},100,null,{"$f":"$C5","v":100}]
[null,"Depreciation or lease payment",5,"years",null,null,null,null,null,85]
[null,"EBIT",null,null,null,{"$f":"F5-F6","v":100},{"$f":"G5-G6","v":100},100,null,{"$f":"J5-J6","v":15},null,null,"Rent"]
[null,"Interest Expense",0.05,null,null,null,null,null,null,null,null,null,-300]
[null,"Earnings before tax",null,null,null,null,null,null,null,null,null,null,85]
[null,"Tax rate",0.2,null,null,null,null,null,null,null,null,null,{"$f":"M9","v":85}]
[null,"NI, Net Income",null,null,null,null,null,null,null,null,null,null,{"$f":"M10","v":85}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"M11","v":85}]
[null,"Balance sheet when buying",null,null,0,1,2,3,null,null,null,null,{"$f":"M12","v":85}]
[null,"NFO",null,null,null,120,null,0]
[null,"FA (machine)",null,null,300,null,null,0,null,null,null,null,{"$f":"IRR(M8:M13)","v":0.12858463526289921}]
[null,"NA, Net assets"]
[]
[null,"Line of credit (D-Short term)",0.8]
[null,"Loan (D-Long term)",3,"years",180]
[null,"D, Total Debt"]
[null,"E, Equity "]
[null,"Equity + Debt"]
[null,null,null,"Cash Surplus"]
[null,null,null,"Cash generated CF"]
[]
[null,"CF shareholder when buying",null,null,0,1,2,3]
[null,"Change in assets ",null,null,{"$f":"-E16","v":0},{"$f":"E16-F16","v":0},{"$f":"F16-G16","v":0},{"$f":"G16-H16","v":0}]
[null,"Change in debt",null,null,{"$f":"E20","v":0},{"$f":"F20-E20","v":0},{"$f":"G20-F20","v":0},0]
[null,"Net Income",null,null,null,{"$f":"$F$11","v":0},{"$f":"$F$11","v":0},{"$f":"$F$11","v":0},null,"Sum of CF"]
[null,"CF shareholder (Buy)",null,null,{"$f":"E27+E28+E29","v":0},0,0,0,null,{"$f":"SUM(E30:H30)","v":0}]
[]
[null,"Required return (K) = ",0.13,null,"NPV =",{"$f":"NPV(C32,F30:H30)+E30","v":0},"IRR =",{"$f":"IRR(E30:H30)","v":{"$error":"#NUM!"}}]
[]
[null,"CF shareholder when renting",null,null,0,1,2,3]
[null,"Change in NFO"]
[null,"Change in line of credit"]
[null,"Net Income when renting",null,null,null,null,null,null,null,"Sum of CF"]
[null,"CF shareholder (Rent)",null,null,null,null,null,null,null,{"$f":"SUM(E38:H38)","v":0}]
[]
[null,null,null,null,"NPV =",{"$f":"NPV(C32,F38:H38)+E38","v":0},"IRR =",{"$f":"IRR(E38:H38)","v":{"$error":"#NUM!"}}]
[]
[]
[]
[null,null,null,null,"Case 7. Comparisons of Projects. SOLVED. "]
[]
["Case","CF shareholder or ECF",null,0,1,2,3,"IRR",null,"Sum of CF"]
[1,"New machine, no debt",null,-300,-28,92,332,0.1000188925132417,null,{"$f":"SUM(D47:G47)","v":96}]
[2,"Risks Worst",null,-330,-89.8,45.2,312.2,-5.8751578537388438E-2,null,{"$f":"SUM(D48:G48)","v":-62.400000000000034}]
[2,"Risks Best",null,-270,39.799999999999997,138.80000000000001,345.8,0.30027686560496547,null,{"$f":"SUM(D49:G49)","v":254.40000000000003}]
[3,"New machine with debt",null,-120,0.79999999999999716,23.36,169.76,0.18267186571826977,null,{"$f":"SUM(D50:G50)","v":73.919999999999987}]
["3b","New machine with debt, Worst case",null,-150,-49,-23.92,137.48000000000002,-0.17459138654184225,null,-85.44]
["3c","New machine with debt, Best case",null,-90,51.799999999999983,70.831999999999994,201.03199999999998,0.7522264968437915,null,233.66399999999996]
[4,"New machine with inflation, no debt",null,-300,-28,88,360.8,0.12249343497344056,null,120.80000000000001]
["4b","New machine with inflation and debt",null,-120,0.79999999999999716,28.960000000000022,188.57600000000002,0.2340795222783163,null,98.336000000000041]
[5,"Old machine, with 0 debt ",null,0,-88,32,152,0.50859268357512954,null,96]
["5b","Old machine, with debt",null,0,8,28.160000000000004,52.16,"na",null,88.32]
[6,"Renting vs Buy",null,0,-15.84,8.16,32.159999999999997,0.71,null,24.479999999999997]

{"sheet":"Exh 1&2","index":10,"n_rows":84,"n_cols":14,"rows_kept":84,"cut":false,"hidden":false,"merged":[[71,1,71,3]]}
[null,null,null,null,"Cases 1&2. Project return and risk. SOLVED"]
[null,"Assumptions in bold and green. To do the calculations, see the notes inside of the cells."]
[]
[null,"P&L assumptions","Assumptions",null,null,1,2,3]
[null,"Pieces produced",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000}]
[null,"Unit selling price in euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":10},{"$f":"G6*(1+$C$8)","v":10}]
[null,"Unit cost in euros",8,null,null,{"$f":"C7","v":8},{"$f":"F7*(1+$C$8)","v":8},{"$f":"G7*(1+$C$8)","v":8}]
[null,"Expected inflation",0]
[]
[null,"P&L (€ ’000)",null,null,null,1,2,3]
[null,"Sales",null,null,null,{"$f":"F5*F6/1000","v":600},{"$f":"G5*G6/1000","v":600},{"$f":"H5*H6/1000","v":600}]
[null,"Cost of Goods Sold",null,null,null,{"$f":"F7*F5/1000","v":480},{"$f":"G7*G5/1000","v":480},{"$f":"H7*H5/1000","v":480}]
[null,"Gross Margin",null,null,null,{"$f":"F11-F12","v":120},{"$f":"G11-G12","v":120},{"$f":"H11-H12","v":120}]
[null,"Operating Expenses",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":20},{"$f":"G14*(1+$C8)","v":20}]
[null,"EBITDA",null,null,null,{"$f":"F13-F14","v":100},{"$f":"G13-G14","v":100},{"$f":"H13-H14","v":100}]
[null,"Depreciation",5,"Year",null,{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60}]
[null,"EBIT",null,null,null,{"$f":"F15-F16","v":40},{"$f":"G15-G16","v":40},{"$f":"H15-H16","v":40}]
[null,"Interest expense",0.05,null,null,{"$f":"E36*$C$18","v":0},{"$f":"F36*$C$18","v":0},{"$f":"G36*$C$18","v":0}]
[null,"Earnings before tax",null,null,null,{"$f":"F17-F18","v":40},{"$f":"G17-G18","v":40},{"$f":"H17-H18","v":40}]
[null,"Tax ",0.2,null,null,{"$f":"F19*$C$20","v":8},{"$f":"G19*$C$20","v":8},{"$f":"H19*$C$20","v":8}]
[null,"Net Income",null,null,null,{"$f":"F19-F20","v":32},{"$f":"G19-G20","v":32},{"$f":"H19-H20","v":32}]
[null,"Dividends paid",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},0]
[null,null,null,null,null,null,null,null,"Average"]
[null,"EBITDA  / Initial investment ",null,null,null,{"$f":"F15/$E$31","v":0.33333333333333331},{"$f":"G15/$E$31","v":0.33333333333333331},{"$f":"H15/$E$31","v":0.33333333333333331},{"$f":"AVERAGE(F24:H24)","v":0.33333333333333331}]
[null,"RONA (EBIT / NA)",null,null,null,{"$f":"F17/E32","v":0.13333333333333333},{"$f":"G17/F32","v":0.1111111111111111},{"$f":"H17/G32","v":0.13333333333333333},{"$f":"AVERAGE(F25:H25)","v":0.12592592592592591}]
[null,"ROA (Net Income / NA)",null,null,null,{"$f":"F21/E32","v":0.10666666666666667},{"$f":"G21/F32","v":8.8888888888888892E-2},0.10666666666666667,{"$f":"AVERAGE(F26:H26)","v":0.10074074074074074}]
[null,"ROE (Net Inc  / Equity)",null,null,null,{"$f":"F21/E37","v":0.10666666666666667},{"$f":"G21/F37","v":9.6385542168674704E-2},{"$f":"H21/G37","v":8.7912087912087919E-2},{"$f":"AVERAGE(F27:H27)","v":9.698809891580977E-2}]
[]
[null,"Short Balance Sheet",null,null,0,1,2,3,null,null,"Assumptions for NFO"]
[null,"NFO (20% Sales)",0.2,"x Sales",null,{"$f":"$C$30*F11","v":120},{"$f":"$C$30*G11","v":120},0,null,null,"Receivables",90,null,"NFO AS % OF SALES"]
[null,"FA, Net ",0,"Capex",300,{"$f":"E31-F16+$C31","v":240},{"$f":"F31-G16+$C31","v":180},0,null,null,"Inventory",10,null,{"$f":"(L30+L31*0.85-L32*0.85)/365","v":0.2}]
[null,"NA, Net Assets",null,null,{"$f":"E31+E30","v":300},{"$f":"F31+F30","v":360},{"$f":"G31+G30","v":300},{"$f":"H31+H30","v":0},null,null,"Payables",30]
[]
[null,"Line of credit D-S",0,"of NFO",{"$f":"$C34*E30","v":0},{"$f":"$C34*F30","v":0},{"$f":"$C34*G30","v":0},0]
[null,"Loan D-L",3,"years",0,{"$f":"E35-($E35/$C35)","v":0},{"$f":"F35-($E35/$C35)","v":0},0]
[null,"D, Total Debt",null,null,{"$f":"E34+E35","v":0},{"$f":"F34+F35","v":0},{"$f":"G34+G35","v":0},0]
[null,"E, Equity",null,null,{"$f":"E31-E35","v":300},{"$f":"E37+F21-F22","v":332},{"$f":"F37+G21-G22","v":364},396]
[null,"Total financing",null,null,{"$f":"E36+E37","v":300},{"$f":"F36+F37","v":332},364,396]
[]
[null,null,null,"Cash on balance sheet (+ or -)",{"$f":"E38-E32","v":0},{"$f":"F38-F32","v":-28},{"$f":"G38-G32","v":64},{"$f":"H38-H32","v":396}]
[null,null,null,"Annual increase in cash",null,{"$f":"F40-E40","v":-28},{"$f":"G40-F40","v":92},{"$f":"H40-G40","v":332}]
[null,null,null,"ECF = Increase of cash + dividends paid",{"$f":"-E37","v":-300},{"$f":"F41+F22","v":-28},{"$f":"G41+G22","v":92},332]
[]
[null,"CF for the shareholder or ECF",null,null,0,1,2,3]
[null,"Net Income",null,null,null,{"$f":"F21","v":32},{"$f":"G21","v":32},{"$f":"H21","v":32}]
[null,"Change in NA",null,null,{"$f":"-E32","v":-300},{"$f":"E32-F32","v":-60},{"$f":"F32-G32","v":60},{"$f":"G32-H32","v":300}]
[null,"Change in Debt",null,null,{"$f":"E36","v":0},{"$f":"F36-E36","v":0},{"$f":"G36-F36","v":0},{"$f":"H36-G36","v":0},"Sum CF"]
[null,"Equity CF or CF shareholder",null,null,{"$f":"SUM(E45:E47)","v":-300},{"$f":"SUM(F45:F47)","v":-28},{"$f":"SUM(G45:G47)","v":92},{"$f":"SUM(H45:H47)","v":332},{"$f":"SUM(E48:H48)","v":96}]
[null,"IRR  =",{"$f":"IRR(E48:H48)","v":0.1000188925132417}]
[]
[null,"FCF after tax",null,null,0,1,2,3]
[null,"EBIT x (1-t)",null,null,null,{"$f":"F17*(1-$C20)","v":32},{"$f":"G17*(1-$C20)","v":32},{"$f":"H17*(1-$C20)","v":32}]
[null,"Change in NA",null,null,{"$f":"E46","v":-300},{"$f":"F46","v":-60},60,300]
[null,"FCF after tax",null,null,{"$f":"SUM(E52:E53)","v":-300},{"$f":"SUM(F52:F53)","v":-28},{"$f":"SUM(G52:G53)","v":92},{"$f":"SUM(H52:H53)","v":332}]
[null,"IRR  =",{"$f":"IRR(E54:H54)","v":0.1000188925132417}]
[]
[]
[]
[null,"Risks. Sensitivity Analysis.",null,null,null,null,"FOR CASE 2"]
[]
[null,null,null,"Biggest Risk"]
[null,"Scenarios","Base","Worst","Best","Range"]
[null,"Price",10,9,11]
[null,"IRR shareholder",0.1,-5.1319434967612376E-2,0.24481039833418961,{"$f":"E64-D64","v":0.29612983330180198}]
[null,"Cost of Machine FA",300,330,270]
[null,"IRR shareholder",{"$f":"C64","v":0.1},7.9008814853287923E-2,0.12455999895101888,{"$f":"E66-D66","v":4.5551184097730957E-2}]
[null,"Size of NFO%",0.2,0.25,0.15]
[null,"IRR shareholder",{"$f":"C64","v":0.1},9.4386474155067512E-2,0.10632719227147946,{"$f":"E68-D68","v":1.1940718116411952E-2}]
[null,"Total Risk ",0.1,-5.8751578537388438E-2,0.30027686560496569,{"$f":"E69-D69","v":0.35902844414235413}]
[]
[]
[null,"IRR  and Level of Debt",null,null,null,null,"FOR CASE 3"]
[null,"Debt","IRR","Increase"]
[null,"60M€ or 20% of FA",0.128]
[null,"120M€ or 40% of FA",0.14899999999999999,{"$f":"C75-C74","v":2.0999999999999991E-2}]
[null,"180M€ or 60% of FA",0.183,{"$f":"C76-C75","v":3.4000000000000002E-2}]
[null,"240M€ or 80% of FA",0.251,6.8000000000000005E-2]
[]
[null,null,null,null,null,null,"FOR CASE 5"]
[]
[null,"CF shareholder or ECF",null,0,1,2,3,"IRR","Sum of CF"]
[null,"New machine without debt (case1)",null,-300,-28,92,332,{"$f":"IRR(D82:G82)","v":0.1000188925132417},{"$f":"SUM(D82:G82)","v":96}]
[null,"New machine with debt (case 3)",null,-120,0.79999999999999716,23.36,169.76,0.18267186571826977,73.919999999999987]
[null,"Old machine (case 5)",null,0,-88,32,152,{"$f":"IRR(D84:G84)","v":0.50859268357512954},{"$f":"SUM(D84:G84)","v":96}]

{"sheet":"Exh 3","index":11,"n_rows":77,"n_cols":14,"rows_kept":77,"cut":false,"hidden":false,"merged":[[71,1,71,3]]}
[null,null,null,null,"Cases 3. Project financed with debt. SOLVED"]
[null,"Assumptions in bold and green. To do the calculations, see the notes inside of the cells."]
[null,null,null,null,null,null,null,null,null,null,"Assumptions for NFO"]
[null,"P&L assumptions","Assumptions",null,null,1,2,3,null,null,"Receivables",90,null,"NFO AS % OF SALES"]
[null,"Pieces produced",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000},null,null,"Inventory",10,null,{"$f":"(L4+L5*0.85-L6*0.85)/365","v":0.2}]
[null,"Unit selling price in euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":10},{"$f":"G6*(1+$C$8)","v":10},null,null,"Payables",30]
[null,"Unit cost in euros",8,null,null,{"$f":"C7","v":8},{"$f":"F7*(1+$C$8)","v":8},{"$f":"G7*(1+$C$8)","v":8}]
[null,"Expected inflation",0]
[]
[null,"P&L (€ ’000)",null,null,null,1,2,3]
[null,"Sales",null,null,null,{"$f":"F5*F6/1000","v":600},{"$f":"G5*G6/1000","v":600},{"$f":"H5*H6/1000","v":600}]
[null,"Cost of Goods Sold",null,null,null,{"$f":"F7*F5/1000","v":480},{"$f":"G7*G5/1000","v":480},{"$f":"H7*H5/1000","v":480}]
[null,"Gross Margin",null,null,null,{"$f":"F11-F12","v":120},{"$f":"G11-G12","v":120},{"$f":"H11-H12","v":120}]
[null,"Operating Expenses",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":20},{"$f":"G14*(1+$C8)","v":20}]
[null,"EBITDA",null,null,null,{"$f":"F13-F14","v":100},{"$f":"G13-G14","v":100},{"$f":"H13-H14","v":100}]
[null,"Depreciation",5,"Year",null,{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60}]
[null,"EBIT",null,null,null,{"$f":"F15-F16","v":40},{"$f":"G15-G16","v":40},{"$f":"H15-H16","v":40}]
[null,"Interest expense",0.05,null,null,{"$f":"E36*$C$18","v":9},{"$f":"F36*$C$18","v":10.8},{"$f":"G36*$C$18","v":7.8000000000000007}]
[null,"Earnings before tax",null,null,null,{"$f":"F17-F18","v":31},{"$f":"G17-G18","v":29.2},{"$f":"H17-H18","v":32.200000000000003}]
[null,"Tax ",0.2,null,null,{"$f":"F19*$C$20","v":6.2},{"$f":"G19*$C$20","v":5.84},{"$f":"H19*$C$20","v":6.4400000000000013}]
[null,"Net Income",null,null,null,{"$f":"F19-F20","v":24.8},{"$f":"G19-G20","v":23.36},{"$f":"H19-H20","v":25.76}]
[null,"Dividends paid",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},0]
[null,null,null,null,null,null,null,null,"Average"]
[null,"EBITDA  / Initial investment ",null,null,null,{"$f":"F15/$E$31","v":0.33333333333333331},{"$f":"G15/$E$31","v":0.33333333333333331},{"$f":"H15/$E$31","v":0.33333333333333331},{"$f":"AVERAGE(F24:H24)","v":0.33333333333333331}]
[null,"RONA (EBIT / NA)",null,null,null,{"$f":"F17/E32","v":0.13333333333333333},{"$f":"G17/F32","v":0.1111111111111111},{"$f":"H17/G32","v":0.13333333333333333},{"$f":"AVERAGE(F25:H25)","v":0.12592592592592591}]
[null,"ROA (Net Income / NA)",null,null,null,{"$f":"F21/E32","v":8.2666666666666666E-2},{"$f":"G21/F32","v":6.4888888888888885E-2},8.5866666666666674E-2,{"$f":"AVERAGE(F26:H26)","v":7.7807407407407408E-2}]
[null,"ROE (Net Inc  / Equity)",null,null,null,{"$f":"F21/E37","v":0.20666666666666667},{"$f":"G21/F37","v":0.1613259668508287},{"$f":"H21/G37","v":0.15318744053282587},{"$f":"AVERAGE(F27:H27)","v":0.17372669135010707}]
[]
[null,"Short Balance Sheet",null,null,0,1,2,3]
[null,"NFO (20% Sales)",0.2,"x Sales",null,{"$f":"$C$30*F11","v":120},{"$f":"$C$30*G11","v":120},0]
[null,"FA, Net ",0,"Capex",300,{"$f":"E31-F16+$C31","v":240},{"$f":"F31-G16+$C31","v":180},0]
[null,"NA, Net Assets",null,null,{"$f":"E31+E30","v":300},{"$f":"F31+F30","v":360},{"$f":"G31+G30","v":300},{"$f":"H31+H30","v":0}]
[]
[null,"Line of credit D-S",0.8,"of NFO",{"$f":"$C34*E30","v":0},{"$f":"$C34*F30","v":96},{"$f":"$C34*G30","v":96},0]
[null,"Loan D-L",3,"years",180,{"$f":"E35-($E35/$C35)","v":120},{"$f":"F35-($E35/$C35)","v":60},0]
[null,"D, Total Debt",null,null,{"$f":"E34+E35","v":180},{"$f":"F34+F35","v":216},{"$f":"G34+G35","v":156},0]
[null,"E, Equity",null,null,{"$f":"E31-E35","v":120},{"$f":"E37+F21-F22","v":144.80000000000001},{"$f":"F37+G21-G22","v":168.16000000000003},193.92000000000002]
[null,"Total financing",null,null,{"$f":"E36+E37","v":300},{"$f":"F36+F37","v":360.8},324.16000000000003,193.92000000000002]
[]
[null,null,null,"Cash on balance sheet (+ or -)",{"$f":"E38-E32","v":0},{"$f":"F38-F32","v":0.80000000000001137},{"$f":"G38-G32","v":24.160000000000025},{"$f":"H38-H32","v":193.92000000000002}]
[null,null,null,"Annual increase in cash",null,{"$f":"F40-E40","v":0.80000000000001137},{"$f":"G40-F40","v":23.360000000000014},{"$f":"H40-G40","v":169.76}]
[null,null,null,"ECF = Increase of cash + dividends paid",{"$f":"-E37","v":-120},{"$f":"F41+F22","v":0.80000000000001137},{"$f":"G41+G22","v":23.360000000000014},169.76]
[]
[null,"CF for the shareholder",null,null,0,1,2,3]
[null,"Net Income",null,null,null,{"$f":"F21","v":24.8},{"$f":"G21","v":23.36},{"$f":"H21","v":25.76}]
[null,"Change in NA",null,null,{"$f":"-E32","v":-300},{"$f":"E32-F32","v":-60},{"$f":"F32-G32","v":60},{"$f":"G32-H32","v":300}]
[null,"Change in Debt",null,null,{"$f":"E36","v":180},{"$f":"F36-E36","v":36},{"$f":"G36-F36","v":-60},{"$f":"H36-G36","v":-156},"Sum of CF"]
[null,"Equity CF or CF shareholder",null,null,{"$f":"SUM(E45:E47)","v":-120},{"$f":"SUM(F45:F47)","v":0.79999999999999716},{"$f":"SUM(G45:G47)","v":23.36},{"$f":"SUM(H45:H47)","v":169.76},{"$f":"SUM(E48:H48)","v":73.919999999999987}]
[null,"IRR  =",{"$f":"IRR(E48:H48)","v":0.18267186571826977}]
[]
[null,"FCF after tax",null,null,0,1,2,3]
[null,"EBIT x (1-t)",null,null,null,{"$f":"F17*(1-$C20)","v":32},{"$f":"G17*(1-$C20)","v":32},{"$f":"H17*(1-$C20)","v":32}]
[null,"Change in NA",null,null,{"$f":"E46","v":-300},{"$f":"F46","v":-60},60,300]
[null,"FCF after tax",null,null,{"$f":"SUM(E52:E53)","v":-300},{"$f":"SUM(F52:F53)","v":-28},{"$f":"SUM(G52:G53)","v":92},{"$f":"SUM(H52:H53)","v":332}]
[null,"IRR  =",{"$f":"IRR(E54:H54)","v":0.1000188925132417}]
[]
[]
[]
[null,"Risks. Sensitivity Analysis."]
[]
[null,null,"Biggest Risk"]
[null,"Scenarios","Base","Worst","Best","Range"]
[null,"Price",10,9,11]
[null,"IRR shareholder",0.1,-5.1319434967612376E-2,0.24481039833418961,{"$f":"E64-D64","v":0.29612983330180198}]
[null,"Cost of Machine FA",300,330,270]
[null,"IRR shareholder",{"$f":"C64","v":0.1},7.9008814853287923E-2,0.12455999895101888,{"$f":"E66-D66","v":4.5551184097730957E-2}]
[null,"Size of NFO%",0.2,0.25,0.15]
[null,"IRR shareholder",{"$f":"C64","v":0.1},9.4386474155067512E-2,0.10632719227147946,{"$f":"E68-D68","v":1.1940718116411952E-2}]
[null,"Total Risk ",0.1,-5.8751578537388438E-2,0.30027686560496569,{"$f":"E69-D69","v":0.35902844414235413}]
[]
[]
[null,"IRR  and Level of Debt"]
[null,"Debt","IRR","Increase"]
[null,"60M€ or 20% of FA",0.128]
[null,"120M€ or 40% of FA",0.14899999999999999,{"$f":"C75-C74","v":2.0999999999999991E-2}]
[null,"180M€ or 60% of FA",0.183,{"$f":"C76-C75","v":3.4000000000000002E-2}]
[null,"240M€ or 80% of FA",0.251,6.8000000000000005E-2]

{"sheet":"Exh 4","index":12,"n_rows":77,"n_cols":14,"rows_kept":77,"cut":false,"hidden":false,"merged":[[71,1,71,3]]}
[null,null,null,null,"Case 4. Project with inflation. SOLVED"]
[null,"Assumptions in bold and green. To do the calculations, see the notes inside of the cells."]
[null,null,null,null,null,null,null,null,null,null,"Assumptions for NFO"]
[null,"P&L assumptions","Assumptions",null,null,1,2,3,null,null,"Receivables",90,null,"NFO AS % OF SALES"]
[null,"Pieces produced",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000},null,null,"Inventory",10,null,{"$f":"(L4+L5*0.85-L6*0.85)/365","v":0.2}]
[null,"Unit selling price in euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":11},{"$f":"G6*(1+$C$8)","v":12.100000000000001},null,null,"Payables",30]
[null,"Unit cost in euros",8,null,null,{"$f":"C7","v":8},{"$f":"F7*(1+$C$8)","v":8.8000000000000007},{"$f":"G7*(1+$C$8)","v":9.6800000000000015}]
[null,"Expected inflation",0.1]
[]
[null,"P&L (€ ’000)",null,null,null,1,2,3]
[null,"Sales",null,null,null,{"$f":"F5*F6/1000","v":600},{"$f":"G5*G6/1000","v":660},{"$f":"H5*H6/1000","v":726.00000000000011}]
[null,"Cost of Goods Sold",null,null,null,{"$f":"F7*F5/1000","v":480},{"$f":"G7*G5/1000","v":528},{"$f":"H7*H5/1000","v":580.80000000000007},null,null,"Case 5. Comparison of two projects.   SOLVED"]
[null,"Gross Margin",null,null,null,{"$f":"F11-F12","v":120},{"$f":"G11-G12","v":132},{"$f":"H11-H12","v":145.20000000000005}]
[null,"Operating Expenses",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":22},{"$f":"G14*(1+$C8)","v":24.200000000000003}]
[null,"EBITDA",null,null,null,{"$f":"F13-F14","v":100},{"$f":"G13-G14","v":110},{"$f":"H13-H14","v":121.00000000000004}]
[null,"Depreciation",5,"Year",null,{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60}]
[null,"EBIT",null,null,null,{"$f":"F15-F16","v":40},{"$f":"G15-G16","v":50},{"$f":"H15-H16","v":61.000000000000043}]
[null,"Interest expense",0.05,null,null,{"$f":"E36*$C$18","v":0},{"$f":"F36*$C$18","v":0},{"$f":"G36*$C$18","v":0}]
[null,"Earnings before tax",null,null,null,{"$f":"F17-F18","v":40},{"$f":"G17-G18","v":50},{"$f":"H17-H18","v":61.000000000000043}]
[null,"Tax ",0.2,null,null,{"$f":"F19*$C$20","v":8},{"$f":"G19*$C$20","v":10},{"$f":"H19*$C$20","v":12.20000000000001}]
[null,"Net Income",null,null,null,{"$f":"F19-F20","v":32},{"$f":"G19-G20","v":40},{"$f":"H19-H20","v":48.800000000000033}]
[null,"Dividends paid",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},0]
[null,null,null,null,null,null,null,null,"Average"]
[null,"EBITDA  / Initial investment ",null,null,null,{"$f":"F15/$E$31","v":0.33333333333333331},{"$f":"G15/$E$31","v":0.36666666666666664},{"$f":"H15/$E$31","v":0.40333333333333349},{"$f":"AVERAGE(F24:H24)","v":0.36777777777777781}]
[null,"RONA (EBIT / NA)",null,null,null,{"$f":"F17/E32","v":0.13333333333333333},{"$f":"G17/F32","v":0.1388888888888889},{"$f":"H17/G32","v":0.19551282051282065},{"$f":"AVERAGE(F25:H25)","v":0.15591168091168098}]
[null,"ROA (Net Income / NA)",null,null,null,{"$f":"F21/E32","v":0.10666666666666667},{"$f":"G21/F32","v":0.1111111111111111},0.15641025641025652,{"$f":"AVERAGE(F26:H26)","v":0.12472934472934477}]
[null,"ROE (Net Inc  / Equity)",null,null,null,{"$f":"F21/E37","v":0.10666666666666667},{"$f":"G21/F37","v":0.12048192771084337},{"$f":"H21/G37","v":0.13118279569892483},{"$f":"AVERAGE(F27:H27)","v":0.11944379669214496}]
[]
[null,"Short Balance Sheet",null,null,0,1,2,3]
[null,"NFO (20% Sales)",0.2,"x Sales",null,{"$f":"$C$30*F11","v":120},{"$f":"$C$30*G11","v":132},0]
[null,"FA, Net ",0,"Capex",300,{"$f":"E31-F16+$C31","v":240},{"$f":"F31-G16+$C31","v":180},0]
[null,"NA, Net Assets",null,null,{"$f":"E31+E30","v":300},{"$f":"F31+F30","v":360},{"$f":"G31+G30","v":312},{"$f":"H31+H30","v":0}]
[]
[null,"Line of credit D-S",0,"of NFO",{"$f":"$C34*E30","v":0},{"$f":"$C34*F30","v":0},{"$f":"$C34*G30","v":0},0]
[null,"Loan D-L",3,"years",0,{"$f":"E35-($E35/$C35)","v":0},{"$f":"F35-($E35/$C35)","v":0},0]
[null,"D, Total Debt",null,null,{"$f":"E34+E35","v":0},{"$f":"F34+F35","v":0},{"$f":"G34+G35","v":0},0]
[null,"E, Equity",null,null,{"$f":"E31-E35","v":300},{"$f":"E37+F21-F22","v":332},{"$f":"F37+G21-G22","v":372},420.8]
[null,"Total financing",null,null,{"$f":"E36+E37","v":300},{"$f":"F36+F37","v":332},372,420.8]
[]
[null,null,null,"Cash on balance sheet (+ or -)",{"$f":"E38-E32","v":0},{"$f":"F38-F32","v":-28},{"$f":"G38-G32","v":60},{"$f":"H38-H32","v":420.8}]
[null,null,null,"Annual increase in cash",null,{"$f":"F40-E40","v":-28},{"$f":"G40-F40","v":88},{"$f":"H40-G40","v":360.8}]
[null,null,null,"ECF = Increase of cash + dividends paid",{"$f":"-E37","v":-300},{"$f":"F41+F22","v":-28},{"$f":"G41+G22","v":88},360.8]
[]
[null,"CF for the shareholder",null,null,0,1,2,3]
[null,"Net Income",null,null,null,{"$f":"F21","v":32},{"$f":"G21","v":40},{"$f":"H21","v":48.800000000000033}]
[null,"Change in NA",null,null,{"$f":"-E32","v":-300},{"$f":"E32-F32","v":-60},{"$f":"F32-G32","v":48},{"$f":"G32-H32","v":312}]
[null,"Change in Debt",null,null,{"$f":"E36","v":0},{"$f":"F36-E36","v":0},{"$f":"G36-F36","v":0},{"$f":"H36-G36","v":0},"Sum of CF"]
[null,"Equity CF or CF shareholder",null,null,{"$f":"SUM(E45:E47)","v":-300},{"$f":"SUM(F45:F47)","v":-28},{"$f":"SUM(G45:G47)","v":88},{"$f":"SUM(H45:H47)","v":360.8},{"$f":"SUM(E48:H48)","v":120.80000000000001}]
[null,"IRR  =",{"$f":"IRR(E48:H48)","v":0.12249343497344056}]
[]
[null,"FCF after tax",null,null,0,1,2,3]
[null,"EBIT x (1-t)",null,null,null,{"$f":"F17*(1-$C20)","v":32},{"$f":"G17*(1-$C20)","v":40},{"$f":"H17*(1-$C20)","v":48.80000000000004}]
[null,"Change in NA",null,null,{"$f":"E46","v":-300},{"$f":"F46","v":-60},48,312]
[null,"FCF after tax",null,null,{"$f":"SUM(E52:E53)","v":-300},{"$f":"SUM(F52:F53)","v":-28},{"$f":"SUM(G52:G53)","v":88},{"$f":"SUM(H52:H53)","v":360.80000000000007}]
[null,"IRR  =",{"$f":"IRR(E54:H54)","v":0.12249343497344078}]
[]
[]
[]
[null,"Risks. Sensitivity Analysis."]
[]
[null,null,"Biggest Risk"]
[null,"Scenarios","Base","Worst","Best","Range"]
[null,"Price",10,9,11]
[null,"IRR shareholder",0.1,-5.1319434967612376E-2,0.24481039833418961,{"$f":"E64-D64","v":0.29612983330180198}]
[null,"Cost of Machine FA",300,330,270]
[null,"IRR shareholder",{"$f":"C64","v":0.1},7.9008814853287923E-2,0.12455999895101888,{"$f":"E66-D66","v":4.5551184097730957E-2}]
[null,"Size of NFO%",0.2,0.25,0.15]
[null,"IRR shareholder",{"$f":"C64","v":0.1},9.4386474155067512E-2,0.10632719227147946,{"$f":"E68-D68","v":1.1940718116411952E-2}]
[null,"Total Risk ",0.1,-5.8751578537388438E-2,0.30027686560496569,{"$f":"E69-D69","v":0.35902844414235413}]
[]
[]
[null,"IRR  and Level of Debt"]
[null,"Debt","IRR","Increase"]
[null,"60M€ or 20% of FA",0.128]
[null,"120M€ or 40% of FA",0.14899999999999999,{"$f":"C75-C74","v":2.0999999999999991E-2}]
[null,"180M€ or 60% of FA",0.183,{"$f":"C76-C75","v":3.4000000000000002E-2}]
[null,"240M€ or 80% of FA",0.251,6.8000000000000005E-2]

{"sheet":"Exh 5","index":13,"n_rows":85,"n_cols":14,"rows_kept":85,"cut":false,"hidden":false,"merged":[[71,1,71,3]]}
[null,null,null,null,"Case 5. Comparison of two projects.   SOLVED"]
[null,"Assumptions in bold and green. To do the calculations, see the notes inside of the cells."]
[null,null,null,null,null,null,null,null,null,null,"Assumptions for NFO"]
[null,"P&L assumptions","Assumptions",null,null,1,2,3,null,null,"Receivables",90,null,"NFO AS % OF SALES"]
[null,"Pieces produced",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000},null,null,"Inventory",10,null,{"$f":"(L4+L5*0.85-L6*0.85)/365","v":0.2}]
[null,"Unit selling price in euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":10},{"$f":"G6*(1+$C$8)","v":10},null,null,"Payables",30]
[null,"Unit cost in euros",9,null,null,{"$f":"C7","v":9},{"$f":"F7*(1+$C$8)","v":9},{"$f":"G7*(1+$C$8)","v":9}]
[null,"Expected inflation",0]
[]
[null,"P&L (€ ’000)",null,null,null,1,2,3]
[null,"Sales",null,null,null,{"$f":"F5*F6/1000","v":600},{"$f":"G5*G6/1000","v":600},{"$f":"H5*H6/1000","v":600}]
[null,"Cost of Goods Sold",null,null,null,{"$f":"F7*F5/1000","v":540},{"$f":"G7*G5/1000","v":540},{"$f":"H7*H5/1000","v":540},null,null,"Case 5. Comparison of two projects.   SOLVED"]
[null,"Gross Margin",null,null,null,{"$f":"F11-F12","v":60},{"$f":"G11-G12","v":60},{"$f":"H11-H12","v":60}]
[null,"Operating Expenses",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":20},{"$f":"G14*(1+$C8)","v":20}]
[null,"EBITDA",null,null,null,{"$f":"F13-F14","v":40},{"$f":"G13-G14","v":40},{"$f":"H13-H14","v":40}]
[null,"Depreciation",5,"Year",null,{"$f":"$E31*(1/$C$16)","v":0},{"$f":"$E31*(1/$C$16)","v":0},{"$f":"$E31*(1/$C$16)","v":0}]
[null,"EBIT",null,null,null,{"$f":"F15-F16","v":40},{"$f":"G15-G16","v":40},{"$f":"H15-H16","v":40}]
[null,"Interest expense",0.05,null,null,{"$f":"E36*$C$18","v":0},{"$f":"F36*$C$18","v":0},{"$f":"G36*$C$18","v":0}]
[null,"Earnings before tax",null,null,null,{"$f":"F17-F18","v":40},{"$f":"G17-G18","v":40},{"$f":"H17-H18","v":40}]
[null,"Tax ",0.2,null,null,{"$f":"F19*$C$20","v":8},{"$f":"G19*$C$20","v":8},{"$f":"H19*$C$20","v":8}]
[null,"Net Income",null,null,null,{"$f":"F19-F20","v":32},{"$f":"G19-G20","v":32},{"$f":"H19-H20","v":32}]
[null,"Dividends paid",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},0]
[null,null,null,null,null,null,null,null,"Average"]
[null,"EBITDA  / Initial investment ",null,null,null,"na","na","na","na"]
[null,"RONA (EBIT / NA)",null,null,null,"na",{"$f":"G17/F32","v":0.33333333333333331},{"$f":"H17/G32","v":0.33333333333333331},{"$f":"AVERAGE(F25:H25)","v":0.33333333333333331}]
[null,"ROA (Net Income / NA)",null,null,null,"na",{"$f":"G21/F32","v":0.26666666666666666},0.26666666666666666,{"$f":"AVERAGE(F26:H26)","v":0.26666666666666666}]
[null,"ROE (Net Inc  / Equity)",null,null,null,"na",{"$f":"G21/F37","v":1},{"$f":"H21/G37","v":0.5},{"$f":"AVERAGE(F27:H27)","v":0.75}]
[]
[null,"Short Balance Sheet",null,null,0,1,2,3]
[null,"NFO (20% Sales)",0.2,"x Sales",null,{"$f":"$C$30*F11","v":120},{"$f":"$C$30*G11","v":120},0]
[null,"FA, Net ",0,"Capex",0,{"$f":"E31-F16+$C31","v":0},{"$f":"F31-G16+$C31","v":0},0]
[null,"NA, Net Assets",null,null,{"$f":"E31+E30","v":0},{"$f":"F31+F30","v":120},{"$f":"G31+G30","v":120},{"$f":"H31+H30","v":0}]
[]
[null,"Line of credit D-S",0,"of NFO",{"$f":"$C34*E30","v":0},{"$f":"$C34*F30","v":0},{"$f":"$C34*G30","v":0},0]
[null,"Loan D-L",3,"years",0,{"$f":"E35-($E35/$C35)","v":0},{"$f":"F35-($E35/$C35)","v":0},0]
[null,"D, Total Debt",null,null,{"$f":"E34+E35","v":0},{"$f":"F34+F35","v":0},{"$f":"G34+G35","v":0},0]
[null,"E, Equity",null,null,{"$f":"E31-E35","v":0},{"$f":"E37+F21-F22","v":32},{"$f":"F37+G21-G22","v":64},96]
[null,"Total financing",null,null,{"$f":"E36+E37","v":0},{"$f":"F36+F37","v":32},64,96]
[]
[null,null,null,"Cash on balance sheet (+ or -)",{"$f":"E38-E32","v":0},{"$f":"F38-F32","v":-88},{"$f":"G38-G32","v":-56},{"$f":"H38-H32","v":96}]
[null,null,null,"Annual increase in cash",null,{"$f":"F40-E40","v":-88},{"$f":"G40-F40","v":32},{"$f":"H40-G40","v":152}]
[null,null,null,"ECF = Increase of cash + dividends paid",{"$f":"-E37","v":0},{"$f":"F41+F22","v":-88},{"$f":"G41+G22","v":32},152]
[]
[null,"CF for the shareholder Old Machine",null,null,0,1,2,3]
[null,"Net Income",null,null,null,{"$f":"F21","v":32},{"$f":"G21","v":32},{"$f":"H21","v":32}]
[null,"Change in NA",null,null,{"$f":"-E32","v":0},{"$f":"E32-F32","v":-120},{"$f":"F32-G32","v":0},{"$f":"G32-H32","v":120}]
[null,"Change in Debt",null,null,{"$f":"E36","v":0},{"$f":"F36-E36","v":0},{"$f":"G36-F36","v":0},{"$f":"H36-G36","v":0},"Sum of CF"]
[null,"Equity CF or CF shareholder",null,null,{"$f":"SUM(E45:E47)","v":0},{"$f":"SUM(F45:F47)","v":-88},{"$f":"SUM(G45:G47)","v":32},{"$f":"SUM(H45:H47)","v":152},{"$f":"SUM(E48:H48)","v":96}]
[null,"IRR  =",{"$f":"IRR(E48:H48)","v":0.50859268357512954}]
[]
[null,"FCF after tax",null,null,0,1,2,3]
[null,"EBIT x (1-t)",null,null,null,{"$f":"F17*(1-$C20)","v":32},{"$f":"G17*(1-$C20)","v":32},{"$f":"H17*(1-$C20)","v":32}]
[null,"Change in NA",null,null,{"$f":"E46","v":0},{"$f":"F46","v":-120},0,120]
[null,"FCF after tax",null,null,{"$f":"SUM(E52:E53)","v":0},{"$f":"SUM(F52:F53)","v":-88},{"$f":"SUM(G52:G53)","v":32},{"$f":"SUM(H52:H53)","v":152}]
[null,"IRR  =",{"$f":"IRR(E54:H54)","v":0.50859268357512954}]
[]
[]
[]
[null,"Risks. Sensitivity Analysis."]
[]
[null,null,"Biggest Risk"]
[null,"Scenarios","Base","Worst","Best","Range"]
[null,"Price",10,9,11]
[null,"IRR shareholder",0.1,-5.1319434967612376E-2,0.24481039833418961,{"$f":"E64-D64","v":0.29612983330180198}]
[null,"Cost of Machine FA",300,330,270]
[null,"IRR shareholder",{"$f":"C64","v":0.1},7.9008814853287923E-2,0.12455999895101888,{"$f":"E66-D66","v":4.5551184097730957E-2}]
[null,"Size of NFO%",0.2,0.25,0.15]
[null,"IRR shareholder",{"$f":"C64","v":0.1},9.4386474155067512E-2,0.10632719227147946,{"$f":"E68-D68","v":1.1940718116411952E-2}]
[null,"Total Risk ",0.1,-5.8751578537388438E-2,0.30027686560496569,{"$f":"E69-D69","v":0.35902844414235413}]
[]
[]
[null,"IRR  and Level of Debt"]
[null,"Debt","IRR","Increase"]
[null,"60M€ or 20% of FA",0.128]
[null,"120M€ or 40% of FA",0.14899999999999999,{"$f":"C75-C74","v":2.0999999999999991E-2}]
[null,"180M€ or 60% of FA",0.183,{"$f":"C76-C75","v":3.4000000000000002E-2}]
[null,"240M€ or 80% of FA",0.251,6.8000000000000005E-2]
[]
[null,null,null,null,null,"FOR CASE 5"]
[]
[null,"CF shareholder or ECF",null,0,1,2,3,"IRR","Sum of CF"]
[1,"New machine without debt (case1)",null,-300,-28,92,332,0.1000188925132417,96]
[3,"New machine with debt (case 3)",null,-120,0.79999999999999716,23.36,169.76,0.18267186571826977,73.919999999999987]
[5,"Old machine, with 0 debt (case 5)",null,0,-88,32,152,0.50859268357512954,96]
["5b","Old machine, with debt (case 5)",null,0,8,28.160000000000004,52.16,"na",88.32]

{"sheet":"Exh 6&7","index":14,"n_rows":57,"n_cols":13,"rows_kept":57,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,"Case 6. Cost reduction project.  Buy or Rent? SOLVED"]
[null,"Assumptions in green and bold"]
[null,null,null,null,null,null,"Buy"]
[null,"P&L ","Assumptions",null,null,1,2,3,null,"Rent"]
[null,"EBITDA",100,null,null,{"$f":"$C5","v":100},{"$f":"$C5","v":100},100,null,{"$f":"$C5","v":100}]
[null,"Depreciation or lease payment",5,"years",null,{"$f":"E15/C6","v":60},{"$f":"F6","v":60},{"$f":"G6","v":60},null,85]
[null,"EBIT",null,null,null,{"$f":"F5-F6","v":40},{"$f":"G5-G6","v":40},40,null,{"$f":"J5-J6","v":15},null,null,"Rent"]
[null,"Interest Expense",0.05,null,null,{"$f":"$C8*E20","v":9},{"$f":"$C8*F20","v":10.8},7.8000000000000007,null,{"$f":"$C8*F18","v":4.8000000000000007},null,null,-300]
[null,"Earnings before tax",null,null,null,{"$f":"F7-F8","v":31},{"$f":"G7-G8","v":29.2},32.200000000000003,null,{"$f":"J7-J8","v":10.199999999999999},null,null,85]
[null,"Tax rate",0.2,null,null,{"$f":"$C10*F9","v":6.2},{"$f":"$C10*G9","v":5.84},6.4400000000000013,null,{"$f":"J9*C10","v":2.04},null,null,{"$f":"M9","v":85}]
[null,"NI, Net Income",null,null,null,{"$f":"F9-F10","v":24.8},{"$f":"G9-G10","v":23.36},25.76,null,{"$f":"J9-J10","v":8.16},null,null,{"$f":"M10","v":85}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"M11","v":85}]
[null,"Balance sheet when buying",null,null,0,1,2,3,null,null,null,null,{"$f":"M12","v":85}]
[null,"NFO",null,null,null,120,{"$f":"$F$14","v":120},0]
[null,"FA (machine)",null,null,300,{"$f":"E15-$F6","v":240},{"$f":"F15-$F6","v":180},0,null,null,null,null,{"$f":"IRR(M8:M13)","v":0.12858463526289921}]
[null,"NA, Net assets",null,null,{"$f":"E14+E15","v":300},{"$f":"F14+F15","v":360},300,{"$f":"H15+H14","v":0}]
[]
[null,"Line of credit (D-Short term)",0.8,null,null,{"$f":"$C18*F14","v":96},{"$f":"$C18*G14","v":96},{"$f":"$C18*H14","v":0}]
[null,"Loan (D-Long term)",3,"years",180,{"$f":"E19-($E19/$C19)","v":120},{"$f":"F19-($E19/$C19)","v":60},0]
[null,"D, Total Debt",null,null,{"$f":"E18+E19","v":180},{"$f":"F18+F19","v":216},156,0]
[null,"E, Equity ",null,null,{"$f":"E16-E20","v":120},{"$f":"E21+$F$11","v":144.80000000000001},{"$f":"F21+$F$11","v":169.60000000000002},{"$f":"G21+$F$11","v":194.40000000000003}]
[null,"Equity + Debt",null,null,{"$f":"E20+E21","v":300},{"$f":"F20+F21","v":360.8},325.60000000000002,194.40000000000003]
[null,null,null,"Cash Surplus",{"$f":"E22-E16","v":0},{"$f":"F22-F16","v":0.80000000000001137},{"$f":"G22-G16","v":25.600000000000023},{"$f":"H22-H16","v":194.40000000000003}]
[null,null,null,"Cash generated CF",{"$f":"-E21","v":-120},{"$f":"F23-E23","v":0.80000000000001137},{"$f":"G23-F23","v":24.800000000000011},{"$f":"H23-G23","v":168.8}]
[]
[null,"CF shareholder when buying",null,null,0,1,2,3]
[null,"Change in assets ",null,null,{"$f":"-E16","v":-300},{"$f":"E16-F16","v":-60},{"$f":"F16-G16","v":60},{"$f":"G16-H16","v":300}]
[null,"Change in debt",null,null,{"$f":"E20","v":180},{"$f":"F20-E20","v":36},{"$f":"G20-F20","v":-60},-156]
[null,"Net Income",null,null,null,{"$f":"$F$11","v":24.8},{"$f":"$F$11","v":24.8},{"$f":"$F$11","v":24.8},null,"Sum of CF"]
[null,"CF shareholder (Buy)",null,null,{"$f":"E27+E28+E29","v":-120},0.80000000000000071,24.8,168.8,null,{"$f":"SUM(E30:H30)","v":74.400000000000006}]
[]
[null,"Required return (K) = ",0.13,null,"NPV =",{"$f":"NPV(C32,F30:H30)+E30","v":17.11686974191511},"IRR =",{"$f":"IRR(E30:H30)","v":0.18422796692630294}]
[]
[null,"CF shareholder when renting",null,null,0,1,2,3]
[null,"Change in NFO",null,null,null,{"$f":"E14-F14","v":-120},{"$f":"F14-G14","v":0},120]
[null,"Change in line of credit",null,null,null,{"$f":"F18-E18","v":96},{"$f":"G18-F18","v":0},-96]
[null,"Net Income when renting",null,null,0,{"$f":"$J$11","v":8.16},{"$f":"$J$11","v":8.16},{"$f":"$J$11","v":8.16},null,"Sum of CF"]
[null,"CF shareholder (Rent)",null,null,{"$f":"E35+E36+E37","v":0},-15.84,8.16,32.159999999999997,null,{"$f":"SUM(E38:H38)","v":24.479999999999997}]
[]
[null,null,null,null,"NPV =",{"$f":"NPV(C32,F38:H38)+E38","v":14.66127104013662},"IRR =",{"$f":"IRR(E38:H38)","v":0.70555652443481698}]
[]
[]
[]
[null,null,null,null,"Case 7. Comparisons of Projects. SOLVED. "]
[]
["Case","CF shareholder or ECF",null,0,1,2,3,"IRR",null,"Sum of CF"]
[1,"New machine, no debt",null,-300,-28,92,332,0.1000188925132417,null,{"$f":"SUM(D47:G47)","v":96}]
[2,"Risks Worst",null,-330,-89.8,45.2,312.2,-5.8751578537388438E-2,null,{"$f":"SUM(D48:G48)","v":-62.400000000000034}]
[2,"Risks Best",null,-270,39.799999999999997,138.80000000000001,345.8,0.30027686560496547,null,{"$f":"SUM(D49:G49)","v":254.40000000000003}]
[3,"New machine with debt",null,-120,0.79999999999999716,23.36,169.76,0.18267186571826977,null,{"$f":"SUM(D50:G50)","v":73.919999999999987}]
["3b","New machine with debt, Worst case",null,-150,-49,-23.92,137.48000000000002,-0.17459138654184225,null,-85.44]
["3c","New machine with debt, Best case",null,-90,51.799999999999983,70.831999999999994,201.03199999999998,0.7522264968437915,null,233.66399999999996]
[4,"New machine with inflation, no debt",null,-300,-28,88,360.8,0.12249343497344056,null,120.80000000000001]
["4b","New machine with inflation and debt",null,-120,0.79999999999999716,28.960000000000022,188.57600000000002,0.2340795222783163,null,98.336000000000041]
[5,"Old machine, with 0 debt ",null,0,-88,32,152,0.50859268357512954,null,96]
["5b","Old machine, with debt",null,0,8,28.160000000000004,52.16,"na",null,88.32]
[6,"Renting vs Buy",null,0,-15.84,8.16,32.159999999999997,0.71,null,{"$f":"SUM(D57:G57)","v":24.479999999999997}]

{"sheet":"Casos 1-5 FAZER","index":15,"n_rows":84,"n_cols":14,"rows_kept":84,"cut":false,"hidden":true,"merged":[[71,1,71,3]]}
[null,null,null,null,"TEMPLATE PARA SOLUCIONAR OS CASOS 1 A 5"]
[null,"Premissas em negrito e verde. Para realizar os cálculos, veja as notas dentro das células."]
[]
[null,"Premissas da Demonstração de Resultados (DRE)","Premissas",null,null,1,2,3]
[null,"Peças produzidas",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000}]
[null,"Preço unitário de venda em euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":10},{"$f":"G6*(1+$C$8)","v":10}]
[null,"Custo unitário em euros",8,null,null,{"$f":"C7","v":8},{"$f":"F7*(1+$C$8)","v":8},{"$f":"G7*(1+$C$8)","v":8}]
[null,"Inflação esperada",0]
[]
[null,"DRE (€ ’000)",null,null,null,1,2,3]
[null,"Vendas"]
[null,"Custo de Mercadorias Vendidas (CMV)"]
[null,"Margem Bruta"]
[null,"Despesas Operacionais (Opex)",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":20},{"$f":"G14*(1+$C8)","v":20}]
[null,"EBITDA"]
[null,"Depreciação",5,"anos"]
[null,"EBIT",null,null,null,{"$f":"F15-F16","v":0},{"$f":"G15-G16","v":0},{"$f":"H15-H16","v":0}]
[null,"Despesa de juros",0.05]
[null,"Lucro antes de impostos",null,null,null,{"$f":"F17-F18","v":0},{"$f":"G17-G18","v":0},{"$f":"H17-H18","v":0}]
[null,"Impostos",0.2,null,null,{"$f":"F19*$C$20","v":0},{"$f":"G19*$C$20","v":0},{"$f":"H19*$C$20","v":0}]
[null,"Lucro Líquido",null,null,null,{"$f":"F19-F20","v":0},{"$f":"G19-G20","v":0},{"$f":"H19-H20","v":0}]
[null,"Dividendos pagos",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},0]
[null,null,null,null,null,null,null,null,"Média"]
[null,"EBITDA / Investimento inicial",null,null,null,{"$f":"F15/$E$31","v":0},{"$f":"G15/$E$31","v":0},{"$f":"H15/$E$31","v":0},{"$f":"AVERAGE(F24:H24)","v":0}]
[null,"RONA (EBIT / AL)",null,null,null,{"$f":"F17/E32","v":0},{"$f":"G17/F32","v":{"$error":"#DIV/0!"}},{"$f":"H17/G32","v":{"$error":"#DIV/0!"}},{"$f":"AVERAGE(F25:H25)","v":{"$error":"#DIV/0!"}}]
[null,"ROA (Lucro Líquido / AL)",null,null,null,{"$f":"F21/E32","v":0},{"$f":"G21/F32","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"},{"$f":"AVERAGE(F26:H26)","v":{"$error":"#DIV/0!"}}]
[null,"ROE (Lucro Líquido / Patrimônio)",null,null,null,{"$f":"F21/E37","v":{"$error":"#DIV/0!"}},{"$f":"G21/F37","v":{"$error":"#DIV/0!"}},{"$f":"H21/G37","v":{"$error":"#DIV/0!"}},{"$f":"AVERAGE(F27:H27)","v":{"$error":"#DIV/0!"}}]
[]
[null,"Balanço Resumido (Curto)",null,null,0,1,2,3,null,null,"Premissas para NOF"]
[null,"NOF (20% Vendas)",0.2,"x Vendas",null,{"$f":"$C$30*F11","v":0},{"$f":"$C$30*G11","v":0},0,null,null,"Contas a Receber",90,null,"NOF como % das Vendas"]
[null,"AF, Líquido",0,"Capex",300,null,null,null,null,null,"Estoques",10,null,{"$f":"(L30+L31*0.85-L32*0.85)/365","v":0.2}]
[null,"AL, Ativo Líquido",null,null,{"$f":"E31+E30","v":300},{"$f":"F31+F30","v":0},{"$f":"G31+G30","v":0},{"$f":"H31+H30","v":0},null,null,"Contas a Pagar",30]
[]
[null,"Linha de crédito D-S",0,"do NOF",{"$f":"$C34*E30","v":0},{"$f":"$C34*F30","v":0},{"$f":"$C34*G30","v":0},0]
[null,"Empréstimo D-L",3,"anos",0,{"$f":"E35-($E35/$C35)","v":0},{"$f":"F35-($E35/$C35)","v":0},0]
[null,"D, Dívida Total",null,null,{"$f":"E34+E35","v":0},{"$f":"F34+F35","v":0},{"$f":"G34+G35","v":0},0]
[null,"E, Patrimônio Líq."]
[null,"Financiamento Total",null,null,{"$f":"E36+E37","v":0},{"$f":"F36+F37","v":0},0,0]
[]
[null,null,null,"Caixa no Balanço (+ ou -)"]
[null,null,null,"Aumento anual do Caixa"]
[null,null,null,"ECF = Aumento no Caixa + Diviendos Pagos"]
[]
[null,"FC para o acionista ou ECF",null,null,0,1,2,3]
[null,"Lucro Líquido"]
[null,"Variação no AL"]
[null,"Variação na Dívida",null,null,null,null,null,null,"Soma dos FCs"]
[null,"Equity CFou FC acionista",null,null,{"$f":"SUM(E45:E47)","v":0},{"$f":"SUM(F45:F47)","v":0},{"$f":"SUM(G45:G47)","v":0},{"$f":"SUM(H45:H47)","v":0},{"$f":"SUM(E48:H48)","v":0}]
[null,"TIR =",{"$f":"IRR(E48:H48)","v":{"$error":"#NUM!"}}]
[]
[null,"FCF após impostos",null,null,0,1,2,3]
[null,"EBIT x (1-t)"]
[null,"Variação no AL"]
[null,"FCF após impostos",null,null,{"$f":"SUM(E52:E53)","v":0},{"$f":"SUM(F52:F53)","v":0},{"$f":"SUM(G52:G53)","v":0},{"$f":"SUM(H52:H53)","v":0}]
[null,"TIR =",{"$f":"IRR(E54:H54)","v":{"$error":"#NUM!"}}]
[]
[]
[]
[null,"Riscos. Análise de Sensibilidade.",null,null,null,null,"PARA O CASO 2"]
[]
[null,null,null,"Maiores riscos"]
[null,"Cenários","Base","Pior","Melhor","Intervalo"]
[null,"Preço",10,9,11]
[null,"TIR acionista"]
[null,"Custo da Máquina FA",300,330,270]
[null,"TIR acionista"]
[null,"Tamanho do NOF%",0.2,0.25,0.15]
[null,"TIR acionista",null,null,null,{"$f":"E68-D68","v":0}]
[null,"Risco Total",null,null,null,{"$f":"E69-D69","v":0}]
[]
[]
[null,"TIR e Nível de Dívida",null,null,null,null,"PARA O CASO 3"]
[null,"Dívida","TIR","Aumento"]
[null,"€ 60M ou 20% do AF"]
[null,"€ 120M€ ou 40% do AF"]
[null,"€ 180M€ ou 60% do AF"]
[null,"€ 240M ou 80% do AF"]
[]
[null,null,null,null,null,null,"PARA O CASO 5"]
[]
[null,"CF acionista ou ECF",null,0,1,2,3,"TIR","Soma FCs"]
[null,"Máquina nova sem dívida (caso 1)",null,-300,-28,92,332,{"$f":"IRR(D82:G82)","v":0.1000188925132417},{"$f":"SUM(D82:G82)","v":96}]
[null,"Máquina nova com dívida (caso 3)",null,-120,0.79999999999999716,23.36,169.76,0.18267186571826977,73.919999999999987]
[null,"Máquina antiga (caso 5)",null,0,-88,32,152,{"$f":"IRR(D84:G84)","v":0.50859268357512954},{"$f":"SUM(D84:G84)","v":96}]

{"sheet":"Caso 6 FAZER","index":16,"n_rows":57,"n_cols":13,"rows_kept":57,"cut":false,"hidden":true,"merged":[]}
[null,null,null,null,"Caso 6. Projeto de redução de custos. Comprar ou alugar? FAZER"]
[null,"Premissas em negrito verde"]
[null,null,null,null,null,null,"Comprar"]
[null,"DRE","Premissas",null,null,1,2,3,null,"Alugar"]
[null,"EBITDA",100,null,null,{"$f":"$C5","v":100},{"$f":"$C5","v":100},100,null,{"$f":"$C5","v":100}]
[null,"Depreciação ou pgto aluguel",5,"anos",null,null,null,null,null,85]
[null,"EBIT",null,null,null,{"$f":"F5-F6","v":100},{"$f":"G5-G6","v":100},100,null,{"$f":"J5-J6","v":15},null,null,"Alugar"]
[null,"Despesa com juros",0.05,null,null,null,null,null,null,null,null,null,-300]
[null,"Lucro antes de impostos",null,null,null,null,null,null,null,null,null,null,85]
[null,"Imposto",0.2,null,null,null,null,null,null,null,null,null,{"$f":"M9","v":85}]
[null,"LL, Lucro líquido",null,null,null,null,null,null,null,null,null,null,{"$f":"M10","v":85}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"M11","v":85}]
[null,"Balanço patrimonial ao comprar",null,null,0,1,2,3,null,null,null,null,{"$f":"M12","v":85}]
[null,"NOF",null,null,null,120,null,0]
[null,"AF (máquina)",null,null,300,null,null,0,null,null,null,null,{"$f":"IRR(M8:M13)","v":0.12858463526289921}]
[null,"AL, Ativos líquidos"]
[]
[null,"Linha de crédito (D-curto prazo)",0.8]
[null,"Empréstimo (D-longo prazo)",3,"anos",180]
[null,"D, Dívida total"]
[null,"E, Patrimônio líquido"]
[null,"Patrimônio líquido + Dívida"]
[null,null,null,"Caixa Excedente"]
[null,null,null,"Caixa Gerado, FC"]
[]
[null,"CF acionista ao comprar",null,null,0,1,2,3]
[null,"Variação nos ativos",null,null,{"$f":"-E16","v":0},{"$f":"E16-F16","v":0},{"$f":"F16-G16","v":0},{"$f":"G16-H16","v":0}]
[null,"Variação na dívida",null,null,{"$f":"E20","v":0},{"$f":"F20-E20","v":0},{"$f":"G20-F20","v":0},0]
[null,"Lucro líquido",null,null,null,{"$f":"$F$11","v":0},{"$f":"$F$11","v":0},{"$f":"$F$11","v":0},null,"Soma FCs"]
[null,"CF acionista (Compra)",null,null,{"$f":"E27+E28+E29","v":0},0,0,0,null,{"$f":"SUM(E30:H30)","v":0}]
[]
[null,"Retorno exigido (K) =",0.13,null,"VPL =",{"$f":"NPV(C32,F30:H30)+E30","v":0},"TIR =",{"$f":"IRR(E30:H30)","v":{"$error":"#NUM!"}}]
[]
[null,"CF acionista ao alugar",null,null,0,1,2,3]
[null,"Variação em NOF"]
[null,"Variação na linha de crédito"]
[null,"Lucro líquido ao alugar",null,null,null,null,null,null,null,"Soma FCs"]
[null,"CF acionista (Aluguel)",null,null,null,null,null,null,null,{"$f":"SUM(E38:H38)","v":0}]
[]
[null,null,null,null,"VPL =",{"$f":"NPV(C32,F38:H38)+E38","v":0},"TIR =",{"$f":"IRR(E38:H38)","v":{"$error":"#NUM!"}}]
[]
[]
[]
[null,null,null,null,"Caso 7. Comparação entre projetos. RESOLVIDO. "]
[]
["Caso","CF acionista ou FC acionista",null,0,1,2,3,"TIR",null,"Soma FCs"]
[1,"Máquina nova, sem dívida",null,-300,-28,92,332,0.1000188925132417,null,{"$f":"SUM(D47:G47)","v":96}]
[2,"Riscos Pior cenário",null,-330,-89.8,45.2,312.2,-5.8751578537388438E-2,null,{"$f":"SUM(D48:G48)","v":-62.400000000000034}]
[2,"Riscos Melhor cenário",null,-270,39.799999999999997,138.80000000000001,345.8,0.30027686560496547,null,{"$f":"SUM(D49:G49)","v":254.40000000000003}]
[3,"Máquina nova com dívida",null,-120,0.79999999999999716,23.36,169.76,0.18267186571826977,null,{"$f":"SUM(D50:G50)","v":73.919999999999987}]
["3b","Máquina nova com dívida, Pior cenário",null,-150,-49,-23.92,137.48000000000002,-0.17459138654184225,null,-85.44]
["3c","Máquina nova com dívida, Melhor cenário",null,-90,51.799999999999983,70.831999999999994,201.03199999999998,0.7522264968437915,null,233.66399999999996]
[4,"Máquina nova com inflação, sem dívida",null,-300,-28,88,360.8,0.12249343497344056,null,120.80000000000001]
["4b","Máquina nova com inflação e dívida",null,-120,0.79999999999999716,28.960000000000022,188.57600000000002,0.2340795222783163,null,98.336000000000041]
[5,"Máquina antiga, sem dívida",null,0,-88,32,152,0.50859268357512954,null,96]
["5b","Máquina antiga, com dívida",null,0,8,28.160000000000004,52.16,"na",null,88.32]
[6,"Aluguel vs Compra",null,0,-15.84,8.16,32.159999999999997,0.71,null,24.479999999999997]

{"sheet":"Anexos 1&2","index":17,"n_rows":84,"n_cols":14,"rows_kept":84,"cut":false,"hidden":true,"merged":[[71,1,71,3]]}
[null,null,null,null,"Casos 1&2. Retorno e risco do projeto. RESOLVIDO"]
[null,"Premissas em negrito verde. Para fazer os cálculos, veja as notas dentro das células."]
[]
[null,"Premissas do DRE","Premissas",null,null,1,2,3]
[null,"Peças produzidas",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000}]
[null,"Preço unitário de venda em euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":10},{"$f":"G6*(1+$C$8)","v":10}]
[null,"Custo unitário em euros",8,null,null,{"$f":"C7","v":8},{"$f":"F7*(1+$C$8)","v":8},{"$f":"G7*(1+$C$8)","v":8}]
[null,"Inflação esperada",0]
[]
[null,"DRE (€ ’000)",null,null,null,1,2,3]
[null,"Vendas",null,null,null,{"$f":"F5*F6/1000","v":600},{"$f":"G5*G6/1000","v":600},{"$f":"H5*H6/1000","v":600}]
[null,"Custo de Mercadorias Vendidas (CMV)",null,null,null,{"$f":"F7*F5/1000","v":480},{"$f":"G7*G5/1000","v":480},{"$f":"H7*H5/1000","v":480}]
[null,"Margem Bruta",null,null,null,{"$f":"F11-F12","v":120},{"$f":"G11-G12","v":120},{"$f":"H11-H12","v":120}]
[null,"Despesas Operacionais (Opex)",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":20},{"$f":"G14*(1+$C8)","v":20}]
[null,"EBITDA",null,null,null,{"$f":"F13-F14","v":100},{"$f":"G13-G14","v":100},{"$f":"H13-H14","v":100}]
[null,"Depreciação",5,"Ano",null,{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60}]
[null,"EBIT",null,null,null,{"$f":"F15-F16","v":40},{"$f":"G15-G16","v":40},{"$f":"H15-H16","v":40}]
[null,"Despesa de juros",0.05,null,null,{"$f":"E36*$C$18","v":0},{"$f":"F36*$C$18","v":0},{"$f":"G36*$C$18","v":0}]
[null,"Lucro antes de impostos",null,null,null,{"$f":"F17-F18","v":40},{"$f":"G17-G18","v":40},{"$f":"H17-H18","v":40}]
[null,"Impostos",0.2,null,null,{"$f":"F19*$C$20","v":8},{"$f":"G19*$C$20","v":8},{"$f":"H19*$C$20","v":8}]
[null,"Lucro Líquido",null,null,null,{"$f":"F19-F20","v":32},{"$f":"G19-G20","v":32},{"$f":"H19-H20","v":32}]
[null,"Dividendos pagos",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},0]
[null,null,null,null,null,null,null,null,"Average"]
[null,"EBITDA / Investimento inicial",null,null,null,{"$f":"F15/$E$31","v":0.33333333333333331},{"$f":"G15/$E$31","v":0.33333333333333331},{"$f":"H15/$E$31","v":0.33333333333333331},{"$f":"AVERAGE(F24:H24)","v":0.33333333333333331}]
[null,"RONA (EBIT / AL)",null,null,null,{"$f":"F17/E32","v":0.13333333333333333},{"$f":"G17/F32","v":0.1111111111111111},{"$f":"H17/G32","v":0.13333333333333333},{"$f":"AVERAGE(F25:H25)","v":0.12592592592592591}]
[null,"ROA (LL / AL)",null,null,null,{"$f":"F21/E32","v":0.10666666666666667},{"$f":"G21/F32","v":8.8888888888888892E-2},0.10666666666666667,{"$f":"AVERAGE(F26:H26)","v":0.10074074074074074}]
[null,"ROE (LL / PL)",null,null,null,{"$f":"F21/E37","v":0.10666666666666667},{"$f":"G21/F37","v":9.6385542168674704E-2},{"$f":"H21/G37","v":8.7912087912087919E-2},{"$f":"AVERAGE(F27:H27)","v":9.698809891580977E-2}]
[]
[null,"Balanço Curto ou Resumido",null,null,0,1,2,3,null,null,"Premissas NOF"]
[null,"NOF (20% Vendas)",0.2,"x vendas",null,{"$f":"$C$30*F11","v":120},{"$f":"$C$30*G11","v":120},0,null,null,"Contas a Receber",90,null,"NFO em % vendas"]
[null,"AF, Líquido",0,"Capex",300,{"$f":"E31-F16+$C31","v":240},{"$f":"F31-G16+$C31","v":180},0,null,null,"Estoques",10,null,{"$f":"(L30+L31*0.85-L32*0.85)/365","v":0.2}]
[null,"AL, Ativos Líquidos",null,null,{"$f":"E31+E30","v":300},{"$f":"F31+F30","v":360},{"$f":"G31+G30","v":300},{"$f":"H31+H30","v":0},null,null,"Contas a Pagar",30]
[]
[null,"Linha de crédito D-S",0,"do NOF",{"$f":"$C34*E30","v":0},{"$f":"$C34*F30","v":0},{"$f":"$C34*G30","v":0},0]
[null,"Empréstimo D-L",3,"anos",0,{"$f":"E35-($E35/$C35)","v":0},{"$f":"F35-($E35/$C35)","v":0},0]
[null,"D, Dívida Total",null,null,{"$f":"E34+E35","v":0},{"$f":"F34+F35","v":0},{"$f":"G34+G35","v":0},0]
[null,"E, Patrimônio Líq. (Equity)",null,null,{"$f":"E31-E35","v":300},{"$f":"E37+F21-F22","v":332},{"$f":"F37+G21-G22","v":364},396]
[null,"Financiamento Total",null,null,{"$f":"E36+E37","v":300},{"$f":"F36+F37","v":332},364,396]
[]
[null,null,null,"Caixa no Balanço (+ ou -)",{"$f":"E38-E32","v":0},{"$f":"F38-F32","v":-28},{"$f":"G38-G32","v":64},{"$f":"H38-H32","v":396}]
[null,null,null,"Aumento anual do Caixa",null,{"$f":"F40-E40","v":-28},{"$f":"G40-F40","v":92},{"$f":"H40-G40","v":332}]
[null,null,null,"ECF = Aumento no Caixa + Diviendos Pagos",{"$f":"-E37","v":-300},{"$f":"F41+F22","v":-28},{"$f":"G41+G22","v":92},332]
[]
[null,"FC para o acionista ou ECF",null,null,0,1,2,3]
[null,"Lucro Líquido",null,null,null,{"$f":"F21","v":32},{"$f":"G21","v":32},{"$f":"H21","v":32}]
[null,"Variação no AL",null,null,{"$f":"-E32","v":-300},{"$f":"E32-F32","v":-60},{"$f":"F32-G32","v":60},{"$f":"G32-H32","v":300}]
[null,"Variação na Dívida",null,null,{"$f":"E36","v":0},{"$f":"F36-E36","v":0},{"$f":"G36-F36","v":0},{"$f":"H36-G36","v":0},"Soma FC"]
[null,"Equity CF ou FC acionista",null,null,{"$f":"SUM(E45:E47)","v":-300},{"$f":"SUM(F45:F47)","v":-28},{"$f":"SUM(G45:G47)","v":92},{"$f":"SUM(H45:H47)","v":332},{"$f":"SUM(E48:H48)","v":96}]
[null,"TIR =",{"$f":"IRR(E48:H48)","v":0.1000188925132417}]
[]
[null,"FCF após impostos",null,null,0,1,2,3]
[null,"EBIT x (1-t)",null,null,null,{"$f":"F17*(1-$C20)","v":32},{"$f":"G17*(1-$C20)","v":32},{"$f":"H17*(1-$C20)","v":32}]
[null,"Variação no AL",null,null,{"$f":"E46","v":-300},{"$f":"F46","v":-60},60,300]
[null,"FCF após impostos",null,null,{"$f":"SUM(E52:E53)","v":-300},{"$f":"SUM(F52:F53)","v":-28},{"$f":"SUM(G52:G53)","v":92},{"$f":"SUM(H52:H53)","v":332}]
[null,"TIR =",{"$f":"IRR(E54:H54)","v":0.1000188925132417}]
[]
[]
[]
[null,"Riscos. Análise de Sensibilidade.",null,null,null,null,"FOR CASE 2"]
[]
[null,null,null,"Maiores riscos"]
[null,"Cenários","Base","Pior","Melhor","Intervalo"]
[null,"Preço",10,9,11]
[null,"TIR acionista",0.1,-5.1319434967612376E-2,0.24481039833418961,{"$f":"E64-D64","v":0.29612983330180198}]
[null,"Custo da Máquina FA",300,330,270]
[null,"TIR acionista",{"$f":"C64","v":0.1},7.9008814853287923E-2,0.12455999895101888,{"$f":"E66-D66","v":4.5551184097730957E-2}]
[null,"Tamanho do NOF%",0.2,0.25,0.15]
[null,"TIR acionista",{"$f":"C64","v":0.1},9.4386474155067512E-2,0.10632719227147946,{"$f":"E68-D68","v":1.1940718116411952E-2}]
[null,"Risco Total",0.1,-5.8751578537388438E-2,0.30027686560496569,{"$f":"E69-D69","v":0.35902844414235413}]
[]
[]
[null,"TIR e Nível de Dívida",null,null,null,null,"FOR CASE 3"]
[null,"Dívida","TIR","Aumento"]
[null,"€ 60M ou 20% do AF",0.128]
[null,"€ 120M ou 40% do AF",0.14899999999999999,{"$f":"C75-C74","v":2.0999999999999991E-2}]
[null,"€ 180M ou 60% do AF",0.183,{"$f":"C76-C75","v":3.4000000000000002E-2}]
[null,"€ 240M ou 80% do AF",0.251,6.8000000000000005E-2]
[]
[null,null,null,null,null,null,"FOR CASE 5"]
[]
[null,"CF acionista ou ECF",null,0,1,2,3,"TIR","Soma FCs"]
[null,"Máquina nova sem dívida (caso 1)",null,-300,-28,92,332,{"$f":"IRR(D82:G82)","v":0.1000188925132417},{"$f":"SUM(D82:G82)","v":96}]
[null,"Máquina nova com dívida (caso 3)",null,-120,0.79999999999999716,23.36,169.76,0.18267186571826977,73.919999999999987]
[null,"Máquina antiga (caso 5)",null,0,-88,32,152,{"$f":"IRR(D84:G84)","v":0.50859268357512954},{"$f":"SUM(D84:G84)","v":96}]

{"sheet":"Anexo 3","index":18,"n_rows":77,"n_cols":14,"rows_kept":77,"cut":false,"hidden":true,"merged":[[71,1,71,3]]}
[null,null,null,null,"Caso 3. Projeto financiado com dívida. RESOLVIDO"]
[null,"Premissas em negrito verde. Para fazer os cálculos, veja as notas dentro das células."]
[null,null,null,null,null,null,null,null,null,null,"Premissas NOF"]
[null,"Premissas do DRE","Premissas",null,null,1,2,3,null,null,"Contas a Receber",90,null,"NFO em % vendas"]
[null,"Peças produzidas",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000},null,null,"Estoques",10,null,{"$f":"(L4+L5*0.85-L6*0.85)/365","v":0.2}]
[null,"Preço unitário de venda em euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":10},{"$f":"G6*(1+$C$8)","v":10},null,null,"Contas a Pagar",30]
[null,"Custo unitário em euros",8,null,null,{"$f":"C7","v":8},{"$f":"F7*(1+$C$8)","v":8},{"$f":"G7*(1+$C$8)","v":8}]
[null,"Inflação esperada",0]
[]
[null,"DRE (€ ’000)",null,null,null,1,2,3]
[null,"Vendas",null,null,null,{"$f":"F5*F6/1000","v":600},{"$f":"G5*G6/1000","v":600},{"$f":"H5*H6/1000","v":600}]
[null,"Custo de Mercadorias Vendidas (CMV)",null,null,null,{"$f":"F7*F5/1000","v":480},{"$f":"G7*G5/1000","v":480},{"$f":"H7*H5/1000","v":480}]
[null,"Margem Bruta",null,null,null,{"$f":"F11-F12","v":120},{"$f":"G11-G12","v":120},{"$f":"H11-H12","v":120}]
[null,"Despesas Operacionais (Opex)",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":20},{"$f":"G14*(1+$C8)","v":20}]
[null,"EBITDA",null,null,null,{"$f":"F13-F14","v":100},{"$f":"G13-G14","v":100},{"$f":"H13-H14","v":100}]
[null,"Depreciação",5,"Ano",null,{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60}]
[null,"EBIT",null,null,null,{"$f":"F15-F16","v":40},{"$f":"G15-G16","v":40},{"$f":"H15-H16","v":40}]
[null,"Despesa de juros",0.05,null,null,{"$f":"E36*$C$18","v":9},{"$f":"F36*$C$18","v":10.8},{"$f":"G36*$C$18","v":7.8000000000000007}]
[null,"Lucro antes de impostos",null,null,null,{"$f":"F17-F18","v":31},{"$f":"G17-G18","v":29.2},{"$f":"H17-H18","v":32.200000000000003}]
[null,"Impostos",0.2,null,null,{"$f":"F19*$C$20","v":6.2},{"$f":"G19*$C$20","v":5.84},{"$f":"H19*$C$20","v":6.4400000000000013}]
[null,"Lucro Líquido",null,null,null,{"$f":"F19-F20","v":24.8},{"$f":"G19-G20","v":23.36},{"$f":"H19-H20","v":25.76}]
[null,"Dividendos pagos",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},0]
[null,null,null,null,null,null,null,null,"Média"]
[null,"EBITDA / Investimento inicial",null,null,null,{"$f":"F15/$E$31","v":0.33333333333333331},{"$f":"G15/$E$31","v":0.33333333333333331},{"$f":"H15/$E$31","v":0.33333333333333331},{"$f":"AVERAGE(F24:H24)","v":0.33333333333333331}]
[null,"RONA (EBIT / AL)",null,null,null,{"$f":"F17/E32","v":0.13333333333333333},{"$f":"G17/F32","v":0.1111111111111111},{"$f":"H17/G32","v":0.13333333333333333},{"$f":"AVERAGE(F25:H25)","v":0.12592592592592591}]
[null,"ROA (LL / AL)",null,null,null,{"$f":"F21/E32","v":8.2666666666666666E-2},{"$f":"G21/F32","v":6.4888888888888885E-2},8.5866666666666674E-2,{"$f":"AVERAGE(F26:H26)","v":7.7807407407407408E-2}]
[null,"ROE (LL / PL)",null,null,null,{"$f":"F21/E37","v":0.20666666666666667},{"$f":"G21/F37","v":0.1613259668508287},{"$f":"H21/G37","v":0.15318744053282587},{"$f":"AVERAGE(F27:H27)","v":0.17372669135010707}]
[]
[null,"Balanço Curto ou Resumido",null,null,0,1,2,3]
[null,"NOF (20% Vendas)",0.2,"x vendas",null,{"$f":"$C$30*F11","v":120},{"$f":"$C$30*G11","v":120},0]
[null,"AF, Líquido",0,"Capex",300,{"$f":"E31-F16+$C31","v":240},{"$f":"F31-G16+$C31","v":180},0]
[null,"AL, Ativos Líquidos",null,null,{"$f":"E31+E30","v":300},{"$f":"F31+F30","v":360},{"$f":"G31+G30","v":300},{"$f":"H31+H30","v":0}]
[]
[null,"Linha de crédito D-S",0.8,"do NOF",{"$f":"$C34*E30","v":0},{"$f":"$C34*F30","v":96},{"$f":"$C34*G30","v":96},0]
[null,"Empréstimo D-L",3,"anos",180,{"$f":"E35-($E35/$C35)","v":120},{"$f":"F35-($E35/$C35)","v":60},0]
[null,"D, Dívida Total",null,null,{"$f":"E34+E35","v":180},{"$f":"F34+F35","v":216},{"$f":"G34+G35","v":156},0]
[null,"E, Patrimônio Líq. (Equity)",null,null,{"$f":"E31-E35","v":120},{"$f":"E37+F21-F22","v":144.80000000000001},{"$f":"F37+G21-G22","v":168.16000000000003},193.92000000000002]
[null,"Financiamento Total",null,null,{"$f":"E36+E37","v":300},{"$f":"F36+F37","v":360.8},324.16000000000003,193.92000000000002]
[]
[null,null,null,"Caixa no Balanço (+ ou -)",{"$f":"E38-E32","v":0},{"$f":"F38-F32","v":0.80000000000001137},{"$f":"G38-G32","v":24.160000000000025},{"$f":"H38-H32","v":193.92000000000002}]
[null,null,null,"Aumento anual do Caixa",null,{"$f":"F40-E40","v":0.80000000000001137},{"$f":"G40-F40","v":23.360000000000014},{"$f":"H40-G40","v":169.76}]
[null,null,null,"ECF = Aumento no Caixa + Diviendos Pagos",{"$f":"-E37","v":-120},{"$f":"F41+F22","v":0.80000000000001137},{"$f":"G41+G22","v":23.360000000000014},169.76]
[]
[null,"FC para o acionista ou ECF",null,null,0,1,2,3]
[null,"Lucro Líquido",null,null,null,{"$f":"F21","v":24.8},{"$f":"G21","v":23.36},{"$f":"H21","v":25.76}]
[null,"Variação no AL",null,null,{"$f":"-E32","v":-300},{"$f":"E32-F32","v":-60},{"$f":"F32-G32","v":60},{"$f":"G32-H32","v":300}]
[null,"Variação na Dívida",null,null,{"$f":"E36","v":180},{"$f":"F36-E36","v":36},{"$f":"G36-F36","v":-60},{"$f":"H36-G36","v":-156},"Soma FCs"]
[null,"Equity CFou FC acionista",null,null,{"$f":"SUM(E45:E47)","v":-120},{"$f":"SUM(F45:F47)","v":0.79999999999999716},{"$f":"SUM(G45:G47)","v":23.36},{"$f":"SUM(H45:H47)","v":169.76},{"$f":"SUM(E48:H48)","v":73.919999999999987}]
[null,"TIR =",{"$f":"IRR(E48:H48)","v":0.18267186571826977}]
[]
[null,"FCF após impostos",null,null,0,1,2,3]
[null,"EBIT x (1-t)",null,null,null,{"$f":"F17*(1-$C20)","v":32},{"$f":"G17*(1-$C20)","v":32},{"$f":"H17*(1-$C20)","v":32}]
[null,"Variação no AL",null,null,{"$f":"E46","v":-300},{"$f":"F46","v":-60},60,300]
[null,"FCF após impostos",null,null,{"$f":"SUM(E52:E53)","v":-300},{"$f":"SUM(F52:F53)","v":-28},{"$f":"SUM(G52:G53)","v":92},{"$f":"SUM(H52:H53)","v":332}]
[null,"TIR =",{"$f":"IRR(E54:H54)","v":0.1000188925132417}]
[]
[]
[]
[null,"Riscos. Análise de Sensibilidade."]
[]
[null,null,null,"Maiores riscos"]
[null,"Cenários","Base","Pior","Melhor","Intervalo"]
[null,"Preço",10,9,11]
[null,"TIR acionista",0.1,-5.1319434967612376E-2,0.24481039833418961,{"$f":"E64-D64","v":0.29612983330180198}]
[null,"Custo da Máquina FA",300,330,270]
[null,"TIR acionista",{"$f":"C64","v":0.1},7.9008814853287923E-2,0.12455999895101888,{"$f":"E66-D66","v":4.5551184097730957E-2}]
[null,"Tamanho NOF%",0.2,0.25,0.15]
[null,"TIR acionista",{"$f":"C64","v":0.1},9.4386474155067512E-2,0.10632719227147946,{"$f":"E68-D68","v":1.1940718116411952E-2}]
[null,"Risco Total",0.1,-5.8751578537388438E-2,0.30027686560496569,{"$f":"E69-D69","v":0.35902844414235413}]
[]
[]
[null,"TIR e Nível de Dívida"]
[null,"Dívida","TIR","Aumento"]
[null,"€ 60M ou 20% do AF",0.128]
[null,"€ 120M ou 40% do AF",0.14899999999999999,{"$f":"C75-C74","v":2.0999999999999991E-2}]
[null,"€ 180M ou 60% do AF",0.183,{"$f":"C76-C75","v":3.4000000000000002E-2}]
[null,"€ 240M ou 80% do AF",0.251,6.8000000000000005E-2]

{"sheet":"Anexo 4","index":19,"n_rows":77,"n_cols":14,"rows_kept":77,"cut":false,"hidden":true,"merged":[[71,1,71,3]]}
[null,null,null,null,"Caso 4. Projeto com inflação. RESOLVIDO"]
[null,"Premissas em negrito verde. Para fazer os cálculos, veja as notas dentro das células."]
[null,null,null,null,null,null,null,null,null,null,"Premissas NOF"]
[null,"Premissas do DRE","Premissas",null,null,1,2,3,null,null,"Contas a Receber",90,null,"NOF como % Vendas"]
[null,"Peças produzidas",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000},null,null,"Estoques",10,null,{"$f":"(L4+L5*0.85-L6*0.85)/365","v":0.2}]
[null,"Preço unitário de venda em euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":11},{"$f":"G6*(1+$C$8)","v":12.100000000000001},null,null,"Contas a Pagar",30]
[null,"Custo unitário em euros",8,null,null,{"$f":"C7","v":8},{"$f":"F7*(1+$C$8)","v":8.8000000000000007},{"$f":"G7*(1+$C$8)","v":9.6800000000000015}]
[null,"Inflação esperada",0.1]
[]
[null,"DRE (€ ’000)",null,null,null,1,2,3]
[null,"Vendas",null,null,null,{"$f":"F5*F6/1000","v":600},{"$f":"G5*G6/1000","v":660},{"$f":"H5*H6/1000","v":726.00000000000011}]
[null,"Custo de Mercadorias Vendidas (CMV)",null,null,null,{"$f":"F7*F5/1000","v":480},{"$f":"G7*G5/1000","v":528},{"$f":"H7*H5/1000","v":580.80000000000007}]
[null,"Margem Bruta",null,null,null,{"$f":"F11-F12","v":120},{"$f":"G11-G12","v":132},{"$f":"H11-H12","v":145.20000000000005}]
[null,"Despesas Operacionais (Opex)",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":22},{"$f":"G14*(1+$C8)","v":24.200000000000003}]
[null,"EBITDA",null,null,null,{"$f":"F13-F14","v":100},{"$f":"G13-G14","v":110},{"$f":"H13-H14","v":121.00000000000004}]
[null,"Depreciação",5,"anos",null,{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60},{"$f":"$E31*(1/$C$16)","v":60}]
[null,"EBIT",null,null,null,{"$f":"F15-F16","v":40},{"$f":"G15-G16","v":50},{"$f":"H15-H16","v":61.000000000000043}]
[null,"Despesa de juros",0.05,null,null,{"$f":"E36*$C$18","v":0},{"$f":"F36*$C$18","v":0},{"$f":"G36*$C$18","v":0}]
[null,"Lucro antes de impostos",null,null,null,{"$f":"F17-F18","v":40},{"$f":"G17-G18","v":50},{"$f":"H17-H18","v":61.000000000000043}]
[null,"Impostos",0.2,null,null,{"$f":"F19*$C$20","v":8},{"$f":"G19*$C$20","v":10},{"$f":"H19*$C$20","v":12.20000000000001}]
[null,"Lucro Líquido",null,null,null,{"$f":"F19-F20","v":32},{"$f":"G19-G20","v":40},{"$f":"H19-H20","v":48.800000000000033}]
[null,"Dividendos pagos",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},0]
[null,null,null,null,null,null,null,null,"Média"]
[null,"EBITDA / Investimento inicial",null,null,null,{"$f":"F15/$E$31","v":0.33333333333333331},{"$f":"G15/$E$31","v":0.36666666666666664},{"$f":"H15/$E$31","v":0.40333333333333349},{"$f":"AVERAGE(F24:H24)","v":0.36777777777777781}]
[null,"RONA (EBIT / AL)",null,null,null,{"$f":"F17/E32","v":0.13333333333333333},{"$f":"G17/F32","v":0.1388888888888889},{"$f":"H17/G32","v":0.19551282051282065},{"$f":"AVERAGE(F25:H25)","v":0.15591168091168098}]
[null,"ROA (LL / AL)",null,null,null,{"$f":"F21/E32","v":0.10666666666666667},{"$f":"G21/F32","v":0.1111111111111111},0.15641025641025652,{"$f":"AVERAGE(F26:H26)","v":0.12472934472934477}]
[null,"ROE (LL / PL)",null,null,null,{"$f":"F21/E37","v":0.10666666666666667},{"$f":"G21/F37","v":0.12048192771084337},{"$f":"H21/G37","v":0.13118279569892483},{"$f":"AVERAGE(F27:H27)","v":0.11944379669214496}]
[]
[null,"Balanço Curto ou Resumido",null,null,0,1,2,3]
[null,"NOF (20% Vendas)",0.2,"x Vendas",null,{"$f":"$C$30*F11","v":120},{"$f":"$C$30*G11","v":132},0]
[null,"AF, Líquido",0,"Capex",300,{"$f":"E31-F16+$C31","v":240},{"$f":"F31-G16+$C31","v":180},0]
[null,"AL, Ativos Líquidos",null,null,{"$f":"E31+E30","v":300},{"$f":"F31+F30","v":360},{"$f":"G31+G30","v":312},{"$f":"H31+H30","v":0}]
[]
[null,"Linha de crédito D-S",0,"% NOF",{"$f":"$C34*E30","v":0},{"$f":"$C34*F30","v":0},{"$f":"$C34*G30","v":0},0]
[null,"Empréstimo D-L",3,"anos",0,{"$f":"E35-($E35/$C35)","v":0},{"$f":"F35-($E35/$C35)","v":0},0]
[null,"D, Dívida Total",null,null,{"$f":"E34+E35","v":0},{"$f":"F34+F35","v":0},{"$f":"G34+G35","v":0},0]
[null,"E, Patrimônio Líq. (Equity)",null,null,{"$f":"E31-E35","v":300},{"$f":"E37+F21-F22","v":332},{"$f":"F37+G21-G22","v":372},420.8]
[null,"Financiamento Total",null,null,{"$f":"E36+E37","v":300},{"$f":"F36+F37","v":332},372,420.8]
[]
[null,null,null,"Caixa no Balanço (+ ou -)",{"$f":"E38-E32","v":0},{"$f":"F38-F32","v":-28},{"$f":"G38-G32","v":60},{"$f":"H38-H32","v":420.8}]
[null,null,null,"Aumento anual do Caixa",null,{"$f":"F40-E40","v":-28},{"$f":"G40-F40","v":88},{"$f":"H40-G40","v":360.8}]
[null,null,null,"ECF = Aumento no Caixa + Diviendos Pagos",{"$f":"-E37","v":-300},{"$f":"F41+F22","v":-28},{"$f":"G41+G22","v":88},360.8]
[]
[null,"FC para o acionista ou ECF",null,null,0,1,2,3]
[null,"Lucro Líquido",null,null,null,{"$f":"F21","v":32},{"$f":"G21","v":40},{"$f":"H21","v":48.800000000000033}]
[null,"Variação no AL",null,null,{"$f":"-E32","v":-300},{"$f":"E32-F32","v":-60},{"$f":"F32-G32","v":48},{"$f":"G32-H32","v":312}]
[null,"Variação na Dívida",null,null,{"$f":"E36","v":0},{"$f":"F36-E36","v":0},{"$f":"G36-F36","v":0},{"$f":"H36-G36","v":0},"Soma FC"]
[null,"Equity CFou FC acionista",null,null,{"$f":"SUM(E45:E47)","v":-300},{"$f":"SUM(F45:F47)","v":-28},{"$f":"SUM(G45:G47)","v":88},{"$f":"SUM(H45:H47)","v":360.8},{"$f":"SUM(E48:H48)","v":120.80000000000001}]
[null,"TIR =",{"$f":"IRR(E48:H48)","v":0.12249343497344056}]
[]
[null,"FCF após impostos",null,null,0,1,2,3]
[null,"EBIT x (1-t)",null,null,null,{"$f":"F17*(1-$C20)","v":32},{"$f":"G17*(1-$C20)","v":40},{"$f":"H17*(1-$C20)","v":48.80000000000004}]
[null,"Variação no AL",null,null,{"$f":"E46","v":-300},{"$f":"F46","v":-60},48,312]
[null,"FCF após impostos",null,null,{"$f":"SUM(E52:E53)","v":-300},{"$f":"SUM(F52:F53)","v":-28},{"$f":"SUM(G52:G53)","v":88},{"$f":"SUM(H52:H53)","v":360.80000000000007}]
[null,"TIR =",{"$f":"IRR(E54:H54)","v":0.12249343497344078}]
[]
[]
[]
[null,"Riscos. Análise de Sensibilidade."]
[]
[null,null,null,"Maiores riscos"]
[null,"Cenários","Base","Pior","Melhor","Intervalo"]
[null,"Preço",10,9,11]
[null,"TIR acionista",0.1,-5.1319434967612376E-2,0.24481039833418961,{"$f":"E64-D64","v":0.29612983330180198}]
[null,"Custo da Máquina FA",300,330,270]
[null,"TIR acionista",{"$f":"C64","v":0.1},7.9008814853287923E-2,0.12455999895101888,{"$f":"E66-D66","v":4.5551184097730957E-2}]
[null,"Tamanho NOF%",0.2,0.25,0.15]
[null,"TIR acionista",{"$f":"C64","v":0.1},9.4386474155067512E-2,0.10632719227147946,{"$f":"E68-D68","v":1.1940718116411952E-2}]
[null,"Risco Total",0.1,-5.8751578537388438E-2,0.30027686560496569,{"$f":"E69-D69","v":0.35902844414235413}]
[]
[]
[null,"TIR e Nível de Dívida"]
[null,"Dívida","TIR","Aumento"]
[null,"€ 60M ou 20% do AF",0.128]
[null,"€ 120M ou 40% do AF",0.14899999999999999,{"$f":"C75-C74","v":2.0999999999999991E-2}]
[null,"€ 180M ou 60% do AF",0.183,{"$f":"C76-C75","v":3.4000000000000002E-2}]
[null,"€ 240M ou 80% do AF",0.251,6.8000000000000005E-2]

{"sheet":"Anexo 5","index":20,"n_rows":85,"n_cols":14,"rows_kept":85,"cut":false,"hidden":true,"merged":[[71,1,71,3]]}
[null,null,null,null,"Caso 5. Comparação de 2 projetos. RESOLVIDO"]
[null,"Premissas em negrito verde. Para fazer os cálculos, veja as notas dentro das células."]
[null,null,null,null,null,null,null,null,null,null,"Premissas NOF"]
[null,"Premissas do DRE","Premissas",null,null,1,2,3,null,null,"Contas a Receber",90,null,"NOF como % Vendas"]
[null,"Peças produzidas",60000,null,null,{"$f":"C5","v":60000},{"$f":"F5","v":60000},{"$f":"G5","v":60000},null,null,"Estoques",10,null,{"$f":"(L4+L5*0.85-L6*0.85)/365","v":0.2}]
[null,"Preço unitário de venda em euros",10,null,null,{"$f":"C6","v":10},{"$f":"F6*(1+$C$8)","v":10},{"$f":"G6*(1+$C$8)","v":10},null,null,"Contas a Pagar",30]
[null,"Custo unitário em euros",9,null,null,{"$f":"C7","v":9},{"$f":"F7*(1+$C$8)","v":9},{"$f":"G7*(1+$C$8)","v":9}]
[null,"Inflação esperada",0]
[]
[null,"DRE (€ ’000)",null,null,null,1,2,3]
[null,"Vendas",null,null,null,{"$f":"F5*F6/1000","v":600},{"$f":"G5*G6/1000","v":600},{"$f":"H5*H6/1000","v":600}]
[null,"Custo de Mercadorias Vendidas (CMV)",null,null,null,{"$f":"F7*F5/1000","v":540},{"$f":"G7*G5/1000","v":540},{"$f":"H7*H5/1000","v":540}]
[null,"Margem Bruta",null,null,null,{"$f":"F11-F12","v":60},{"$f":"G11-G12","v":60},{"$f":"H11-H12","v":60}]
[null,"Despesas Operacionais (Opex)",20,null,null,{"$f":"C14","v":20},{"$f":"F14*(1+$C8)","v":20},{"$f":"G14*(1+$C8)","v":20}]
[null,"EBITDA",null,null,null,{"$f":"F13-F14","v":40},{"$f":"G13-G14","v":40},{"$f":"H13-H14","v":40}]
[null,"Depreciação",5,"anos",null,{"$f":"$E31*(1/$C$16)","v":0},{"$f":"$E31*(1/$C$16)","v":0},{"$f":"$E31*(1/$C$16)","v":0}]
[null,"EBIT",null,null,null,{"$f":"F15-F16","v":40},{"$f":"G15-G16","v":40},{"$f":"H15-H16","v":40}]
[null,"Despesa de juros",0.05,null,null,{"$f":"E36*$C$18","v":0},{"$f":"F36*$C$18","v":0},{"$f":"G36*$C$18","v":0}]
[null,"Lucro antes de impostos",null,null,null,{"$f":"F17-F18","v":40},{"$f":"G17-G18","v":40},{"$f":"H17-H18","v":40}]
[null,"Impostos",0.2,null,null,{"$f":"F19*$C$20","v":8},{"$f":"G19*$C$20","v":8},{"$f":"H19*$C$20","v":8}]
[null,"Lucro Líquido",null,null,null,{"$f":"F19-F20","v":32},{"$f":"G19-G20","v":32},{"$f":"H19-H20","v":32}]
[null,"Dividendos pagos",0,null,null,{"$f":"$C22*F21","v":0},{"$f":"$C22*G21","v":0},0]
[null,null,null,null,null,null,null,null,"Média"]
[null,"EBITDA / Investimento inicial",null,null,null,"na","na","na","na"]
[null,"RONA (EBIT / AL)",null,null,null,"na",{"$f":"G17/F32","v":0.33333333333333331},{"$f":"H17/G32","v":0.33333333333333331},{"$f":"AVERAGE(F25:H25)","v":0.33333333333333331}]
[null,"ROA (LL / AL)",null,null,null,"na",{"$f":"G21/F32","v":0.26666666666666666},0.26666666666666666,{"$f":"AVERAGE(F26:H26)","v":0.26666666666666666}]
[null,"ROE (LL / PL)",null,null,null,"na",{"$f":"G21/F37","v":1},{"$f":"H21/G37","v":0.5},{"$f":"AVERAGE(F27:H27)","v":0.75}]
[]
[null,"Balanço Curto ou Resumido",null,null,0,1,2,3]
[null,"NOF (20% Vendas)",0.2,"x Vendas",null,{"$f":"$C$30*F11","v":120},{"$f":"$C$30*G11","v":120},0]
[null,"AF, Líquido",0,"Capex",0,{"$f":"E31-F16+$C31","v":0},{"$f":"F31-G16+$C31","v":0},0]
[null,"AL, Ativos Líquidos",null,null,{"$f":"E31+E30","v":0},{"$f":"F31+F30","v":120},{"$f":"G31+G30","v":120},{"$f":"H31+H30","v":0}]
[]
[null,"Linha de crédito D-S",0,"% NOF",{"$f":"$C34*E30","v":0},{"$f":"$C34*F30","v":0},{"$f":"$C34*G30","v":0},0]
[null,"Empréstimo D-L",3,"anos",0,{"$f":"E35-($E35/$C35)","v":0},{"$f":"F35-($E35/$C35)","v":0},0]
[null,"D, Dívida Total",null,null,{"$f":"E34+E35","v":0},{"$f":"F34+F35","v":0},{"$f":"G34+G35","v":0},0]
[null,"E, Patrimônio Líq. (Equity)",null,null,{"$f":"E31-E35","v":0},{"$f":"E37+F21-F22","v":32},{"$f":"F37+G21-G22","v":64},96]
[null,"Financiamento Total",null,null,{"$f":"E36+E37","v":0},{"$f":"F36+F37","v":32},64,96]
[]
[null,null,null,"Caixa no Balanço (+ ou -)",{"$f":"E38-E32","v":0},{"$f":"F38-F32","v":-88},{"$f":"G38-G32","v":-56},{"$f":"H38-H32","v":96}]
[null,null,null,"Aumento anual do Caixa",null,{"$f":"F40-E40","v":-88},{"$f":"G40-F40","v":32},{"$f":"H40-G40","v":152}]
[null,null,null,"ECF = Aumento no Caixa + Diviendos Pagos",{"$f":"-E37","v":0},{"$f":"F41+F22","v":-88},{"$f":"G41+G22","v":32},152]
[]
[null,"FC para o acionista ou ECF Máquina Antiga",null,null,0,1,2,3]
[null,"Lucro Líquido",null,null,null,{"$f":"F21","v":32},{"$f":"G21","v":32},{"$f":"H21","v":32}]
[null,"Variação no AL",null,null,{"$f":"-E32","v":0},{"$f":"E32-F32","v":-120},{"$f":"F32-G32","v":0},{"$f":"G32-H32","v":120}]
[null,"Variação na Dívida",null,null,{"$f":"E36","v":0},{"$f":"F36-E36","v":0},{"$f":"G36-F36","v":0},{"$f":"H36-G36","v":0},"Soma FC"]
[null,"Equity CFou FC acionista",null,null,{"$f":"SUM(E45:E47)","v":0},{"$f":"SUM(F45:F47)","v":-88},{"$f":"SUM(G45:G47)","v":32},{"$f":"SUM(H45:H47)","v":152},{"$f":"SUM(E48:H48)","v":96}]
[null,"TIR =",{"$f":"IRR(E48:H48)","v":0.50859268357512954}]
[]
[null,"FCF after tax",null,null,0,1,2,3]
[null,"EBIT x (1-t)",null,null,null,{"$f":"F17*(1-$C20)","v":32},{"$f":"G17*(1-$C20)","v":32},{"$f":"H17*(1-$C20)","v":32}]
[null,"Change in NA",null,null,{"$f":"E46","v":0},{"$f":"F46","v":-120},0,120]
[null,"FCF after tax",null,null,{"$f":"SUM(E52:E53)","v":0},{"$f":"SUM(F52:F53)","v":-88},{"$f":"SUM(G52:G53)","v":32},{"$f":"SUM(H52:H53)","v":152}]
[null,"IRR  =",{"$f":"IRR(E54:H54)","v":0.50859268357512954}]
[]
[]
[]
[null,"Risks. Sensitivity Analysis."]
[]
[null,null,"Biggest Risk"]
[null,"Scenarios","Base","Worst","Best","Range"]
[null,"Price",10,9,11]
[null,"IRR shareholder",0.1,-5.1319434967612376E-2,0.24481039833418961,{"$f":"E64-D64","v":0.29612983330180198}]
[null,"Cost of Machine FA",300,330,270]
[null,"IRR shareholder",{"$f":"C64","v":0.1},7.9008814853287923E-2,0.12455999895101888,{"$f":"E66-D66","v":4.5551184097730957E-2}]
[null,"Size of NFO%",0.2,0.25,0.15]
[null,"IRR shareholder",{"$f":"C64","v":0.1},9.4386474155067512E-2,0.10632719227147946,{"$f":"E68-D68","v":1.1940718116411952E-2}]
[null,"Total Risk ",0.1,-5.8751578537388438E-2,0.30027686560496569,{"$f":"E69-D69","v":0.35902844414235413}]
[]
[]
[null,"IRR  and Level of Debt"]
[null,"Debt","IRR","Increase"]
[null,"60M€ or 20% of FA",0.128]
[null,"120M€ or 40% of FA",0.14899999999999999,{"$f":"C75-C74","v":2.0999999999999991E-2}]
[null,"180M€ or 60% of FA",0.183,{"$f":"C76-C75","v":3.4000000000000002E-2}]
[null,"240M€ or 80% of FA",0.251,6.8000000000000005E-2]
[]
[null,null,null,null,null,"FOR CASE 5"]
[]
[null,"Equity CF ou FC acionista",null,0,1,2,3,"TIR","Soma FC"]
[1,"Nova máquina sem dívida (caso 1)",null,-300,-28,92,332,0.1000188925132417,96]
[3,"Nova máquina com dívida (caso 3)",null,-120,0.79999999999999716,23.36,169.76,0.18267186571826977,73.919999999999987]
[5,"Máquina antiga, sem dívida (caso 5)",null,0,-88,32,152,0.50859268357512954,96]
["5b","Máquina antiga, com dívida (caso 5)",null,0,8,28.160000000000004,52.16,"na",88.32]

{"sheet":"Anexos 6&7","index":21,"n_rows":57,"n_cols":13,"rows_kept":57,"cut":false,"hidden":true,"merged":[]}
[null,null,null,null,"Caso 6. Projeto de redução de custos. Comprar ou alugar? FEITO"]
[null,"Premissas em negrito verde"]
[null,null,null,null,null,null,"Comprar"]
[null,"DRE","Premissas",null,null,1,2,3,null,"Alugar"]
[null,"EBITDA",100,null,null,{"$f":"$C5","v":100},{"$f":"$C5","v":100},100,null,{"$f":"$C5","v":100}]
[null,"Depreciação ou pgto aluguel",5,"anos",null,{"$f":"E15/C6","v":60},{"$f":"F6","v":60},{"$f":"G6","v":60},null,85]
[null,"EBIT",null,null,null,{"$f":"F5-F6","v":40},{"$f":"G5-G6","v":40},40,null,{"$f":"J5-J6","v":15},null,null,"Aluguel"]
[null,"Despesa com juros",0.05,null,null,{"$f":"$C8*E20","v":9},{"$f":"$C8*F20","v":10.8},7.8000000000000007,null,{"$f":"$C8*F18","v":4.8000000000000007},null,null,-300]
[null,"Lucro antes de impostos",null,null,null,{"$f":"F7-F8","v":31},{"$f":"G7-G8","v":29.2},32.200000000000003,null,{"$f":"J7-J8","v":10.199999999999999},null,null,85]
[null,"Imposto",0.2,null,null,{"$f":"$C10*F9","v":6.2},{"$f":"$C10*G9","v":5.84},6.4400000000000013,null,{"$f":"J9*C10","v":2.04},null,null,{"$f":"M9","v":85}]
[null,"LL, Lucro líquido",null,null,null,{"$f":"F9-F10","v":24.8},{"$f":"G9-G10","v":23.36},25.76,null,{"$f":"J9-J10","v":8.16},null,null,{"$f":"M10","v":85}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"M11","v":85}]
[null,"Balanço patrimonial ao comprar",null,null,0,1,2,3,null,null,null,null,{"$f":"M12","v":85}]
[null,"NOF",null,null,null,120,{"$f":"$F$14","v":120},0]
[null,"AF (máquina)",null,null,300,{"$f":"E15-$F6","v":240},{"$f":"F15-$F6","v":180},0,null,null,null,null,{"$f":"IRR(M8:M13)","v":0.12858463526289921}]
[null,"AL, Ativos líquidos",null,null,{"$f":"E14+E15","v":300},{"$f":"F14+F15","v":360},300,{"$f":"H15+H14","v":0}]
[]
[null,"Linha de crédito (D-curto prazo)",0.8,null,null,{"$f":"$C18*F14","v":96},{"$f":"$C18*G14","v":96},{"$f":"$C18*H14","v":0}]
[null,"Empréstimo (D-longo prazo)",3,"anos",180,{"$f":"E19-($E19/$C19)","v":120},{"$f":"F19-($E19/$C19)","v":60},0]
[null,"D, Dívida total",null,null,{"$f":"E18+E19","v":180},{"$f":"F18+F19","v":216},156,0]
[null,"E, Patrimônio líquido",null,null,{"$f":"E16-E20","v":120},{"$f":"E21+$F$11","v":144.80000000000001},{"$f":"F21+$F$11","v":169.60000000000002},{"$f":"G21+$F$11","v":194.40000000000003}]
[null,"Patrimônio líquido + Dívida",null,null,{"$f":"E20+E21","v":300},{"$f":"F20+F21","v":360.8},325.60000000000002,194.40000000000003]
[null,null,null,"Caixa Excedente",{"$f":"E22-E16","v":0},{"$f":"F22-F16","v":0.80000000000001137},{"$f":"G22-G16","v":25.600000000000023},{"$f":"H22-H16","v":194.40000000000003}]
[null,null,null,"Caixa Gerado, FC",{"$f":"-E21","v":-120},{"$f":"F23-E23","v":0.80000000000001137},{"$f":"G23-F23","v":24.800000000000011},{"$f":"H23-G23","v":168.8}]
[]
[null,"FC acionista ao comprar",null,null,0,1,2,3]
[null,"Variação nos ativos",null,null,{"$f":"-E16","v":-300},{"$f":"E16-F16","v":-60},{"$f":"F16-G16","v":60},{"$f":"G16-H16","v":300}]
[null,"Variação na dívida",null,null,{"$f":"E20","v":180},{"$f":"F20-E20","v":36},{"$f":"G20-F20","v":-60},-156]
[null,"Lucro líquido",null,null,null,{"$f":"$F$11","v":24.8},{"$f":"$F$11","v":24.8},{"$f":"$F$11","v":24.8},null,"Soma FC"]
[null,"FC acionista (Compra)",null,null,{"$f":"E27+E28+E29","v":-120},0.80000000000000071,24.8,168.8,null,{"$f":"SUM(E30:H30)","v":74.400000000000006}]
[]
[null,"Retorno exigido (K) =",0.13,null,"VPL =",{"$f":"NPV(C32,F30:H30)+E30","v":17.11686974191511},"TIR =",{"$f":"IRR(E30:H30)","v":0.18422796692630294}]
[]
[null,"FC acionista ao alugar",null,null,0,1,2,3]
[null,"Variação em NOF",null,null,null,{"$f":"E14-F14","v":-120},{"$f":"F14-G14","v":0},120]
[null,"Variação na linha de crédito",null,null,null,{"$f":"F18-E18","v":96},{"$f":"G18-F18","v":0},-96]
[null,"Lucro líquido ao alugar",null,null,0,{"$f":"$J$11","v":8.16},{"$f":"$J$11","v":8.16},{"$f":"$J$11","v":8.16},null,"Soma FC"]
[null,"FC acionista (Aluguel)",null,null,{"$f":"E35+E36+E37","v":0},-15.84,8.16,32.159999999999997,null,{"$f":"SUM(E38:H38)","v":24.479999999999997}]
[]
[null,null,null,null,"VPL =",{"$f":"NPV(C32,F38:H38)+E38","v":14.66127104013662},"TIR =",{"$f":"IRR(E38:H38)","v":0.70555652443481698}]
[]
[]
[]
[null,null,null,null,"Caso 7. Comparação entre projetos. RESOLVIDO. "]
[]
["Caso","CF acionista ou ECF",null,0,1,2,3,"TIR",null,"Soma FC"]
[1,"Nova máquina, sem dívida",null,-300,-28,92,332,0.1000188925132417,null,{"$f":"SUM(D47:G47)","v":96}]
[2,"Riscos - Pior Cenário",null,-330,-89.8,45.2,312.2,-5.8751578537388438E-2,null,{"$f":"SUM(D48:G48)","v":-62.400000000000034}]
[2,"Riscos - Melhor Cenário",null,-270,39.799999999999997,138.80000000000001,345.8,0.30027686560496547,null,{"$f":"SUM(D49:G49)","v":254.40000000000003}]
[3,"Nova máquina com dívida",null,-120,0.79999999999999716,23.36,169.76,0.18267186571826977,null,{"$f":"SUM(D50:G50)","v":73.919999999999987}]
["3b","Nova máquina com dívida, Pior Cenário",null,-150,-49,-23.92,137.48000000000002,-0.17459138654184225,null,-85.44]
["3c","Nova máquina com dívida, Melhor Cenário",null,-90,51.799999999999983,70.831999999999994,201.03199999999998,0.7522264968437915,null,233.66399999999996]
[4,"Nova máquina com inflação, sem dívida",null,-300,-28,88,360.8,0.12249343497344056,null,120.80000000000001]
["4b","Nova máquina com inflação e dívida",null,-120,0.79999999999999716,28.960000000000022,188.57600000000002,0.2340795222783163,null,98.336000000000041]
[5,"Máquina antiga, sem dívida",null,0,-88,32,152,0.50859268357512954,null,96]
["5b","Máquina antiga, com dívida",null,0,8,28.160000000000004,52.16,"na",null,88.32]
[6,"Alugar vs Comprar",null,0,-15.84,8.16,32.159999999999997,0.71,null,24.479999999999997]