NAME TUITION SPORTS BOOK ARREARS SCHOLARSHIP

FEE FEE FEE

BABYBELL 3000000 5% 20000 3% 4%
0
FAITH 4000000 12000
0
SAMMY 3500000 25000
0
JAY- JAY 5000000 11000
0
MEGEE 1500000 30000
0
EBENEZAR 4500000 16800
0
ALBERT 2500000 40000
0
MARIAN 5500000 36000
0
EVA 1000000 40000
0
NAS 2000000 20000
0
DZIGSAM COMPUTER INSTITUTE

MICROSOFT EXCEL

EXERCISE 1

Below are the fee status of 10 students at Kwame Nkrumah University of science &
technology (KNUST) - Sunyani.

1) Plot on an excel sheet, a heading Merge and Centered with Bold,

Underline and font size 14 with the font style as Arial Rounded
MT Bold.
2) Calculate the total fee payable per student.
3) Note: all percentages are calculated on the tuition fee except the
scholarship which is calculated on the Total fee.
4) Find the sum, Count, Median, Minimum, Maximum and Average
for all fields.
5) Babybell, Nas and Jay – Jay need to pay extra 200000 for lost of
Textbook.

-1-
 DZIGSAM COMPUTER INSTITUTE

MICROSOFT EXCEL

EXERCISE 1

The following table shows the exam records of some of the students at KNUST –
Sunyani.

INDE NAME COMPUTE ACCOUNTIN FORESTR TOTA GRAD

X NO. R G Y L E
SCIENCE
0001 ALBERT 98 80 72
0002 MARIAN 50 50 50
0003 JAY- JAY 20 33 17
0004 BABYBELL 89 80 31
NOTE:
If total is greater than or equal to 250, grade is A
If total is greater than or equal to 200, grade is B
If total is greater than or equal to 150, grade is C
If total is greater than or equal to 70, grade is D
 Using the IF function write the formula that will generate the grade for each
student.
 Add double line borders with color blue to all your work with font color pink for
the answers.
 Save your work with your index numbers in a folder created your with your
names.
 Calculate Sum, Average, Maximum, Minimum, Median and Count for all the
courses.

-2-
 DZIGSAM COMPUTER INSTITUTE

MICROSOFT EXCEL

EXERCISE 1

The following employees work with GABWAY& ASSOCIATES:

1. Kofi
2. Joy
3. Ama
4. Addo
5. Kwame
6. John
7. Rose
8. Kwaku
9. Yaa
10. Mark
Each employee earns ¢65,000.00 more than the one listed above him, and the
basic salary of Kofi is ¢650,000.00.
The following are the allowances and deductions of employee of the firm
Allowances
Risk - 3.5% of basic salary
Medical - 4.2% of basic salary
Ration - 2.3% of basic salary
Transport - 2.0%of basic salary

Deductions

Income tax - 22.1%of basic salary

Credit union - 0.8%of basic salary
Rent - 15.0% of basic salary
Utilities - 7. 2% of basic salary

Gross Salary = sum of Allowances+ Basic salary,

Net salary = Gross Salary – T. deduction.
a) With the help of a spreadsheet application package, prepare a payroll for
the employees
b) Calculate the totals for all the deductions, allowances, gross salaries and
Net salaries.

-3-
 c) Format all figures into cedes. The name field column should be 18 and
the remaining field columns should be 12.

d) Put BORDERS around your payroll.

e) Save the document as your name.

DZIGSAM COMPUTER INSTITUTE

MICROSOFT EXCEL

EXERCISE 2

The table shows the payroll of Footprint Enterprise where staff is ranked as senior and
junior.
Last First Rank Basic Total Total Net
Name Name Salary Allowance Deductions Salary
Asare Adwoa Senior 800,500
Opoku Stella Senior 350,600
Annor Rita Junior 900.000
Ali Yakubu Junior 300,100
Boateng Eric Senior 550,000
Oppong Eben Junior 870,990
Quay Grace senior 450,340
Total

For Senior Staff, Total allowance is 10% of Basic Salary and for
junior staff. Total allowance is 8% of Basic Salary.

Introduce a column for the following

 SSNIT contribution is 5% of Basic Salary;
 Gross salary,
 income tax 22.1%
 Use (DZIGSAM COMPUTER INSTITUTE) as the Title name of your
work.
 Merge and Center the Title, Font Size :16, Font Face :Times New
Roman

-4-
  Plot a chart for your work.
 Print your work on a A4 sheet.

DZIGSAM COMPUTER INSTITUTE

MICROSOFT EXCEL

EXERCISE 2b

The table shows the payroll of Footprint Enterprise where staff is ranked as senior and
junior.
Last First Rank Basic Total Total Net
Name Name Salary Allowance Deductions Salary
Asare Adwoa Senior 800,500
Opoku Stella Senior 350,600
Annor Rita Junior 900.000
Ali Yakubu Junior 300,100
Boateng Eric Senior 550,000
Oppong Eben Junior 870,990
Quay Grace senior 450,340
Total

For Senior Staff, Total allowance is 10% of Basic Salary and for
junior staff. Total allowance is 8% of Basic Salary.

Introduce a column for the following

 Using the IF function to calculate the Allowance for the Senior and Junior.
 SSNIT contribution is 5% of Basic Salary;
 Gross salary,
 income tax 22.1%
 Use (DZIGSAM COMPUTER INSTITUTE) as the Title name of your
work.
 Merge and Center the Title, Font Size :16, Font Face :Times New
Roman
-5-
  Plot a chart for your work.
 Print your work on a A4 sheet.

DZIGSAM COMPUTER INSTITUTE

MICROSOFT EXCEL

EXERCISE 3

Below are the fee status of 10 students at DZIGSAM COMPUTER INSTITUTE -Sunyani
.

6) Plot on an excel sheet, a heading Merge and Centered with Bold, Underline and
font size 14 with the font style as Arial Rounded MT Bold.
NAME TUITION SPORTS BOOK ARREARS SCHOLARSHIP
FEE FEE FEE
ABIGAIL 3000000 5% 200000 3% 4%

PAUL 4000000 120000

SAMMY 3500000 250000

PETER 5000000 110000

MEGEE 1500000 300000

EBENEZAR 4500000 168000

ALBERT 2500000 400000

MARIAN 5500000 360000

EVA 1000000 400000

RICHIE 2000000 200000

7) Calculate the total fee payable per student.

8) Note: all percentages are calculated on the tuition fee except the scholarship
which is calculated on the Total fee.

-6-
 9) Find the sum, Count, Median, Minimum, Maximum and Average for all fields.
10) Abigail, Richie and Peter need to pay extra 200000 for lost of Textbook.

DZIGSAM COMPUTER INSTITUTE

MICROSOFT EXCEL

EXERCISE 4

The following table shows the exam records of some of the students at DZIGSAM
COLLEGE– Sunyani.

INDEX NAME MS MS PHOTOSHOP V.B TOTAL GRADE

NO. WORD ACCEAA
0001 ALBERT 35 30 5 10
0002 MARIAN 30 20 15 25
0003 JAY- JAY 20 25 20 7
0004 BABYBELL 42 30 10 12
0005 ABIGAIL 33 25 15 10
0006 PAUL 10 15 10 20
0007 SAMMY 15 5 30 20
0008 PETER 30 25 10 5
0009 MEGEE 30 20 30 10

NOTE:
If total is greater than or equal to 90, grade is A
If total is greater than or equal to 75, grade is B
If total is greater than or equal to 65, grade is C
If total is greater than or equal to 50, grade is D
If total is greater than or equal to 0, grade is F
 Using the IF function write the formula that will generate the grade for each
student.
 Add double line borders with color blue to all your work with font color pink for
the answers.
 Save your work with your full name in a folder created with your name.
 Calculate Sum, Average, Maximum, Minimum and Count for all the courses.

-7-
 DZIGSAM COMPUTER INSTITUTE

MICROSOFT EXCEL

EXERCISE 5

Joyce is the owner of communication center in Sunyani. Joyce communication center

has branches at Adjisu, Srodai, Betom, Zongo, and Techiman. Below is the sales
Analysis for the month of January 2006.

LOCAL CALLS: Adjisu – 500,000, Srodai – 450,000, Betom – 400,000, Zongo –

350,000, Techiman – 420,000.

IDD CALLS: Adjisu – 380,000, Srodai – 340,000, Betom – 270,000, Zongo -

260,000, Techiman – 200,000.

P / COPIES: Adjisu – 410,000, Srodai – 330,000, Betom – 160,000, Zongo -

250,000, Techiman – 10,000.

FAX : Adjisu – 400,000, Srodai – 590,000, Betom – 390,000, Zongo -

350,000, Techiman – 600, 000.

QUESTIONS

1) Plot this on an Excel worksheet.

2) Provide a suitable heading for this work.
3) Format the heading font size 14, bold, merged and centered.
4) Find totals for all fields.
5) Find the Average, Maximum, Minimum and Count for all fields.
6) Plot a chart for your work.
7) Create a folder on the desktop with your name
8) Save the work with your full name in the folder.

-8-
 DZIGSAM COMPUTER INSTITUTE

MICROSOFT EXCEL

Exercise 6

Joe Bamfo is the proprietor of Dzigsam Limited, a PC dealer.

Here is the wages of his company of January 2018.

WORKERS:- Sam, Agnes, Comfort, Kwame, Ruth, Afi, Smart and Mensah.

Hours Worked:- Sam – 120, Comfort – 80, Kwame – 130, Ruth – 88, Smart – 69,
Afi – 140, Mensah – 90 and Agnes – 100.

Rate Per Hour:- Sam –15,000, Comfort – 88,000, Kwame –10,000, Ruth – 25,000,
Smart – 20,000, Agnes – 22,000, Afi – 10,000, Mensah – 14,000.

Bonus :- Sam - 25,000, Comfort –26,000,Kwame – 28,000,Agnes –30,000,

Ruth – 11,000, Mensah – 20,000, Afi – 32,000, Smart –15,000.

T&T :- Sam – 15,000, Smart – 20,000, Agnes – 22,000, Comfort –12,000

Mensah – 14,000, Afi – 20,000, Ruth – 25,000, Kwame – 10,000.

TAX :- Sam – 22,000, Smart – 24,000 Agnes – 30,000, Ruth – 25,000,

Comfort –22,000, Kwame –18,000,Afi – 23,000,Mensah – 40,000

RENT :- 25% for each woker.

SSF :- 5% for each worker.

UTILITIES :- Sam –22,000, Smart – 24,000, Agnes – 30,000, Comfort – 22,000

Kwame –180,000, Ruth – 25,000, Afi – 23,000, Mensah – 40,000

QUESTIONS:

i. Plot this on an Excel worksheet.

ii. Supply a heading, merge and center with font size 16 and Bold.
iii. Calculate Gross Wages.
iv. Calculate Average, Maximum and Minimum for all figures.
v. Create a column for Total deduction.
-9-
 vi. Calculate Net Wages.
vii. Calculate the Base Salary.
viii. Plot a chart for your work.
ix. Create a folder on the desktop with your name and Save the work with your full.
x. Print out the work.

DZIGSAM COMPUTER INSTITUTE

MICROSOFT EXCEL

EXERCISE 7

Below is the fee status of 10 students at old Estate Senior Secondary School.

NAME TUITION SPORTS BOOK ARREARS SCHOLARSHIP

FEE FEE FEE
JOE 250,000 10% 80,000 12% 5%
MARK 60,000 100,000
LIZ 200,000 120,000
AMA 220,000 680,000
KOFI 180,000 90,000
ADEZ 155,000 110,000
DEE 208,000 70,000
CHARLES 168,000 85,000
MAGGI 300,000 50,000
COMFORT 284,000 150,000

TASK
1. Plot on the Excel sheet, a heading, Merge and Centered with Bold, Italic and font
size 14.
2. Calculate the total fee payable per student.
3. Note: All percentages are calculated on the Tuition Fee.
4. Adez and Comfort need to pay ¢80,000 each for lost of textbook.
5. Find the Count, Maximum, Minimum, and the Average for all the fields.
6. Use any of the conditional formatting to format your work.

- 10 -
 DZIGSAM COMPUTER INSTITUTE

MICROSOFT EXCEL

EXERCISE 8

SIX MONTHS PROJECT SALES AND PAYROLL EXPENDITURE FOR

DZIGSAM COMPUTER SCHOOL
JANUARY FEBUARY MARCH APRIL MAY JUNE
SALES 525,135 650,255 443,873 833,892 378,234 623,754
Payroll
Expenditure:
Base salary
Commission
Bonus
Benefits
Payroll Tax
Total P/R
Expenditure

ASSUMPTIONS
Base Salary 30%
Commission 2.5%
Bonus ¢17,750
Net Salary for 500,000
Bonus
Benefits 23.75%
Payroll Tax 10.25%

TASKS
1) Calculates the Base Salary, Commission, Bonus, Benefits and the Payroll tax for
the six months.
2) Format each answer to two decimal places.
3) Merge and Center the heading, Bold and font size 14.
4) Represent your answers on a chart.
5) Print out the work along with the chart.

- 11 -
 DZIGSAM COMPUTER INSTITUTE

MICROSOFT EXCEL

ASSIGNMENT 7

QUESTIONS

1. Create a worksheet for the above data and save it as

your name.
2. Calculate the total values for all fields.
3. Format your worksheet to any desirable form.
4. Calculate for Average, Maximum, Minimum and Count
Values using the appropriate functions.
5. Insert today’s date using the now function.
6. Use any of the conditional formatting to format your work.
- 12 -
 DZIGSAM COMPUTER INSTITUTE

MICROSOFT EXCEL

ASSIGNMENT 10

1. Name five components of Excel Screen and define three.

2. Distinguish between Workbook and Worksheet.

3. What are rows, cells and columns?

4. List the procedure use to protect workbook and worksheet.

5. How many rows and columns do we have?

6. How will you:

a) Insert a row between row number 3 and row number 4

b) Insert a column between column C and D.

c) Rename a worksheet

7. Explain the following and give function formula for each.

a) Average

b) Sum

c) Maximum

d) Minimum

e) Count

8. What is the default number of sheet excel displays whenever

you open it? List them.

- 13 -
 9. Explain an active cell and an active worksheet.

10. State the steps in using

the Go To command and to set up a page.

DZIGSAM COMPUTER INSTITUTE

MICROSOFT EXCEL

ASSIGNMENT 11

Instructions: Perform the following tasks to create a worksheet

1. Enter the worksheet title, Steel Frame House weekly payroll, in cell A1,
2. Enter the column titles in row 2,
3. The row titles in column A and the data in column B through D
4. Use the following formulas to determine the gross pay, federal tax, state tax, and net pay:
Gross Pay = Rate*Hours (Hint: Assign the first employee in cell E3 the formula = B3*C3, and copy
the formula in E3 to the range E4:E8 for the remaining employee)
Federal tax = 20%*(Gross Pay – Dependents*38.46)
State Tax = 3.2%*Gross Pay
Net Pay = Gross Pay – (Federal Tax + State Tax)
Show totals for the gross pay, federal tax, state tax and net pay in row 9.
Determine the average, highest, and lowest values of each column in rows 10 through 12 by using
the appropriate functions.
Bold the worksheet title.
Using the buttons on the formatting toolbar, assign the comma style with two decimal places to the
range B3:H12.
Bold, italicize, and draw a heavy border under the column titles in the range A2:H2. Right – align
the column titles in the range B2:H2.
Italicize the range A9:A12. Draw a heavy top border and a light double bottom border in the range
A9:H9.
Change the height of row 10 to 24.00 points, the width of column A to 10.00 characters, and the
width of columns B through H to best fit.
Enter your name, course, Exercise Number, date and instructor’s name below the entries in
column A in separate but adjacent cells.
Save the workbook using the filename, Steel Frame House.
Preview and then print the worksheet.

- 14 -
- 15 -