Using Excel for an Optimization Problem

The astute reader will note that I didn’t (and won’t) write a book called Advanced Excel. Not because there aren’t a number of advanced Excel topics I could’ve written about but more because that becomes the realm of people who really get in the weeds on Excel and I’d also have to find a way to make it comprehensive and that just seemed a little too…much.

But yesterday on Facebook a friend of mine asked for Excel help so I thought I’d share the question and what I came up with here.

Here’s the scenario she gave: It’s career day. You have fifty students. Ten lecturers. There are four sessions, so each student can attend up to four lectures. Students have provided a ranking of their preferences from 1 to 10 with 1 being their top choice. Is there a way to have Excel tell you which students should be paired with which lecturers?

Short answer: Yes. Slightly longer answer: Not in the basic version of Excel. At least not at the scale we’re talking about here. (It can handle up to 200 decision variables but what I gave you above would have 500.)

So if you actually wanted to do this you’d have to get access to a more robust version of Solver. But you can use Excel to build a mini version of the problem.

I used four students and eight lecturers and rigged my data so that I knew there’d be a solution.

Here’s how it goes.

Start with your student data:

Student Data Data Optimization

Here you can see I’ve built a table with one student per row and their ranking of each lecturer across the different columns. (See how I rigged it so that two students want the first four lecturers and the other two want the last four?)

Next, create a “decision grid”. It’s the exact same as the first table except instead of student rankings all the values in the table are set to 1. (Could be set to zero, too. Either way works.)

Decision Grid Data Optimization

You also want to have the total lectures given by each lecturer (the last row) and the total lectures attended by each student (Column V). This is because these will have constraints that need to be met for our final solution.

Finally, create an “outcome grid” which takes the student ranking in the first grid and multiplies it by the value in the second grid. So if you put 1s in the second grid this will look just like the first grid, but it’s using a formula.

Outcome grid data optimization

At the end of the outcome grid you want a total value for each student (Column AE) and then at the bottom of that column of values you want the average of those student scores.

Now, to do this next part you need to enable Solver, which comes installed in Excel but isn’t automatically activated. Go to File->Options->Add-Ins and at the bottom where it says Manage Excel Add-Ins click on Go. This will bring up the Add-Ins dialogue box. Click the box for Solver Add-In and say OK.

Once you do that you will have the Solver option in the Analysis section of the Data tab. Click on it.

This will bring up the Solver Parameters dialogue box.

solver screen

Here’s where all the fun happens. In this case we want to minimize the average student score. So we’re minimizing the value in Cell AE7 which is where we have the average student score after they’ve been assigned to their lecturer. (This is because in this scenario 1 is the best outcome, 10 is the worst, so the lower the score per student the better.) So our Set Objective is Min AE7.

To do this, we need Excel to say yes/no to which lectures a student will attend. That is done by adjusting the 1s we placed in Cells N2:U5 to either be 1’s (for yes, go to that lecture) or 0’s (for no, don’t go to that lecture). That’s what we specify in the “By Changing Variable Cells” box. When Excel solves the problem it will do that for us.

But Excel needs to know our constraints. For example, that students can only attend 4 lectures. That’s why we tell it V2:V5 must equal 4. (Only make four entries per student equal to 1.)

And we need to make sure that Excel doesn’t do something wacky like decide you can attend half a lecture. So we have to say N2:U5 must be binary. (That means an integer that is either 1 or 0.)

And in this scenario, I had to limit the number of students each lecturer gave a lecture to to 2 so there could be a possible solution. (In the real life example this would be a minimum of number of students per session times the number of sessions and a maximum number of students per session times the number of sessions.) That’s the N6:U6>=2 part.

Finally, I wanted each student to at least be marginally happy with the outcome so I made it so that AE2:AE5 were all less than 12. (We don’t want one kid having a terrible outcome while everyone else has a good outcome.)

And that was it. Then you just tell Excel to solve it. Excel will run scenarios until it finds one that meets all of those criteria. (Or it will tell you it doesn’t have a solution which happened a few times to me when I was building this because I’d built it so it wasn’t solvable.)

If you let it implement its solution, you get something like this for that second grid:

Solver solution

In this case, each student attends four lectures, each lecturer lectures to two students, and (you can’t see it) all students have a score of 10.

As you can see, your constraints are crucial on something like this. And you really need to look at the outcome when it’s all done and see if makes sense and to confirm you didn’t miss some crucial constraint or set it wrong. (If that happens, change it, reset the grid, and run the solver again.)

Also, the model type you choose can have an impact. (It tells you when to use each type but I just try each one to see which one works.)

Now, one final caution. Solver will stop on the first feasible solution. I originally had student scores set to a possible value of 20 instead of 12 and it gave me a solution that worked, but wasn’t the ideal solution you see above.

Anyway. This is why I didn’t do much writing yesterday, because, sadly enough, I find this kind of thing fun so I was happy to set aside my current project to puzzle it out. And I thought it might be fun to see for those looking for more advanced uses for Excel.

You Don’t Have to Love It

A lot of times you’ll hear someone say that you have to love what you do to be a writer. Or that if you can imagine doing anything else you should.

And I get where that advice is coming from. Because this is not an easy path to walk. It’s not straight uphill to fame and fortune. (Maybe it is for some, but not for most.)

So you need something that drives you to carry on when things aren’t going well. You need something internal that puts your butt in that chair and your hands on those keys. Something that keeps you writing after that first rejection and that first bad review. Something that pushes you through that moment when you think it’s all going to start working now and then it doesn’t.

But it doesn’t have to be love.

For me it’s sheer pig-headed stubborness because I don’t like to fail. I’m sitting here brooding instead of writing and casually thinking I could always go find some consulting work. And that maybe I should. (Even though I’ve been seeing steady signs of improvement and have already doubled last year’s income.)

That funk won’t last though.

Because in about five minutes my “you only fail if you quit” side will kick in. And it’ll force me to keep going, because I’m going to master this thing, damn it even if I end up living in my car to do it. (Okay, maybe I wouldn’t take it that far. Pretty sure pup would not appreciate the lack of six different beds to choose from.)

So I don’t do it out of love. I do it because it’s a challenge that I think I can master.

I’d even go so far as to say that if you love it, if this really is the only thing you can ever imagine yourself doing or the only thing you’ve ever wanted to be, that that’s the hardest path to take.

Because every setback will hurt that much more. And every critique and every delay won’t just feel like a challenge to be overcome but something personal. Something that strikes at the very core of who you are. It’s easy to become bitter if you love something and can’t have it. Especially if someone else doesn’t love it and does get it.

So you don’t have to love it. And maybe you’re even better off if you don’t.

But if you’re going to commit yourself to the writing path, you have to have something internal that keeps you moving forward. No one else is going to drive you through the years it takes to get there.

It has to be you.

A Quick & Dirty Guide to AMS for Authors

A wise person suggested that maybe I should put together a quick FAQ on AMS to direct people to.

Now, remember, I wrote a whole book about AMS ads (AMS Ads for Authors by M.L. Humphrey) the last time I decided to do something like this, so brevity on this subject is not my strong suit.

But here goes.

What Are AMS?

AMS is shorthand for Amazon Marketing Services. AMS ads are a type of pay-per-click advertisement that authors can use that allow them to advertise their books in Amazon search results, on Amazon product pages, and on Kindles.

There are multiple types of AMS accounts that have different dashboards and advertising options, but most authors just open the type of AMS account they can access using their KDP account.

If you do this, there will be two types of advertising available to you: Product Display and Sponsored Product ads.

What is the Difference Between Product Display and Sponsored Product Ads?

There are a number of differences between the two ad types. They display in different locations, contain different information, and behave differently. Also, how you target the ads differs.

For the purpose of this quick and dirty overview, be sure when you’re talking to someone else about AMS that you understand what type of ad they use. Otherwise you may get “bad” advice.

How Long Does It Take For An Ad to Be Approved?

It should be approved in less than a day.

My Ad Was Rejected. Why?

Usually it’s a cover that Amazon thinks is too sexy or violent or you tried to use ad copy Amazon doesn’t like.

There’s a guide to their ad policies that it’s worth reviewing if you run into this or if you write books with sexy people or guns on the cover. And don’t use ALL CAPS in your ad. Or ellipsis. Or a double dash.

When Should I Expect My AMS Ad to Start Running?

For SP ads, immediately. You should see impressions and clicks the day the ad is approved. For PD ads…Maybe never.

I’m Seeing Sales or Page Reads, But the Dashboard Shows No Sales

That’s because there is usually a two to three day delay in the reporting of sales on the AMS dashboard. So you’ll see clicks and impressions but the sales that were generated from that won’t show for a couple days. This is why you shouldn’t use the dashboard to monitor your ads.

Also, the AMS dashboard shows nothing related to KU borrows.

If I Don’t Use the Dashboard, How Do I Monitor My Ads?

For new ads I only use the AMS dashboard to see what I’ve spent. That I believe is pretty accurate. I then compare that to the KDP dashboard and CreateSpace dashboard to determine if I’m seeing increased sales on that book. For a book in KU I will also watch the book’s rank to see if there are increased borrows.

If I’m seeing a lot of spend but not a lot of increased sales/borrows, I will shut that ad down. New ads can sometimes generate a lot of clicks but lead to no sales/borrows.

For my long-running ads…

On a daily basis, I only pay attention to those ads where I’ve maxed out my budget. So if I have a $5 budget and Amazon tells me I’ve spent it, I go look at the Amazon dashboard and CreateSpace to see if I’ve earned more than $5 on sales of that book that day. If so, I up my bid. If not, that ad is done for the day. (If you’re in KU, you should also look at your book’s rank to see if it reflects enough borrows to justify keeping the ad going.)

In addition, I look at profitability across all of my ads every time Amazon bills me. I spend enough on AMS that they bill me every ten days or so. At that point I go and compare what I spent for the period to what I earned on ebook sales, print sales, and page reads for the period. (There are some minor flaws in that approach, but it’s good enough for me.) If I’m profitable, I keep the ad running. If I’m not, I either adjust bids or shutdown the ad.

(You will be billed at least once a month for your AMS ads no matter what you spend, so you can do this at least once per month.)

You Mentioned Print Sales. How Does That Work?

AMS is very good for driving print sales in addition to ebook sales. You have to have an ebook version available to run this type of AMS ad, but they seem to do very well with print sales as well. As a matter of fact, I have a couple non-fiction titles where I get about 60% print sales to 40% ebook with AMS.

Keywords. How Many? Where Do I Find Them? What Makes One Good?

Different approaches work well with AMS. I am of the limited number of keywords, high bid, sponsored product ad school of thought. Others do well with as many keywords as they can find and low bids. So it’s really up to you.

And there are many, many places to find them. (I cover ten in my book.) Think like a reader. When you go to Amazon and look for a book, how do you search for it? I’ve found that with fiction I do best with generic genre terms and author names. With non-fiction I do best with topic-related search terms and book titles.

There is some debate about what makes a keyword a good one. For me, it’s about sales. So lots of impressions with low clicks? Bad. Lots of clicks with low sales? Bad. I want impressions that lead to clicks that lead to sales. I don’t care about visibility if it isn’t also resulting in paying customers.

(And be careful if you’re in KU because AMS ads can lead to borrows that won’t show on the dashboard, so you can see clicks but low sales on a good keyword.)

Bidding. What Should I Bid?

That’s up to you. Some choose a low-bid strategy. Some choose a high-bid strategy. I tend to be on the high bid side of things but I’ve seen people do well with the low bid side. When I say low bid I’m probably talking under 15 cents. When I say high bid I’m probably talking over 45 cents. (But those are moving targets. AMS is a bid system where you are bidding against others for that ad slot. Some genres, like romance, are more competitive than others.)

I Bid Really High. Why Didn’t It Work?

Because AMS is not a pure bid-based system. There is a relevance factor involved in how AMS evaluates your bid against others’. None of us know exactly how it works, but it’s in your best interests to have as successful an ad as you can manage if you want to win your auctions. That means you want people buying your book if you want to continue winning your auctions.

Start a New Ad or Revive An Old One?

Once more, there are different opinions on this. New ads tend to run hot so will rack up more impressions faster. If they’re working that’s great, but they can be a quick way to lose a lot of money. I prefer to keep an older successful ad running because I think that’s part of what Amazon looks at when it judges two bids against one another. But you can’t do that without maintaining that ad. (At least on the SP side of things.)

To maintain an ad, I kill off non-performing keywords, change bids, change budgets, and sometimes will pause an ad for a bit and then start it up again.

AMS Are a Nightmare. It’s All So Confusing. Why Use Them? Aren’t They Just Scamming Us Out of More Money?

I love AMS because they give me direct access to the largest ebook and print book market in the world. And they give me the opportunity to advertise my books at full price on a daily basis.

They also work for me. I make a profit running them. I have one ad right now where I spend about $125 every ten days and I make about $400 in sales.

Are all of my ads that successful? No.

Is it frustrating sometimes to have an ad stop running and not know why? Yes.

Can one competitor entering the market and outbidding me change all that tomorrow? You betcha.

But before AMS existed, that book I just mentioned? It would’ve never sold as many copies as it has.

Since I started running AMS I have seen significant improvement in sales of all of my books. That doesn’t mean I’ve sold millions of copies. I don’t write to market. And some of what I write probably only has a potential audience of a hundred people. But AMS have been a lifesaver for me.

They are what they are. Yes, sales cost more if you run them than if you had organic sales. But…Most of us are not at the stage where we get organic sales. So the choice is: pay to advertise your books or don’t sell at all. I know which I prefer.

And, of course, I did write a book on this: Easy AMS Ads that’s only $4.99 in ebook or $10 in paperback.

It’s also now a video course, available here  and for a special introductory price until June 30th.

And…Done.

Excel for Beginners and Intermediate Excel are now available as video courses! Phew. That was a lot of work, but I’m very pleased with the final product. (And now back to writing new books for a while. Lawnmower and construction season is not the best time to be creating video courses.)

For anyone who wants to check them out, you can do so for just $9.99 each (instead of $99.99) until June 15th using the following links:

EXCEL FOR BEGINNERS PRESENTATION VERSION

INTERMEDIATE EXCEL PRESENTATION VERSION

So Close…

This morning I finished the final review of the videos for Intermediate Excel. So all I have to do at this point is load 120+ videos to Udemy (for both Excel for Beginners and Intermediate Excel), convert that many closed caption files and upload those as well, and then I’m done with these two video courses and can move on to writing the next books.

(I have five non-fiction titles on my board, two of which I desperately need to get out there. Not to mention the novels I’d planned on writing this year…Haha.)

So lots to do.

And yet…

I am sitting here futzing with my AMS ads and checking FB too many times and generally showing a complete lack of interest in doing that last little bit to wrap this up.

These are the hard moments. You’ve done the fun part. You created something. But then comes the polishing and finalizing and, god help me, the administrative parts.

It’s like reading a bad book that isn’t so bad you can just quit it. You know you need to finish it to move on to the next one that you’ll read start to finish in a day, but you just can’t bring yourself to do it.

It’s easy to bog down at this stage. To take a week instead of a day to do all the boring bits. But that wasted time adds up. Big time.

So enough. Time to shake it off and get back in there and get these classes published so I can follow some very good advertising advice from folks smarter than I am at advertising and then get on to the next two projects.

Pbbt.

The Dirty Little Secret of Self-Publishing

I’m sure there’s actually more than one, but the one I’m thinking about today is this:

How many copies you sell is meaningless.

It’s what so many people talk about and you see it used in advertising all the time, but at the end of the day no author is going to be able to do this full-time, even if they’re selling millions of copies, unless they’re actually making a profit on those sales.

Self-publishing is horribly myopic in this respect. Rarely do I see someone report “I made $X profit.” Instead it’s “I sold X copies” or “I’ve sold $X worth of books.”

And I get it. The gross numbers certainly look a lot better for everyone than the net number. It’s far more exciting to say “I sold a million copies” than “I sold a million copies but it cost me so much that I’m now in the hole $10,000…”

And in this business you gotta celebrate every little victory no matter what. (And perception matters, too. People want to read what other people read. They want to associate themselves with success.)

Anyway.

What prompted this thought is that I realized yesterday that my first-in-series fantasy novel sold it’s 2500th copy sometime in April. Which is a big milestone for me. I had no idea I’d sold that many copies of that title until I stopped and looked at my reports.

Woohoo! Right?

But.

Here’s the interesting thing about that title and that series: it’s my least profitable series. I actually consider it a failure.

It’s only one of three “series” (out of 26) I have that are in the red. And the only one that’s more than $50 in the red. (It’s the cost of those damned covers that I love so much…)

Interestingly, my most profitable series has sold only half as many copies but grossed more because it’s never been on sale and been significantly more profitable because it’s easier to advertise.

It’ll never get a Bookbub. (I can’t even apply for one because it’s under their page count threshold.) I don’t get fan mail for it . I barely get reviews on it.

And yet…

That’s where the money is. Not in the one that’s sold a lot of copies and had three Bookbubs. But in the little workhorse title that just chugs along day after day racking up sales rain or shine.

So if you want to do this full-time. If what matters to you is being able to work for yourself and from home, don’t focus on how many copies you’ve sold. Focus on profitability. Focus on making more in sales than you spend to get those sales. And on leveraging every sale the best way you can. (By writing in series, for example.)

Another Five-Figure Year And Yet…

2017 was my first five-figure year self-publishing. It was a huge milestone for me seeing as I’d only had my first $1,000 month that June. And I didn’t cross that mark until the end of October last year.

So to reach that same mark three and a half months into the new year is awesome. And even better, I’ve made more in profit this year than I did all of last year. (It’s nice to write a book people are actually looking for and want…)

I should be ecstatic. And I am. In rare moments.

But I’m not satisfied with it. It’s not enough.

There’s this part of me that fears it will never be enough. Me being me there will always be something that keeps me from just settling in and resting on my laurels, so I’ll always be striving to be better in some respect. And will occasionally throw everything out and start over (like I did when I started writing) just to have that challenge.

With the writing I tell myself I just want to get it to the point where I’m earning enough to pay all my bills, do a few little fun projects or buy a few luxury items, and put some aside enough for the down times.

(Not much to ask for is it? Except for when you actually ask what that number is and then laugh outrageously at what I think it takes to have all that.)

But I wonder if that’s true. Because if I reach that level I want to reach, I won’t be at the top. There will definitely be self-published authors who are doing orders of magnitude better than me. (I could probably reach that level with titles that never crack a ranking of 10,000 on Amazon US.)

I like being self-employed (even the consulting work) more than being an employee because I don’t have to go through all the “but why did Bob get a promotion, too” or “why does Suzie earn that when I earn this” drama. I can set my rate, work my hours, and get paid. Or I can put a book out there at my chosen list price and people will either buy it or they won’t.

But being self-published doesn’t eliminate that ability to compare yourself to others. It’s one of the most bizarrely transparent industries I’ve ever seen when it comes to income. People talk all the time about what they’ve earned. Publicly. (Myself included it seems since I’m doing so right now.) And then there are things like Data Guy’s Author Earnings reports that put it out there even more. (I love those reports, though.)

So there’s no way to live in a vacuum and just write and publish and hit your goal and not know what others are doing. I mean, I guess there is. I could just avoid all author forums, but then I’d miss out on all the industry intelligence that I’ve found so incredibly valuable.

Sigh. I don’t know. I like this industry because it’s so uncertain. And at the same time I hate this industry because it’s so uncertain.

But we have to celebrate our little victories when they occur.

So for just one little moment–I’ll give it ten seconds–I’m going to bask in this accomplishment. 10, 9, 8…

Alright. Time’s up.

Back to the grind.