Plastics in Space
Johnnetwork 
Copyofltv-worksheet.xlsxLTV Template
| Assumptions | Entries | Notes | ||||||
| One Time Sale | ||||||||
| Average Initial Sale | $0 | This model assumes that Initial purchase is made at beginning of the year 0. | ||||||
| Gross Margin - Initial Sale | 0.0% | If there is no initial purchase, enter $0 for Average Initial Sale. | ||||||
| Next Product Purchase Rate | 0% | |||||||
| Next Product Purchase Year | 0 | If product is purchased 1 or mores times a year, use the Periodic Purchases section below | ||||||
| Periodic Purchases | Customer buys 1 or more times a year | |||||||
| Yearly Revenue | $0 | Typical of many businesses | ||||||
| Gross Margin - Yearly Revenue | 0.0% | |||||||
| Retention Rate | 0.0% | |||||||
| Recurring Revenue | Customer pays on a recurring basis | |||||||
| Average Recurring Revenue | $0 | Revenue per billing period | ||||||
| Gross Margin - Recurring Revenue | 0.0% | Customer pays on a recurring basis | ||||||
| # months of recurring in Year 1 | 0 | Could be a monthly subscription | ||||||
| # months of recurring in Years 2-5 | 0 | Could be a quarterly subscription box | ||||||
| Retention Rate | 0.0% | |||||||
| Addon Sales | Any other sales that do not fit in above categories | |||||||
| Yearly Addon Sales - Year 1 start | $0 | |||||||
| Gross Margin - Addon sales | 0.0% | |||||||
| Retention Rate | 0.0% | |||||||
| Cost of Capital | Assume 50% if giving up equity | |||||||
| Cost of Capital | 50% | If borrowing money, use 12% | ||||||
| Day | Year | Year | Year | Year | Year | |||
| LTV Calculation | 0 | 1 | 2 | 3 | 4 | 5 | ||
| Initial Sale Revenue | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Gross Margin - Initial Sale | 0% | 0% | 0% | 0% | 0% | 0% | ||
| Repurchase Rate | 0% | 0% | 0% | 0% | 0% | |||
| Profit from initial sale | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Periodic Purchases - Yearly Revenue | $0 | $0 | $0 | $0 | $0 | |||
| Retention Rate | 0% | 0% | 0% | 0% | 0% | |||
| Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | |||
| Gross Margin for Yearly Revenue | 0% | 0% | 0% | 0% | 0% | Gross margin is the difference between revenue and cost of goods sold (COGS) divided by revenue. | ||
| Profit from Yearly Revenue | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Recurring Revenue | $0 | $0 | $0 | $0 | $0 | |||
| Retention Rate | 0% | 0% | 0% | 0% | 0% | |||
| Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | |||
| Gross Margin for Recurring | 0% | 0% | 0% | 0% | 0% | |||
| Profit from Recurring | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Addon Sales Revenue | $0 | $0 | $0 | $0 | $0 | |||
| Retention Rate | 0% | 0% | 0% | 0% | 0% | |||
| Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | |||
| Gross Margin for Addon sales | 0% | 0% | 0% | 0% | 0% | |||
| Profit from Addon sales | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Total Profits | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Cost of capital rate | 50% | 50% | 50% | 50% | 50% | 50% | ||
| Net Present Value Factor | 1.0000 | 0.6667 | 0.4444 | 0.2963 | 0.1975 | 0.1317 | ||
| Present value of Profits | $0 | $0 | $0 | $0 | $0 | $0 | ||
| LTV/Net Present Value of Profits | $0 | |||||||
| LTV/CAC ratio | 3 | 4 | 5 | 6 | 8 | |||
| CAC (Cost of Customer Acquisition) | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | |||
Example 1
| Example - periodic purchases with a quarterly subscription box | ||||||
| Assumptions | Entries | Notes | ||||
| One Time Sale | ||||||
| Average Initial Sale | $0 | This model assumes that Initial purchase is made at beginning of the year 0. | ||||
| Gross Margin - Initial Sale | 0.0% | If there is no initial purchase, enter $0 for Average Initial Sale. | ||||
| Next Product Purchase Rate | 0% | |||||
| Next Product Purchase Year | 0 | If product is purchased 1 or mores times a year, use the Periodic Purchases section below | ||||
| Periodic Purchases | Customer buys 1 or more times a year | |||||
| Yearly Revenue | $32 | Typical of many businesses | ||||
| Gross Margin - Yearly Revenue | 68.0% | |||||
| Retention Rate | 80.0% | |||||
| Recurring Revenue | Customer pays on a recurring basis | |||||
| Average Recurring Revenue | $40 | Revenue per billing period | ||||
| Gross Margin - Recurring Revenue | 68.0% | Customer pays on a recurring basis | ||||
| # months of recurring in Year 1 | 4 | Could be a monthly subscription | ||||
| # months of recurring in Years 2-5 | 4 | Could be a quarterly subscription box | ||||
| Retention Rate | 80.0% | |||||
| Addon Sales | Any other sales that do not fit in above categories | |||||
| Yearly Addon Sales - Year 1 start | $0 | |||||
| Gross Margin - Addon sales | 0.0% | |||||
| Retention Rate | 0.0% | |||||
| Cost of Capital | Assume 50% if giving up equity | |||||
| Cost of Capital | 12% | If borrowing money, use 12% | ||||
| Day | Year | Year | Year | Year | Year | |
| LTV Calculation | 0 | 1 | 2 | 3 | 4 | 5 |
| Initial Sale Revenue | $0 | $0 | $0 | $0 | $0 | $0 |
| Gross Margin - Initial Sale | 0% | 0% | 0% | 0% | 0% | 0% |
| Repurchase Rate | 0% | 0% | 0% | 0% | 0% | |
| Profit from initial sale | $0 | $0 | $0 | $0 | $0 | $0 |
| Periodic Purchases - Yearly Revenue | $32 | $32 | $32 | $32 | $32 | |
| Retention Rate | 80% | 80% | 80% | 80% | 80% | |
| Cumulative Retention Rate | 80% | 64% | 51% | 41% | 33% | |
| Gross Margin for Yearly Revenue | 68% | 68% | 68% | 68% | 68% | |
| Profit from Yearly Revenue | $0 | $17 | $14 | $11 | $9 | $7 |
| Recurring Revenue | $160 | $160 | $160 | $160 | $160 | |
| Retention Rate | 80% | 80% | 80% | 80% | 80% | |
| Cumulative Retention Rate | 80% | 64% | 51% | 41% | 33% | |
| Gross Margin for Recurring | 68% | 68% | 68% | 68% | 68% | |
| Profit from Recurring | $0 | $87 | $70 | $56 | $45 | $36 |
| Addon Sales Revenue | $0 | $0 | $0 | $0 | $0 | |
| Retention Rate | 0% | 0% | 0% | 0% | 0% | |
| Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | |
| Gross Margin for Addon sales | 0% | 0% | 0% | 0% | 0% | |
| Profit from Addon sales | $0 | $0 | $0 | $0 | $0 | $0 |
| Total Profits | $0 | $104 | $84 | $67 | $53 | $43 |
| Cost of capital rate | 12% | 12% | 12% | 12% | 12% | 12% |
| Net Present Value Factor | 1.0000 | 0.8929 | 0.7972 | 0.7118 | 0.6355 | 0.5674 |
| Present value of Profits | $0 | $93 | $67 | $48 | $34 | $24 |
| LTV/Net Present Value of Profits | $266 | |||||
| LTV/CAC ratio | 3 | 4 | 5 | 6 | 8 | |
| CAC (Cost of Customer Acquisition) | $88.57 | $66.43 | $53.14 | $44.29 | $33.21 | |
Example 2
| Example - recurring revenue only - monthly subscription | ||||||
| Assumptions | Entries | Notes | ||||
| One Time Sale | ||||||
| Average Initial Sale | $0 | This model assumes that Initial purchase is made at beginning of the year 0. | ||||
| Gross Margin - Initial Sale | 0.0% | If there is no initial purchase, enter $0 for Average Initial Sale. | ||||
| Next Product Purchase Rate | 0% | |||||
| Next Product Purchase Year | 0 | If product is purchased 1 or mores times a year, use the Periodic Purchases section below | ||||
| Periodic Purchases | Customer buys 1 or more times a year | |||||
| Yearly Revenue | $0 | Typical of many businesses | ||||
| Gross Margin - Yearly Revenue | 0.0% | |||||
| Retention Rate | 0.0% | |||||
| Recurring Revenue | Customer pays on a recurring basis | |||||
| Average Recurring Revenue | $20 | Revenue per billing period | ||||
| Gross Margin - Recurring Revenue | 95.0% | Customer pays on a recurring basis | ||||
| # months of recurring in Year 1 | 12 | Could be a monthly subscription | ||||
| # months of recurring in Years 2-5 | 12 | Could be a quarterly subscription box | ||||
| Retention Rate | 85.0% | |||||
| Addon Sales | Any other sales that do not fit in above categories | |||||
| Yearly Addon Sales - Year 1 start | $0 | |||||
| Gross Margin - Addon sales | 0.0% | |||||
| Retention Rate | 0.0% | |||||
| Cost of Capital | Assume 50% if giving up equity | |||||
| Cost of Capital | 50% | If borrowing money, use 12% | ||||
| Day | Year | Year | Year | Year | Year | |
| LTV Calculation | 0 | 1 | 2 | 3 | 4 | 5 |
| Initial Sale Revenue | $0 | $0 | $0 | $0 | $0 | $0 |
| Gross Margin - Initial Sale | 0% | 0% | 0% | 0% | 0% | 0% |
| Repurchase Rate | 0% | 0% | 0% | 0% | 0% | |
| Profit from initial sale | $0 | $0 | $0 | $0 | $0 | $0 |
| Periodic Purchases - Yearly Revenue | $0 | $0 | $0 | $0 | $0 | |
| Retention Rate | 0% | 0% | 0% | 0% | 0% | |
| Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | |
| Gross Margin for Yearly Revenue | 0% | 0% | 0% | 0% | 0% | |
| Profit from Yearly Revenue | $0 | $0 | $0 | $0 | $0 | $0 |
| Recurring Revenue | $240 | $240 | $240 | $240 | $240 | |
| Retention Rate | 85% | 85% | 85% | 85% | 85% | |
| Cumulative Retention Rate | 85% | 72% | 61% | 52% | 44% | |
| Gross Margin for Recurring | 95% | 95% | 95% | 95% | 95% | |
| Profit from Recurring | $0 | $194 | $165 | $140 | $119 | $101 |
| Addon Sales Revenue | $0 | $0 | $0 | $0 | $0 | |
| Retention Rate | 0% | 0% | 0% | 0% | 0% | |
| Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | |
| Gross Margin for Addon sales | 0% | 0% | 0% | 0% | 0% | |
| Profit from Addon sales | $0 | $0 | $0 | $0 | $0 | $0 |
| Total Profits | $0 | $194 | $165 | $140 | $119 | $101 |
| Cost of capital rate | 50% | 50% | 50% | 50% | 50% | 50% |
| Net Present Value Factor | 1.0000 | 0.6667 | 0.4444 | 0.2963 | 0.1975 | 0.1317 |
| Present value of Profits | $0 | $129 | $73 | $41 | $24 | $13 |
| LTV/Net Present Value of Profits | $281 | |||||
| LTV/CAC ratio | 3 | 4 | 5 | 6 | 8 | |
| CAC (Cost of Customer Acquisition) | $93.58 | $70.18 | $56.15 | $46.79 | $35.09 | |
Example 3
| Example - 1-time sale and monthly recurring revenue | |||||||||
| Assumptions | Entries | Notes | |||||||
| One Time Sale | |||||||||
| Average Initial Sale | $10,000 | This model assumes that Initial purchase is made at beginning of the year 0. | |||||||
| Gross Margin - Initial Sale | 65.0% | If there is no initial purchase, enter $0 for Average Initial Sale. | Gross margin is the difference between revenue and cost of goods sold (COGS) divided by revenue. | ||||||
| Next Product Purchase Rate | 75% | ||||||||
| Next Product Purchase Year | 5 | If product is purchased 1 or mores times a year, use the Periodic Purchases section below | |||||||
| Periodic Purchases | Customer buys 1 or more times a year | ||||||||
| Yearly Revenue | $0 | Typical of many businesses | |||||||
| Gross Margin - Yearly Revenue | 0.0% | ||||||||
| Retention Rate | 0.0% | ||||||||
| Recurring Revenue | Customer pays on a recurring basis | ||||||||
| Average Recurring Revenue | $125 | Revenue per billing period | |||||||
| Gross Margin - Recurring Revenue | 85.0% | Customer pays on a recurring basis | |||||||
| # months of recurring in Year 1 | 6 | Could be a monthly subscription | |||||||
| # months of recurring in Years 2-5 | 12 | Could be a quarterly subscription box | |||||||
| Retention Rate | 90.0% | ||||||||
| Addon Sales | Any other sales that do not fit in above categories | ||||||||
| Yearly Addon Sales - Year 1 start | $0 | ||||||||
| Gross Margin - Addon sales | 0.0% | ||||||||
| Retention Rate | 0.0% | ||||||||
| Cost of Capital | Assume 50% if giving up equity | ||||||||
| Cost of Capital | 50% | If borrowing money, use 12% | |||||||
| Day | Year | Year | Year | Year | Year | ||||
| LTV Calculation | 0 | 1 | 2 | 3 | 4 | 5 | |||
| Initial Sale Revenue | $10,000 | $0 | $0 | $0 | $0 | $10,000 | |||
| Gross Margin - Initial Sale | 65% | 65% | 65% | 65% | 65% | 65% | |||
| Repurchase Rate | 75% | 75% | 75% | 75% | 75% | ||||
| Profit from initial sale | $6,500 | $0 | $0 | $0 | $0 | $4,875 | |||
| Periodic Purchases - Yearly Revenue | $0 | $0 | $0 | $0 | $0 | ||||
| Retention Rate | 0% | 0% | 0% | 0% | 0% | ||||
| Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | ||||
| Gross Margin for Yearly Revenue | 0% | 0% | 0% | 0% | 0% | ||||
| Profit from Yearly Revenue | $0 | $0 | $0 | $0 | $0 | $0 | |||
| Recurring Revenue | $750 | $1,500 | $1,500 | $1,500 | $1,500 | ||||
| Retention Rate | 90% | 90% | 90% | 90% | 90% | ||||
| Cumulative Retention Rate | 90% | 81% | 73% | 66% | 59% | ||||
| Gross Margin for Recurring | 85% | 85% | 85% | 85% | 85% | ||||
| Profit from Recurring | $0 | $574 | $1,033 | $929 | $837 | $753 | |||
| Addon Sales Revenue | $0 | $0 | $0 | $0 | $0 | ||||
| Retention Rate | 0% | 0% | 0% | 0% | 0% | ||||
| Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | ||||
| Gross Margin for Addon sales | 0% | 0% | 0% | 0% | 0% | ||||
| Profit from Addon sales | $0 | $0 | $0 | $0 | $0 | $0 | |||
| Total Profits | $6,500 | $574 | $1,033 | $929 | $837 | $5,628 | |||
| Cost of capital rate | 50% | 50% | 50% | 50% | 50% | 50% | |||
| Net Present Value Factor | 1.0000 | 0.6667 | 0.4444 | 0.2963 | 0.1975 | 0.1317 | |||
| Present value of Profits | $6,500 | $383 | $459 | $275 | $165 | $741 | |||
| LTV/Net Present Value of Profits | $8,523 | ||||||||
| LTV/CAC ratio | 3 | 4 | 5 | 6 | 8 | ||||
| CAC (Cost of Customer Acquisition) | $2,841.09 | $2,130.81 | $1,704.65 | $1,420.54 | $1,065.41 | ||||
Cost of Equity Capital
| https://www.planprojections.com/funding/cost-of-equity-financing/ | |
| Formula is i=(FV/PV)^(1/n)-1 | |
| Enter the first 5 values and the cost of equity is calculated | |
| Factor | |
| PV - Equity Investment | 70,000 |
| % equity purchased | 40% |
| Value of business at disposal | 940,000 |
| FV = Value of equity at disposal | 376,000 |
| n - number of years to disposal | 5 |
| i = cost of equity | 40% |
Churn
| Churn at different rates | |||||
| Churn Rate | 2% | 5% | 10% | 15% | 20% |
| Initial # customers | 100 | 100 | 100 | 100 | 100 |
| Period 1 | 98.0 | 95.0 | 90.0 | 85.0 | 80.0 |
| Period 2 | 96.0 | 90.3 | 81.0 | 72.3 | 64.0 |
| Period 3 | 94.1 | 85.7 | 72.9 | 61.4 | 51.2 |
| Period 4 | 92.2 | 81.5 | 65.6 | 52.2 | 41.0 |
| Period 5 | 90.4 | 77.4 | 59.0 | 44.4 | 32.8 |
| Period 6 | 88.6 | 73.5 | 53.1 | 37.7 | 26.2 |
| Period 7 | 86.8 | 69.8 | 47.8 | 32.1 | 21.0 |
| Period 8 | 85.1 | 66.3 | 43.0 | 27.2 | 16.8 |
| Period 9 | 83.4 | 63.0 | 38.7 | 23.2 | 13.4 |
| Period 10 | 81.7 | 59.9 | 34.9 | 19.7 | 10.7 |
| Period 11 | 80.1 | 56.9 | 31.4 | 16.7 | 8.6 |
| Period 12 | 78.5 | 54.0 | 28.2 | 14.2 | 6.9 |
| Period 13 | 76.9 | 51.3 | 25.4 | 12.1 | 5.5 |
| Period 14 | 75.4 | 48.8 | 22.9 | 10.3 | 4.4 |
| Period 15 | 73.9 | 46.3 | 20.6 | 8.7 | 3.5 |
| Period 16 | 72.4 | 44.0 | 18.5 | 7.4 | 2.8 |
| Period 17 | 70.9 | 41.8 | 16.7 | 6.3 | 2.3 |
| Period 18 | 69.5 | 39.7 | 15.0 | 5.4 | 1.8 |
| Period 19 | 68.1 | 37.7 | 13.5 | 4.6 | 1.4 |
| Period 20 | 66.8 | 35.8 | 12.2 | 3.9 | 1.2 |
| Period 21 | 65.4 | 34.1 | 10.9 | 3.3 | 0.9 |
| Period 22 | 64.1 | 32.4 | 9.8 | 2.8 | 0.7 |
| Period 23 | 62.8 | 30.7 | 8.9 | 2.4 | 0.6 |
| Period 24 | 61.6 | 29.2 | 8.0 | 2.0 | 0.5 |
| Period 25 | 60.3 | 27.7 | 7.2 | 1.7 | 0.4 |
| Period 26 | 59.1 | 26.4 | 6.5 | 1.5 | 0.3 |
| Period 27 | 58.0 | 25.0 | 5.8 | 1.2 | 0.2 |
| Period 28 | 56.8 | 23.8 | 5.2 | 1.1 | 0.2 |
| Period 29 | 55.7 | 22.6 | 4.7 | 0.9 | 0.2 |
| Period 30 | 54.5 | 21.5 | 4.2 | 0.8 | 0.1 |
| Period 31 | 53.5 | 20.4 | 3.8 | 0.6 | 0.1 |
| Period 32 | 52.4 | 19.4 | 3.4 | 0.6 | 0.1 |
| Period 33 | 51.3 | 18.4 | 3.1 | 0.5 | 0.1 |
| Period 34 | 50.3 | 17.5 | 2.8 | 0.4 | 0.1 |
| Period 35 | 49.3 | 16.6 | 2.5 | 0.3 | 0.0 |
| Period 36 | 48.3 | 15.8 | 2.3 | 0.3 | 0.0 |
