51|EXCEL – Allowable Expenses

[klayton]

BYU Student Author: @klayton
Reviewers: @Parker_Sherwood, @Jonathan_Weston
Estimated Time to Solve: 75 Minutes

We provide the solution to this challenge using:

• Excel

Need a program? Click here.

Overview
Imagine you are a tax professional working at a small accounting firm. Your boss has just tasked you with going through all of the firm’s expenses from the past year and identifying any expenses that are disallowable for tax purposes. This task is critical to ensure that the firm does not mistakenly deduct any expenses that are not allowed by the tax laws and regulations.

As a tax professional, you understand that there are many different rules and regulations related to business expenses that must be carefully considered when reviewing the firm’s records. You know that some expenses, such as those related to meals and entertainment, may be partially deductible, while others, such as fines and penalties, are generally not deductible at all. For this challenge, we are assuming that business meals are only 50% allowable, and that taxes paid are not deductible.

You also know that it’s important to carefully review each expense and determine whether it was truly incurred for business purposes, or if it may be considered a personal expense that cannot be deducted. This can be a time-consuming task, as it requires you to review each expense in detail and ensure that there is sufficient documentation to support the deduction. However, with the use of tables and arrays in excel, this time-consuming task can be accomplished in a fraction of the time it would take to do manually.

Despite the challenges involved, you are confident in your ability to complete this task successfully. You know that by carefully reviewing the firm’s expenses and identifying any disallowable expenses, you can help ensure that the firm remains in compliance with the tax laws and regulations, while also minimizing its tax liability.

Instructions

Part 1

1. Using the data (starting at B12) on the Part 1 sheet, create a table and name it year_data.
2. To answer the following questions, use Advanced Filtering on your year_data table. Advanced Filtering can be found on the Data tab in the Sort & Filter area.
   • Find the total amount of money spent on Meals for the year.
   • Find the total amount of money spent on taxes for the year.
   • For meals to be allowable, they must have a business purpose AND be reasonable in amount. You know that your boss likes to be conservative in his tax stances, so any business meal that cost $100 or more is unreasonable. Find the total amount of money spent on meals where a client was present, and the amount was under $100.
3. On the right are three more questions just for you to practice using advanced filter with more filters on the data. Play around with it!

Part 2

On the Part 2 sheet, you are looking at only the expenses for October through December. You will use arrays to calculate how much money was spent each day for each category. This is important because for accrual method taxpayers, typically you pay for a good/service before receiving it. For tax purposes, these expenses cannot be deducted against income until the goods/service is received. Fortunately, for certain expenses, if you receive the goods/service within a certain amount of time into the next year, those expenses can actually go against the previous year’s income. The goal with this sheet is to identify applicable expenses that might be able to be deducted on the 2022 return.

1. Create a table using the data on sheet Part 2 and name it eoy_data.
2. Highlight the range B3:B42 and name the range “date”. You can do this using the Name Manager (found on the formula tab) or by typing the name in Name Box with the range highlighted.
3. Repeat step 2 for the remaining columns: Category, Amount and Description
4. Using these named columns, in cell H4, create an array formula that calculates the amount of office supplies purchased on 10/5/2022. Use absolute/relative references in your formula, as you will need to be able to drag the formula across the remaining highlighted cells to calculate every expense. To make the formula an array formula, hit ctrl + shift + enter (instead of just enter) after typing out the entire formula.
5. After filling out all the highlighted cells, calculate the sums at the bottom to ensure it matches the total amount of expenses for the month.
6. Look at the data, does anything seem out of place? Do you have any suggestions on how to save the company money?

Part 3

On the Part 3 sheet, there are two little tables to be filled out. The first one is a simple book to tax reconciliation to figure out taxable income. Of all the expenses, the three that create a difference are meals, taxes, and prepaid advertising. Remember that meals are only tax deductible to the extent that they are business related and reasonable (50%). Taxes paid are never tax deductible. And since we are an accrual method company, prepaid advertising isn’t deductible until the service has been completed. As of the end of the year, none of the advertising services that had been paid for in October or later has been received. All these numbers have been calculated in Parts 1 and 2. If an expense is deductible for book purposes but not tax purposes, that results in an increase to income.

The second table is for tracking expenses that could potentially be deducted in 2022 instead of 2023. This only applies to Prepaid Advertising as both Meals and Travel are permanent book/tax differences. Prepaid Advertising would be able to be deducted so long as the service is received within 3.5 months of paying for the service.

Data Files

• Challenge51_Data.xlsx
• Challenge51_Data_Dictionary.docx

Suggestions and Hints

Part 1
For advanced filtering you need to be sure to clear the filter and start fresh to apply the filter.
The third filter should all be on one line. “Meals” for the category. “<100” for the amount. “client” for the description.

Part 2
The formula in cell H4 should look like this “{=SUM(($G4=date)*(H$3=category)*amount)}” . You should then be able to drag it across to column O, and then drag it down to row 25.

Solution

Challenge51_Solution.xlsx
Solution Video: Challenge 51|EXCEL – Allowable Expenses

[TylerBooth]

Great challenge! I had never used the Advanced Filter tool like you demonstrated. I can see how that would be very helpful to easily filter based on multiple criteria at once. I don’t have as much practice with array formulas and it gave me some trouble. I was able to come up with the same solution using a SUMIFS formula instead. My formula from cell H4 is below:

=SUMIFS(amount,date,$G4,category,H$3)

[Hyrum]

Lovely challenge! I think my favorite part was the array formulas. I took a class where they taught them last year and I can see them being super applicable! I came up with the same formula as you, but it took me a while to remember that I have to do ctrl + shift + enter in order to make it an array formula. I also struggled a bit because I had to call the column names instead of just highlighting the rows. Here is the formula I had for that section: {=SUM(($G4=date)*(H$3=category)*amount)}

[Jonathan_Weston]

Great Challenge. I thought Part 3 was a great way to tie together all of the work for the challenge. The array formulas in Part 2 were very useful. I hadn’t worked with arrays for a minute so I had to take some time brushing myself up on that tool. I forgot to include a total row in part 1, so I just use the selected cells sum tool to calculate those first 3 answers.

[Jae]

Interesting challenge, I enjoyed going through the whole process. For Part 1 I found it quicker to use array formulas instead of the advanced filtering in the instructions. I did still use the advanced filtering for the description question though. It was really satisfying to fill that whole table in Part 2 in one go with the Array Formula.

[Taj_Bhatti]

Part 1