Want answers to the assignment Below?

Text or Whatsapp Olivia at +1 (307) 209-4351”>iLab> STEP 6: Create a
Pivot Table


·” title=”Listen to this page using ReadSpeaker”>Listen”>What’s this?

iLab Overview


You are an intern with the Regional Realty
Association and are analyzing the claim made by Luke Liberty Realty that
“we get your price.” You have prepared a spreadsheet that shows data
relating to 3 months’ sales by Luke Liberty 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



Submit your assignment to the Dropbox, located
at the top of this page. For instructions on how to use the Dropbox, read these”>step-by-step instructions.

(See the Syllabus
section “Due Dates for Assignments & Exams” for due dates.)

Turn in one workbook to the Dropbox. When
submitting the workbook, provide a comment in the Dropbox comments area
explaining what you learned from completing this lab activity.

File naming convention: If your name is
Jane Doe, then your file should be named very similar to: Doe_J_Week3_iLab.xls

Click to download the”>Grading Rubricfor
Lab 3

Required Software

Microsoft Office: Excel

Options for accessing Microsoft Excel 2013:

Use a personal copy on
your PC. You can request a copy of Microsoft Office 2013 via the Student
Software Store icon on the Course Resources Page

If you are a MAC user,
click to read the”>MAC User Information.

virtual lab to access Microsoft Excel in the virtual lab.
For additional virtual lab information and tutorials on saving files, click the
iLab icon on the Course Resources page.

Lab Steps


Be sure you have reviewed the Tutorials located
on the Lesson page before you begin the iLab.

Please do not rely solely on
the videos to complete this week’s lab.
The videos provide detailed examples using the supporting
documents. Applying the examples to the loan assignment will give you both
practice and instruction on what to complete.

For example, your final assignment file should
include seven worksheets (Documentation, Sales Data, Sort Data,
AskvsSellbyAgent, Filtered Data, Conditional Format, Pivot Table), each with a
corresponding example, as shown in the video tutorials.

STEP 1: Open, Save,
and Document the Workbook

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

Download the workbook”>Week3_iLab_LibertyRealty_Student.xlsx

Open the workbook in
Excel, and save it according to the file naming convention above.

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 price.”

STEP 2: Add
Calculations and Format Sales Data Worksheet

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.

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.

Format Columns D and E
as currency, with no decimals. Format Columns G and H as dates so just the day
and month (e.g., 5-May) are visible. You will need to use the Number Format
dropdown list, select More Number Formats, and select the Date category.

Widen columns to make
all data and headings visible. Format titles as appropriate. Bold and center
the column headings.

Your Sales Data worksheet will look something
like this.

devry bis155 week 3 ilab Latest 2016 March.gif” alt=”image shows prior instructions”>

STEP 3: Sort Data and
Create Summary Report and Chart

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 Data.

In order to sort the
data, you may want to convert them to a data table. Format the data table to
look attractive.

Sort the data by selling
agent (A to Z) and then by asking price (largest to smallest).

Convert the sorted data
back into a range, and create a summary report showing the total asking price
and total selling price by selling agent.

Create a column chart
that shows the difference by agent between asking price and selling price.

STEP 4: Filter the

Create another copy of
the original sales data. Rename the sheet Filtered Data.

Convert the range to a
table, and then filter to show only those properties sold in Miami by agent

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.

Select the data range
and insert a table.

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

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

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

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.

Create a pivot table
based on the named range, SalesData. Create the pivot table in a new worksheet.

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.

Rename the sheet
PivotTable, and move it so that it is the last tab in the worksheet.

STEP 7: Draw a

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

Submit your completed workbook to the Dropbox. Make
sure you post a comment about what you learned
when submitting the


Other Questions.

We Accept

Order your Assignment today and save 15% with the discount code ESSAYHELP