Excel Project 1 – MS Excel | Complete Solution

Excel Project 1 – 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 1." 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 including
graphing, 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 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 Excel Project 1-Data.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.

Tutorials
Note: Tutorials that address activities used in this project have been assigned in Week 1, 2 and 3.
Requirement Points
Allocated
Comments
1
Open Excel and save a blank worksheet with the following name:
a. “Student’s First InitialLast Name Excel Project 1”
Example: JSmith Excel Project 1.
b. Set Page Layout to Landscape
0.2
Use Print Preview to review
how spreadsheet would
print.
2
a. Change the name of the worksheet to Analysis by.
0.1
3
a. In the Analysis by worksheet, enter the four labels
vertically in column A 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 in the cell
d. It may be necessary to adjust the column width so
the four labels are clearly visible.
0.3
Use the following text
format:
Arial 10 point
Bold
Align values Right in the
cell
4
a. In the Analysis by worksheet, with all entries in
column C, enter the appropriate values for Name,
Class and Section, Project, Date Due.
b. It may be necessary to adjust the column width so
the four labels are clearly visible.
c. Use the formatting listed in the Comments
0.3
Use the following text
format:
Arial 10 point
Bold
Align values left in the cells
Requirement Points
Allocated
Comments
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.
0.2
6
a. If necessary, reorder the four worksheets so they
are in the following order: Analysis by, Data,
Sorted, Airport. 0.1
7
In the Data worksheet,
 import the text file Excel Project 1-Data.txt.
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 to
Currency format with NO decimal points and to change
NumCars to numerical format, with no decimal points, as a
result of the import operation.
This may or may not occur, but in case it does it needs to
be corrected.
0.5
Format all data (field
names and numbers) to
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.
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,
c. The style should highlight the field names in the first
row.
d. Ensure NO blank spaces are part of the specified
data range. Insure the table has headers.
e. Insure that Header Row and Banded Rows are
selected in the Table Style Options Group Box.
0.6
Some adjustment may be
necessary to column
widths to insure 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 insure all data
and field names are readable.
0.4
10
In the Sorted worksheet,
a. sort the data by location (ascending)
b. (All cells with data in the table should be selected)
0.2
Insure all the table is
selected, NOT just the
location column.
11
a. Copy the entire table from the Sorted worksheet to
the Airport worksheet.
b. Some column adjustments may be necessary to
insure the field names as well as the data is all
readable.
0.2 Insure all cells with data
are selected.
12 In the Airport worksheet, 0.3
Requirement Points
Allocated
Comments
a. delete all the rows in the table where the data is for
Downtown,
b. the field names should remain at the top of the
table, and
c. the remaining data should be for the Airport location.
13
In the Airport worksheet,
a. sort all the data first by CarClass (Ascending),
b. then by Year (Ascending),
c. then by Quarter (Ascending).
0.3 Insure that all cells with
data have been selected.
14
In the Airport worksheet,
a. add a new column head AvgRev that calculates the
Average Revenue for a row,
b. add a formula to calculate the AvgRev by dividing
the Revenue by the NumCars for all rows in the
table, and
c. use 2 decimal points in the values.
d. Column adjustment may be needed to insure both
the title and the data is visible.
e. Use the formatting instructions in the Comments.
0.5
The format of the table
should be extended to the
new column.
Use the following text
format:
Arial 10 point
Bold
Insure all of the data values
for AvgRev are Currency
Format (using the Format
Cells option).
15
In the Airport worksheet,
a. create a range named AE that includes every
AvgRev value for Economy cars, and
b. create a range named AP that includes every
AvgRev value for Premium cars.
0.4
AE stands for Airport
Economy, AP stands for
Airport Premium.
16
In the Airport worksheet,
a. on the third row below the final row of the data,
b. create a label called Average Quarterly Revenue
per Economy Car (Airport).
c. Use the formatting instructions in the Comments.
0.3
Use the following text
format:
Arial 10 point
Bold
17
In the Airport worksheet,
a. in the first fully visible cell to the right of the label,
b. calculate the Average Quarterly Revenue per
Economy Car (Airport) using the average function
and the named range AE. Insure the Average is
Currency format with two decimal points.
No credit will be awarded if anything other than AE as the
function range is used instead of the named range.
0.5
Use the following text
format:
Arial 10 point
Normal
18
In the Airport worksheet,
a. on the fourth row below the final row of the data,
b. create a label called Average Quarterly Revenue
per Premium Car (Airport).
c. Use the formatting instructions in the Comments.
0.3
Use the following text
format:
Arial 10 point
Bold
19
In the Airport worksheet,
a. in the first fully visible cell to the right of the label,
b. calculate the Average Quarterly Revenue per
Premium Car (Airport)
c. using the average function and the named range
AP. Insure the Average is Currency format.
d. Use the formatting instructions in the Comments.
No credit will be awarded if anything other than AP as the
function range is used instead of the named range.
0.4
Use the following text
format:
Arial 10 point
Normal
20 In the Airport worksheet, 0.75
Requirement Points
Allocated
Comments
a. create a bar chart that clearly shows the average
revenue per economy car versus the quarter for
2015.
b. Provide an internal chart title that explains what is
portrayed on the graph.
c. Place the chart to the right of the data.
21
In the Airport worksheet,
a. create a bar chart that clearly shows the average
revenue per premium car versus the quarter for
2015.
b. Provide an internal chart title that explains what is
portrayed on the graph.
c. Place the chart to the right of the data and below the
first chart.
0.75
For the questions below, present your answers in a very readable format under the data. These
answers may be placed on the page below your chart if there is not sufficient room on page 1. Do
not let the answers be "split" over more than one page. You can type your answer in one cell (in
Column A), then highlighting and selecting 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 data above. You will want to change the text from Center to Left justification. Play with this a
bit. If you simply type your answer on a single line in Column A, that will also be ok. But be sure
the entire answer can be read without the reader having to change any formatting.
22
Question1: Is there a trend that is shared between the
Economy and Premium average revenue per car based
on the two graphs? Along with the yes or no explain why
there is or why there is not a trend.
a. Label your response Question 1. Answer this
question in 2 to 3 sentences after the average
revenue per premium car (airport). DO NOT
change your spreadsheet. Just respond to the
question. (1.0)
b. Readability, format and location (0.2)
1.2
Use the following text
format:
Arial 10 point
Normal/Black
Align text left in the cell
Position on worksheet
23
Question 2: Explain one possible cause for the average
revenue per share in Q3. The answer should relate to
both economy and premium cars.
a. Label your response Question 2. Answer this
question in 2 to 3 sentences in a new row under
your response to Question 1. DO NOT change
your spreadsheet. Just respond to the question.
(1.0)
b. Readability, format and location (0.2)
1.2
Use the following text
format:
Arial 10 point
Normal/Black
Align text left in the cell
Total Points 10