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 2016 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 spring2020rentalcars.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. Students 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 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. EntertheappropriatevaluesforyourName,Classand

Section, Project, Date Due across from the appropriate label

in column A.

b. UsetheformattingintheCommentscolumn(totheright).

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 spring2020rentalcars.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 creationprocess. The data must begin in Column A..

7

In the Data worksheet:

a. CreateanExceltablewiththerecentlyimporteddata.

b. Pickastylewiththestylesgrouptoformatthetable(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. EnsureNOblankcellsarepartofthespecifieddatarange.

e. EnsurethatHeaderRowandBandedRowsareselectedin

theTableStyleOptionsGroupBox. DoNOTcheckthe 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 2017 data as well as 2018data.

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 2016 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.

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 2016 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

eachofthefourquartersincolumnsfor2016. Agrandtotalfor 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 2016. 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 bethetwolocations. Placethispivottabletworowsbelowthe upper pivot table and left aligned with the upper pivot table. 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 to the right of the four quarter labels in both pivot tables

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 forthefour2016quarters. Bothcartypesandquartersmustbe 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 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. Thesametypeofbarorcolumnchartmustbeused 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 2016 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 incolumnsfor2016. Agrandtotalforthetotalrevenuemust alsobedisplayed. Thecolumnlabelsmustbethefourquarters

Format (for both pivot tables):

Currency ($) with comma separators (for thousands) and

Step

Requirement

Comments

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 2016. 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 that follow the four Quarter labels in both pivot tables

13

In the Slide 3 worksheet, based solely on the 2016 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 classforthefour2016quarters. Ensurebothcartypesand 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 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. Thesametypeofbarchartmustbeusedthroughoutthis 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, 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 2016 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).

The two values should match the values from the previously created pivot tables.

Step

Requirement

Comments

c. Format the two values to match the data above in the particular respective column.

16

a. Open a new, blank Power Point presentation file.

b. Save the Presentation using the following name:

Students Last Name First Name Initial Presentation Example: Smith Jane P Presentation

17

SlidesareNOTMicrosoftWorddocumentsviewedhorizontally. 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

andfeelforallslidesinthepresentation. Blankslideswith

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 2016"

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 2016"

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

Ensure that there are no grammar or spelling errors on your chart and in your speaker notes.

Step

Requirement

Comments

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).

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 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

Apply an animation on at least one slide. The animation may be applied to text or a graphic.

Be sure you submit BOTH the Excel file and the PowerPoint file in the appropriate Assignment folder (Excel Project #3).

Year Quarter Location CarClass Revenue NumCars

2018 Q1 Airport Economy 1011191 5650

2018 Q1 Downtown Economy 943509 5965

2017 Q4 Airport Economy 926382 5122

2016 Q3 Downtown Economy 777155 5280

2017 Q1 Downtown Economy 725178 4802

2017 Q4 Downtown Economy 710955 4529

2016 Q3 Airport Economy 708349 4649

2018 Q2 Airport Premium 697496 3726

2018 Q2 Airport SUV 697326 3918

2018 Q2 Airport Hybrid 697206 3781

2017 Q3 Downtown Economy 687341 4451

2018 Q1 Airport SUV 685728 3923

2018 Q1 Airport Premium 685524 3722

2018 Q1 Airport Hybrid 685013 3812

2017 Q3 Airport Hybrid 664684 3850

2017 Q3 Airport Premium 664353 3807

2017 Q3 Airport SUV 664219 4017

2017 Q2 Downtown Economy 646991 4218

2016 Q4 Airport Premium 640671 3816

2016 Q4 Airport Hybrid 640254 3898

2016 Q4 Airport SUV 623463 4034

2016 Q4 Airport Economy 618064 4031

2017 Q2 Airport Premium 605136 3486

2017 Q2 Airport SUV 604825 3709

2017 Q2 Airport Hybrid 604445 3578

2016 Q4 Downtown Premium 602216 3780

2016 Q4 Downtown Hybrid 601894 3869

2016 Q4 Downtown SUV 601517 4018

2018 Q2 Airport Economy 600780 3114

2017 Q3 Airport Economy 599375 3485

2018 Q2 Downtown Economy 580217 3532

2017 Q1 Airport Premium 571105 3351

2017 Q1 Airport SUV 570887 3552

2017 Q1 Airport Hybrid 570859 3446

2017 Q4 Airport Hybrid 546026 3054

2017 Q4 Airport SUV 545509 3168

2017 Q4 Airport Premium 545319 3022

2016 Q3 Downtown SUV 521701 3532

2016 Q3 Downtown Hybrid 521472 3376

2016 Q3 Downtown Premium 521308 3315

2016 Q4 Downtown Economy 513442 3389

2017 Q2 Downtown Hybrid 505147 3110

2017 Q2 Downtown SUV 505039 3187

2017 Q2 Downtown Premium 504891 3040

2017 Q1 Airport Economy 503650 3231

2017 Q3 Downtown SUV 499871 3134

2017 Q3 Downtown Hybrid 499161 3005

2017 Q3 Downtown Premium 499079 2947

2016 Q3 Airport Hybrid 488077 3029

2016 Q3 Airport Premium 487711 2945

2016 Q3 Airport SUV 487214 3138

2016 Q2 Airport Economy 484954 3272

2016 Q2 Downtown Hybrid 470951 3072

2016 Q2 Downtown SUV 470764 3213

2016 Q2 Downtown Premium 470558 3034

2016 Q1 Airport Economy 453665 3196

2018 Q1 Downtown Premium 436719 2500

2018 Q1 Downtown Hybrid 436650 2515

2018 Q1 Downtown SUV 436119 2643

2017 Q4 Downtown Premium 435682 2512

2017 Q4 Downtown Hybrid 435056 2569

2017 Q4 Downtown SUV 434943 2655

2017 Q1 Downtown Hybrid 428988 2675

2017 Q1 Downtown Premium 428691 2609

2017 Q1 Downtown SUV 428575 2744

2016 Q2 Downtown Economy 382948 2688

2017 Q2 Airport Economy 371631 2111

2016 Q1 Airport SUV 363309 2439

2016 Q1 Airport Hybrid 363062 2354

2016 Q1 Airport Premium 362646 2289

2016 Q1 Downtown Economy 361977 2658

2016 Q1 Downtown SUV 358062 2463

2016 Q1 Downtown Hybrid 357967 2373

2016 Q1 Downtown Premium 357693 2336

2018 Q2 Downtown Premium 332931 1837

2018 Q2 Downtown SUV 332851 1917

2018 Q2 Downtown Hybrid 332307 1873

2016 Q2 Airport Hybrid 306364 1943

2016 Q2 Airport Premium 305996 1895

2016 Q2 Airport SUV 305953 1987

i couldn't upload a document, so i copied and pasted it. pls send a google drive link, so i can download the solution. thanks