個案總覽

In 2017, a sports journalist based in Houston, Texas was confused about his editor’s latest idea. His editor had emailed him the night before proposing an article on the best (and worst) Major League Baseball hitters of all time. The first step was to provide a list of the players that would be profiled. This meant finding the 10 best (and worst) batters. However, after ranking the players, could he quantify the probability that he ranked them incorrectly? Because the article was based on data, he had to justify his choices and he wanted his decisions to be based on sound statistical grounds.

In mid-2017, the regional head of Operations and Supply Chain at Sika AG, a Swiss specialty chemicals leader that developed solutions for building and automotive industries, faced a new challenge in the company’s epoxy flooring supply chain. She was tasked with identifying a robust solution that would both satisfy a shortened lead time and provide spare capacity to accommodate future growth. How should she proceed with these tasks?

Instructor Spreadsheet to accompany product 8B18E008.

Student Spreadsheet to accompany product 9B18E008.

Instructor Spreadsheet to accompany product 8B18E005.

Student spreadsheet for product 9B18E005.

In November 2017, a fan of hockey watched his favourite team lose 6–4 against a contending team. After a great start to the season with a record of 6 wins and 1 loss, his favourite team had lost 6 of the last 8 games, dropping their record to 8 wins and 7 losses. The fan knew that the time was approaching for the inevitable discussions with his friends regarding the team's success. Which players should they have signed? Which player should the team trade for? What line combinations would serve the team best? Who was overvalued, and who was undervalued? Knowing that conversations among his friends typically took on a random and unstructured format, the fan planned to provide a more analytical approach to the discussion this year. His research would raise the level of the conversation and help back up his suggestions.

Ecotricity Group Ltd., founded in 1995, had grown to be a major renewable energy utility service provider in the United Kingdom. In 2016, the company wanted to expand its "electric highway"—a network of charging stations for electric vehicles along major U.K. motorways. The expansion plans involved two chargers: Type A chargers, installed in public locations along the motorway, and Type B chargers, installed in suitable home garages owned by Ecotricity customers. To meet the projected traffic flow of electric vehicles in the future, the company needed to optimally expand the number of both types of charging points in each highway zone.<br><br>This case uses integer programming to determine the optimal number of Type A and Type B chargers that Ecotricity should install to expand its electric highway. The solution is obtained by using Microsoft Excel Solver and interpreting the results. The case also provides a platform for discussing optimal investment strategies, and for considering another topic not emphasized extensively in the case—how an operational strategy affects image building.

Spreadsheet to accompany product 8B18E006.

Instructor Spreadsheet to accompany product 8B18E001.

Student Spreadsheet to accompany product 9B18E001.

A key contributor to the business reputation and success of Caterpillar Inc. (Caterpillar) was the company’s reliable aftermarket service. Caterpillar’s Asia Distribution Centre in Singapore processed urgent spare parts orders received from dealers located in 10 Southeast Asian countries. In 2017, the operations team was working to develop a rigorous analytical method for making daily decisions about how to ship the spare parts. Shipments were consolidated by country destination and needed to optimize the interests of three stakeholders: (1) Caterpillar, which wanted to minimize freight costs; (2) dealers, who wanted to receive the spare parts in the shortest time possible; and (3) freight forwarders, who wanted to be rewarded for their on-time delivery performance.

In April 2016, the chairman of the partnership charged with building the $1 billion Highway 407 East Extension Phase 2 in Ontario was reviewing an e-mail from the procurement team that recommended allocation of more than $45 million in contracts for the purchase of granular base material for the highway construction. He knew that the procurement team had worked for several weeks to negotiate and analyze the supplier’s quotes, but after reviewing the recommendation, the chairman wondered whether the team’s recommendation captured the optimal supplier mix and the most cost-effective selection of suppliers. It was certainly a good solution, but was it the optimal solution? Even with an experienced team, this was a difficult optimization problem to solve.

In April 2016, the owner of Apoorva Mess (Apoorva) in Manipal, India, wanted to open a full-service economical restaurant to reach out to customers, beat the competition, improve his sales, and fulfil his dream of serving quality food at affordable rates to the lower middle class. He was facing a dilemma over selecting the best location for establishing his new restaurant, knowing that the wrong choice would spell disaster for his business in the long run. A number of conflicting constraints were making it difficult for him to come to a decision. He wanted to follow a systematic approach to pinpoint the final location.

In October 2010, Ira Pittelman, lead producer of the Broadway musical American Idiot, had to evaluate one week's grosses from when star musician, Billie Joe Armstrong, was put into the show in New York City. The rock star had been enthusiastic about filling in for an actor in the musical based on an album by his band, Green Day. Audiences had also been enthusiastic, and revenues for the play were up. The producer had to determine whether Armstrong was the source of the increased revenue and, if so, how much his performance had contributed. He needed to determine how to respond should Armstrong offer to act in the musical again. Should he accept such an offer? How much compensation should he offer the star?

Instructor Spreadsheet for product 8B17E012.

Instructor Spreadsheet to accompany product 8B17E013.

Spreadsheet to accompany product 9B17E012.

Student Spreadsheet to accompany product 9B17E013.

Survival analysis refers to a collection of statistical techniques used to analyze the occurrence and expected timing of events. Survival analysis can provide not only the probability of an event of interest occurring, but also the time at which the event is likely to occur. A particularly useful application of survival analysis is in the area of "customer churn"—the loss of clients or customers—which is a major concern in many industries. This note introduces survival analysis and discusses two approaches to examine customer churn: the Kaplan-Meier estimator and the Cox proportional hazards model. Instructions are provided on how to build these models in Microsoft Excel, without add-ins.