The Pivot Table Gateway Drug—Don’t Let This Be You

That pivot table was so seductive. It made it easy to get your data out when all IT would give you was a huge data dump. The boss was happy. And the numbers worked fine. So, why worry?

But then next month, someone asked you to combine that data with a dump from another system. What harm could there be? Just a simple VLOOKUP? You didn’t even have to copy and paste data between spreadsheets—just specify the file you wanted in the formula and everything was fine.

But then, the pivot table wasn’t enough. Someone wanted special formatting. And you thought, it’s really time to get this into the system. But the report writers were backed up. And IT had other priorities. But with just a few more formulas and GETPIVOTDATA, and you could be the hero. So, you worked through the weekend, and the numbers were fine.

But then, you went over the edge. Someone asked for adjustments that you couldn’t get into the current system. You weren’t just combining data from someplace else. They wanted you to track data that existed only in your “system.” IT said it would take six months to get it done. And they really wanted those numbers now. So, you thought about it. You had Access on your desktop as well as Excel. Maybe, you could hook your spreadsheets up with Access and get everything they wanted. And it worked. You were riding high. You could do no wrong. Sure, you had a few jitters now and then, but you always managed to get it done.

The high didn’t last. One month some numbers didn’t make sense. You spent hours combing through spreadsheets to find the bad data. The next month, you missed a reporting deadline. And then, (cue Darth Vader music) internal audit showed up. It wasn’t pretty. And now they’re making you use Google Docs.

Don’t let this be you. Use pivot tables as they were intended—for quick, ad hoc analysis. They shouldn’t form the linchpin of your financial reporting process. Invest in system reports. And remember: Friends don’t let friends rely on Excel.

Seriously though, I love Excel when it’s used properly. (See previous posts on my top 5 reasons for using Excel for reporting and top four reasons for not using it. Also see my post on going over to the dark side in recommending Microsoft SSRS.)

But as happens regularly, last month I spent considerable time working on client spreadsheets. While I can quickly answer almost any question about any report we’ve developed over the years, I can spend hours piecing together other people’s spreadsheets—and usually under tremendous pressure.

I get that people do what they need to do to get the job done, which drives the creation of these spreadsheets. But these monstrosities don’t solve the problem. They just kick it down the road. As with so much in business and in life, if you don’t pay now, you’ll have to pay later. And when you pay later, it’s going to cost you more.

 

Goldilocks and the Three Project-Managing Bears ? Part 2

I received great feedback on the Goldilocks post. Thanks. Thinking about it more, I realize I was describing the ?what? of good project management ? not too hard, not too soft. The bigger question, though, is how do you get there? For every Baby Bear out there, there are plenty of Papa Bears and Mama Bears.

If Baby Bear?s style seems right to you (as it does to Red Three), here are our four keys to a successful project:

1) We acknowledge that we don’t know exactly what the end result will be.
Formally, people refer to this as Agile project management, and it’s fundamental to every software or reporting project we do. It demonstrates how software is different from other kinds of projects. When architects design buildings, they want exact specifications: size, shape, color, and so on.

In software, the end result isn’t so clear. We have a basic idea of what’s supposed to happen, but often we don’t really get the full picture until we’re into the project.

2) At the same time, we believe it’s better to do some thinking than to blindly have at it.
Or as I like to say, “Put your hands in the air and step away from the keyboard.” Many software developers love writing code and dislike almost anything else. So, once they have a basic request, they?re off to the races. This doesn’t work well. While you may not know exactly what you?re going to do, if you don’t spend as much time thinking about the problem as you do writing the code, you?re not going to get a good result.

3) We have a culture of trust, where we emphasize responsibility, not liability.
Not having everything laid out in advance (the Mama Bear position) increases the likelihood of making mistakes and having higher costs than your original budget estimate. In some companies, that’s ok. In other companies, they believe, ?if you don’t nail it down, you?re going to get nailed on it.? This doesn’t work. If you want to live in the Baby Bear universe you have to accept that estimates are just estimates. Sure, you can’t be regularly off in cost or time by orders of magnitude, but 10% here or there shouldn’t be a huge issue. More importantly, you have to use mistakes as an opportunity to learn ? not a way to score political points.

4) Our projects are a joint effort of IT and business.
Goldilocks didn’t just throw down her requirements and disappear for a month. She checked in. She answered questions. She tested. IT projects die because people stop paying attention or because people who’ll use the software don’t have time to work on the project.

Are there any more keys to successful projects? What would you add?

 

Data Indicates I’m not as Funny as I Think

One of my clients asked where my funny posts come from. I’m flattered people like the writing. I try for amusing and entertaining, and I like to think I’m just naturally funny. But I looked at my data; and I realized it’s really more a matter of effort than talent. Let’s do the numbers. (And in the spirit of ditching your decimals, we’re rounding all numbers here.)

Let’s start with how much writing I do. I post three times a week and my average post is 350 words. So, that’s about 4500 words a month that actually make it on the blog. For each month?s worth of posts, there are probably another five ideas I scope out but that don’t get posted. So, I’m up to 6000 words a month. Of the 14 posts or so that make the final cut, about a third I get right on the first shot. Another third, I redraft about half the article. The final third I wind up rewriting almost completely ? keeping the idea but completely restructuring it. And this is all before the editor gets it. So, let’s say I’m writing 8000-8500 words a month for the blog. If you add in other writing I do for white papers and such, I’m writing about 9000 words a month on newsletter-type topics.

So, how many of them are really funny? Funny enough where people will come back to me months later and joke about it? I?d estimate that while we get a smile pretty often and, hopefully educate regularly, the really funny posts (judging by click-through rates) happen once every three months. So let’s say that’s 350 really funny words out of 27000 written — a little over 1%.

Which means, to my chagrin, I’m not a naturally funny writer. I just try a lot of stuff and sometimes it works. Which brings me back to my thoughts about focus. I can do good work without too much strain. But really remarkable work takes a lot of background effort. So, before I take on a new area, I have to ask myself: can I put enough work into it to get really remarkable results?

Goldilocks and the Three Project-Managing Bears

Goldilocks was Director of Reporting for Acme Widgets. She needed to get a dashboard built to analyze their sales of anvils to Coyotes, who chase Roadrunners (naturally). So she went to the Project-Managing Bears she knew, each of whom had their own consulting firm.

First Goldilocks went to Mama Bear. Mama Bear said, “Sure, no problem! Let me work on the plan.” And four weeks later Mama Bear came back with a beautiful 1,000-line Gantt chart and an 18-month estimate. Goldilocks said “That’s all very nice, Mama Bear, but this market is moving quickly. We can’t wait 18 months to understand our data.”

Next Goldilocks went to Papa Bear, who immediately started coding. “Come back next Monday,” said Papa Bear. “I should be done by then.” Goldilocks felt excited, but nervous. Could Papa Bear really deliver something that quickly? And when Goldilocks came back the next Monday, sure enough there were beautiful screens. “Oh, how lovely!” she exclaimed, but then she took a closer look. The numbers made no sense. And pretty soon the screens began to crash. “I’ll get that fixed,” said Papa Bear nervously. “It’s just a small bug.” Goldilocks left, disappointed.

Goldilocks pondered her predicament. All she wanted was to know how many anvils Acme was moving every month to those Roadrunner-chasing Coyotes. Seemed simple enough. But five weeks had passed already and she had nothing to show for it.

And just as Goldilocks was about to give up entirely along came her old college pal, Baby Bear. She explained her reporting needs to Baby Bear who said “No problem. Let me put a quick task list together, and we can review tomorrow.” “Yeah, right,” said Goldilocks. “Fool me once….”

But Baby Bear wasn’t deterred. “How about I come by your office tomorrow,” he said. “I’ll see you at 2:00pm.” Goldilocks rolled her eyes, but sure enough at 2:00pm the next day Baby Bear arrived. And in his hot little paw he held a simple, 40-line task list in Excel. “I’ve thought about the various things we need to do,” Baby Bear explained “Should take a few weeks. We just need to be organized, but not overthink the problem.”

“That’s all well and good, Baby Bear,” said Goldilocks, “but I’m nervous. Can I really wait another month to know whether you’re making progress?” Baby Bear said that she certainly shouldn’t wait a month to see results. Each week they’d meet and review progress to date and give her as much real stuff to look at as possible, always adjusting the task list to make sure that they were staying on track. Without any other options, Goldilocks agreed.

Every week Goldilocks and Baby Bear went through the work and adjusted the task list, and every week she saw progress. And at the end of the month she had her dashboard.

The moral of the story: Project Management isn’t brain surgery. But you have to know what you’re doing. We want neither the “too cold” approach of thousands of tasks and not enough work, nor the “too hot” approach of, “Let’s just start coding now.” Rather, we need the “just right”approach—enough structure, consistent progress, and a successful project.

 

It’s 10pm – Do You Know Where Your Data Is?

We spend most of our days making data work for people. But before it can work, two things have to happen. One ? the data has to be there; it can’t be lost because your server crashed or your cloud provider went down. Two – the data has to be secure; you can’t have a laptop with the Social Security Numbers and compensation of the entire executive committee go missing.

But all too often I find that our clients ask the wrong questions about security. Metaphorically speaking, they install a dozen locks and a high-tech motion detector on the front door but leave the windows wide open. So to help assess the level of security risk that threatens your data, I’ve developed this simple quiz. Answer the nine questions below to determine just how vulnerable your data may be.

1. I send emails to the wrong person?
(a) Never ? I’ve modified my email so it doesn’t auto complete and my secretary double checks every email before I send it. (10 points)
(b) It only happened once – I’m much more careful now . (5 points)
(c) Speed is what matters. If the wrong thing goes to the wrong person once in a blue moon it’s a small price to pay. (0 points)

2. When an employee leaves the company?
(a) Their accounts are disabled immediately and their activity for the last 2 months reviewed. (10 points)
(b) We make sure to copy down their passwords so we can get into all their key files. (5 points)
(c) We take them for drinks at the local bar. Great 2 for 1 specials. (0 points)

3. Employees share passwords with one another…
(a) Never – it’s absolutely forbidden and strictly enforced. (10 points)
(b) On rare occasions when it’s absolutely necessary. I mean, the Controller spends a lot of time in meetings and those SOX auditors have made life difficult. (5 points)
(c) All the time – that’s why everyone keeps a Post-It note with passwords right on their monitor. (0 points)

4. Your network is either monitored by your internal IT department or by an outsourced party. How frequently does a third, independent party verify that your firewalls and other network security are in working order?
(a) Once a year – like clockwork. (10 points)
(b) We change providers every 18 months so things get checked then. (5 points)
(c) How much more money do you expect us to spend with consultants like you? (0 points)

5. People can log in from home…
(a) Using an RSA secure ID ? the token that changes effective passwords. (10 points)
(b) Using a basic password to our VPN. (5 points)
(c) Directly from our website ? who needs more aggravation? (0 points)

6. We test our backups… (restoring the data and validating)?
(a) Every six months. (10 points)
(b) We got a new server a couple years ago ? we got the data moved over after a week so it seems to work. (5 points)
(c) You mean that stack of tapes isn’t enough? (0 points)

7. If your computer room was flooded right now…
(a) Our backup DR (Disaster Recovery) site would kick in and those with power could start working from home. (10 points)
(b) We?d quickly move the servers to the top floor of the building. (5 points)
(c) That’s why I’m here networking… (0 points)

8. Crucial data (like Social Security Numbers)?
(a) Is kept secure and reviewed regularly. (10 points)
(b) Was supposed to be secured when we bought the software. (5 points)
(c) Should be free ? we’re all friends. (0 points)

9. My ERP data is?
(a) Entirely contained within our firewall. (10 points)
(b) Connected directly to our ecommerce site ? it’s really cool! (5 points)
(c) Made available directly online ? it is web-enabled, isn’t it? (0 points)

TOTAL SCORE
66- 90 points ? Security Risk Minimal. You can sleep soundly.
35-65 Points ? Security Risk Moderate. You?ve made some progress, but you?ve still got work to do.
0- 35 points ? Security Risk High. Call Red Three.

The Takeaway:

We worry about all kinds of things, but we don’t focus on the basics ? like making sure your tape backup works. Sure there are all kinds of reports of hacks and stolen data, but the biggest security risk is NOT having data. We also frequently forget that some tools ? like email ? just aren’t secure. Human beings are the biggest problem. No matter what you spend on technology, if the members of your organization aren’t security-conscious, you?re going to have problems.

Next month we’ll drill more deeply into how to ensure your data is secure and safe. If you have questions in the meantime, please feel free to contact us.

 

The 60,000,038-Year-Old Dinosaur

 

A family on a trip to the Museum of Natural History comes across a display of a particularly interesting dinosaur. The massive beast is displayed in an impressive pose – standing on it’s muscular back legs, mouth agape, razor-sharp teeth fully bared.

As the family stands admiring the display, a museum docent approaches. “Impressive, isn’t she?” the docent asks. Each member of the family concurs, and the docent continues, “This dinosaur is 60,000,038 years old.”

“How is that possible?” asks mom. “How could you know the age so exactly?” “Simple,” replies the docent, “when I started working here they told me the dinosaur was 60 million years old. That was 38 years ago.”

Ridiculous? Of course! But how often do we do the same thing in business – treating a number that is no more than an approximation as though it were an absolute and perfectly accurate figure? Anyone who has worked with corporate data knows how much estimation and approximation gets entered into any system, and of course there is human error to account for, and omission.

Sure, those of us who are comfortable with data and accounting know that the numbers are estimates, that we are making approximations upon approximations upon approximations. But Numberphobes don’t understand that. They see detail down to the penny in the annual budget projection and they think that we know something that they don’t, that we are some sort of superhuman being with secret accounting knowledge that allows us to see into the future.

So ditch your decimals if you want to get Numberphobes to look at your report. By eliminating some of that needless detail you’ll be able to more effectively communicate your data to those members of you organization who hate numbers.

 

Finance, Oh Finance

 

Finance, Oh Finance

Lyrics by Adam Jacobson

(with apologies to Jerry Bock and Sheldon Harnick)

Finance, oh Finance I’ve seen the report.
The numbers look bad. We have to talk.
After you make an adjustment or three
Things will be better – you’ll see.

Let’s start – our sales aren’t low.
We’ve signed ten new contracts – didn’t you know?
And those expenses seem high.
They really are capex – I wouldn’t lie.

Finance, oh Finance we aren’t quite through.
Though it looks better there’s more to do.
If you look harder I’m sure that you’ll find
There’s something that’s slipped your mind.

Now let’s talk inventory.
You say it’s not moving – that’s not the whole story.
End with professional fees.
A one-time restructure – proforma please.

Finance, oh Finance we’ve had our review.
I feel much better – I’m sure you do too.
The future is bright and my bonus is clear.

This wasn’t so bad.
The Board will be glad.
And such a light touch.
Why, thank you so much!
I can’t wait to talk next year!

 

The Hitchhiker’s Guide to the Galaxy – The Best Business Book Ever

You’re probably asking yourself, “How in the world is The Hitchhiker’s Guide to the Galaxy a business book?” You’re also probably asking, “Is Adam really that big of a geek?” To the second question I will respond with a resounding, “Yes, I am.” To the first, well, here goes…

I read a ton, both for business and for pleasure. I have a wide range of interest, and I’ll read just about anything – serious stuff, funny stuff, geek stuff, and of course a lot of business books. Most of the business books I encounter really should be magazine articles – you get the point after the first chapter, and the remaining dozen chapters are there to support a speaking tour. But there are some business books which are worth your while. The books below all have three things in common:

  • The content is memorable and has stuck with me over time.
  • I have either re-read them or passed them on to my staff, or both.
  • The content is unique and not repetitive – they are worth reading all the way through.

With those criteria in mind, I present a list of my favorite business books:

The Cathedral and The Bazaar – Eric S. Raymond
Flow – Mihaly Csikszentmihalyi
The Goal – Eliyahu Goldratt
The 7 Habits of Highly Effective People – Stephen R. Covey
The Social Life of Information – John Seely Brown
The Visual Display of Quantitative Information – Edward Tufte
Where Good Ideas Come From – Steven Johnson

And, of course…

The Hitchhikers Guide to the Galaxy – Douglas Adams

So why is The Hitchhiker’s Guide to the Galaxy one of the best business books ever written? Simple. It contains the single most useful piece of business advice I’ve ever received:

 

Do You Have Consultant Dependency Syndrome?

“CDS” is Consultant Dependency Syndrome. It’s ugly, expensive, and it’s usually contracted from a consultant who aims to embed him or herself into your organization like a tick until they become a necessary part of your regular operational procedures.

At Red Three we are dedicated to the eradication of CDS wherever we find it. So how do you know if you’re infected?

  1. If you need the consultant to come back and fix a report every time the data changes, you have CDS.
  2. If you can’t close the books at month’s end without the consultant, you have CDS.
  3. If the auditor arrives and you need to call the consultant to explain how the system calculates some key numbers, you have CDS.
  4. If, when you ask for training, the consultant says, “We’ll make sure to schedule that” and then never does, you have CDS.
  5. If you’re amazed at the complexity of your system—but have a gut feeling that it really shouldn’t be so hard, you have CDS.
  6. If your consultant knows more about your operations—not just your systems—than anyone on your staff, you have CDS.

There’s nothing wrong with outsourcing any particular function if that’s what makes sense. But that should be an active choice, not the result of a gradually building case of CDS.

 

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284
Email: inquiries@redthree.com

Request a Consult