BYU Student Author: @Trent_Barlow
Reviewers: @Marta_Ellsworth, @James_Gerstner
Estimated Time to Solve: 20 Minutes
This challenge was written in partnership with an accounting 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 work for a small accounting firm that handles taxes and business management for privately-held businesses. One of your clients, a small movie-production company, wants to gain a better understanding on their monthly expenditures and from the sources of these expenses. They have provided you with a list of transactions, along with categories and subcategories into which they want the transactions organized. Your task is to categorize each transaction according to the provided data.
Open the Excel template provided and follow the instructions below.
Instructions
- First, go to the “TransactionData” sheet. Under “Sub-Category,” create a formula that matches the “Charge Subtype” of each transaction with the data in the “ChargeSubtypeClassification” sheet. Use an XLOOKUP or VLOOKUP.
Solution Code
=XLOOKUP(E2,ChargeSubtypeClassification!$C$4:$C$68,ChargeSubtypeClassification!$D$4:$D$68)
- Certain types of subcategories should be assigned to specific categories. For example, the pay for an employee in the R&D department should be classified as R&D expenditures. This principle also applies to outside contractors performing specialized work a single department. It is essential to identify which transactions belong to specific categories. In the “P&L Category Indicator” column, create a formula that assigns a 1 if the Sub-Category is “Salary”, “Payroll Tax Expense”, or “Contractor”; and a 0 for all other cases .
Solution Code
=IF(OR(M2=“Contractor”,M2=“Payroll tax expense”,M2=“Salary”),1,0)
- Determine the “P&L Category” for each transaction. If the “P&L Category Indicator” is 0, it should display “G&A”. Otherwise use an XLOOKUP or VLOOKUP to find the transaction’s Reference in the table on the “PLCategorizationByName” sheet, and return the corresponding value. Note that this table is not exhaustive and was provided by the client! If there is no match in the table, display “Misc” as the client is interested in identifying transactions that have not yet been classified.
Hint: Incorporating IFERROR might be useful in this formula.
Solution Code
=IF(N2=0,“G&A”,IFERROR(VLOOKUP(G2,PLCategorizationByName!$B$3:$C$60,2,0),“Misc”))
- Using the transaction data, including the new columns you’ve added, create a pivot table in a new sheet named “Solution Pivot Table”. The “Amount” should be the Value, formatted in dollars. The client wishes to analyze expenditures by month, and thus, the pivot table should include the Invoice Date, P&L Category, and Sub-Category.
Data Files
Solution
