Illustrated Excel 365 | Module 1: SAM Project A Rezio Real Estate

GETTING STARTED

1. Save the file IL_EX365_1A_FirstLastName_1.xlsx as IL_EX365_1A_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_1A_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. Mariam Fadel is the sales manager of Rezio Real Estate, an online real estate agency headquartered in Weston, Massachusetts. She has created a worksheet comparing the real estate listings in two sales regions for the first three months of the year, and has asked you to help complete the quarterly analysis. Go to the Quarter 1 worksheet. Enter the text Quarter 1 Listings in cell A2 to provide a subtitle for the worksheet.

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

3. In cell A5, change the text "Area" to Region so that the cell contains the correct text, "North Region".

4. In cell D7, enter the value 312 to provide the number of condo listings in March.

5. In cell E7, enter a formula that uses the SUM function to calculate the total condo listings in the North region for Quarter 1 (the range B7:D7).

6. In cell B14, enter a formula that uses the SUM function to calculate the total listings of properties in the North region for January (the range B7:B13).

7. Use the Fill Handle to autofill the range C17:D17 based on the text in cell B17 to enter the remaining months in Quarter 1.

8. Enter the information shown in Table 1 into the range B22:D22 to provide the number of single family home listings in the South region.

Table 1: Values for the Range B22:D22

Cell | B22 | C22 | D22 Value | 324 | 368 | 382

9. Move the text from cell G14 ("Statistics by Region") to cell G5 to provide a heading for the data in the range G6:I12.

10. Mariam started calculating statistics on the real estate listings in the North region. She wants you to complete the statistics for the South region as follows:

a. In cell H8, enter a formula that uses the AVERAGE function to find the average number of listings for each property type in Quarter 1 (the range B18:D23).

b. In cell H10, enter a formula that uses the MAX function to find the most listings in Quarter 1 (the range B18:D23).

c. In cell H12, enter a formula that uses the MIN function to find the fewest listings in Quarter 1 (the range B18:D23).

d. In cell I10, enter Single Family as the property type with the most listings in the South region.

e. In cell I12, enter Mobile as the property type with the fewest listings in the South region.

11. Clear everything from the range I17:K22 to remove the data and formatting for quarters other than Quarter 1.

12. In the range H18:H22, Mariam wants to analyze Quarter 1 listings by comparing them to sales and listings from the previous quarter. Enter the calculations as follows:

a. In cell H18, enter a formula that adds the total listings for the North region (cell E14) to the total listings for the South region (cell E24).

b. In cell H20, enter a formula that divides the total sales for Quarter 1 (cell H19) by the total listings for Quarter 1 (cell H18).

c. In cell H22, enter a formula that first subtracts the previous quarter listings (cell H21) from the total listings for Quarter 1 (cell H18), and then divides the result by the previous quarter listings (cell H21).

13. Hide the gridlines for the Quarter 1 worksheet to make it easier to read.

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