Monthly Archives: April 2013

Your Spreadsheet is Lying to You

I love Excel spreadsheets. There’s nothing more satisfying than turning a blank Excel grid into a fountain of data and conclusions. Invite over pivot tables, external data, and macros, and suddenly you have an entire business analysis party. You can confidently make big dollar decisions from small and simple cells – or so we think.

Excel Lying

Knowing my dependence and appreciation of spreadsheets, a good friend sent me a very interesting Wall Street Journal article, 88% of Spreadsheets Have Errors by Jeremy Olshan. In the article, Olshan exposes how almost all spreadsheets have at least one error in them, and large spreadsheets are usually filled with mistakes. We often are so rushed to get the calculated conclusion to other people that we don’t take time to check our formulas and have someone else proofread our work. Miscalculations and mistrust of analysis can often be the result. The article mentions major economic research that drew incorrect conclusions based on flawed spreadsheet formulas. Likewise, I’d like to share a story of how incorrect cells can wreak havoc on supply chains.

Barcode Blunder

I still shudder to think that a simple, one-sheet spreadsheet completely ruined two weeks of my life.

My company uses a barcode printing program that pulls its information from a simple spreadsheet. The spreadsheet allows users to easily enter the item number they need, and the close-by thermal printer quickly spits out the barcode stickers. I had set up the spreadsheet to pull all the necessary information, including the 12-digit barcode and description, from external sources when the item number was typed in. The formulas worked great, and the spreadsheet seemed perfectly self-explanatory. Or so I thought.

Because the interface was so easy to use, more and more employees began to use it to print barcodes. This was great for me because I was not called out seven times a day to print the stickers. However, the lack of standardized training and safeguards eventually created a problem. Seeing the UPC column, someone replaced my formula that calculated the UPC with a static 12-digit UPC number for the item they needed. Having been saved with the incorrect number, the spreadsheet now printed the same, incorrect UPC barcode for every item. However, because the description was correct, no one caught the error. Worst of all, that week we had a big project that required a large number of labels.

One of our customers increased their orders significantly for an item that we had to label individually. This amounted to over 50,000 incorrect barcodes stickers that we put on, and then shipped without knowing the error. They went out the door on time, and the problem wasn’t fully realized until the product was already set in thousands of retail locations.

Was the Spreadsheet to Blame?

So who or what can we really blame for this mistake? After lots of pondering, I couldn’t blame anyone but myself – the creator of the spreadsheet. I don’t know who changed the cell, but it really wasn’t that person’s fault. Whenever I create a process, program, or spreadsheet for others, I need to make sure it’s error-proof. To fix it, I immediately locked down all the cells and rewrote the spreadsheet to be much harder to incorrectly edit (some simple poka-yoke, or in English, error-proofing). Just as important, we added a barcode-check step in which we scan the barcode and get a second pair of eyes to approve it. So, when the malevolent spreadsheet decides to strike again, we’ll be able to catch it.

While this may be a story more on the need for better processes, it also illustrates the devastating power one spreadsheet cell can conjure.

In the end, the retailer was able to change their computer system to accept the incorrect barcode, so the effect on end consumers was very minimal. The real pains were internal as we identified our process problems and worked to correct them. Of course, I lost some sleep and worked under heavy stress for a couple weeks, but as a result, I learned some important lessons – not the least of which concerns spreadsheets.

Five Tips toward Error-free Spreadsheets

Even if the world is plagued by lying spreadsheets, we can still personally become sources of accurate and correct data analyses. Here are five tips toward more accurate spreadsheets.

1. Get Someone to Proof Your Work and Make You Explain Your Formulas

If you do nothing else, please do this: Ask someone to look through your spreadsheet and test all your assumptions and formula logic. Have them examine key calculations closely and confirm everything is calculating correctly. Offer to do the same for your friend’s spreadsheets in return.

2. Trace Cell Relationships

excel trace function

Excel’s trace functions, found under the formula tab in Excel 2007 and 2010, are excellent ways to make sure all the cells you think are included in a calculation actually are. Trace precedents will point arrows to all the cells that feed into the cell you’ve selected. Trace dependents show all the cells depending on the selected cell. These help you catch problems of cells sneaking in or out of formulas – as well as seeing what might happen to other cells when you change the current cell’s value. Plus you feel pretty smart when there are hundreds of arrows all over your screen, “Wow, I can make some intense calculations.” Note that this step would probably have helped me catch the error in the story above.

3. Verify Links

Update Links

External data, especially from other spreadsheets, can be very tricky. Excel isn’t the best database system in the world, so links sometimes break or don’t update. To avoid problems, verify and update links through the edit links button on the data tab. This tool let’s you ensure links are active and correct. I’ve had many links break for no apparent reason, so be vigilant on checking this frequently.

4. Address background error checks

Background Error

Those little triangles in the top left corner cells are signaling that Excel thinks something might be wrong. Address them all, since often they are true problems. Only ignore the error if you’re sure it’s not an error, and the triangle will disappear.

5. Force Excel to Calculate

There are several reasons Excel stops calculating formulas. Usually it’s because calculations are turned off, but sometimes big files have quirks that hamper calculation – especially when VBA is involved. To recalculate all formulas and update all links, press Ctrl + Alt + Shift + F9. If you have external data, you’ll want to use the Refresh All command. If you have pivot tables that pull from External Data, you’ll want to refresh the pivot tables after you Refresh All (or Refresh All twice). Pressing Refresh All twice, then pressing Ctrl + Alt + Shift + F9 will refresh and calculate everything possible.

When You Need to be 100% Accurate, Rebuild from Scratch

Although these tips will help you catch many errors, there are still chances of problems. Several third-party vendors offer error-checking software. I’ve never tried them, but I do have a pretty strong technique to ensure high accuracy. For very important spreadsheets and decisions, I often recreate the entire project from a blank workbook to confirm everything is correct. I don’t copy and paste, but instead retype the formulas to make sure everything matches. Quite time intensive indeed, but duplicating my work usually takes a fraction of what it usually takes to create it. In fact a two-week spreadsheet took only an hour to completely rebuild from scratch because I knew exactly what to do. Having rebuilt it, and with everything matching perfectly, I feel confident that my spreadsheet is accidental-error free. Of course, whether my human assumptions are correct is an entirely other issue.

So the next time you find yourself in possession of a breakthrough calculation in Excel, I hope you’ll be a bit more leery that your spreadsheet is likely lying to you in some part. Encourage honest spreadsheets by error checking and proofreading so we can all avoid painful spreadsheet problems.

How to Win Friends and Influence People in Your Supply Chain


How ti Win Friends and Influence People in the Supply Chain

Even though supply chains are becoming more automated, people still play a critical role in successful interactions and product flow. This is especially true for most small businesses. Relationships and conversations matter much more to small businesses because they don’t have the power of Walmart or Apple to rely on novel-length contracts and impersonal automation. Instead, phone calls, personal visits, special favors, and constant cooperation are what keep small companies alive and their products flowing. So it makes sense that the ability to win friends and influence people in your supply chain is a powerful skill that is needed to help your company survive and flourish.

I recently finished listening to the business classic, How to Win Friends and Influence People (HTWF&IP), by Dale Carnegie. Although originally published over 75 years ago, it’s still a consistent bestseller because it’s simply a great book. The lessons are timeless and apply directly to everyday life in along the supply chain. I’d like to share a few simple examples of how I’ve seen the book’s suggestions directly help me solve problems with vendors, customers, and fellow team members.

Always Make the Other Person Feel Important

“There is one all-important law of human conduct. If we obey that law, we shall almost never get into trouble. In fact, that law, if obeyed, will bring us countless friends and constant happiness. But the very instant we break the law, we shall get into endless trouble. The law is this: Always make the other person feel important.”

“Make the other person feel important – and do it sincerely.”

― Dale Carnegie, HTWF&IP

The strongest takeaway I gained from the book is that making others feel important can help solve most conflicts. The key, however, is to do so in a sincere way. Never use flattery or give undeserved phrase. However, by recognizing the important roles that others play – and seeing problems from their perspective, you can give them the due respect and honor that you would like to be given if you were in their place. Often problems vanish as soon as the other person feels acknowledged and respected.

I’ll first share an embarrassing example I’ve realized about myself. My company often gets last-minute rush orders that fall outside our normal fulfillment time. We have a standard lead time, but sometimes to win the sale, our salespeople must promise delivery in a much shorter period than our standard. When those orders are thrown at me, and I’m ordered to make it happen, I find myself reluctant to do so. However, when someone approaches me kindly, acknowledges how busy my team is, and asks me to please consider making an exception this one time, my attitude is completely different. I feel important and thus do everything I can to make sure the order succeeds. Of course, I recognize that I need to work on helping all orders succeed equally, but you can see how powerful an extra 30 seconds of conversation can be.

How to Win Friends and Influence People

Utilizing the same principal, I recently helped schedule a delivery appointment with a key customer even though we thought it couldn’t be done. Our warehouse supervisor had called for an appointment at the end of the month, and because the customer was fully booked, the customer said we couldn’t deliver until several days later. Our CEO wanted us very much to deliver within the month, so I called back. I talked with the same receiving clerk for a few minutes. I acknowledged how busy he was and listened for a minute about everything he had going on. I praised him that he’d be willing to handle that much on a Friday. After just a minute of getting to know him a little more, I explained that our order was very small, just a few cases, and he would be doing us a great favor if we could sneak it in between his many other important shipments. He said it wouldn’t be a problem – and we delivered that afternoon. Not only did the order get delivered on time, but I now have a friend at our customer’s warehouse that I look forward to talking with again.

Make others feel important – and do so sincerely.

Continuous Improvement through Continuous Praise

“Let us praise even the slightest improvement. That inspires the other person to keep on improving.”

― Dale Carnegie, HTWF&IP

Enabling others to improve is one of the most rewarding and effective methods of improving your company and supply chain. Through improving and encouraging the job skills, creativity, and problem solving abilities of others, your entire network will benefit from more engaged players and improved processes. In fact, a key tenant of the Toyota Production System is to encourage improvement through the creativity of every one of its employees and supply chain members. Imagine if Toyota managers were dictatorial, constantly shouting out commands and berating employees for mistakes. It’s not a stretch to predict that Toyota would be out of business if that’s how they managed their people.

Praise, in contrast, has an almost magical effect in motivating others to improve. Based on this principal, I’ve watch my company grow one of our key suppliers through targeted praise – and withholding the occasional frustrations.

When we first started producing one of our new products, we found a small firm of just a few people that was eager to work with us. Although eager, they often had quality control problems, delays, and incongruent processes that come from being a young company. I became frustrated many times, and recommended dropping them as a supplier – even though that meant they would likely go out of business.

Despite my recommendation, my wise manager counseled that we keep them on as a continued ally. My manager was careful to encourage them to improve:  he identified and focused on what they did right. When problems surfaced, we took a shared-problem approach and tried to solve the issues in a way that we and the supplier would bear responsibility together for improvement. We rewarded their progress with compliments and increased orders.

Because of the years of patience and encouragement, this supplier is now our miracle worker. We have other, more advanced suppliers. However, when we’re in a tight spot, no one can pull off an emergency order with next to no lead time like that small supplier we’ve grown. Because of this supplier’s expertise at short-notice orders, we’ve been able to catch many sales opportunities that we would have otherwise needed to pass up – or pay much more in expedited freight. I’ve witness how patience and praise can help a supplier grow to a point that boosts your bottom line when no one else can.

Avoid Arguments and Let Others Save Face

“If you argue and rankle and contradict, you may achieve a victory sometimes; but it will be an empty victory because you will never get your opponent’s good will.”

“Letting one save face! How important, how vitally important that is! And how few of us ever stop to think of it! We ride roughshod over the feelings of others, getting our own way, finding fault, issuing threats, criticizing a child or an employee in front of others, without even considering the hurt to the other person’s pride. Whereas a few minutes’ thought, a considerate word or two, a genuine understanding of the other person’s attitude, would go so far toward alleviating the sting!”

― Dale Carnegie, HTWF&IP

In the course of your interactions with customer and vendors, I guarantee that points of disagreement and contention will arise. Huge quality problems, incorrect freight counts, painful cycle counts, overdue orders, key stock outs, mountains of obsolete inventory, and…well, I’m sure you could add another 20 problems to the list. Indeed, problems are so common in the supply chain that I’ve heard supply chain management called problem management or fire management. But how do we fight fires while still thinking long-term? Indeed, supply chain success comes not only from solving the temporary problems, but creating long-term cooperation and synergy through strong relationships. Thus, we find ourselves in a precarious balance between the problems of today – “who’s responsible for fixing and paying for this?” – and long-term partnerships to improve the entire supply chain – “how can we improve information and product flow along the chain?”

Better defined, the risk we face is allowing the problem of the day to slow and destroy the progress made on long-term relationships. Even if someone is particularly difficult to work with, venting your frustrations and giving that person a piece of your mind could easily set back months or years of investment. Even if you’re completely right, avoiding argument – or letting your opponent gracefully retreat – may be the smartest move to make.

Certainly when money is involved, the process takes on higher stakes. In the interest of our companies, it certainly befits your and I to recover money that is rightfully ours. However, when arguments or disagreements become personal, bitter, and unrelated to the actual problem – these are fights to avoid.

When dealing with some of our Asian suppliers, this concept becomes especially important. Asian cultures treat conflict differently than other cultures. Saving face is much more important there than in the US or Europe – although we often fail to realize that it’s still quite important in any culture. When problems arise, if we are able to deflect the blame away from any specific person and instead focus on solutions, our suppliers are much more willing to work with us on solving the problem. For example, whenever I send an email to a supplier that dictates, “This was your fault. You need to either do this or pay a significant penalty,” nothing good ever comes from it. However, when I am able to approach the problem as the following, the problem is often rectified: “We noticed this problem. This is not good because our end consumers are not happy when this happens. What can we do to solve this problem?” Working together to solve a problem is always more productive and rewarding than pushing a punishment.

Final Thoughts

How to Win Friends and Influence people is a great reminder of how we should treat others. It brings together a lot of applicable advice on how to interact with people, and therefore becomes an excellent real-world resource that teaches just what the title professes.

I enjoy spreadsheets, databases, and technology, but relationships with people are more important than any automated email alert. Carnegie cites a study that explains how “even in such technical lines as engineering, about 15 percent of one’s financial success is due to one’s technical knowledge and about 85 percent is due to skill in human [interactions] and the ability to lead people.” I see it as no stretch to say that at least 85% of the success of your business in the supply chain is your ability to interact positively with others.

If you’ve already read Dale Carnegie’s book and would like another great read on improving human relations, then I recommend Leadership and Self-deception.