Description

Assignment 2 enables hands-on practice with presenting, organizing, and calculating data. You can also use MS Excel to create budgets, track your weekly spending, or create detailed project plans. This assignment gives you an opportunity to reflect upon your recent school supply purchases while producing a professional looking spreadsheet.

Grading ( Click Here to View Rubric )

There are five (5) main items addressed in Assignment 2:

  1. Did you enter all 21 data points into the required cells? Weight 20.00%
  2. Did you use the multiplication formula provided or a multiplication function to correctly calculate cell range D3:D7? Weight 15.00%
  3. Did you use the SUM function as directed or a similar formula to correctly calculate cell D8? Weight 15.00%
  4. Did you generate a pie chart by selecting the correct data ranges, display the appropriate data, and relocate the chart correctly? Weight 10.00%
  5. Did you follow all of the proper formatting for the cells including the use of a theme, merge and center, autofit of columns, Accounting number format, table format, and cell styles? Weight 40.00%

Click the above link to view the rubric detail so that you know exactly what is expected.

Preparation

Do (Click here for a sample of what Assignment 2 looks like completed)

  1. Open MS Excel on your computer: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/43830551/entry_id/1_kitc3ebi/embed/dynamic
  2. Open a new Blank Workbook: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/43830551/entry_id/1_4saimroo/embed/dynamic
  3. Save the blank workbook with the following nomenclature: CIS105_Assignment2_FirstName_Last Name_Date: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/43830551/entry_id/1_u5w9o2ml/embed/dynamic
  4. Type and enter the title My School Supply Purchases in cell A1: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/43830551/entry_id/1_z3g3cpqo/embed/dynamic
  5. Type and enter the following headings into the appropriate cells as indicated: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_tdtaxaps/embed/dynamic
    • Cell A2: School Supplies
    • Cell B2: Quantity
    • Cell C2: Price
    • Cell D2: Cost
  6. Type and enter the following school supplies into the appropriate cells as indicated: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_18yq473x/embed/dynamic
    • Cell A3: TestOut Access Code
    • Cell A4: Spiral Notebook
    • Cell A5: Pencils
    • Cell A6: Pens
    • Cell A7: Highlighters
    • Cell A8: Total Cost
  7. Type and enter the following quantities into the appropriate cells as indicated: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_uztqktj6/embed/dynamic
    • Cell B3: 1
    • Cell B4: 2
    • Cell B5: 10
    • Cell B6: 5
    • Cell B7: 4
  8. Type and enter the following prices into the appropriate cells as indicated: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_w7o8q5g2/embed/dynamic
    • Cell C3: 115.00
    • Cell C4: 1.50
    • Cell C5: 2.00
    • Cell C6: 1.50
    • Cell C7: 2.50
  9. In cell D3, type and enter =B3*C3 to calculate the price OR use the multiplication function in Excel to calculate the price (DO NOT use a calculator and type the total cost into each cell as this is incorrect and this will be graded using the formula bar inputs): https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_52vyrxdo/embed/dynamic
  10. Use the Fill Handle to fill in from cell D3 to cells D4 through D7: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_n4h211pi/embed/dynamic
  11. In cell D8, use the SUM function to get the total cost of all school supplies: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_yilonlok/embed/dynamic
  12. Apply your favorite theme to the spreadsheet using the Page Layout tab: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_q9yau725/embed/dynamic
  13. Select range A1:D1 and use the Merge and Center button on the Home tab to merge and center the title of the spreadsheet over the data: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_5gi9ktcm/embed/dynamic
  14. Select range A8:C8 and use the Merge and Center button on the Home tab to merge and center the Total Cost label under the data: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_2d1yrhap/embed/dynamic
  15. Select the entire spreadsheet and autofit the columns so that the information is showing: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_d5q1xzl5/embed/dynamic
  16. Select columns C and D and change the number format to Accounting: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_x6p3wb2y/embed/dynamic
  17. Select range A2:D7 and apply a table formatting that you like: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_238j3wxb/embed/dynamic
  18. Select range A8:D8 and apply the Total format under Cell Styles: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_kipt7fwz/embed/dynamic
  19. Select cell A1 and apply the Title format under Cell Styles: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_aldp8vt0/embed/dynamic
  20. Create a pie chart by selecting the School Supplies range, A2:A7, and the Cost range, D2:D7, (use the CTRL key to highlight each range separately) to generate the chart: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_a1yj0q2u/embed/dynamic
  21. With the above ranges selected, click on the Insert tab and insert a pie chart under the Charts section: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_6rb5q4us/embed/dynamic
  22. With the pie chart selected, select a chart style on the Chart Tools menu that shows each school supply item and the percentage of the cost: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/38285871/entry_id/1_vtd6dim5/embed/dynamic
  23. Add the name Total School Supply Costs above the pie chart: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/43830551/entry_id/1_94brt4yz/embed/dynamic
  24. Drag and drop the chart underneath the data contents of the spreadsheet in columns A through D with any formatting changes necessary and save your changes to submit your Excel file type (.xlsx) to Blackboard for grading: https://cdnapisec.kaltura.com/index.php/extwidget/preview/partner_id/956951/uiconf_id/43830551/entry_id/1_w0pr0r2w/embed/dynamic