Question

You own a small real estate company in Indianapolis. You track the real estate properties you list for clients. You want to analyze sales for selected properties. Yesterday, you prepared a workbook with a worksheet for recent sales data and another worksheet listing several properties you listed. You want to calculate the number of days that the houses were on the market and their sales percentage of the list price. In one situation, the house was involved in a bidding war between two families that really wanted the house. Therefore, the sale price exceeded the list price.

For the purpose of grading the project you are required to perform the following tasks:
Step
Instructions
Points Possible
1. Start Excel. Download and open the file named e01_grader_h3.xlsx.
0.000
2. Delete row 8, which has incomplete sales data. The owners took their house off the market.
3.000
3. Type 2018-001 in cell A5 and use Auto Fill to complete the series to assign a property ID to each property in the range A6:A12.
5.000
4. Enter a formula in cell C5 that calculates the number of days the first house was on the market. Copy the formula to the range C6:C12. Format the range C5:C12 with Number format with zero decimal places.
10.000
5. Format the list prices and sold prices in the range F5:G12 with Accounting Number Format with zero decimal places.
5.000
6. Enter a formula in cell H5 that calculates the sales price percentage of the list price for the first house. The second house was listed for $500,250, but it sold for only $400,125. Therefore, the sale percentage of the list price is .7999. Copy the formula to the range H6:H12.
10.000
7. Format the values in the range H5:H12 with Percent Style with two decimal places.
5.000
8. Wrap the headings in the range A4:H4.
2.000
9. Insert a new column between the Date Sold and List Price columns. Move the Days to Market data to the new blank column F by moving the range C4:C12 to F4:F12. Delete column C.
5.000
10. Edit the list date of the 41 Chestnut Circle house to be 4/22/2018. Edit the list price of the house on Amsterdam Drive to be $355,000.
5.000
11. Select the property rows (rows 5:12) and set a 25 row height and apply Middle Align.
5.000
12. Apply the All Borders border style to the range A4:H12.
3.000
13. Apply Align Right and increase the indent two times on the days on market formula results in the range E5:E12.
5.000
14. Set the Days on Market column width to 9. Set the List Price and Sold Price column widths to 11.86.
5.000
15. Apply 120% scaling.
2.000
16. Delete the Properties sheet and insert a new sheet named Formulas.
5.000
17. Use the Select All feature to select all data on the Houses Sold worksheet and copy it to the Formulas worksheet.
5.000
18. Hide the Date Listed and Date Sold columns in the Formulas worksheet.
5.000
19. Display cell formulas. Set options to print gridlines and row and column headings. Set 6.86 column width for column E and 6 for column H.
3.000
20. Group the two worksheets. Select Landscape orientation. Center the page horizontally and vertically between the margins.
7.000
21. With both worksheets still grouped, insert a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side.
5.000
22. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
0.000
Total Points
100.000
J. Heaton Realty
Sales Report
Prop ID Property Days on Market Date Listed Date Sold List Price Sold Price % of List Price
125 Amsterdam Drive 1/12/18 4/12/18 350000 335000
341 North Oak Drive 11/15/17 3/1/18 500250 400125
100 South 325 West 12/3/17 4/30/18 650725 500750
386 East Elm Street 3/1/18
2304 West 39th Street 3/14/18 3/30/18 315000 325000
41 Chestnut Circle 2/22/18 5/18/18 423500 420750
973 East Mulberry Lane 11/30/17 3/1/18 375000 375000
538 North Oak Drive 10/4/17 1/5/18 475000 400000
64 Chancellor Estates 4/1/18 4/15/18 500999 495000

Answer 1

To perform these tasks in Excel, you will need to delete incomplete sales data, assign property IDs, calculate the number of days a house was on the market, format prices, create formulas, format cells, wrap headings, move columns, edit specific data, set row heights and column widths, apply scaling, insert and hide sheets, display cell formulas, group worksheets, set page orientation and margins, insert a footer, save and exit Excel.

Step-by-step explanation:

To perform these tasks in Excel:

1. Delete row 8, which has incomplete sales data.
2. Type '2018-001' in cell A5 and use Auto Fill to complete the series to assign a property ID to each property in the range A6:A12.
3. Enter a formula in cell C5 that calculates the number of days the first house was on the market. Copy the formula to the range C6:C12. Format the range C5:C12 with Number format with zero decimal places.
4. Format the list prices and sold prices in the range F5:G12 with Accounting Number Format with zero decimal places.
5. Enter a formula in cell H5 that calculates the sales price percentage of the list price for the first house. Copy the formula to the range H6:H12.
6. Format the values in the range H5:H12 with Percent Style with two decimal places.
7. Wrap the headings in the range A4:H4.
8. Insert a new column between the Date Sold and List Price columns. Move the Days to Market data to the new blank column F by moving the range C4:C12 to F4:F12. Delete column C.
9. Edit the list date of the 41 Chestnut Circle house to be 4/22/2018. Edit the list price of the house on Amsterdam Drive to be $355,000.
10. Select the property rows (rows 5:12) and set a 25 row height and apply Middle Align.
11. Apply the All Borders border style to the range A4:H12.
12. Apply Align Right and increase the indent two times on the days on market formula results in the range E5:E12.
13. Set the Days on Market column width to 9. Set the List Price and Sold Price column widths to 11.86.
14. Apply 120% scaling.
15. Delete the Properties sheet and insert a new sheet named Formulas.
16. Use the Select All feature to select all data on the Houses Sold worksheet and copy it to the Formulas worksheet.
17. Hide the Date Listed and Date Sold columns in the Formulas worksheet.
18. Display cell formulas. Set options to print gridlines and row and column headings. Set 6.86 column width for column E and 6 for column H.
19. Group the two worksheets. Select Landscape orientation. Center the page horizontally and vertically between the margins.
20. With both worksheets still grouped, insert a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side.
21. Save the workbook. Close the workbook and then exit Excel.

Answer 2

Open excel program and then select a worksheet. Double click on the worksheet tab and rename it according to the file name. Click import data and the excel will start downloading the data to the worksheet. When the data is download save as the worksheet so that any editing does not harms the original file.

Step-by-step explanation:

Excel is a spreadsheet program which is developed by the Microsoft. This enables the users to handle complex numerical calculations with ease. It provides a wide range of complex formula in which multiple worksheets can be linked together for various tasks.