1. For each home sold, determine annual taxes as percentage of appraised value. You should create a new column to the right of annual taxes and label this new column as Percentage Taxes. You can compute percentage taxes by dividing annual taxes by appraised value and converting it into percentage. Use two decimal places for rounding off.
2. Compute the average, minimum, maximum, and standard deviation for Percentage Taxes column for all homes. Create a new worksheet and label it as Statistics. Create a table like the one shown below on Statistics worksheet. Use formulae to compute the statistic and do not enter values manually.
Statistic
Percentage Taxes
Average
...
Minimum
...
Maximum
...
Std. Deviation
...
3. Compute the average appraised home price for each year, 2009, 2010, 2011, and 2012. Using the Pivot Table feature in Excel, create a new worksheet and label it as Average Price. Your pivot table should look the one shown below:
Description
Price
Average appraised price for 2009
...
Average appraised price for 2010
...
Average appraised price for 2011
...
Average appraised price for 2012
...
4. Make a copy of the data worksheet created as part of task listed in point 1 above and label it as Taxes. Use conditional formatting to fill the cells in Red (for the column, Percentage Taxes) where Percentage Taxes exceed 2%.
5. Make a copy of the data worksheet created as part of task listed in point 1 above and label it as Top 20. Use filtering to show the top 20 homes in the dataset in terms of lot size. Make sure these homes are listed in descending order of lot size.
6. Make a copy of the data worksheet created as part of task listed in point 1 above and label it as Price Distribution. Determine the number and percentage of homes in the dataset provided to you which have appraised value less than $150,000, between $150,000 and $250,000 ($150,000 and $250,000 are included), and greater than $250,000. Draw a bar chart to show this information.
You are expected to create a table like the one shown below:
Homes
Less than $150,000
Between $150,000 and $250,000
More than $250,000
Number
...
...
...
Percentage (%)
...
...
...
Hint: Use Filter function.
7. Create a Pivot table that shows the average Appraised Price by Geographical Location and Architectural Style of House. You should create the pivot table on a new worksheet and label the pivot table worksheet as Price by Location. Write your answer to following questions on the worksheet:
a. Which geographical location has the highest average appraised price?
b. Which architectural style has the lowest appraised price?
8. Create a Pivot table that shows the average number of houses sold for different weekdays and then create a pie chart to show the percentage for each weekday. You should create the pivot table and pie chart on a new worksheet and label the pivot table worksheet as Sales by Weekday. Write answer to the following question on the worksheet:
a. Which weekday accounts for the highest average number of houses sold?