COSC 1301: Excel 4 – Barr Realty



COSC 1301: Lab 9 (Excel Chapter 4) – Barr Realty

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 three months' sales by Alice Barr Realty. You are going to determine the percent of asking price for each home sold that month. Determine which sales people have the most total sales and determine how many sales are made within the city of Miami. You will prepare an attractively formatted summary report for your boss and a chart showing the total sales by sales person.

Assigned Reading

Excel Chapter 4

Open and Save Worksheet

You must open a worksheet that lists home sales for three months.

a. Open the Excel chapter 4 starter file (COSC1301_Excel4_Starter.xlsx). Add a Standard Header to the top of the page (read COSC 1301 Lab Instructions to see what goes in the header).

b. Save the new file to your disk, diskette, or USB drive, as lastname_firstinitial_E4.xlsx.

Calculate Percent of Asking Price and Format the Data

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.

a. The percent of asking price is calculated by dividing the selling price by asking price. Enter the formula in column F.

b. Format columns D and E as currency, no decimals. Format columns G and H as dates so just the day and month (for example, 5-May) are visible. Change column F to a percentage with one decimal.

c. Widen columns to make all data and headings visible. Bold and center the column headings.

Sort the Data and Prepare the Summary Report and Chart

In order to sort the data, you must first convert the list to a data table. Once the data are sorted, in order to prepare the summary report, you must convert the data table back into a range.

a. Convert the range to a data table.

b. Sort the data first by selling agent alphabetically and then by asking price from smallest to largest.

c. Format the data table attractively.

d. Convert the data table back to a range.

e. Prepare a summary report by inserting a new row after each agent. Put an agent-name Total label in column C, and calculated values for total asking price and the total selling price in columns D and E.

f. Use a fill color to highlight each sales person's total asking price and selling price.

g. Prepare a clustered column chart on a separate chart sheet that shows each sales person's total asking price and selling price. Include a title and a legend, and format the chart to compliment the worksheet. Rename the chart sheet tab Sales Analysis.

Filter the Data Table

Your report should list just those properties sold in Miami by agent Carey, and you will use a filter to extract this data. Further, you must format before printing to make sure the report is documented and fits on one page.

a. Make a copy the Sales Data worksheet. On the worksheet copy, convert the range to a data table and filter the data table to show only those properties sold in Miami by agent Carey. Delete the Summary Total and Grand Total from the bottom of the filtered list. Change the orientation to landscape, center horizontally and vertically, and print gridlines and row and column headings.

Print the Report

a. Preview your Sales Analysis chart and worksheets to check your results, to verify that the documents have the Standard Header, and to make any necessary adjustments to print each document on one page. Save your changes.

b. Submit the Sales Analysis chart, and the two worksheets, following the instructions given by your professor.

Sample Solution Follows

[pic]

[pic]

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download