3011 Excel Project
Due Date: 10/15/2013
For the project below, use the file Excel Project 2013.xlsx from Blackboard.
1. (12 points)Enter your name and due date on the Summary worksheet.
a. (On the Home Sales Data worksheet, in column E, calculate the Tax Percentage. Using a nested IF calculate the following (Hint: do not use the function AND because if the house does not have 1 bedroom and does not have 2 bedrooms then the house MUST have more than 3 bedrooms.)
b. 1 Bedroom homes use 3%
c. 2 Bedroom homes use 4%
d. For all homes with 3 OR MORE bedrooms:
i. if the age of the home is less than 10 years old, use 5.25%
ii. If the age of the home is less than 20 years old (but more than 9), use 5.5%
iii. If the age of the home is 20 years old or more (or for all other homes), use 5.75%
2. (3 points)In Column F, multiply the Tax Percentage by the Sales Price
3. (5 points)Create a range name for cells O3:P7 on the Homes Sales Data worksheet. The name should be Your last name plus the word “Realty”. For example, James Bond would name his range “BondCompany”.
4. (12 points)In Column M, find the Realty Company from the lookup table in cells O3:P7. In cell P3, change the Realty Name to your last name Realty (e.g. Devesa Realty). Use the VLOOKUP command to calculate. (Hint: explore the fourth component of the VLOOKUP function.) So, home with square footage from:
a. 0 to less than 1200 feet, go to (Your Name) Realty
b. 1200 to less than 1800 feet, go to Florida Homes
c. 1800 to less than 2500 feet, go to Arturo’s Realty
d. 2500 to less than 3000 feet, go to Palm Builders
e. Homes with 3000 feet or more, go to Sunshine Developers
5. (12 points))Go back to displaying cell values, not formulas. Make cells A1:M73 a table named “Listing” in table style Light 10
a. In a multi-level sort, sort the data by the following:
i. Realty Company in A to Z order
ii. Style in A to Z order
iii. Date Sold in Newest to Oldest
b. Add in Total Row and have it average Sales Price, average Square Footage, count Realty Company columns
6. (12 points)Group together worksheets “East”, “South”, “Mid-West”, and “West” and do the following:
a. Use AutoSum to total the amounts in cells B10, C10, D10, and E10.
b. Format B5:E10 as currency, no decimal places.
c. Type in cell B13, .04 if your last name starts with the letters A-M, .06 if your last name starts with the letters N-Z.
d. Format B13 as percentage, 2 decimal places.
e. Calculate in cell D5 the sales commission. Using cell references ONLY, multiply the commission percentage by the Gross Sales. Copy this formula to cells D6:D9.
f. Calculate in cell E5, subtract Expenses and the Commission from the Gross Sales. Copy this formula to cells E6:E9.
7. (12 points)Using 3-D references and Grouping on the “Fiscal Year 2013” worksheet, sum up all the regions of each Subsidiary in cells B5:D9 for the Gross Revenues, Expenses, Commission and Revenues.
a. Format columns C, D, and E as currency, with 2 decimals.
b. In cells B10 to E10 find the total for Gross Revenues, Expenses, Commission and Revenues.
8. (12 points)On the Fiscal Year 2013 worksheet, create a 3-D pie chart in cells A12:F29 where the Net Revenues (E5:E9) is charted with the Subsidiaries (A5:A9) as the data labels.
a. Add numerical Inside Data labels to the pie chart.
b. Move the legend to the left side of the chart.
9. (12 points)On the Summary worksheet, use the COUNTIF and SUMIF functions to fill in columns B and C. For example, in cell B12, count the number of homes with 1 Bedroom. In cell C12, sum the Taxes for homes with 1 Bedroom. Repeat in the cells below for the different numbers of Bedrooms. Making sure all formulas are fully displayed, print out this worksheet with formulas displayed in landscape mode. Fit this into a single page.
10. ( 5 points)Copy Home Sales Data worksheet to a new worksheet and name the new worksheet “Subtotals”. (Tip: Make sure to remove the Totals row from the table first)
a. Add subtotals by Realty Company and calculate the average of the Asking Price
11. ( 3 points)Save your file as “ExcelProject YourName.xlsx” where your name is the first letter of your first name plus your last name (for example – ADevesa). (Under Assignments, Lab Assignments, click View/Complete Assignment, section #2 allows you to attach a file.) Make sure to attach this file to the Excel Project assignment on Blackboard. Save this to Blackboard BEFORE your class on the day this is due and hand in the printouts NO LATER THAN your class on the due date.
3011 Excel Project