\n<\/p>\n

<\/p>\n\n\n\n\n\n\n\n\n\n

\n 1<\/span> – Create your file and Import your data<\/span><\/p>\n \n Start by creating your own original new file using Excel 2013 or 2016 with the name Xxx.xxxxx_Exam4.xlsx<\/strong> Files not created in Excel 2013 may not earn full credit!<\/strong> <\/li>\n Enter<\/strong> the appropriate Excel header and footer elements in the locations below. The only part you should type manually is your name.\n <\/a> <\/li>\n Each student has a unique data file for this assignment.<\/strong> 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<\/strong><\/span> the button below you agree to follow the guidelines for this assignment. Click once <\/li>\n Import the data file that you downloaded <\/strong>The file is a Comma Delimited file<\/strong> so be sure to select that option during the import import the data into cell A3<\/strong> in the current worksheet at this point your file should look something like this<\/a> with 417 rows of data<\/strong> <\/li>\n Add the following 3 document properties via the Document Properties panel. Author<\/strong>: xxxxx.xxxxx <\/em> Title<\/strong>: Exam 4 spring 2018<\/em> Comments<\/strong>: location where you completed the exam examples if you completed it at home then list – “home computer<\/em>“ if you complete it on campus then list the room and computer number examples – “Cuyamaca E206 system 32<\/em>” or “Grossmont 541 system 32<\/em>“ Tech Mall examples – “Cuyamaca Tech Mall system 16<\/em>” or “Grossmont Tech Mall system 41<\/em>“<\/li>\n Save<\/strong> the file with the name Xxx.xxxxx_Exam4.xlsx<\/strong> <\/li>\n<\/ol>\n<\/td>\n<\/tr>\n

\n Part 2<\/span> – Layout, Format and Enter Formulas –<\/span> Revenue<\/span>Worksheet<\/span><\/p>\n This part of the exam modifies the worksheet to format and calculate the revenue with a discount<\/p>\n \n Worksheet data entry, <\/strong>the following cells should contain information as follows: <\/strong>cell G2<\/strong> contains the text Markup<\/strong>. cell G3<\/strong> contains the text Order Total<\/strong>. cell H2<\/strong> contains the value 15%<\/strong> formatted as percent with 0 decimal places. cell H3<\/strong> contains the text Sale Price<\/strong>.<\/li>\n Add<\/strong> the title your last name<\/em> Revenue in cell A1. (e.g. Ewdish Revenue<\/strong>).<\/li>\n Change<\/strong> the title font size in cell A1 to 18 points.<\/li>\n Calculate Order Total<\/strong> in column G for all 416 items. The Order Total is the Quantity times the Unit Price. <\/li>\n Calculate Sales Price <\/strong>in column H for all 416 items. The Sales Price is the Order Total<\/strong> times (1 + Markup<\/strong>) + Quantity<\/strong> times Shipping<\/strong>. <\/li>\n Merge and Center<\/strong> the title in cell A1<\/em> across only the data columns of the worksheet.<\/li>\n Add like breaks<\/strong> between words in all column titles with 2 words by using a line break. Do not use wrap lines.<\/li>\n Change<\/strong> 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<\/strong> and adjust columns B, G, and H to the width of 13<\/strong>.<\/li>\n Bold<\/strong> all column headings in row 3.<\/li>\n Align<\/strong> all the column titles vertically at the top and horizontally centered. <\/a><\/li>\n Theme<\/strong> make sure you are using the standard Office <\/strong><\/em>theme and set the background color of cell A1 to Green, Accent 6 Darker 25% <\/strong>and the text color to White.<\/strong><\/li>\n Change<\/strong> the background color of cells A3:H3 to Green, Accent 6 Lighter 60%<\/strong>. View Formatted Example of rows 1 – 3 to this point<\/a><\/li>\n Rename<\/strong> the worksheet tab to Revenue.<\/strong><\/span><\/li>\n Change<\/strong> the tab color to Green, Accent 6, Lighter 60%<\/strong>.<\/li>\n Freeze<\/strong> panes – only<\/em><\/strong> Column A<\/strong> and Rows 1-3<\/strong> should always visible when you scroll the worksheet either horizontally or vertically<\/li>\n Enter formulas<\/strong> at the bottom of the Order Total<\/strong><\/em>and Sales Price<\/strong><\/em> columns to calculate the total all items each of those columns and bold the cells. <\/li>\n Enter<\/strong> the text Totals<\/strong> in column F next to the formulas you added and bold the cell.<\/li>\n Add the range name <\/strong>Revenue_Total<\/span> for the Sales Price<\/strong>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. <\/li>\n Format<\/strong> as follows:\n \n columns<\/strong>, Unit Price, Shipping – set as comma style<\/a><\/li>\n columns<\/strong> Order Total, Sales Price – set as comma style<\/a>with 0 decimal places<\/li>\n column D<\/strong> – Short Date<\/li>\n cell H2<\/strong> – Percent<\/li>\n<\/ul>\n<\/li>\n Change<\/strong> the worksheet view to the Normal view (if needed).<\/li>\n Save<\/strong> the workbook before continuing.<\/li>\n<\/ol>\n<\/td>\n<\/tr>\n

\n Part 3<\/strong> – What-If Analysis – <\/strong>Analysis <\/strong>Worksheet<\/strong><\/p>\n \n Copy<\/strong> the Revenue worksheet<\/em> to a new tab changing tab name to Analysis<\/strong><\/li>\n Change<\/strong> the tab color to Orange, Accent 2, Lighter 60%<\/strong><\/li>\n Change<\/strong> the text in cell A1 to Analysis<\/strong><\/li>\n Change<\/strong> background fill color the cell A1 to Orange Accent 2, Darker 25%<\/strong><\/li>\n Add the range name<\/strong> Analysis_Total<\/span> 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. <\/li>\n Use Goal Seeking<\/strong> to adjust the Markup value in cell H2 so that the Sales Price <\/strong>total value is $30,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.<\/li>\n Format<\/strong> the data area A3:H419<\/strong> including the column headings but not the totals at the bottom of the columns as a Table using style Medium 3<\/strong> (1st row in Medium list, column 3) – Select Yes<\/strong> if you receive the message “Your selection overlaps one or more data ranges. …”<\/li>\n Change<\/strong> the fill color for the column headings in row 3 to match row 1. – see example<\/a> <\/li>\n<\/ol>\n<\/td>\n<\/tr>\n

\n Part 4<\/strong> – Sorting and Filtering – <\/strong>Filtered<\/strong> Worksheet<\/strong><\/p>\n \n Copy<\/strong> the Revenue worksheet<\/em> to a new tab and name it Filtered<\/strong><\/li>\n Change<\/strong> the tab color to Blue, Accent 1, Lighter 60%<\/strong><\/li>\n Change<\/strong> the text in cell A1 to Filtered<\/strong><\/li>\n Change<\/strong> background fill color the cell A1 to Blue, Accent 1, Darker 25%<\/strong><\/li>\n Delete<\/strong> the totals and adjacent text at the bottom of the data rows in the Filtered worksheet so only the sales remain. <\/li>\n Add a filters<\/strong> for the column headings in row 3 of the Filtered worksheet.<\/li>\n Using the Sort button on the ribbon,<\/strong> add a Custom Sort Sort<\/strong> the data by the Category Name in ascending order (A to Z)<\/em><\/li>\n Add a secondary sort<\/strong> on the Quantity in ascending order (Smallest to Largest)<\/em><\/li>\n Add Subtotals<\/strong> for Order Total and Sales Price <\/em>with each change in Category Name<\/em> and include Summary below the data<\/li>\n Change<\/strong> the filter to only show the Meat\/Poultry Category and Meat\/Poultry Total<\/em> by selecting those values from the filter list see example <\/a><\/li>\n Add the range name<\/strong> Filtered_Total<\/span> 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. <\/li>\n<\/ol>\n<\/td>\n<\/tr>\n

\n Part 5<\/strong> – Create Totals Worksheet<\/strong><\/p>\n \n Copy<\/strong> the Revenue<\/em> worksheet to a new tab and name it Totals<\/strong><\/li>\n Change<\/strong> the tab color to Gold, Accent 4, Lighter 60%<\/strong><\/li>\n Change<\/strong> the text in cell A1 to Totals<\/strong><\/li>\n Change<\/strong> background fill color the cell A1 to Gold, Accent 4, Darker 25%<\/strong><\/li>\n Delete<\/strong> the totals and adjacent text at the bottom of the data rows in the Totals worksheet so only the sales remain. <\/li>\n Using the Sort button on the ribbon,<\/strong> add a Custom Sort Sort<\/strong> the data by the Category Name in ascending order (A to Z)<\/em><\/li>\n Add a secondary sort<\/strong> on the Order Total in descending order (Largest to Smallest)<\/em><\/li>\n Add Subtotals<\/strong> for each change in Category Name with a sum of the Order Total and Sales Price<\/em><\/li>\n Collapse<\/strong> the outlines to Hide Detail so that only the subtotals are displayed see example<\/a><\/li>\n Add the range name<\/strong> Grand_Total<\/span> 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. <\/li>\n<\/ol>\n<\/td>\n<\/tr>\n

\n Part 6<\/strong> – Create a 3D Pie Chart<\/strong><\/p>\n \n Create a 3D Pie Chart<\/strong> of the category names Beverages thru Seafood and their associated Sales Price subtotals<\/em> from the Totals worksheet<\/li>\n Move the chart<\/strong> to a new worksheet named Pie Chart<\/li>\n Change the chart style to Style 8<\/strong><\/li>\n Add<\/strong> Percentages<\/em> and Category Name<\/em> as the only data labels and set them to display on the outside edge of the chart<\/li>\n Change<\/strong> the chart title<\/em> SALES BY CATEGORY<\/strong><\/span> – see example<\/a><\/li>\n Change<\/strong> the name of the chart worksheet tab to Pie Chart<\/em> if needed<\/li>\n<\/ol>\n<\/td>\n<\/tr>\n

\n Part 7<\/strong> – Adding Summary <\/strong>Worksheet<\/strong><\/p>\n \n Add a New Worksheet<\/strong> and named Summary<\/strong><\/li>\n Change<\/strong> the Summary worksheet tab color to Dark Red<\/strong> (in the Standard Colors)<\/li>\n Enter<\/strong> the title Exam 4 Summary<\/strong> in cell A1 and copy the format from cell A1 in the Revenue worksheet. <\/li>\n Enter text<\/strong> in the cells as indicated below \n\n\n\n\n\n\n\n\n\n\n\n <\/td>\n A<\/td>\n B<\/td>\n C<\/td>\n D<\/td>\n<\/tr>\n 1<\/td>\n Exam 4 Summary<\/td>\n<\/tr>\n 2<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n 3<\/td>\n Worksheets<\/strong><\/td>\n Value<\/strong><\/td>\n <\/td>\n Range<\/strong><\/td>\n<\/tr>\n 4<\/td>\n Revenue<\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n 5<\/td>\n Analysis<\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n 6<\/td>\n Filtered<\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n 7<\/td>\n Totals<\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n Bold<\/strong> the text in cells A3:D3, Center the text in cell B3<\/li>\n Indent <\/strong>the content of cells A4:A7<\/li>\n Add the formulas<\/strong> listed below to reference the named cells as follows: cell B4 = Revenue_Total<\/em> cell B5 = Analysis_Total<\/em> cell B6 = Filtered_Total<\/em> cell B7 = Grand_Total<\/em><\/li>\n Format<\/strong> the values in column B using the comma style<\/a>format with 0 decimal places. Then adjust the colum width so the values are correctly displayed<\/li>\n Paste the range names<\/strong> starting in cell D4 using the Paste List<\/strong> command. <\/li>\n Adjust the column width<\/strong> so the names are completely displayed<\/li>\n Create a hyperlink<\/strong> to each worksheet from the text in cells A4:A7; i.e. link the text in cell A4 (Revenue) to the Revenue worksheet<\/li>\n Arrange<\/strong> the worksheet tabs in the following order from left to right Summary, Revenue, Analysis, Filtered, Totals, Pie Chart<\/em> – see example<\/a><\/li>\n Select<\/strong> the Revenue worksheet. <\/li>\n Save<\/strong> the file and exit Excel<\/li>\n Upload<\/strong> the file Xxx.xxxxx_Exam4.xlsx <\/strong>to the class Drop Box using the Exam 4 link<\/span><\/li>\n<\/ol>\n<\/td>\n<\/tr>\n \n How is this assignment graded?<\/p>\n \n Follow instructions: naming worksheets, correct placement of discount percentage, headers, footers <\/li>\n Correct use of formulas: using cell references, functions including Sum and absolute references<\/li>\n Formatting worksheet: layout, margins, header\/footer, style<\/li>\n Proper use of goal seeking to correct value. Is the Total Revenue in the Discount worksheet exactly $30,000 greater than the Total Revenue in the Revenue worksheet?<\/li>\n Create a chart as a worksheet from the sorted worksheet with subtotals. <\/li>\n Data is filtered and correctly sorted<\/li>\n<\/ul>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n","protected":false},"excerpt":{"rendered":" 1 – Create your file and Import your data Start by creating your own original new file using Excel 2013 or 2016 with the name Xxx.xxxxx_Exam4.xlsxFiles not created in Excel 2013 may not earn full credit! Enter the appropriate Excel header and footer elements in the locations below.The only part you should type manually is […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_joinchat":[]},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/posts\/163807"}],"collection":[{"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/comments?post=163807"}],"version-history":[{"count":0,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/posts\/163807\/revisions"}],"wp:attachment":[{"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/media?parent=163807"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/categories?post=163807"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/tags?post=163807"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}