Tugasan Excel

Dear all

Below is your Excel assignment:

You are asked by your father to organize the inventory data of his sundry shop. Use these tables and follow the instructions.

Table 1  Items on Hand

Item Number	Description	Inventory	Price(RM)
B60338	Nescafe 3-in-1	619	14.25
M44910	Milo 500g	873	9.50
C71610	Dumex 500g	579	17.60
S80787	Beras cap ketupat 10kg	537	22.50
T36275	Planta 1kg	764	12.45
T74695	Kinder Bueno 3x43g	208	8.30
W59366	Quaker Oat cookies 280g	268	7.95
C248890	Nutella 375g	385	13.80
C87343	Mineral water 1.5l x 15	387	14.05
W15840	CintanMeeGorengPedas 2packs	699	6.85
C49955	Marigold 100% Fruit Juice	237	18.25

           

Table 2  Priority Codes

Inventory	Priority
0	1
250	2
400	3
600	4
800	5

1.      Create an inventory table from the data in Table 1.  Name this table as Table 3

a.       Add an Amount field and a Priority field. Both are calculated columns. Inventory Amount equals Inventory times Price.

b.      Find the Total Amount, Average, Minimum and Maximum Amount, each in separate rows below Table 3

c.       Calculate the percentage of Inventory Amount of each item in a new table (Table 4)

d.      Create a Priority Code table in the range U1:J6 using the data shown in Table 2.

e.       Use the VLOOKUP function to determine the priority to assign each record.

2.      Copy the table into 3 and sort each table by the following

a.        in ascending sequence by inventory

b.      by amount (ascending) within priority code (descending)

c.       in descending sequence by priority code

3.      Execute the following queries

a.       Number of items with Priority code equals to 2

b.      Inventory  greater than 250 and less than 600

c.       Sum of Inventory Amount greater than RM2000

4.      Create a suitable chart that shows the item description against the Inventory Amount.

The due date for this assignment is March 29, 2012.  Upload your assignment on your blog. Late submissions will not be entertained.