Excel Exercise 2 – MS Excel
Use the following instructions 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 Exercise 2.” Click on Show Rubrics if the rubric is not already displayed.
Summary
A bike rental company has two metropolitan locations, one at the beach and another centrally located near the city park. It has been operating since 2018, and each location summarizes its revenue quarterly. Both locations rent four types of bikes: cruiser, electric, racing, mountain. Rental revenue is maintained separately for the four types of bikes.
Regarding Applications
The online version of Microsoft Office 365 is not appropriate for the course. UMGC provides an Office 365 subscription for all UMGC students that includes access to a fully functioning downloadable, installable version of Office 2019/365. You can access the download via http://office.umgc.edu.
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 Exercises (it is missing about 30 percent of the normal Excel functions). The Chromebook will not allow you to download the full version of Excel from UMGC 365.
If you are using a Chromebook, you can complete the Excel exercises using the online virtual lab option. In LEO, under Content, Virtual Lab Access, you’ll find instructions for accessing the virtual lab.
Tutorials
Tutorials that may help you complete this exercise are available in Module 1 (Weeks 1 and 2) and Module 2 (Week 3).
Instructions
Excel Exercise 2 builds directly on your work from Excel Exercise 1. You should work on the Week 3 tutorials, but wait for feedback from your instructor regarding your work on Excel Exercise 1 before beginning work on this week’s Excel Exercise 2.
Steps
Take Note
Defined Avg Rev Ranges:
BC is Beach Cruiser, BE is Beach Electric, BR is Beach Racing, BM is Beach Mountain.
Open your Excel Exercise 1 file. In the Beach worksheet:
Create a range named BC that includes every AvgRev value for all Cruiser bikes.
Create a range named BE that includes every AvgRev value for all Electric bikes.
Create a range named BR that includes every AvgRev value for all Racing bikes.
Create a range named BM that includes every AvgRev value for all Mountain bikes.
Take Note
- Format:
Arial 10-point font
- Bold
- Left-justified
- In the Beach worksheet:
- In Row 1, two or three columns to the right of the AvgRev column:
Create a label in Row 1 called Average Quarterly Revenue per Cruiser Bike (Beach).
Immediately (same column, next row down, Row 2) under the Cruiser label create a label called Average Quarterly Revenue per Electric Bike (Beach).
Immediately (same column, next row down, Row 3) under the Electric bike label, create a label called Average Quarterly Revenue per Racing Bike (Beach).
Immediately (same column, next row down, Row 4) under the Racing label, create a label called Average Quarterly Revenue per Mountain Bike (Beach).
Apply the formatting instructions.
Take Note
Format:
Arial 10-point
- Normal
Currency ($ and two decimal places).
- Truncation (cut off) of any of the four labels is not acceptable.
- In the Beach 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 BikeType:
- Calculate the Average Quarterly Revenue (AQR) for the Label-indicated BikeType for each of the four BikeTypes.
- Use the average function and the named range (BC, BE, BR, or BM) ONLY (NOT a range of cells).
- Ensure each of four averages is Currency format with two decimal places preceded by a $ with no spaces between the $ and the first number.
- Take Note
Format:
The highlighted AvgRev cell should be Green Fill with Dark Green Text.
In the Beach worksheet:
In the AvgRev column, use conditional formatting to identify all AvgRev values where the AvgRev is greater than (>) the Average Quarterly Revenue FOR THAT BIKETYPE calculated in Step 3 above. DO NOT USE the calculated AQR number in the conditional formatting rule. Instead, use the absolute cell reference where that Average Quarterly Revenue value is stored so that if either Revenue or NumBikes change in a particular row the conditional formatting will be based on the updated value for Average Quarterly Revenue. The result will be four conditional formatting rules using the values calculated for each of the four classes in Step 3.
Take Note
You will have eight bars in your chart, one for each quarter. Each bar represents the average revenue per Electric bike for eight quarters—Beach location only.
In the Beach worksheet:
Create a column or bar type of chart that clearly shows the average revenue (AvgRev) for Electric bikes for 2018 to 2019 by quarter in time-wise sequence.
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.
Place the chart one or two rows below the four labels and left-aligned in the same column where the four Average Quarterly Revenue labels begin.
- Take Note
You will have four bars in your chart, one for each quarter. Each bar represents the average revenue per Racing bike for four quarters—Beach location only.
In the Beach Worksheet:
Create a column or bar type of chart that clearly shows the average revenue (AvgRev) for Racing bikes (BikeType) for the four 2019 quarters.
- 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 BikeTypes).
- Place the chart two or three rows below the first chart and positioned so that left sides of the two charts are in alignment.
How to Enter Answers
For the questions below, present your answers in a very readable format, left-aligned, with and one or two rows beneath the lower chart. You can type your answer in one cell, then highlight and select several rows and columns, selecting Merge Cells and selecting Wrap Text. Change the text from Center to Left justification. Do NOT widen the columns as this will adversely impact the appearance of the worksheet. Play with this a bit. If you simply type your answer on a single line in that will also be okay. 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.
Take Note
Format:
Arial 10-point
Normal/black
Left-align text in the cell
Question 1: Based on the Electric bike 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 two-to-three sentences left-aligned with and under the lower chart. DO NOT change your spreadsheet. Just respond to the question.
Take Note
Format:
- Arial 10-point
Normal/black
Left-align text in the cell
Question 2: If you had to display a chart for the AvgRev (based on the data in this worksheet) for all four BikeTypes for the eight quarters into one chart, what type of chart would you use and why would you use it?
Label your response Question 2. Answer this question in two-to-three sentences in a new row left-aligned with the Question 1 response and one or two rows beneath it.
Begin work on this section in the DataNotes worksheet. We’ll now complete the Field Definitions section. Here you have three columns for Field, Type, and Description. One example is given for you for Location. Enter the remainder of the Fields from your Data worksheet (the original data) and, based on content from Module 1, identify the Data Type (e.g., Discrete, Continuous, Categorical, Ordinal) for each field and add a brief description/clarification.
Take 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.
Follow these steps:
Create new worksheets: Data2, Initial Analysis, and Profit Analysis
Upon completion, you should have seven worksheets: DataNotes, Data, Sorted, Beach, Data2, Initial Analysis, and Profit Analysis. Be sure they appear in this order from left to right.
- Delete any other worksheets.
Take Note
- Format:
- For all data (field names, data text, and data numbers) use 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.
After clicking on the blank cell A1 (to select it) in the Data2 worksheet, import the text file 2019rentalbikes.txt into the Data2 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 NumBikes 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 Data2 worksheet, sometimes when text data is imported into a worksheet, a new worksheet is created. If this happens, delete the blank Data2 worksheet. Then change the name of the new worksheet with the imported data as “Data2.” Make sure worksheets are in the correct order.
Take Note
Some adjustment may be necessary to column widths to ensure all field names and all data are readable (not truncated or obscured).
In the Data2 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).
Pick a style with the styles group to format the table (choose a style that shows banded rows, i.e., rows that alternate between two colors).
- The style should highlight the field names in the first row.
Ensure NO blank cells are part of the specified data range.
- Ensure the table has headers.
- Ensure that Header Row and Banded Rows are selected in the Table Style Options Group Box, but do NOT select a Total row.
- In the Data2 worksheet:
Sort the entire table by Year (ascending).
There should be one row for titles and sixty-four rows of data.
There should be no column or data truncation.
Copy the contents of the Data2 worksheet to both the Initial Analysis and Profit Analysis worksheets, with cell A1 as upper left-hand corner in both worksheets.
The two worksheet Excel tables should meet the formatting requirements of the Data2 worksheet as specified.
There should be no column or data truncation.
Each of the destinations should have an Excel table.
Adjust column widths to avoid title and data truncation as necessary.
Take Note
Format (for PivotTable label, text, data as appropriate):
- Currency (leading dollar sign) with no space between the $ and the first number
No decimal places
Arial 10-point
Normal font
No title or data truncation in the PivotTable
Right-align the five column labels
Left-align the three row labels (Beach, CityPark, Grand Total)
In the Initial Analysis worksheet, using the Excel table just copied there:
Create a PivotTable using all 2018 and 2019 data.
Locate the top edge of the PivotTable at the top edge of Row 1 and the left edge a few columns to the right of the data.
Display the total revenue for all four types (BikeType) of bikes in four columns. (Put BikeType in the columns area of the PivotTable fields).
- Display the total revenue for both Beach and CityPark locations in two rows. (Put Location in the rows area of the PivotTable fields). In the PivotTable fields set-up, DO NOT put a check in the box for Quarter.
Row and column calculations (sum) are required so that the total revenue for EACH location and the total revenue for EACH BikeType are visible in the PivotTable. There must be a grand total that represents the sum of ALL revenue.
Ensure that the formatting is correct.
Left align both locations and Grand Total below them and right align four Bike Types and Grand Total to the right of the four Bike Types in the PivotTable.
Final step: Adjust column widths, if necessary, to eliminate title and data truncation of the PivotTable.
- Take Note
The chart should allow a viewer to determine approximate revenue totals for each Bike Type and total revenue at each location.
The chart should have no more than eight bars or columns.
In the Initial Analysis worksheet, using the PivotTable:
Create a column or bar type of chart that shows the total revenue for each of the four Bike Types at each location.
Add a title that reflects the information presented by the chart.
- The upper left corner of the chart should be one or two rows below the above referenced PivotTable and left-aligned with the PivotTable.
The chart should clearly indicate location and type of bike.
- For the Next Two Questions…
For the two questions that follow, present your answers in the Initial Analysis worksheet in a readable format. These answers should be placed on the worksheet one or two rows below and left-aligned with the chart. Do not let the answers be “split” over more than one page. You can type your answer in one cell, then highlighting and selecting several rows and columns, select Merge Cells and select Wrap Text. Do NOT widen the columns as this will adversely impact the appearance of the PivotTables 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, that will also be okay. Be sure the entire answer can be read without the reader having to change any formatting, scroll horizontally, or view the results in the cell contents box. Use Currency (leading dollar sign) with no space between the $ and the first number format for dollar figures.
Take Note
- Use the following text format:
Arial 10-point
Normal/black
Left-align text in the cell
Question 3: Based on the PivotTable above, which location for 2018 and 2019 combined produced the greater revenue? How much revenue was it?
Label your response Question 3.
Answer this question in one or two sentences.
Take Note
- Use the following text format:
Arial 10-point
Normal/black
Left-align text in the cell
Question 4. Based on the PivotTable above, which combination of location and Bike Type for 2018 and 2019 combined produced the most revenue? How much revenue was it?
Label your response Question 4.
Answer this question in one-to-two sentences. Your response should have a location and a Bike Type.
Take Note
Use the following text format:
Arial 10-point
Normal/black
- Left-align text in the cell
In the Profit Analysis worksheet:
- In Row 1 and in the column to the immediate right of NumBikes label, add the following labels in the following order:
Expenses, Profit, ProfitperBike (three labels, each at the top of a column). - If necessary, adjust column widths to eliminate truncation.
- The Excel table should be extended three columns to the right after adding these three new columns.
- Take Note
- Use the following text format:
- Arial 10-point
Normal/black
Left-align text in the cell
- In the Profit Analysis worksheet:
In Row 1, two columns to the right of the Profit per Bike label add the label: Electric Expense per Bike.
- In Row 2, in the same column as Electric Expenses add the label Non-Electric Expense per Bike.
- DO NOT adjust column widths for these two labels. The blank column to the left of these two labels will prevent these two labels from becoming a right extension of the Excel table.
- Take Note
Format:
Currency (leading dollar sign) with no space between the $ and the first number
- No decimal places
Arial 10-point
- Normal font
- In the Profit Analysis worksheet:
In the next totally visible cell (no content bleed over from label) in Row 1 add 15 for Electric Expense per Bike in Row 1 and in Row 2 add 5 for Non-Electric Expense per Bike. The absolute cell addresses of these two numeric values will be used to calculate Expenses in the Excel table for each row.
- Take Note
- The use of absolute cell addresses allows those Expenses values to be changed and then by recalculation, update the entire Excel table. It can be helpful to double-check an Electric and a Non-Electric row with a calculator for calculation accuracy.
Format:
Currency (leading dollar sign) with no space between the $ and the first number
No decimal places
Arial 10-point
Normal font
In the Profit Analysis worksheet:
Expenses costs include maintenance, cleaning, and administrative expenses associated for a Bike rental. The Expenses for one rental transaction will be determined from the values specified in the worksheet. DO NOT enter the values in this column on a cell-by-cell basis.
In the first cell directly under the Expenses label, calculate the total Expenses that correspond to the number of Bikes rented IN THAT ROW (NumBikes) using an “IF” statement as part of the formula to determine the value of Expenses for one bike.
First, use the “IF” statement to determine the single bike Expenses (by comparing the values in the “BikeType” column) and second, edit the cell contents to multiply it by the NumBikes so that the Expenses value represents the total Expenses costs for the Number of Bikes (NumBikes) rented that row. Expenses = NumBikes * Expense per Bike
Use the absolute cell address for the numerical Expenses value for ONE bike which should then be multiplied by the number of Bikes in each row.
Adjust column width as needed to eliminate truncation after format adjustments.
Take Note
- Format:
Currency (leading dollar sign) with no space between the $ and the first digit.
- No decimal places for Profit and two decimal places for ProfitperBike data
- Arial 10-point
- Normal font
- In the Profit Analysis Worksheet:
- In the first cell directly under the Profit label, calculate total profit by subtracting Expenses from revenue (should be a positive number). (Revenue – Expenses). Then repeat the formula for each row below.
- In the first cell directly under the ProfitperBike label, divide Profit by NumBikes (Profit/NumBikes). Then repeat the formula for each row below.
- Adjust column widths as needed to eliminate truncation after formatting.
- Verifying the accuracy of a few cells calculation with a calculator can be helpful.
Take Note
Format (for PivotTable labels, text, data as appropriate):
Currency (leading dollar sign) with no space between the $ and the first number.
No decimal places
Arial 10-point
Normal font
In the Profit Analysis worksheet:
- Create a PivotTable using all 2018 and 2019 data.
Locate the top edge of the PivotTable in Row 4 and left-aligned with the left edge of the Electric Expenses label.
- Display the total profit for all four types (BikeType) of bikes in four columns. (Put BikeType in the columns area of the PivotTable fields).
- Display the total profit for both Beach and CityPark locations in two rows. (Put Location in the rows area of the PivotTable fields).
- Row and column calculations (sum) are required so that the total profit for EACH location and the total profit for each BikeType are visible in the pivot table. There must be a grand total that represents the sum of all profit. In the PivotTables fields set-up, DO NOT put a check in the box for Quarters.
- Format your work appropriately.
Left align both locations and Grand Total below and right align four Bike Types and the Grand Total to the right of the four Bike Types in the PivotTable.
Final step: Adjust columns widths, if necessary, to eliminate title and data truncation of the PivotTable.
Row and column calculations (sum) are required so that the total profit for EACH location and the total profit for EACH BikeType are visible in the PivotTable. There must be a grand total (sum) that represents the sum of ALL profit.
Take Note
This PivotTable displays average NOT total.
Format (for PivotTable labels, text, data as appropriate):
Currency (leading dollar sign) with no space between the $ and the first number.
No decimal places
Arial 10-point
Right-align PivotTable column labels
- Normal font
In the Profit Analysis worksheet:
- Create a PivotTable using all the 2018 and 2019 data.
- Position the upper left corner to left align with the PivotTable above and two rows below the PivotTable above.
Display the average profit for all four types (BikeType) of bikes in four columns. (Put BikeType in the columns area of the PivotTable fields).
Display the average profit for both Beach and CityPark locations in two rows. (Put Location in the rows area of the PivotTable fields).
Row and column calculations (average) are required so that the average profit for EACH location and the average profit for EACH BikeType are visible in the PivotTable. There must be a composite average (average) that represents the average of all profit values. In the PivotTables Fields set-up, DO NOT put a check in the box for Quarters.
Format your work according to the instructions.
Left align both locations and Grand total below and right align four Bike Types and the Grand Total to the right of the four Bike Types in the PivotTable.
Final step: Adjust column widths, if necessary, to eliminate title and data truncation in BOTH PivotTables.
Answering the Next Questions…
For the questions below, in the Profit Analysis worksheet, present your answers in a readable format left-aligned with and one or two rows beneath the lower PivotTable. 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 items 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 that will also be okay. 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. Use Currency (leading dollar sign) with no space between the $ and the first number format for dollar figures.
- Take Note
Format:
- Arial 10-point
- Normal/black
Left-align text in the cell
Question 5: Based on the upper PivotTable, which of the two locations produces the greater profit (include the profit figure) and which Bike Type of the four produces the least profit (include the profit figure)?
Label your response Question 5. Answer this question in two-to-three sentences one or two rows below and left-aligned with the lower PivotTable.
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.