CIS 110 Principles Information System Exam 4

Description


Part 1


– Create your file and Import your data


  1. Start by creating your own original new file using Excel 2016 with the name Yousif.Mikha_Exam4.xlsx



    Verify that your system date and time are correct before creating your file.

    Files with a created date before the assignment period will not be scored.


    Files not created in 2016 may not earn full credit!


  2. Enter

    the appropriate Excel

    header and footer elements

    in the locations below.

    The only part you should type is your name.





  3. Each student has a unique data file for this assignment.


    Left Click the link below to create your file. A download link will appear after you click the link and your file has been created.

    By clicking


    Create My Data File


    the button below you agree to follow the guidelines for this assignment.



    Click once

  4. Import the data file that you downloaded and add 2 rows


    The file is a

    Comma Delimited file

    so be sure to select that option during the import.

    Use the

    Load To

    option in the import wizard and load the data into

    cell A3

    .

    Use the

    Convert to Range

    option to convert the table to a range.

    At this point your file should look something like this with

    417 rows of data




  5. Add 3 document properties

    via the Document Properties panel.


    Author

    :

    yousif.mikha



    Title

    :

    Exam 4 Spring 2019



    Comments

    : location where you completed the exam examples

    if you completed it at home then list – ”

    my home computer



    if you complete it on campus then list the room and computer number

    examples – ”

    Cuyamaca E206 system 35

    ” or ”

    Grossmont 540 system 35



    Tech Mall examples – ”

    Cuyamaca Tech Mall system 17

    ” or ”

    Grossmont Tech Mall system 19


  6. Save

    the file with the name

    Yousif.Mikha_Exam4.xlsx


Part 2


– Layout, Format and Enter Formulas –


Revenue


Worksheet

Modify the worksheet to format and calculate the revenue with a discount


  1. Worksheet data entry,

    the following cells should contain information as follows:




    cell G2

    contains the text

    Markup

    .


    cell G3

    contains the text

    Order Total

    .


    cell H2

    contains the value

    15%

    formatted as percent with 0 decimal places

    .

    cell H3

    contains the text

    Sale Price

    .

  2. Add

    the title

    your last name

    Revenue in cell A1. (e.g.

    Mikha Revenue

    ).

  3. Change

    the title font size in cell A1 to 18 points.

  4. Calculate Order Total

    in column G for all 416 items.

    The Order Total is the Quantity times the Unit Price.

  5. Calculate Sales Price

    in column H for all 416 items.

    The Sales Price is the

    Order Total

    times (1 +

    Markup

    ) +

    Quantity

    times

    Shipping

    .

  6. Merge and Center

    the

    title in cell A1

    across only the data columns of the worksheet.

  7. Add like breaks

    between words in all column titles with 2 words by using a line break.

    Do not use wrap lines.

  8. Change

    all column widths to 20 to verify that the text still wraps correctly.

    Then set columns

    A, C, D, E, and F to a width of 10


    and adjust columns

    B, G, and H to the width of 13

    .

  9. Bold

    all column headings in row 3.

  10. Align

    all the column titles vertically at the top and horizontally centered.



  11. Theme

    make sure you are using the standard


    Office


    theme and set the background color of cell A1 to

    Green, Accent 6 Darker 25%

    and the text color to

    White.

  12. Change

    the background color of cells A3:H3 to

    Green, Accent 6 Lighter 60%

    .


    View Formatted Example of rows 1 – 3 to this point

  13. Rename

    the worksheet tab to


    Revenue.


  14. Change

    the tab color to

    Green, Accent 6, Lighter 60%

    .

  15. Freeze

    panes –

    Column A

    and

    Rows 1-3

    should remain visible when you scroll the worksheet either horizontally or vertically

  16. Enter formulas

    at the bottom of the


    Order Total

    and

    Sales Price


    columns to calculate the total all items each of those columns and bold the cells.

  17. Enter

    the text

    Totals

    in column F next to the cell containing the formula you added in the previous step. Format the text as bold.

  18. Add the range name


    Revenue_Total

    for the

    Sales Price

    total cell at the bottom of the Sales Price column. The scope of the range name should be the workbook and not restricted to a worksheet. Check the scope using the Name Manager.

  19. Format

    as follows:

    • columns,

      Unit Price

      ,

      Shipping

      – set as

      comma style
    • columns

      Order Total

      ,

      Sales Price

      – set as

      comma style

      with 0 decimal places

    • column D

      – Short Date

    • cell H2

      – Percent

  20. Change

    the worksheet view to the Normal view (if needed).

  21. Save

    the workbook before continuing.


Part 3


– What-If Analysis –


Analysis


Worksheet


  1. Copy

    the

    Revenue worksheet

    to a new tab changing tab name to

    Analysis

  2. Change

    the tab color to

    Orange, Accent 2, Lighter 60%

  3. Change

    the text in cell A1 to

    Analysis

  4. Change

    background fill color the cell A1 to

    Orange Accent 2, Darker 25%

  5. Add the range name


    Analysis_Total

    for the Sales total revenue cell at the bottom of the column. The scope of the range name should be the workbook and not restricted to a worksheet.

  6. Use Goal Seeking

    to adjust the

    Markup

    value in cell H2 so that the

    Sales Price total value is $40,000 higher

    than the value in the Revenue worksheet.

    – You must use goal seeking.

    – Be sure the column is wide enough to display the result at the bottom of the column.

  7. Format

    the data area

    A3:H419

    including the column headings but not the totals at the bottom of the columns as a Table using style

    Medium 3

    (1st row in Medium list, column 3)


    – Select Yes

    if you receive the message “Your selection overlaps one or more data ranges. …”

  8. Change

    the fill color for the column headings in row 3 to match row 1. –

    see example


Part 4


– Sorting and Filtering –


Filtered


Worksheet


  1. Copy

    the

    Revenue worksheet

    to a new tab and name it

    Filtered

  2. Change

    the tab color to

    Blue, Accent 1, Lighter 60%

  3. Change

    the text in cell A1 to

    Filtered

  4. Change

    background fill color the cell A1 to

    Blue, Accent 1, Darker 25%

  5. Delete

    the totals and adjacent text at the bottom of the data rows in the Filtered worksheet so only the sales remain.

  6. Add a filters

    for the column headings in row 3 of the Filtered worksheet.

  7. Using the Sort button on the ribbon,

    add a Custom Sort


    Sort

    the data by the

    Category Name in ascending order (A to Z)

  8. Add a secondary sort

    on the

    Quantity in ascending order (Smallest to Largest)

  9. Add Subtotals

    for

    Order Total and Sales Price

    with each change in

    Category Name

    and include Summary below the data

  10. Change

    the filter to only show the

    Meat/Poultry Category and Meat/Poultry Total

    by selecting those values from the filter list


    see example

  11. Add the range name


    Filtered_Total

    for the Grand Total in the Sales Price column. The scope of the range name should be the workbook and not restricted to a worksheet.


Part 5


– Create Totals Worksheet


  1. Copy

    the

    Revenue

    worksheet to a new tab and name it

    Totals

  2. Change

    the tab color to

    Gold, Accent 4, Lighter 60%

  3. Change

    the text in cell A1 to

    Totals

  4. Change

    background fill color the cell A1 to

    Gold, Accent 4, Darker 25%

  5. Delete

    the totals and adjacent text at the bottom of the data rows in the Totals worksheet so only the sales remain.

  6. Using the Sort button on the ribbon,

    add a Custom Sort


    Sort

    the data by the

    Category Name in ascending order (A to Z)

  7. Add a secondary sort

    on the

    Order Total in descending order (Largest to Smallest)

  8. Add Subtotals

    for each change in Category Name with a sum of the

    Order Total and Sales Price


    Note

    : You cannot add subtotals if you format your worksheet as a table!

  9. Collapse

    the outlines to Hide Detail so that only the subtotals are displayed


    see example

  10. Add the range name


    Grand_Total

    for the Grand Total of the Sales Price cell. The scope of the range name should be the workbook and not restricted to a worksheet.


Part 6


– Create a 3D Pie Chart


  1. Create a 3D Pie Chart

    of the category names

    Beverages to Seafood and their associated Sales Price subtotals

    from the Totals worksheet

  2. Move the chart

    to a new worksheet named Pie Chart
  3. Change the chart style to

    Style 8

  4. Add


    Percentages

    and

    Category Name

    as the only data labels and set them to display on the outside edge of the chart

  5. Change

    the

    chart title



    2019 SALES BY CATEGORY




    see example

  6. Change

    the name of the chart worksheet tab to

    Pie Chart

    if needed


Part 7


– Adding Summary


Worksheet


  1. Add a New Worksheet

    and named

    Summary

  2. Change

    the Summary worksheet tab color to

    Dark Red

    (in the Standard Colors)

  3. Enter

    the title

    Exam 4 Summary

    in cell A1 and copy the format from cell A1 in the Revenue worksheet.

  4. Enter text

    in the cells as indicated below

    A B C D
    1 Exam 4 Summary
    2
    3
    Worksheets

    Value

    Markup %

    Range
    4 Revenue
    5 Analysis
    6 Filtered
    7 Totals

  5. Bold

    the text in cells A3:D3, Center the text in cell B3

  6. Indent

    the content of cells A4:A7

  7. Add the formulas

    listed below to reference the named cells as follows:

    cell B4

    =

    Revenue_Total



    cell B5

    =

    Analysis_Total



    cell B6

    =

    Filtered_Total



    cell B7

    =

    Grand_Total


  8. Format

    the values in column B using the

    comma style

    format with 0 decimal places.

    Then adjust the column width so the values are correctly displayed

  9. Insert a 3D references in cells C4 & C5

    to the Markup values from the Revenue and Analysis worksheets and format them as percent with 0 decimal places.

  10. Paste the range names

    starting in cell D4 using the

    Paste List

    command.

  11. Adjust the column width

    so the names are completely displayed

  12. Create a hyper link

    to each worksheet from the text in cells A4:A7; i.e. link the text

    in cell A4 (Revenue) to the Revenue worksheet

  13. Arrange

    the worksheet tabs in the following order from left to right


    Summary, Revenue, Analysis, Filtered, Totals, Pie Chart



    see example

  14. Select

    the Revenue worksheet.

  15. Save

    the file and exit Excel

  16. Upload

    the file

    Yousif.Mikha_Exam4.xlsx

    to the class Drop Box using the

    Exam 4 link

How is this assignment graded?

  • Follow instructions: naming worksheets, correct placement of discount percentage, headers, footers
  • Correct use of formulas: using cell references, functions including Sum and absolute references
  • Formatting worksheet: layout, margins, header/footer, style
  • Proper use of goal seeking to correct value. Is the Total Revenue in the Discount worksheet exactly $40,000 greater than the Total Revenue in the Revenue worksheet?
  • Create a chart as a worksheet from the sorted worksheet with subtotals.
  • Data is filtered and correctly sorted


Upload

the saved file as

Yousif.Mikha_Exam4.xlsx

as the

Exam 4

assignment



99% penalty



– for files with circular references

.


Resubmit

– This assignment

CAN be resubmitted

1 time to improve your score.

Grading Rubric
Points Requirements
22
Revenue worksheet


header/footer, document properties, formatting, formulas, freeze panes
10
Analysis worksheet


Goal Seek, format table
15
Filtered worksheet


Sorting, Subtotals, Filter
9
Totals worksheet


Sorting, Subtotals, Outline
9
Pie Chart


Move Chart, Set Style, Title, Data Labels
12
Summary worksheet


Formulas with range names, Hyper links, Paste names, formatting
3 Worksheet tab color and order correct

80

Total possible for uploading – Yousif.Mikha_Exam4.xlsx
We offer the bestcustom writing paper services. We have done this question before, we can also do it for you.

Why Choose Us

  • 100% non-plagiarized Papers
  • 24/7 /365 Service Available
  • Affordable Prices
  • Any Paper, Urgency, and Subject
  • Will complete your papers in 6 hours
  • On-time Delivery
  • Money-back and Privacy guarantees
  • Unlimited Amendments upon request
  • Satisfaction guarantee

How it Works

  • Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
  • Fill in your paper’s requirements in the "PAPER DETAILS" section.
  • Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
  • Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
  • From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.