Spreadsheets for Authors! (To Write & Have Written)

We’re writers. Spreadsheets are from the other side of the office suite, and they’re scary.

That’s why I brought in a freakin’ spreadsheet wizard to help you tame and embrace them, to simplify and organize your writing career. All sales from all countries on one page, rendered to your home currency and prettified for quick reference? Done. Know at a glance what you spent on production of your last release and how near it is to paying for itself? Done. Know how your mailing list is growing compared to your social media? Done. Keep all your ad copy organized for quick repeats or changes? Done.

And this is a great chance to get set up before we meet with the fabulous Chris Morris, a CPA who specializes with creative entrepreneurs, on Dec 1 to talk about accounting for writers.

Video (from Twitch and YouTube):

To Write and Have Written: A Writer's Guide To The Business Side
To Write and Have Written: A Writer's Guide To The Business Side
Laura VanArendonk Baugh

<p>Writing is only part of a writing career -- no one warned us that we would need business acumen and entrepreneurship to be an author. Whether you're traditionally published or an independent self-publisher, it's good to have a leg up on accounting, marketing, time management, and other key skills.</p><br><p>These recordings of live discussion on craft and development, on business best practices, on explorations of fascinating and inspiring real life cool stuff, and more will help you along your writing journey and career development. Join Laura VanArendonk Baugh as she shares what she's learned and what she's learning. (Or join the weekly live discussion with your own questions!)</p><br /><hr><p style='color:grey; font-size:0.75em;'> See <a style='color:grey;' target='_blank' rel='noopener noreferrer' href='https://acast.com/privacy'>acast.com/privacy</a> for privacy and opt-out information.</p>


LEARN WITH ME Spreadsheets! Discover how they are useful for so much more than accounting (and also – powered by Happy Scribe

Good evening, or if you’re watching this

in the future or in another hemisphere,good morning, something.

All right.

So, hey, it is time for a Learn With Me.

So that’s what we willbe doing this evening.

We’ve reached that theme on our rotation,

but I did want to just say a couplehousekeeping things beforehand.

So last week we talked about rewards

versus reinforcements, how they’re not thesame thing, what we can do to actually

get some practical use out of these.

And I’ve heard back from a number

of people who have been trying thisand having a good time with it.

And I’ve even seen some really cute little

charts that people made that Idid not ask permission to share.

So I’m just going to say that they existand I was really happy to see them.

And one thing that cameup several times was, uh

uh, sorry, Candy.

People, a lot of people are using cookiesor candy or whatever as the reinforcers.

And I definitely talked about foodlast week when we were doing this.

Just because Candy and NaNoWriMo are kindof traditionally a thing. It is not

an accident that NaNoWriMo begins on theday that Halloween candy goes on sale.


as we say in the biz,

non nutritive reinforcers are alsoreinforcers and are certainly available.

So don’t feel like, you know,candy is your only option.

You know, there are lotsof things that you can use.

Feel free to use all the good things.

But I’m going to say it’s 2020.

If you’re going to eat the junk foodanyway, go ahead and make it work for you.

And that is totally fine.

So you do.There you go.


Oh, wow.

Natalie says she showed up in fiveseconds before the alert on the phone.

Poof.That is awesome.

Like very you are probably more

predictable about this than me becauseI’m usually like, oh, it’s seven.

I should find a button.

So good.All right.

So tonight we are doing a learn with me

and we are going to talkabout spreadsheets.

Spreadsheets are the,

as I said earlier today in in a little

bit of promo, spreadsheets comefrom the other side of the office suite.

And writers, we don’t wedon’t like spreadsheets.

They’re complicated, they’re scary.

But they also are really,

really powerful and can do a lotof amazing things for you,

even outside of the obviousspreadsheet application of mathing.

So we’re going to talk about that.

And I have brought in, you see, if I can

get my

window up.All right.

And live, our, tonight’sspreadsheet expert.

We have here our very own Seeker Within,also known as Jon, also known as Mr. Laura.

So I have shamelessly stolen him becausefor years I have borrowed his expertise

to help me arrange and organizeand sort things with spreadsheets.

And he’s a frickin wizard with the things.

So I thought, let’s bring himin and actually have him talk through.

OK, all right.

So the two people I have in the chat rightnow both like spreadsheets,

it might just be you,because since I did say we were doing

spreadsheets tonight, I mighthave run everybody else off.

So if it turns out that it’s just like youguys and Jon and you can all sit around

and have like a spreadsheet Kumbayah,and that’s fine, too.

But I, I did want to go through and atleast introduce for some other people.

Oh, Jon, you’re gettingwaves from the chat so.

Yeah.OK, ok, good.

I didn’t know you had the chat up, awesome.

So, so yeah — our set up here isactually quite hilarious tonight.

So I’m in my little studio area


Jon is on the opposite side of my table

with my giant monitor setup in front of between us.

And so we’re kind of shouting over

the monitors at each otherand this is pretty hilarious.

Oh my gosh, Scott’s here too.

And he also likes spreadsheets.

Look, it’s just going to be a spreadsheet

fan gathering then insteadof a walkthrough so we can do that.

Awesome.So, hey, Scott, thanks for stopping by.

OK,so let’s go ahead and talk about what we

can do with spreadsheets other than,you know, people tend to think of them as

just for accounting, but so much sorting,so much less math in my life.

So I made

all right, hey,I’m going to pop over here.

So I made a super brief little walkthrough of different ways that I’m using

spreadsheets right nowthat Jon has helped me with.

And so you guys can seesome of the options.

And then Jon’s going to walkus through how we get these.

So non math ways that I’m usingspreadsheets right now: when I’m

tracking my students homework, so, OK,

and I’ve blocked out most someof the stuff is sensitive information,

obviously my students arein the scores that they’re getting.

I’m not going to share that.

But some of the stuff is justdata or just covered up.

But I just didn’t want to itwas easy to block everything.

But I can see at a glance, OK,

who’s turned what in,what was the score they got.

All of these kinds of things are easy

and I don’t have to writeanything down or file things.

My author snapshots, we talked about this.Oh gosh.

A month or two ago we were talking aboutmarketing and and I gave you some

marketing homework and I said wewere going to build on that later.

And now is the time.

So being able to just look at the trackvery, very easily,

see at a glance if something is improvingor maybe going the wrong direction.


Jon is going to talk aboutconditional formatting with that.

A fun thing that I do with my spreadsheetsis I track my Christmas gift purchases.

So, again, I can see at a glance, youknow, are all my columns the same length?

Then I have presents for everybody.

I have a really long oneand a really short one.

I need to start some panic buying,OK, that kind of thing.

Same thing for Christmas cards,

which you can also use as a mailmerge to dump and get your

address labels printed.

One thing I started doing last yearand I’m doing again this year is I’m

sending Christmas cards to anyof my readers who would like one.

So I had to sign up in my lastnewsletter that went out for that.

And so everything just goes

into a spreadsheet where it’seasy to manage submission logs.

So I don’t have to remember where I sent

stories and who has already rejected themor when does that exclusivity expire

so I get the rights back, all of that.

It’s just using a spreadsheetlog and then, um,

for handling ads.

So these are my Amazon ads, which I’mnot good at yet, but I’m working on it.

And rather than, you know,trying to keep track,

there’s so much data, so muchdata to organize in doing that.

And so spreadsheets are the way that

it’s still not fun, but I’m not in tears,so that’s the most I can explain.


Oh, yes, somebody spotted a Penny.

She’s now back down, crashedon the carpet, not being cool anymore.

So she may come back and see.

Adam has no emotional reaction.

But Adam, you’re a developer, so,you know, spreadsheets don’t

scare you quite as much asthe average English major.

Probably so.

All right.

All right.Let’s let’s just back to get back here.

I’m going to.

I’m sorry, I’m still workingon, I still need my intern.

All right,

Jon, you should now have the abilityto throw some spreadsheets into the mix.

They should come up.

I would think.

Oh, there we go.

OK, maybe

I need the mouse back.All right.

So this is the this is the problemthat we’re going to have here.

All right.

You accidentally moved my camera,so I’m going to.

There we go.OK, now you can have the mouse back,

OK?All right.

So this first one that we have up here is


this is the author Snapshot, which againtalked about a couple of months ago.

And it’s just finding out where you are.

And I said at that time,just start tracking numbers.

Remember, the data does not judge you.

It’s just a bunch of numbers and then wecan use that to make other decisions.

So here are some numbers.

And you can see I’ve just pulled out

a few simple things,things to play with.


so Laura was trapped in the spreadsheet.

That’s what it feels like at times.

Not even kidding.

There’s a reason I goto a guru for assistance.

But Jon, I’m going to let you actuallytalk now that everybody knows you’re here

and I’ve done this like this iswhy spreadsheets are useful.

I’m going to let youactually get a word in.

Sure.Thanks, Laura.

And obviously,

the spreadsheets we’re going to walk through today.

They are all big data.

I think Laura’s actually eventually goingto put them up on her website so that if

you just see one, you’re like whoever theywant, that one, you can take it.

Yes, there will be a place to grabthem at the end of this episode.

So there you go.

So this one

is somewhat reminiscent of what Laura doesdo with her,

as she calls it, the snapshot where she’stracking at the beginning of every month.

What exactly is going on with a variety

of counts, searching through,see how she’s doing out there?

But one thing that you seewhat this is just raw numbers.



with the wonder of spreadsheets

with the wonder of something calledconditional formatting,

you can find the mouse becauseshe keeps doing it back for me.

You have the ability to manipulatehow these like how these numbers look.

Well, actually doing a whole lot of work.

So one handy, easy way to dosince she’s all about the,

How’s it going?

She could very easily use Directionalsand she can see, hey, look,

you know, my number didn’t, it wentdown in one month and then it went up.

And then overall it’sgoing up, which is good.

You can also play if you really are moreinto the color game,

you can play, you know,the highest number is the green one.

The red is the lowest number.

You’ll notice that these can,in fact, stack.

So if you decide, you know,I really should have done that,

you do have the ability to come in here

and you can change the rules and modifyand say, you know, I want one of these.

I really don’t want the other one.

That was a mistake.

Now, conditionals can get really funky

because you always havethe ability to play

where you would actually do rulesthat are based on formulas.

And that’s when Laura says, you know, Jon,I don’t want to play any more

because once we start gettinginto formulas, so do some.

But most of what she justdoesn’t want to do that.

It’s one of those like, knowyour skills, know that it will take me

longer to do it than it will takeme to ask Jon to do it, I guess.

Yeah.And so yeah.

Something you can do.

I mean you play this kindof game before as well.

So she’s got her nice roundnumbers, here.


But if she wants to know sort of howare they going from a percentage basis,

there’s nothing preventing us from downhere saying, OK, we’ll increase over time.

You know, standard little formula where I

say, hey, look,you know, it’s this no now.

And last month it was this number

and I’d like to know whatit’s so changed, why it went down by 63.

I think if we knew there wasa problem there or down just data.

But I’d like to know as a percentage

basis, OK, all I can dosee is a percentage.

And once I’ve got one,I can do things called fill

over here in editing.You can fill

and I can take that formula and I canfill it down or I can fill it right.

And it will just automatically pleasesort of fix itself as it goes along.

We’ll figure out why.

So I’m going to interruptfor just a second.

For those of us who walked

into spreadsheets and, you know, haven’tbeen using them for a while,

can you show walk through howyou would make that formula?

Because that’s not inherently obvious.

I’m sure we will magicallymake them all go away.

So for email subscriber,which is actually here,

there’s a couple.Ways I can do it.

We’re going to go the simple way first,

so I know that if I want to dopercentages, I want to say,

what’s the you know, there’s a numberat the top of anybody number the bottom.

So the first thing to dois type the equals sign.


to get the top number, which is,you know, how much did it change?

I can click on the field.

I can put them, you know,and then do the sign.

And then I click on Field that’s one approach,the difference.


and then put that inparentheses for crazy.

That’s OK.

But that parentheses and then I can say

and all that divides by andagain I can click on field

and if I do that,you know, it just magically also reversed

itself and it says, OK,you what, two percent.

And then if I want to see decimals, Ican play games here and see big numbers.

But the other thing you can do is alongthe top you see A, B, C, D, E, F, G.

Then on the left you see one, two,three, four, five, six, seven.

So I could also go out here and say,hey, look equals sign.

And I could say this.I want the this is now I want D three

and you can sort of seea little blue box shut up.

And thirty three isminus D or C three again.

And you can actually see howthe little red box jumps around.

As I mistype that thoselittle boxes are showing you.

Where is the number coming from now as you

want to get really crazy you can actuallysee how the little cursor change the multi

arrow can drag that aroundand watch your change.

Yeah, I didn’t want to do that.

And that may not be what I wanted,but that’s what I did.

And that willonce I come back up here and select

the formula, I can see him again and I cansay, whoops, you know, that’s not good.

But it back really be

everything’s cool.


the thing is that doing that over and over

and over and all of these fields,I mean, think about that.

I have to do one of those.

And that just seems.

Boring is not good.

You’re stealing my mouse.

That was Penny who stole your mouse.

I’m so sorry.

That was Penny walked by andcaught herself on the cable.

The other thing is to know that I can

select I can copy a formulaand copy the formula itself.

If I paste it, then the formula isthe same between the two fields.

If I’m like, look, I’m justgoing to copy the field.

And I pasted. Excel because it’s being

smart and it changes the formulafor you to represent the new row.


yeah, if you if something just doesn’tlook right, if you copied the field rather

than copying the contents,that may be your problem.

There is an exception to that.

It’s called Dollar Signs are our friends.

If you put the dollar sign in front

of either the letter or the numberor both, if you wanna go crazy,

that tells itself, hey, look,that should never change.

So then if I play the copy paste game,

that C three doesn’t change, eventhough the B three changed the secret.

And I don’t have those dollar signs

when you play those games, it all changes.

But going back to where I was before last.

But where does it come from?

Again, taking that overand over and over is boring.

I like the area I care about.

I can wherever it is served as my startingbox, the box, it’s currently white.

That’s where it started from.

I can feel when I feel I can feel down,

I can feel right.

And yes, then all my numbers were taken

care of and I didn’t haveto manually do them all myself.

The only comment I will make at this point

is that, look, I started off,which doesn’t actually have a number.

That’s my bad.So cool.

Take the formula,take it, drop it down in here.

I want it

redo and I’m going to use

hotkeys which I don’t expect to now.

And I get the same exact pattern.

And now if I want to if I want to playwith my conditional formatting,

I can say if you look as a percentage,you know, how did things go?

And ultimately it’s no different thanit was before, but it allows me to see

you in percentages rather than in

poll numbers so that, OK, you know,

it went up or down by whatdoesn’t mean a lot.

But hey, look, it went down by two point

four percent, went back up by two,and then it was sort of stay flat.

Can it be a lot more useful to me?Yeah.

And yes, there are dollar signs, now that I’m

looking over,dollar signs are basically lock.

It says do not move this.

This is a lockthat can actually be really helpful.

And we’ll talk aboutthat as we move along.


yeah, when you start getting into somecrazier stuff, the fact that you can lock

some fields and like other fieldsmove becomes really, really helpful.

So this is going to be like a very highlevel overview at, oh,

this is not going to be the in-depthtutorial for most of these things.


what I really like about

what Jon just showed is, yeah,let’s say like one of the one

of the statistics that’s on this, she ismy average open rate for my newsletter.

And so I could say, OK, um, you know,it was it’s been running at thirty five.

It’s dipped down actually.

I’m looking here at this one.

I can see it, you know,it goes down three months in a row.

What is going on there.

Do I need to look and see, you know, did,

did something happen,technologically?

Did I ever did I try a new openingline that’s clearly not working?

You know, I can I can play with, uh,uh, I can see the trends.

So that gives me ways to, uh,

things to look at rather than, uh,everything feels fine, which is

much more based on whether I’veeaten lately than actual data.

So, yes, it gives us things to look into.Exactly.

OK, so, um, and I’m still the mouseback Jon because, uh, I yeah.

So like I said, we’re,we’re sharing a machine

with two screens facing in oppositedirections and it’s kind of hilarious.

Um, so

now you can have the mouse back again.

So I talked about using spreadsheetsfor organizing,

you know, again, for the Christmas gifts,for tracking my students homework,

you know, that kind of thing for mysubmission log, which is hugely useful.

I knew someone who sent the same story

to the same editor twice afterthe editor rejected it the first time.

And the editor wrote back with lovely,polite email, very polite, saying, yeah,

I don’t actually wantthis this time either.

Doesn’t look like any changes are made.

Thanks for sending it again, but no again,

and my friend who had a name which rhymeswith Laura Baugh, felt really embarrassed.

And so having a submission likeit is a very, very good idea.

And it’s just

one of those things that keeps you keepsyou much more task oriented,

much more able to look and seeexactly where you are at a glance.

But then

there’s the giant sheet of doom,as I call it, which is the where we get

into the heavy lifting of doing actuallya lot of math and a lot of tracking.

So I think Jon’s got the thesheet of doom to share with you.


it’s not that it is the sheet of doom.

It’s it’s got a lot of tabsand it’s got a lot of math.

And basically I’m thrilled that it’s doing

the math, so I don’t haveto that sort of thing.

So structurally, what this is and again,

obviously all the data is strippedout of it so that you don’t have to

if you opened the sheet of doom,

I need you to close the other onebecause I can’t see the sheet of doom


They’re better now, now that’s better.


So this is what Laura likes to call

the sheet of doom, it’s justa little bit more complex.

The intent of this document is for anygiven book, which for this purposes they

want a name to, that she has the abilityto capture what expenses, you know.

Did she

have somebody did she paysomebody for a cover?

Did she pay somebody to do an edit

any of those kindsof expenses she might have?

And then to see the royalties

from a variety of sources,KDP find a way, et cetera, et cetera.

So it’s you know, she’s made hasshe made money on this book yet?

Or she’s still losing money.

And she can also to keep track of how many

copies she had she justsold in the various places.

Now we’re sort of in the middle

of changing this over because finda way to get used to be there.

But, yeah, this is an old one.That’s a template.

And we’ll update it and get it cleaned

and make it pretty and allthose kinds of things.

But the other fun thing aboutthis is that, as we all know.

Different authors know different publishing

locations may namethe books differently.

You mean you have an ASINfrom Amazon, you have ISBN,

you have, you know, all kinds of fun,

different toys and different thingsthat you use to track the information.

And ultimately, she wantedall and all over the place.

She wanted to all serve in one room.


the this is where things get fun.

You know, we have an expense to have

expenses is probably the easiestpiece you’re going to have.

Hey, look, give me a name.What was your expense?

Give me the amount Googledates if you got them.

And all of this really is doingand there’s multiple ways to do it.

But the simplest way to explain to Laurawas there’s this cool feature called

the function called some ifand what some it will do

is some effort will, as you can sortof see it in text, give any range.

And in this case, the range isactually on the expenses tab

can find any place for that range,

matches some specific piece of data,which in this case was my title.

You see the blue.

That’s why this littlething is blue, that’s blue.

And then a matching range,which is optional.

But how we’re doing it, find a matchingrange and some of the values.

So right now,this is going to the expenses tab.

There’s those cool dollar signs, where thetwo and the two thousand is

locked so that that won’tmove around on me.

And anywhere that there’s a name one,it’s counting expenses.

So currently it’s currentlyit’s an expense of 500 bucks.

If Laura comes back and says,hey, look, book name one.

Now she’s got editing.

And for some reason,since they were really,

really cheap and they only cost 75 bucks,I probably kidnapped their child.

That may be why.

But, you know, you actually shouldcall the book the same book name.

At that point, my expenseswill go up to five.

Seventy five.No big deal.

Straightforward, very simple.

Royalties are doing much the same thingwhere we’re going to, in this case, KDP.

Now, you’ll notice that there’sno dollar signs on this one.

The reason there’s no dollar signs is

whereas the two thousand said, look,Laura, and you get to which you can do two

thousand rows in here and once you getto two thousand, no more data after that.

It’s just not going to capture the data.

But when we’re talking KDP,although I’ve got a nice little cutesy

minimal thing here, she copies that copyand their pages after pages after pages.

And I need her to keep that data forever

so that I have all the historicalinformation, because big

by not having the dollar signs,it looks at the entire column.

It never cares.

It always just sits the whole column.

Find it anything of that, all that.

Take it all.

And so therefore,this one, it looks at all a column.

Now, this one is going over to my AEfield succeed.

It’s got titles AE in here.

This is the titles tab.So it’s over here.

So it’s actually using the ASIN number.

Now, ABC one, two,three isn’t a real ASIN number

but cool.

ASIN numbers are fun.

It will go on, it will pull the ASIN

Now, the other nasty thing with KDP,I’m going to go with nasty.


You’ll notice that a couple of thesefields in include those are blue because

that’s the hey, look,these are fields I’m actually using.

It’s helpful to remember when you have

a massive file that comes from KDP.Which ones you care, about just the detail.

Now, this one is also grey.

When they when you work with KDP,they give you their currency.

Hey, look, what’s your currency, right?

Well, you know, they get you know,

she sold the book and, you know,Great Britain, UK.

So they sold that one in pounds.

That’s great.

If you add pounds of dollars,

you’re going to turn it into somethingthat you can’t really figure out.

And good luck with that.

Currency rates change over time.

Now, you can be simple and you can say,

hey, look, I only wantone rate for every year.

And that’s all I care about.

I mean, and evil and nasty.

And I said, congratulations,you get to do it once a month.

So what we’ve done here ishere the very last column.

This royalty in USD has a new formula.

We’re going to play Formula Games. P2,

which is the royalty amountwe want to modify it by.

I’m going to interrupt for just a second.

Can you slide your screen offto the right to your right hand column?

Is hard to see on the actual.There we go.

Thank you.

That’s great.


Every column up until so other thanthe day column at the very front

and his last column at the very back,every other column in here was

from an extract Laura for me from KDPyesterday that data has been stripped

and moved and modifiedto protect the innocent.

But at least that wayyou’ve got something,

this new fee thing we’re talking about.

There’s a couple pieces in herewe’re going to talk through.

The first thing, is this EO Month thing.

What the heck is EO Month?

EO month is the end of month.

Now there’s also EO year, etc.

EO month is going to A2 to seethe little box, it’s going to October.

So it doesn’t matter if it’s October,

first, second, third,fourth, fifth, sixth.

This is going to turn it intothe last day of the month of October

Thirty first of 2020 in this case,

as part of my view, look,

that means a vertical look upnow just knowing that way.

Like I said, I’m evil.

When we get to our currency,

Laura has to track what month is itand what currency is it.

And then how did thattranslate into dollars?



so when I’m doing my lookup,

I want to combination of what’s the dateand what’s the currency type to figure out

how are we going to manipulate whatKDP gave us to turn it into dollars.

Not one, not the other.

I need both of these piecesof information for it to be useful.

I need the date and the amount.

Well, cool thing about vertical lookup is

vertical lookup will say, hey, look,given a given a block of data

for purposes, I’m justgoing to highlight these

in yellow.It will look at the very first column

and then you get to tell itwhich column you want to send back.

In this case I want column three.

That’s where my USD equivalent is.

The problem is if it only looks at column

one, that doesn’t give me my your estateor my dollar, that’s a problem.

So I created something called.

Column eight here, which is say, look,

now it’s nice and gray becausethat way it’s just formulaic.

That way it helps to rememberit’s just a formula.

If she puts it in the white ones,it’ll take care of it.

She doesn’t need to put stuffin the gray, it’ll just magically happen.

And what it is doing is it is

concatenating, it is addingthe and sign being add, columns being columns.

Now, you’ll notice when you look at this

thing that’s got thisfour four one three five.

That’s the

numeric representation of October 31st,

2020 based on the Gregorian calendar.People don’t care.

The key is it’s you need four four one

three five is going to be different thanif I had eleven thirty, twenty twenty

I’ve got a number, USD,

Hey, look it magically attaches.

Hey look, it’s still going to be a buck.

Everything’s cool.

The vertical look up that we have.

OK, and this is why when

again Laura is going to drop this spreadsheetout there and as I get the pages for find

a way fixed and all thosethings will drop it out again.

You don’t want to do all the craziness.Cool, great.

Don’t do the craziness just now.

You’ll need to go to copy copy pasteand then fix the currency once a month.

Go out to Google, search on, you know,

November 31st, USA, USA, USA or greatBritish pounds to USD currency exchange.

You can stick it in here.You’re done.

And I’m going to jump in real quickly

and just say,just like with the marketing, this is

this is not a kit that needsto be assembled in a single day.

Right.Like take the parts that are useful to you

right now, add additional partsas they are useful to you later.

And if they’re never useful to you,don’t worry about it.

And as you get more comfortable with using

parts of it, then startusing more parts of it.

And this is by no means an all or nothingproposition because it’s

easy to easy to get overwhelmedand don’t let it bully you.

You are bigger.You are stronger.

You are smarter than the spreadsheet.

As an example, if you decided to workon this, that’s way too much.

I just I can’t handle it.

If she just

used the same values all the time and justsaid, I want it to be one for now

and someday I’ll figure out that currencything, but at least for now, it’s

it’s close enough and one soundsgreat and one’s a great number.

Math’s gonna, the formulas don’t know.Don’t care.

They’re going quite happy with that

because of that.

But what it’s now going to dostructurally is it comes in here.

It takes the end of the monthof my date, the beginning.

It adds to it the currency,K2, that’s why it’s purple.

It uses that says, OK,so do a vertical lookup.

Given that piece of data,

go to the currency translation tab,look in the little bit of a 3D.

Again, there’s no no numbers there.

So it’s looking at the whole columns.

Doesn’t matter how tall it is,it’ll look in column.

It will count overto the fourth column ABCDE.

And that’s the column of datathat is going to give me back.

Now, the false

I always use false whenever I’m usingvertical lookups, you don’t have to,

but vertical lookups, as you can see whenI highlighted they do a range lookup.

If you do not do false,if you do true the first time they find

something to sort of kind of looks closeenough, they’ll give you that number


We’re precise here.

We want things to be exact.

Your true are used.You put false in there.

Don’t leave it true.Don’t let it decide.

Set false.No, you can’t get close.

It’s got to be my thing.

Once it’s your thing, it returns a value.

Will you get there you go.


if you ever find yourself dealing

with formulas like this, you’re like,I have no idea what you just did.

The wonderful thing is there is

in the formulas tab somethingcalled evaluate formula.

You can actually have the thingslowly walk through piece by piece.

So there’s P2

I want to evaluate this, P2.

It goes out, it says, OK, well P2 isthe 235 64 vertical look at end of month.

What the heck.

So what it did was it got the four four

one three five knocking at that date,October 20th in Gregorian, yay rah.

And it added K2,

which is USV.

And you see it’s all underlined when youclick it again,

is going to merge it together into thesingle string that’s got that currency

translation false silliness thing,

you’re going to click on it and allof that turned into my one.

So it sort of walk you through.

What did it do to come up with my numberone.

Type one, there’s your dollar amount.


And that allows you to see if you’ve ever

got a formula you’re trying to goand what the heck is happening?

Or you’ve got a formula from somebody elseand you’d like to see what’s going on.

That’s a way to see how it’s go,what’s going on.

But bottom line, all of that really isdriven towards going back to the front.

Hey, look,

as Laura drops in data for the next bookand I’m just going to cheat here and just

say, hey, look, magically in October

this book sold the exact same

number of copies as itdid in the months prior.

It was November and it was November 15th.


When I come back to the titles,

the money here for royalties has just

moved up and moved upand my copies sold have also moved up.

And so that allows her

to watch over time and see, hey,look, this is what’s going on now.

Something we have done that I think I

ripped out recentlyin the plan of rebuilding was this big,

nasty thing that allowed usto do them on a per year basis.

Welcome to the we’re doing things andmaking things smarter, faster, better.

But at least the starting point of thisLaura will drop out, on her Web site.

And then as we finish up the updateswe’re doing, we’ll drop it again.

So that’s what she calls a spreadsheetof doom for obvious reasons.

There’s a lot of thosesome things in here.

You see them all over the place.

Here’s a fun one where you can actuallysee that it does a sum of and then it

subtracts another sum of because shewanted to keep this being sold copies.

And so she ever gave away a freebie, like,hey, look, it was a part of a promotional.

So I gave away a bunch of copies.Well, that’s nice.

But she wanted to be able to subtract

that and reduce and not count that in hertotal and your paid copies and that we

could have her total copies accountfor paid plus as three copies

know little things like that.

And again,

you can’t you could merge the datatogether, but it’s easier to be that as

separate cabs for the different kindof things,

because that way when they give youdifferent formats

where you have different columns ordifferent, you know,

how many columns you get from differentplaces, find the key columns that matter,

use those, ignore the restand where the data comes from.

If you are on KDP’syou know, you’re KDP reports

tab, or if you go to Draft2Digitalif you’re going to Nook Press or anywhere

that any of those platformswhere you’re selling your books,

they will all have a spreadsheetto download to give you your sales data.

Some of them will give it to you on

the Web page, broken out in their HTML,

but all of them will have a wayto download that in an Excel sheet.

And I just copy paste because I’m lazy.

So there’s no reasonto manually enter this data.

The advantage of having each platformhaving its own tab means that each one can

be set up to receive it in the formatthat that platform exports it in.

And so then it’s literallyjust copy paste.

And you have updated,

you know, twenty seven titles worth ofbook data for the month.

So yeah, it says yes.Yes, it is nice.

This is why I like it.This is why we’re having this conversation

because there’s no way I’m goingto copy all that stuff by hand.

I am lazy as I have mentioned.

So and again this is this is

an overpowered spreadsheet for especiallyif you’re, you know,

getting started in your writing career,that if you don’t have audio books,

if you don’t have overseas sales,then don’t worry about those parts, OK?

Just use what you need to useand add on to it as you need it.

And, you know, just take it,take it by pieces so as it’s useful.

So, OK, there is there is obviously a lotof really interesting data that’s also

available from that can be downloadedas sales versus refunds, et cetera.

But part of that isfor what is what was the goal for this

goal for this was not to figure out whatwas my average list price without tax,

my average offer price it Ididn’t care about that goal was

tell me what the net sales,what’s the net royalties?

That’s what we care about.

But because it’s easier just to dump allthe data into the spreadsheet and then use

the spreadsheets power to pull outthe pieces we care about,

that’s what Laura does. That way shedoesn’t have to figure out which columns

matter in the spreadsheet.Take a whole thing, drop the whole thing,

save it, Move on.

Control C,control V, I’m done.

I move on with my life.Yeah.

OK, um so yeah again this is where you can

find your friend,work slowly through with yourself,

steal from other people,build something that once you’ve got

in place you can use it and help keeptrack of your writing stuff over time.

Or you know,if you want to play the same game with dog

tracking where you’re like, hey, look,it’s it’s Penny and Penny is learning.

One of her sits.And we did this training session and we

did this many attempts and we did thisand we actually got this many successes.

You can track all that you could trackover time and say, hey, look,

you know, I could sit in a room,I could use the time data or I can say,

hey, look, over the course ofthe course of Penny’s life,

we’ve done 30000 set attempts and we’vesucceeded on twenty five thousand of them.

Or you do that, I don’t know.

But if you decided you wanted to,

the tools are availableand you wouldn’t have to know.

You could actually for service dog work.

I would want that data.

Right, like that’s.

And if you were if you were doing search

work,you know, that would actually be something

that might be relevantin court to present that data.

So, yeah,


All right, I’m going to move us on to our

last spreadsheet, because I’vegot 20 minutes left right

the last spreadsheet.

This is where we do the bigmassive disclaimer.

I am not an accountant,

but tune in in two weeksbecause Chris Morris is

an accountant.

But one of the things that you need to do

in many walks of life is you need to trackmoney in and money out

and you can use a spreadsheet to and yes,BridgerDogs, before I move on.

Yes, absolutely.If you want to track how many times

a reactive trigger level of intensityfor your perspective, you can do that.

That could include not just,

you know, the number of timesand the level of intensity, but location,

you know, date.

And it could even include time because,

you know, spreadsheetsdo track time as well.

So, yeah, you could haveall the pieces there.

And so if you find out that it’s always

at 8:00 p.m., well, that mighttell you to start looking around.

What’s going on.Yeah.


spreadsheets or accounting,

what you see in front of you is, again,

sort of the basic one, I believewhat I was going to throw up.

It’s not intended to replace

if you need QuickBooksoh my gosh.

The power of QuickBooks.

It is massive and strong.OK, cool.

But at least as a starting point

and probably for quite a while,you don’t need the power of QuickBooks

in the many, many, many,many things it can do.

And I’ll say that I had QuickBooks

for years and I know now I just usea spreadsheet because it does everything I

needed to do for notthe cost of QuickBooks.

So, yeah, there’s some basic structuresthat are going to be helpful.

This is not a doubleentry cost accounting.

And yeah, if you took your accountingclass and I told you all the wonders

of double entry accounting,this is not that it’s not intended to be.

That’s OK.

But what this allows us to do,

there is a reason we have an expensecolumn separate from an income column

that actually is very helpful for thereasons which we’ll get into it that

day.When did you do it?

Description that’s helpful for you?Categories.

Categories are actually very helpful

that will allow you to check thisstuff up into meaningful pieces.

You’ll notice right nowwhen I click on this new row

there’s a little down arrowwhich will actually fill a box.

That looks like it’s empty.

Oh, look, here’s a list of specificthings I’m allowed to enter.

Unfortunately, your menu does not come

through on the, uh,on the stream for whatever reason.

It’s got your cursor, but not the menu.But let’s go.

I can just tell people that that but that

should be, if I remember correctly,is that blue column.


That everything that’s in blueis in that drop down menu.

And that’s said I was going to go there.

So basically what we’ve done here


inside data.

There’s something called data validation.

And what we’ve done is we’ve said, hey,look, this particular field has to be

if there’s a listthat has to be from this group.

Now, I made it a little extra long downto twenty six, even though I didn’t need

to, so that people could stick a coupleextra in there if they really wanted to.

But what that does is that means that I

have something that I know it’s alwaysgoing to be that list of values.

Um, I could add back, you know,

you can set error alertsand all kinds of craziness.

But for what we were doing here,look, just a forced list.

This is what we need to do was handed

key things that we cared about was, hey,

look, when I sold that book,did I sell it at a discount?

What was my net sales?

Hey, look, you know, I can’t I can clickon the thing and see the percentages.

Laura tends to like sell books.

And I figure this sales tax,I know how to do that for real,

but I didn’t care for thatfor a demo standpoint.


Not to mention that way, you know,

when he was using Square and if she wantsto, she’s not to look at sold for as much.

And this was actually plug in the taxwith the net sales.

I use a formula for net sales.

You could flip and say, well, you know,

I really wanted the sales taxto be the thing that’s calculated.

Different people, different approaches.

What you want. So location is anotherone that’s going to be important.

Sure, you don’t have a locationwhen you buy a book cover.

That’s an expense.

That’s not a sale.

But you will want the salesand we’ll get into why?

Because that will be helpful for theaccounting people when you get to them.

Now, once you’ve captured data,as you go forward and you keep capturing

data and you say, hello,can I get some advertising?

I’m sure you know, today

or not today, but a couple of daysfrom now, I’ll do advertising on Amazon

and I’ll spend another 15 bucks with themand how to take the number of up.

What that allows me to do is as I pull all

that data, because I have certainpieces that I really cared about.

Hey, look, this group thing is

automatically figured outbased on my category.

It’s an expense.It’s these things, I mean.

And here, look at where welook up things back again.

I like it, it’s fun.

What I can then do

is I can then usethe power of pivot tables.

So a couple of things about pivot tables,

thing that, you know, at the topis sort of the grouping’s it.

But it expects that there hasto be a title for everything.

If you’ve got a blank,the pivot table is going to mock you

mercilessly and say nountil you give it a title.

Everything must have a title.

Once you have all your titles,

I can come in here and I can say him,

look, I would like to insert nowI’m an to the table apart for the titles.

You can put them all over the place.

You could put themin the same spreadsheet.

You can put on a new spreadsheet.

It’s always easier to put them under

if you, again, advanced skills would beputting them on the same,

realizing that that can do some strangethings which you’re not paying attention.

So where are you going to do?Simple.

OK, that’s started me.

I don’t understand what I have here.Don’t worry about it.

Over on the right,you see the table feels.

What is it you care about.

Well, you know, I’d liketo know the grouping.

What kind of you know,was it expenses was income.

OK, that’s cool.

You know, I would like to go aheadand know what my expenses are.

That’s what I would liketo some of my expenses.

I’d like some of my income,

but, you know, income and expenses,that’s not the most helpful for me.

I’d like to know when I didthat on a monthly basis.

OK, so now I’m startingto see some structures.

OK, cool.

Or, you know, you reallydon’t care about the date.

But you know, what I reallycare about is the category.

What what was it I was seen.

OK, so in October,I guess I should get rid of all the dates

that, uh, you know,my my expenses for quarter.

My expenses are in advertisingand production.

My income is in sales.


So now I’m starting to builda report that I can use.

Hey, look, I need to I need to give thisto my accountant and he’s going to care about

or I need to use thisfor sales tax reporting purposes.

OK, well then here’s your sales tax.

But sales tax, I probably oughtto know where did that sale happen?

So I guess I should go ahead with this.

Now I know where the sales occur

and I can build out as a pivot table.

It will sort of do the hard work of doingall those sum ofs and all that silliness.

For me,

there are some limitations,those reasons I’m not using pivot tables

in what Laura calls the spreadsheet of doom,but it’s a handy way to sort of build

a report that’s not too complexor that doesn’t look too bad.

And then if you decide, you know,but this look, I don’t like how it looks.

You can go to the designs and you can

start playing with the you know,like it really should look more like

an outline or all those are differentroles or, you know,

now you’ve got to remember now you’vegot tabular so I can see the data.

What should you do?You know, I should go ahead.

I want if there’s a value,I want to repeat it so I can see it.

You know, the subtotal lines,those are the line.

Get rid of those.

Now, I can see my expensesfor advertising as a line.

And I can again,

as you can see with some pretty quick

clicks here in the design section aroundsubtitles, these things I can manipulate.

It was at the bottom

why you would do this, you know,I don’t want it at the top

like it.There you go.

You can manipulate and get yourselfa report that you can then use for

taxes or interacting with your

with your accountant to say, hey, look,

here’s what I here’smy expenses for the year.

And here’s the categories that I had.

Here’s my income for the year.

Here’s where that money came from.So, you know,

I probably have already paid the salestax because they just kept it in Indiana.

I owe them 75 cents.

And you can work through that without.

And this is really useful because like

in 2019 back whenwe still traveled,

I had sales tax duein I think four states.

Right.So being able to break that out quickly

rather than OK, it’s the end of the year,let me crawl through all of my sales

and try to tabulate sales taxfor each individual state.

It’s going to be paid.

So this allows you to do that in a

pivot table structured basis that allowsyou to just sort of those pieces.

And again, this is where pivottables can be pretty nice now

and you can get into graphs and fun,things like that, but.

I’m not going to do that,

make you all crazy right now,so from an Excel standpoint,

you know, sort of the things we walkthrough so far, the very first the very

first step was all about haloconditionals, how you can make colors so

that it’s easy to see thingsdid a little bit of talking about how you

manipulate formulas and how the formulasget put together so that math happens

without you having to do it allthe time or you do it once.

And then once you’ve done it once,you can make the spreadsheet,

copy that value over and over and over,keeping pieces that you want to change

or pieces you don’t want to change,doing those kinds of things.

We moved on to the lastspreadsheet of death,

the spreadsheet of death, where, again,it’s really not that terrifying once you

start cracking it open and looking into itpiece by piece where expenses its own tab

keeps its own tab each otherselling location is its own tab.

And as long as that one is currently set

for as long as we keep the we just pickarbitrarily the end of the month as our.

Currency Translation Day,it works pretty well,

you could do it yearly, but every month iscool and you get some slight variations.

And by doing that,

minimize the amount of actual keeping,minimize the having to think through.

What pieces do I care if thesebig extracts versus such pieces.

I don’t care aboutfrom these big extracts.

Dump it in one place and then get the

end result out back in the back endthat you want which is, cool,

I really like to know how many booksI sold of Title X title my titles.

And then this third one is sort of the OKquick books in those kinds of things.

No reason to do big massive structures.

Simple, keep track of your expenses,keep track of your income.

Do some basic category so you know what

where you’re spending your money orwhere your money is coming from.

And again, if you decide you want to dodifferent groupings, you can do that.

And then hopefully you can driveinto a pivotal for reporting purposes.

Yeah, my it should be on I don’t know,I just tweaked the audio over here.

I hope that makes a difference for people.

We’ll see.

We’ll watch the chat and find out.We’ll find out.

I just noticed I was like,oh, is my headset on?

I hope so.If not, I should be a whole lot louder.

So that runs

high level spreadsheets.

It is possible to divedeep into these things.

It is possible to get really,really crazy formulaic.

It is crazy to get to the pointthat you actually do coding.

There are something called macros.Uh,

there is all kinds of cool visualsfrom a graphical standpoint, bar code,

you know, bar graphs and pie chartsand and all those fun things.

And BridgerDogs says that didmake a difference.

Whatever you did, congratulations.

But at bottom line, at the end of the day,

we’re trying to get this relativelysimple, relatively straightforward.

And so hopefully this sort of has helpedagain, whether it is

accounting,whether it is keeping track of your books,

whether it’s just keeping track ofaccounts going up and down over time

and wanting to see visually making iteasier to understand what’s happening.

Spreadsheets going to be really cool

for animal training.Yes, absolutely.

Tracking, you know, the date and time

of occurrences, the frequency,the rate of rate of success, etc.

You can find yourself with some reallyuseful information from that as well.

Yeah, that I’ll watch to see ifthere’s any specific questions.

But otherwise, Laura,I will go back to you.

OK, I’m going to stealthe mouse cursor back.

This is all about we each have a mouse,but there’s only one cursor.

So we get to fight for it


make it back here.

So I think.Yeah.

So there’s a URL on your screen

that is go dot Laura VAB dot com slashBizTips, capital B I Z capital Tips.

If you go there, there should be a sign up

that you can say you would like to getthese forms when they are ready, this

spreadsheet templates and and then I willmail those out to you

when you when those are polished up andready to go and updated because I’ve been

been updating slowlybut not really in a rush.

But if we’re going to actually share them,we’ll get them polished up.

That’s an excuse to finally get that done.

So then yeah.

Again, just there I’ve got,

I think four or five differentsheets that we’re going to share.

Pick the ones that aremost useful to you and


Oh, thanks for throwing that inthe in the chat as well, Natalie.

And yeah.

So just choose the ones thatthat are useful to you.

Use the features that are useful to you.

If you find that it is too big, too much,don’t use the whole thing just again,

just like the marketing, take it as youneed it, you add onto it as you go.

But I do recommend that if you arelistening to this and you are not

not really comfortable with spreadsheetsyet, go ahead and grab this play

with a little bit becausenext week will be our regular monthly

create-in this this time with a specialNaNoWriMo theme because it’s November.

And then on December 1st,we have Chris Morris,

who is a CPA who specializes in workingwith creative entrepreneurs.

And he’s going to walk us throughthe accounting that we do need.

And if you’ve already got your spreadsheet

ready to go, that will really give youa leg up on on getting started with that.

So, um.

So you thought I had forgotten allabout that marketing homework?

You thought you’d gotten away, but.

No, no, I told you the spreadsheets, OK?Yes.

And yeah, Natalie thinks eithereither of those links is fine.

I was trying to make it short for people

on the on the screen,but whatever is fine.

So both of those links that Natalieposted in the chat are the same link.

So.All right.

And if anybody has questions for Jon,

again, this is not the in-depthwalkthrough tutorial.

Although if people have specific questions

to come back to another time,we could probably do that.

But we, uh.

But if you want to have a question about

something that we did tonight,please, this is your time to ask.

Oh, good.

Adam has been tracking hisnumbers this month, haha.

You just didn’t realizethere was going to be a quiz.

So this is it.

So but good job, gold star to Adam for forstaying on top of his marketing homework.

That’s great.

Oh so and during next week’s write in I will be on and so.

If you have a specific question,

drop it in the chat and Seekerwith that can respond.

There obviously will be charts if you’re

like, well, I got threethousand questions.

Now, if it’s one or two,I’ll be happy to respond.

Yeah.And if it’s something we’re like, OK,

can we get a step by step walkthroughof how to set this up for the first time?

Yeah, we can take time and do that.

Like that’s that’s why we’re here.

It’s why we’re doing stuff so OK.

And oh sorry.

Let me get back to

it’s all right.

And if you find

this useful or entertaining or at leastmakes you feel smarter than me or in any

way good for you,you can support the stream, please.

Like you can.

You can support the streamby subscribing but not spending money.

If you have an Amazon Prime account to useyour Prime Twitch subscription here and

it gets its subscription to support thestream, but you don’t have to pay for it.

So that’s fantastic.

I do have a Ko-Fi, but honestly,

I would tell you like,oh, please help me clean my house.

It’s 2020.

All of my live events got canceledand there are dozens or hundreds of books

stacked to my left andI’m running a Pandemic

Help Me Clean My House sale.

All that information is on my blog.

So signed books for yourholiday gifting, stickers.

So many things,

many of which are on discount and allof which can be customized and signed.

And for your gift recipients

and on my blog, LauraVAB.com,you can find all of that information.

And that would be awesome becausethen I could have my floor back.

Thank you.OK, all right.

Oh, yeah.Thanks for subscribing ShyRedFox.

That’s awesome.I think that’s Amy.

OK, so trying to trying to get people’snames and screen names together.

And I’m only partly trusting myselfso far, but I’m working on it.

All right.

Does anybody have anyquestions for Jon or.

We are going to wrap


We wait the twenty seconds of delayfor that to to come around, so.

Oh it is Amy.Good, good, good, good.

I’m always happy when I don’t rename people so.

OK,and also again for the Learn With Mes

guys always please feel free to send meyour questions or suggestions for topics

for the Learn With Me,because otherwise I’m just going to keep

careening through things that I findinteresting or helpful and that may or may

not be what the rest of the worldwants to see or hear.

So suggestions are always welcome.

And Natalie says no questions,but thank you.

This is helpful.So hooray.

Great.All right.

I’m just going to play with the sheetsso those will go out


Make sure you get that,get on that sign up and then when those

are ready I will email those outto people on that, on that list.

So OK,

I think that’s it.Adam says thank you.

Thank you very much.All right.

And so we’re going to well ifcan’t quit because I’ve got my

screen layered in so many windows I can’t

find how to get wereon the street forever now, so.

All right.

OK, thank you, guys.

Everybody have an awesome night.Take care.

Wash your hands.

Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *