Question

Excel Project #2 Regional Realty Association You have obtained a position with the Regional Realty Association and are analyzing the claim made by Alice Barr Realty that “we get your price”. You have prepared a spreadsheet that shows data relating to three months sales by Alice Barr Realty. You are going to determine the percent of asking price for each home sold that month. Determine which sales people have the most total sales and determine how many sales are made within the city of Miami. You will prepare an attractively formatted summary report for your boss and a chart showing the total sales by sales person. Instructions: Import and open the file Barr-sales file provided by your wonderful professor. Use the computer and Excel to do all look ups and calculations. Calculate percent of asking price and format the data A formula is used to calculate the percent of asking price, and this formula is applied to all listed sales. You will format the list in an attractive and useful manner. 1. The percentage of asking price is calculated by dividing the selling price by asking price. Enter the formula in column F 2. Format columns D and E as currency, no decimals. Format columns G and H as dates so just the day and month are visible. (example 5-May) Change columns F to a percentage with one decimal. 3. Widen columns to make all data and headings visible. Format titles as appropriate for the Regional Realty Association. Bold and center the column headings. 4. In Cell A44 insert the comment “CS131” using the rights click mouse option. Sort the Data and Prepare the Summary Report and Chart In order to sort the data by asking price and sales person, you must first covert the list to a filtered area range. Once the data is sorted, in order to prepare the summary report, you must convert the item into a range if you made the table for sort purposes. • Convert the range to a filtered area. • Sort the data by asking price and then by selling agent. • Format the data attractively. • Convert the data back to a range if you made a table. • Prepare a summary report showing total asking price and selling price by agent. • Use a fill color to highlight each sales person’s total asking price and selling price. • Prepare a chart on a separate chart sheet that shows each sales person’s total asking price and selling price. Include a title and legend, and format the chart to compliment the worksheet. Rename the chart sheet tab Sales Analysis. • Store and Place the Chart on its own worksheet Filter the data and print the report Your report should list just those properties sold by agent Carey in cities other than Miami, and you will use a filter to extract this data. Further, you must format before printing to make sure the report is documented and fits on one page. • Convert the range and filter the data to show only those properties sold by agent Carey in cities other than Miami. Delete the Summary Total and Grand total from the bottom on the filtered list. • Create a custom header with your name on the left and your instructor’s name on the right. Change the orientation to landscape, center horizontally and vertically, and print gridlines and row and columns headings. Preview your work sheet and make any necessary adjustment to print on one page. Save your changes, print the worksheet, and print the Sales Analysis Chart. • Save your changes, you should have three work sheets present: A) the general worksheet, and B) Chart of sales agent asking and selling price, Sales Analysis Chart, and C) non Miami property by Agent Carey.

Alice Barr Realty
We get your price and we sell it quickly!
Address	City	Selling Agent	Asking Price	Selling Price	% of Asking	Listing Date	Sale Date
8687 Kenwood Road	Miami	Hernandez	725000	645250		39173	39187
7 Kingston Court	Sunrise	Carey	500000	465000		39175	39182
1370 Pinellas Road	Sunrise	Lugo	219000	215000		39176	39204
1971 Glenview Road	Coral Gables	Minkus	165000	156750		39184	39214
10995 SW 88 Court	Coral Gables	Goodrich	110000	106000		39184	39266
8030 Steeplechase Drive	Sunrise	Merkin	450000	382500		39187	39202
2006 Cutwater Court	Sunrise	Minkus	345000	339999		39190	39225
4081 Lybyer Avenue	Sunrise	Minkus	325000	308750		39191	39233
758 University Drive	Coral Gables	Hernandez	325000	302250		39200	39203
224 Rockaway Street	Sunrise	Pijuan	98000	93100		39202	39252
8307 S Indian River Drive	Coral Gables	Hernandez	250000	255000		39203	39220
79 Island Drive South	Sunrise	Carey	158000	155000		39224	39248
9408 Forest Hills Circle	Miami	Reuter	185500	179000		39203	39206
11971 SW 269 Terrace	Miami	Carey	410000	397700		39203	39258
16235 Orange Boulevard	Miami	Lugo	77500	75900		39207	39277
2448 Woodacres Road	Coral Gables	Hernandez	450000	382500		39207	39217
1414 N Sheridan Road	Sunrise	Pijuan	1250000	1225000		39214	39239
10700 Lake Shore Lane	Miami	Carey	650000	598000		39217	39242
81 Island Drive South	Sunrise	Bethune	147800	150000		39224	39248
28 Kent Road	Coral Gables	Lugo	261900	256906		39235	39327
605 Reservoir Drive	Sunrise	Reuter	310000	291400		39224	39231
1 Southampton Place	Coral Gables	Goodrich	285000	276450		39230	39251
1629 NW 43rd Street	Miami	Carey	179900	163709		39233	39313
21 Compass Road	Miami	Merkin	189900	182304		39233	39234
30 Kent Road	Coral Gables	Bethune	259900	246905		39235	39327
520 E Spring Street	Sunrise	Minkus	189900	186102		39239	39288
4916 Rock Spring Road	Miami	Lugo	275000	264000		39246	39284
8923 Harris Drive	Sunrise	Minkus	89000	86330		39251	39271
1132 SW 52nd Street	Miami	Hernandez	254500	236685		39256	39346
10 Vestal Drive	Miami	Carey	555000	565000		39257	39263
103 Jasper Drive	Coral Gables	Bethune	165900	159264		39262	39329
1600 Reeves Street	Miami	Reuter	245900	233605		39263	39308

Answer 1