Gregory’s Blog
Wealth Gap (Monte Carlo – Unequal Weights)
Download File (MS Excel 2010+)
This MS Excel file is a self-contained case study to teach Excel users how to create a Monte Carlo simulation while exploring how to handle the United States wealth gap. For most of you, the essential skill-building includes:
- Creating a Monte Carlo simulation when the outcomes do not have equal probabilities.
- Creating a random outcome the probability of which is dependent on the survivors of previous generation (proportions change when dealing with generations of the fruit fly, drosophila melanogaster, for example).
These 2 issues listed above grant you a distinct challenge which you might not find in many other MS Excel learning offerings.
Requirements:
- A basic understanding of dominant and recessive traits from high school biology
- A basic understanding of IF and AND functions
- A good sense of humor!
Pay Discrimination (Regression – Binary, Control Variables)
Welcome to Cask Studies, where you can properly age your skills without getting old. Even sour grapes can become fine wines here.
Pay Discrimination (Regression – Binary, Control Variables)
A fictional case study by Gregory Taketa. Non-data managers can briefly read this document to see how data analysis helps in hidden ways. Meanwhile, regression practitioners can hone using binary and control variables with a MS Excel Data Set to approach a realistic problem. Babs, an experienced office worker, believes that she and her female counterparts are victims of gross pay discrimination based on gender.
Babs: “I’m telling you, Gregory, there’s blatant gender discrimination at my workplace!”
Me: “What is your evidence of that, Babs?”
Babs: “I’ve been hearing about so many men at the office who are paid over $50,000 a year while a number of experienced women are still being paid in the $40,000s.”
Me: “I can understand that you perceive an injustice at first glance. Have you ruled out other factors for differences in pay, including education, hours worked, and certain results achieved?”
Babs: “Well, nobody gets paid higher for being more educated, since our office work does not require formal education to be excellent. Some have claimed greater experience as a justification to be paid more. We also have a scorecard filled out by the manager during our performance reviews.”
Eventually, Babs and I discuss possible key factors the management uses for pay. The manager agrees that these are meaningful factors in deciding salary, and a random sample of 30 employees (14 men & 16 women) is interviewed. The factors and some statistics are shown below:
| Variable | The Logic Behind the Variable | Men’s Average | Women’s Average |
| Salary | This is the output variable and is the pre-tax total compensation for this year. | $53,529 | $46,513 |
| Merit (Composite Performance Score) | Higher weighted-average scores suggest more valuable results achieved in the eyes of management.The score assesses performance in terms of:
|
5.52 (out of 10) | 5.16 (out of 10) |
| Hours/Week | Whether you are highly talented or almost highly talented, hard work is valued. | 42.4 | 40.3 |
| Years in This Position at the Company | Employee commitment and experience. | 5.43 | 5.31 |
| Years in That Profession | Experience and Skills overall. | 8.43 | 8.38 |
| # Raises | The more raises you were awarded, the higher your salary. Normally, the employee asks for a raise. | 2.71 | 2.06 |
Manager: “Babs, I think it’s quite clear that there is no gender discrimination here. As you can see, the men on average have been achieving better results, working harder, holding more experience, and asking for more raises. Our company’s business analysts and general counsel have confirmed this after seeing these statistics.”
Babs: “Gregory, is he right? Or did you interview the wrong people?”
Me: “The sample is fine, Babs. Although these averages are as your manager and advisors say, I have a feeling you still have a case.”
Very quickly, I demonstrated a $4,500 shortfall for each woman in the sample and gave the management a couple of easy suggestions to implement, including encouraging women to ask for more raises. Babs and her co-workers were thrilled to quickly receive a well-deserved $72,000 collectively.
Click Here to Download Data (MS Excel 2007+)
Cask Questions:
- Most data sets in real life tend to report qualitative data such as gender in text format (e.g. “Female,” “Male”). How would you change these data to work under a mathematical model such as regression?
- Many data analysts would be satisfied to have only 1 input variable for their regression. In this case, they would simply use the gender variable. Why is this a poor practice for this case?
- Although the “Merit” variable comes from subjective, ordinal data (ranked scores of 1-10), we often accept this as a necessity. What is the real problem behind the Merit score, and how could you quickly get management to mitigate that problem?
- Do any of those variables seem redundant or conflicting with each other? What can you do to make the analysis easier?
- Run a regression model using your own judgment.
- What sorts of statistical diagnostics can you use to check that your model has satisfactory support?
- What do you infer from your own findings?
- What advice would you give this company based on your findings?
My own approach is provided in the latter red tabs of the Excel file. There is more than 1 way to legitimately approach the problem, and I do not claim to have the “perfect” or “the absolutely right” method. However, we can examine ourselves and determine whether we have a satisfactory model and inferences. Did you learn anything new from my own example?
How likely would your own business analysts agree with the manager upon seeing the statistics? What new value does the data analyst provide?
Skol!
When the Outhouse Stinks Less Than Inhouse (Hiring External Analysis)
Welcome to the White Wine Papers! Please Enjoy Gregory’s Most Aromatic (Or Intoxicating) Thoughts About Business Analysis.
When the Outhouse Stinks Less Than Inhouse
The outhouse, or zero-flush facility as I call it, works wonders for your water bill but not for your nose. Most North American and European households prefer the indoor flush toilets for closer proximity and cleaner fragrance. Not surprisingly, most firms also prefer in-house business analysts for similar reasons (including taking crap).
Although external analysts or consultants have already been frequently contracted for their special skills (or for employers to avoid paying fringe benefits), I believe that as Big Data continues its avalanching growth, a conflict of interest will wedge a gaping rift between the in-house analyst and the manager.
Leading IT firm CSC forecasts, “Data production will be 44 times greater in 2020 than it was in 2009,” (csc.com) and states, “Enterprises [currently] store 80 percent” of all data (ibid).
Managers have great incentive to exploit that 80%, but their direct reports face a dilemma that is all too familiar in my experience:
“What if the data reveal that everything my boss believes is completely wrong?!” [*yes, the word “data” is plural; “datum” is singular]
It is only natural that the boss, an expert of common sense and experience, would be opposed by largely counterintuitive data (if the data were usually consistent with common sense, then investing in Big Data would frankly be a redundant waste of money).
Although more savvy managers are apt to keep an open mind, the stereotypical manager is still the victim (or rather, the perpetrator) of escalated commitment: the desire to stay on the original path in spite of overwhelmingly conflicting data. Yes, I know that doughnut is bad for my health – now let me eat it in peace!
Every in-house analyst faces at least 3 possible problems:
- “I’m not sure I conducted the analysis at a professional/research/conscious level.” Most linear regression reports I read do not account for control variables, which is heretical.
- “I’m sure I’m 100% right and thought of every possible outcome.” Not only is this arrogant and ridiculous, but most analysts with this attitude were not even in the top 20% of their class.
- “My boss believes our company will profit the most from an aggressive price-cut, but the data indicate we would profit the most from a 20% price increase while risking a 10% drop in sales volume. However, his bonus is based on volume production, and his pay will be cut if he heeds my advice.” Is there really any analyst so altruistic as to risk his/her job for the sake of the company? [Can count them on fingers…]
No matter how skillful an in-house analyst becomes, there will always be the human element, the meat that comes with the machine. The ability to either confront or persuade a manager with an opposing opinion is not necessarily innate with the position (it is a mix of immutable, inborn personality and behavioral training). Naturally, the possibility of being fired is an ever-present smother for the analysts’ fire.
External analysts will be in greater demand because they are more resistant to this dilemma. Although an irate client can cancel the fee, good consultants possess more candor because they can easily find work elsewhere (and great consultants get paid ahead of time, which quashes the client’s incentive to get mad).
Are external analysts just better people? Of course not. Their different financial situations create the difference. Many in-house analysts are jumping on the trend to go independent to give more independent advice. MBO Partners “projects that 1 in 2 American workers will either move to independent work or spend at least part of their working hours as self-employed professionals by 2020” (The Future of Work: Preparing for Independence, a Survey, MBO Partners, April 2014).
If you are a manager seeking the most unbiased input to get the best results, you might find that the outhouse will ironically contain a lot less…waste…than the in-house. Salut!
Gregory Taketa | Gregarious Consulting 