38|EXCEL – Cowabunga Surf Audit

Alex_Garrett · April 24, 2023, 8:50pm

BYU Student Author: @Alex_Garrett
Reviewers: @Boston, @Nate, @Spencer
Estimated Time to Solve: 15 Minutes

We provide the solution to this challenge using:

Excel

Need a program? Click here.

Overview
You have been working as an auditor in Southern California for the past 2 years now, and you are having a blast! One of the clients you are currently working on is Cowabunga Surf, a small company based in San Diego that purchases and resells the latest and greatest surfboards. They promised that at the end of the audit, you can take home a surfboard of your choice, so you want to do an excellent job.

It’s now January 1st, and you are counting inventory and matching your count against the company’s count. Cowabunga Surf has a higher-than-average inventory turnover (i.e., they sell their inventory quickly). As a result, there isn’t a lot to count, so you decide to count every individual surfboard. Your findings are on the “Audit_Count” sub-sheet in the “Cowabunga_Inventory” spreadsheet. The company has also provided the inventory count that they have on record as of January 1st on the “Cowabunga_Count” sub-sheet in the same spreadsheet mentioned above. This sheet, however, did not include the following tags that were found by the company after it had already submitted its count. Make sure to update Cowabunga’s inventory count with the following additional tag numbers before starting your analysis. NOTE: For best results, enter the data manually as Excel’s auto-formatting may mess up further analysis.

Tag #4019, Inventory #42-0047-1453, Quantity: 6, Plant: CA99
Tag #8068, Inventory #42-0047-1443, Quantity: 2, Plant: WA13
Tag #4020, Inventory #42-0047-1447, Quantity: 1, Plant: CA99
Tag #4021, Inventory #42-0047-1453, Quantity: 3, Plant: CA99

Instructions
Your job is to compare your inventory count with the count provided by Cowabunga Surf. For purposes of this challenge, assume that your count (the auditor) is correct and that inventory needs to be adjusted to reflect the correct number. Assume that the tolerable misstatement for the inventory account is 0 (i.e., we need to find all mistakes) and that any difference between your count and the company’s count is a mistake in sales. There are no timing differences. For example, if your count is higher than the company’s count, then sales are overstated. On the other hand, if your count is lower, sales are understated. At the end of the challenge, make an adjusting entry to reflect actual inventory and actual sales on the “Solution” sub-sheet. Assume that the books are still open for the year ended and that all sales are made on account.

Data Files

Suggestions and Hints

Look up how to use the subtotals feature in Excel to analyze the data quickly. The SUMIF() function can also be used to summarize the data.

Solution

Challenge38_Solution.xlsx
Solution Video: Challenge 38|EXCEL – Cowabunga Surf Audit

MitchFrei · April 25, 2023, 2:43pm

Here’s my journal entry:

Looks like Cowabunga didn’t count any Sundrift surfboards, which was the main culprit of their overstatement of revenue!

Marco · April 25, 2023, 5:10pm

Here it is:

Looks like someone has some explaining to do regarding the Sundrift boards.

klayton · April 25, 2023, 7:04pm

I decided to use a PivotTable to do my comparison of the two. I summarized the Cowabunga count and them compared it to my count. Someone messed up at missed all The Sundrift! Although the The Surf Lancer was also off by a lot.
As for my Surfboard payment, I think I’ll take El Mar.
Here’s my JE:

Boston · April 25, 2023, 7:47pm

Surfs up dude!

Andrew · April 25, 2023, 11:24pm

What if it wasn’t just 4 inventory items to enter? What if it was 100?

This was a great challenge. Instead of typing the new data in manually, I decided to use the TEXTSPLIT() formula, then using this formula {=RIGHT(L3,LEN(L3)-FIND(“#”,L3))} to parse out the value from the text (I replaced the ‘#’ for a ‘:’ for the applicable values). This would be useful in the scenario that more inventory items needed to be entered.

Jae · April 26, 2023, 5:48am

It seems like the data file I downloaded is different now as my result was different from the other comments/solution video. After looking through the sheets again to see what I missed I noticed that the challenge data file has 56 rows now for Cowabunga_Count instead of the solution file, which has 60. As a result, the number of misstatements in my file was 15 instead of 3.
Here is my solution:

Nate · April 26, 2023, 9:44pm

@Jae, I’m so grateful to see your solution because I got the same answer as you did, and I was getting worried looking at all the other posts in this thread hahaha. Way to catch that difference though! I’m not sure if I would’ve been able to catch that.

Screen Shot 2023-04-26 at 3.43.54 PM

I thought this was a really cool challenge to brush up on some auditing. It’s been a little over a year since I took my last auditing class, and I need all the practice I can get since I’ll be starting the CPA in the next few months. Thanks for this, @Alex_Garrett, and Cowabunga man!