CS3: Assignment 2 On Spreadsheets

PRACTICAL QUESTION ONE

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 TWO

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 THREE

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 September 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.