Plastics in Space

profileJohnnetwork
Copyofltv-worksheet.xlsx

LTV 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%
https://www.planprojections.com/funding/cost-of-equity-financing/

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