Illustrated Excel 365 | Module 1: SAM Project B Navigate Green

GETTING STARTED

1. Save the file IL_EX365_1B_FirstLastName_1.xlsx as IL_EX365_1B_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file IL_EX365_1B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Christopher Starks is the sales manager of Navigate Green, a car rental company in Ft. Lauderdale, Florida. He has created a worksheet comparing car rentals in two sales regions for the second quarter of the year, and has asked you to help complete the quarterly analysis. Go to the Quarter 2 worksheet. Enter the text Quarter 2 Listings in cell A2 to provide a subtitle for the worksheet.

2. In cell A4, enter the text Rentals by Region to provide a section title.

3. In cell D10, enter the value 78 to provide the number of luxury car rentals in June.

4. In cell E9, enter a formula that uses the SUM function to calculate the total standard car rentals in the Midwest region for Quarter 2 (the range B9:D9).

5. In cell B15, enter a formula that uses the SUM function to calculate the total number of rentals in the Midwest region for April (the range B7:B14).

6. Use the Fill Handle to autofill the range C18:D18 based on the text in cell B18 to enter the remaining months in Quarter 2.

7. Enter the information shown in Table 1 into the range B25:D25 to provide the number of standard SUV rentals in the Northeast region.

Table 1: Values for the Range B25:D25

Cell | B25 | C25 | D25 Value | 188 | 208 | 233

8. Move the text from cell G12 ("Statistics by Region") to cell G17 to provide a heading for the data in the range G18:I24.

9. Christopher started calculating statistics on the real estate listings in the Northeast region. He wants you to complete the statistics for the Northeast region as follows:

a. In cell H20, enter a formula that uses the AVERAGE function to find the average number of listings for each vehicle type in Quarter 2 (the range B19:D26).

b. In cell H22, enter a formula that uses the MAX function to find the most listings in Quarter 2 (the range B19:D26).

c. In cell H24, enter a formula that uses the MIN function to find the fewest listings in Quarter 2 (the range B19:D26).

d. In cell I22, enter Standard SUV as the vehicle type with the most rentals in the Northeast region.

e. In cell I24, enter Luxury SUV as the vehicle type with the fewest rentals in the Northeast region.

f. Clear everything from the range I6:J11 to remove the data and formatting for quarters other than Quarter 2.

10. In the range G6:H11, Christopher wants you to analyze Quarter 2 sales by comparing them to rentals and upgrades from the previous quarter. Enter the calculations as follows:

a. In cell H7, enter a formula that adds the total rentals for the Midwest region (cell E15) to the total rentals for the Northeast region (cell E27).

b. In cell H9, enter a formula that divides the total rentals for Quarter 2 (cell H7) by the total upgrades for Quarter 2 (cell H8).

c. In cell H11, enter a formula that first subtracts the previous quarter rentals (cell H10) from the total rentals for Quarter 2 (cell H7), and then divides the result by the previous quarter rentals (cell H10).

11. Hide the gridlines for the Quarter 2 worksheet to make it easier to read.

12. Change the orientation of the worksheet to Landscape to prepare for printing the worksheet on one page.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.

Final Figure 1: Quarter 2 Listings