Create a Podcast Advertising Calculator

About this Tutorial

Build from scratch a calculator to figure out podcast advertising rates.

Video Transcript

<div> Hey, sheet shakers. That's what you're called. It's the big sheeter Andrew here. This video is gonna be a little different, maybe a little bit longer than you are normally expecting because we are building a calculator. We're literally copying this, so I am. Shamelessly 100% admitting that I am a hundred percent copying this for your benefit. So I'm gonna show you how I take this, which is a Cal podcast advertising calculator, and create it in Google Sheets. I'm. Doing it from scratch. I have not prepared anything. This might be boring.

I'm gonna take some pauses in the video and skip ahead sometimes when I do something pretty rote or if I'm sitting here trying to figure something out or I wanna look at it and redo it a little bit. But we're going to get this done now. In this one video, we're going to have a working copy published to the web. And why because these kinds of calculators are incredibly useful for businesses as, oh God growth act.

If you want to incorporate Attract people that are along your mission that you are helping in some other way. If you have a business, a small business, or a service, and you have this one thing that you do multiple times and you're like, oh, I wish people could do this on their own. This is great. So what advertised cast does is they do ads. They help you put ads into your podcast. And so one of the things that people ask probably most of the time is, how much can I make? And this answers it literally. The question is, how much money can I make from podcast advertising?

And there are a number of variables. All of the variables are here. You don't need to ask follow up questions. You just ask these sort of five questions is how many downloads per episode? How many episodes do you air? How many ad spots do you have to sell every episode? What's your CPM rate? Which is the rate you're gonna pay an advertiser's gonna pay, and how much do you fill? The percentage fill rate? All of those added together gets you this numbers and it quickly. Calculates it up.

What is different about this than say other calculators that I've seen is that this automatically takes out the percentage that advertised cast will take. So if you I just put in some dummy numbers here. How many downloads per episode? Let's say a hundred. How many episodes do you air per year? 10. I put very specifically like round numbers to figure out. I only selected one 32nd spot. I can. Select one sec 60 seconds button. And it doesn't change because I put the same CPM rate here, so a hundred and a hundred.

It gives you some industry standard rates here, which we'll look at and we'll put into the equation there. And a fill rate a hundred percent. I just increase it. So if we did 50, you can see that this number is 50% of a hundred percent. And so what it is, you can tell it's a hundred. You're a hundred dollars. Sorry, a hundred dollars per thousand, let's even do that. So it's a hundred dollars per thousand times 10. Times one times 100% gets you $700 per year or $70 per episode. And as you increase these zero by zeros, you can see they increase here by a zero.

This one by zero, this one by zero. So we'll do that all in a Google sheet as well. And then we'll make it public so that you can embed it or you can my coffee almost spill . You can. Put it out to the world and as a Google sheet, so you can put it in a Google sheet or you can put embedded it into a website. If you already have a website, no matter what, you can use it. And you can also, a, as I do this, you'll see how I build it, and then you'll see how you can build it for yourself for any other type of. Calculator that you're doing, CPM calculator. God, there's so many uses.

If you wanna say how much are TikTok influencers being paid? You can create a calculator for that. How much can I sell if I sell Tahi, Tahitian, Noni Juice? Anything you wanna sell. I don't recommend selling cohesion. No knees. I am not an ML er. Okay. Onward to creating this. So I'm gonna create it from a brand new sheet. So I'm not gonna do anything design wise until we get to the end. I'll do a couple little things to make some tweaks, but we are starting with a brand new brand new sheet.

So there's nothing I have built yet. So we're gonna, how much, let's call this, how much money can I make? Takes a moment for the sheet to load. And every so often I will take a pause in the video and skip ahead if there's something that I just don't think is going to be very useful to you. Probably me thinking through something, but if there's this amount of time to pause, I'll just talk through it. Alright. We just want to get there. So what are we trying to do? We want how many inputs? 1, 2, 3, 4. Let's kill this 62nd. I'll. Second.

But let's do a 32nd spot CPM rate, fill rate. So we want one, two, we want a checkbox for these three, four things. We want input here. So we want four inputs and four check boxes. Let's do that right here. And then we want our answer. So let's do a, let's just, I don't know, let's put it in this here. Let's do that. So one, I'm using command Y to do this repeat. So we have, oh, I already messed up two. Two inputs, then four check boxes, then two inputs. All right. Let's just do that. All right, we got four of those. Let me move this over so I can get it.

Now we need to insert four check boxes. Let's go insert below a few. Let's do one more. I want to get rid of all of these cuz this is gonna annoy me if we have more rows and things we need. Also, if you have a ton of blank space you end up going into that space. You really want to in for a calculator. You want to make sure everything's within the same view port, right? Same as they do here. Everything that I do is in mass view. We want the answer. Let's put the answer over here. Let's put it in green. We'll figure that out later. Let's label it.

You could make, oh, and it's actually two so you could make, and let's put hang there. Okay, so we have two answers here. We need to get, and we have four inputs, and then we need four check boxes. Let's insert those check boxes. Check. I'm gonna just do command Y to do it again. Four check boxes. What do those represent? 32nd spots. Let's label these things. So 32nd spots, zero. 1, 2 3. So 0, 0 1, 2, 3. Sworn to move that over here so it's easier to see. Let's just combine . I know I'm doing a little bit of design, just be to make it easier on myself to see.

I'm gonna add one more line above here to separate that. Okay. We need to label those things. So how many downloads? Okay. And we'll get to the calculations here in a second. All right. I already see we want to increase that. So how many downloads? We want to put these check boxes over here on the. Get them closer. That's okay there for now. We'll fix out. We'll fix the design in a second. How many episodes do you air per year? We'll put that there. Then the second or the third and fourth CPM rate. Ah, that's correct. CPM rate. You gotta fill in yourself.

Fill rate is a percentage we. Do that a little differently. Okay, so how many downloads? This is just gonna be a number , but it could be like with comma's number . But let's just make the format . This dollar sign . Dollar sign . How many episodes? I don't think anyone's gonna do more than a hundred. I guess the most is like 365, but let's just do. 1001. How many episodes? Let's do 10 just so that we get our calculations correct. This, we can do check boxes. Great. Not a very simple suggestion here.

This, these check boxes are all independent, so we might actually want to change this to a dropdown menu because it is, you can only select one. You should be you. Only be able to select one at a time. In this case, a dropdown menu is going to work for us. So we'll do data validation . Let's just do lists from items. We're gonna do 0, 1, 2, 3, save, and we can get rid of all of these. Yeah, we're just gonna change this a little bit cuz our inputs are a little different than those check boxes. It's gonna be hard.

It's gonna be hard to manage those check boxes if you can select multiple. So let's just do zero. Let's do this. Sometimes you do have to make a little, some, a few like minor adjustments because of our input selections are different. Let's make it in the center and let's give it the same treat. Ands the other one. These blue this is something that I do regularly. Anything blue is a user can input it cuz no one in the right mind is ever gonna use this blue as a design element. It'll stick out no matter what we do. Let's move that over to CPM rate is going to be a dollar.

Yeah, let's make it a dollar. Format is currency and let's do 10 there. But fill rate is going to be 10, but it's gonna be percentage. So now let's fix that. There you go. So now we have per 10% here. Or actually, let's do 100%. Let's kill the last two digits. Okay? Because it's 0.00, but like most of the time we're gonna be like 50%, 80%, 20%, 25. So now we have all of our inputs. We have a thousand downloads. How many episodes do you air per year? Let's just, for sake of doing this, let's put this all on the center.

That might not be a good, that might not be our final thing, but I just want to do that for the eye right now. Okay, now we need to figure out this calculation , right? So calculation is we want to do per year how much money you make per year. And then this one is per episode. Cool. So what is the calculation , right? That's a good question. We have per how much can you make per, so the episode is, this is an easy one to get. The difference. So per episode to per year is gonna be, how many episodes do you air per year?

So this is just gonna be this con. Whatever the number is times. How many episodes do you air per year? So right now it's gonna be zero. And then how much do you make per episode is going to be equals this number of downloads, and then we're gonna divide it by a thousand multiplied by the CPM rate. Let's put that in parentheses. And then it's gonna be how many 32nd spots do we have? So it'll be my end times that. Okay. And then that number will put that in parentheses times this fill rate. And that should get us however many, how much money do we make per episode?

And so let's change this to one. And now it's $10 per episode. Does that match ? And I know already it's not gonna match . And I'll tell you why in a second. One thou, and you probably know why. So 1000, we'll change this to 1000. We'll change this to 10. Let's do 10. We have actually one of these. And zero of these? 100. 100. This is $70 per episode. What is. $10 per episode. Why is that? Ten one thousand. Ten one. 100. 100. Oh. Because our CPM is different. Okay, so that's a hundred.

And why is that different now between this and this 70 Because we haven't taken advertised, advertised cast rate, which is a 70 30 split that is in here. We'll take everything here and times it by 0.7. And now we have the same 700 per year. 700 per year. And we can also. Add a zero here. 7,700. 700. If we add a zero here, 7,700, everything's perfect, right? Not so much because they have these, you want to add these, right? You have a 62nd spot and a 32nd spot. Maybe a one 32nd and a two 62nd spot. We want to add that, right? Let's see what we can do. So let's do.

This and we're gonna add a row underneath. This is gonna get a little complicated, 62nd spots. Let's move our CPM rate because it's CPM rate is per which one? Let's do move that here. I'm using command X because as I use, if I use Command X to cut and. These formulas actually move along with it. So I'm gonna copy and paste that here, not cut and paste. So now we have CPM ray for each one, and we need to add that into our formula . So this formula only takes into consideration a 32nd spot. So we need to actually command C. Actually, we want to command C.

This whole thing except the time seven, we could keep it in there, but let's do plus. And now instead of C seven, we want C eight. So we want C eight, and instead of D eight, D seven, we want the eight. So now we're adding these two things together. We're doing everything the same. Oh, we want to change C 12. We don't want that individually with each one. We want to put that around everything here, and we want to multiply by C 12. There you go. So now we have one one. $100. $100. Let's do 0.5, 50%. Or we can actually move this up as. Save a little space there.

So now this is 10,010. One one at a hundred dollars. Let's change this to $200 and 50% and we now get this and this. Let's see if this matches right. So we have 10,010. We have. One and one. We changed this to 200 and this to 50. Does it match 10,500. 1050. Perfect. So now our math works out and we don't have to change any of this, right? We just need to make it let's do this. View grid lines, if we want to embed it. CPM rate does this. That was pretty quick. And we can embed this. We can use this, we know, oh, fill this in.

Maybe we want to say, we want to add those little elements. How many ad spots do you wanna sell per episode? Oh, we forgot to do that. Let's put that above here. Give it one more. We'll insert a row above that. Cool. You know what? This doesn't look so hot. Let's change it up a little bit. I'm gonna change to design a little bit, but I'm gonna think about it. I'm gonna take a pause and I'm gonna think about it a little bit. Okay.

So I haven't really finished my thought through how to show this, but I'm just looking at what they've done here on advertised cast. And I'm not exactly gonna copy it a hundred percent because they have a couple things going for them on web forms , on HTML forms . You have I don't want to say basic these forms look like that. You should fill 'em out. They are interactive. Check boxes are interactive on Google sheets. You have some other things going for you or against you. Every cell is editable, right? And when we use a Google sheet, we know this.

So how do we let me come in, get rid of that styling. How do we tell someone, insert data here. These are the things you can change. We can write that out here. We can say, Hey, anything blue, you can edit. But that might not be enough. That might not be. So intuitive. And you have to read it, right? You have to know. Okay, I'm gonna read from top to bottom. Oh, I can edit these. We can make it a little more like a form, right? Let's do that. I'm gonna try to make this as close as possible to here.

Where the white cell, they're white cells , they are recessed a little bit. I'm gonna try that and see how that looks. I'm gonna do that with you right now. Let's do that right now. Let's just go here. We're gonna change this to a little bit of a gray. I'm gonna do that as well here. And here. I'm just using command y. Real quick to redo . It's like a fun way to redo exactly this last thing you did. Let's use that as white. I'm gonna command why, again, make that really quick. You can edit these things. Keeping that blue, because that is just something I do all.

And I want people who are using this form to feel that as well, so that if they use other forms like this, blue is you can change it. We're gonna increase this a little. We're gonna, you know what I'm gonna do? Not halfway. Yeah. I'm gonna gray this out a little bit because I noticed this is smaller, but it could also be gray out. Ooh. They had, they have this a little bit different. They have this the other way they. Let's try that. Let's do the same background pace, special format only . I'm gonna do 20 as well, but I'm gonna change that green. Big old green.

Oh, that's terrible. Let's try a different green. Let's try this green. Oh, that's okay. Let's try this playful green here. Okay, that's fine. Let's bold it. And then this one is going to be a slight, oh, that's terrible. Let's do the gray here, right? What is that? This one? It could be a little more there. That's okay. Cool. Let's make this even bigger. I want this like super big, right? You want, Ooh, that's awful. Let's. I don't know what we're doing here. Let's make this smaller then, and we're gonna take away those two little dots at the end. The decimal points.

Very few of these need the decimal point. Oh, it's okay. You can change it any way you want, but I think that looks okay here. We want to make these a little bit more prominent, so let. Do that. Here, let me make this a little bit bigger. There you go. Just to get a little bit of space, vertical space, I'm going to move these all into the middle. That looks nicer. It looks a little cleaner. Gonna increase the width a little bit. A lot. Whoa.

Let's decrease the, I'm going to use this, I'm gonna resize the row and just do half, so let's do 25 there. Oh yeah, I want half there and half there. The size selected rows 25. Perfect. Oh that all fits nicely. Looks good to me. Let's move this as well, or choose this as well to 25. Cool. So this is coming together nicely. These recessed cells look like I should change them. Looks very similar to. And, this is pretty nice and embeddable. I can change this to make the gap a little bit smaller here. I could even get rid of this entire column . Yeah, that fits a little nicer.

I'm gonna put. I'm gonna try this, see what this looks like. I'm gonna put a border, a green border. I don't know if it's gonna look so hot. Oh, that looks fine. Maybe even extend that down. I'm gonna do this instead. I'm gonna make the border around here. I'm gonna kill this border. Do this border again. Make it a little bit. I don't think I like that look too much, but I do like it when it was around here. So let me do that and get rid of this one. There you go. That's a, that's nice. It draws the eye a little bit more. Obviously we can get rid of aerial. Let's try.

Oh, that doesn't look so hot. Yeah, you can try any any font you want. I don't know if that looks okay. Let's, I don't know. Let's see what it looks like if I use green. Oh, that looks nice. Let's just make it all green over there. That's green is money, baby. Blue. Putting the blue get the green. That's cool. This looks pretty good. Let's let's share it. So we share it. We can publish it to the web and embed it into any site or just share the damn link. Change to anyone. Let's change that to, people have to edit it, right? Anyone with a link can edit.

That's pretty much opening yourself up to somebody coming in and editing it. So you absolutely can publish this to the web. You can go here to file, publish to the web, then you can embed it the entire document or just a sheet if you have some calculations that you don't want to show. And they're on a different sheet. You can just embed one sheet and the calculation should appear on a separate sheet so they don't show up. But also do the entire document, gives you some options here. You can get a link, you can get an embed.

One thing I will go over right at this moment, if you want to give people access to those, if you want to give people access to this page, but you don't want them messing with the calculation over here, right? You can protect the sheet by clicking protect sheet. Some other videos that go much deeper into this. Let's say just the inputs, you can say a range . And you can protect every cell or just, let's don't touch my calc. That's what I'm doing here. So I'm saying E, what is it? E. Let's just do E to E. Okay. Set permissions and only you.

So now this is just one way you can do it. I just protected the exact calculation . You could spend some time and just protect every single cell except for these cells . You can do that, or you can even do B and A, a, B and E if you want. Protect all those. That could be a simple solution. But that's really fun because you can then give people access to the entire sheet. You can also, let's do this. This is something if you want to protect your sheets, but you do want them public. I think you can do it here where you say, let's. Let's go editor . Yeah.

Anyone with the link is an editor ? If it's restricted . Oh, here, go to these settings. Okay, so what you need to do is when you share it, if you absolutely give it to everyone, you won't be able to do this, but you can share with people's settings and you can check off that. Editors can change permissions and share , and viewers and commenters can have the option to download, print and copy if you don't want it. Copy. Absolutely. A lot of the sheets that I share here, I say, please copy them. But everything in the sheet is available to be copied.

So if you have a calculation that you don't necessarily want to share , or if you wanna make it less easy for someone to share it, then you can uncheck this. And now people cannot download print or copy unless they're an editor . But the thing is you need to give them edit access to enter the data . So it's a little much, it's pretty much wide open. People will have access to your calculations . So I wouldn't necessarily put anything that's proprietary in.

That is probably the best way you can protect yourself is just don't put anything that is proprietary you just guaranteed people will copy this. But that's how you can make a great calculator that people can then use. You can put your branding on here. You can put business branding, your personal branding, contact details. That's what they did here is, they put a little signup button right here. I would absolutely recommend doing that right there and maybe I will do that too. Made by better sheets. Bye.</div>