View the step-by-step solution to:

United Realty Holding Company, Inc. Author: Date: Report's Purpose: To record and display home data and realty's revenues Home Listing Summary:

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.

United Realty Holding Company, Inc. Author: Date: Report's Purpose: To record and display home data and realty's revenues Home Listing Summary: # of Bedrooms Total Properties Total Taxes 1 2 3 4
Background image of page 1
Listing ID Asking Price Age Property Tax Rate Annual Tax Sales Price Style Bedroom Bath Date Listed Realty Company 138 $184,400 2,250 40 $182,500 Condo 3 2 11/4/2009 5/21 152 $85,500 1,900 5 $81,000 Ranch 2 1.5 7/4/2009 6/1 107 $133,000 1,850 5 $115,500 Ranch 2 1.5 3/2/2010 5/16 172 $123,500 1,894 14 $123,500 Ranch 2 1.5 7/27/2009 5/15 121 $105,000 1,920 8 $95,500 Ranch 2 1.5 4/27/2010 5/14 159 $127,000 1,880 8 $127,000 Ranch 2 1.5 7/27/2009 4/29 140 $156,000 1,920 1 $134,000 Ranch 2 1.5 12/29/2009 4/28 132 $210,000 2,116 25 $195,250 Victorian 3 2 9/1/2009 6/20 161 $145,000 2,150 10 $146,250 Victorian 3 2 11/2/2009 6/1 164 $130,000 2,000 11 $117,000 Victorian 2 2 5/15/2010 5/25 147 $137,500 1,837 4 $137,500 Victorian 2 1.5 4/16/2009 4/7 163 $61,900 837 10 $53,000 Condo 1 0.5 2/5/2010 6/20 133 $66,000 1,159 25 $62,500 Ranch 2 1 9/13/2009 6/13 150 $75,900 997 4 $75,000 Ranch 1 0.5 6/13/2009 6/2 156 $75,000 1,030 6 $69,750 Ranch 2 1 11/19/2009 5/28 127 $58,000 1,051 15 $58,000 Ranch 2 1 12/5/2009 5/17 125 $60,000 1,198 14 $51,000 Ranch 2 1 3/8/2010 5/9 119 $86,900 1,165 7 $88,500 Victorian 2 1 8/10/2009 5/15 106 $77,000 900 5 $67,750 Victorian 1 0.5 6/21/2009 5/14 130 $54,000 1,142 21 $51,750 Victorian 2 1 10/11/2009 5/5 105 $73,900 970 4 $63,500 Victorian 1 0.5 2/7/2010 4/24 129 $78,000 1,080 21 $68,500 Victorian 2 1 1/9/2010 4/2 124 $70,000 1,505 14 $70,500 Condo 2 1.5 3/15/2010 5/30 104 $87,000 1,273 4 $81,750 Condo 2 1 9/30/2009 5/12 171 $67,000 1,350 13 $68,250 Condo 2 1.5 9/23/2009 5/6 157 $88,900 1,549 7 $77,250 Condo 2 1.5 6/4/2009 5/4 112 $105,000 1,620 6 $106,000 Condo 2 1.5 6/27/2009 5/3 120 $76,600 1,200 7 $68,000 Condo 2 1 5/7/2009 5/1 101 $87,400 1,236 3 $77,750 Condo 2 1 11/22/2009 4/14 162 $89,000 1,746 10 $77,250 Condo 2 1.5 2/4/2010 4/13 166 $99,500 1,725 12 $98,500 Condo 2 1.5 2/25/2010 4/12 111 $112,900 1,700 6 $115,000 Condo 2 1.5 2/4/2010 4/8 144 $75,500 1,275 3 $77,000 Condo 2 1 10/10/2009 4/1 115 $87,200 1,229 6 $87,000 Condo 2 1 1/23/2010 6/26 155 $81,000 1,365 6 $81,750 Ranch 4 1.5 6/29/2009 6/23 109 $102,000 1,606 5 $103,000 Ranch 2 1.5 8/5/2009 5/29 103 $95,000 1,715 4 $95,000 Ranch 3 1.5 5/18/2010 5/22 137 $62,000 1,480 40 $59,500 Ranch 2 1.5 9/11/2009 5/20 149 $75,000 1,338 4 $76,500 Ranch 2 1.5 4/8/2010 5/15 165 $83,500 1,560 11 $73,250 Ranch 5 1.5 8/13/2009 5/13 118 $92,000 1,415 7 $91,000 Ranch 2 1.5 7/6/2009 4/24 108 $116,000 1,720 5 $99,750 Ranch 3 1.5 3/18/2010 4/23 116 $97,500 1,500 7 $82,750 Ranch 2 1.5 6/28/2009 4/16 139 $69,900 1,400 45 $60,750 Ranch 4 1.5 3/10/2010 4/10 170 $105,000 1,680 13 $101,750 Ranch 2 1.5 4/26/2009 4/6 110 $94,000 1,305 5 $88,250 Spanish 2 1.5 6/20/2009 5/25 117 $100,000 1,535 7 $91,000 Spanish 2 1.5 5/9/2009 4/28 123 $97,500 1,739 13 $84,750 Victorian 4 1.5 5/10/2010 6/21 102 $110,900 1,740 4 $105,250 Victorian 2 1.5 8/23/2009 6/20 141 $144,900 1,710 1 $143,250 Victorian 2 1.5 4/16/2010 6/2 158 $80,500 1,258 7 $74,750 Victorian 2 1 4/16/2010 5/27 131 $107,000 1,464 22 $107,000 Victorian 2 1.5 12/22/2009 5/25 135 $93,900 1,428 40 $82,500 Victorian 2 1.5 10/3/2009 5/23 136 $82,000 1,375 40 $82,750 Victorian 2 1.5 5/17/2009 5/6 134 $72,500 1,280 38 $70,250 Victorian 2 1 3/1/2010 5/4 168 $97,500 1,430 13 $99,250 Victorian 2 1.5 5/19/2009 4/5 148 $215,000 2,848 4 $193,500 Ranch 3 2.5 12/25/2009 5/30 145 $199,900 2,580 4 $187,750 Ranch 3 2.5 6/30/2009 4/18 167 $205,000 2,650 13 $178,250 Victorian 3 2.5 1/9/2010 6/21 153 $215,000 2,664 6 $212,750 Victorian 4 2.5 9/20/2009 6/13 126 $158,000 2,563 14 $159,500 Victorian 3 2.5 11/19/2009 6/11 143 $215,000 2,921 3 $215,000 Victorian 4 2.5 11/17/2009 5/6 160 $208,000 2,600 10 $178,750 Victorian 3 2.5 8/4/2009 4/29 142 $180,000 2,774 2 $165,500 Victorian 3 2.5 12/10/2009 4/5 113 $104,500 3,245 6 $98,000 Condo 2 1.5 7/22/2009 4/2 169 $92,500 3,985 13 $78,500 Ranch 2 1 5/4/2010 6/19 114 $103,000 3,987 6 $90,500 Ranch 2 1.5 3/24/2010 6/12 151 $87,000 5,124 5 $87,000 Victorian 2 1 1/23/2010 6/27 122 $94,500 3,254 9 $95,250 Victorian 2 1.5 4/5/2010 6/15 128 $169,500 3,750 19 $165,000 Victorian 4 2.5 3/14/2010 5/30 154 $87,500 4,521 6 $87,500 Victorian 2 1 8/29/2009 5/27 146 $190,000 3,575 4 $171,000 Victorian 3 2.5 9/2/2009 4/22 Square Feet Date Sold 2013
Background image of page 2
Show entire document
Sign up to view the entire interaction

Top Answer

Answer I have assumed... View the full answer

Excel Project 2013 (1).xls

United Realty Holding Company, Inc.
Author:
Date:
Report's Purpose: To record and display home data and realty's revenues Home Listing Summary:
# of Bedrooms
1
2
3
4 Total Properties
4
49
12
6...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online