Solved by verified expert: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.
rentalcars.txt
excel_project__3___instructions.pdf
Unformatted Attachment Preview
Year
2017
2017
2015
2016
2016
2015
2016
2016
2016
2015
2015
2016
2015
2017
2017
2016
2017
2016
2016
2015
2015
2015
2016
2015
2016
2017
2015
2016
2015
2016
2016
2015
2015
2016
2017
2015
2015
2016
2015
2017
Quarter
Location
CarClass
Revenue
Q1
Downtown
Economy
” $912,900 ”
Q1
Airport
Economy
” $978,534 ”
Q3
Downtown
Economy
” $752,272 ”
Q4
Airport
Economy
” $896,251 ”
Q1
Downtown
Economy
” $701,460 ”
Q3
Airport
Economy
” $685,248 ”
Q4
Downtown
Economy
” $687,844 ”
Q3
Downtown
Economy
” $665,548 ”
Q2
Downtown
Economy
” $626,232 ”
Q4
Airport
Economy
” $597,979 ”
Q4
Airport
Premium
” $619,900 ”
Q3
Airport
Premium
” $643,168 ”
Q4
Downtown
Premium
” $582,504 ”
Q1
Airport
Premium
” $663,276 ”
Q2
Airport
Premium
” $674,672 ”
Q2
Airport
Premium
” $585,156 ”
Q2
Downtown
Economy
” $561,124 ”
Q3
Airport
Economy
” $579,947 ”
Q1
Airport
Premium
” $552,324 ”
Q3
Downtown
Premium
” $504,800 ”
Q4
Downtown
Economy
” $496,840 ”
Q2
Airport
Economy
” $468,791 ”
Q1
Airport
Economy
” $487,124 ”
Q1
Airport
Economy
” $438,521 ”
Q2
Downtown
Premium
” $488,588 ”
Q2
Airport
Economy
” $581,071 ”
Q2
Downtown
Premium
” $455,424 ”
Q4
Airport
Premium
” $527,900 ”
Q3
Airport
Premium
” $471,816 ”
Q3
Downtown
Premium
” $483,256 ”
Q1
Downtown
Premium
” $415,016 ”
Q2
Downtown
Economy
” $370,128 ”
Q1
Downtown
Economy
” $349,852 ”
Q4
Downtown
Premium
” $421,120 ”
Q1
Downtown
Premium
” $422,288 ”
Q1
Downtown
Premium
” $345,952 ”
Q1
Airport
Premium
” $351,060 ”
Q2
Airport
Economy
” $359,781 ”
Q2
Airport
Premium
” $296,120 ”
Q2
Downtown
Premium
” $321,768 ”
NumCars
6,262 ”
5,891 ”
5,678 ”
5,480 ”
5,151 ”
5,017 ”
4,848 ”
4,799 ”
4,550 ”
4,343 ”
4,221 ”
4,164 ”
4,155 ”
4,106 ”
4,090 ”
3,850 ”
3,824 ”
3,740 ”
3,695 ”
3,657 ”
3,655 ”
3,541 ”
3,475 ”
3,456 ”
3,350 ”
3,349 ”
3,341 ”
3,327 ”
3,259 ”
3,250 ”
2,898 ”
2,897 ”
2,874 ”
2,804 ”
2,750 ”
2,589 ”
2,558 ”
2,323 ”
2,099 ”
2,049 ”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
”
Excel Project 3 – 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 3. 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 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 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 rentalcars.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.
Points
Allocated
Comments
1
Open Excel and save a blank workbook with the following
name:
a. “Student’s First InitialLast Name Excel Project 3”
Example: JSmith Excel Project 3
b. Set Page Layout Orientation to Landscape
0.2
Use Print Preview to review
how the first worksheet
would print.
2
Change the name of the worksheet to Analysis by.
0.1
Step
Requirement
In the Analysis by worksheet:
3
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
Format for text in column
A:
0.3
•
•
•
Arial 10 point
Normal font
Right-align all four
labels in the cells
It may be necessary to adjust the column width so the four
labels are clearly visible.
4
In the Analysis by worksheet with all entries in column C:
a. Enter the appropriate values for your Name, 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).
Format for text in column
C:
0.2
•
•
•
Arial 10 point
Bold
Left-align all four
values in the cells
Step
5
6
7
8
9
Requirement
a. Create three new worksheets: Data, Slide 2, Slide 3.
Upon completion, there should be Analysis by as
well as the three newly created worksheets.
b. Delete any other worksheets.
After clicking on the blank cell A1 (to select it) in the Data
worksheet:
a. Import the text file rentalcars.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.
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. 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.
In the Data worksheet,
a. Sort the entire table by Year (Ascending).
b. Delete rows that contain 2015 data as well as 2017
data.
The resulting table should consist of Row 1 labels followed
by 2016 data, with NO empty cells or rows within the table.
In the Data worksheet:
a. Select the entire table (data and headers) using a
mouse.
b. Copy the table to the both the Slide 2 as well as the
Slide 3 worksheets.
c. The upper left-hand corner of the header/data should
be in cell A1 on Slide 2 and Slide 3
Points
Allocated
Comments
0.2
Format for all data (field
names, data text, and data
numbers)
•
•
0.3
0.5
0.2
0.4
Arial 10 point.
Normal font
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..
Some adjustment may be
necessary to column
widths to ensure all field
names and all data are
readable (not truncated or
obscured).
Step
Requirement
Points
Allocated
Comments
Adjust columns widths if necessary to ensure all data and
field names are readable.
In the Slide 2 worksheet, based solely on the 2016 data:
a. Create a Pivot Table that provides a quarterly breakdown
(columns) of the number of car rentals by car class
(rows). Include totals for rows and columns
b. Place the pivot table two rows below the data beginning
in column A. Ensure that the formatting is as listed in the
Comments column.
10
c. Create a Pivot Table that provides a quarterly breakdown
(columns) of number the of cars rentals by location
(rows). Include totals for rows and columns.
d. Place this pivot table two rows below the above pivot
table beginning in column A. Ensure that the formatting is
as listed in the Comments column.
Format (for both pivot
tables):
•
2.0
•
•
•
Currency ($) with
comma separators (for
thousands)
No decimal places
Arial 10 point
Normal
Adjust the column widths as necessary to preclude data and
title and label truncation.
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 for the four 2016 quarters. Both car types and
quarters must be 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. Use this same
type of bar or column chart for the remaining three charts
to be created.
The charts should allow a
viewer to determine
approximate number or car
rental by car class (first
chart) and number of car
rentals by location (second
chart)
1.8
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. The same type of bar or column chart must be
used throughout this project.
12
In the Slide 3 worksheet, based solely on the 2016 data:
a. Create a Pivot Table that provides a quarterly breakdown
(columns) of the revenue from car rentals by car class
(rows). Totals for rows and columns should be included.
b. Place the pivot table two rows below the data beginning
in column A.
c. Create a Pivot Table that provides a quarterly breakdown
(columns) of the revenue from cars rentals by location
(rows). Totals for rows and columns should be included.
d. Place this pivot table two rows below the above pivot
table beginning in column A.
The charts should have no
more than 8 bars or
columns.
Format (for both pivot
tables):
•
2.0
•
•
Currency ($) with
comma separators (for
thousands)
No decimal places
Arial 10 point
Normal
Step
Requirement
Points
Allocated
Comments
Adjust the column widths as necessary to preclude data and
title and label truncation.
In the Slide 3 worksheet, based solely on the 2016 data:
13
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 2016 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 should be used throughout this project.
The charts should allow a
viewer to determine
approximate number or car
rental by car class (first
chart) and number of car
rentals by location (second
chart)
1.8
d. Using the pivot table created in Step 12 b, 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. The same type of bar chart should be used
throughout this project.
14
a. Open a new, blank Power Point presentation file.
b. Save the Presentation using the following name:
“Student’s First Initial Last Name Presentation”
Example: JSmith Presentation
0.1
Slides are NOT Microsoft Word documents viewed
horizontally. Be brief. Full sentences are not needed. Blank
space in a slide enhances the viewer experience and
contributes to readability.
15
16
Slide 1:
a. Select an appropriate Design to maintain a
consistent look and feel for all slides in the
presentation. Blank slides with 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 should be
displayed.
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 should be the same type and equal size
and be symmetrically placed on the slide.
The charts should have no
more than 8 bars or
columns.
No speaker notes required.
0.8
1.1
Remember, the title on
your slide should convey
what the presentation is
about. Your Name,
Class/Section, and Date
Due can be used in the
subtitle area.
Ensure that there are no
grammar or spelling errors
on your chart and in your
speaker notes.
Step
17
18
19
20
21
Requirement
d. A bullet or two of explanation of the charts may be
included, but is not required if charts are selfexplanatory.
e. Use the speaker notes feature to help you discuss the
bullet points and the charts (four complete sentences
minimum).
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 should 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 selfexplanatory.
e. Use the speaker notes feature to help you discuss the
bullet points and the charts (four complete sentences
minimum).
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 should 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 should be a minimum of one recommendation
based on slide 2 and one recommendation based on
slide 3. Recommendations are strategies or
suggestions to improve 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).
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 should be no larger than
Font size of 6, so it does not distract from the content of the
slide.
Create a footer using “Courtesy of Your Name” so that is
shows on all slides including the Title Slide. The text in this
footer should be on the left side of the slides IF the theme
selected allows. Otherwise let the theme determine the
position of this text.
Create a footer for automated Slide Numbers that appears
on all slides except the Title Slide. The page number should
be on the right side of the slides IF the theme selected
allows. Otherwise let the theme determine the position of the
page number.
Points
Allocated
Comments
1.1
Ensure that there are no
grammar or spelling errors
on your chart and in your
speaker notes.
1.1
Ensure that there are no
grammar or spelling errors
on your chart and in your
speaker notes.
0.2
0.2
Replace the words “Your
Name” with your actual
name.
0.2
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.
Step
Requirement
Points
Allocated
Comments
Ensure that your name does appear on every slide, but the
page numbers start on slide #2. This will involve slightly
different steps to accomplish both.
22
Apply a transition scheme to all slides.
0.1
23
Apply an animation on at least one slide. The animation may
be applied to text or a graphic.
0.1
TOTAL
One transition scheme
may be used OR different
schemes for different
slides
15.0
Be sure you submit BOTH the Excel file and the PowerPoint file in the appropriate Assignment
folder (Excel Project #3).
…
Purchase answer to see full
attachment
