Excel Worksheet

Description

Produce a Excel worksheet with the data provided in the file.

DATA
Year
Quarter Location
CarClass
Revenue
2017
Q1
Downtown
Economy
964990 6114
2017
Q1
Airport Economy
2015
Q3
Downtown
2016
Q4
Airport Economy
2016
Q1
Downtown
2015
Q3
Airport Economy
2016
Q4
Downtown
Economy
727150 4662
2016
Q3
Downtown
Economy
703000 4583
2016
Q2
Downtown
Economy
661733 4347
2015
Q4
Airport Economy
2017
Q2
Downtown
2016
Q3
Airport Economy
2015
Q4
Downtown
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
2017
Q1
Airport Premium
701141 3846
2017
Q2
Airport Premium
713386 3850
2016
Q2
Airport Premium
618926 3607
1034210
Economy
5796
794856 5421
947474 5261
Economy
741697 4938
724486 4784
632148 4158
Economy
593441 3653
613034 3606
Economy
Premium
525148 3509
615940 3904
NumCars
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
2015
Q4
Airport SUV
637670 4161
2016
Q3
Airport SUV
679353 4144
2015
Q4
Downtown
SUV
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
340535 1940
615225 4145
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
2015
Q4
Airport Hybrid 654842 4023
2016
Q3
Airport Hybrid 679828 3975
2015
Q4
Downtown
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
340454 2020
Hybrid 615611 3994
Hybrid 339897 1976
Instructions
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: a. “Student’s First InitialLast Name
Excel Project 1” Example: JSmith Excel Project 1. 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 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:
a. Enter the appropriate values for your Name (in cell C1), 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 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
a. Create new worksheets: Data, Sorted, and Airport. Upon completion, there should be Analysis by as
well as the three newly created worksheets. b. 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”. Make sure
worksheets are 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:
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. d. Ensure NO blank cells are part of the specified
data range. e. Ensure the table has headers. f. 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:
a. Select the entire table (data and headers) using a mouse. b. Copy the table to the Sorted worksheet. c.
The upper left-hand corner of the header/data in Sorted should be in cell A1. d. Adjust columns widths if
necessary to ensure all data and field names are readable. e. Make sure that all the format requirements
for the Data Worksheet are met in the Sorted Worksheet
10
In the Sorted worksheet:
a. Select all the cells with data in the table (including the header row. b. Sort the data by location
(ascending). c. 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
a. 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. b. Some column
adjustments may be necessary so that the field names as well as the data is all legible. c. 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: a. Delete all the rows in the table where the data is for a. Downtown. b. 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:
a. Sort all the data first by CarClass (Ascending), b. then by Year (Ascending), c. 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: a. 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. b. 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 c. 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.
d. Column adjustment may be needed to ensure both the title and the data is visible. e. 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:
a. Create a range named AE that includes every AvgRev value for all Economy cars. b. Create a range
named AH that includes every AvgRev value for all Hybrid cars. c. Create a range named AP that includes
every AvgRev value for all Premium cars. d. 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:
a. In row 1, two or three columns to the right of the AvgRev column: b. Create a label called Average
Quarterly Revenue per Economy Car (Airport) in row 1. c. Immediately (same column, next row down)
under the Economy label create a label called Average Quarterly Revenue per Hybrid Car (Airport). in
row 2. d. 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. e. Immediately (same column, next row
down) under the Premium label, create a label called Average Quarterly Revenue per SUV (Airport) in
row 4. f. 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:
a. Calculate the Average Quarterly Revenue (AvgRev) for the Label-indicated carclass for each of the four
carclasses. b. Use the average function and the named range (AE, AH, AP, of AS) ONLY NOT a range of
cells. c. 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: a. Create a column or bar type of chart that clearly shows the average
revenue (AvgRev) for Hybrid vehicles for 2015 to 2017 by quarter. b. 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. c. 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:
a. Create a column or bar type of chart that clearly shows the average revenue (AvgRev) for Premium
(carclass) vehicles for the four 2015 quarters. b. 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). c. 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.
Format:
• Arial 10 point • Normal/Black • Left-align text in the cell

We offer the bestcustom writing paper services. We have done this question before, we can also do it for you.

Why Choose Us

  • 100% non-plagiarized Papers
  • 24/7 /365 Service Available
  • Affordable Prices
  • Any Paper, Urgency, and Subject
  • Will complete your papers in 6 hours
  • On-time Delivery
  • Money-back and Privacy guarantees
  • Unlimited Amendments upon request
  • Satisfaction guarantee

How it Works

  • Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
  • Fill in your paper’s requirements in the "PAPER DETAILS" section.
  • Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
  • Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
  • From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.