BYU Student Author: @Mark
Reviewers: @Alex_Garrett, @Christian
Estimated Time to Solve: 120 Minutes
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
You work for Tax Mouse, a local tax company that serves a community of mice. Every year, you collect data from your clients, including Total Income, Adjustments to Income, and Deductions that could be itemized. You also collect descriptive data about potential dependents each client has. Your job is to start with each client’s Total Income (in Cheese Bits) and use the data collected to calculate the Total Tax owed by each client. If you can correctly calculate all steps, then your Mouse Managers have promised a Chuck of Cheese as a bonus!
Instructions
A mouse maze is included to check your answers as you go. Simply type your answers to each question in the box underneath the maze into the blue area (Cells B4:B13). If your answer is wrong, then the mouse will start heading towards a dead end in the maze. The number at the dead end is the question with an incorrect answer. You may answer the questions as you go or once you’ve calculated the Total Tax for each client.
You will need to add more columns and rows in the data to calculate each step correctly. The solution includes columns and rows not found in the original data. The gold columns should be filled out and are associated with the steps below, though some may not be used depending on your method.
- Calculate the “Adjusted Gross Income” (AGI) by subtracting “Adjustments to Income” from “Total Income.”
- Calculate the “Standard Deduction” based on the “Tax Status” of each client. “Standard Deduction” is included in the “Federal Tax Brackets” sheet.
- Determine the amount of “Deductions Claimed.” All clients want to minimize taxes, so the “Deductions Claimed” will be the larger of “Standard Deduction” and “Itemized Deductions.”
- Calculate “Taxable Income”, which is AGI reduced by any deductions claimed. However, “Taxable Income” cannot be less than 0. If it is, then put 0 as the Taxable Income.
- Calculate the number of potential dependents for each client.
- Determine whether each potential dependent qualifies as a dependent or not based on the rules at the top of the “Potential Dependents” sheet. Then, calculate the number of dependents for each client.
- Calculate the “Tax before Credits”, which is based on the table in the “Federal Tax Brackets” sheet. For information on how tax is calculated from the table, see this link under the heading “How to calculate your effective tax rate”. The columns named “Tax from Exceeded Brackets” and “Tax from Ultimate Bracket” are included as intermediate steps used to calculate the “Tax before Credits.” You will need to manipulate the “Federal Tax Brackets” table.
- Creating a copy of the “Federal Tax Brackets” sheet is recommended before manipulating the table.
- There is no “right way” to manipulate the table. Manipulate it such that you will be able to pull the information you need. It is recommended that you include the accumulating amount of tax paid as the bracket is exceeded (ie, the income is greater than the range of the bracket).
- The solution uses If statements and lookup functions on the manipulated table to find the accumulated tax from the last exceeded bracket and the tax from the ultimate bracket. The sum of these two amounts is “Tax before Credits.”
- Assume that each filer receives the full credit (2,500 Cheese Bits) for each of their dependents. Calculate the “Total Tax” by subtracting the “Credit for Dependents” from “Tax before Credits.” Assume the credits are nonrefundable (ie, “Total Tax” cannot be less than 0; put 0 if less than 0).
- Answer the questions for the maze.
Note: Your tax amounts may not exactly match the solution because of rounding and other differences, but should be very close (ie, the sum of the “Total Tax” column should be within 100 Cheese Bits of the solution).
Data Files
Suggestions and Hints
The numbers before each hint correlate with the number for the step above.
6. A nested If statement will be useful.
7b. See the dropdown for an image of the solution’s manipulated tax bracket table:
7c. XLOOKUP is recommended for this step if using a table in the format of the 7b hint. Most arguments in XLOOKUP should be used, so examine them carefully. Depending on your table format, an INDEX MATCH combination could also be used. The solution uses a long formula with nested If statements and XLOOKUPs for finding the Tax from Exceeded Brackets, inserts a column after “Tax from Exceeded Brackets” to find the lower bound of the ultimate tax bracket (this formula is very similar to the “Tax from Exceeded Brackets” formula), and uses the “Tax Status” and lower bound to perform a lookup to find the marginal tax rate, which is necessary to find the “Tax from Ultimate Bracket.”
Solution
The total tax owed by all clients is 9,698,038 cheese bits!
Challenge120_Solution.xlsm
Solution Video: Challenge 120|EXCEL – Get That Cheese!
