Category Archives: Uncategorized

The Problem With KPIs

Posted on by
Reply

I was walking around the production area with one of my former bosses earlier in my career. I had recently been appointed to a position I had been performing for years and during this walk I was told that a list of approximately 7 metrics were what I needed to hit to succeed in the new role. And, as a newly anointed member of management, I would be measured against these KPI’s. I reviewed the 7 and I assured my boss that hitting the targets would be no problem, to which he looked astonished and then smirked. I felt a lesson coming on.

My boss, about 10 years my senior, assured me in a lightly condescending tone that I should try hard as he would very much appreciate that effort. It was apparent to me that he felt my over exuberance was simply showing naivety. I turned to him and said that I honestly have no doubt I can accomplish these objectives, the real question is do you really want me to?

He stopped, not knowing how to handle my defiantly subversive comment. He was genuinely interested at my statement and my apparent lack political correctness. From my point of view, to make money and succeed, we need to be brutally honest and do what actually needs to be done.

Anyone can make a KPI turn green on a report, but that doesn’t mean we have done what is truly needed for success!

Success? — Declaring KPIs can give us a managerial assuredness of progress and the successful meeting of business measures, but it does one insidious thing–

KPIs become the proof, unquestioned, that business is succeeding!

And often are used to justify the very actions which lead to business failure. KPIs are not the truth, nor should they be confused with the truth.

KPI stands for Key Performance Indicator and nothing else

We have divorced ourselves from the main point of a KPI –> Indicator. An indicator is nothing more than a touch point to begin to understand. The entry point for guided analysis. To put it another way, a KPI is not the analysis, it is simply the beginning. And no KPI should be ignored, regardless of the target attainment characterization.

Analysis must be of the underlying root causes, but to often is relegated to simplistic summary reporting of KPI values, often with the infamous change over time comparisons of the very KPI such as Year Ago or Like Periods –essentially an analysis of the change in KPI status.

Do the hard thing–ask your team:

  • “What is wrong with the company?”
  • “Why are we losing market share?”
  • “Why are we meeting the SLA?”
  • Etc.

Will the team give you the KPI’s restructured with trend over time?

How about a song and dance about a series of initiatives?

I suspect no true scientific testing, very little statistical analysis, etc. And driving this behavior is the fear of reprisal for saying “we do not know why” and who could blame them when the analysts’ jobs have been to refresh the standard reports and send them to the proper stakeholders for the last 5-20 years, with only the occasional investigation into if a negative color is the result of a data refresh issue, a master data change that broke a formula or just actual performance!

No one is at fault, it is the trap of using KPI’s incorrectly

Business success requires being on the forefront of analytical understanding of data and a divorce from the “all green” KPI dashboard as proof of success. If someone is hitting their targets, question it. If a department is green, show me the details. Run an analysis on the underlying data points, you will probably not like what you see. Prove why hitting this target did what we needed!

Accept a normal operating tolerance that allows for the “yellow to red” as an OK and normal part of doing business. Ignore nothing. We must accept that:

Not every project will be Green and nor should they be

Yellow marks deserve the same level explanation as a Green ones–imvestigaton beyond the indicator!

Use VBA to Pull a Text file into Excel

Posted on by
Reply

Have you ever wanted a list from a text file to be imported into Excel?

Using VBA you can import data to your Excel file from many types of files. Automating this process with VBA will allow you to quickly pull data avoiding unnecessary manual copy and paste techniques.

Note: This procedure builds on my previous posts using VBA to generate File Lists in Text files. Batch for Obtaining MS Excel File List and VBA To publish a Batch File from MS Excel

Tools: MS Excel and the batch file called filelist.bat from the previous blogs

Please see reference Excel file to follow along—the file has the VBA to write the batch file, to run it, and to the below code to import the text files data.
The Code:


Sub OpenFileList_User_Entry()

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Capture Files in directory for processing (can be a network drive)
'
' Imports .txt into Excel on Sheet2 column A
'
' Authored by Alexander Zbiciak 1/14/15
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim Filepath As String
Dim row_number As Integer
Dim Folder As String

'Delete old list from column A

Sheet2.Columns(1).ClearContents

Sheet2.Cells(1, 1) = "File Name"

'Pull in new list, first declare folder and file

Folder = <<Enter your file path here with a closing \>>

'’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
'
' Example: Folder = "C:\Users\Alexander zbiciak\Batch Example\"
'
'’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
Filepath = Folder & "fileslist.txt"

'Select Output location (Will start at row 2 and be column A)
Sheet2.Cells(2, 1).Select

'Loop each line to a cell in Excel
Open Filepath For Input As #1

row_number = 0

Do Until EOF(1)

Line Input #1, LineFromFile

LineItems = Split(LineFromFile, "")

ActiveCell.Offset(row_number, 0).Value = LineItems(0)
row_number = row_number + 1

Loop

Close #1

'''''''''''''''''''''''''''''''''''''''''''''''''''
'
' If your batch file does not filter out the non Excel files you will need to control for it here
'
' Add in VBA to clean your list
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End Sub

How To:

Download the example file. Use this as a template to understand how to import the contents of the .txt. Later you can change where data is written to.

Write the Batch file and save it to the folder you want the file list from

Open the file and the developer tab

File >> Options >> Customize Ribbon >> Developer

Look for Module 1 and find the VBA labelled “Sub OpenFileList_User_Entry()”. The sub is the same code as above. Once you are in the code, you will see the red text that needs to be updated:

TextImport1

Update the file path to the location you will have your files and batch file at.

The VBA code will pull in all files in the directory you declare. Please note that there is the chance you will bring in non-excel files depending on how you wrote your batch file. The batch file should limit to .xls type files, but always check to be sure the command *.xls? in the batch file isn’t bringing in the wrong types of files.

Namaste!

Posted on by
2

2014-07-20 13.25.20Kaise Ho Tum!

And welcome to my first post from India. My friends have been expecting this, so here we go!

(yes no daily lesson on Excel best practices–you lucked out. Instead just a how its going blurb.)

 

 

Where the Heck Am I? 

Hyderabad, India. Specifically: Telecom Nagar Colony, Gachibowli.

Yeah that’s a mouthful! I was brought here to advance my skills in SAP. Being an avid Excel guru and an, in retrospect, light SAP user, I figured now is time to step up the game of corporate information management from a mere Microsoft enigma to a full fledged ERP solution. My M.B.A. in C.I.S. just isn’t going to cut it–time to dig deep. TekLink International took me on and brought me here to do just that.

Acclimatization!

Two weeks in I have recovered from jet lag and away from American food to the local meals. Here the food style is referred to as South and is, from what I am told, a lot spicier. Surprisingly I like what I have had so far, my stomach, however, maybe not so much. The good thing is Dahi  (yogurt) is readily available.

2014_Mackinaw ArchRock

I am however, missing my family and friends quite a bit. Luckily we have Google Hangouts and Skype. Love the daily talks, miss the daily hugs.

Communication

The language is mixed. Hindi is primarily used north of here and most of the locals speak Telugu or one of a few other dialects. English is understood by most some, but apparently not by anyone who takes money–I am amazed at the sudden lack of understanding when I ask for the normal fare riding the rickshaw–suddenly English is not understood hahaha! Yeah, I guess I am a mark.

The good thing is my training cohort is here to take care of me. 20-40 Rupees per ride versus 80-160 is quite the difference! I of course can seem to get them to 80 from 160, but still!

2014-07-07 18.28.59

Rickshaw ride–lots of wrong way, incessant horn honking and generally crazy times….They call ’em Auto or Little Yellow Fellow after the color of the ride.Traffic laws are an option here. The horn rules.

Suggestion to anyone wishing to visit–get real good at timing! Crossing  the road is a treat.

 

More to come I am sure. But for now I think i will sign off with a local treat!

NAMASTE! and CHEERSKingfisher cheers!