The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
The purpose of this spreadhseet is for podcasters to get calculations of how effective an advertisement will be for generating profit. You can input your own results and estimates to determine whether/how long an advertisement will return a profit.
2
Prepared byChes Hall
based on the calculations by
see the article that inspired it here:
3
+7 Intelligence Podcast
4
The podcast about how games impact people
5
04/07/2017
6
7
Instructions:
8
First: This sheet is locked, so before inputting any information, Click above at File > Make a Copy to create a copy that you have complete control over.
9
Second: Use the legend below to know which cells you can edit.
10
Third: Each section has its own focus and purpose, but may rely on inputs from previous sections. So I strongly recommend filling out information from the top first.
11
12
Legend
These are values that you input; can be an estimate or an exact value
13
This is a value that is calculated by this spreadsheet; not advised to change
14
A fixed value used for calculation; will cause calculations to be incorrect if changed
15
A benchmark value taken from elsewhere; you can change these if you want
16
*
Means that the value is copied from an earlier cell; this means that it has been repeated for clarity
17
Calculated values that are particularly important
18
19
20
Here, you can input the prices and estimates given by an advertising space
21
Estimated performance
22
Price for advertisement slotViews (Lower)Views (Upper)Taps (Lower)Taps (Upper)Subscriptions (Lower)Subscriptions (Upper)Cost per subscriber (Lower)Cost per subscriber (Upper)
23
\$125.0010000015000010001500100150\$0.83\$1.25
24
Middle1250001250125\$1.04
25
Click-through rate
1.00%10.00%
26
27
Actual Statistics
28
29
30
13981313850.99%16411.84%\$0.76124089.53%
31
32
Compared to estimate
33
These give you an idea of how your results compare to the estimates you were given
34
111.85%110.80%99.06%131.20%118.41%73.17%
35
36
CPM
37
Here you can input the CPM of the sponsorships that you have on your show that generate revenue for you
38
39
220\$12.00\$18.00\$0.00\$0\$60.001000
40
41
42
43
44
90%9001350\$54.00\$81.00-56.80%-35.20%
45
Middle1125\$67.50-46.00%
46
47
48
1240\$74.40-40.48%
49
50
Projected Revenue (By subscriber)
51
This section deals with calculating how long it will take to break even on the cost of an advertisement based on how many downloads each subscriber generates
52
53
54
\$0.06\$0.7612.726.4
55
56
This calculates Inventory filled: ie how much of your monetization opportunities are being used
57
Current CPM
Potential CPM*
% Inventory Filled
58
\$50.00\$60.0083%
59
60
Months until Profitability by % Inventory Filled
61
This table gives you the number of months it will take to break even based on the cost to gain subscribers and the monetization opportunities
62
% Inventory FilledObserved \$/sub*Lower Estimate*Upper Estimate*Middle Estimate*Lower Estimate (Across Categories)Upper Estimate (Across Categories)Middle Estimate (Across Categories)
63
\$0.76\$0.83\$1.25\$1.04\$1.41\$2.21\$1.81
64
10%63.569.4104.286.8117.5184.2150.8
65
20%31.834.752.143.458.892.175.4
66
50%12.713.920.817.423.536.830.2
67
100%6.46.910.48.711.818.415.1
68
Current83.33%7.68.312.510.414.122.118.1
69
70
71
72
Platform
73
iTunesStitcherPodbean
74
Subscribers10014343
75
76
77
Average
78
79
Months until Profitablity Per Subscriber
80
Each table gives you the months until your advertisement breaks even based on a fixed cost per subscriber; it allows you to see the effects of changing your monetization opportunities and how many downloads per subscriber
81
82
Example 1 Cost per subscriber
Current
83
\$1.41
% Inventory Filled
20%50%75%100%105.45%
84
10%587.5235.0156.7117.5111.4
85
20%293.8117.578.358.855.7
86
50%117.547.031.323.522.3
87
100%58.823.515.711.811.1
88
Current83.33%70.528.218.814.113.4
89
90
91
Example 2 Cost per subscriber
Current
92
\$2.21
% Inventory Filled
20%50%75%100%105.45%
93
10%920.8368.3245.6184.2174.6
94
20%460.4184.2122.892.187.3
95
50%184.273.749.136.834.9
96
100%92.136.824.618.417.5
97
Current83.33%110.544.229.522.121.0
98
99
Current Cost per subscriber
100
This table gives you the most accurate estimate of how long it will take for your advertisement to start turning a profit based on your current situation