Excel Capabilities for Charting Project 3 Data Worksheets
Excel Capabilities for Charting Project 3 Data Worksheets
Excel Project 3 – MS Excel (Spring 2019)
Use the project description HERE to complete this activity. For a review of the complete rubric used in grading this exercise, click on the Assignments tab, then on the title Excel Project #3. Click on Show Rubrics if the rubric is not already displayed.
Summary
Create a Microsoft Excel file with four worksheets that provides extensive use of Excel capabilities for charting. The charts will be copied into a Microsoft PowerPoint file and the student will develop appropriate findings and recommendations based on analysis of the data.
A large rental car company has two metropolitan locations, one at the airport and another centrally located in downtown. It has been operating since 2015 and each location summarizes its car rental revenue quarterly. Both locations rent four classes of cars: economy, premium, hybrid, SUV. Rental revenue is maintained separately for the four classes of rental vehicles.
The data for this case resides in the file spring2019rentalcars.txt and can be downloaded by clicking on the Assignments tab, then on the data tile name. It is a text file (with the file type .txt).
Do not create your own data, you must use the data provided and only the data provided.
Default Formatting. All labels, text, and numbers will be Arial 10, There will be $ and comma and decimal point variations for numeric data, but Arial 10 will be the default font and font size.
Step Requirement Comments
1
Open Excel and save a blank workbook with the following name: a. “Student’s First InitialLast Name Excel Project 3” Example: JSmith 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 C (not extending into Column D).
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
Step Requirement Comments
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 spring2019rentalcars.txt into the Data worksheet. The data should begin in Column A, Row 1.
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..
7
In the Data worksheet: a. Create an Excel table with the recently imported data. b. 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). c. The style must highlight the field names in the first row. These are your table headers. d. Ensure NO blank cells are part of the specified data range. e. 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 2016 data as well as 2017 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 2015 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 but not the formatting of the Excel table. 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.
Step Requirement Comments Adjust columns widths if necessary to ensure all data and field names are readable.
10
In the Slide 2 worksheet, based solely on the 2015 data: a. Create a Pivot Table that displays the total number of car rentals for each car class in columns and the total number of car rentals for each of the four quarters in rows for 2015. A grand total for the total number of rentals (NumCar) 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 two rows below the data beginning at the left border of column A. 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 columns and the total number of car rentals for each of the four quarters in rows for 2015. 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 pivot table (step 10 a) beginning at the left border of column A. 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.
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 in both pivot tables
11
In the Slide 2 worksheet, based solely on the 2015 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 2015 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 in row 1 and two or three columns to the right of the data table. 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 2015 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 rental 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 2015 data: a. Create a Pivot Table that displays the total revenue for each car class in columns and the total revenue for each of the four quarters of in rows for 2015. A grand total for the total revenue
Format (for both pivot tables): • Currency ($) with comma separators (for thousands) and
Step Requirement Comments 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 two rows below the data beginning at the left border of column A.
c. Create a Pivot Table that must displays the total revenue for each location in columns and the total revenue for each of the four quarters in rows for 2015. 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) table beginning at the left border of column A.
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.
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 in both pivot tables
13
In the Slide 3 worksheet, based solely on the 2015 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 2015 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 in row 1 and two or three columns to the right of the data 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 2015 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 number or car rental 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.”
14
In the Filter Analysis worksheet, highlight the entire non-Excel table to include the row of titles and all 32 rows of 2015 rental car data. Click Data at the top of the worksheet. Turn on filtering for all 33 rows.
15
In the Filter Analysis worksheet: a. Select Hybrid ONLY and Quarter 1 ONLY in their respective columns. b. Two rows below the last line of data, in the revenue and number of cars columns, calculate the sum of that column using a function using a SUM function. c. Format the two values to match the data above in the particular column.
The two values should match the values from the previously created pivot tables.
Step Requirement Comments
16
a. Open a new, blank Power Point presentation file. b. Save the Presentation using the following name: “Student’s First Initial Last Name Presentation” Example: JSmith Presentation
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 2015” 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 2015” 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.
Ensure that there are no grammar or spelling errors on your chart and in your speaker notes.
Step Requirement Comments e. Use the speaker notes feature to help you discuss the findings and recommendations (four complete sentences minimum).
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.
22
Create a footer using “Courtesy of Your Name” so that is shows on all slides including the Title Slide. The text in this footer must be on the left side of the slides IF the theme selected allows. Otherwise let the theme determine the position of this text.
Replace the words “Your Name” with your actual name.
23
Create a footer for your name and automated Slide Numbers that appears on all slides except the Title SlideThe 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.
24 Apply a transition scheme to all slides.
One transition scheme may be used OR different schemes for different slides
25
Apply an animation on at least one slide. The animation may be applied to text or a graphic.
Year Quarter Location CarClass Revenue NumCars
2017 Q1 Downtown Economy 964990 6114
2017 Q1 Airport Economy 1034210 5796
2015 Q3 Downtown Economy 794856 5421
2016 Q4 Airport Economy 947474 5261
2016 Q1 Downtown Economy 741697 4938
2015 Q3 Airport Economy 724486 4784
2016 Q4 Downtown Economy 727150 4662
2016 Q3 Downtown Economy 703000 4583
2016 Q2 Downtown Economy 661733 4347
2015 Q4 Airport Economy 632148 4158
2017 Q2 Downtown Economy 593441 3653
2016 Q3 Airport Economy 613034 3606
2015 Q4 Downtown Economy 525148 3509
2015 Q2 Airport Economy 496013 3391
2016 Q1 Airport Economy 515134 3349
2015 Q1 Airport Economy 464013 3313
2017 Q2 Airport Economy 614472 3231
2015 Q2 Downtown Economy 391689 2800
2015 Q1 Downtown Economy 370241 2769
2016 Q2 Airport Economy 380115 2216
2015 Q4 Airport Premium 655270 3941
2016 Q3 Airport Premium 679490 3932
2015 Q4 Downtown Premium 615940 3904
2017 Q1 Airport Premium 701141 3846
2017 Q2 Airport Premium 713386 3850
2016 Q2 Airport Premium 618926 3607
2016 Q1 Airport Premium 584122 3470
2015 Q3 Downtown Premium 533194 3434
2016 Q2 Downtown Premium 516403 3156
2015 Q2 Downtown Premium 481290 3150
2016 Q4 Airport Premium 557750 3138
2015 Q3 Airport Premium 498833 3060
2016 Q3 Downtown Premium 510460 3062
2016 Q1 Downtown Premium 438472 2720
2016 Q4 Downtown Premium 445621 2622
2017 Q1 Downtown Premium 446682 2610
2015 Q1 Downtown Premium 365860 2444
2015 Q1 Airport Premium 370925 2397
2015 Q2 Airport Premium 312988 1998
2017 Q2 Downtown Premium 340535 1940
2015 Q4 Airport SUV 637670 4161
2016 Q3 Airport SUV 679353 4144
2015 Q4 Downtown SUV 615225 4145
2017 Q1 Airport SUV 701350 4049
2017 Q2 Airport SUV 713212 4044
2016 Q2 Airport SUV 618609 3833
2016 Q1 Airport SUV 583899 3673
2015 Q3 Downtown SUV 533595 3653
2016 Q2 Downtown SUV 516555 3305
2015 Q2 Downtown SUV 481501 3331
2016 Q4 Airport SUV 557944 3286
2015 Q3 Airport SUV 498324 3255
2016 Q3 Downtown SUV 511270 3251
2016 Q1 Downtown SUV 438353 2857
2016 Q4 Downtown SUV 444866 2766
2017 Q1 Downtown SUV 446069 2754
2015 Q1 Downtown SUV 366237 2572
2015 Q1 Airport SUV 371603 2548
2015 Q2 Airport SUV 312944 2091
2017 Q2 Downtown SUV 340454 2020
2015 Q4 Airport Hybrid 654842 4023
2016 Q3 Airport Hybrid 679828 3975
2015 Q4 Downtown Hybrid 615611 3994
2017 Q1 Airport Hybrid 700619 3937
2017 Q2 Airport Hybrid 713089 3905
2016 Q2 Airport Hybrid 618220 3700
2016 Q1 Airport Hybrid 583871 3566
2015 Q3 Downtown Hybrid 533362 3496
2016 Q2 Downtown Hybrid 516666 3227
2015 Q2 Downtown Hybrid 481692 3189
2016 Q4 Airport Hybrid 558473 3170
2015 Q3 Airport Hybrid 499207 3145
2016 Q3 Downtown Hybrid 510543 3120
2016 Q1 Downtown Hybrid 438775 2787
2016 Q4 Downtown Hybrid 444981 2680
2017 Q1 Downtown Hybrid 446611 2626
2015 Q1 Downtown Hybrid 366140 2482
2015 Q1 Airport Hybrid 371350 2462
2015 Q2 Airport Hybrid 313365 2047
2017 Q2 Downtown Hybrid 339897 1976
"You need a similar assignment done from scratch? Our qualified writers will help you with a guaranteed AI-free & plagiarism-free A+ quality paper, Confidentiality, Timely delivery & Livechat/phone Support.
Discount Code: CIPD30
Click ORDER NOW..
