Confirmation Paper Assignment | Homework Help Websites

 

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

36218 Topic: Excel Project 1 – MS Excel (Spring 2019)

Type of document: Other (Not listed)

Academic Level:Undergraduate

Number of Pages: 1 (Double Spaced)

Category:   Advertising

Language Style: English (U.S.)

Order Instructions: Attached

 

This in EXCEL format. If you need the documents I can email it to you but this is it copied and pasted.

Excel Project 1 MS Excel

(Spring 2019)

Use the following project description to complete this activity (all the details are included in this document, in

the 22 steps below). For a review of the rubric used in grading this exercise, click on the Assignments tab,

then on the title “Excel Project 1.” Click on Show Rubrics if the rubric is not already displayed.

Summary

Create a Microsoft Excel workbook with four worksheets that provides extensive use of Excel capabilities

including charting, and written analysis and recommendations in support of a business enterprise.

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

Tutorials

Note: Tutorials that address activities used in this project have been assigned in Week 1, 2 and 3.

Step Requirement Comments

1

Open Excel and save a blank workbook with the following

name:

1. Studen s First InitialLast Name Excel Project 1

Example: JSmith Excel Project 1.

1. 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:

1. 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:

1. Place a blank row between each label. Please note

the colon : after each label.

1. 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 column A:

Arial 10 point

Normal font

Right-align all

four labels in the

cells

Step Requirement Comments

4

In the Analysis by worksheet with all entries in column C:

1. Enter the appropriate values for your Name (in cell C1), Class

and Section, Project, Date Due across from the appropriate

label in column A.

1. 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 and are clearly visible within Column C (not

extending into Column D).

Format for column C:

Arial 10 point

Bold

Left-align all four

values in the

cells

5

1. Create new worksheets:

Data, Sorted, and Airport.

Upon completion, there should be Analysis by as well as

the three newly created worksheets.

1. Delete any other worksheets.

Note: The three new worksheets might not have Arial 10 point as

the default type so it may be necessary to change the font and

point to Arial 10 for the new worksheets.

6

If necessary, reorder the four worksheets so they are in the following

order: Analysis by, Data, Sorted, Airport.

7

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 in the correct

order per Item 6.

Format:

all data (field names,

data text, and data

numbers)

Arial 10 point.

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.

Step Requirement Comments

8

In the Data worksheet:

1. Create an Excel table with the recently imported data.
2. 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).

1. The style should highlight the field names in the first row.
2. Ensure NO blank cells are part of the specified data range.
3. Ensure the table has headers.
4. Ensure that Header Row and Banded Rows are selected in the

Table Style Options Group Box, but do NOT select a Total

row.

Some adjustment may

be necessary to column

widths to ensure all field

names and all data are

readable (not truncated

or obscured).

9

In the Data worksheet:

1. Select the entire table (data and headers) using a mouse.
2. Copy the table to the Sorted worksheet.
3. The upper left-hand corner of the header/data in

Sorted should be in cell A1.

1. Adjust columns widths if necessary to ensure all data and field

names are readable.

1. Make sure that all the format requirements for the Data

Worksheet are met in the Sorted Worksheet

10

In the Sorted worksheet:

1. Select all the cells with data in the table (including the header

row.

1. Sort the data by location (ascending).
2. Adjust column widths if necessary to ensure all data and field

names are readable with no truncation.

Ensure all the table is

selected, NOT just

the location column.

Format:

All data (field names,

data text, and data

numbers)

Arial 10 point.

Step Requirement Comments

11

1. Copy the entire table from the Sorted worksheet to the

Airport worksheet. In the Airport worksheet, the field names

should be in row 1 and the Year column should be in column

A.

1. Some column adjustments may be necessary so that the field

names as well as the data is all legible.

1. Make sure that the same format requirements for the Data

Worksheet are met in the Airport Worksheet.

Ensure all cells with

data are selected.

Format:

All data (field names,

data text, and data

numbers)

Arial 10 point.

12

In the Airport worksheet:

1. Delete all the rows in the table where the data is for
2. Downtown.
3. b. The field names should remain at the top of the table. c.

The remaining data should be for the Airport location.

13

In the Airport worksheet, using a custom sort:

1. Sort all the data first by CarClass (Ascending),
2. then by Year (Ascending),
3. then by Quarter (Ascending).

Ensure that all cells with

data have been selected

before sorting. This sort

is completed in a single

step, not 3

separate steps.

14

In the Airport worksheet:

1. Add a new column heading AvgRev for the column where the

average revenue per car for each row will be calculated and

displayed. This column must be the next column to the right

of the NumCars column.

1. Add a formula to calculate the AvgRev by dividing the

contents of the Revenue cell by the contents of the NumCars

cell for each data row in the table

1. The AvgRev values format is currency (a $ and two decimal

points) with no space between the $ and the first number

following it. When you are clicking on cells to construct a

formula in cell G2, while you may be expecting to see

E2/F2 what may appear in the cell editor is

Revenue/@NumCars, a result of using an Excel table.

1. Column adjustment may be needed to ensure both the title

and the data is visible.

1. Apply the formatting instructions in the Comments column

(next column at right).

The format of the table

must be extended to

the new column.

Format:

Arial 10 point

data values for

AvgRev are

Currency Format

(using the Format

Cells option). A

typical AvgRev

value could be

$123.45, for instance

with no space

between the $ and

the 1.

Revenue and

NumCars format

from Data

Worksheet.

Step Requirement Comments

15

In the Airport worksheet:

1. Create a range named AE that includes every AvgRev value

for all Economy cars.

1. Create a range named AH that includes every AvgRev value

for all Hybrid cars.

1. Create a range named AP that includes every AvgRev value

for all Premium cars.

1. Create a range named AS that includes every AvgRev value

for all SUV cars.

Defined Avg Rev

Ranges

AE is Airport Economy,

AH is Airport Hybrid,

AP is Airport Premium,

AS is Airport SUV.

16

In the Airport worksheet:

1. In row 1, two or three columns to the right of the AvgRev

column:

1. Create a label called Average Quarterly Revenue per

Economy Car (Airport) in row 1.

1. Immediately (same column, next row down) under the

Economy label create a label called Average Quarterly

Revenue per Hybrid Car (Airport). in row 2.

1. Immediately (same column, next row down) under the Hybrid

Car label, create a label called Average Quarterly Revenue

per Premium Car (Airport) in row 3.

1. Immediately (same column, next row down) under the

Premium label, create a label called Average Quarterly

Revenue per SUV (Airport) in row 4.

1. Apply the formatting instructions in the Comments column

(next column, at right).

Format:

Arial 10 point

Bold

Left-justified

17

In the Airport worksheet:

In the first fully visible cell to the right of the of the widest of

the four labels so that all four values are in the same column

aligned with the appropriate row for the specified carclass:

1. Calculate the Average Quarterly Revenue (AvgRev) for the

Label-indicated carclass for each of the four carclasses.

1. Use the average function and the named range (AE, AH, AP,

of AS) ONLY NOT a range of cells.

1. Ensure each of four averages is Currency format with two

decimal places preceded by a $ with no spaces between the

$ and the first number.

Format:

Arial 10 point

Normal

Currency ($ and two

decimal places).

Truncation (cut off) of

any of the four labels is

not acceptable.

18

In the Airport worksheet:

Use conditional formatting to identify all AvgRev values where the

AvgRev is greater than the AvgRev FOR THAT CARCLASS

calculated in Item 17 above. DO NOT USE the calculated

AvgRev number, instead USE the cell contents of the AvgRev

value so that if either Revenue or NumCars change in a particular

row that the conditional formatting is based on the updated value

for Average Quarterly Revenue.

Format:

The highlighted AvgRev

cell should be Green Fill

with Dark Green Text.

Step Requirement Comments

19

In the Airport worksheet:

1. Create a column or bar type of chart that clearly shows the

average revenue (AvgRev) for Hybrid vehicles for 2015 to

2017 by quarter.

1. Provide an internal chart title that explains what is

portrayed on the chart. The year and the quarter must be

included in the chart for clarity.

1. Place the chart one or two rows below and left-aligned in

the same column where the Average Quarterly Revenue

labels begin.

You will have ten bars in

your chart, one for each

quarter. Each bar

represents the average

revenue per hybrid car

for ten quarters airport

location only.

20

In the Airport Worksheet:

1. Create a column or bar type of chart that clearly shows the

average revenue (AvgRev) for Premium (carclass) vehicles

for the four 2015 quarters.

1. Provide an internal chart title that explains what is portrayed on

the chart. The two chart titles should be similar (except for the

text difference in

carclasses).

1. Place the chart two or three rows below the first chart and

positioned so that left sides of the two charts are in alignment.

You will have four bars

in your chart, one for

each quarter. Each bar

represents the average

revenue per premium

car for four quarters

airport location only.

For the questions below, present your answers in a very readable format left-aligned with and one or

two rows beneath the chart. You can type your answer in one cell, then highlight and select several

rows and columns, selecting merge cells and selecting Wrap Text. Do NOT widen the columns as this

will adversely impact the appearance of the worksheet the text from Center to Left justification. Play

with this a bit. If you simply type your answer on a single line in that will also be ok. But be sure the

entire answer can be read without the reader having to change any formatting. Reading the

answer in the cell editor is NOT acceptable; the answer must be visible in the worksheet itself.

21

Question 1: Based on the hybrid AvgRev chart above, is there a

trend? In addition to the yes or no response, explain (give

reasons) for the trend or why there is not a trend to be observed.

Label your response Question 1. Answer this question in 2 to 3

sentences left-aligned with and under the chart. DO NOT change

your spreadsheet. Just respond to the question.

Format:

Arial 10 point

Normal/Black

Left-align text in

the cell

22

Question 2: If you had to display a chart for the AvgRev

(based on the data in this worksheet) for all four carclasses

for the ten quarters into one chart, what type of chart of

chart would you use and why would you use it?

Label your response Question 2. Answer this question in 2 to 3

sentences in a new row left-aligned with the Question 1 response

and one or two rows beneath it.

 

file:///C:/Users/User/Downloads/work.pdf