excell

FINAL EXAM - Excel

Note: If the function was not covered in class or in the textbook, it should not be part of your answer. DO NOT go to the internet for solutions. There should not be anything but IF, SUM, MIN, MAX and AVERAGE (if applicable).

1. Insert the following table into a new Excel Workbook, keeping the cell

references consistent. Be sure to keep the cell data the same.

A B C

1 Product. Price Quantity

2 Planters 6.98 206

3 Shears 6.49 109

4 Hoes 12.67 38

5 Spade 14.98 18

6 Hose 9.45 59

7 Watering Cans 3.55 41

8 Wheel barrow 39.98 4

9

10

11 Discounts

12 Discount for 50 items and above 12.2%

13 Discount for 10 items and above 9.3%

14 Less than 10 items 0

2. Save the workbook as yourname_Q1 3. Place a box around cells A11:B14 4. Color the background to these cells

a. You choose the color

CALCULATIONS 5. Add the column title: Pre-Discount Price at cell D1 6. Add the column title: Discount Amount at cell E1 7. Add the column title: Price after Discount at cell F1 8. Calculate the Pre-Discount Price, Discount Price 9. Calculate the Discount amount

a. Use an IF function with ABSOLUTE cell reference to determine the Discount Amount

b. Use the values in cells B12:B14 c. Be sure to copy the same formula to all the cells in the column

10. Calculate the Price after Discount 11. Add 2 rows at the top of the worksheet 12. At A1 enter the title: Beautiful Landscaping 13. Center the title (A1) across the table 14. Change the Font, Font Size and Color of your Title

a. You Choose 15. Center and Bold the column titles 16. Use the AutoFit Option to fix the column widths 17. Change the font, font size and color of table cells

a. You choose 18. Format the table to your liking. 19. Adjust column widths again 20. Change the name of the worksheet to Discounts

a. Color the tab GREEN 21. Change the page orientation to Landscape 22. Be sure to format your numbers appropriately CONDITIONAL FORMAT 23. Use conditional format for the quantity only if the cells have a value less than 40 units

a. format them with a Blue Background and Bold White letters

GRAPH 24. Create a Column Chart showing the Item and Total after Discount

a. Move the Graph to a new worksheet b. Call the worksheet Sales Totals

i. color the tab GREEN c. ADD axis titles d. ADD data labels e. Add a title for the chart

FINISHING TOUCHES 25. Add your name and student ID number to the header of the worksheet with the table 26. Re-Order the tabs so the chart is the 2nd worksheet

27.Change the Document Properties

a. Place your Name b. Subject: CGS1030-SumB c. TAGS – minimum of 3, separated by commas

i. Initials are not key words ii. Excel, final and exercise are also NOT keywords.