1. Prepare the following worksheet and save it "BALANCE.XLSX".

Purchase Profit
SNO SNAME Sale Amount
Amount Amount
1 LCD MONITOR 120000 150000
2 FLOPPY DISK 600 700
3 COMPACT DISK 800 900
4 DVD WRITER 70000 80000
5 FLASH MEMORY 2GB 30000 34000
6 MOUSE 5000 5500
7 CRT MONITOR 80000 85000
8 MEMORY CARD 30000 32000
9 VGA CARD 40000 48000
10 CD WRITER 60000 65000
TOTAL
MAXMIMUM
MINIMUM
AVERAGE

2. Prepare the following worksheet is printing effect and save the file name it
“PAYROLL.XLSX".

Bonus = 15% of Basic Pay.

Gross Pay = Basic Pay + Bonus.
Income Tax = 4.5% of Gross Pay.
Net Salary = Gross Pay – Income Tax.
Total no of managers
Payroll Detail Report for Song of Flower Co., Ltd.

Basic Gross Income Net

Name Post Bonus
Pay Pay Tax Salary
U Kyaw Kyaw Manager 500,000
Daw Su Su Win Asst: Manager 300,000
Daw Lay Lay Myint Supervisor 200,000
U Thura Aung Manager 500,000
U Khun Naung Sr. Manager 700,000
Daw Soe Soe Accountant 300,000
Daw Mya Thu Moe Receptionist 150,000
Daw Than Than Sanitation 100,000

Pages ( 1 of 8)
3. Prepare the following worksheet is printing effect and save the file name it “FOOD.XLSX".

Sale Tax = 5% of Total Cost

Service Charge = 5% of Total Cost
Discount = 5% of Total Cost
Total Cash = (Total Cost + Sale Tax + Service Charge) - Discount

Bill List for M3 Food Center

Item Item
Quantity Price Amount
No Name
1 Green Tea 1 1000 K
2 Dim Sum 20 650 K
3 Coffee (Fresh) 3 500 K
4 Fried Rice 2 2000 K
5 Rice Soup 1 1500 K
Total Cost
Sale Tax
Service Charge
Discount
Total Cash

4. Prepare the following worksheet is printing effect and save the file name it “CITY
MART.XLSX".
(a) Calculate must be percentage is according to the sale amount total.
(b) If percentage is greater than 15% Then print Bonus is Sale Amount of 10% Else Sale Amount
of 5% .

City Mart Super Market

Sale
Stock
No Amount Percentage Bonus
Name

1 Stationery 50000 xxxx%

2 Foods and Snacks 60000

3 Shoes & Coats 40000
4 Medicine 30000
5 Fruits & Vegetables 80000
Total xxxx xxxx%

Pages ( 2 of 8)
 5. Prepare the following worksheet is printing effect and save the file name is "RESULT.XLSX".
(a) Calculate Total Marks.
(b) If Total Marks is greater than 230 Then print "CREDIT" Else "-"

Examination Result For CBC Level I

MS Total
Roll Student MS
MS Excel Power Marks Grade
No Name Word
Point
1 Wai Yan Mg 89 60 80
2 Zin Mar Htwe 40 52 50
3 Sai Aung Naing 100 89 60
4 Myo Thein Thu 96 82 70
5 The` Mon Nwe 50 65 40
6 Myat Mon 70 68 76
7 Thae Thae 60 83 85
8 Thuya 65 54 84
9 Thin Thin 80 70 90
10 Khin Thet Maw 90 75 100

6. Prepare the following worksheet is printing effect and save the file name is "
VEHICLE.XLS".
(a) Heading is Arial Narrow font and 12pts.
(b) Find the Value is Price of Quantity.
(c) If Value greater than 400000 Then Discount = 10% of Price Else Discount = 3%
of Price.
(d) Item Price and Value column is punctuate format with 3 decimal.

Vehicle Vehicle Arrival

Price(¥) Quantity Value Discount
No Code Date
1 VEHI-1 10/2/2005 ¥100,000 5
2 VEHI-2 1/5/2004 ¥210,000 2
3 VEHI-3 4/4/2003 ¥150,000 4
4 VEHI-4 5/1/2005 ¥500,000 10
5 VEHI-5 1/2/2005 ¥750,000 5
Total

Pages ( 3 of 8)
 7. Prepare the following worksheet is printing effect and save the file
name is " Customer.XLS".
(a) Heading is Tahoma font and 12pts.
(b) Find the Balance is Credit Limit - Credit Amount.
(c) If Balance less than 25000 Then Bonus = 15% of Balance Else Bonus=30% of
Balance.

Custom Custome
Organisation Credit Credit Profit
er r Balance
Name Limit Amount Bonus
No Name
1 U Than Win ABC CompanyLtd. $ 100,000.00 $ 75,000.00
2 U Khin Win Shwe Thazin Ltd. $ 500,000.00 $ 60,000.00
3 U Mg Mg Mingalar Co.Ltd. $ 140,000.00 $ 120,000.00
4 U Khin Mg Shwe Gabar Ltd. $ 250,000.00 $ 210,000.00
5 U Thiha AAA Co., Ltd. $ 150,000.00 $ 65,000.00

8. Prepare the following worksheet is printing effect and save it "Examination.XLSX"

Examination Result for CYC Section Result

Student Software Cred
Name Pai Programmi Wor Exc P- it Pass
No Result
nt ng d el Point Grad Grade
e
1 Su Su Mon 90 100 90 85 98

2 Nay Lin Myo 35 94 55 69 65

3 Htang Htin 70 56 78 52 65

4 Ei Wai Phyo 25 35 69 60 69

5 Kay Thwe Oo 67 65 48 45 45

6 Aung Thuya 80 70 90 80 90

7 Soe Soe 78 80 78 71 80

8 Moe Moe San 76 70 54 75 70

9 Swa Taw 90 87 50 67 65

10 Sein Sein 56 60 80 80 77

Pages ( 4 of 8)
1.. Draw a Line graph no-1 to show the List of Students by Courses with title X, Y – axis title and Legend is
bottom of the graph. Save the with "COURSES.XLS"

COURSES MARCH JUNE SEPTEMBER DECEMBER

CYC Level I 100 75 85 95
CBC Level I 30 35 40 50
CAC Level I 50 25 45 50
CBC Level II 70 80 80 90
CAC Level II 55 55 45 56

2. Draw a Bar graph no - 2 to show the List of Fruits by Day with title X, Y – axis title and Legend is Right of
the graph. Save the with "FRUIT.XLS"

FRUITS NAME MON TUE WED THURS FRI SAT SUN

Coconut 50 100 75 58 35 78 69
Palm 100 150 90 569 897 600 500
Pine Apple 50 45 98 20 46 80 65
Papaya 56 78 80 65 73 98 63
Jack Fruit 35 85 65 45 78 96 58

3. Draw a Area graph to show the Qualification by Year with title and X, Y – axis title. Save the graph file
name it "QUALIFICATION.XLS"
Student's Qualification Status
QUALIFICATION 2002 2003 2004 2005
Under Grad: 100 115 125 250
Graduate: 90 95 100 250
Post Grad: 25 35 50 150
Certificate: 20 30 50 100
Honours 300 255 200 250

4. Draw a Pie graph no - 2 to show the Students information of courses for the month of July with title, show
++
label with X point label, Exployed ( Visual Basic & C & C ) and save the file name it "COURSE.XLS"
Student Information of Courses
COURSES JULY
ICT Foundation 300
Win XP 300
C & C ++ 150
Visual Basic 100
Visual Basic.Net 200
Java Programming 180

5. Draw a Column graph no - 7 to show the Sale Income by Super Market with title. X axis title is Supermarket, Y axis title
is Months and Z axis title is Income. Save the file name it "SUPERMARKET.XLS"

SUPER MARKET JANUARY FEBRUARY MARCH APRIL

119 Super Market 5000000 4521000 5120000 700000
La Pyae Won Super Market 5500000 8000000 6000000 900000
Sein Kaung Super Market 4000000 2250000 3500000 300000
City Mark Super Market 6000000 5500000 7360000 795600
YanKin Center Super Market 7500000 8230000 7856000 963100

Question- 1
Draw a Pivort Table to show the List of Fruits by Day the graph. Save the with "FRUIT.XLS"

Pages ( 5 of 8)
FRUITS MON TUE WED THURS FRI SAT SUN
NAME
Coconut 50 100 75 58 35 78 69
Palm 100 150 90 569 897 600 500
Pine 50 45 98 20 46 80 65
Apple
Papaya 56 78 80 65 73 98 63
Jack Fruit 35 85 65 45 78 96 58

Pages ( 6 of 8)
Data Management Exercises
.
STAFF TABLE

SNO SNAME POST DEPT BPAY ICR DED

1 U Tun Tun P Bot 9000 300 90
2 U Thein AP Eng 8000 300 80
3 Daw Mon Mon AP Phy 8000 300 80
4 Daw Lei Lei L Chem 7000 200 70
5 U Zaw Myint L Eng 7000 200 70
6 Daw Yin Yin L Maths 7000 200 70
7 Daw Thein Gi T Zool 6000 100 60
8 Daw Thidar T Bot 6000 100 60
9 Daw Khine Khine T Phy 6000 100 60
10 Daw Nilar T Chem 6000 100 60

Sorting and Subtotal

1. Using the STAFF TABLE.
(a) Show the staff table in the data with Ascending or Descending effect.
(b) Calculate the required subtotal.

Validation
2. Using the STAFF TABLE.
(a) Validation is less than 6000 between greater than 9000.
(b) Input Message Title is "Validation" and Input Message is "This cell boxes are checking now".
(c) Error Alert Title is "Wrong the data" and Error Message is "You carefully input the data must be less than
6000 between greater than 9000"

Condition Formatting
3. Using the STAFF TABLE.
(a) Condition1 is less than 6500 and text color is Red.
(b) Condition3 is greater than or equal to 8000 and text color is Blue.
(c) Condition2 is equal to 7000 and text color is Green.

Pages ( 7 of 8)
.
Income

Item Name Amount

Basic Pay 1000,000
Bonus 100,000
Over time Budget 50,000
Other 25,000
Total Income

Expense

Item Name Amount

Daily Cost 200,000
Shopping 150,000
Transport Cost 30,000
Phone Bill 10,000
Visit Cost 150,000
Total Expense

Saving Cost

Item Name Amount

Income Amount
Expense Amount
Total Saving Amount

Consolidate
(a) You can input the 1st numeric data in Sheet1.
(b) You can input the 2nd numeric data in Sheet2.
(c) Using the Consolidate. Find the Saving value data of Sheet1 and Sheet2 and then display Result data
in the Sheet3.
Define Method

(a) You can input the 1st numeric data in Sheet1.

(b) You can input the 2nd numeric data in Sheet2.
(c) Using the Define. Find the Saving value data of Sheet1 and Sheet2 and then display Result data in the
Sheet3.

Pages ( 8 of 8)