Step	Instructions	Points Possible
1	Download Excel File : https://ufile.io/fvfma 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%	12.000
2	In Column F, multiply the Tax Percentage by the Sales Price	3.000
3	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	5.000
4	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. Feidelman 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 Arturos Realty d. 2500 to less than 3000 feet, go to Palm Builders e. Homes with 3000 feet or more, go to Sunshine Developers	12.000
5	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	12.000
6	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.	12.000
7	Using 3-D references and Grouping on the Fiscal Year 2018 worksheet, sum up all the regions of each Subsidiary in cells B5:E9 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.	12.000
8	On the Fiscal Year 2018 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.	12.000
9	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, in landscape mode. .	12.000
10	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	5.000
11	Save your file as ExcelProject YourName.xlsx where your name is the first letter of your first name plus your last name (for example NLastName).	3.000