Overview for this book

Price: $ 79.99

It is recommended that this case be used in Cost Accounting or Managerial Accounting.

Students download an Excel file containing sales data for a fictitious company called 'MegaCorp, Inc.'  This data has been cleaned as well as scrubbed, and it is ready for analysis.  The sales data is organized in the following fields: Customer ID, Product, Salesperson, Salesperson Title, Commission Rate, Region, Customer Type, and Sales by Quarter.

The students must analyze the data using Pivot Tables in Excel and answer specific questions such as:

• What was Joan Townsend’s Average Sales in dollars?
• What was Gary Peters’ Sales, in dollars, of Miniserver C1?
• How many orders for Gigaserver GZX2 were placed in the North region?
• Of the total revenues of the four regions what were the highest total revenue in dollars?
• Sales of Gigaserver GZX2 represent what percent of the total sales of the West region?
• What region had the highest sales revenue for Miniserver C2?
• What was total Large business sales in the West region?
• What region had the highest sales revenue for Large business sales?
• What was the number of orders placed by Government in the East region?

Overview for this Book

It is recommended that this case be used in Financial Accounting after the accounting cycle has been covered.  In addition to reinforcing accounting processes, the case exposes students to a key step in the data analytics process, namely the extract, transform, and load (ETL) process. Students download an Excel file containing the General Journal for the month of January.  They then perform ETL processes using a tool of your selection (Alteryx, Tableau, Power BI, Excel) to transform the data as follows:

• Remove the blank rows
• Remove the extra columns
• Sort the file
• Rearrange the columns
• Create a running balance
• Export a general ledger
• Merge some fields
• Summarize on account
• Export an unadjusted trial balance.

After completing the above, students answer nine questions regarding the ending balances of specific accounts and calculate the net income at the end of the month.

Overview for this Book

This is a more complex case with two streams of input that must be stacked, for a slightly more complicated ETL process.  It is recommended that this case be used in Intermediate Accounting after the accounting cycle has been covered.  Students download an Excel file containing two sets of data.  The first set includes transactions from the General Journal for the month of January. The second data set includes the General Journal for February through December which was created in an alternative accounting system.  The second set is noticeably different from the first one. Both sets need to be transformed as discussed in Case 1. Additional transformations are required to the second set of data before the two sets can be combined and exported as a General Ledger and an Unadjusted Trial Balance.

These transformations are required because:

• Field names are in different rows
• Field names are different
• Fields are combined and must be separated
• The field containing account numbers must be converted to the original four-digit code
• Extra fields must be removed
• Field names must be changed
• Fields must be converted from string fields to numeric fields
• New fields must be created
• Two streams must be stacked

After completing the above, students answer nine questions regarding the ending balances of specific accounts and calculate the net income at the end of the year. As with the first case, this project can be solved in Excel and/or Alteryx, Power BI Tableau Prep, or any other ETL software. The following flowchart depicts the Alteryx steps for this case.

Overview for this Book

It is recommended that this case be used in Intermediate Accounting after depreciation has been covered.

Students must analyze the data and calculate the change in net income as the depreciation method changes.  The students download an Excel file containing two data sets and transform the data as explained in Case 1 and Case 2.  Using the General Ledger generated in their transformation, the students create a Fixed Asset Register at the journal entry level.  First, they must check that the fixed asset acquisitions comply with the capitalization policy. Second, using the additional information available regarding the useful life and residual value, the students determine the change in the net income if the method of depreciation is altered from the straight-line method of depreciation.

After completing the steps, students answer questions regarding the difference in net income as a specific Fixed Asset’s depreciation method is changed.  An additional question is related to the net income for the year prior to the adjustments.

This project too can be solved in Excel and/or Alteryx, Power BI Tableau Prep, or any other ETL software.

Overview for this Book

This case be used after contribution margin is defined as a product's selling price minus all associated variable costs. In addition to standard transformations this case requires files to be joined and a pivot table to be transformed to a flat data set. This comprehensive case can be the student’s first exposer the extract, transform, and load (ETL) process. Students download an Excel file containing four related data sets. They then perform ETL processes using a tool of your selection (Alteryx, Tableau, Power BI, Excel) to transform the data as follows:

• Remove the blank rows.

• Remove the extra columns.

• Join data streams.

• Make data types uniform.

• Rearrange a pivot table into a flat table.

• Create calculated fields.

• Create primary and foreign fields.

• Correct data errors.

• Summarize by salesperson.

• Export a Commission Expense Report.

• Modify in Excel.

After completing the above, students answer nine questions regarding the contribution margin by salesperson, region and product.