(Solution) MOS 4471 Data analytics assignment Question 1

CPA Map Competencies1:

Documents and assesses business processes, systems and data requirements and recommends improvements to meet information needs

Identifies ethical and privacy issues related to information technology and its use

Evaluates sources and drivers of revenue growth

Text Box: Learning Outcomes:

a) Explains potential solutions that may be available to meet information needs including potential IT solutions

b) Identifies the impact of IT on an organization’s decision-making and performance

c) Evaluates ability of suggested information technology (IT) solutions to address management information requirements

d) Identifies and evaluates decision criteria for alternative IT solutions

e) Evaluates alternatives for addressing gaps in information or for generating improvements to existing management information, and makes specific recommendations

f) Addresses IT ethical and privacy issues by recommending an appropriate course of action

g) Applies relevant analytical techniques and makes recommendations to improve revenue growth (e.g., pricing strategy, product and product-mix strategies, and distribution strategies, outsourcing, business alliances or other arrangements

Background

Custom Canadian Cycles (CCC) is a bike manufacturer based in Ontario, Canada who specializes in selling custom bikes alongside a variety of bike accessories and safety equipment. CCC sells bikes in both Canada and Germany.

Today is October 30th, 2022, and you, a Financial Controller at CCC, have been tasked with spearheading CCC’s expansion into the United States market in fiscal 2023. CCC has put together a US Expansion sub-committee to determine the logistics surrounding this expansion.

From your company’s database, you have gathered sales data related to the past eight years (2015 – 2022), However, you know that this information alone is not sufficient for decision making and needs to be organized so that you may gain insight into what a successful expansion will look like.

Based on your preliminary discussions with your colleagues, you have been able to learn the following:

CCC wishes to introduce two of their existing bike models in the US within the first year. Introducing the other bike models in subsequent years.
Projected sales figures in Germany have not been achieved as of 2022. CCC suspects that the differences between the Canadian & German markets are to blame for this.
CCC’s Information security systems have not been updated in the past 5 years. Potentially resulting in some security issues within their database.
There have been numerous reports from CCC’s accounts receivable team involving transactions in their systems missing key sales data. These issues occurred for both Canadian and German sales.

Information Available

You have access to CCC’s sales data from 2015 to 2022. This sales data contains various sales information generated by their enterprise resource planning software ranging from order numbers to profit margins per sale.

Refer to the Question 1 Dataset spreadsheet for a detailed view sales data from the years 2015 to 2022.

Required

Part 1: Data Cleaning & Preparation

One of the core ideas behind the accounting profession is helping people make decisions. In the case of management accounting, managers need to have relevant information that allows them to achieve their company’s strategic and operational objectives. Raw data will not allow managers to make effective decisions, and therefore, requires “cleaning” to maximize the effectiveness of their decision making.

Examine the provided dataset. Identify data that is not likely to be relevant in your analysis and anything that presents ethical/privacy issues (Think about what data may be most relevant to the US market). Remove this data from your data set.

[Hint: Examine the data labels]

Due to issues with CCC’s systems, certain transactions were not recorded correctly and were displayed in their database as blank cells. Remove the rows of transactions with incomplete information.

[Hint: find blank cells using the “Go to Special” tool in Excel]

Using PivotTables, create visual representations of total unit sales and profit margin for each category of bike sold by CCC.

[Hint: Use two PivotTables to create two separate graphs]

Part 2: Memorandum to Management Regarding Market Expansion Analysis

Based on your analysis performed in Part 1, prepare a memo to the sub-committee discussing:

The data you used to come to your decision
Any issues you have found with the sales data (i.e. Security issues, administrative, etc.)
Your analysis on which bikes should be sold to the US market.
Which two bikes should be introduced to the US market.

The sub-committee may wish to present your case to shareholders, so be as concise as possible and state any assumptions that you have made in your analysis.