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  21Nov  Carl  Toys  1238  1,782.10 
5  26Nov  Carl  Stationery  1255  4,853.55 
6  26Nov  Carl  Toys  1395  51.35 
7  Carl’s Total  
8  21Nov  John  Cards  1141  91.15 
9  24Nov  John  Books  1982  442.60 
10  21Nov  John  Toys  1885  561.50 
11  26Nov  John  Toys  1875  62.75 
12  John’s Total  
13  22Nov  Judy  Books  1032  234.50 
14  26Nov  Judy  Sports goods  1920  472.60 
15  Judy’s Total  
16  25Nov  Mary  Toys  1774  364.15 
17  Mary’s Total  
18  22Nov  Susan  Electronics  1160  52.95 
19  23Nov  Susan  Cards  1075  81.60 
20  23Nov  Susan  Others  1745  132.95 
21  24Nov  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 AE 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  26Nov  Kennedy  5  70  350.00 
5  26Nov  Kennedy  5  100  500.00 
6  26Nov  Mary  5  100  500.00 
7  26Nov  Lewis  4  100  400.00 
8  30Nov  Judy  3  100  300.00 
9  30Nov  Kennedy  6  70  420.00 
10  30Nov  Lewis  5  100  500.00 
11  30Nov  Kennedy  4  70  280.00 
12  30Nov  Judy  5  100  500.00 
13  30Nov  Lewis  5  100  500.00 
14  02Dec  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  
Pretax 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 Pretax 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 Pretax Income.
Sales – Total Operating Cost – Interest on Loans.
 Calculate the Tax.
 Calculate the Profit:
Pretax 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 3D 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 Yaxis should be Employee Names and the Xaxis 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 3D visual effect.
 Increase the Chart Title Font size to 14.
 Change the text direction for the title of the Xaxis to 0 degrees, and for the Yaxis 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 
27Oct  Albert  4  Sun  100  400 
8Nov  Albert  3  Fri  50  150 
18Nov  Lewis  4  Mon  50  200 
9Nov  Albert  4  Sat  50  200 
12Nov  Albert  3  Tue  50  150 
27Oct  Carl  5  Sun  100  500 
15Nov  Albert  4  Fri  50  200 
19Nov  Albert  1  Tue  50  50 
19Nov  Albert  4  Tue  50  200 
5Nov  Bernard  4  Tue  50  200 
7Nov  Bernard  5  Thu  50  250 
20Nov  Albert  2  Wed  50  100 
21Nov  Albert  4  Thu  50  200 
28Oct  Carl  3  Mon  50  150 
11Nov  Lewis  4  Mon  50  200 
22Nov  Lewis  2  Fri  50  100 
26Oct  Bernard  2  Sat  50  100 
28Oct  Bernard  4  Mon  50  200 
30Oct  Bernard  3  Wed  50  150 
2Nov  Bernard  1  Sat  50  50 
9Nov  Bernard  4  Sat  50  200 
14Nov  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:
 Show what would happen if the buying price of sugar was increased by 2 percent. (2 marks)
 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 3D column chart for Table 1. The title for the graph should be AVERAGE DAILY RAINFALL. The title for the Xaxis should be DAY while that of the Zaxis 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..