Create a workbook to determine the tax bill for various properties.
The tax rate varies depending on the district where the property is located.
First time homebuyers who do not live in district I have their tax rate reduced by 1%. Residents of district I do not get this benefit.
If a property is located in district B or District M then that property may get flat rate exemption on its taxes. The exemption amount is dependent on property value.
- Determine the base tax rate for the district where the property is located. You can lookup the base rate by district using the table Tax Rates located in the Tables sheet
- If the buyer is a first time homebuyer who does not live in district I then the tax rate is reduced by 1%. There is no reduction for those living in district I.
- If the property is in district B or district M then the taxpayer is subject to a tax exemption. The value of the exemption is dependent on property value. Look up the exemption for taxpayers in either of these districts using the table Excemption (B & M) located in the Tables sheet. (Hint: use a range lookup)
- Compute the tax owed. The tax is the base rate (adjusted for first time homebuyers if needed) times the valuation minus the reduction (if any). The tax cannot be less than zero – be sure to check for this.
- Summarize the data:
- Count the number of properties in each district
- Count the number of 1st time homebuyers.
- Make a pie chart showing the number of properties in each district
- Make a pie chart showing the number of 1st time homebuyers (only 2 pieces of pie here – 1st time and not 1st time)
Both charts should have titles and data labels showing the percentage of each piece.
Data files are attached.