a. Import the text file fall2018rentalcars.txt into the Data worksheet.

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:

a. Import the text file fall2018rentalcars.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.

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

Step Requirement Comments

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 by the formatting of the Excel table are not copied.

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.

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 and the total number of car rentals for each of the four quarters of 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 and the total number of car rentals for each of the four quarters of 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 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.

The charts must allow a viewer to determine approximate number or car rental by car class (first chart) and number of car

Step Requirement Comments

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.

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 and the total revenue for each of the four quarters of 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 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 and the total revenue for each of the four quarters of 2015. A grant 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.

Format (for both pivot tables):

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

• No decimal places • Arial 10 point

Normal ● Right-align the Q1 through Q4 and Grand Total 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.

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

Step Requirement Comments

 

Looking for a Similar Assignment? Hire our Top Techical Tutors while you enjoy your free time! All papers are written from scratch and are 100% Original. Try us today! Active Discount Code FREE15

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *