ACCTG 331: Intermediate Accounting and Analytics I

· Under Data Analytics, click on Analytics Case 2 (submission window opens 12:01 am, Wednesday, October 13, 2021) and upload your assignment.

· Please note that your file must be in Excel (.xlsx extension)

Directions:

· The dataset needed for this assignment can be found in your Canvas course in the Data Analytics and Accounting module. (AIA_ch05_data_and_student_submission_template.xlsx)

· Before you get started with the project file in Excel
· You will be using Excel to calculate ratios using formulas, create and add to a column chart, and use the column chart to answer questions. You will start with an Excel file containing select data from most of the DOW 30 companies.

· Microsoft 365 Excel for Windows must be used for this project. You do need to use Microsoft 365 Excel for Windows; earlier versions of Excel will not work for portions of the project. Because there are still a few differences between the online version of Excel and the desktop version, the desktop version of Excel is recommended. The mobile app for Excel is not full-featured and will also not be adequate for this project. Apple Numbers and Google Sheets cannot be used for this project – only Microsoft Excel.

· Before starting the project, update your version of Microsoft 365 and Excel. For instructions on how to update the software, search for “how to update Microsoft 365” in a search engine. Also know that when you download Microsoft 365 for the first time, that version may not be the latest version – you may still have to update Microsoft 365.

· Be sure to save your Excel workbook frequently throughout the process – and be sure to update your version of Microsoft 365 before beginning the project. Also know that when you download Microsoft 365 for the first time, that version may not be the latest version – you may still have to update Microsoft 365.

· A 10% (5 point) penalty will be assessed for doing any of the following “Do NOTs”:

· Do NOT change the file name.

· Do NOT change the file format.

· Do NOT add or delete tabs, unless instructed to do so.

· Do NOT add or delete columns and/or rows, unless instructed to do so.

· All ratios should be computed using formulas.

· No credit will be given for hard coded amounts.

· Your answers to the questions should be written as if you are presenting to a company’s Board of Directors. Connections between the data and case information should be exhibited. Discuss at the critical level. Critical discussion includes your opinion of items mentioned, but also includes the reasons you hold that opinion, and why it may be inconsistent or consistent with what you’ve learned. Justify your reasoning with facts. How does what you’re presenting affect present and future situations? Proper grammar, spelling, and punctuation should be used.

· You may find it helpful to review Data Analytics Lesson 03: Resources (Canvas >>> Modules >>> Data Analytics and Accounting). Videos on charts and formulas can be found within these resources.

· View and verify your submission. Using a Mac or Chromebook to complete this project can create issues.

Learning Objectives:

Students will advance their technical skill working with a data set in Microsoft Excel in an authentic accounting context. Students will analyze this data set in order to answer accounting questions.

Instructions:

1. Go to the Data Analytics and Accounting Module in Canvas to download the Excel file (AIA_ch05_data_and_student_submission_template.xlsx) containing the spreadsheet with select data from most of the DOW 30 companies (excluding some financial and other companies). On the first tab, you’ll see the data needed for this assignment. On the second tab (Ratios- Student Submission) the first four columns of data have been copied over for you. Please use columns E-W to submit your answers.

2. Use the select data (on Tab 1: Data) from most of the DOW 30 companies (excluding some financial and other companies) to calculate liquidity, activity, profitability and coverage ratios. (Reminder, do this on Tab 2: Ratios- Student Submission)

· Do not alter the data set (Tab 1: Data).

· Do not make entries in cells other than those highlighted in light blue on Tab 2: Ratios- Student Submission.

· Do not re-format the cells on Tab 2: Ratios- Student Submission.

· Review your formulas for math signs, parentheses, etc.

Use Tab 3 (Graphs and Short Answers) to submit your graphs and answers to the short answer questions below.

3. Create a 2-D Clustered Column Chart that compares asset turnover ratios. The horizontal axis should list the Company Name rather than numbers. Place your chart starting in cell B2 with the chart area ranging from cell B2 to P31.

· Which types (industry) of companies tend to have higher rates of asset turnover?

· Your answer to this question should be made with reference to and based on an analysis of the chart you created.

4. Use column W (Tab 2: Ratios- Student Submission) to divide inventory/total assets.

· Add this measure to your 2-D Clustered Column Chart.

· What do you notice? Which company doesn’t seem to fit with the others?

· Your answers to these questions should be made with reference to and based on an analysis of the chart you added to in this step.

5. Use column X (Tab 2: Ratios- Student Submission) to divide accounts receivable/total assets.

· Add this measure to your 2-D Clustered Column Chart.

· What trends do you notice among related industries? Does anything surprise you?