Data Analytics in Accounting

This is module 6 of 10 for the Business Analytics course.

Readings

Self-Directed Learning

Search the internet for articles, other resources, and examples of how business analytics is used in accounting. See what you can learn from what others are saying. Accounting is an important application of analytics for measuring, monitoring and making sense of a company’s performance.

Discussion of Module Topic

Write down your thoughts and experiences while you’re learning. For this module, this should include writing about your experience of learning about the use of data analytics in accounting. Here are some prompts to get you started for writing about the module topic:

Provide links to any useful and relevant resources about data analytics in accounting that you can find on the internet.
Do you have any experience in using analytics in accounting?
Can you find interesting examples of companies that have applied analytics to their accounting practices?
How can accounting transition from descriptive analytics to higher levels of analytics?
What are the pros and cons of accounting becoming more automated?
Can you find examples of startups whose business is providing better accounting services?
What do you think is the future of data analytics in accounting?

Discussion of Module Exercises

For this module, the discussion of the submission should include writing about your experience of working on the case. You should write about your experience working with the specific data, but also try to think about how this approach could be applied to other datasets. Here are some prompts to get you started for writing about it:

Why is it sometimes helpful to have different data sets that are not all combined into one?
How do you “connect” data sets?
What are the different ways that you can connect data sets in Excel?
What is a “relational database”? (e.g., Microsoft Access is the Office software that is designed to work with relational databases.)
How can statistics be useful for summarizing invoice data and transaction data?
How can data visualization be used to highlight key diagnostics?
How do you feel about the real-world potential of this type of analysis?
What did you conclude about how the retail location in the submission could improve its profitability?
Why is the final step of drawing business insights so important?

Module Exercises

We will be doing the case work in Teams under Files. The goal is to work with the data in Excel, interpret your findings in Word, and then present your findings in PowerPoint in the second session. Please see the files in the Accounting Case Work folder under Files in M6.

For some guidance on what you can do with the data – here is more of an “assignment” from a previous quarter in which I used this data. You DO NOT need to follow these steps, but they will give you some ideas.

The steps for doing your own analysis of the accounting data are as follows:

For this module, you are going to work with real accounting data under a fictitious company name.

Picture yourself working as an accountant at Bibitor, LLC. Bibitor, LLC is a retail wine and spirits company, with about 80 locations. Sales typically range from $420-450 million dollars and cost of goods sold can range from $300-350 million dollars.

Bibitor’s CFO, John Smith, has asked you to analyze a struggling retail location of the company in the city of Hardersfield. John wants you to analyze the contribution that each product makes to the store’s financial performance with the goal of deciding which brands to eliminate and which brands to promote to help improve the store’s performance.

John has provided you with two files to use in your analysis. The first file contains the purchase price for each product. The second file contains all sales transactions at the retail location during the fiscal year ended June 30, 2016.

Here are the steps for working on the module submission:

Step 1. Download the two .csv files from the D2L submission folder. Combine them into a single .xlsx workbook with the first worksheet as “Purchase Price and Invoice Price” and the second worksheet as “Sales Data.”

Step 2. Copy the purchase price data into a third worksheet labeled “Working Purchase Data.” In this working data, construct a field that is “Profit,” which is Retail Price – Invoice Price. Construct a second field that is “Profit Margin,” which is Profit divided by Retail Price.

Step 3. In your fourth worksheet, you should include the following:

A brief description of the data in “Working Purchase Data”
A brief definition/description of each field
Descriptive statistics of the $ fields (Retail Price, Invoice Price, Profit, and Profit Margin) including mean, median, min, max
A PivotTable that shows the top ten products by profit margin in order from highest to lowest (with three decimal places). This sort should be based on product number (in Rows). The table should also show “Description” in Rows and “Volume” in Values.
A PivotTable that shows the bottom ten products by profit margin in order from lowest to highest (with three decimal places). This sort should be based on product number (in Rows). The table should also show “Description” in Rows and “Volume” in Values.
A brief statement of what you think the key takeaways are from these two tables.

Step 4. Copy the sales price data into a fifth worksheet labeled “Working Sales Data.” In this working data, construct a field “Invoice Price” that uses VLOOKUP with the product number to pull “Invoice Price” from “Working Purchase Data.” (Hint: You may want to use the full columns e.g. A:H for the second input to the VLOOKUP). Construct a field that is “Profit,” which is SalesPrice – Invoice Price. Your working sales data should now have two additional columns of full data in them. Construct a final field “Total Profit” that is “SalesQuantity” * “Profit.”

Step 5. In your sixth worksheet, you should include the following:

A brief description of the data in “Working Sales Data”
A brief definition/description of each field
Descriptive statistics of the $ fields (Sales Price, Profit, and Profit Margin) including mean, median, min, max
An answer to the question, “Why are these statistics different than the statistics in the fourth worksheet?”
A PivotTable that shows the top ten products by Sum of Total Profit in order from highest to lowest (with three decimal places). This sort should be based on product number (in Rows). The table should also show “Description” in Rows and “Volume” in Values.
A PivotTable that shows the bottom ten products by Sum of Total Profit in order from lowest to highest (with three decimal places). This sort should be based on product number (in Rows). The table should also show “Description” in Rows and “Volume” in Values.
A brief statement of what you think the key takeaways are from these two tables.

Step 6. In your seventh worksheet, you should include the following:

A chart that shows total profits from the “top ten” table in the sixth worksheet as a horizontal bar chart with text labels of the products.
A chart that shows total profits from the “bottom ten” table in the sixth worksheet as a horizontal bar chart with text labels of the products.
A chart of your own design that highlights something that you think is interesting. Suggestion: do you want to do anything with profit margin?
A statement (4-5 sentences) to John Smith that summarizes your views about which brands to eliminate and which brands to promote. In your comments, think about whether there are any common characteristics to the best or worst. For instance, wine vs. spirit? Your conclusion should provide key takeaways, not just a laundry list of results.