Executive MBA

MBUC 862

Management Analytics

____________________________________________________________________________________

Assignment One

Modelling, Probability and the Normal Distribution

Redwing Computer Sales is a small company located in Kingston, Ontario. The founder of the company, Jonathan Baldwin, began the business by selling computer systems through mail-order at discount prices. Redwing was one of the first computer mail-order companies to offer a toll-free phone number to their customers for support and trouble-shooting. While the company has grown over time, many new competitors have entered the computer mail-order marketplace so that Redwing's share of this market has actually declined.

Five years ago, Bob Gravenstein, a marketing consultant, was contracted by Redwing to develop long-term marketing plans and strategies for the company. After careful study, he recommended that Redwing branch out to reach a new and growing segment of the computer sales market: college students. At that time, most universities were providing micro-computer labs and facilities for their students. However, many students were purchasing their own computers and printers so that they could have access to a computer at any time in their apartments or dorms. After graduation, students take the computers, with which they are already familiar, to their new jobs or use them at home after work hours. The percentage of college students owning a computer was increasing rapidly and Bob Gravenstein recommended that Redwing Computer Sales take advantage of this marketing opportunity.

The marketing plan developed by Bob Gravenstein worked as follows: Five universities were initially selected for the program with expansion to other universities planned over time. Any student in at least his or her sophomore year at one of these universities was eligible to purchase a discounted JCN-2001 microcomputer system with printer from Redwing Computer Sales under the program. The JCN-2001 is a private label, fully compatible system with all of the features of brand name models. The student makes a small payment each semester at the same time regular tuition payments were due. When the student graduates and finds a job, the payments are increased so that the computer will be paid for within two years after graduation. If the student fails to make payments at any time, Redwing could repossess the computer system.

The prospect of future sales of computer equipment to these students was also part of the marketing strategy. The JCN-2001 is an entry-level computer system that suffices for most academic and professional uses. Eventually, however, many students who purchased this system would outgrow it and require an upgrade to a more powerful machine with more features. Bob Gravenstein argued that after their good experience with the JCN-2001, these customers would make their future purchases from Redwing Computer Company.

Today, five years later, Redwing Computer Sales is still operating the student purchase program and has expanded it to several more universities. There are currently enough data available from the early days of the program for Redwing to determine whether or not the program has been successful and if it should be continued. To discuss the future of the student purchase program, Jon Baldwin has called a meeting with Ben Davis, who has been in charge of the program since it began, and Teresa Grimes, the new vice-president of marketing.

Baldwin:"I called you both in here today to discuss the student purchase program. As you know, the program has been in place for approximately five years and we need to make decisions about where to go from here. Ben, weren't you telling me last week that we now have enough data to evaluate the program'?"

Davis:"Yes, sir. Any student who began the program as a sophomore five years ago should be out of school and employed for at least two years."

Baldwin:"Well, based on your information, would you say the program has been a success or a failure?'

Davis:"That's a tough call, sir. While most of the participants in the program eventually pay their account in full, we have had a high rate of defaults, some while the students were still in school, but mostly after they graduated."

Baldwin:"How much are we losing on those who default?"

Davis:"Each case is different. As I said, some default early and others after they have graduated. There are also the costs of repossession and repair to bring the product back up to resale quality. In many instances we were not able to retrieve the computer systems. Our data suggest that our average loss on each student-customer who defaults is about $1,200. On the other hand, our average profit from participants who pay their accounts in full is approximately $750. Overall, we are close to just breaking even."

Grimes:"Ben, have you considered 'qualifying' students for the program, much like a loan officer would qualify someone for a loan?"

Davis:"We had initially thought about doing that, but we didn't believe that there was much information, if any, in the way of a credit history on most college students. Applicants are still requested to provide as much information as possible on their application, including their class, grade point average, work experience, scholarships, and how much of their college expenses were earned through work. However, we weren't sure that this information was particularly useful for screening applicants."

Grimes:"Knowing that we were going to have this discussion today, I had one of my assistants who is well-versed in statistics look over some of those data last week. She has come up with a 'screening test' based only on the information you have been collecting from student applicants. By being more selective about whom we allow to participate in the student purchase program, it may be possible to increase our profit from the program."

Davis:"It would be easy enough to check out her screening test by trying it out on our early data from the program. In those cases, we know whether or not the student actually defaulted."

Baldwin:"Why don't the two of you spend some time on this idea and get back to me next week? At that time I want a recommendation to either discontinue the program, continue it as is, or continue it using this 'screening test' idea. Make sure you have the evidence to back up your recommendation."

Assignment

Ben Davis and Teresa Grimes must analyze the data from the student purchase program and make a recommendation to Mr. Baldwin about the future of the program. The necessary data are contained in the file REDWING.XLSX. The comments in this file describe the data on each student.

Using this data set and other information given in the case, help Ben Davis and Teresa Grimes evaluate the student purchase program and recommend any necessary changes in the program. In particular, you need to evaluate the profitability of the program as it is currently administered and to evaluate the usefulness of the screening test developed by the assistant to Teresa Grimes.

Issues to consider:

What is the probability of a student defaulting?

What is the average profit per student currently?

Does it appear that there is a difference among the schools?Hint: what do the confidence intervals tell you about the difference in average values between each pair of schools? When confidence intervals of this type do not overlap, it is usually safe to conclude that there is a statistically significant difference.

How can you use the screening test to improve the company's position?

You might find it handy to consider the Excel functions =SUMIF and =COUNTIF. The following illustration may help.The syntaxes for the two commands are:

=SUMIF(range to check for criterion, criterion to satisfy, range to sum if criterion is met)

=COUNTIF(range to check, criterion to satisfy).

Note that in the example, the =SUMIF command counts the number of defaults in column B, over the range B3:B8, for those students in School 1.

The =COUNTIF function counts the number of students in column A, in the range A3:A8, who belong to School 1.

Deliverable: Provide a written report addressed to Jon Baldwin that addresses all of the questions raised above including a clear recommendation regarding the program.In addition to the report, submit an excel spreadsheet file with your analysis that you refer to in the report.