WORKING WITH EXAMPLES PRACTICALLY

Download and install the following softwares that we shall be using while studying this course.

1. Microsoft Office 2010 https://drive.google.com/file/d/18p9ZHoxhRRFy01BfmLemjccMiFhRowbd/view?usp=sharing
2. Microsoft Expression Web https://drive.google.com/file/d/1sMZzh0d1B6tZdrqsJobAVK961bexiMRo/view?usp=sharing
3. For zipping and unzipping use this software (Winrar) https://drive.google.com/drive/folders/10QCneHc39W-xZJwyK6NvmwbcY62rajNS?usp=sharing

PRACTICAL QUESTION ONE

Below is the worksheet showing Austin Citizens High School Leavers’ party budget. Use any of the spread sheet programs and type in the work in the table below before you follow the instructions provided in the required list.

1. Save your work as Your Name – Leavers Budget

Required:

1. Use a function or a formula to calculate the Total Price in each item
2. Use appropriate functions to calculate:
   • Average Total Price
   • Lowest Total Price
   • Highest Total Price
   • Grand Total Price
3. If the Leavers made a contribution of 1,500,000 as indicated in the worksheet, use a formula to calculate the balance to remain after all the purchases.
4. Use the IF() function to display comments as “High Price” if the item’s price is 10000 and above, “Normal Price” if the item’s price is above 5000 and “Low Price” if the item’s price is 5000 and below
5. Apply a thick green border with red grids inside
6. Create a pie chart based on Items against Price fields showing labels and price variation in percentages. Save it as Pie-Chart.
7. Put your full name and class as footer and make printout.

PRACTICAL QUESTION TWO

(b) The table below shows examination results of senior four students.

NB:   – BOT = Beginning of term mark and EOT = End of term mark.

– The final mark is a summation of BOT, MID- TERM and EOT.

1. Enter the students’ data above in a spread sheet program and save it as Performance –Todays Date
2. Determine the final score for every student by use of a formula.
3. Determine students’ position in a new column basing on their final marks
4. Calculate the square root of Rehema’s total mark in cell F12
5. Add a column called GRADE and use the final mark column to assign for grade as follows From 80-100 assign A, 70-79, “B”, 60-69 “C”, 50-59, “D”, 40-49, “E” and 00-39, “F” using the IF function
6. Insert a Green thick border with blue grids inside around your work.
7. Insert a header “STUDENTS PERFORMANCE” and a footer of your Names and centre them.
8. Copy and paste your work to work sheet 3.
9. Write your Names below the work on sheet 3
10. Select the names, EOT and Final Mark columns to form a column graph and save it as My Graph.
11. Convert the EOT marks into a line graph
12. Save changes and Print your work.

PRACTICAL QUESTION THREE

Using a suitable software package, type the file that has been created to calculate the profit or loss on sales at Jinja Orange Juice Stall during a six day period. Save it Days Sales.

1. Using absolute and relative cell references, type a formula in cell D6 to calculate the total cost for Monday which is a cups made. Copy it cells D7 to D11 for Tuesday to Saturday.
2. Using absolute and relative cell references, type a formula in cell E6 to calculate the Day’s Sales for Monday, as a product of the selling price and the number of Orange Juice cups sold. Copy it to cells E7 to E11 for Tuesday to Saturday.
3. In cell F6 to F11, calculate the profit/loss by subtracting the Total Cost from the Day’s Sales.
4. Format all currencies to have the ‘UGX’ symbol before the vale.
5. Use a function to compute the GRAND TOTALS in row 13.
6. Make the title in font size 22, bolded in blue colour., Fill your title with a light green background colour
7. Create a combined column-line combo chart with column chart representing the number of orange juice cups made in a weeks’ time and line graph showing trend of profits / loss on the secondary vertical axis. Locate it on its own new sheet.
8. In cell F3 on sheet 1, calculate the second highest profit made.
9. Shade the Grand totals with an orange background colour.
10. Add your name as footer and print your work.

PRACTICAL QUESTION FOUR

St Joseph’s of Nazareth High School has decided to do two kinds of tests marked out of 40 and 60 respectively. Use the information given below to answer the questions that follow.

Required

1. Using a spread sheet package of your choice type the above data as exactly it appears and save it as Students Records in your folder.
2. Calculate the total mark for each student

• Using the “if statement, assign grades to students using their Total marks in a way that , marks above 80, assign a D1,75-79, a D2, 70-74, a C3, 60-69, a C4, 50-59, a C5,the rest an F

1. Add a column comment and determine the comments as follows; 80+ = “Excellent”, 70-79 = “Very Good”, 60-69= “Tried”, 50-59 = “Fair” otherwise “Work Hard”.
2. Apply red all borders and also make a printout.
3. Select the names, set one and total columns to form a column chart with appropriate title and labels. Save it as Pictorial Analysis.

• Using this chart, convert the values for set one into a line graph.
• Save changes and make print out

PRACTICAL QUESTION FIVE

Using a suitable software package, type the file that has been created to calculate the profit or loss on sales at Jinja Orange Juice Stall during a six day period. Save it Days Sales.

1. Using absolute and relative cell references, type a formula in cell D6 to calculate the total cost for Monday which is a cups made. Copy it cells D7 to D11 for Tuesday to Saturday.
2. Using absolute and relative cell references, type a formula in cell E6 to calculate the Day’s Sales for Monday, as a product of the selling price and the number of Orange Juice cups sold. Copy it to cells E7 to E11 for Tuesday to Saturday.
3. In cell F6 to F11, calculate the profit/loss by subtracting the Total Cost from the Day’s Sales.
4. Format all currencies to have the ‘UGX’ symbol before the vale.
5. Use a function to compute the GRAND TOTALS in row 13.
6. Make the title in font size 22, bolded in blue colour., Fill your title with a light green background colour
7. Create a combined column-line combo chart with column chart representing the number of orange juice cups made in a weeks’ time and line graph showing trend of profits / loss on the secondary vertical axis. Locate it on its own new sheet.
8. In cell F3 on sheet 1, calculate the second highest profit made.
9. Shade the Grand totals with an orange background colour.
10. Add your name as footer and print your work.

PRACTICAL QUESTION SIX

St Joseph’s of Nazareth High School has decided to do two kinds of tests marked out of 40 and 60 respectively. Use the information given below to answer the questions that follow.

Required

1. Using a spread sheet package of your choice type the above data as exactly it appears and save it as Students Records in your folder.
2. Calculate the total mark for each student

• Using the “if statement, assign grades to students using their Total marks in a way that , marks above 80, assign a D1,75-79, a D2, 70-74, a C3, 60-69, a C4, 50-59, a C5,the rest an F

1. Add a column comment and determine the comments as follows; 80+ = “Excellent”, 70-79 = “Very Good”, 60-69= “Tried”, 50-59 = “Fair” otherwise “Work Hard”.
2. Apply red all borders and also make a printout.
3. Select the names, set one and total columns to form a column chart with appropriate title and labels. Save it as Pictorial Analysis.

• Using this chart, convert the values for set one into a line graph.
• Save changes and make print out

PRACTICAL QUESTION SEVEN

From the payroll of KAMPALA COMPUTER ENTERPRISES, for the month of February, Enter the following data as it appears in the table below in a worksheet and save as ‘KLACOMPUTERENT’                                                            

1. Due to inflation, the management increased the wages of employees who earn 300,000 and below by 20% and for those who earn above 300,000 by 15% for month of Septemb Using an appropriate function, compute the new wage for each employee in the month of September.
2. Add a columns for SEP PAYE, SEP NSSF, SEP NET PAY after SEP WAGES
3. Calculate the Pay as You Earn (PAYE) tax given that its rate is 8% of the Gross wag There after calculate the second oldest age in cell B9
4. NSSF is calculated at 3% of Gross wag Calculate the NSSF savings for September per employee.
5. Use a suitable formula to generate the NET PAY that will be available for each employ
6. In the TOTALS row, compute the Totals for all the columns having currencies.
7. In cell C10 use the count if function to compute for how many are managers.
8. Add “Shs” symbol as a suffix to your values in the table.
9. Apply a green line colour “all borders” outline to all cells with dat
10. Select the Names Column September Wages column to generate a 3-D clustered column graph with appropriate labels to be saved as Employee Pictorial Data.
11. Fill your bars with a green horizontal brick pattern.
12. Add your name and class as a right aligned footer and print your work in a landscape orientation.

PRACTICAL QUESTION EIGHT

This information was extracted from Kyabaleta high school. Study it carefully to answer the questions that follow.

Other information:

Selling price = Cost Price + School Tax + Expenses + Profits.

Expenses are =40% of the selling price, Profits is 30% of the selling price.

School tax is 10% of the selling price.

Tasks:  As someone who appreciates spread sheets principle and functions;

• Enter the above information in a spread sheet application of your choice. Save it as with your Real Name.
• Determine the amount of tax the school gets from the canteen, profits and expenses for each item.
• Determine the average and total amount of tax the school collects from the canteen.
• Change the name of sheet 1 to Canteen Sales.
• Determine the cost price for all items.
• At the beginning of next year the canteen operators have asked the school management that he is going to increase the selling prices by 20%. Insert a new column to work out the new prices for all canteen items next year.
• On a separate sheet, plot a line chart to represent the items, cost price, and selling price.
• On sheet three show all the formulas used and Apply a centred footer of your name and registration number in your worksheet
• Copy and paste your work on sheet two, and add Shs currency symbol.

PRACTICAL QUESTION NINE

(b) Create an Excel work book named “Spread sheet Analysis” and carry out the following tasks.      Insert/ type the data exactly as given below

Give that the employees who belong to Salary Scale of U1 earn 2,000,000, U2 earn 1,500,000, U3 earn 1.000,000, U4 earn 800,000 and those of U5 earn 300000. Use any function to populate basic salary in the worksheet.

• Use functions to determine GROSS SALARY, NSSF and NET SALARY given that:
• GROSS SALARY=BASIC SALARY + OVERTIME ALLOWANCE
• NSSF is 18% of basic salary for basic salary above 1000000 and 14% of basic salary otherwise.
• NET SALARY=GROSS SALARY – NSSF
• Format the values to have “¥” Chinese PRC symbol as their suffix with no decimal place value.
• Type the labels U1, U2, U3, U4 and U5 each in the series A22:E22.
• Use function to discover the number of employees falling in the different salary scales corresponding to U1, U2, U3, U4 and U5 each in the series A23:E23.
• Use the data discovered in the instructions (VI) above to plot a column graph of salary scale against number of employees. Save it as My Graph with well labelled axes.
• Insert your name and personal number in the header.
• Save your work as salary and print.

PRACTICAL QUESTION TEN

The Police report on the number of deaths as a result of road accidents in various towns of the country in the past years was as follows.

In 2014, Tororo-144, Kampala-356, Kabale-88, Gulu-56, Jinja-70, Mbarara- 289

In 2017, Tororo-200, Kampala-500, Kabale-120, Gulu-93, Jinja-150, Mbarara-300

In 2018, Tororo-211, Kampala-519, Kabale-120, Gulu-100, Jinja-148, Mbarara-159

1. Use a suitable spread sheet program to enter the above data in one sheet. Save your work as Road accidents.
2. Use appropriate formulae to determine the total and average number of accidents in each town for the three years. Label the columns TOTAL and AVERAGE respectively.

• The towns whose average number of accidents exceeds 150 are noted. Insert a column “STATUS” and use IF function to indicate “INSECURE” for towns whose average number of accidents exceeds 150, else “SAFE”.

1. Add all boarders to your work.
2. Align your row headings at an angle of -60⁰.
3. Create a column Chart to represent the towns and number of accidents in the three years.

• Save the graph as My Pictorial.
• Add a footer of your name and index number on sheet1.

1. Save and print your work.

PRACTICAL QUESTION ELEVEN

Open a suitable application program and enter the data below exactly as it appears and save it as Absolute Day Book on the Desktop in a folder.

Additional information:

–Gross pay is the equivalent of basic pay plus all allowances

–Net pay is a function of gross pay minus tax

• Using absolute cell referencing, calculate the Revenue Tax for all employees having in mind that it is 10% of basic pay.
• Calculate the Gross Pay, Net Pay.
• Determine the Second highest tax payer by use of a function
• Rank all the employees basing on their basic pay.
• All employees whose revenue tax is less than 20,000/= are categorized as “Form” while others are categorized as “Substance”. Use a simple if function to determine the category to which each of employee.
• Select the Names column and Revenue Tax Column and generate a Pie and Save it as Pie Chart.
• Let all your columns with amount values be formatted to SHS currency symbol, with commas and No decimal place value.
• Using COUNTIF in cell B10, calculate those who earn basic salary above 300000.
• Filter out those whose net pay is above 500,000. Save it as Class A.
• Print Your Work on a landscape page orientation