Excel Exercise 2: Charts and Conditional Formatting

Smart Nursing Papers - Nursing Questions - Excel Exercise 2: Charts and Conditional Formatting

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 .

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

  1. Open your Excel Exercise 1 file. In the Beach worksheet:
    1. Create a range named BC that includes every AvgRev value for all Cruiser bikes.
    2. Create a range named BE that includes every AvgRev value for all Electric bikes.
    3. Create a range named BR that includes every AvgRev value for all Racing bikes.
    4. Create a range named BM that includes every AvgRev value for all Mountain bikes.
  1. In the Beach worksheet:
    1. In Row 1, two or three columns to the right of the AvgRev column:
    2. Create a label in Row 1 called Average Quarterly Revenue per Cruiser Bike (Beach).
    3. Immediately (same column, next row down, Row 2) under the Cruiser label create a label called Average Quarterly Revenue per Electric Bike (Beach).
    4. Immediately (same column, next row down, Row 3) under the Electric bike label, create a label called Average Quarterly Revenue per Racing Bike (Beach).
    5. Immediately (same column, next row down, Row 4) under the Racing label, create a label called Average Quarterly Revenue per Mountain Bike (Beach).
    6. Apply the formatting instructions.
  1. 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:

    1. Calculate the Average Quarterly Revenue (AQR) for the Label-indicated BikeType for each of the four BikeTypes.
    2. 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.

  1. 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.

  1. In the Beach worksheet:
    1. 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.
    2. 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.
    3. 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.
  1. In the Beach Worksheet:
    1. Create a column or bar type of chart that clearly shows the average revenue (AvgRev) for Racing bikes (BikeType) for the four 2019 quarters.
    2. 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).
    3. 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.

  1. 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.

  1. 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.

  1. 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.
  1. Follow these steps:
    1. 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.

    2. Delete any other worksheets.
  1. 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.

  1. In the Data2 worksheet:
    1. Create an Excel table with the recently imported data (Office 2019/365 may have automatically created an Excel table when you imported the data).
    2. 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).
    3. The style should highlight the field names in the first row.
    4. Ensure NO blank cells are part of the specified data range.
    5. Ensure the table has headers.
    6. Ensure that Header Row and Banded Rows are selected in the Table Style Options Group Box, but do NOT select a Total row.
  1. In the Data2 worksheet:
    1. Sort the entire table by Year (ascending).
    2. There should be one row for titles and sixty-four rows of data.
    3. There should be no column or data truncation.
  1. 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.
    1. The two worksheet Excel tables should meet the formatting requirements of the Data2 worksheet as specified.
    2. There should be no column or data truncation.
    3. Each of the destinations should have an Excel table.
    4. Adjust column widths to avoid title and data truncation as necessary.
  1. In the Initial Analysis worksheet, using the Excel table just copied there:
    1. Create a PivotTable using all 2018 and 2019 data.
    2. 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.
    3. Display the total revenue for all four types (BikeType) of bikes in four columns. (Put BikeType in the columns area of the PivotTable fields).
    4. 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.
    5. 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.
    6. Ensure that the formatting is correct.
    7. 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.
    8. Final step: Adjust column widths, if necessary, to eliminate title and data truncation of the PivotTable.
  1. In the Initial Analysis worksheet, using the PivotTable:
    1. Create a column or bar type of chart that shows the total revenue for each of the four Bike Types at each location.
    2. Add a title that reflects the information presented by the chart.
    3. The upper left corner of the chart should be one or two rows below the above referenced PivotTable and left-aligned with the PivotTable.
    4. 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.

  1. Question 3: Based on the PivotTable above, which location for 2018 and 2019 combined produced the greater revenue? How much revenue was it?
    1. Label your response Question 3.
    2. Answer this question in one or two sentences.
  1. 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?
    1. Label your response Question 4.
    2. Answer this question in one-to-two sentences. Your response should have a location and a Bike Type.
  1. In the Profit Analysis worksheet:
    1. 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).
    2. If necessary, adjust column widths to eliminate truncation.
    3. The Excel table should be extended three columns to the right after adding these three new columns.
  1. In the Profit Analysis worksheet:
    1. In Row 1, two columns to the right of the Profit per Bike label add the label: Electric Expense per Bike.
    2. 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.

  1. 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.

  1. In the Profit Analysis worksheet:
    1. 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.
    2. 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.
    3. 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
    4. 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.
    5. Adjust column width as needed to eliminate truncation after format adjustments.
  1. In the Profit Analysis Worksheet:
    1. 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.
    2. In the first cell directly under the ProfitperBike label, divide Profit by NumBikes (Profit/NumBikes). Then repeat the formula for each row below.
    3. Adjust column widths as needed to eliminate truncation after formatting.
    4. Verifying the accuracy of a few cells calculation with a calculator can be helpful.
  1. In the Profit Analysis worksheet:
    1. Create a PivotTable using all 2018 and 2019 data.
    2. Locate the top edge of the PivotTable in Row 4 and left-aligned with the left edge of the Electric Expenses label.
    3. Display the total profit for all four types (BikeType) of bikes in four columns. (Put BikeType in the columns area of the PivotTable fields).
    4. Display the total profit for both Beach and CityPark locations in two rows. (Put Location in the rows area of the PivotTable fields).
    5. 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.
    6. Format your work appropriately.
    7. 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.
    8. 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.

  1. In the Profit Analysis worksheet:
    1. Create a PivotTable using all the 2018 and 2019 data.
    2. Position the upper left corner to left align with the PivotTable above and two rows below the PivotTable above.
    3. Display the average profit for all four types (BikeType) of bikes in four columns. (Put BikeType in the columns area of the PivotTable fields).
    4. Display the average profit for both Beach and CityPark locations in two rows. (Put Location in the rows area of the PivotTable fields).
    5. 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.
    6. Format your work according to the instructions.
    7. 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.
    8. 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.

  1. 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.

  1. Question 6: Based on the lower PivotTable, which combination of location and BikeType is MOST profitable and which combination is least profitable?

    Label your response Question 6. Answer this question in two-to-three sentences in a new row left-aligned with the Question 5 response and one or two rows beneath it.