Faculty of Computing

Faculty of Computing,

Engineering and Technology

Tutorial Task 1

Customer Relationship Management

Academic Year, 2008/9

Tutorial Task 1– Customer Services Management:

Finding out about customers

Aim:

To test Pareto’s Rule which states

In general it can be said that

Ø  20% of customers account for 80% of your service and supply problems

Ø  20% of customers account for 80% of your turnover

Ø  20% of customers account for 80% of your profits

Task:

Pareto’s rule holds true for many organisations. The first point will be hard to prove with the data we have. However, using pivot tables we can look at the other parts

1.  Create a new column in the spreadsheet to hold the profit. Calculate the profit and insert the value into each row (=K3-L3)

2.  Create a pivot table (select Data from the main menu and then Pivot table and Pivot chart from the drop down).

3.  Select all the data on the sheet including row 2 with the titles (Sheet1!$A$2:$L$346) when asked what data you want to use

4.  Choose the layout option

a.  Drag the Cust No field into the column area

b.  Drag the Price rrp field and Profit field into the data area

(this should sum up the revenue (turnover) and profit per customer)

c.  Drag the Number field into the data area

i.  Double click and select the count function

(this should count the number of orders per customer)

5. Insert the Pivot table into a new sheet (this just makes it easier to deal with!)

a.  Above the pivot table number the first 20 columns (there are 100 customers so this will be 20%).

b.  Select all the data (the four rows) and sort the row containing Price rrp in descending order

c.  Sum the first 20 columns

d.  Find this value as a percentage of the total (given at the end of the pivot table).

6. Repeat section 5 for the Profit

Are the customers who produce the most revenue the same as those who produce the most profit?

Order the customers in terms of numbers of orders placed.

7. What can you say about Pareto’s law?

8. Fill out the following table

9. Which are the five most promising customers in your opinion and why?

10. What CRM techniques would you use to increase your revenue, profits and turnover and why?

1