c.   Given that the 7 reports listed as Canes Realty Reports are reports that are printed each week, create the criteria ranges needed for those reports. Build the criteria ranges on a separate sheet and enter text above or next to them to label them clearly so that it is evident which report each is being used for. Please note that you do not need to create the reports – only the criteria ranges.

d.   Build criteria ranges and Copy to (Extract) ranges for 2 additional reports. The criteria ranges should be built on the same sheet as the criteria ranges you built for item c above and again should be labeled clearly so that it is evident which report each is being used for. The Copy to ranges must be built on the original worksheet and should be built to the right of the original list.

Report 1 should list only the homes that sold in the $200,000 - $299,999 price range. The report should only include the Listing ID, City, Square Footage, Sales Price, Percent Below Asking, and Agent.

Report 2 should list only the homes where the Type was B (the agent was both the listing and selling agent) or the Percent below Asking was above 10%. The report should only include the Listing ID, Address, Asking Price, Sales Price, Percent below Asking, Agent, and Type.

      Once the Criteria ranges and Copy to ranges are built, use the Advanced Filter command to create the two reports. Create a title for each report and then format each so that the columns are wide enough to display all of the data and the reports are attractively laid out.

 

Canes RealtyListing IDAddressCityBedroomsDate ListedAsking PriceDate SoldSales PriceDays on MarketAgentTypeCommissionL101335 Trenton RoadSweetwater1,23632/19/2014$287,4005/3/2014$250,0007313.01% FletcherL$5,000L1028550 SW 140 TerraceNorth Bay Village1,740211/20/2013$310,9004/8/2014$258,00013917.02% TobinS$10,320L1031504 Michigan AveSweetwater1,715412/13/2013$295,000Not sold yetNot sold yet EvansLPendingL1046841 SW 44 StreetBal Harbour1,273412/28/2013$287,0005/31/2014$245,00015414.63% FletcherL$4,900L105240 Shore Drive EastPinecrest97035/7/2014$273,9005/13/2014$263,50063.80% FletcherS$10,540L1065 Speridakis TerraceNorth Bay Village90039/18/2013$277,0006/2/2014$267,7502573.34% TobinL$5,355L1077925 SW 124 StreetNorth Bay Village1,8505/30/2014$333,0006/4/2014$315,50055.26% LazarusB$18,930L108943 Oak Harbor DrivePinecrest1,72054/14/2014$316,000Not sold yetNot sold yet TobinLPendingL1092303 Pin Oak DriveSweetwater1,606411/2/2013$302,0006/17/2014$300,0002270.66% EvansS$12,000L11071 Wckoff StreetBal Harbour1,30529/17/2013$294,0006/13/2014$288,2502691.96% EvansS$11,530L1112824 NE 29th StreetBal Harbour1,7002/1/2014$312,9004/27/2014$296,000855.40% LazarusS$11,840L1121719 SandmanNorth Bay Village1,62039/24/2013$305,000Not sold yetNot sold yet LazarusLPendingL1131630 Sheridan RoadPinecrest1,630410/19/2013$304,5004/21/2014$298,0001842.13% EvansL$5,960L114251 Crandon BoulevardPinecrest1,54046/21/2014$303,0007/1/2014$290,500104.13% FletcherB$17,430L11522 Silver Oak DriveSweetwater1,22944/22/2014$287,2007/15/2014$277,000843.55% LazarusB$16,620L11610 S.W. South River DriveSweetwater1,50059/25/2013$750,0005/5/2014$682,7502228.97% LazarusL$13,655L117825 Bay StreetBal Harbour1,53568/6/2013$650,000Not sold yetNot sold yet TobinLPendingL1181920 Meridian StreetNorth Bay Village1,415410/3/2013$292,0005/13/2014$291,0002220.34% TobinB$17,460L1191221 Stafford RoadNorth Bay Village1,165211/7/2013$286,9006/3/2014$248,50020813.38% TobinL$4,970L1205175 NE 3rd CourtBal Harbour1,20028/4/2013$276,6005/20/2014$268,0002893.11% EvansL$5,360L121520 Valencia RoadBal Harbour1,92033/24/2013$305,0006/2/2014$295,5004353.11% LazarusS$11,820L1225901 Laurel AvenueBal Harbour1,58035/3/2014$294,5007/4/2014$265,250629.93% LazarusS$10,610L123232 Peninsula RoadPinecrest1,739410/6/2013$297,5006/10/2014$284,7502474.29% FletcherS$11,390L1247708 Landmark Ridge StreetPinecrest1,50546/12/2014$270,0006/18/2014$240,500610.93% EvansS$9,620L1258608 Westmoreland LaneSweetwater1,19849/3/2013$260,0005/28/2014$251,0002673.46% EvansB$15,060L1265606 Merrick DriveNorth Bay Village2,56332/16/2014$358,000Not sold yetNot sold yet LazarusLPendingL1273939 Leafy WayBal Harbour1,05133/4/2014$258,0006/5/2014$258,000930.00% FletcherB$15,480L1281718 Vestal DriveSweetwater3,75046/11/2014$369,5006/18/2014$365,00071.22% TobinL$7,300L1291970 NW 51 TerracePinecrest1,08034/8/2014$278,0004/21/2014$268,500133.42% EvansS$10,740L1307 Madison StreetPinecrest1,14231/8/2014$254,0005/24/2014$241,7501364.82% FletcherB$14,505L1313 Grove Isle Dr.Sweetwater1,46443/21/2014$307,0006/13/2014$307,000840.00% LazarusB$18,420AssumptionsStatistics - use COUNT, SUM, AVERAGE, COUNTIFS, SUMIFS, or AVERAGEIFSTypeDescriptionCommission %1. Average Commission AmountAvereage ifBListing & Selling Agent6%2. Number of homes where our agent was both Listing & Selling agent8LListing Agent Only2%3. Number of homes in Sweetwater whose asking price was $300,000 or more4SSelling Agent Only4%4. Total Commissions paid to Lazarussumif5 Average Asking Price for Homes less than 1500 square feet in North Bay VillageCanes Realty Reports - Criteria Ranges Needed1. Homes not yet sold2. Homes in Pinecrest that sold for $250,000 or more3. Homes sold in North Bay Village4. Homes in Bal Harbour or Sweetwater where Tobin was the realto5. Pinecrest Homes sold in May 20146. Home with sq ft over 2,000 or more than 3 bedrooms7. Homes that have been soldSquareFootagePercent belowAsking

Attachments:

• 1495784442-Exercise 4.xlsx