(Solution) Data Analytics Assignment Question 2

CPA Map Competencies1:

3.4.1 Evaluates sources and drivers of revenue growth

3.5.1 Performs sensitivity analysis

Learning Outcomes:

Analyzes financial and other information in support of pricing decisions (market sensitivity to price)

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)

Applies sensitivity analysis where appropriate and discusses the results and impact on the entity

Performs sensitivity analysis where appropriate and discusses the results and impact on the entity

Calculates cost-volume profit (CVP), undertakes a sensitivity analysis for a given entity, assesses the implications for profitability, and assesses the impact on operational decisions and strategic goals

Background

Canadian Cycles Corporation (“CCC”) is a bicycle manufacturer based on Ontario, Canada who specializes in selling a wide variety of bikes to retailers in Canada and Germany. CCC plans to introduce two of their existing bike products into the US market upon expansion.

Today is July 4th, 2022, and you, a Financial Controller CPA at CCC who has led the initial preparation stages of this market expansion, have been tasked by the “US Market” sub-committee to perform a cost-volume-profit (CVP) analysis as well as a sensitivity on their operations.

Information Available

You have access to sales data from January to June for the 2022 fiscal year. Refer to the Question 2 Dataset spreadsheet for detailed sales data related to the 2022 fiscal year.

With the efforts of the sub-committee and market analysts, you have gathered the following additional information:

The US Market Sub-Committee has decided to introduce the Touring Bike and Road Bike into the US market
CCC’s market analysts indicate that every $250 change in the price results in a 100 unit change in the units sold.
CCC’s market analysts indicate that every $100 change in the price results in a 200 unit change in the units sold.
CCC owns a variety of machines for manufacturing. These machines were purchased in 2015 and cost (in total) $3,000,000. These machines have a useful life of 10 years and are depreciated on a straight-line basis.
Other information:

Costs	Amount (Dollars)
Fixed cost of goods sold	$ 200,000
Advertising	$ 100,000
Management salaries	$ 700,000
Other miscellaneous fixed costs	$ 50,000

CCC’s tax rate is 40%
The partial income statement of one of the US competitors:

United Cycles Limited (UCL)
Partial Income Statement
For the Period Jan 1 – June 30, 2022
		Amounts ($)
Sales		$ 8,100,000
Cost of Goods sold		$ 3,200,000
Contribution Margin		$ 4,900,000
Other costs
Fixed expenses	$ 500,000
Advertising	$ 600,000
Administrative Salaries	$ 1,200,000
Depreciation	$ 700,000
Other fixed costs	$ 15,000	$ 3,015,000
Operating Income		$ 1,885,000

Required

Part 1: Cost-Value-Profit (CVP) Analysis

The sub-committee wishes to see how CCC stacks up against the current US market. Due to this, you have decided to compare CCC to United Cycles Limited (UCL) at this time of the year. Create a partial income statement for CCC from January 1 to June 30, 2022. Comment on the different cost structures of the two companies.
The sub-committee believes that profits should be higher than they currently are with the number of units they have sold. Calculate CCC’s break-even point in dollars and units sold (round dollars to 2 decimal places and units sold to the nearest whole number). How does this affect CCC’s ability to make profit?
CCC has a target annual operating income of $1,600,000 after tax. Calculate the required sales amount in dollars and units sold. Do you think this is achievable? Explain your reasoning on why it is or is not.
The sub-committee would like to know how CCC would perform in periods of varying demand compared to its competitors. Determine and compare the degree of operating leverage for CCC and UCL at this time of the year. If the demand for bikes were to increase, who would be better off? Explain your reasoning.

Part 2: Sensitivity Analysis

Perform a sensitivity analysis on the contribution margins for the Touring Bike and Road Bike.

[Hint: Use Excel Data Tables]

Prepare a brief memo to the sub-committee explaining the price(s) you believe CCC should sell their Touring and Road Bikes in the US market at. Justify your decision using your analysis and other case facts that you have gathered. State any assumptions you have made in your analysis.

Solution

Part 1:

a) The fixed costs of CCC are much lower with about 1 million than the fixed costs of UCL of 3 million.

The cost of goods sold for CCC is higher at about 5 million compared to about 3 million for UCL.

The sales of both companies are almost equal and with their different cost structures costing about the same they both have an operating income of about 2 million with CCC having slightly more income.

b) Break-even point calculation:

$1,050,000 total fixed costs / ($3,102,800 contribution margin / 4509 units sold) = 1526 units needed

$8,121,700 sales / 4509 units sold = $1801.22 average unit cost

..Please click on the Icon below to purchase the full answer at only $5