52 Application Cases in MIS CASE 3: Heavenly Herbal Tea Chariton's Coffee and Tea Company has produced coffees and teas distributed primarily through supermarkets throughout the eastern United States for 45 years. Charlton's has a sales staff of 36 salespersons assigned to one of four regional sales managers. The regional managers in turn report to Sandra Charlton the Vice President for Marketing. The sales staff at Charlton's has always been assigned on a purely geographic basis with each salesperson selling all product lines to customers within her or his territory. Five years ago Charlton's introduced a new product line, called Heavenly Herbal Teas (H2T). These teas contain herbs designed to stimulate memory. This new product line has proven to be quite successful, with sales reaching 18% of total company sales. However, Sandra believes that sales growth in this new product line has been hampered by the structure of Charlton's sales staff. Sales in the new product line have been strongest among drug and health food stores and not traditional supermarkets. Some members of the sales staff have been quick to recognize the need to seek out this new type of customer, while others have not. Sandra feels that the set of customers for the H2T product line is sufficiently different to require a separate sales staff. She proposes to establish an initial sales force of four salespersons, one for each of Charlton's sales regions. Each salesperson would then be responsible for selling the H2T line throughout his or her entire region. Sandra feels that the current sales territories need to be restructured and that some territories can be consolidated. Sandra has received approval from the CEO to make this move providing that she does not increase the total sales staff, that the salespersons for the new team are selected entirely from the existing sales staff, and that no salesperson is transferred to a different region. Salespersons who are considered "good" candidates for the new sales team will be invited to apply. Sandra has asked you to prepare a spreadsheet for her that will identify these "good" candidates. She feels that two characteristics are particularly important. First, the H2T sales staff will have to seek out many new customers and thus the salespersons selected should have a history of seeking out new customers. Second, andSpreadsheet Cases 53 most importantly, H2T salespersons should be individuals who have been enthusiastic about selling this new line. Thus, the salespersons selected should have a strong track record in sales of H2Ts. Sandra also feels that the sales quota should be considered when evaluating the performance of the sales staff. Quotas are maintained for each sales territory, and they are felt to be good measures of the sales potentials of different territories. She asks you to consider sales for the last two reporting years in your analysis. Sandra also adds that she would like you to include a table or chart that will help her to see how much variability there is across the sales staff of each region in these two target elements. She says she wants something that will "let me know it there is enough variation in sales to new customers and sales of H2Ts to justify using them as criteria to find the sales people I need." The information systems department was able to extract summary data from organizational databases and place it in spreadsheet format for you. This data is available on the web site for this casebook. The first 9 rows of this spreadsheet are reproduced below. For each salesperson three rows of data have been extracted, one for each year. In addition to the YEAR, the following items have been retrieved. S_NAME - the salesperson's name, REGION - the region to which the salesperson is assigned, QUOTA - the sales quota, NCUST -sales to new customers, and H2TS - sales of the H2T product line. Application Development Notes A sample layout form for this application is shown below. Two work sections, or worksheet pages, and a graph area are needed for this spreadsheet file. The input data should look like the sample data shown and this portion of the spreadsheet has been created for you. The second section of the application is a worksheet for the sales performance report that you are to create. The data used for this report is to come from the sum of sales from 2002 through 2004. Thus, the values for cells displaying the dollar sales amounts must be found by adding up the cells containing the 2002, 2003 and 2004 amounts. There are multiple criteria to be used in evaluating sales performance. Thus both the rate of Sales to New Customers and the Rate of Sales of H2Ts are presented. A last column called High Sales is added which is designed to identify salespersons that have shown strong performance on both criteria. Salespersons whose percentage sales in54 Application Cases in MIS both of the target categories are more than 30 percent above average are to be identified as having high sales. Finally, in order to highlight top candidates, the data area for salespersons in each region is sorted from high to low based on Sales of H2Ts as a percentage of Sales Quota, since this is considered the most important target variable. The graph is based on the data in the Sales performance reporting area and shows both sales to new customers and sales of H2Ts as a percentage of each salesperson's sales quota. Assignment 1. Using the input data and layout forms provided, develop a spreadsheet application to provide Sandra Charlton with information highlighting those salespersons who are good candidates to join the new sales team. Be sure to test your application for accuracy and completeness. Add a documentation section to your spreadsheet to make it as self-documenting as possible. 2. Write a memorandum to Sandra Smith describing the results of your application. Incorporate a copy of your Sales Performance Report in your memorandum. Make preliminary recommendations to Ms. Charlton based upon this report. Also suggest to her any limitations you see in the analysis and any other factors that you would suggest she look at. 3. Prepare a set of business presentation materials for an oral presentation to Ms. Charlton of the information described in 2 above