Economía Unitaria (LTV/CAC y Cohortes)
Cuándo usarlo
Úselo cuando necesite saber si un cliente vale más de lo que cuesta adquirirlo: CAC, margen de contribución bruta, retención, LTV, el ratio LTV/CAC y el periodo de recuperación del CAC en meses. Ideal para negocios SaaS y de suscripción, revisiones de junta directiva y diligencia debida donde una cuadrícula de retención por cohortes respalde los ratios principales. La habilidad genera un archivo .xlsx descargable en la aplicación Claude con openpyxl; no utiliza ningún complemento de Excel ni conexión con Microsoft 365.
Qué construye
Un libro de trabajo con seis pestañas:
- Portada: título, leyenda de colores, selector de escenario, LTV/CAC principal y periodo de recuperación.
- Supuestos: entradas azules (gasto en S&M, nuevos clientes, ARPU, % de margen bruto, abandono, tasa de descuento, multiplicadores de escenario).
- Cohortes: una cuadrícula de retención, cohortes en las filas y meses en las columnas, con % retenido e ingresos por cohorte.
- Economía Unitaria: CAC, ARPU, contribución por cliente, periodo de recuperación.
- LTV-CAC: LTV mediante la fórmula simple y mediante la contribución descontada por cohorte, más el ratio.
- Verificaciones: banderas de consistencia, retención monótona y validez del periodo de recuperación.
Flujo de construcción
- Cree el libro de trabajo y las seis pestañas en el orden anterior.
- En Supuestos, disponga todas las entradas en azul con unidades; añada una celda de escenario (1=Base, 2=Alcista, 3=Osuna) denominada
scn. - Construya el CAC y la contribución por cliente en Economía Unitaria a partir de los gastos y las entradas de clientes.
- Construya la cuadrícula de cohortes: una base del mes 0 del 100% (o clientes iniciales) que se descompone por retención a lo largo de los meses.
- Añada una fila de ingresos y contribución debajo de la cuadrícula de retención por cohorte.
- Construya el LTV de dos maneras en LTV-CAC: forma cerrada y suma descontada de cohortes; calcule el ratio y el periodo de recuperación.
- Construya la pestaña Verificaciones con pruebas de consistencia, monotonía y periodo de recuperación.
- Recalcule el libro de trabajo sin cabeza (LibreOffice) para que todas las fórmulas se computen.
- Verifique que no haya errores de fórmula (#REF!, #DIV/0!, #VALOR!, #NOMBRE?); corrija y recalcule en un bucle, luego entregue.
Especificación pestaña por pestaña
Portada
- Celda de título, línea de propósito del modelo y una fecha de construcción.
- Bloque de leyenda de colores: una muestra azul etiquetada "Entrada", negra etiquetada "Fórmula", verde etiquetada "Enlace entre pestañas".
- Eco del escenario:
=ELEGIR(scn,"Base","Alcista","Osuna")que toma el escenario activo de Supuestos. - Salidas principales como enlaces verdes entre pestañas: LTV/CAC
=LTV-CAC!<celda del ratio>, periodo de recuperación del CAC en meses y LTV descontado. - Una línea de nota que indique que el libro se recalcula en vivo en Excel y fue verificado sin errores en la construcción.
Supuestos
- B2 Selector de escenario (azul, 1/2/3), denominado
scn. - Gasto en S&M por período B5; nuevos clientes adquiridos B6.
- ARPU por mes B7; % de margen bruto B8.
- % de abandono mensual B9 (por lo tanto, retención mensual
=1-B9); tasa de descuento por mes B10. - Multiplicadores de escenario (Base/Alcista/Osuna) para abandono y ARPU: abandono activo
=ELEGIR(scn,...)en B12, ARPU activo=ELEGIR(scn,...)en B13.
Economía Unitaria
- CAC B3
=Supuestos!B5/Supuestos!B6. - ARPU efectivo B4
=Supuestos!B7*Supuestos!B13. - Beneficio bruto por cliente-mes B5
=B4*Supuestos!B8. - Retención mensual B6
=1-Supuestos!B9*Supuestos!B12. - Periodo de recuperación del CAC (meses) B7
=CAC/beneficio bruto por cliente-mes=B3/B5. Si prefiere un periodo de recuperación consciente de cohortes, calcule el beneficio bruto acumulado por mes de cohorte hasta que supere el CAC e informe ese índice de mes. - Vida media del cliente (meses) B8
=1/(Supuestos!B9*Supuestos!B12).
Cohortes
- Fila 3 encabezados de mes 0,1,2,...,N en las columnas.
- Columna A etiquetas de cohorte (Cohorte 1, Cohorte 2, ...).
- % Retenido: celda del mes 0 = 1 (100%); cada mes posterior
=celda_mes_anterior*'Economía Unitaria'!$B$6(aplicar retención mensual). Una fórmula coherente en toda la fila. - Clientes activos por cohorte:
=%retenido * Supuestos!$B$6. - Ingresos por cohorte-mes:
=clientes activos * 'Economía Unitaria'!$B$4. - Contribución por cohorte-mes:
=ingresos * Supuestos!$B$8.
LTV-CAC
- LTV forma cerrada B3
='Economía Unitaria'!B4*Supuestos!B8/('Economía Unitaria'!B6 abandono de vida)es decir=ARPU*GM% / abandono; en celdas='Economía Unitaria'!B4*Supuestos!B8/(Supuestos!B9*Supuestos!B12). - LTV cohorte descontado B4: suma de los meses de contribución por cliente descontada,
=SUMAPRODUCTO(fila_contribución_por_cliente, 1/(1+Supuestos!B10)^índice_mes)usando la fila de cohorte del mes 0 dividida por los clientes iniciales para que sea por cliente. - LTV/CAC B5
=B4/'Economía Unitaria'!B3(use el LTV descontado como principal). - Eco del periodo de recuperación B6
='Economía Unitaria'!B7.
Verificaciones
- Consistencia:
=SI(ABS(LTV_forma_cerrada-LTV_descontado)/LTV_descontado<=Supuestos!tol,"PASA","FALLA"). - Retención monótona dentro de cada cohorte:
=SI(SUMAPRODUCTO(--(celdas_mes_actual>celdas_mes_anterior))=0,"PASA","FALLA")para que ningún mes supere al anterior. - Periodo de recuperación positivo y dentro de un límite establecido (entrada azul).
- LTV/CAC calculado a partir del mismo ARPU, margen y abandono utilizados en la cuadrícula de cohortes.
Convenciones de formato y fórmulas
- Fuente azul para entradas (solo Supuestos). Negra para fórmulas internas de la pestaña. Verde para enlaces entre pestañas.
- Sin números fijos en las fórmulas; las constantes residen en Supuestos. Use referencias absolutas a los impulsores de retención y ARPU para que las filas de cohortes se llenen limpiamente.
- Una fórmula coherente por fila de cohorte que se copie en todos los meses sin ediciones.
- Retención y márgenes como porcentaje; CAC, LTV, ARPU como moneda; periodo de recuperación y vida en meses con un decimal.
- Nombres
scn,tol, CAC y la celda de retención para fórmulas legibles de Verificaciones y LTV.
Verificaciones
- LTV/CAC calculado de manera consistente (forma cerrada y descontado dentro de la tolerancia).
- Periodo de recuperación del CAC informado en meses y positivo.
- Retención monótonamente no creciente dentro de cada cohorte (ningún mes supera al anterior).
- Márgenes y retención entre 0 y 1; CAC y LTV no negativos.
Recalcular y verificar
Después de escribir, recalcule sin cabeza con LibreOffice para que el texto de la fórmula de openpyxl se convierta en valores computados. Escanee cada hoja en busca de #REF!, #DIV/0!, #VALOR!, #NOMBRE?. Tenga en cuenta que LTV y periodo de recuperación dividen por abandono, así que protéjase contra abandono = 0 que produce #DIV/0!. Corrija cualquier fórmula ofensiva o rango nombrado faltante y recalcule de nuevo en un bucle. Entregue solo cuando la pestaña Verificaciones muestre todas las PASA y no queden cadenas de error.
Entradas a recopilar
- Gastos de ventas y marketing y nuevos clientes en el mismo período (para el CAC).
- ARPU por mes y % de margen bruto.
- Abandono mensual (o retención) y una tasa de descuento.
- Número de cohortes y meses a modelar en la cuadrícula.
- Multiplicadores de escenario para abandono y ARPU (Base/Alcista/Osuna) y la banda de tolerancia.
Ejemplo
Hipotético: S&M 200,000, 400 nuevos clientes, por lo tanto CAC 500. ARPU 50 por mes, 80% de margen bruto da 40 de beneficio bruto por cliente-mes, periodo de recuperación 12.5 meses. Abandono del 4% mensual implica una vida media de 25 meses; LTV de forma cerrada alrededor de 1,000 y LTV de cohorte descontado ligeramente inferior; LTV/CAC cerca de 2.0x. La cuadrícula de cohortes descompone cada cohorte 100%, 96%, 92.2%, ... a lo largo de los meses, y la contribución suma el LTV descontado.