109|EXCEL&TABLEAU – The Cookie Company

BYU Student Author: @Erick_Sizilio
Reviewers: @Marco, @Nate
Estimated Time to Solve: 60 Minutes

We provide the solution to this challenge using:

  • Excel
  • Tableau

Need a program? Click here.

Overview
You work for an accounting firm and are currently part of an engagement team auditing The Cookie Company, a company that wants to go public. The company has been in operation since 2018, when Mr. Dough, the owner of the company, decided to use his grandmother’s delicious and unique cookie recipe from a small storefront. The company has since been very successful and has gained the attention of many investors. A total of 278 stores are currently in operation, located in all regions of the U.S. Mr. Dough invested a significant amount of his own money into the company. Also, it wasn’t difficult for him to get loans to expand to other locations because of how successful the company has been. Mr. Dough believes that the next step to improving the business is to go public and restructure the company to reduce the amount of debt it has.

Mr. Dough hired managers for each of the 278 locations. Each manager is responsible for creating financial statements for their own location. Mr. Dough provided the following files:

The Cookie Company accountants created the consolidated financial statements by combining all financial statements from individuals stores as shown in the Financial_Statement.xlsx file.

Instructions
Your job is to verify that the consolidated financial statement (found in the Financial_Statements Excel Workbook) is correct. Take notes about any discrepancies you find as you will have to report it to your manager and then to the client.

  1. Start this part of the audit by checking the relationship of the different accounts in the financial statements.
  2. Then, recreate the “Consolidated Financial Statement” by using the General Ledger and chart of accounts.
  3. After recreating the consolidated financial statement, verify that it matches with the consolidated financial statements provided by Mr. Dough.
  4. If there are significant differences, see if you can find why the consolidated financial statement that you created is different than the one Mr. Dough provided.
  5. Write simple report, it can be in bullet point format, that shows all the discrepancies that you found and potential reasons why they occurred.

Data Files

Suggestions and Hints
  • Join the RawData and the chart of accounts together
  • The first three digits of the ExpenseID represents the StoreID and the last four charaters represents the Account#.
  • Be careful with your joins!
  • You can union multiple sheets using the Wildcard Union in Tableau Prep. Here is a link explaining how to use it: Wildcard Union for Tableau Prep

Solution