Objectives
Create, edit, save and print an Excel Workbook
Enter data and apply basic formatting to a Worksheet
Create, modify and format charts to display information
Create, sort and filter an Excel Table
Create, modify and use formulas and functions to return information
Overview
Ms. Chavez has asked you to create a spreadsheet for three of the top Realtors for the past month for the company you created the newsletter for in the last module. In the spreadsheet, she would like to be able to see the total sales by city and by realtor as well as the income to the Realtor and the company at 4% each commission on each selling price. Include a separate worksheet in the spreadsheet document with the name and logo of the company you created in the previous module (copy/paste from Word to Excel for these).
Sales are as follows:
Murray – Sold 8 houses in the cities shown at the prices below:
Denver – 115,000
Brighton – 362,000
Louisville – 657,000
Aurora – 539,000
Lakewood – 282,000
Lafayette – 795,000
Lone Tree – 604,000
Broomfield – 436,000
Guled – Sold 4 houses at the following prices:
Lousiville – 748,000
Broomfield – 133,800
Aurora – 352,000
Thornton – 579,000
Gutierrez – Sold 5 houses at the following prices:
Aurora – 1,782,072
Denver – 922,000
Broomfield – 728,200
Lakewood – 255,000
Thornton – 499,000
Instructions
Create a Spreadsheet:
The layout for your spreadsheet needs to have the ability to calculate:
- Total sales for each Realtor
- Total sales per city in the Metro area
- Average Sale per city
- Average Sale per Realtor
- Total Commission per Realtor
- Total Commission for the moving company
- Average Commission per Realtor
To complete the calculations based on commissions, enter the commission percentage (4%) one time on your spreadsheet, and use an Absolute Reference to calculate each individual commission amount. Do not create a new formula for each cell for those calculations.
Format:
Use the formatting techniques learned in this Module, and include the following at a minimum:
- Borders and Shading
- Merged Cells
- Use proper cell format for each cell based on the information in the cell
- Include the company Logo (from Module 2) on the Spreadsheet in a separate worksheet
Chart:
Create two Charts and place them in a New Worksheet
- One Chart for total sales by Realtor
- One Chart for total sales by City
- Use the Chart Format of your choice
