Formulas and Functions

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.

You should complete this assignment using the Excel_Exercises_Template.xls file that accompanies these instructions.

The data for this case resides in the file 2019rentalbikes.txt and can be downloaded by clicking on the Assignments tab, then on the data file 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.

Steps

Take Note

Use Print Preview to review how the first worksheet would print.

Open the Excel Exercises template:

Enter your name in Cell B1 of the DataNotes worksheet.

Set Page Layout Orientation to Landscape.

For Excel Exercise 1, disregard the Field Definitions section of Data Notes.

Follow these steps: 

1. Create new worksheets: Data, Sorted, and Beach.
   Upon completion, there should be Data Notes as well as the three newly created worksheets.

Delete any other worksheets.

1. 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.
2. If necessary, reorder the four worksheets so they are in the following order: Data Notes, Data, Sorted, Beach.
3. Take Note

For all data (field names, data text, and data numbers), use 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.

1. After clicking on the blank cell A1 (to select it) in the Data worksheet, import the text file 2019rentalbikes.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 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 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.”

Take Note

1. Some adjustment may be necessary to column widths to ensure all field names and all data are readable (not truncated or obscured).

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

1. 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 Data worksheet:

1. Select the entire table (data and headers) using a mouse.

Copy the table to the Sorted worksheet.

The upper left-hand corner of the header/data in Sorted should be in cell A1.

Adjust column widths if necessary, to ensure all data and field names are readable.

Make sure that all the format requirements for the Data Worksheet are met in the Sorted Worksheet.

Take Note

Ensure the entire table is selected, NOT just the location column.

All data (field names, data text, and data numbers) should be in Arial 10-point.  

1. In the Sorted worksheet:

Select all the cells with data in the table (including the header row).

Sort the data by location (ascending).

Adjust column widths if necessary, to ensure all data and field names are readable with no truncation.

Take Note

Ensure all cells with data are selected.

All data (field names, data text, and data numbers) should be in Arial 10-point.  

Follow these steps:

Copy the entire table from the Sorted worksheet to the Beach worksheet. In the Beach worksheet, the field names should be in Row 1 and the Year column should be in Column A.

Some column adjustments may be necessary so that the field names as well as the data are all legible.

Make sure that the same format requirements for the Data Worksheet are met in the Beach Worksheet.

Take Note

Delete the rows from the Excel table, NOT just the data.

In the Beach worksheet:

Delete all the rows in the table where the data is for City Park.

1. The field names should remain at the top of the table.

The remaining data should be for the Beach location. 

Take Note

Ensure that all cells with data have been selected before sorting. This sort is completed in a single step, not three separate steps.

1. Note: If the sort does not result in sequential row listing of all bikes of the same class, the ranges will be difficult to create as well as to evaluate.
2. In the Beach worksheet, using a custom sort:
3. Sort all the data first by BikeType (Ascending).
4. Then by Year (Ascending).
5. Then by Quarter (Ascending).

Take Note

The format of the table must be extended to the new column.

1. Format the table as follows:

Arial 10-point

1. data values for AvgRev are Currency Format (using the Format Cells option). A typical AvgRev value could be $123.45, for instance with no space between the $ and the 1
2. Revenue and NumBikes format from Data Worksheet
3. In the Beach worksheet:
4. Add a new column heading AvgRev for the column where the average revenue per bike for each row will be calculated and displayed. This column must be the next column to the right of the NumBikes column, the column heading should be in cell G1.
5. Add a formula to calculate the AvgRev by dividing the contents of the Revenue cell by the contents of the NumBikes cell for each data row in the table

The AvgRev values format is currency (a $ and two decimal points) with no space between the $ and the first number following it. When you are clicking on cells to construct a formula in cell G2, you may expect to see =E2/F2 and what may appear in the cell editor is =[@Revenue]/[@NumBikes] or =[Revenue]/[NumBikes]. These last two formulas are the result of using an Excel table.

Column adjustment may be needed to ensure both the title and the data are visible.