A Little Reminder on Backwards Compatibility

Microsoft is at it again, releasing new versions of its Office suite of products. So new users will have Word 2019 and Excel 2019, for example.

And on the Excel side there are a few exciting (to me, because I’m a nerd) changes they’re making. A lot of it around IF functions. Here’s the link to what’s new in Excel 2019 and the link to what’s new in Word 2019 for the curious.

The key issue here, though, is backwards compatibility. Just because you may have the latest and greatest does not mean anyone else does. And using one of those new functions if you’re going to be sharing your files with someone who isn’t upgraded to the latest version is going to mean they can’t use what you send them.

So by all means, upgrade and try the new functions in Excel. But if you intend to share that document with clients or counterparties, be sure that they can use the file you send them.

Let me give you a personal, very painful example of how I learned this lesson the hard way.

Back during the big mortgage crisis I had a consulting client who had a large residential and commercial loan portfolio. And I ended up in a role where I was helping someone who was an expert in calculating a bank’s allowance for loan losses automate that process using Excel. The goal was to create an Excel workbook we could then hand off to the client so they could make that calculation going forward whenever they needed to. When we were done, all they’d have to do is put their current data into one or two worksheets and everything else would be calculated for them.

It took about a week for me to put the workbook together because there were a lot of moving parts, but I finally had it ready to go and handed it over to them to test.

(Now, I should add here that for a long-term solution Excel was not an ideal choice. But for something they could have up and running in two weeks? It was probably the only choice. And this was at a point in time where bidding things out and taking six months to build a technology solution were not options.)

So, anyway, I handed it off.

The client came back and said they couldn’t use it. Because it relied, in part, on using the SUMIFS function, which was available in my version of Excel but not their version. And getting a large corporate client to upgrade their version of Office is not a simple process, especially during the midst of a financial meltdown where it was very possible that company wasn’t going to exist in six months.

So I had to spend a couple days rewriting that whole workbook to remove the use of every SUMIFS and replace it with multiple IF functions in multiple columns that could accomplish the same result.

It was not fun.

Thankfully, I worked for the type of boss who didn’t blame me or yell at me for my mistake, just told me to fix the issue. If I’d been working for a different client or a different boss that whole situation could’ve been much much uglier than it was. As is, it was bad enough.

So remember: keep in mind who you’re working with when you create an Excel workbook (or even a Word document or PowerPoint presentation) and make sure that they’ll be able to use what you give them when it’s done.

(I should add here that all of my current Office guides are written using the 2013 versions of the products–so Excel 2013, Word 2013, and PowerPoint 2013–and with most of it compatible back to the 2007 versions, partially for these reasons.)

Consulting Services…

So I just added a page to the website that covers consulting services. I’ve debated about doing something like this for over a year now, but there were some reasons I hesitated.

First was that the people who’d approached me about this generally were looking for someone to manage their AMS ads and I just don’t think that’s feasible for most authors. I love AMS and I would have very few sales without them, but they just are not that predictable. And, honestly, not all books sell well with AMS. So I could never see how to charge for that and have it be fair to both the author and myself.

(What I can do though is help find keywords for a new ad or give feedback on an existing sponsored product ad, for example.)

Second was the pricing issue. I knew that what I charge for regulatory consulting and so am used to receiving for “consulting” is far more than most people would be prepared to pay. And even though I’m willing to accept very low hourly income while I get my writing business launched I wasn’t sure if I could do the same for consulting. So I compromised. You’ll see that the rate I’m charging is not low. ($100/hour) But it’s also not even close to what the financial institutions I’ve consulted for have paid. I’ve also carved out regulatory and compliance consulting from that rate because of the legal implications involved with that kind of work. I’m not willing to do that kind of work without a team that includes at least one lawyer who reviews everything I do even if I do the bulk of the work.

Third was the “who do I want to be” issue. I want to be a writer. I want most of my time to be spent on creating new material whether that’s a non-fiction book, a novel, or a video course. I don’t want to become one of those people who gets sucked into doing classes and teaching others and stops doing the creative work themselves. So I’m going to be limiting the amount of this work I do.

There were a lot of reasons I hesitated to do this, but at the same time…

I see so many people who could use just a little bit of help to get unstuck. One thirty minute conversation could save them hours of research or keep them from going down the wrong path.

Or maybe they’re like me when I first started and they just want someone to take a quick look at their writing and say, “Is this good? What mistakes am I making?” I don’t want to be an editor, but I’d be happy to spend 25 minutes reading something someone has written and then giving them my honest (perhaps brutal) feedback. When I was getting started I spent $1,000 to get an edit on my first novel to get that kind of feedback and I honestly think it was way too much money spent for what I got. But there weren’t a lot of good alternatives. And peer critique is only as good as your peers.

(Now, you could argue I’m no better for that than anyone else. And that’s fine. Don’t use my services if you don’t think I can provide value.)

So we’ll see where this goes. It’s possible no one will want my help and that’s okay. But I look at, for example, Excel for Budgeting or Excel for Self-Publishers, and I think that there are people who could benefit from what’s covered in those books but who just don’t have it in them to wrestle with Excel that much. This is my stop-gap attempt to fill that void. (Without getting so busy consulting that I have no time left for the writing.)

If you want to see more about the nitty gritty details, click here. And if you think I can help, reach out.

Also, don’t think this means I won’t answer questions via email anymore. I most definitely will. This is for when we get beyond “How do I X?” to “Can you walk me through how to do X?” or “Can you do X for me?”

Excel for Budgeting is Live

File this one under why I should never read my emails. Haha. Just kidding, this was one I’d been thinking of writing for a while but just hadn’t written yet. So that email someone sent a couple months ago asking how to apply Excel to budgeting was just a good reminder that I wanted to get this done.

Over the last ten years of self-employment I’ve developed an Excel workbook I use to juggle my finances. Because one of the biggest challenges of self-employment is cash flow. I can make $25K in one month and then nothing for three. So I have to always be monitoring where I am in terms of cash to pay my bills. And I have to know where I can look to get cash to pay my bills if there isn’t a big paycheck coming and there isn’t enough in the bank account.

So over the years I’ve developed a focus on my short-term liquid net worth and also on haircutting my assets. (Much like a broker-dealer is required to haircut their securities portfolio when they value their holdings.) Because a 401(k) is great and all, but it doesn’t do you any good if you can’t pay your bills today.

In Budgeting for Beginners I talked about all of this and how to judge where you are financially and take steps to improve upon that. But what I didn’t include there was the Excel workbook I use to track all of this. And it’s not exactly intuitive how you’d create something like this.

I now have one nice little worksheet I can print off that shows my next three months of cash flows, my assets and liabilities, my available credit, and my short-term and long-term net worth. But when I look at my trackers from ten years ago they are nothing like what I now have.

(I think I missed my calling as some sort of data nerd. But, oh well.)

So anyway, that’s what Excel for Budgeting is. It walks through how to create this workbook for tracking your finances. It’s what I use and (knock wood) I’ve somehow managed to stumble along for close to a decade now with uncertain income and making extreme life choices. I do think you should know Excel before you try to use it, but there are step-by-step instructions in there for how to create everything if you want to give it a try anyway.

(And, as a weird added bonus, if you’re like I once was and split time between two countries so have to deal with payments in multiple currencies, I covered that, too. Because, why not?)

Now maybe I’ll be turning to writing some fiction. Maybe. As long as I don’t walk my dog or check my emails before I start the next project.

Free Video Course: Pivot Tables

To celebrate the release of my latest series of books, the Easy Excel Essentials series, I have a free video offer for anyone who wants to learn how to use Pivot Tables in Excel.

Here’s the link: https://www.udemy.com/pivot-tables/?couponCode=EEE_PT_50FREE

If you are a self-published author, have Excel, and don’t know how to use pivot tables yet, please use the link and learn how. Because I honestly do not know how authors monitor their sales on Amazon if they don’t know how to use pivot tables.

True story: My annoyance at the fact that authors do not know how to use pivot tables (even though they really are as easy as dragging and dropping fields in the right places), led me to write four books.

I started writing an Excel for writers book that would include how and why I use pivot tables as an author. That book ended up being split into two books: Excel for Writers and Excel for Self-Publishers. But then I realized that there would be some folks who didn’t know how to use Excel at all and some who knew some Excel but not enough. So I wrote Excel for Beginners and Intermediate Excel.

Four books written. All because people annoyed me with their overreliance on BookReport and their complaints about not being able to see their sales using the Amazon reports.

(Yes, I am crazy…)

And, as a side point, here’s a picture of the new books:

New Release Image EEE

The astute observer will realize that these books actually cover material that I’ve already covered in my other Excel titles. But I figured there was a market for people who just wanted to learn one specific topic. (And do so for just $2.99 in ebook, nonetheless.)

This was one way I thought of to “extend” my Excel books when I felt that I’d pretty much already said what I felt needed to be said about Excel. I have another idea for extending the Excel titles, but pretty sure that’s going to have to wait a while. I think I’m just about to pivot back to writing fiction for a bit.

See what I did there? Pivot.  Haha.

(That wasn’t intentional…)

Anyway. Time to upload links for all six of those books for all five sales platforms I have linked from this site. Happy happy, joy joy.

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.

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

Black Friday Sale

The “holiday” season has started. I made the mistake of stepping on a scale this morning and learning that I gained 2.5 pounds yesterday. Suffice it to say, I enjoyed Thanksgiving here in America. (Although I actually had a steak and chocolate torte instead of ye old standby of turkey and pumpkin pie. That’s because I made myself a ten-pound turkey on Friday along with a pumpkin pie, so I’d sort of had my fill of the traditional goodies by now.)

Anyway.

Getting to the point.

Excel for Beginners is part of Kobo’s Black Friday sale this weekend, so you can get it for 99 cents instead of $4.99. If that doesn’t interest you, there are a ton of other books that are part of the sale: