Excel Advanced Features(draft)
Tejas: Our teacher said that the local Kirana shop owner wanted some help with his business and asked us to become his consultants!
Moz: What kind of help does he need?
Jyoti: He enters a lot of sales and inventory data in notebooks. He now wants to analyse /interpret the data and take some informed decisions.
Tejas: He also stores the customer data, but now wants to see how he can use this to provide personalised services to his customers. This can be done easily if customer preferences are stored.
Moz: OK. It looks like we can use that data to learn new features of EXCEL and in the process, also help the shop keeper. Why don’t you go and collect the information about the customers/items in the shop and put them under categories. [CONNECT: CATEGORISATION]
Jyoti: We can also draw some charts to show him about which items are moving fast and which brands are preferred, this sounds exciting!
Tejas: Here is the list of frequent customers. It will be good to sort it alphabetically [ CONNECT to LISTS AND TABLES ALHABETICALLY SORT]
Customer NameYasmin
Tabish
Bhairav
Aruna
Usha
Savani
Prashant
Rekha
Moz: Yes, in spreadsheets you can sort a column of both textual and numerical data.
Concept of Sorting []
Skill of sorting [ select column on which you want to sort, ……]
Customer NameAruna
Bhairav
Prashant
Rekha
Savani
Tabish
Usha
Yasmin
Tejas: We also have data on the monthly expenditure of these customers.The shop keeperwants to find out the top 5 customers so that he can give them a 8% discount.
Moz: If the list is small, we can do it manually. But for huge data the spreadsheets sort feature can be used. You can now sort on the second column to see the top 5 customers.
Jyoti: But if the first column remains unchanged , then the amounts do not match the customers !
Customer Name / Monthly bill (Rs)Aruna / 2200
Bhairav / 3000
Prashant / 3510
Rekha / 4000
Savani / 4500
Tabish / 5000
Usha / 5500
Yasmin / 8000
Customer Name / Monthly bill (Rs)
Aruna / 8000
Bhairav / 5500
Prashant / 5000
Rekha / 4500
Savani / 4000
Tabish / 3510
Usha / 3000
Yasmin / 2200
Moz: Good observation. You cannot select and sort on a single column when you have multiple columns of data. You have to select the entire table.
Skill of sorting on column( select table, click sort, selct sort by column name, select ascending descending )
Customer Name / Monthly bill (Rs)Yasmin / 8000
Usha / 5500
Tabish / 5000
Savani / 4500
Rekha / 4000
Prashant / 3510
Bhairav / 3000
Aruna / 2200
Jyoti: Let us compute the discount now for top 5 customers by adding two columns called discount amount and Final Bill.We also know how to use formula [ CONNECT AVE FORMULA USED EARLIER]
Recall Skill box to show how formula : = 8*cell number/100 is used for calculating discount and Final amount by subtracting the second column values from third column values.=8*F7/100, =F7-G7
Customer Name / Monthly bill (Rs) / Discount amount / Final billYasmin / 8000 / 640 / 7360
Usha / 5500 / 440 / 5060
Tabish / 5000 / 400 / 4600
Savani / 4500 / 360 / 4140
Rekha / 4000 / 320 / 3680
Prashant / 3510 / 0 / 3510
Bhairav / 3000 / 0 / 3000
Aruna / 2200 / 0 / 2200
Moz: Suppose the shop keeper decides to change the discount percentage, all you have to do is change the formula in the cell number: G7 and all other new values are automatically calculated.
Tejas: We had earlier used the Sum icon to compute the sum , is there any other way of doing it?
Moz: Yes there are built in Functions in Spreadsheets like SUM,AVE,MAX,MIN, ………
=SUM(F7:F14)skill box
------
Chocolate sale data for 3 monthsMonth / Brand Name
B1 / B2 / B3
M1 / 11000 / 5000 / 7000
M2 / 13000 / 3000 / 7200
M3 / 15000 / 500 / 7500
Moz: Notice that the B1 is growing every month, B2 sales are going down and B3 is almost constant from M1 to M3. So the shop keeper can discontinue to stock B2 brand of chocolates . See the following data and tell me what is your conclusion?
Ice cream and cool drinks sale data in amonthVariety / Stock / Sold / Remaining / Percentage sold
Family Pack icecream / 75 / 15 / 60 / 20
Small pack icecream(cups+cones+sticks) / 250 / 225 / 25 / 90
Cool drinks (small bottles) / 350 / 340 / 10 / 97
Cool drinks (Large) / 150 / 85 / 65 / 57
Jyoti: The percentage sold for Family packs and large cool drinks is less whereas Small pack icecreams and small bottles of cooldrinks are being sold very quickly!
Moz: Good, I see the consultants are able to reda and interpret data from a graph . This information can help him to decide which item scan be stocked more and for which items the stock can be reduced.
[ You already have some advice about which customers should be given discounts, about stocking , about which brands of chocolates to discontinue ]
Tejas: One more critical decision has to be taken. The shop keeper has either walk-in customers or home delivery customers. He has many phone orders, and when he informs the customer that it will take time, he might end up losing them. So, he has to decide whether hiring more delivery boys will improve his business.
Moz: This is an interesting problem. How do you prose to solve it?
------
Jyoti: The categories and sales for the month are given . We calculated the profit percentage for each category. As Stationery and cereals are not very profitable , these categories can be stopped and more investment can be done for the high return categories .
Category / Sale / ProfitRice,dal,flour / 400000 / 80000
Cosmetics / 300000 / 15000
Soaps / 100000 / 5000
Cereals / 50000 / 1500
Stationery / 10000 / 100
Snacks / 200000 / 50000
Category / Sale / Profit / Profit %
Rice,dal,flour / 400000 / 80000 / 20
Cosmetics / 300000 / 15000 / 5
Soaps / 100000 / 5000 / 5
Cereals / 50000 / 1500 / 3
Stationery / 10000 / 100 / 1
Snacks / 200000 / 50000 / 25