BIS 155 Lab 3 of 7: Alice Barr Realty Analysis

BIS 155 Lab 3 of 7: Alice Barr Realty Analysis

Lab 3 of 7: Alice Barr Realty Analysis

You are an intern 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 3 months’ sales by Alice Barr
Realty. You are going to determine the percent of asking price for each home
sold and analyze the sales data to determine the true track record of the
company in selling homes for the asking price.

STEP 1: Open, Save, and Document the
Workbook (5 points)

You must open a workbook that lists home sales for 3 months.

  1. Download the file chap4_cap_barr from DocSharing, Lab
  2. Open the workbook in Excel, and save it as
  3. Insert a documentation sheet that includes title, author,
    date, and purpose. Format it appropriately, including changing the Sheet Name to
    Documentation. At the end of this activity, you will be asked to
    provide a conclusion as to whether this realty company “really gets your

STEP 2: Add Calculations and Format
Sales Data Worksheet (10 points)

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. Calculate the percent of asking price for each sale (selling
    price divided by asking price). Enter the formula in Column F. Format Column F
    as a percentage with one decimal.
  2. Format Columns D and E as currency, with no decimals. Format
    Columns G and H as dates so just the day and month (for example, 5-May) are
    visible. You will need to use the Number Format dropdown list, select More
    Number Formats, and select the Date category.
  3. Widen columns to make all data and headings visible. Format
    titles as appropriate. Bold and center the column headings.

STEP 3: Sort Data and Create Summary Reportand Chart (10

  1. Before analyzing data, it is a good idea to copy the
    data 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 copy
    of the current spreadsheet and move it to the end. Name the sheet Sorted
  2. In order to sort the data, you must convert them to a data
    table. Format the data table to look attractive.
  3. Sort the data by selling agent (A to Z) and then by asking
    price (largest to smallest).
  4. Convert the sorted data back into a range, and create a
    summary report showing the total asking price and total selling price by selling
  5. Create a column chart that shows the difference by agent between
    asking price and selling price

Step 4. Filter the data

  1. Create another copy of
    the original sales data. Rename the sheet Filtered Data.
  2. Convert the range to a table, and then filter to show only
    those properties sold in Miami by agent Carey.

Step 5. Apply Conditional formatting

You determine that one measure of how well the company “gets
your price” is to set a scale. Excellent performance is at or above the asking
price. Good performance is between 95% and 100%. Below 95% is average
performance. You will create conditional formatting rules to display graphically
the performance level for each sale. Make a copy of the
original sales data chart. Name the new sheet Conditional Format.

  1. Select the data range and
    insert a table.
  2. Use Advanced Formatting to set up the following conditional
    formatting rules for the field Percent of Asking Price.

Greater than or equal to 100% = GREEN 95% to 99.9% = YELLOW Less than 95% = RED

  1. Use Percent of Asking Price as the Sort Key, and sort by
    color with green first, then yellow, and then red

Step 6. Create a Pivot Table

Now that you have sorted, subtotaled, filtered, and conditionally
formatted the data, you have a few more questions to answer. You want to see
average percentage of selling price by agent and by city. This will allow you to
see whether there are any patterns based on the city or based on the
salesperson. To do this kind of comparison, you decide to create a pivot table.

  1. Open the Sales Data worksheet. Select the Sales Data range
    (A3:H33). From the Formulas tab, select Define Name from the Defined Names
    group. Name the range of cells SalesData.
  2. Create a pivot table based on the named range, SalesData.
    Create the pivot table in a new worksheet.
  3. Select Selling Agent as your Row Labels and City as your
    Column Labels. Select Percent of Sales as your Values. Change the Summary type
    for Percent of Sales to average, the Number Format to percent, and the Name to
    Average Percent of Asking Price. Apply Data Bars Conditional Formatting to the
    percentages. Hide the Field Headers.
  4. Rename the sheet PivotTable, and move it so that it is the
    last tab in the worksheet.

Step 7. Draw a conclusion

On the Documentation Sheet, add a section called Analysis. Based on
your data analysis, do you believe that Barr Realty should be allowed to
advertise that they “Get your price?” Justify your answer by referring to the
data analysis you have just completed.


BIS 155 Course Project: Excel Project

BIS 155 Lab 1 of 7: Saddle River Marching Band

BIS 155 Lab 2 of 7: Excel’s Advanced Formulas & Functions

BIS 155 Lab 3 of 7: Alice Barr Realty Analysis

BIS 155 Lab 4 of 7: Create Address Labels

BIS 155 Lab 5 of 7: Bruno’s Pizza Analysis

BIS 155 Lab 6 of 7: Day Care Center

BIS 155 Lab 7 of 7: Access Database


Follow this link to get a similar paper written from scratch
Place an order for this assignment or any other paper with us. We guarantee that the paper will be delivered within the specified deadline. We also have a personalized customer support and you will be able to communicate with your writer throughout the order preparation process. Thank you for choosing us.


Have troubles with paper writing? You’ve found the right paper writing company! We are the leading essay writing services that provides quality papers for a reasonable price. On our website, you can order various assignments. Our work is 100% original. We write your essay from scratch, according to the exact specifications of your assignment. We guarantee it will pass any plagiarism check

Get Your Papers Completed by Expert Writers

Order essays, term papers, research papers, reaction paper, research proposal, capstone project, discussion, projects, case study, speech/presentation, article, article critique, coursework, book report/review, movie review, annotated bibliography, or another assignment without having to worry about its originality – we offer 100% original content written completely from scratch


Related Posts

© All Right Reserved