how to do excel

User Generated

qernzre12

Business Finance

Description

Year Quarter Location CarClass Revenue NumCars

2017 Q1 Downtown Economy " $912,900 " " 6,262 "

2017 Q1 Airport Economy " $978,534 " " 5,891 "

2015 Q3 Downtown Economy " $752,272 " " 5,678 "

2016 Q4 Airport Economy " $896,251 " " 5,480 "

2016 Q1 Downtown Economy " $701,460 " " 5,151 "

2015 Q3 Airport Economy " $685,248 " " 5,017 "

2016 Q4 Downtown Economy " $687,844 " " 4,848 "

2016 Q3 Downtown Economy " $665,548 " " 4,799 "

2016 Q2 Downtown Economy " $626,232 " " 4,550 "

2015 Q4 Airport Economy " $597,979 " " 4,343 "

2015 Q4 Airport Premium " $619,900 " " 4,221 "

2016 Q3 Airport Premium " $643,168 " " 4,164 "

2015 Q4 Downtown Premium " $582,504 " " 4,155 "

2017 Q1 Airport Premium " $663,276 " " 4,106 "

2017 Q2 Airport Premium " $674,672 " " 4,090 "

2016 Q2 Airport Premium " $585,156 " " 3,850 "

2017 Q2 Downtown Economy " $561,124 " " 3,824 "

2016 Q3 Airport Economy " $579,947 " " 3,740 "

2016 Q1 Airport Premium " $552,324 " " 3,695 "

2015 Q3 Downtown Premium " $504,800 " " 3,657 "

2015 Q4 Downtown Economy " $496,840 " " 3,655 "

2015 Q2 Airport Economy " $468,791 " " 3,541 "

2016 Q1 Airport Economy " $487,124 " " 3,475 "

2015 Q1 Airport Economy " $438,521 " " 3,456 "

2016 Q2 Downtown Premium " $488,588 " " 3,350 "

2017 Q2 Airport Economy " $581,071 " " 3,349 "

2015 Q2 Downtown Premium " $455,424 " " 3,341 "

2016 Q4 Airport Premium " $527,900 " " 3,327 "

2015 Q3 Airport Premium " $471,816 " " 3,259 "

2016 Q3 Downtown Premium " $483,256 " " 3,250 "

2016 Q1 Downtown Premium " $415,016 " " 2,898 "

2015 Q2 Downtown Economy " $370,128 " " 2,897 "

2015 Q1 Downtown Economy " $349,852 " " 2,874 "

2016 Q4 Downtown Premium " $421,120 " " 2,804 "

2017 Q1 Downtown Premium " $422,288 " " 2,750 "

2015 Q1 Downtown Premium " $345,952 " " 2,589 "

2015 Q1 Airport Premium " $351,060 " " 2,558 "

2016 Q2 Airport Economy " $359,781 " " 2,323 "

2015 Q2 Airport Premium " $296,120 " " 2,099 "

2017 Q2 Downtown Premium " $321,768 " " 2,049 "

Unformatted Attachment Preview

Excel Project 3 – MS Excel 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 two classes of cars: economy and premium. Rental revenue is maintained separately for the two classes of rental vehicles. The data for this case resides in the file rentalcars.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. Points Allocated 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 0.2 Use Print Preview to review how the first worksheet would print. 2 Change the name of the worksheet to Analysis by. 0.1 Step Requirement In the Analysis by worksheet: 3 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 Format for text in column A: 0.3 • • • Arial 10 point Normal font Right-align all four labels in the cells It may be necessary to adjust the column width so the four labels are clearly visible. 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). Format for text in column C: 0.2 • • • Arial 10 point Bold Left-align all four values in the cells Step 5 6 7 8 9 Requirement a. Create three new worksheets: Data, Slide 2, Slide 3. Upon completion, there should be Analysis by as well as the three newly created worksheets. b. Delete any other worksheets. After clicking on the blank cell A1 (to select it) in the Data worksheet: a. Import the text file rentalcars.txt into the Data worksheet. b. Adjust all column widths so there is no data or column header truncation. 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, and then rename the new worksheet which HAS the recently imported data as “Data.” It may be necessary to change Revenue data to Currency format (leading $ and thousands separators) with NO decimal points and to change NumCars data to Number format with NO decimal points, but with the comma (thousands separator) because of the import operation. This may or may not occur, but in case it does it needs to be corrected. Adjust all column widths so there is no data or column header truncation. 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 should 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. In the Data worksheet, a. Sort the entire table by Year (Ascending). b. Delete rows that contain 2015 data as well as 2017 data. The resulting table should consist of Row 1 labels followed by 2016 data, with NO empty cells or rows within the table. In the Data worksheet: a. Select the entire table (data and headers) using a mouse. b. Copy the table to the both the Slide 2 as well as the Slide 3 worksheets. c. The upper left-hand corner of the header/data should be in cell A1 on Slide 2 and Slide 3 Points Allocated Comments 0.2 Format for all data (field names, data text, and data numbers) • • 0.3 0.5 0.2 0.4 Arial 10 point. Normal font The field names should 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 should begin in Column A.. Some adjustment may be necessary to column widths to ensure all field names and all data are readable (not truncated or obscured). Step Requirement Points Allocated Comments Adjust columns widths if necessary to ensure all data and field names are readable. In the Slide 2 worksheet, based solely on the 2016 data: a. Create a Pivot Table that provides a quarterly breakdown (columns) of the number of car rentals by car class (rows). Include totals for rows and columns b. Place the pivot table two rows below the data beginning in column A. Ensure that the formatting is as listed in the Comments column. 10 c. Create a Pivot Table that provides a quarterly breakdown (columns) of number the of cars rentals by location (rows). Include totals for rows and columns. d. Place this pivot table two rows below the above pivot table beginning in column A. Ensure that the formatting is as listed in the Comments column. Format (for both pivot tables): • 2.0 • • • Currency ($) with comma separators (for thousands) No decimal places Arial 10 point Normal Adjust the column widths as necessary to preclude data and title and label truncation. 11 In the Slide 2 worksheet, based solely on the 2016 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 2016 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. The charts should allow a viewer to determine approximate number or car rental by car class (first chart) and number of car rentals by location (second chart) 1.8 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 2016 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. 12 In the Slide 3 worksheet, based solely on the 2016 data: a. Create a Pivot Table that provides a quarterly breakdown (columns) of the revenue from car rentals by car class (rows). Totals for rows and columns should be included. b. Place the pivot table two rows below the data beginning in column A. c. Create a Pivot Table that provides a quarterly breakdown (columns) of the revenue from cars rentals by location (rows). Totals for rows and columns should be included. d. Place this pivot table two rows below the above pivot table beginning in column A. The charts should have no more than 4 bars or columns. Format (for both pivot tables): • 2.0 • • Currency ($) with comma separators (for thousands) No decimal places Arial 10 point Normal Step Requirement Points Allocated Comments Adjust the column widths as necessary to preclude data and title and label truncation. In the Slide 3 worksheet, based solely on the 2016 data: 13 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 2016 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 should be used throughout this project. The charts should allow a viewer to determine approximate number or car rental by car class (first chart) and number of car rentals by location (second chart) 1.8 d. Using the pivot table created in Step 12 b, create a bar or column chart that displays the revenue from car rentals by location for the four 2016 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 should be used throughout this project. 14 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 0.1 Slides are NOT Microsoft Word documents viewed horizontally. Be brief. Full sentences are not needed. Blank space in a slide enhances the viewer experience and contributes to readability. 15 15-A 16 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 should be displayed. The charts should have no more than 4 bars or columns. No speaker notes required. 0.7 Ensure that there are no grammar or spelling errors on your chart and in your speaker notes. 0.1 Slide 2: a. Title this slide "Number of Cars Rented in 2016" 1.0 Remember, the title on your slide should convey what the presentation is about. Your Name, Class/Section, and Date Due can be used in the subtitle area. Step Requirement Points Allocated b. Add two charts created in the Slide 2 worksheet of the Excel file c. The charts should 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 selfexplanatory. e. Use the speaker notes feature to help you discuss the bullet points and the charts (four complete sentences minimum). 16-A 17 17-A 18 18-A 19 Ensure that there are no grammar or spelling errors on your chart and in your speaker notes. 0.1 Slide 3: a. Title this slide "Car Rental Revenue in 2016" b. Add two charts, created in the Slide 3 worksheet of the Excel file. c. The charts should 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 selfexplanatory. e. Use the speaker notes feature to help you discuss the bullet points and the charts (four complete sentences minimum). 1.0 Ensure that there are no grammar or spelling errors on your chart and in your speaker notes. 0.1 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 should 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 should 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. 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 should be no larger than Font size of 6, so it does not distract from the content of the slide. 1.0 0.1 0.2 Comments Step 20 21 Requirement Create a footer using "Courtesy of Your Name" so that is shows on all slides including the Title Slide. The text in this footer should be on the left side of the slides IF the theme selected allows. Otherwise let the theme determine the position of this text. Create a footer for automated Slide Numbers that appears on all slides except the Title Slide. The page number should be on the right side of the slides IF the theme selected allows. Otherwise let the theme determine the position of the page number. Points Allocated 0.2 Replace the words "Your Name" with your actual name. 0.2 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. One transition scheme may be used OR different schemes for different slides Ensure that your name does appear on every slide, but the page numbers start on slide #2. This will involve slightly different steps to accomplish both. 22 Apply a transition scheme to all slides. 0.1 23 Apply an animation on at least one slide. The animation may be applied to text or a graphic. 0.1 TOTAL Comments 15.0 Be sure you submit BOTH the Excel file and the PowerPoint file in the appropriate Assignment folder (Excel Project #3).
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

The Excel solution is attached.Please, remember to complete steps 1 to 4 with your personal details as contained in the instruction.Kindly let me know if you require further explanation.

Name:
Class/Section:
Project:
Date Due:

xxxxx
xxxxx
xxxxx
xxxxx

Year
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016

Quarter
Q4
Q1
Q4
Q3
Q2
Q3
Q2
Q3
Q1
Q1
Q2
Q4
Q3
Q1
Q4
Q2

Location
Airport
Downtown
Downtown
Downtown
Downtown
Airport
Airport
Airport
Airport
Airport
Downtown
Airport
Downtown
Downtown
Downtown
Airport

CarClass
Economy
Economy
Economy
Economy
Economy
Premium
Premium
Economy
Premium
Economy
Premium
Premium
Premium
Premium
Premium
Economy

Revenue
NumCars
$896,251
5,480
$701,460
5,151
$687,844
4,848
$665,548
4,799
$626,232
4,550
$643,168
4,164
$585,156
3,850
$579,947
3,740
$552,324
3,695
$487,...


Anonymous
Great! Studypool always delivers quality work.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags