Intermediate Accounting I – MOS 3360B – Winter 2023
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
DAN Department of Management and Organizational Studies
Intermediate Accounting I – MOS 3360B – Winter 2023
Assignment – Part A – Data Analytics
Introduction
This assignment will require you to analyze raw data using data analytic techniques and to provide insights to the company based on your analysis. The specific requirements to complete the assignment are outlined in Appendix A to this document and within the Part A Excel Working File provided. Please review these requirements carefully to ensure you have the best chance of success.
Students may complete this assignment individually or with one other student. If you choose to work with another student, only one student needs to submit the report.
Part A Deliverables
• All submissions should be completed in the Excel workbook provided, MOS 3360 Part B Working File 2023. No other files or formats will be accepted.
• Include your student name, student number, UWO email, and section number in the space provided within the working file. If you are working in groups, include this information for both students.
• When answering the questions in the working file, responses should be well written, complete, concise and easy to understand. Complete sentences and proper grammar should be used.
• Rename the working file in the following format prior to submission:
MOS 3360 Part A_Last Name_First Name (of the student submitting the file)
(example: MOS 3360 Part A_Smith_Jane)
• Submit your completed working file (Excel workbook) through the “Assignment” tab on the OWL course site by the due date for Part A. E-mailed responses will not be accepted.
Academic Honesty and Integrity
Students are to complete this assignment on their own or in groups of two. The work completed should reflect your own work and be in your own words. Please be aware that working with others (outside of your group) and using material provided by other individuals will be considered cheating. You may refer to YouTube videos to assist you in completing this part of the assignment.
Cheating and plagiarism are serious academic offences and will not be tolerated. Students that cheat or plagiarize will receive a grade of zero (0%) on this assignment and may face further academic penalties, such as suspension from the University or expulsion. Western’s code of conduct will be enforced. Students are referred to the university policy on scholastic offences:
https://www.uwo.ca/univsec/pdf/academic_policies/appeals/scholastic_discipline_undergrad.pdf
Part A Due Date
The completed working file for Part A of the assignment is due online on Friday, March 31, 2023, at 5:00 pm. Working files received after this due date will receive a grade of zero (0).
Remember: both parts of the assignment must be completed to receive a passing grade in this course.
Appendix A – Data Analytics Case
Necesspresso is a Canadian reseller of coffee pods for expresso machines. The company’s products include five coffee flavours based upon the location of the coffee beans (Columbia, Costa Rica, Ethiopia, Mexico and Peru). The company is experiencing great growth with increasing revenue in the past few years and is led by its charismatic CEO, Tim Clooney, who stars in company commercials. During the year, due to geopolitical tensions, coffee consumers in North America began to boycott coffees from certain regions, including two coffee blends offered by Necesspresso, (Mexico and Costa Rica).
You are a newly hired financial analyst and will assist Tim in better understanding the company’s figures as well as supporting financial reporting. The company recently completed its 2022 year-end and Tim would like to better understand its results. You have downloaded sales and cost data from the organizations financial reporting system for the years 2020, 2021 and 2022. You are confident that the data provided by the system is correct (Clean Data worksheet in the Excel Working File).
Tim states “I’ve been so busy trying to get products out the door this past year, I haven’t had a chance to really see the big picture. I need to understand where our sales are coming from, if we really are growing, and if there are any significant issues.”
You have settled on performing the following analysis; however, other beneficial analysis that will provide insights may be added.
A. Summarize the Sales, considering products and customers.
i. Create a pivot table and chart summarizing Revenue by Product.
ii. Create a separate pivot table and chart summarizing Revenue by Customer.
B. Provide a visualization summarizing sales by product and customer.
i. Create a pivot table and chart summarizing Revenue by Customer and Product.
C. Create a visualization by month for the 2022 year.
i. Create a pivot table and chart summarizing sales by revenue dollars each month.
ii. Create a pivot table and chart summarizing sales by quantity each month.
D. Analyze gross margins for the fiscal year.
i. Create a pivot table calculating gross margin (in dollars) each month for the fiscal year.
ii. Create a pivot table and chart summarizing selling prices for products each month for the fiscal year.
E. Create a pivot table comparing product Revenue and Cost bases during December 2022.
F. Using Pivot Tables, perform a “Rate / Volume” analysis comparing revenue from 2021 to 2022.
G. Perform any other analysis you believe will provide her with further insights.
These questions and further information are found in the subsequent worksheets of the Working File 2023. Answer the specific questions in the area provided within the worksheets.
For assistance with Excel functionality, refer to Lesson 3 in the Data Analytics Module. Short 1-2 minute videos are provided on various functions of Excel.
For assistance with pivot tables and charts, you may wish to refer to this YouTube video:Introduction to Pivot Tables and Charts. Other YouTube videos may be used as a reference to complete this part of the assignment.
2023-04-03