1 Open Excel and save a blank workbook with the followingname: a. “Student’s Last Name First Name Initial Excel Project 3” Example: Smith Jane P Excel Project 3 b. Set Page Layout Orientation to Landscape Use Print Preview to review how the first worksheet would print. 2 Change the name of the worksheet to Analysis by. 3 In the Analysis by worksheet: a. Beginning in Row 1, enter the four labels in column A (one label per row) in the following order: Name:, Class/Section:, Project:, Date Due: b. Place a blank row between each label. Please note the colon : after each label. c. Align the labels to the right side in the cells It may be necessary to adjust the column width so the four labels are clearly visible within Column A (not extending into Column B). Format for text in column A: ?Arial 10 point ?Normal font ?Right-align all four labels in the cells. 4 In the Analysis by worksheet with all entries in column C: a. Enter the appropriate values for your Name, Class and Section, Project, Date Due across from the appropriate label in column A. b. Use the formatting in the Comments column (to the right). It may be necessary to adjust the column width so the four labels are clearly visible within Column C (not extending into Column D). Format for text in column C: ?Arial 10 point ?Bold ?Left-align all four values in the cells 5 a. Create four new worksheets: Data, Slide 2, Slide 3, Filter Analysis. Upon completion, there must be the Analysis by worksheet as well as the four newly created worksheets. b. Delete any other worksheets. 6 After clicking on the blank cell A1 (to select it) in the Data worksheet, import the text file 2021rentalcars.txt into the Data worksheet. In Excel 2019/365 this is done via the Data tab, Get & Transform Data: Click ‘From Text/CSV’ and follow the prompts. If you are using an earlier version of Excel, you can find additional instructions under Additional Tutorials for Excel 2013/2016, Importing a Text File into Excel (Excel 2016 or earlier). The data should begin in Column A. Row 1 should contain the labels for each Column.. It will be necessary to change Revenue data to Currency format ($ and comma (thousands separators) with NO decimal points, and to change NumCars data to number format, with NO decimal points, but with the comma (thousands separator). Note: in the Currency format there is NO space between the $ and the first numeric character that follows the $. Though the intent is to import the text file into the Data worksheet, sometimes when text data is imported into a worksheet, a new worksheet is created. If this happens, delete the blank Data worksheet. Then change the name of the new worksheet with the imported data as “Data”. Make sure worksheets are n the correct order per Item 5. Format for all data (field names, data text, and data numbers) ?Arial 10 point. ?Normal font The field names must be in the top row of the worksheet with the data directly under it in rows. This action may not be necessary as this is part of the Excel table creation process. The data must begin in Column A.. Step Requirement Comments 7 In the Data worksheet: Create an Excel table with the recently imported data( Office 2019/365 may have automatically created an Excel Table when you imported the data). a. Pick a style with the styles group to format the table (choose a style that shows banded rows, i.e., rows that alternate between 2 colors). b. The style must highlight the field names in the first row. These are your table headers. c. Ensure NO blank cells are part of the specified data range. d. Ensure that Header Row and Banded Rows are selected in the Table Style Options Group Box. Do NOT check the Total Row. Some adjustment may be necessary to column widths to ensure all field names and all data are readable (not truncated or obscured). 8 In the Data worksheet, a. Sort the entire table by Year (Ascending). b. Delete rows that contain 2018 data. Erasing or deleting only the data DOES NOT remove the rows from the Excel table. The resulting table must consist of Row 1 labels followed by 32 rows of 2019 data, with NO empty cells or rows within the table. 9 In the Data worksheet: a. Select the entire table (data and headers) using a mouse. b. Copy the table to the Slide 2, Slide 3, and Filter Analysis worksheets. For the Filter Analysis worksheet use Paste | Values so that the values are copied and the cell values retain the same formatting but not the formatting of the Excel table (e.g., no banded rows). c. The upper left-hand corner of the header/data must be in cell A1 on Slide 2 and Slide 3 d. Format specifications from Data worksheet are required for these three worksheets (Slide 2, Slide 3, Filter Analysis). Adjust columns widths if necessary to ensure all data and field names are readable. 10 In the Slide 2 worksheet, based solely on the 2019 data: a. Create a Pivot Table that displays the total number of car rentals for each car class in rows and the total number of car rentals for each of the four quarters in columns for 2019. A grand total for the total number of rentals (NumCars) must also be displayed. The column labels must be the four quarters and the row labels must be the four car classes. b. Place the pivot table beginning in row 1 two columns to the right of the data. Ensure that the formatting is as listed in the Comments column. c. Create a Pivot Table that displays the total number of car rentals for each location in two rows and the total number of car rentals for each of the four quarters in columns for 2019. A grand total for the total number of rentals must also be displayed. The column labels must be the four quarters and the row labels must be the two locations. Place this pivot table two rows below the upper pivot table and left aligned with the upper pivot table. Format (for both pivot tables): ?Number format with comma separators (for thousands) ?No decimal places ?Arial 10 point ?Normal ?Right-align the Q1 through Q4 as well as Grand Total column header labels to the right of the four quarter labels in both pivot tables Step Requirement Comments Ensure that the formatting is as listed in the Comments column. After the both pivot tables are created and appropriately formatted, adjust the column widths as necessary to preclude data and title and label truncation. Some of the columns will appear disproportionally large in the Excel table to preclude data and title truncation in the two pivot tables. 11 In the Slide 2 worksheet, based solely on the 2019 data: a. Using the pivot table created in Step 10 a, create a bar or column chart that displays the number of car rentals by car class for the four 2019 quarters. Both car types and quarters must be clearly visible. b. Add a title that reflects the information presented by the chart. c. Position the top of the chart two rows below the lower pivot table and left-aligned. Use this same type of bar or column chart for the remaining three charts to be created. d. Using the pivot table created in 10 c, create a bar or column chart that displays the number of car rentals by location for the four 2019 quarters. Both locations and quarters must be clearly visible. e. Add a title that reflects the information presented by the chart. f. Left-align this chart with the left side of the first chart and below it. The same type of bar or column chart must be used throughout this project. The charts must allow a viewer to determine approximate number or car rentals by car class (first chart) and number of car rentals by location (second chart) The top chart must have no more than sixteen bars or columns. The bottom chart must have no more than eight bars or columns. ALL FOUR (Slide 2 as well as Slide 3) charts must have the same “format.” 12 In the Slide 3 worksheet, based solely on the 2019 data: a. Create a Pivot Table that displays the total revenue for each car class in rows and the total revenue for each of the four quarters in columns for 2019. A grand total for the total revenue must also be displayed. The column labels must be the four quarters and the row labels must be the four car classes. b. Place the pivot table in the first row and two columns to the right of the data. c. Create a Pivot Table that must display the total revenue for each quarter in columns and for each of the two locations in rows for 2019. A grand total for the total revenue must also be displayed. The column labels must be the four quarters and the row labels must be the two locations. d. Place this pivot table two rows below the pivot (step 12a) and left-aligned with the upper pivot table. After the both pivot tables are created and appropriately formatted, adjust the column widths as necessary to preclude data and title and label truncation. Some of the columns will appear disproportionally large in the Excel table to preclude data and title truncation in the two pivot tables. Format (for both pivot tables): ?Currency ($) with comma separators (for thousands) and no space between the $ and the first number ?No decimal places ?Arial 10 point Normal ?Right-align the Q1 through Q4 and Grand Total column labels that follow the four Quarter labels in both pivot tables Step Requirement Comments 13 In the Slide 3 worksheet, based solely on the 2019 data: a. Using the pivot table created in Step 12 a, create a bar or column chart that displays the revenue from car rentals by car class for the four 2019 quarters. Ensure both car types and quarters are clearly visible. b. Add a title that reflects the information presented by the chart. c. Position the top of the chart two rows below and left-aligned with the bottom pivot table. The same type of bar chart must be used throughout this project. d. Using the pivot table created in Step 12 c, create a bar or column chart that displays the revenue from car rentals by location for the four 2019 quarters. Ensure both locations and quarters are clearly visible. e. Add a title that reflects the information presented by the chart. f. Left-align this chart with the left side of the first chart and below it. The same type of bar chart must be used throughout this project. The charts must allow a viewer to determine approximate revenue by car class (first chart) and revenue by location (second chart) The top chart must have no more than sixteen bars or columns. The bottom chart must have no more than eight bars or columns. ALL FOUR (Slide 2 as well as Slide 3) charts must have the same “format.” 14 In the Filter Analysis worksheet, if necessary, remove all TABLE formatting from the Excel Table but keep the cell values in the same format as on previous worksheets so that it is one row of labels in Row 1 followed by 32 rows of 2019 formatted as specified rental car data. Turn on filtering for all 33 rows. 15 In the Filter Analysis worksheet: a. Select Economy ONLY and Quarter 1 ONLY in their respective columns. b. In row 34, the next blank row after the data, in the revenue and number of cars columns, calculate the sum of that column by adding the contents of the addresses of the cells resulting from the filter action (should be two values for Revenue and two values for NumCars). c. Format the two values to match the data above in the particular respective column. The two values should match the values from the previously created pivot tables. 16 a. Open a new, blank Power Point presentation file. b. Save the Presentation using the following name: “Student’s Last Name First Name Initial Presentation” Example: Smith Jane P Presentation Step Requirement Comments 17 Slides are NOT Microsoft Word documents viewed horizontally. Be brief. Full sentences should not be used on the slide. Bullet points only. Blank space in a slide enhances the viewer experience and contributes to readability. (Speaker notes should be complete sentences.) Slide 1: a. Select an appropriate Design to maintain a consistent look and feel for all slides in the presentation. Blank slides with text are not acceptable. b. This is your Title Slide. c. Select an appropriate title and subtitle layout that clearly conveys the purpose of your presentation. d. Name, Class/Section, and Date Due must be displayed. No speaker notes required. Remember, the title on your slide must convey what the presentation is about. Your Name, Class/Section, and Date Due can be used in the subtitle area. 18 Slide 2: a. Title this slide “Number of Cars Rented in 2019” b. Add two charts created in the Slide 2 worksheet of the Excel file c. The charts must be the same type and equal size and be symmetrically placed on the slide. d. A bullet or two of explanation of the charts may be included, but is not required if charts are self-explanatory. e. Use the speaker notes feature to help you discuss the bullet points and the charts (four complete sentences minimum). Ensure that there are no grammar or spelling errors on your chart and in your speaker notes. 19 Slide 3: a. Title this slide “Car Rental Revenue in 2019” b. Add two charts, created in the Slide 3 worksheet of the Excel file. c. The charts must be the same type and equal size and be symmetrically placed on the slide. d. A bullet or two explanation of the charts may be included, but is not required if charts are self-explanatory. e. Use the speaker notes feature to help you discuss the bullet points and the charts (four complete sentences minimum). Ensure that there are no grammar or spelling errors on your chart and in your speaker notes. 20 Slide 4: a. Title this slide “And in Conclusion…..” b. Write and add two major bullets, one for findings and one for recommendations. c. There must be a minimum of one finding based on slide 2 and one finding based on slide 3. Findings are facts that can be deduced by analyzing the charts. What happened? Trends? Observations? d. There must be a minimum of one recommendation based on slide 2 and one recommendation based on slide 3. Recommendations are strategies or suggestions to improve or enhance the business based on the findings above. e. Use the speaker notes feature to help you discuss the findings and recommendations (four complete sentences minimum). Ensure that there are no grammar or spelling errors on your chart and in your speaker notes. 21 Add a relevant graphic that enhances the recommendations and conclusions on slide 4. If a photo is used, be sure to cite the source. The source citation must be no larger than Font size of 6, so it does not distract from the content of the slide. Step Requirement Comments 22 Create a footer for your name and automated Slide Numbers that appears on all slides except the Title Slide, the page number must be on the right side of the slides IF the theme selected allows. Otherwise let the theme determine the position of the page number Ensure that your name does appear on every slide in the footer, but the page numbers start on slide #2. This will involve slightly different steps to accomplish both Depending upon the theme you have chosen, the page number or your name may not appear in the lower portion of the slide. That is ok, as long as both appear somewhere on the slides. 23 Apply a transition scheme to all slides. One transition scheme may be used OR different schemes for different slides 24 Apply an animation on at least one slide. The animation may be applied to text or a graphic.Year Quarter Location CarClass Revenue NumCars2018 Q2 Airport Premium 303242 19332018 Q2 Airport Hybrid 303607 19822018 Q2 Airport SUV 303199 20272019 Q2 Airport Economy 368286 21532018 Q1 Airport Premium 359382 23352018 Q1 Downtown Premium 354474 23832018 Q1 Airport Hybrid 359794 24012018 Q1 Downtown Hybrid 354745 24202018 Q1 Airport SUV 360039 24882018 Q1 Downtown SUV 354839 25122019 Q4 Downtown Premium 431761 25622019 Q4 Downtown Hybrid 431140 26202019 Q1 Downtown Premium 424833 26612019 Q4 Downtown SUV 431029 27082018 Q1 Downtown Economy 358719 27112019 Q1 Downtown Hybrid 425127 27292018 Q2 Downtown Economy 379501 27422019 Q1 Downtown SUV 424718 27992018 Q3 Airport Premium 483322 30042019 Q3 Downtown Premium 494587 30062019 Q3 Downtown Hybrid 494669 30652019 Q4 Airport Premium 540411 30822018 Q3 Airport Hybrid 483684 30902018 Q2 Downtown Premium 466323 30952019 Q2 Downtown Premium 500347 31012019 Q4 Airport Hybrid 541112 31152018 Q2 Downtown Hybrid 466712 31332019 Q2 Downtown Hybrid 500601 31722019 Q3 Downtown SUV 495372 31972018 Q3 Airport SUV 482829 32012019 Q4 Airport SUV 540599 32312019 Q2 Downtown SUV 500494 32512018 Q1 Airport Economy 449582 32602018 Q2 Downtown SUV 466527 32772019 Q1 Airport Economy 499117 32962018 Q2 Airport Economy 480589 33372018 Q3 Downtown Premium 516616 33812019 Q1 Airport Premium 565965 34182018 Q3 Downtown Hybrid 516779 34442018 Q4 Downtown Economy 508821 34572019 Q1 Airport Hybrid 565721 35152019 Q3 Airport Economy 593981 35552019 Q2 Airport Premium 599690 35562018 Q3 Downtown SUV 517006 36032019 Q1 Airport SUV 565749 36232019 Q2 Airport Hybrid 599005 36502019 Q2 Airport SUV 599382 37832018 Q4 Downtown Premium 596796 38562019 Q3 Airport Premium 658374 38832018 Q4 Airport Premium 634905 38922019 Q3 Airport Hybrid 658702 39272018 Q4 Downtown Hybrid 596477 39462018 Q4 Airport Hybrid 634492 39762019 Q3 Airport SUV 658241 40972018 Q4 Downtown SUV 596103 40982018 Q4 Airport Economy 612501 41122018 Q4 Airport SUV 617852 41152019 Q2 Downtown Economy 641168 43022019 Q3 Downtown Economy 681155 45402019 Q4 Downtown Economy 704556 46202018 Q3 Airport Economy 701974 47422019 Q1 Downtown Economy 718651 48982019 Q4 Airport Economy 918045 52242018 Q3 Downtown Economy 770161 5386Engineering & TechnologyComputer ScienceIFSM 201
Get a plagiarism-free order today we guarantee confidentiality and a professional paper and we will meet the deadline.