BBA 11043 – Information and Communication Technology
Faculty of Management and Finance
Spreadsheet Management 2025
1. Open a blank Excel workbook and add the following entries into the specified cells in the work
sheet (File Name as a Products)
A1: Quotation for Computers and Mobile Phones
A3: Product B4: Apple C4: MacBook Pro 16 D4: $2399
A4: Computer B5: Dell C5: XPS 15 D5: S1999
A7: Mobile Phone B6: HP C6: Spectrex360 D6: $1390
B3: Brand B7: Apple C7: iPhone 12 Pro D7: $999
C3: Model B8: Samsung C8: Galaxy S21 Ultra D8: $850
D3: Price B9: Xiaomi C9: Mi 11 D9: $650
i. Merge the cells from A1 to D1.
ii. Merge the cells from A4 to A6 and set the text orientation as “Rotate Text Up”.
iii. Merge the cells from A7 to A9 and set the text orientation as “Rotate Text Down”.
iv. Add the font size, font style, font color as in appropriately.
v. Apply the border for the table.
2. Create the following table.
i. Open a new worksheet and rename worksheet as “Employee”.
ii. Enter the labels and values in the extract cells location as desired.
iii. Use AutoFill to put the Employee Numbers into cells A7 to A12.
iv. Set the columns width, rows height and labels alignment appropriately.
v. Use wrap text and merge cell as desired.
vi. Apply borders, gridlines and shade them to the tables as desired.
vii. Apply date format on Date of Joined column (Choose type “14-March-2001”)
1 Computer Unit – Faculty of Management and Finance, University of Ruhuna.
BBA 11043 – Information and Communication Technology
Faculty of Management and Finance
Spreadsheet Management 2025
viii. Apply currency format (Rs.) from Gross Pay to Net Pay columns with two decimal places.
ix. Calculate the Gross Pay for each employee.
𝑮𝒓𝒐𝒔𝒔 𝑷𝒂𝒚 = (𝑷𝒂𝒚 𝒑𝒆𝒓 𝑯𝒐𝒖𝒓 𝒙 𝑻𝒐𝒕𝒂𝒍 𝑯𝒐𝒖𝒓𝒔 𝑾𝒐𝒓𝒌𝒆𝒅)
+ (𝑶𝒗𝒆𝒓𝒕𝒊𝒎𝒆 𝑷𝒂𝒚 𝒑𝒆𝒓 𝑯𝒐𝒖𝒓 𝒙 𝑻𝒐𝒕𝒂𝒍 𝑶𝒗𝒆𝒓𝒕𝒊𝒎𝒆 𝑯𝒐𝒖𝒓𝒔)
x. Calculate the Income Tax for each employee which is 9.5% of the Gross Pay.
𝑰𝒏𝒄𝒐𝒎𝒆 𝑻𝒂𝒙 = (𝑮𝒓𝒐𝒔𝒔 𝑷𝒂𝒚 𝒙 𝑰𝒏𝒄𝒐𝒎𝒆 𝑻𝒂𝒙 𝒑𝒆𝒓𝒄𝒆𝒏𝒕𝒂𝒈𝒆)
xi. Calculate the Net Pay for each employee.
𝑵𝒆𝒕 𝑷𝒂𝒚 = (𝑮𝒓𝒐𝒔𝒔 𝑷𝒂𝒚 – 𝑰𝒏𝒄𝒐𝒎𝒆 𝒕𝒂𝒙)
xii. Set the worksheet vertically and horizontally on the page.
xiii. Save your work.
3. Open a new worksheet in same workbook and rename it as “Student”, then, create the following table
Highest Total Average Pass/
Student Name Maths English Science Grade Rank
Marks Marks Marks Fail
Dakshina Perera 94 61 85
Ravindu Nagasinghe 56 56 34
D. Attanayake 59 38 25
SithariWanigasekara 19 19 19
Kusal Kodikara 30 24 18
Gayeli de Silva 66 78 55
F.Weerasekara 35 59 20
Nilu Mendis 85 78 81
i. Fill Highest Marks, Total Marks and Average Marks columns in the above data table (use Excel
functions: Max, Sum, and Average).
ii. Fill the Pass/Fail column, use the “if” function and the following criteria.
Average marks ≥ 40 → Pass
Average marks < 40 → Fail
iii. Fill the Grade column, use the “if” function and the following criteria.
Grade A: Average marks ≥ 75
Grade B: Average marks ≥ 65
Grade C: Average marks ≥ 40
Grade D: Average marks < 40
iv. Find the Rank for each student.
2 Computer Unit – Faculty of Management and Finance, University of Ruhuna.
BBA 11043 – Information and Communication Technology
Faculty of Management and Finance
Spreadsheet Management 2025
v. Create a column chart, which shows Student Name in x-axis and Subject Marks in y-axis.
vi. Show Dakshina Perera’s subject marks using a pie chart.
vii. Create a line chart, which shows Student Name versus Average Marks.
4. Open a new worksheet in above workbook and rename it as “Salary”, then, create the following table.
Basic salary Allowance Housing loan Total salary Income tax
Emp. Name
(Rs) (Rs) (Rs) (Rs) (Yes/No)
A.T. Sirimanna 120,115 33,500.00
C. R. Salgadu 225,560 77750.00
B. T. Ranathunga 90,000 22,250.00
D. Samarasingha 125,560 15000.00
F. K. Kumara 113,270 10550.00
E. Wanigasekara 325,000 44,600.00
G. Dharmaranga 130,775 32,500.00
H. Saranga 225,560 30000.00
i. Fill the Allowance and the Total salary columns, use the following formulae.
Allowance = Basic salary × 25%
Total Salary = Basic salary + Allowance - Housing loan
ii. Fill above Income tax column, use “if” function and following criteria.
Pay income tax, if Annual Basic salary and Allowance ≥ Rs. 1800,000.00
iii. Insert a new column right of the above table for income tax calculations based on the following
criteria, their monthly salary,
a. First 1 ½ lax is free of tax
b. Second 1 ½ to 6 lax, 1.6% of total earnings.
c. Third 6 to 12 lax 2.3% of total earnings.
d. Forth 12 lax to above 4.5% of total earnings.
iv. Create a column chart, which shows Emp. Name in x-axis and Basic salary in y-axis.
v. Create a bar chart, which shows Emp. Name in x-axis and Total salary in y-axis.
5. Add a new worksheet and create the following table.
3 Computer Unit – Faculty of Management and Finance, University of Ruhuna.
BBA 11043 – Information and Communication Technology
Faculty of Management and Finance
Spreadsheet Management 2025
Item Quantity Unit price (Rs) Amount Discount (Rs) Price (Rs)
Empty CD 20 25.00
Empty DVD 40 60.00
Paper Cutter 30 18.50
Color pencil 50 10.00
Note book 25 48.00
Total
i. To fill the Amount, Discount and the Price columns, use the following formulae.
Amount = Quantity × Unit price
Discount = Amount × 10% (only for number of items are more than or equal 24)
Price = Amount - Discount
ii. Sort the above database in ascending order by ‘Item’.
iii. Create a bar chart, which shows Item in x-axis and Unit price in y-axis.
iv. Create a column chart, which shows Item in x-axis and Quantity in y-axis.
4 Computer Unit – Faculty of Management and Finance, University of Ruhuna.
