Q.No. 1. Perform following calculation in MS-Excel.
Places
|
Target
|
Production
|
Achievement
|
Grade
|
Dolakha
|
4800
|
6000
|
125%
|
|
Sindhupalchwok
|
5000
|
4500
|
90%
|
|
Kavrepalanchwok
|
5000
|
5000
|
100%
|
|
Average
|
4933.33
|
5166.67
|
104.73%
|
a)
Calculate
grade A+ for more than 100% achievement; A for 100% achievement and B for below
100% achievement.
b)
Create
a bar chart to show the target and production of three places under
observation.
Q.No.2 Enter
following data in your worksheet.
Loan Amount
|
13500
|
Loan Duration
in Years
|
7
|
Rate
|
|
Interest
|
a)
Enter
formula for the rate so that it will be 14% if Loan Duration is less than 5
years and Loan Amount is more than one lacks, otherwise it should be 8%. (2)
b)
Calculate
Interest = Loan Amount x Loan Duration x Rate / 100 (1)
c)
Change
the value of Loan Duration as necessary to get 80000 in Interest. (2)
d)
Apply
thick line boarder around the table and single thin line for the inner lines.
(1)
e)
Enter
“Interest Calculation” as header and set page number at the bottom center of
page. (2)
Q.No.3 Create a bar chart with the following data in
Sheet 1. (5)
Chart Title:
“Crop Production on 2005-2010”.
X-Axis: Year
Y-Axis:
Production
2005
|
2006
|
2007
|
2008
|
2009
|
2010
|
|
Rice
|
3476
|
3485
|
3488
|
3490
|
3499
|
3500
|
Wheat
|
3000
|
3200
|
3500
|
3700
|
3900
|
4300
|
Maize
|
2854
|
2967
|
3087
|
3154
|
3223
|
3410
|
Barley
|
4000
|
3800
|
4200
|
4500
|
4800
|
4000
|
Q.No.4. Calculate commission for following data in
Sheet 1. (5)
S.no.
|
Product
|
Cost Price
|
Selling Price
|
Commission %
|
Commission Amt.
|
1
|
Makita Hand
Drill 3420
|
$3000.00
|
$4200.00
|
||
2
|
Philips Steam
Iron PS243
|
$300.00
|
$385.00
|
||
3
|
Soni Camcord
$C331
|
$1200.00
|
$1800.00
|
a)
Commission
is 7% for all (Selling Price-Cost Price) is more than $500, otherwise it is 4%.
b)
Commission
Amount is calculated as Commission Percentage of Cost Price.
Q.No.5. Enter following data in your worksheet and
calculate values for empty cells. (5)
S.no.
| Name | Id no. |
Year 2014–15
|
Year 2015–16
|
Year 2016-17
|
Total |
Average
|
१
|
Dinesh Khadka
|
559926
|
98.22
|
99
|
98.89
|
||
२
|
Deepa Dhami
|
576478
|
100
|
99.2
|
100
|
||
३
|
Radhika Pal
|
278789
| 93.6 |
97
|
99
|
a.
On
the worksheet of No1, configure the settings so that the marks in third, fourth
and fifth column will be automatically displayed in red color when they are
less than 99. (2)
b.
Create
a line chart to show the increasing or decreasing trend in share market in
Sheet 2.
Organization
|
First Week
|
Second Week
|
Third Week
|
Fourth Week
|
Bank of
Kathmandu
|
235
|
255
|
312
|
200
|
Everest Bank
|
110
|
150
|
180
|
212
|
Sanima Bank
|
300
|
235
|
200
|
220
|
a. Create lines
for each bank in different color and show the index to indicate which color is
for which bank.
Q.No.6. Create a suitable pie chart to convey the
following message: (3)
There
are 28 students in a class. 10 are freshmen, 8 sophomores, 4 juniors, and 1
senior, 5 people did not answer the question.
Create
the following table and calculate ‘Final Grade’ as the average of four exams.
Award ‘Letter Grade’ A, B, C or D based
on Final Grade is above 90, 70, 60 or below respectively. Alphabetize the list.
Plot a bar chart of Final Grade. (7)
Name
|
Exam 1
|
Exam 2
|
Exam 3
|
Final
|
Final Grade
|
Letter Grade
|
Washington,
Adam
|
80
|
88
|
87
|
94
|
||
Stewart, Olive
|
99
|
92
|
96
|
100
|
||
Danforth, Elsie
|
85
|
99
|
42
|
95
|
||
Elton, Roger
|
56
|
76
|
74
|
70
|
||
Banks, Michael
|
45
|
35
|
56
|
60
|
||
Chau, Lisa
|
100
|
90
|
95
|
100
|
||
McDonald, Wendy
|
75
|
88
|
97
|
89
|
||
Renfrew,
William
|
90
|
90
|
85
|
89
|
Q.No.7. Enter following data in a sheet named 1990. (2)
Product
|
Production
|
Rice
|
5000
|
Wheat
|
4800
|
Maize
|
5200
|
Barley
|
4500
|
Enter following data in a sheet name 1991.
Product
|
Production
|
Rice
|
3000
|
Wheat
|
2800
|
Maize
|
4500
|
Barley
|
2000
|
Enter following
data in a sheet named 1992.
Product
|
Production
|
Rice
|
4000
|
Wheat
|
3600
|
Maize
|
4200
|
Barley
|
2800
|
a. Consolidate the sheets 1990, 1991
and 1992 using Sum function. (4)
b. Created a bar chart to display
consolidated data and enter chart title: Crop Production of Three Years. (4)
Q.No.8. Calculate following Salary Sheet.
ENO
|
ENAME
|
DESIG
|
BASIC
|
ALLOWANCE
|
DEDUCTIONS
|
NET PAY
|
a.
Configure
DESIG column in such a way that is can’t store more than 4 characters. The
probable values for DESIG are ADMN, MNGR, ACCT & PEON. (2)
b.
Create
formula for BASIC so that it is: (2)
c.
Rs.
7000 for PEON, Rs. 14000 for ACCT, Rs. 18000 for MNGR, Rs. 22000 for ADMN .
d.
ALLOWANCE
will be 15% of BASIC if he/she is not ADMIN or MNGR. (2)
e.
DEDUCTIONS
will be 1% of BASIC for PEON and 11% for MNGR and ACCT. For ADMN deduct 15% of
BASIC. (2)
f.
Create
range names so that you can type = ALLOW – DEDU as formula for NET PAY column.
(2)
Q.NO.9. Calculate following score sheet.
Symbol No
|
Eng
|
Nep
|
Mat
|
Sci
|
Percent
|
Grade
|
a. Use data
validate to allow symbol number to have exactly 7 characters. (2)
b. Apply
conditional formatting to display marks in red color if it is smaller than 50.
(2)
c. Calculate
Percentage and Grade. Grade is awarded as below. (4)
i. A+ if any of the marks obtained is more
than 90.
ii. A if percent is greater than 60.
iii. B if percent is greater than 50.
iv. C if percent is below 50 or any of the
mark is below 60.
d. Apply
Colorfull auto-format for the table. (2)
Q.No.10. Enter following data
in sheet 1 and display subtotal of each item and grand total. (6)
Date
|
Item
|
Quantity
|
12-Jan-00
|
Pen
|
25
|
13-Jan-00
|
Ink
|
28
|
13-Jan-00
|
Pen
|
27
|
14-Jan-00
|
Ink
|
10
|
14-Jan-00
|
Pen
|
15
|
14-Jan-00
|
Rubber
|
12
|
15-Jan-00
|
Carbon
|
25
|
15-Jan-00
|
Ink
|
8
|
15-Jan-00
|
Pen
|
22
|
15-Jan-00
|
Rubber
|
12
|
16-Jan-00
|
Ink
|
28
|
16-Jan-00
|
Pen
|
16
|
19-Jan-00
|
Rubber
|
10
|
a. Create a pie
chart for item Pen with sectors for each date. (4)
No comments:
Post a Comment