Podcast: Play in new window
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):
<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>
Transcript:
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.
However,
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.
So.
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
and
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.
And
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.
So.
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
for
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.
And
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.
Great.
But
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.
Yeah.
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.
Now,
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.
Look,
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.
Now,
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.
So,
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.
But,
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.
But
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.
And
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
yet.
They’re better now, now that’s better.
Yes.
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.
So
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.
Sure.
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.
Yes.
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?
OK,
and
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
use.True.
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.
Now,
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.
Congratulations.
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.
Sure.
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,
go.
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.
Anyway,
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.
Yeah.
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
is
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.
Excellent.
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.
Cool.
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
and
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
then.
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
now.
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
again.
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.
