Sorry, guys — had some tech issues this time, but there is info in here still. In this episode we’ll talk about SPREADSHEETS and MIND MAPS, two tools — linear and organic — for getting information out of your head and into an organized format for easy reference.
OK, let’s try this. Hey, everybody, I am so sorry for taking the stream, like we’re going live! Oh, sorry we’re not, because I accidentally clicked an update for the webcam, so I just had to kind of shut down, let that finish. But we’re back. So thanks for being patient. Really appreciate it. Welcome, WorkApprorpriateGoth, which is a fantastic name. I think that’s awesome. OK, so tonight I have with me — let me see if I can get my… Oh no. Nope. I broke all the things. Hold on. I’m going to try to get a…
It shows me a little green light.
A little green light is not helpful. I would like a face. But I have had so many issues trying to get the webcam up for you today. So yes. ShyRedFox oh updates, the blessing and a curse. Yeah. So I did a lot of shifting things to make room for Seeker to sit on the other side of my desk tonight and OBS pretty much said, I’m sorry, did you touch a cable? That cable no longer exists. Yes, so, wow, Jon, you’re just not here tonight.
I don’t exist. That’s fine.
You don’t exist.
I have a black box, so.
Huh, I got nothing. OK, I’ll work on that, but what we are actually going to do tonight is talk about, we’ve talked about spreadsheets before and how they are useful for writers. But a great percentage of writers are — they were frightened by spreadsheets as a child and they’ve never recovered. And it’s really difficult sometimes to convince creative type people that a math tool can be good for them. So, yeah, yeah, we hear Jon’s voice, which is good, because the last time I had Jon on to talk about spreadsheets, I actually screwed up his sound. So now we get his voice. But less Jon. So, yeah, it’s OK, we’re going to, I’m going to mess with this, but we’re actually going to bring up a spreadsheet to to talk about the demos with let’s see what we got going on here. So I want to say first, I just want to show an example of where I use it just for really, really easy list list tracking. Here we go, Jon. Can you see everything?
I see everything just fine.
Awesome. We just can’t see Jon. He’s the spreadsheet guru from the sky.
This is me making hand gestures and it doesn’t matter.
Thanks, it’s really helpful. So, yeah, sorry guys. “Stealth mode: expert,” Bridger says.
So here’s how this is going to work tonight. We’re going to talk through spreadsheets, then we’re going to talk through mind maps. I really, really would like this to be interactive. So we’re going to do to talk about this little demo here for a second and then we’re going to do a work along. And please, please, please throw your questions into the chat and we will make Jon deal with them because he doesn’t have to worry about being on camera so he can deal with all of the questions.
Oh, no, we’re getting an echo on Jon. You know what? I’ll bet you that is Jon on his own headset, but then also still being picked up by my mic.
That’s probably because I’m loud.
Because yeah, probably because he’s loud. So we’ll see if we can tone Jon down. We’ll see if that works or not. So. OK. So. All right. Yeah, sorry, so we are interactive, that’s where we’re going with. So first off, and I talked with Mary, who I don’t know if she made it to that made it to the show tonight, to the chat today. But we were talking this week about using spreadsheets as a submission log just because they are so much faster and easier to track things very quickly.
So and my submission log is probably over overbuilt. Most people don’t need. You know what? I actually had this set up earlier before I had to do that quick reboot, give me a second and make this easier for everyone to see. So as you can see here, this is my I’ve just got the name of the story that where I’m sending it, what date it goes out, what day I got the response. I track personal rejections and form rejection separately, because if you’ve heard me talk about rejections before, personal rejections are a form of win. So go ahead and track those. And, you know, there’s a lot of stuff here that probably not everybody needs to needs to keep track of. But one thing I do is I’ll mark when exclusivity expires. So I know that’s when I can resell that story as a reprint or I can do something with it myself or whatever. So, yeah, spreadsheet submissions managers make everything easier because what you don’t want–
I’m gonna tell you about a story, a story about a friend of mine who sent a story to an editor and the editor wrote back with a nice thank you but no, you know, it’s a nice story. It’s not for me. And then, like two months later, that friend of mine sent that story to an editor who wrote back and said, looks kind of like it did two months ago. Still a nice story. Still not for me. That was like really embarrassing for my friend. So I don’t ever want to be that person just saying so.
Yeah. Oh, good. ShyRedFox has started a spreadsheet for book launch. Oh my gosh. Yeah.
Launch arcs or ARCs and reviews, like just have anything that you need to keep track of and check off like this has been done. I’ve got this many back from this many people, whatever. That’s what spreadsheets are fantastic for. So let’s hop over to a different spreadsheet. And I have built us. A dummy sheet to play with here so we can get into all the nitty gritty. So is this showing properly? Yes, it is. All right, so this is a copy with some dummy data of a spreadsheet that I use to track my advertising.
This is all my Amazon ads, which I am trying to get better at. Taking a course. I’m making progress. Eventually I’ll be good at them. But I you know, I want to know what I’m spending on ads. I want to know what I’m making on those ads so I know whether to put more money into it or to bail out because this is a terrible, terrible campaign or any number of things. And I see people do this with writing things down individually and they’ll they’ll have a calculator open and they’ll have a word doc open and they’ll have notepad open and they’re doing everything back and forth.
And then they have to check their math. You know, they’re doing the same calculation for every title, every month, every ad, and then they have to check their math every time or you know, or they if you make a mistake, they can throw off everything. And then you spend a bunch of money that you weren’t supposed to spend, you know, whatever.
And so so this is where I was like, hey, I know what I need. And I went to Jon and I said, hey, help me build this.
So let’s walk through what this is. Jon. You can feel free to jump in at this point because now this is where I’m going to start.
We can start talking about what we did.
So some of these are numbers that I’m just going to enter in like this particular month.
Oh, can you guys see my can you go see my cursor? If I’m doing stuff on the I’m doing the spreadsheet or. No, no.
But if you look a field it will have a little box around where you are.
Do you have a little box here? I’m in C thirty eight.
Hmm, doesn’t that doesn’t really show up. So tell you what, let’s do this, let’s just make it bold when you click on something. And that way, just Control B. Sorry, it was I was floating on the wrong menu. OK, so, OK, OK, Bridger says yes, WorkAppropriateGoth says that it’s floating in and out. OK. OK. And part of it is that I’m jumping back and forth between Excel and OBS. So poor little computers try to figure out what to do. You know what you’re doing. It doesn’t. All right.
So so this here, title F, that 74 42, that would be the amount that I’m actually pulling out of my Amazon ads dashboard when it tells me what I spent this month. And as you can see on the left hand column, I’m doing this on the first day of every month so I can have a consistent way to track things. The next to that is the royalties. That’s actually what I sold in that month. In this case, this is a February 1st date, so that would be a January sales.
Then we start getting into the things that I don’t want to have to do math for and. So can I see it, you can see it. OK, up at the very, very top, if you look at you know what, I’m going to enlarge this just a little bit for people. If you look up at the top where my unicorn’s stuff is trampling. You can see, oh, no, it’s it’s it’s not enlarging it’s not enlarging it at the formula bar.
But you can see where, you know, we had a number, the 74 42, and then when I click over on average cost per click up at the very top, it has that equal sign, if error, et cetera, et cetera. This is the point where I want to stop doing math. This is going to I’m going to make everything else do the heavy lifting for me. So Jon do you wanna explain what we did there.
Sure. So this percentage concept is pretty straightforward, right? C and K, C being her ad spend and K, which is not on the screen right now, being her total ad clicks per month.
She wants to know, you know, OK, if I spent this much, you know, what am I spending per click? So that’s pretty straightforward.
The problem that we’ve run into is that if you divide by zero, then things get weird. So if she has no clicks and she tries to divide her, that’s been by zero. Bad things happen. But fortunately, spreadsheets, they have this thing called if error.
If error is a function that says, well, if there was a problem, let’s not throw all kinds of weird stuff all over your screen.
Let’s just, we’ll tell you I’ll tell you what I want you to do. And as this case has changed my screen, I mean. But that’s OK. Oh, did it not?
That’s in this case, what was said is just use the default for this kind of field, which, you know, is typically going to either blank or zero.
So in this particular case, we said, hey, look, you know, this this is a number of fields. Just use the default. So what it would end up doing is, it would give us a zero. Now Laura doesn’t have one of those, but if we were to go ahead and down that road that you’re on, go ahead and remove your 249 for just a second.
Over in row K. Just delete it. You can see what’s happened is just blanked it out, isn’t it? We don’t have the data. I got nothing. Put a zero there. Still nothing, but if she puts her 249 back formula no longer goes into that if error conditions. And so we actually get a structure that actually works. So that’s cool, that did what we wanted to do. Now, that was sort of the first thing that we did.
There was just, hey, look, we’ll use this. If anything.
The other thing that you can do with this and I’m going to use this as an example, is let’s say that that formula is just something weird was going on. We weren’t sure what was happening. And I just it’s coming up with a number that doesn’t make any sense. And I’ll try to be quieter to reduce the echo.
Go ahead. I just muted my mic over here.
OK. If you go to the formulas tab where the formulas live, right. Where there’s all those formulas that make no sense. But you go to the formulas tab and Laura’s driving or I guess I’m driving. Cool. Um, in here there’s something called evaluate formula. It’s in the formula auditing area and you can evaluate the formula. And what it will do is it will actually allow you to walk through piece by piece and see the values turn into their number. So you can now see if they’re 740. Seventy four.
Seventy two. And you can see when I hit evaluate that key thirty eight will become the two forty nine which will turn into the math, which is hey look, everything worked. And so there was our answer. And for this one that’s not that big a deal because it’s, you know, it’s two field, it’s one divided by the other. But as we get into bigger and more complex things that in shape or heaven help us if we have formula stacked on formula, stacked on formula, that can become very helpful because it allows you to walk through and see what’s going on.
The other fun thing, although this may be difficult to visually see, is if I actually click up here in the bar, you can see color boxes around the pieces that are involved in my formula. So I can now see a color bar a little this little blue box. I can see this little red box and I can see that they’re on my row. If for some reason I really didn’t want to be using everything on my same row, I could actually come over here and I could say, you know, this is wrong.
I want to click and drag my color box. If I look at it, and as you can see, it actually changes the formula. So that now it will reduce the hours as my box is for doing that calculation, so now it would calculate using this C 33 divided by K thirty eight instead of C, thirty eight, divided by K thirty eight. Now obviously I don’t want to leave it that way because that’s broken. But that’s another way where if I’ve clicked on the formula, I can actually see the boxes that are involved.
Now again, if it’s a real complex, that doesn’t work as well, especially if you reference the same box several times. But it’s a nice visual way. And if you do have a formula that’s just a little off, you can do this. Look at the fields and then fix them if you need to. So those a couple sort of quick things on just using this average cost click to say, hey, look, here’s some things I can look at and manipulate that are actually not that bad.
And I am an appropriate gothe, formulas on formulas. Not my worst nightmare.
I have far worse ones, but that’s another conversation for another day.
I like shutter’s and data analysis. All right. So I’m going to jump in and interrupt for just a second, because you just frightened me. And this is my own spreadsheet. So when when we are looking at.
There are, I guess, let me back up, because there are two there are there are two kinds of people who might be watching this, people who are going, oh yeah, formulas, formulas on formulas, formulas and formulas and formulas.
And then there are people who are who are like, oh, there are numbers in boxes. And so it’s not a terribly specific categorization there, but I think you probably know where I’m going.
What specifically I wanted this spreadsheet to do was, as you can see, this is quite a lot of. This is quite a lot of math going on, and it’s a quite a lot of math that is going on every month. And again, this is my dummy sheet, but I’m my real one’s pretty comparable to this.
And it’s a lot of the same numbers that I’ve just repeated in here.
And so I want something where I can see at a glance how my projects are doing and make decisions without having to do math every time. I want to take the error of math way, way down, it’s still possible I could still enter something completely wrong. But it’s going to be an entry error, not a I did my math wrong error and I want it to automatically propagate for me and do do all that, all those decisions for me. So.
These formulas look terrifying, but they’re really, really not. So let’s take something that is let’s go let’s go way down to introductory level here for a moment. Jon, can you talk us through this formula? I’m in G thirty eight. I see. Yeah.
OK, so at its core, what we wanted to know was she wanted to say, OK, what theoretically I got royalties. But how much did I have to spend in ads to get them? And so what she did or what we did hear was I said, OK, we know that the columns we want are C and D, there’s my royalties. Here’s my ad spend, and so what we did is we said, OK, well then let’s just do our basic algebra game.
I say equal sign. And then then I came over here and I said, OK, and we’re going to just click the field that we want to be our royalty’s. Minus, and then I’m like, look, the field that is my husband and I got my math is just down and it record it for me and I could then I had it once and. I’m just going to have some fun here for a second, once I had it once, I could copy it just just as you always do, you know, come in here and I could copy.
And once I had copied it, I could paste it. And I’m using the pretty, pretty pictures on the top. And the formula, as you can see, if you look in that formula more, you’ll see it was thirty eight and it’s thirty nine and said so as I pasted it, it just spreadsheet says I am smart enough to think and sometimes it’s wrong and I am smart enough to think that whatever you did last time, since this is a copy paste and not a move, that whatever you did, it’s a formula, I will repeat it, but I will adjust it to my record.
And so I could do that and I could copy. And I’m just using the keyboard and paste over and over. Or I could have. Said, hey, look, you know, I really like this formula and I want to take this old and now that’s a plus, I want to just drag that formula down and it would do the same kind of thing. Or for those people who are keyboard monsters and like keyboards or can’t remember that little X, because that’s hard.
I could say, hey, look, I’m just going to select the fields I want. And up here in the home page, there is a fill bar way over on the right in edit or way on the right in editing I could say I want to fill down and that would put that same formula. And so once Laura got the formula right the first time, she didn’t have to keep doing the formula over and over and over and over. She could take the formula she got it right once. She could just fill it down and make it go. OK, sorry, I got to remember that myself now, so I’m trying to reduce echoing you, I don’t know if you guys were able to see as Jon was doing that in column M, things were magically disappearing and appearing as he completed that column G formula. So this is the great thing again, once once you build your once you build your initial formula, you can just connect everything to it.
And that’s that much more math that I don’t have to do anything that results in me. Doing less math is probably great. OK.
And so a default settings for Excel and I believe for Google Sheets as well, is to do this thing where they’re showing negatives and red and positives and black. But you can set that up to however you want.
So we’re going to start a project now.
Group project, group project, please open Excel if you have it, or go to Google Sheets, which I think is just sheets, dot Google dot com. If you do not have it, if you do not have Excel and what we’re going to do is going to work fine in either of those. And we’re going to build one of these four years for you guys and doesn’t have to be for ads.
It can be for absolutely anything that we’re going to track as far as marketing or selling, and we’re going to do a little bit of playing with it live so that if you run into questions, we can address them right now. So you know what we could also do as snapshots sheet? I think we talked about that way back last fall, but I don’t know that we’ve ever worked on one. I just said it was a good idea to have one so we can definitely do some of these.
So I’m going to hop over where I can see the chat. This is this is an awkward kind of thing where it is harder for me to to to see two things at once. So I apologize for that. And it says I’m having some trouble with my bitrate. How’s it going, guys? You see, if I can. See if I can try to fix some things there. Sorry if the stream was getting. I don’t want that to be going on.
It’d be really nice if I could have a Jon visible, but that’s not going to happen. So sorry. Does everyone have a spreadsheet, are you sitting comfortably? OK. And let’s go ahead and. Let’s make a. New. And we’re just going to title, you know, what we’re going to do month title. Sales, royalties. Orcharding. Free. No, no, that just, uh. So I’m trying to. I should have I should have planned my assignments better.
Let’s do this. I would call that good and then we’ll to marketing costs. OK. I can check check every. So a silly question, so we’re going to we’re going to plunge ahead. OK, so Jon what we want here is a very, very basic tracking sheet.
So a simplified version of what the one we’ve done before where we can enter in each of these columns and then end up with an actual picture of how we’re doing. Sure. So after the data, you received the start there. I’ve got something in January, ABC. Now, I’m not sure if your sales line is one or multiple. Yep, that’s a great title. I love the second book to. It’s a standard problem, some serious. All right, um, yeah, go ahead, plug in your numbers.
That’s fine, since this is a demo, we’re just plugging in any numbers. All numbers are good numbers. It’s the hard part, taking and figuring out the numbers you want to type. And so now let’s put in your marketing costs. Sounds great now, my only question I have for you is your sales, are those generi