Download Microsoft Excel: Practical Exercises and more Exercises MS Microsoft Excel skills in PDF only on Docsity!
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, 3 Lunch^ 20,163^ 21,416^ 19,912^ 19,681^ 18, 4 Bar 9,873 12,172 12,642 12,711 18, 5 Snacks 2,405 3,544 2,694 3,120 3, 6 TOTALS PRACTICAL EXERCISE 1. 2 Enter the data given below into a worksheet. A B C D E
1 Stationery Supplies Ltd
3 Date SalesPerson Item Receipt No Amount 4 21 - Nov Carl Toys 1238 1,782. 5^26 - Nov^ Carl^ Stationery^1255 4,853. 6 26 - Nov Carl Toys 1395 51. 7 Carl’s Total 8 21 - Nov John Cards 1141 91. 9 24 - Nov John Books 1982 442. 10 21 - Nov John Toys 1885 561. 11 26 - Nov John Toys 1875 62. 12 John’s Total 13 22 - Nov Judy Books 1032 234. 14 26 - Nov Judy Sports goods 1920 472. 60 15 Judy’s Total 16 25 - Nov Mary Toys 1774 364. 17 Mary’s Total 18^22 - Nov^ Susan^ Electronics^1160 52. 19 23 - Nov Susan Cards 1075 81. 20 23 - Nov Susan Others 1745 132. 21 24 - Nov Susan Sports goods 1662 2,580. 22 Susan’s Total 23 24 Grand Total (i). Calculate the totals for each salesperson and get the grand total.: (ii). 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. (iii). Put a double border round the whole table and a single line border inside the table. (iv). 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. 5^26 - Nov^ Kennedy^5 100 50 0. 6 26 - Nov Mary 5 100 500. 7 26 - Nov Lewis 4 100 4 00. 8^30 - Nov^ Judy^3 100 300. 9 30 - Nov Kennedy 6 70 42 0. 10 30 - Nov Lewis 5 100 5 00. 11 30 - Nov Kennedy 4 70 28 0. 12 30 - Nov Judy 5 100 5 00. 13 30 - Nov Lewis 5 100 5 00. 14 02 - Dec Judy 4 70 280. 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
3 Name Basic Pay Allowances Gross Salary Deductions Net Salary
4 Lewis 5 Francis 6 Edwin
.. .. .. 13 Totals
SHEET 1:
INCOME AND EXPENSES PROJECTIONS
Sales 10,
% 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.
(i). Open the worksheet named Income and Expenses Projections.xls. (ii). Rename Sheet1 as Projections while Sheet 2 should now be Parameters. (iii). Calculate the Sales for the year 2000 using the percentage given in cell C. (iv). Copy the formula across to the Year 2004. (v). 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 (vi). Calculate the Total Operating Costs: Total Cost of Goods Sold + Total General and Administrative Expenses. (vii). Calculate the Interest on Loans: (viii). Calculate the Pre-tax Income. Sales – Total Operating Cost – Interest on Loans. (ix). Calculate the Tax. (x). Calculate the Profit: Pre-tax Income - Tax. (xi). Format the worksheet as follows: Make all the Totals bold, zero decimal places, comma, center the heading between A1:G and make it size 16, bold. (xii). 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
3 Salesman Qtr1 Qtr2 Qtr3 Qtr4 Total
4 Albert 148 156 171 140 615 5 Carl^122 131 153 118 6 Cornell 211 243 246 250 950 7 Edwin 129 150 92 218 589 8 Francis 311 270 247 322 1, 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
(xi). Put a double border round the whole table and a single line border inside the table. Shade the column for Average temperature gray. (xii). 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. (xiii). Move the chart created above to Sheet3. Do not insert it as an object. (xiv). Move the left top corner of the chart in Sheet3 to cell A7. Resize the chart to fit into the range A7:h20. (xv). 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.
Rent 4300. Electricity 1000. Telephone 200. Advertisements 20000. Freight and clearing 15650. Security 3800. Questions (i). Insert a new column between Budget and Savings column. (ii). Enter the title ‘Actual’ in cell C3. (iii). 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 (iv). Calculate the savings in cells D 4 :D10. (v). Format the sheet title to Arial Black, size 14, and Bold. (vi). Save the file as Audit 1. (vii). Format the range B4:D10 to two decimal places. (viii). Adjust column C such that all the values are displayed. (ix). Add the title Savings % in cell E3 and calculate the savings as a percentage of the budget. (x). Format the range E4:E10 as a percentage. (xi). Enter the row title Total in cell A12 and obtain totals for Budget, Actual, and Savings columns. (xii). Copy the formula in E10 to E12.
(xiii). Format the new heading to match the existing headings and Align the heading to Right. (xiv). 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,
% Growth over the previous year 20% 30% 20% 10% 10% Materials 1,700 2,040 2,652 3,182 3,501 3, Wages 1,400 1,680 2,184 2,621 2,883 3, Other benefits^210 252 328 393 432 Others 100 108 117 126 136 147 Total Cost of Goods Sold 3,410 4,080 5,280 6,322 6,952 7, Questions (i). Insert five rows above Row 1. (ii). Reduce the size of the picture to a Height of 0.96” by a Width of 1.66” (inches). (iii). Drag and drop the picture to fit in the range A1:A5. (iv). Add the Header ‘ Balance Sheet 2001 ’ to the right section of the worksheet. (v). Make the heading size 14, and Bold. (vi). Save the file as Balance Sheet. PRACTICAL EXERCISE 1. 12 Use the worksheet given below to answer the questions that follow: Employee Details
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 (i). Find the name Lewis and replace it with Martin. (ii). Sort the data according to name in Ascending order. (iii). Insert a row at each change of name. (iv). Type the heading ‘ Average ’ in cell E. (v). Adjust the width of column E to view the contents in full. (vi). Copy the heading in E13 to cells: E22, E38, E50, E61, E74, E87, E98 and E109. (vii). Use the AVERAGE function to calculate the average sales of all salesmen. (viii). Insert four blank rows above row 1. (ix). Insert a picture (Logo) into the worksheet. (x). Resize the picture to Height 0.62 and Width 3.76 inches. (xi). Position the picture such that the bottom left corner of the picture is within cell A4. (xii). Cut Bernard’s details (A18:F26) and paste them in Sheet 2, starting from Cell A2. (xiii). In cell E11 type the title ‘Maximum’. (xiv). Use a function to extract the maximum amount that Bernard has ever earned. (xv). 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 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. (i). Basic Pay = Hours Worked * Hourly Rate. (ii). Allowances are allocated at 10% of the Basic Pay. (iii). Gross Pay = Basic Pay + Allowances. (iv). Tax Deduction is calculated at 20% of the Gross Pay. (v). 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, Unga (ctn) 340 120 400 450 Salt (ctn) 271 107 200 250 Kimbo (ctn) 300 210 1,150 1, Blue band (ctn) 250 30 220 265 GRAND
(ii). Find the lowest rainfall for the week and the year respectively. (2 marks). (iii). Find the highest rainfall for the week and the year respectively. (2 marks). (iv). Find the mean rainfall for the week and the year respectively. (4 marks). (c). Save the changes made to the worksheet. (2 marks). (d). 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. (2 marks)
