Devry BIS155 Lab 3 of 7: Alice Barr Realty Analysis

BIS 155 Lab 3 of 7: Alice Barr Realty AnalysisLab 3 of 7: Alice Barr Realty AnalysisYou are an intern with the Regional Realty Association and areanalyzing the claim made by Alice Barr Realty that “we get your price.” You haveprepared a spreadsheet that shows data relating to 3 months’ sales by Alice BarrRealty. You are going to determine the percent of asking price for each homesold and analyze the sales data to determine the true track record of thecompany in selling homes for the asking price.STEP 1: Open, Save, and Document theWorkbook (5 points)You must open a workbook that lists home sales for 3 months.A. Download the file chap4_cap_barr from DocSharing, LabMaterials.B. Open the workbook in Excel, and save it asLab3_Yourlastname.xlsx.C. Insert a documentation sheet that includes title, author,date, and purpose. Format it appropriately, including changing the Sheet Name toDocumentation. At the end of this activity, you will be asked toprovide a conclusion as to whether this realty company “really gets yourprice.”STEP 2: Add Calculations and FormatSales Data Worksheet (10 points)A formula is used to calculate the percent of asking price, andthis formula is applied to all listed sales. You will format the list in anattractive and useful manner.A. Calculate the percent of asking price for each sale (sellingprice divided by asking price). Enter the formula in Column F. Format Column Fas a percentage with one decimal.B. Format Columns D and E as currency, with no decimals. FormatColumns G and H as dates so just the day and month (for example, 5-May) arevisible. You will need to use the Number Format dropdown list, select MoreNumber Formats, and select the Date category.C. Widen columns to make all data and headings visible. Formattitles as appropriate. Bold and center the column headings.STEP 3: Sort Data and Create Summary Reportand Chart (10points)A. Before analyzing data, it is a good idea to copy thedata to a new worksheet so that you have a back-up copy of the original data.Right click on the Sales Data sheet tab and select Move or Copy. Create a copyof the current spreadsheet and move it to the end. Name the sheet SortedData.B. In order to sort the data, you must convert them to a datatable. Format the data table to look attractive.C. Sort the data by selling agent (A to Z) and then by askingprice (largest to smallest).D. Convert the sorted data back into a range, and create asummary report showing the total asking price and total selling price by sellingagentE. Create a column chart that shows the difference by agent betweenasking price and selling priceStep 4. Filter the dataA. Create another copy ofthe original sales data. Rename the sheet Filtered Data.B. Convert the range to a table, and then filter to show onlythose properties sold in Miami by agent Carey.Step 5. Apply Conditional formattingYou determine that one measure of how well the company “getsyour price” is to set a scale. Excellent performance is at or above the askingprice. Good performance is between 95% and 100%. Below 95% is averageperformance. You will create conditional formatting rules to display graphicallythe performance level for each sale. Make a copy of theoriginal sales data chart. Name the new sheet Conditional Format.A. Select the data range andinsert a table.B. Use Advanced Formatting to set up the following conditionalformatting rules for the field Percent of Asking Price.Greater than or equal to 100% = GREEN 95% to 99.9% = YELLOW Less than 95% = REDC. Use Percent of Asking Price as the Sort Key, and sort bycolor with green first, then yellow, and then redStep 6. Create a Pivot TableNow that you have sorted, subtotaled, filtered, and conditionallyformatted the data, you have a few more questions to answer. You want to seeaverage percentage of selling price by agent and by city. This will allow you tosee whether there are any patterns based on the city or based on thesalesperson. To do this kind of comparison, you decide to create a pivot table.A. Open the Sales Data worksheet. Select the Sales Data range(A3:H33). From the Formulas tab, select Define Name from the Defined Namesgroup. Name the range of cells SalesData.B. Create a pivot table based on the named range, SalesData.Create the pivot table in a new worksheet.C. Select Selling Agent as your Row Labels and City as yourColumn Labels. Select Percent of Sales as your Values. Change the Summary typefor Percent of Sales to average, the Number Format to percent, and the Name toAverage Percent of Asking Price. Apply Data Bars Conditional Formatting to thepercentages. Hide the Field Headers.D. Rename the sheet PivotTable, and move it so that it is thelast tab in the worksheet.Step 7. Draw a conclusionOn the Documentation Sheet, add a section called Analysis. Based onyour data analysis, do you believe that Barr Realty should be allowed toadvertise that they “Get your price?” Justify your answer by referring to thedata analysis you have just completed.