BIS 155 Week 3 iLab You are an intern with the Regional Realty Association and are analyzing the slogan made by Alice Barr Realty that "We Get Your Price."

BIS 155

Week 3

iLab

Scenario Summary:

You are an intern with the Regional Realty Association and are analyzing the slogan 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. Download the chap4_cap_barr.xlsx file in DocSharing under Week 3 and save it as Lab3_Yourlastname.xlsx.

Your first sheet should be the documentation sheet for your workbook. Use the same layout for the documentation sheet as you did for the other iLabs. Be sure you name the Documentation.

Step 2,

a.    Click in F4

b.    Calculate:  E4 (Selling price) divided by D4 (asking price)

c.    Copy the formula to each cell below.

d.    Format the cells in Column F to show percentage.

a.    Highlight Column F and select the % sign in the Number group. See below in Figure 1.

                                    Figure 1

b.    Use the Decrease Decimal to remove the decimal places.

e.    Format Columns D and E as currency with no decimal

a.    Highlight Columns D and E and use the drop down arrow in the Number group on the Ribbon and look for Currency.  See Figure 1 above.

f.     Format Columns G and H as dates

a.    Highlight Columns G and H.

b.    Right Click and select Format Cells

c.    Select Date.

d.    Click on the Day and Month format.

e.    Click OK.

g.    Widen all columns to make all data and headings visible.

h.   
Format titles as appropriate and bold and center the column headings

i.      Your worksheet should look like this.

B. Convert the data list range in to a table. Select the range of cells that contains the Sales Data. Click the Insert Tab and select Tables from the Tables Group:

Click OIK

E. Now that the data has been sorted, you can convert it back into a Range.

You MUST convert to range before you can provide a Summary Report.

Click OK. Rename the new sheet Filtered Data.

Click OK. Rename the new sheet Conditional Formatting.

Click OK. The data will be converted into a Table and the Table Tools tab will open.

Click OK.