195|EXCEL – Mega Texas Taxes

Challenges
, , ,
1

BYU Student Author: @Marta_Ellsworth
Reviewers: @Kyle_Nilsen, @jimmy_han
Estimated Time to Solve: 40 Minutes

This challenge was written in partnership with a business professional. The data, scenario, or both were inspired by real-world business scenarios.

We provide the solution to this challenge using:

  • Excel

Need a program? Click here.

Overview
You’re a key member of the accounting team at a small local company. Each month, you’re tasked with the critical role of managing sales tax payments. However, there’s a twist in the process. The accounting system you rely on provides a monthly sales tax report, but it categorizes the sales tax according to various tax jurisdictions.

Your primary focus is on the Texas sales tax. Unlike other states, Texas requires businesses to report and pay sales tax for each individual city. Adding to the complexity, you’ve encountered some inaccuracies with the address information in your system, rendering it unreliable for this task. Consequently, you must depend solely on the tax report to navigate these payments accurately. After you create a new city column use Excel to create a pivot table with the city and the total tax for that city.

Instructions

  1. You need to create a new column called “City”. This column should be the city name based on the Tax Jurisdiction column.
    a. Any Tax Jurisdiction item that includes “Texas” or “County” should be a blank in the city column.
    b. Some of the cities have extra information we want to remove. Make sure to keep the city name, we are just removing the extra jurisdiction information. Remove TX, City, MTA, ATD, Special, Crime Control, Fire Control, and Mun Dev Dist.
    c. There is a 5 or 6 digit number in some of the jurisdictions, remove those as well.
  2. Remember we want to be able to use this in the future, try and get the information down to a formula that can be copied.
  3. Create the pivot table output. It should only include the City and Tax columns.

Data Files

Suggestions and Hints
  • One way to accomplish this task is to create a mega formula. (Also called a nested formula.) You can find more information on these here or here.
  • Try building out each step in a different cell and then consolidate them all into one mega formula. If you are still having issues, remember you can always watch how to do it in the solution video after you have completed all your formulas.
  • This will be a very large, nested function, remember you can always use the evaluate formula dialog box built into Excel to make sure it is working how you would like it to.
    -Don’t forget to use the trim function, so your cities don’t have leading and trailing spaces.
  • The most useful formulas to solve this are SUBSTITUTE() and SEARCH()/FIND().

Solution

Challenge195_Solution.xlsx
Solution Video: Challenge 195|EXCEL – Mega Texas Taxes