Case 6-2 Template | | | | | | | | | | | |INSTRUCTIONS: Read the case in the textbook. As a team, answer the questions in this spreadsheet, then save and submit the assignment as one Microsoft® Excel® attachment. Also, submit a 1-paragraph Microsoft®Word document explaining any issues or successes you had in answering these questions. | | | | | | | | | | | | | | | | | | | | | | | |Refer to and use the following abbreviations for the problems below: • PS = Public Subscribers • AD = Advertisers • TC = Telephone Customers • ISP = Internet Customers • TOT = Total Customers | | | | | | | | | | | | | | | | | | | | | | | |1. What percentage of the total does each of the four customer groups represent? Round to the nearest hundredth of a percent. | | | | | | | | | | | |(Show your work in this cell to solve for the percentage of the total that each of the four customer groups represent.) the group of customers are listed as follow : 70,000 publication subscribers, 58,000 advertisers, 30,000 telephone services customers, and 18,000 ISP (Internet Service Provider).
We add up all the customers group together to find the total group
TOT = PS+AD+TC+ISP ; TOT= 70,000 + 58,000+30,000+ 18,000 =176000 Therefore TOT = 176,000
To get each group percentage divide each client group by the the total client and multiply the decimal result by 100 to get the percentage rate. 70000/176000=0.39772727272 (100) = (39.77%) and 58000/176000=0.32954545454(100) =(32.95%) and 30000/176000=0.17045454545(100) =(17.05%) and 18000/176000=0.102272727(100) =(10.23%)
Therefore each group are represented as follow: PS= 39.77%, AD= 32.95%, TC=17.05%, ISP=10.23%. | | | | | | | | | | | | | | | | | | | | | | | |Use the cells below to fill in the appropriate numbers for the variables (PS, AD, TC, ISP) to check your answers. | | | | | | | | | | | | |Variables Used | |PS= |70,000 | | | | | | | | | | |AD= |58,000 | | | | | | | | | | |TC= |30,000 | | | | | | | | | | |ISP= |18,000 | | | | | | | | | | |TOT= |176,000 | | | | | | | | | | | | | | | | | | | | |Excel Formulas | |PS % of total |39.77% | | | | | | | | | | |AD % of total |32.95% | | | | | | | | | | |TC % of total |17.05% | | | | | | | | | | |ISP % of total |10.23% | | | | | | | | | | | |Use the cursor to highlight the yellow cells and look at the area above the column headings to see the background formulas. What is great about a Microsoft®Excel® reference formula is you can change any of the non-total variables and the Total and Percentage answers will change; thus, allowing many what-if scenarios. Try it. | | | | | | | | | | | | | | | | | | | |2. Minh’s data shows that on average only 4.6% of customers were purchasing complementary services available within Media Systems. By using his company’s services, Minh was projecting that these percentages would triple across all user groups within 1 year. a) How many customers would that equate to in total for each group? b) What would be the difference compared to current levels? | | | | | | | | | | | |(Show your work in this cell to solve for the total number of customers in each group and the difference compared to current levels.) | | | | | | | | | | | | | | | | | | | | | | | |Use the cells below to fill in the appropriate numbers for the variables (PS, AD, TC, ISP, current purchasing %, projected multiple factor, and % difference) to check your answers. | | | | | | | | | | | | |Variables Used | |PS= |1 | | | | | | | | | | |AD= |1 | | | | | | | | | | |TC= |1 | | | | | | | | | | |ISP= |1 | | | | | | | | | | |Current Purchasing % |1.00% | | | | | | | | | | |Projected multiple factor |1.00 | | | | | | | | | | |% Difference |1.00% | | | | | | | | | | | | | | | | | | | | |Excel Formulas | | |Part A |Part B | | | | | | | | | |PS= |$0.01 |$0.01 | | | | | | | | | |AD= |$0.01 |$0.01 | | | | | | | | | |TC= |$0.01 |$0.01 | | | | | | | | | |ISP= |$0.01 |$0.01 | | | | | | | | | | |Use the cursor to highlight the yellow cells and look at the area above the column headings to see the background formulas. What is great about a Microsoft®Excel® reference formula is you can change any of the variables and the cells with formulas will change; thus, allowing many what-if scenarios. Try it. | | | | | | | | | | | | | | | | | | | |3. Customer complaint data showed that within the last year, complaints by category were as follows: publication subscribers, 1,174; advertisers, 423; telephone service customers, 4,411; and ISP customers 823. a) What percentage of customers (round to two decimal places)complained within the last year in each category? b) If the CRM software were able to reduce complaints by 50% each year over the next 2 years, how many complaints would there be by category at the end of that time period? And c) What would the number of complaints at the end of 2 years represent on a percentage basis? | | | | | | | | | | | |(Use this cell to answer parts a, b, and c. Be sure to show your work.)
A.You take the total number of compliants and divide that by the total number of customers. Once you have the decimal you multiply that number by 100 to find the percentage of customers that complained within the last year in each category. PS = 1174/70000 = .01677143 x 100 = 1.68%; AD = 423/58000 = .0072931 x 100 = 0.73%; TC = 4411/30000 = .14703333 x 100 = 14.7%; ISP = 823/18000 = .04572222 = 4.57%. B. Take the total number of compliants for each of the groups and multiply that number by the 50% reduction factor twice to solve for the number of compliants over the next two years. PS = 1174 x .50 x .50 = 293.50; AD = 423 x .50 x .50 = 105.75; TC = 4411 x .50 x .50 = 1102.75; ISP = 823 x .50 x .50 = 205.75. C. To solve for the number of compliants at the end of 2 years based on percentage take the number of complaints at the end of the second year for part (B) and divide that by the total number of customers for each category. Once you have the anwser convert the decimal into a percentage by mulitplying it by 100. PS = 293.50/70000 = .00419286 x 100 = 0.42%; AD = 105.75/58000 = .00182328 x 100 = 0.18%; TC = 1102.75/30000.03675833 x 100 = 3.68%; ISP = 205.75/18000 = .01143056 x 100 = 1.14%. | | | | | | | | | | | | | | | | | | | | | | | |Now use the cells below to fill in the appropriate numbers for the variables to check your answers. | | | | | | | | | | | | |Variables Used | | |Complaints |Customers | | | | | | | | | |PS= |1,174 |70,000 | | | | | | | | | |AD= |423 |58,000 | | | | | | | | | |TC= |4,411 |30,000 | | | | | | | | | |ISP= |823 |18,000 | | | | | | | | | |TOT= |6,831 |176,000 | | | | | | | | | | | | | | | | | | | | | |Complaint reduction Factor |50% | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |Excel Formulas | | |Part A |Part B |Part C | | | | | | | | |PS= |1.68% |$293.50 |0.42% | | | | | | | | |AD= |0.73% |$105.75 |0.18% | | | | | | | | |TC= |14.70% |$1,102.75 |3.68% | | | | | | | | |ISP= |4.57% |$205.75 |1.14% | | | | | | | | | |Use the cursor to highlight the yellow cells and look at the area above the column headings to see the background formulas. What is great about a Microsoft®Excel® reference formula is you can change any of the variables and the cells with formulas will change; thus, allowing many what-if scenarios. Try it. | | | | | | | |