BerlinPacific.com Blog

How do you get excel to quickly perform 160,000 lookups against 160,000 different unique records and get an exact match, for a total of up to 25,600,000,000 different operations?

Basically there is a fast way to do this that quickly produces results in a minute or so - and a slow way to do this that checks all combinations and could take hours.

The fast way is to use IF formula to check if index match found an exact match on a sorted table, if it did not then don’t use the result, if it did then use the result.
=IF(A3=INDEX(’Rate Table’!$B$1:$B$145390,MATCH(O3,’Rate Table’!$B$1:$B$145390)),INDEX(’Rate Table’!$A$1:$A$145390,MATCH(A3,’Rate Table’!$B$1:$B$145390)),”NONE”)

Why would you need to do this?

Maybe you have a huge list of names and you want to look up their phone number in a directory, or vice versa.

In our case we were analyzing the costs of 160,000 different phone calls against different rate plans. Each call could be billed at any of 160,000 different rate plans. (While consumer rates are simple - wholesale rates are quite complex - all numbers starting with the NPA NXX 212-555 may have a different rate from all numbers starting with 212-556.)

You want excel to look at the call and see where the call was made to and what the rate should be. For example a call is to the 212-555 NPA NXX. You therefore look up on the rate table what the rate is for calls to the 212-555 NPA NXX. You then have excel do this for every call you make.

(more…)

This is a good article in The Atlantic - The Management Myth.

Summary:
Most of management theory is inane, writes our correspondent, the founder of a consulting firm. If you want to succeed in business, don’t get an M.B.A. Study philosophy instead

Notes:
Philosophy is important. A key question should be - how do we know? With our prospective clients we’ve noticed that those who aren’t interested in working with us usually think there’s no room for improvement - however they have no way of knowing that. In fact everything they say indicates there is room for improvement. Since the areas we help aren’t part of the core business we believe it isn’t a priority for the business, which is why they should hire an outside expert. Businesses hire outside people to do their taxes. They also focus on their core competency - they don’t try to deliver their own packages overnight they use fedex. However these outside experts usually don’t understand the business of their clients as well as their clients - so they have to be careful to not fall in the trap of cookie cutter recommendations and leverage the knowledge and expertise of their clients.

It is interesting how in many fields people will talk about and do what sounds good without actually checking to see what the results really are.

It is particularly interesting his comments about Taylorism - people might want to read John Taylor Gatto’s Underground History of American Education. Many of the ‘features’ of schooling come from the assumption that ordinary people are too dumb to do things well and must be trained by experts, when in fact ordinary people can do quite a lot once they put their mind to it.

The article notes how many consultants don’t show or track results. At Berlin Pacific we actually track the results of our projects. We can prove bottom line results. And of course we charge for it.

People may also be interested in Theory of Constraints/TOC and Market Based Management. Both are focused on making money - i.e. cash flow. That is probably why Koch industries has revenue of around $100 billion. The Measurement Nightmare goes in to some detail as to how measure performance - it and The Goal are great TOC books to start with.

Today’s news shows that Allstate is suing Countrywide (owned by Bank of America) for misrepresenting the mortgage investments Countrywide sold Allstate.

You can read a good analysis here.
http://www.zerohedge.com/article/how-allstate-used-sampling-confirm-bofacountrywide-lied-about-virtually-everything-selling-m?goback=.gde_40850_member_38750536

Allstate claims to have had no idea what is was really buying, just like most firms have no idea what they’re really buying from their telecom vendors and if they could or should have paid less.

The root problem was that Countrywide apparently systematically did not collect valid information. (Many loans were ‘liar loans.’) Lacking good information Allstate bought investments it didn’t actually want (it wanted AAA securities.) Similarly Countrywide apparently did not report that many approved mortgages (around 20% sometimes more) were ‘exceptions’ to Countrywide’s underwriting standards. Countrywide management appears to have been aware that their loans didn’t meet their underwriting standards and information about the borrowers income, value of the home, etc. was often false but they didn’t do anything to disclose this to investors.

You can read the allegations here
http://www.scribd.com/doc/46005151/12-28-10-1
It is a very interesting document.

It is interesting to learn that the exact same problems (lack of visibility, lack of information, use of misleading aggregates) that we see in our clients’ spend on vendor purchases also plagued institutional investor spending on purchases of investments. However this mortgage mess was apparently systemic outright misrepresentation by the underwriters. We find vendors simply don’t provide the information clients need to make an informed decision, and they’re certainly under no obligation to provide it. A huge moral difference on the part of the sellers, but as far as the buyer is concerned the outcome is the same. Interestingly in both cases some important information isn’t even readily available to the sellers let alone the buyers.

Apparently Allstate went to the trouble of checking out many of the individual loans and found huge discrepancies between the reality of the applicants and what Countrywide had stated about the quality of the applicants. This is similar to what we do, our goal is to check EVERY single service to make sure it is adding value and is not misbilled, underused, or overpriced.

One has to wonder though why no one at Allstate did any due diligence sampling of the underlying loans before they bought them…

Presumably because they were too busy and they had no reason to suspect anything was wrong. Too bad they were wrong.

(more…)

[Download a Copy]

Rockefeller and Standard Oil gave the world inexpensive refined oil by continual improvement –decreasing the cost of even the humble stopper in a barrel of oil while maintaining quality. Japanese called this process Kaizen and using it gave consumers cars renowned for not breaking down.
Many firms see telecom expense management (TEM) as a process of bill processing and auditing, along with periodic contract negotiation. They do not see the opportunity for continuous improvement through lowered costs and better service year after year.
Continual savings do not come from periodic contract negotiations every few years or looking for billing errors that shouldn’t be there in the first place. Best practices require looking for continual improvements as prices decline, superior solutions come to market, and business needs diverge from current services. Firms that put in place these best practices realize savings of 20-50% compared with firms that do not implement these best practices.

Three Approaches
There are three primary ways to cut costs, all of which require a firm understanding of the company’s current infrastructure and bill detail. The three approaches are bill validation, infrastructure optimization, and contract management. This means that every single service must be individually scrutinized to ensure that the service is being billed for correctly, the service still serves a business need, and the service is billed for at market rate.

(more…)

We got a fascinating piece of spam today from free wifi robin dot com. It brings up interesting security concerns. Their product helps one get on wifi networks, it is unethical and looks very slick. Not the kind of free we recommend.

Basically the $160 solution will crack WEP WiFi networks. Due to the crummy nature of the encryption, capturing enough WEP traffic will allow anyone to crack the WEP encryption in 20 minutes. This means if your network uses WEP it can be cracked, exposing your machines. Apparently this wifi cracker also has a powerful antenna, so it can connect over a wide radius.

The way to solve this is to not use WEP, but WPA.

This bring up the problem of whether WPA can be cracked. It can, but it takes a while. (WPA2 AES is the best encryption - however, and takes the longest, and may not be easily crackable.)

What basically happens is that the cracker will monitor your encrypted connection and force you to re-authenticate. When you re-authenticate, which you probably won’t notice, that data can be saved to the hackers computer and cracked at their leisure. Basically they’ll run a brute force attack - trying every possible combination. It may take a while, but today’s computer are very powerful. One can even use graphics cards to speed up processing. Any password less than ten characters might be easy to crack that way. (Most people use only the 26 lowercase letters and ten numbers to make a password, therefore a password of 6 characters is one of only 2,000,000,000 combination’s. From a computer’s perspective this isn’t very many combinations.)

One is advised to use long passcodes - longer the better.
Passphrases are good -
itwasadarkandstormynight would probably work.

If you want to keep people off your Wi-Fi, don’t use WEP use WPA2 AES, or WPA and create a longer password.

Gliider is a free downloadable tool to help you research travel plans. You can

1. Find and gather travel info from all your favorite sites
2. Ask for advice from people you trust.
3. Book and save info on travel deals

http://www.gliider.com/demo/

The demo is great.

We’re starting a blog to talk about our various activities. Berlin Pacific is focused on vendor cost management. But we’re also interested in whatever improves business performance. So we’re always learn about Sales and Marketing, and are interested in ways people can improve their processes to be more efficient, or get more out of them in the case of Finance and Accounting.

This is our first post.