MICROSOFT EXCEL QUESTIONS – MASOMO MSINGI PUBLISHERS

MICROSOFT EXCEL

PRACTICAL EXERCISE 1.1

Using the data given, get the sum of all the figures within the range.

	A	B	C	D	E	F	G
1		Mon	Tue	Wed	Thur	Fri	TOTAL
2	Breakfast	3,560	3,186	2,952	3,395	3,436
3	Lunch	20,163	21,416	19,912	19,681	18,628
4	Bar	9,873	12,172	12,642	12,711	18,846
5	Snacks	2,405	3,544	2,694	3,120	3,712
6	TOTALS

PRACTICAL EXERCISE 1.2

Enter the data given below into a worksheet.

	A	B	C	D	E
1	Stationery Supplies Ltd
2
3	Date	SalesPerson	Item	Receipt No	Amount
4	21-Nov	Carl	Toys	1238	1,782.10
5	26-Nov	Carl	Stationery	1255	4,853.55
6	26-Nov	Carl	Toys	1395	51.35
7				Carl’s Total
8	21-Nov	John	Cards	1141	91.15
9	24-Nov	John	Books	1982	442.60
10	21-Nov	John	Toys	1885	561.50
11	26-Nov	John	Toys	1875	62.75
12				John’s Total
13	22-Nov	Judy	Books	1032	234.50
14	26-Nov	Judy	Sports goods	1920	472.60
15				Judy’s Total
16	25-Nov	Mary	Toys	1774	364.15
17				Mary’s Total
18	22-Nov	Susan	Electronics	1160	52.95
19	23-Nov	Susan	Cards	1075	81.60
20	23-Nov	Susan	Others	1745	132.95
21	24-Nov	Susan	Sports goods	1662	2,580.10
22				Susan’s Total
23
24				Grand Total

Calculate the totals for each salesperson and get the grand total.:
Format the worksheet as follows:

Make all the Totals bold, two decimal places, comma, center the title across columns A-E and make it size 16, bold and Italic.

Put a double border round the whole table and a single line border inside the table.
Save the worksheet as Stationery Analysis.

PRACTICAL EXERCISE 1.3

Using the information given in the table below, calculate the total amount payable by the company to the employees.

	A	B	C	D	E
1	Services Company Ltd
2	Overtime Details
3	Date	Name	Hours Worked	Rate	Amount
4	26-Nov	Kennedy	5	70	350.00
5	26-Nov	Kennedy	5	100	500.00
6	26-Nov	Mary	5	100	500.00
7	26-Nov	Lewis	4	100	400.00
8	30-Nov	Judy	3	100	300.00
9	30-Nov	Kennedy	6	70	420.00
10	30-Nov	Lewis	5	100	500.00
11	30-Nov	Kennedy	4	70	280.00
12	30-Nov	Judy	5	100	500.00
13	30-Nov	Lewis	5	100	500.00
14	02-Dec	Judy	4	70	280.00
15			Total Amount

PRACTICAL EXERCISE 1.4

A Payroll consists of Basic Pay, Allowances, Gross Salary, Deductions and Net Salary. The Allowances are 23% of the Basic Pay while the Deductions are 12% of the Gross Salary.

In the given worksheet, indicate in each cell what will be inserted, that is – a value or a formula. In the case of a formula, write down the formula in the cell.

	A	B	C	D	E	F
1	Stationery Supplies Ltd
2
3	Name	Basic Pay	Allowances	Gross Salary	Deductions	Net Salary
4	Lewis
5	Francis
6	Edwin
.						.
.						.
.						.
13	Totals

PRACTICAL EXERCISE 1.5

Assume you are the Accountant of Stationery Supplies Ltd. Below is the current payroll in the workbook OLD PAYROLL.

OLD PAYROLL.

	A	B	C	D	E	F
1	Stationery Supplies Ltd.
2
3	Name	Basic Pay	Allowances	Gross Salary	Deductions	Net Salary
4	Lewis	15,791	3,137	18,928	1,256	17,672
5	Francis	15,537	3,061	18,598	776	17,822
6	Edwin	15,506	3,051	18,557	999	17,558
7	Bernard	15,417	3,025	18,442	1,099	17,343
8	George	15,008	2,902	17,910	718	17,192
9	Albert	14,969	2,890	17,859	846	17,013
10	Edward	14,651	2,795	17,446	760	16,686
11	Cornell	14,618	2,785	17,403	663	16,740
12	John	14,553	2,765	17,318	558	16,760
13	Carl	14,508	2,752	17,260	706	16,554
14
15	Totals	150,558	29,163	179,721	8,381	171,340

The following salary review is given to you in the workbook INCREMENT.

INCREMENT

	A	B	C
1	Name	Current Pay	% increase
2	Lewis	15,791	19%
3	Francis	15,537	19%
4	Edwin	15,506	22%
5	Bernard	15,417	18%
6	George	15,008	21%
7	Albert	14,969	17%
8	Edward	14,651	15%
9	Cornell	14,618	25%
10	John	14,553	19%
11	Carl	14,508	20%
12
13	Allowances
14	20%

Required:

Using formulas, you are required to update the payroll with the changes in a blank worksheet. This new blank sheet is in the workbook NEW PAYROLL.

PRACTICAL EXERCISE 1.6

You are in charge of a young and growing business. You have identified the various factors (sources of revenue and expenses) that influence the business as shown in the table below. Use the figures provided and the layout to create a financial projection model for the business for the next six years. The parameters are given on Sheet 2.

SHEET 1:

INCOME AND EXPENSES PROJECTIONS
	1999	2000	2001	2002	2003	2004
Sales	10,000
% Growth over the previous year		20%	30%	20%	10%	10%

Materials
Wages
Other benefits
Others

Total Cost of Goods Sold

Salary: Office
Salary: Sales
Other Benefits
Advertising & Promotions
Depreciation
Miscellaneous

Total General & Admin. Expenses

Total Operating Costs

Interest on Loans

Pre-tax Income

Tax

Profit

SHEET 2:

Parameters		Description
Sales	10,000	Starts at 10,000 and grows by a percentage
Materials	17%	17% of Sales
Wages	14%	14% of Sales
Other benefits	2.1%	2.1% of Sales
Others	8%	Starts at 100, then grows by 8% yearly
Salary: Office	10%	Starts at 1,000, then grows by 10% annually
Salary: Sales	8%	8% of Sales
Other Benefits	17%	17% of Total Salary
Advertising & Promotions	2.5%	2.5% of Sales
Depreciation	20	Fixed at 20 every year
Miscellaneous	10	Starts at 10 and grows by a fixed amount of 10 annually

Interest on Loans	10	A fixed amount of 10 each year

Tax	52%	52% of Pre-tax Income

Exercise Instructions.

Open the worksheet named Income and Expenses Projections.xls.
Rename Sheet1 as Projections while Sheet 2 should now be Parameters.
Calculate the Sales for the year 2000 using the percentage given in cell C5.
Copy the formula across to the Year 2004.
Calculate the different items that make up the Total Operating Costs using the parameters in the Parameters sheet.

(You should enter the formula for the Year 1999 and copy down to the year 2004. Use Absolute Referencing effectively).

Hint: Total Cost of Goods Sold = Materials + Wages + Other Benefits + Others

Calculate the Total Operating Costs:

Total Cost of Goods Sold + Total General and Administrative Expenses.

Calculate the Interest on Loans:
Calculate the Pre-tax Income.

Sales – Total Operating Cost – Interest on Loans.

Calculate the Tax.
Calculate the Profit:

Pre-tax Income – Tax.

Format the worksheet as follows:

Make all the Totals bold, zero decimal places, comma, center the heading between A1:G1 and make it size 16, bold.

Save the file as C:\Exams\Creative.xls

PRACTICAL EXERCISE 1.7

From the data given in the table below, create a Pie Chart to show the distribution of the total amount amongst the various salesmen.

	A	B	C	D	E	F
1	ABC Company Sales Performance Report
2
3	Salesman	Qtr1	Qtr2	Qtr3	Qtr4	Total
4	Albert	148	156	171	140	615
5	Carl	122	131	153	118	524
6	Cornell	211	243	246	250	950
7	Edwin	129	150	92	218	589
8	Francis	311	270	247	322	1,150

PRACTICAL EXERCISE 1.8

	A		B	C	D	E	F	G	H	I
1	XYZ COMPANY SALES PERFORMANCE
2
3
4	SALESPERSON	ANNUAL TARGET		QTR1	QTR2	QTR3	QTR4	TOTAL SALES	AVERAGE SALES	COMMISSION
5
6	ALBERT	750		148	256	133	154	X
7	MICHAEL	650		187	143	258	143	X
8	CARL	800		233	200	216	152	X
9	GEORGE	700		256	145	136	259	X
10	LUCY	1,000		249	212	215	124	X
11
12	TOTAL	X		X	X	X	X	X
13
14	COMMISSION	6%
15

Given the table above, write formulas or describe how you would calculate the Total Sales in column G and Row 12.
How would you calculate the Average Sales? Write the formula as it should appear in Excel and show the method of duplicating it to the other cells.
How would you calculate the Commission? Write the formula as it would appear in Excel and explain the method of duplicating it to the other cells.
Explain how you can insert two rows above Row 10.
What does this sign (# # # # # # #) mean when seen in cell(s)? What should you do when you see this sign?
How does one delete an entire row or column and all its cells?
You are asked to compare QTR1 and QTR3 sales for all salespersons in the above table using a chart:
- What range of cells do you need to select and how would you select it?
- What type of graph would you use?
How can you edit a chart once it is created, lets say, to change the series from columns to rows, to change the legend, etc?

PRACTICAL EXERCISE 1.9

Create the worksheet below from scratch.

	A	B	C	D	E	F
1	TOWN / DAY	Mon	Tue	Wed	Thur	Fri
2	Mombasa	30	29.5	31	28.5	32
3	Kisumu	31	33	30	30	32
4	Nakuru
5	Nairobi	24	23.5	22	23	24.5
6
7
8	TOWN / DAY	Mon	Tue	Wed	Thur	Fri
9	Mombasa
10	Kisumu
11	Nakuru
12	Nairobi

Questions

Use a formula to calculate the temperature for Nakuru, given that the temperature for Nakuru is ¾ that of Mombasa.
Create two blank rows below Row 1.
Type “ in degrees Celsius” in Cell A2 and “Temp. in degrees Fahrenheit” in Cell A9.
Calculate the temperature in F using the conversion factor given as F=(C+19)*9/5.
Insert a blank row before Nakuru and enter the row heading Kericho in both temperature versions.
Calculate the temperature for Kericho in both versions given that it is 4/5 that of Kisumu.
Type “Average Temp. (C)” in Cell G3 and “Average Temp. (F)” in Cell G11 respectively. Calculate the average temperatures for all the towns.
Format all cells containing the temp. values to zero decimal places with no commas.
Format all cells containing Average Temp. to Arial Black, Italics, size 12.
Centre the Titles across columns A and G.
Put a double border round the whole table and a single line border inside the table. Shade the column for Average temperature gray.
Use the Average values (C) in column G to create a 3-D Exploded Pie Chart to show distribution of temperature for the towns.

The title should be “Average Temp. (C)”. Use the text in column A as the legend. In the data labels, select Show Value.

Move the chart created above to Sheet3. Do not insert it as an object.
Move the left top corner of the chart in Sheet3 to cell A7. Resize the chart to fit into the range A7:h20.
Save the worksheet as Weather.

PRACTICAL EXERCISE 1.10

Use the worksheet given to answer the questions that follow:

Expenses for the Month of January vs. Budget

	Budget	Savings
Salaries and Wages	156675.00
Rent	4300.00
Electricity	1000.00
Telephone	200.00
Advertisements	20000.00
Freight and clearing	15650.00
Security	3800.00

Questions

Insert a new column between Budget and Savings column.
Enter the title ‘Actual’ in cell C3.
Enter the following figures in the new column.

	Actual
Salaries and Wages	145200
Rent	4300
Electricity	1207
Telephone	142
Advertisements	18550
Freight and clearing	13400
Security	3800

Calculate the savings in cells D4:D10.
Format the sheet title to Arial Black, size 14, and Bold.
Save the file as Audit 1.
Format the range B4:D10 to two decimal places.
Adjust column C such that all the values are displayed.
Add the title Savings % in cell E3 and calculate the savings as a percentage of the budget.
Format the range E4:E10 as a percentage.
Enter the row title Total in cell A12 and obtain totals for Budget, Actual, and Savings columns.
Copy the formula in E10 to E12.
Format the new heading to match the existing headings and Align the heading to Right.
Save and Close the worksheet.

PRACTICAL EXERCISE 1.11

Use the Balance Sheet given below to answer the questions that follow:


Income and Expenses Projection
	1999	2000	2001	2002	2003	2004
Sales	10,000	12,000	15,600	18,720	20,592	22,651
% Growth over the previous year		20%	30%	20%	10%	10%

Materials	1,700	2,040	2,652	3,182	3,501	3,851
Wages	1,400	1,680	2,184	2,621	2,883	3,171
Other benefits	210	252	328	393	432	476
Others	100	108	117	126	136	147

Total Cost of Goods Sold	3,410	4,080	5,280	6,322	6,952	7,644

Questions

Insert five rows above Row 1.
Reduce the size of the picture to a Height of 0.96” by a Width of 1.66” (inches).
Drag and drop the picture to fit in the range A1:A5.
Add the Header ‘Balance Sheet 2001’ to the right section of the worksheet.
Make the heading size 14, and Bold.
Save the file as Balance Sheet.

PRACTICAL EXERCISE 1.12

Use the worksheet given below to answer the questions that follow:

Employee Details

Emp No	Name	Category	Basic Pay	Allowances	Gross Pay	Tax Deductions
E8	Cornell	Assistant	4,600	300
E9	John	Assistant	3,500	450
E2	Francis	Supervisor	6,508	500
E3	Edwin	Management	8,006	1,801
E10	Carl	Assistant	4,200	100
E4	Bernard	Management	7,917	1,775
E5	George	Supervisor	5,500	850
E1	Lewis	Management	8,291	2,500
E6	Albert	Supervisor	5,700	760
E7	Edward	Supervisor	7,151	1,545

Tax Rate
12%

Questions

Make all the column titles bold, and size 12. Center the title , across columns A1:H1 and make it size 16, and Bold.
Calculate the gross pay in cell F4.
Calculate the amount of tax deducted from each employee, given that the tax rate is 12% of the gross pay. Tax rate is found in cell A16 of the worksheet.
Format the text orientation in the range A4:G4 to 0 degrees.
Adjust the column width such that all the headings are visible.
In cell H4, enter the title Net Pay and calculate the Net pay for all employees.
Format the range G4:H13 to zero (0) decimal places.
Format the title Net Pay to match the other titles.
Set the range A1 to H16 as Print Area.
Using the Names in column B, Basic Pay in column D, and Allowances in column E, insert a Clustered Column Pie Chart on the same sheet to show comparison of the salaries for the employees. The Chart Title should be Employee Details, the Y-axis should be Employee Names and the X-axis should be Thousands (Kshs).
Move the chart so that the top left corner is on cell A18.
Change the Chart Title to Employees’ Salary Details.
Change the Chart Type to Clustered bar with a 3-D visual effect.
Increase the Chart Title Font size to 14.
Change the text direction for the title of the X-axis to 0 degrees, and for the Y-axis to -90 degrees.
Resize the chart such that the bottom left corner is on cell A55, while the bottom right corner is on cell I55 so that all the details are clearly visible.
Save the worksheet as Salary Details (Reviewed Copy).

PRACTICAL EXERCISE 1.13

Use the data given in the table below:

SHEET 1:

Brick Court Investments
Overtime calculations for the month of November
Date	Name	Hours Worked	Day of Week	Rate	Amount
27-Oct	Albert	4	Sun	100	400
8-Nov	Albert	3	Fri	50	150
18-Nov	Lewis	4	Mon	50	200
9-Nov	Albert	4	Sat	50	200
12-Nov	Albert	3	Tue	50	150
27-Oct	Carl	5	Sun	100	500
15-Nov	Albert	4	Fri	50	200
19-Nov	Albert	1	Tue	50	50
19-Nov	Albert	4	Tue	50	200
5-Nov	Bernard	4	Tue	50	200
7-Nov	Bernard	5	Thu	50	250
20-Nov	Albert	2	Wed	50	100
21-Nov	Albert	4	Thu	50	200
28-Oct	Carl	3	Mon	50	150
11-Nov	Lewis	4	Mon	50	200
22-Nov	Lewis	2	Fri	50	100
26-Oct	Bernard	2	Sat	50	100
28-Oct	Bernard	4	Mon	50	200
30-Oct	Bernard	3	Wed	50	150
2-Nov	Bernard	1	Sat	50	50
9-Nov	Bernard	4	Sat	50	200
14-Nov	Bernard	1	Thu	50	50

SHEET 2:

Date	Name	Hours Worked	Day of Week	Rate	Amount








			Average
			Maximum

Questions

Find the name Lewis and replace it with Martin.
Sort the data according to name in Ascending
Insert a row at each change of name.
Type the heading ‘Average’ in cell E13.
Adjust the width of column E to view the contents in full.
Copy the heading in E13 to cells: E22, E38, E50, E61, E74, E87, E98 and E109.
Use the AVERAGE function to calculate the average sales of all salesmen.
Insert four blank rows above row 1.
Insert a picture (Logo) into the worksheet.
Resize the picture to Height 0.62 and Width 3.76 inches.
Position the picture such that the bottom left corner of the picture is within cell A4.
Cut Bernard’s details (A18:F26) and paste them in Sheet 2, starting from Cell A2.
In cell E11 type the title ‘Maximum’.
Use a function to extract the maximum amount that Bernard has ever earned.
Save the file as Overtime analysis.

PRACTICAL EXERCISE 1.14

The following is a simple payroll:

	A	B	C	D	E	F	G	H	I
1	Name	Hours Worked	Hourly Rate	Basic Pay	Gross Pay	Tax Deductions	NSSF Contributions	Allowances	Net Pay
2	John	8	200
3	Peter	12	450
4	Sam	22	300
5	Njogu	30	286
6	Mary	16	220
7	Sally	45	468
8	Jane	15	150
9	Tina	3	280
10
11

Required:

Write formulae using cell names for the following expressions. State where the formula is placed.

Basic Pay = Hours Worked * Hourly Rate.
Allowances are allocated at 10% of the Basic Pay.
Gross Pay = Basic Pay + Allowances.
Tax Deduction is calculated at 20% of the Gross Pay.
Net Pay = Gross Pay – Tax Deductions. (10 marks)

PRACTICAL EXERCISE 1.15

The data below represents day sales of a certain wholesale shop in Sultan Hamud.

Enter the details into a worksheet using a spreadsheet package, and use it to answer the questions that follow. (4 marks)

Item	Opening Stock	Closing Stock	Sold Items	Buying Price	Selling Price
Sugar (bags)	250	130		2,500	2,650
Unga (ctn)	340	120		400	450
Salt (ctn)	271	107		200	250
Kimbo (ctn)	300	210		1,150	1,200
Blue band (ctn)	250	30		220	265
*GRAND TOTAL*

(a). Adjust the columns to fit the contents. (2 marks)

(b). The heading row should be Size 12 and Bold. The rest should be size 10. (2 marks)

(c). Apply the borders around the table as shown. (3 marks)

(d). Insert two new columns after the column for Selling Price. Enter the titles ‘Total’ and

‘Profit’ (2 marks)

(e). The column for the Totals should be Italic and shaded in blue. (3 marks)

(f). Position the contents in the totals column at the center of the cells. (2 marks)

(g). Add thousand separators where necessary, and set the number of decimal places to be zero. (2 marks)

(a). Calculate the number of items sold for each. (2 marks)

(b). Calculate the total sales for each item on that day. (3 marks)

(c). Calculate the total sales for that day (the grand total sale)? (2 marks)

(d). What was the profit gained in each group of items? (2 marks)

(e). Calculate was the total profit the wholesale made that day. (2 marks)

(f). Save the changes to your document. (1 marks)

Down the worksheet, create another table with the same contents and respond to the following:
1. Show what would happen if the buying price of sugar was increased by 2 percent. (2 marks)
2. Save the document as Income and Expenditure. (1 mark)

PRACTICAL EXERCISE 1.16

You have been asked to analyse the rainfall pattern of a city called Mooncity. The following data, which shows the average weekly and monthly rainfall, has been provided.

Table 1.

Weekly Rainfall of Mooncity in mm.

MON	TUE	WED	THUR	FRI	SAT	SUN
10	5	30	20	15	0	50

Table 2.

Monthly Rainfall of Mooncity in mm.

JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
30	25	55	100	60	40	35	60	35	25	20	10

Enter Table 1 and Table 2 into Microsoft Excel and Save as Mooncity (6 marks).
Using the most appropriate formulas and functions,
Calculate the total rainfall for the week and the year respectively. (4 marks).
Find the lowest rainfall for the week and the year respectively. (2 marks).
Find the highest rainfall for the week and the year respectively. (2 marks).
Find the mean rainfall for the week and the year respectively. (4 marks).
Save the changes made to the worksheet. (2 marks).
Create a 3-D column chart for Table 1. The title for the graph should be AVERAGE DAILY RAINFALL. The title for the X-axis should be DAY while that of the Z-axis should be labeled as RAINFALL (mm). The chart should be inserted as an object within the worksheet. Format the chart appropriately (10 marks)

PRACTICAL EXERCISE 1.17

You are in charge of a young and growing company. You have identified the various factors (sources of revenue and expenses) that influence the company business as shown below.

All the figures are in thousands of Kenya Shillings except the percentages.

ANNUAL INCOME AND EXPENSES PROJECTION
Revenue
Sales	Starts at 10,000 and grows by 8%
Expenses
Raw materials	15% of sales
Transport	7% of sales
Electricity	2.1% of sales + 10
Rent	Starts at 120 then grows by 2%
Salaries	Starts at 1,000 then grows by 10%
Commissions (Sales)	1.8% of sales
Loan repayment	170
Advertising and promotion	2.5% of sales
Depreciation	Fixed at 20
Miscellaneous	Starts at 10 and grows by a fixed amount of 10
Total (Expenses)
Profit

Use the information provided in the table above to produce a financial projection model for the company for the next six years. (30 marks)
Format all the projected figures into Kshs. (3 marks)
Make a line graph of sales, total expenses and profit for the six year period. (10 marks)
Find the average expenses, sales and profit for the six year period. (5 marks)

Print the worksheet and the graph..

(Visited 6,185 times, 2 visits today)