Core 2 — Integrated Problem 5

Scenario (120 minutes)

Power-Ride Inc. is a privately owned business in Western Canada that has found
success with the production of two-wheeled, self-balancing personal transportation
vehicles called Ride-Outs. The popularity of two-wheeled personal transportation
vehicles is continuing to grow in both the private consumer and commercial markets.
Two-wheeled personal transportation vehicles give people the option to use battery
power as an alternative to cycling or walking.

Power-Ride was founded eight years ago by two siblings, Ryan and Nick, who love the
outdoors. Ryan suffers from severe asthma and had to significantly reduce his outdoor
activities — until he discovered two-wheeled personal transportation vehicles during a
European vacation. He is very passionate about the business. Given that the business
is mostly seasonal, growth is heavily dependent on the weather; however, the company
is seeing an overall steady growth pattern.

It is time to start preparing next fiscal year’s budget for presentation to the board.

You, CPA, are the controller for Power-Ride and have been asked by the owners to
help prepare the documents required for the budget.

Task #1

Ride-Out sales are highest in the spring and summer months, when private consumer
purchases peak. The most profitable months are always March to May, when outdoor
adventure stores are preparing for the summer season.

The first nine months of this year have been difficult. Due to poor weather, monthly
sales have dipped 4% over the prior three-year average, and this decrease is reflected
in the estimate for the remaining three months. Fortunately, there is no indication that
the bad weather will repeat into the upcoming year.

© Chartered Professional Accountants of Canada. All rights reserved.

No part of this publication may be reproduced or transmitted, in any form or by any means, without the prior written consent of CPA Canada.
For information regarding permissions, please contact permissions@cpacanada.ca.
2023-08-25
Core 2 — Integrated Problem 5 Problem

You have obtained the following sales data for the past nine months, as well as three
months of forecasted data:

Month Units sold

January 74
February 90
March 210
April 240
May 255
June 205
July 180
August 160
September 90
October (forecast) 85
November (forecast) 70
December (forecast) 65

Power-Ride had a one-time special order in April for 15 Ride-Outs that is not expected
to occur this upcoming year. One of Power-Ride’s retailers, Outfitters Inc., recently
closed. It accounted for 5% of regular sales for January to March, and 8% of regular
sales for the remainder of the year. The Ride-Outs currently sell for $1,400 per unit, and
the company is planning to increase the selling price by 5% for the coming year.

Market research shows that two-wheeled personal transportation vehicles are gaining
popularity with those who live in hilly or windy areas and those with reduced fitness
levels. As a result, experts are predicting that sales growth in this market will be at least
6% in the coming year. Power-Ride requires inventories of finished goods on hand at
the end of each month to be equal to 50% of the following month’s budgeted sales.
Power-Ride expects to have 20 units on hand on this December 31 (at year end).

Prepare a monthly sales budget in both units and dollars for the next fiscal year. In
addition, prepare a budget in units to forecast required monthly production to meet
expected sales.

Your response should be no longer than half a page, excluding any Excel files.

2 / 17
Core 2 — Integrated Problem 5 Problem

Task #2

You have completed the budgets for the production inputs for next year, summarized
below.

January February March April May June

Direct materials $30,624 $50,945 $75,547 $76,000 $63,230 $50,584
Direct labour $30,019 $49,940 $74,055 $74,499 $61,981 $49,585
Manufacturing overhead $12,615 $20,986 $31,120 $31,307 $26,046 $20,837

July August September October November December

Direct materials $41,678 $32,772 $29,923 $27,608 $23,867 $22,528
Direct labour $40,855 $32,126 $29,332 $27,062 $23,395 $22,083
Manufacturing overhead $17,169 $13,500 $12,326 $11,372 $ 9,832 $ 9,280

The cost per unit of the opening inventory in January is expected to be $705 per unit.

Monthly inventory is costed at the average annual manufacturing costs for the year
based on the required production level, and any over- or under amounts are included in
the cost of goods sold for the month.

You were also provided with the following to use for the selling and administrative
budget.

Prior-year monthly expense information and assumptions:

Shipping costs $45 per unit (no expected change)
Wages and salaries $20,000 expected to increase by 3% inflation only
at the beginning of the year and then remain
constant
Depreciation $8,200 expected to stay the same
Utilities $7,100 expected to increase by 3% in February
and then remain constant
Insurance $4,100 expected to increase by 2% on renewal in April
and then remain constant
Miscellaneous $2,900 expected to stay the same

a) Prepare the monthly cost of goods sold budget for the next fiscal year.
b) Prepare the monthly selling and administrative expenses budget for next fiscal year.
c) Prepare the annual budgeted net income statement for next fiscal year, ignoring
income taxes based upon the information provided.

Your response should be no longer than half a page, excluding any Excel files.

3 / 17
Core 2 — Integrated Problem 5 Problem

Task #3

Third-quarter results are in, and cost of goods sold is higher than anticipated. A direct
material that had some variance in the quarter is the aluminum used in the Ride-Outs.
You have decided to look at the flexible budget direct material variance related to this,
as well as the flexible budget direct labour variance for all production. You have the
following information to perform your analysis:

Standard inputs Standard cost

for one unit of output for one unit of input
Direct materials 10.00 kg $32/kg
Direct labour 8.00 hours $42/hour

Actual performance for the company is shown below:

Actual output (in units): 1,371

Direct materials:
Purchased and used 13,750 kg
Actual price per input $39.40/kg

Direct labour:
Labour hours of input 11,500 hours
Actual price per hour $43.10/hour

Calculate the flexible budget, rate, and efficiency variances for the direct materials and
direct labour, and evaluate the results.

Your response should be no longer than half a page, excluding any Excel files.

4 / 17
Core 2 — Integrated Problem 5 Problem

Task #4

In order to offset the higher cost of aluminum, the board wants to look at the motor
suppliers to see if material costs can be reduced in this area. We are currently using a
local supplier, Alpha, with a cost of $130 per unit. Use Power BI to provide analysis of
the supplier options to present to the board.

The board would like you to do the following:

i. Create a card visualization of how many suppliers there are.
ii. Create a map visualization of the supplier locations.
iii. Create a dashboard, including a table, chart, and slicer, showing the total costs
per supplier.
Submit your response by following the steps outlined in the Appendix and providing
screenshots from Power BI.

Provide a recommendation to the board on the supplier with the lowest total cost.
Compare this recommendation to the current supplier.

Hints:
• Your visualization has to be selected before you can work on it. To select your
visualization, simply click on it in Power BI.
• Using Microsoft’s Snipping Tool, you can easily capture an image of the specific
visualization requested. Copy the visualizations and paste them into your memo.

5 / 17
Core 2 — Integrated Problem 5 Problem

Appendix
Step 1:

Open Power BI and import the Excel file “CO2-2401E-IP05.Data.xlsx” using the Get
Data function. Use the screenshots below to guide you through the steps.

1. Select Get data

and Connect or
use the drop-
down list.

6 / 17
Core 2 — Integrated Problem 5 Problem

2. Select Excel workbook, then

find your “CO2-2401E-
IP05.Data.xlsx” file and double
click or open.

3. Select the Suppliers

workbook and click the
Note: You should see a preview of
checkbox.
the data that looks like this.

4. Select Load.

7 / 17
Core 2 — Integrated Problem 5 Problem

Step 2:

The board wants to know how many suppliers there are. Using the instructions below,
determine how many suppliers Power-Ride has by creating a visualization using the
Card function and the Supplier field.

1. Select the card icon.

2. Select Supplier.

3. First Supplier will automatically be

populated; use the drop-down arrow
to select Count (Distinct).

Submission: Take a screenshot of your card and include it as your response to

Task #4-i.

8 / 17
 Core 2 — Integrated Problem 5 Problem

Step 3:

The board would also like to know where the suppliers are located. Create a new sheet
(tab at bottom). Then, following the instructions below, prepare a map to show where
the suppliers are located.

1. Select the globe icon (the

map tool). Ensure you do not
select the filled map icon.

2. Select Location under Suppliers; it should autofill

to the Legend field. Drag and drop Location to the
Location field.

9 / 17
Core 2 — Integrated Problem 5 Problem

3. Select the paint brush icon (the

formatting tool); choose Category
labels and turn on.

You should now have a visual representation of where the suppliers are located.

Submission: Take a screenshot of your map, and include it as your response to

Task #4-ii.

Step 4:

As the board is most concerned with cost, it would like to know the cost information for
each supplier and the lowest-cost option. Create a new sheet (tab at bottom). Then,
using the steps below, create a dashboard on the new sheet using tables and graphs to
determine and visualize the lowest-cost option and the total cost per supplier.

10 / 17
 Core 2 — Integrated Problem 5 Problem

1. Select the table icon.

2. Select Supplier, Location, ∑ Unit Motor Costs,

and ∑ Unit Shipping Costs. You may need to resize
your table on your sheet to show all data. Drag the
lower right-hand corner of the table to make it larger.

Hint: Try selecting the values in a different order.

Note that the order in which you select them will
impact how they are presented. In some cases,
specific ordering may be important.

3. Select New column from the Modeling tab. A formula

bar will appear in which you can enter custom formulas.
This will also create a new field in your Suppliers table.

4. Type in this formula to add a column with the

total costs as shown (fields will start to auto-
populate). By changing “Column =” to “Total Cost =”
the name of the New Column also gets updated in
the table.

11 / 17
Core 2 — Integrated Problem 5 Problem

5. Total Cost is now a new field, and you

can add it to your table by selecting it.

12 / 17
Core 2 — Integrated Problem 5 Problem

6. Select the paint brush icon (formatting).

Select Column Headers, and change text size to 16.

Select Values, and change text size to 16.

Sort the table by total cost. Select the arrow under the Total
Cost heading column to sort from highest to lowest.

You may need to resize and reposition your table. You can
resize by clicking and dragging a corner. You can move the
chart by clicking on it and moving it with your mouse button
held down.

7. Keep the paint brush icon (formatting)

selected.

Under Totals, turn off Values.

Power BI will automatically total, and

sometimes it does not work with the data
provided. You should always consider whether
it may be appropriate to turn this function off.

Now that your table is complete, follow the instructions below to present this information
in a clustered column chart on the same sheet so that there is a visual representation of
the data.

13 / 17
Core 2 — Integrated Problem 5 Problem

8. Maintain the table and add a new

visualization to this sheet by clicking on a blank
area of the sheet and then selecting the
clustered column chart icon.

9. Under the Suppliers field, select

Total Cost first, then Supplier, to
ensure that the total cost is your
vertical axis and the supplier is
your horizontal axis.

14 / 17
Core 2 — Integrated Problem 5 Problem

10. Select the paint brush icon

(formatting). Select X-Axis, and change
text size to 16.

Turn on Title to access the formatting

options, and repeat this step for the title.

Repeat these steps for the Y-Axis.

15 / 17
Core 2 — Integrated Problem 5 Problem

11. Select the magnifying glass icon

(analytics tool). Select Min line and Add
line. You may also need to change the
colour so that the line is visible.

Follow the instructions below to add a total cost slicer to the sheet so that you can
manipulate the data.

12. Maintain the current visualizations and

add a new visualization to this sheet by
clicking on a blank area of the sheet and then
selecting the slicer visualization. Then select
Total Cost. You may need to drag this from
the Suppliers section to the Field section.

13. Click on the arrow in the upper right-hand

corner of the slicer (“Select the type of slicer”
will appear). Choose Between.

The purpose of a slicer is to allow you to filter

data. Now that the slicer is in place, try sliding
the ends of the slicer back and forth and see
how it changes your dashboard.

Now that you have your table, graph, and slicer, try moving them around your
worksheet to determine the best way to present them.

16 / 17
Core 2 — Integrated Problem 5 Problem

Submission: Take a screenshot of your dashboard (make sure that you set your slicer
to the maximum and minimum points so that the full range of prices is displayed), and
include it as your response to Task #4-iii.

You have now finished your visual presentation for the board.

17 / 17