Excel Project 3 – MS Excel

Excel Project 3 – MS Excel
(Spring 2021)
Note: Microsoft Office 365, the online version, is not appropriate for the course. But anyone who has
an Office 365 subscription has access to a fully functioning downloadable version of Office 2013 or
2016 If you do not have an Office 365 subscription you may download the 2016 or 2019 version of
Microsoft Office Professional Plus or Microsoft Office Home & Business from OntheHub. (See the
Course Resources link.)
If you are using a Chromebook, the only Excel product available from the Google Play Store is not a
full version of Excel and cannot be used to complete the steps in our Excel projects (it is missing
about 30% of the normal Excel functions). The Chromebook will not allow you to download the full
Excel from UMGC 365
If you are using a Chromebook, you can complete the Excel Projects using the online Virtual Lab
option. In LEO, under Content, Virtual Lab Access, you’ll find instructions for accessing the virtual
lab.
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 2018 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 2021rentalcars.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.
Step Requirement Comments
1
Open Excel and save a blank workbook with the following name:
a. “Student’s Last Name First Name Initial Excel Project 3”
Example: Smith Jane P Excel Project 3
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.
Step Requirement Comments
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 text in
column A:
• Arial 10 point
• Normal font
• Right-align all four
labels in the cells
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).
It may be necessary to adjust the column width so the four labels
are clearly visible within Column C (not extending into Column D).
Format for text in
column C:
• Arial 10 point
• Bold
• Left-align all four
values in the cells
5
a. 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, import the text file 2021rentalcars.txt into the Data
worksheet. In Excel 2019/365 this is done via the Data tab, Get &
Transform Data: Click ‘From Text/CSV’ and follow the prompts. If
you are using an earlier version of Excel, you can find additional
instructions under Additional Tutorials for Excel 2013/2016,
Importing a Text File into Excel (Excel 2016 or earlier). The data
should begin in Column A. Row 1 should contain the labels for
each Column..
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 n the correct
order per Item 5.
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..
Step Requirement Comments
7
In the Data worksheet:
Create an Excel table with the recently imported data( Office
2019/365 may have automatically created an Excel Table when you
imported the data).
a. 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).
b. The style must highlight the field names in the first
row. These are your table headers.
c. Ensure NO blank cells are part of the specified data range.
d. 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).
b. Delete rows that contain 2018 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 2019 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 and the cell values
retain the same formatting but not the formatting of the Excel
table (e.g., no banded rows).
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 (Slide 2, Slide 3, Filter Analysis).
Adjust columns widths if necessary to ensure all data and field
names are readable.
10
In the Slide 2 worksheet, based solely on the 2019 data:
a. Create a Pivot Table that displays the total number of car rentals
for each car class in rows and the total number of car rentals for
each of the four quarters in columns for 2019. A grand total for
the total number of rentals (NumCars) 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 beginning in row 1 two columns to the right
of the data. 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 in two rows and the total number of car rentals
for each of the four quarters in columns for 2019. 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
upper pivot table and left aligned with the upper pivot table.
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
column header
labels to the right of
the four quarter
labels in both pivot
tables
Step Requirement Comments
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.
11
In the Slide 2 worksheet, based solely on the 2019 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 2019 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 two rows below the lower pivot table
and left-aligned. 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 2019 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.
The charts must allow a
viewer to determine
approximate number or
car rentals 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
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 2019 data:
a. Create a Pivot Table that displays the total revenue for each car
class in rows and the total revenue for each of the four quarters
in columns for 2019. 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 in the first row and two columns to the right
of the data.
c. Create a Pivot Table that must display the total revenue for each
quarter in columns and for each of the two locations in rows for
2019. 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 two locations.
d. Place this pivot table two rows below the pivot (step 12a) and
left-aligned with the upper pivot table.
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) and
no space between
the $ and the first
number
• No decimal places
• Arial 10 point
Normal
• Right-align the Q1
through Q4 and
Grand Total column
labels that follow the
four Quarter labels
in both pivot tables
Step Requirement Comments
13
In the Slide 3 worksheet, based solely on the 2019 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 2019 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 two rows below and left-aligned with
the bottom pivot table. The same type of bar chart must be used
throughout this project.
d. Using the pivot table created in Step 12 c, create a bar or
column chart that displays the revenue from car rentals by
location for the four 2019 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 must be used throughout this
project.
The charts must allow a
viewer to determine
approximate revenue
by car class (first chart)
and revenue 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.”
14
In the Filter Analysis worksheet, if necessary, remove all TABLE
formatting from the Excel Table but keep the cell values in the same
format as on previous worksheets so that it is one row of labels in
Row 1 followed by 32 rows of 2019 formatted as specified rental car
data. Turn on filtering for all 33 rows.
15
In the Filter Analysis worksheet:
a. Select Economy ONLY and Quarter 1 ONLY in their
respective columns.
b. In row 34, the next blank row after the data, in the revenue
and number of cars columns, calculate the sum of that
column by adding the contents of the addresses of the cells
resulting from the filter action (should be two values for
Revenue and two values for NumCars).
c. Format the two values to match the data above in the
particular respective column.
The two values should
match the values from
the previously created
pivot tables.
16
a. Open a new, blank Power Point presentation file.
b. Save the Presentation using the following name:
“Student’s Last Name First Name Initial Presentation”
Example: Smith Jane P Presentation
Step Requirement Comments
17
Slides are NOT Microsoft Word documents viewed horizontally. Be
brief. Full sentences should not be used on the slide. Bullet points
only. Blank space in a slide enhances the viewer experience and
contributes to readability. (Speaker notes should be complete
sentences.)
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 must be displayed.
No speaker notes
required.
Remember, the title on
your slide must convey
what the presentation is
about. Your Name,
Class/Section, and Date
Due can be used in the
subtitle area.
18
Slide 2:
a. Title this slide “Number of Cars Rented in 2019”
b. Add two charts created in the Slide 2 worksheet of the Excel
file
c. The charts must be the same type and equal size and be
symmetrically placed on the slide.
d. A bullet or two of explanation of the charts may be included,
but is not required if charts are self-explanatory.
e. Use the speaker notes feature to help you discuss the bullet
points and the charts (four complete sentences minimum).
Ensure that there are no
grammar or spelling
errors on your chart and
in your speaker notes.
19
Slide 3:
a. Title this slide “Car Rental Revenue in 2019”
b. Add two charts, created in the Slide 3 worksheet of the Excel
file.
c. The charts must 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 self-explanatory.
e. Use the speaker notes feature to help you discuss the bullet
points and the charts (four complete sentences minimum).
Ensure that there are no
grammar or spelling
errors on your chart and
in your speaker notes.
20
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 must 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 must 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).
Ensure that there are no
grammar or spelling
errors on your chart and
in your speaker notes.
21
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 must be no larger than Font size of 6, so it does
not distract from the content of the slide.
Step Requirement Comments
22
Create a footer for your name and automated Slide Numbers that
appears on all slides except the Title Slide, the page number must
be on the right side of the slides IF the theme selected allows.
Otherwise let the theme determine the position of the page number
Ensure that your name does appear on every slide in the footer, but
the page numbers start on slide #2. This will involve slightly different
steps to accomplish both
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.
23 Apply a transition scheme to all slides.
One transition scheme
may be used OR
different schemes for
different slides
24 Apply an animation on at least one slide. The animation may be
applied to text or a graphic.
Be sure you submit BOTH the Excel file and the PowerPoint file in the appropriate Assignment
folder (Excel Project #3).

Friend Essay
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Work with Us

High Quality Essays

FriendEssay helps with essay papers for all levels of academic courses such as high school, undergraduate or graduate level assignments (any subject). The Asssignment writers at Friend Essays have years of experience in helping students write their essays with expertise in various fields like Engineering, Economics, Marketing etc. You can trust our essay writers to take care of your work without any mistakes or plagiarism. Whether you need a simple 5-page essay or an extensive thesis paper on a complex topic - we will do it with professionalism, accuracy, and skill.

Experienced Academic Writers

All of our paper writers are native English speakers with at least three years’ experience in essay writing. They know what they are doing when it comes to essay formatting requirements for the different types of papers; MLA, APA or Harvard formats - we can handle them all without any difficulty whatsoever.

Free Unlimited Revisions

Paper modifications are like a breath of fresh air! If you need any revisions done on your paper, simply click the revision button and we will happily help.

Swift Delivery and 100% Money Back Guarantee

Friend essay is different from other essay writing companies because they are the only company in the world that offers a money back guaranty on any order!

Original and Confidential

We're committed to making sure that all documents we produce for you are 100% original. Not only do our editors check every quotation in a text, but they also promise the utmost confidentiality with each document service.

24/7 Customer Support

The customer support agents at our company are always available to help you. They can be reached 24 hours a day, 7 days a week and will work hard each time they try to get in touch with you so that the experience is as good as it gets.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Looking for online essay writer to do an assignment for you? FriendEssay offers a wide range of academic help service such as admission essay writing, research paper help, dissertation writing among others. Simply place an order to get a discount today!

Essays

Homework Help Services

The best part is that we offer essay help on any topic - whether it's literature, history, math or even philosophy! Our specialists know their way around these subjects like no other which means they'll take care of your work as if it were their own and get back to you with quality essays within the time frame specified by our customer service team.

Admissions

Business and Admission Essay

For those seeking professional business writing services such as resume writing, business plan writing, admission essays writing, we are also here for you. We have professionals in distinct business areas who have handled all types of business needs. So, worry no more.

Reviews

Articles and Blog Writing Services

We also specialize in article and blog writing services, so if you're looking to get some content published on the web and need a reliable content writer, simple check the “help with my assignment” service from FriendEssay.com!

Reviews

Editing Support

Whether you need someone who will write an essay from scratch on a topic of your choice or if you want us to edit one already written by yourself, FriendEssay has talented professionals available 24/seven so there's never any essay help delay