Power BI Questions

(Intermediate
Management
Accounting)

Chartered Professional Accountants of Canada, CPA Canada, CPA

are trademarks and/or certification marks of the Chartered Professional Accountants of Canada.
© 2020, Chartered Professional Accountants of Canada. All Rights Reserved.
2025-06-10
Power BI Questions (Intermediate Management Accounting)

Question 1
The Boston Bar Chocolate Company (BBCC) is a privately owned business that takes
pride in the value of its tasty and healthy chocolate bars. The company wholesales its
product to high-end grocery outlets that specialize in gourmet foods. Customers keep
coming back for more of BBCC’s product, not only because of the great taste, but also
because of the growing number of health reports stating that the flavanols in chocolate
help to reduce blood pressure and increase memory retention. This gives the company
a competitive advantage in the premium-quality chocolate market.

The company’s management team is deeply invested in the use of data from a variety
of sources, both internal and external, to be proactive to changing costs in the
manufacturing of its chocolate bars, which are produced in batches of 1,000 bars. This
will assist it in setting selling prices that will attract customer demand while reducing the
risk of loss. It has engaged the use of an outside data analyst to assist in the
development of a tool that can be used to both monitor and predict break-even points.
As a starting point, the data analyst has sketched out the following prototype of a
dashboard that can be used for this analysis:

2 / 16
Power BI Questions (Intermediate Management Accounting)

The following data was extracted from the company’s database as it relates to its
current variable costs per batch of 1,000 chocolate bars and annual fixed costs:
Variable costs
Number of
Cost Cost units in
Cost behaviour classification finished
code Description (V/F) (PR/PE) Cost Per product
LIQ Chocolate liquor V PR $ 5.50 KG 35.0
BUT Cocoa butter V PR $ 6.00 KG 13.3
POW Cocoa powder V PR $ 1.15 KG 7.0
SUG Cane sugar V PR $ 0.70 KG 13.3
EMU Emulsifier V PR $ 0.80 KG 0.6
VAN Vanilla V PR $70.00 LITRE 0.8
WRA Wrapper V PR $80.00 EACH 1.0
DLA Direct labour V PR $32.40 DLH 5.9
PLU Plant utilities V PR $10.73 DLH 5.9
MTC Plant V PR $ 9.60 DLH 5.9
maintenance
COM Commission V PE $61.80 EACH 1
DEL Delivery and V PE $20.40 EACH 1
shipping

Annual fixed costs

FCost FCost
FCost behaviour classification
code FDescription (V/F) (PR/PE) FCost
CTL Quality control F PR $19,500.00
COM Computer and supplies F PR $94,500.00
PEA Plant and equipment F PR $75,000.00
RDE Research and F PR $60,000.00
development
PSA Plant salary F PR $96,250.00
PLE Plant lease F PR $98,000.00
ASA Admin. salary F PE $87,500.00
INT Interest and bank F PE $ 9,000.00
charges
PRO Professional fees F PE $ 6,200.00
ADV Advertising and F PE $28,260.00
promotion
ACM Admin. computer F PE $72,800.00
services

For now, management is satisfied with a tool that uses internal data.

3 / 16
Power BI Questions (Intermediate Management Accounting)

Required:

a) As the data analyst, you are required to develop the prototype into a dashboard
using Power BI. The dashboard will not only inform on costs by behaviour, but also
allow the management team to determine break-even batches based on proposed
selling price per batch.

The process to create the dashboard is broken into the following tasks:
 Task 1: Develop a visualization of total variable costs per unit and a visualization
of total annual fixed costs using the Card function. Steps 1 and 2 — Appendix

 Task 2: Develop a visualization that illustrates the variable costs using a bar
chart and a visualization that illustrates the fixed costs using a bar chart. Steps 3
and 4 — Appendix

 Task 3: Develop a visualization that illustrates the proportion of period and

product costs for fixed costs using a pie chart and a visualization that illustrates
the proportion of period and product costs for variable costs using a pie chart.
Steps 5 and 6 — Appendix

 Task 4: Develop a “what-if” analysis that allows the user to enter a selling price
per batch between $1,200 and $3,000 and predict the break-even point in
batches. This will result in an input field using a slider and a card visualization
that displays the break-even point in batches. Steps 7 and 8 — Appendix

 Task 5: Arrange the components into a dashboard that looks similar to the
prototype.

b) Using the working dashboard, answer the following questions:

i) Report the break-even point at the following selling prices per batch (round up to
the nearest batch):
1. $2,000
2. $2,500
3. $3,000
ii) How does the batches-to-break-even figure change when only the product costs
are taken into consideration? Use the selling prices from part (i). (Hint: Hold
down the CTRL key and click on the Product Cost slice of the pie charts for both
fixed and variable costs. The left sidebar will display results for only product
costs.)
iii) How does the batches-to-break-even figure change if advertising and promotion
costs are eliminated and selling price is $1,500?

4 / 16
Power BI Questions (Intermediate Management Accounting)

c) Ideally, management would like to tie this information into external data to produce
more results in real time and to make more predictions. Provide management with two
ways in which the use of external data can improve this model. Your answer should
discuss the possibilities of using both structured and unstructured external data.

It is important that you save the Power BI file you have completed and save it to your
desktop. You will need it for working on your assignment project.

5 / 16
Power BI Questions (Intermediate Management Accounting)

Appendix to Question 1

The following instructions assist in completing Question 1 using Power BI. If you are
new to Power BI, it may be helpful to view the following tutorials:

 Get data: [Link]

 Create and customize simple visualizations:
[Link]
visuals

These tutorials will also assist with some of the details (formatting, colour, layout, and
so on) that are not explained in the following steps.

This screen print of the Question1-PROBDATA1 Excel file as it displays in Power BI in

Model setting, showing that it consists of two tables with the following table structures.
You should download this file from Brightspace (D2L) to your desktop.

These tables contain the data listed in Question 1.

6 / 16
Power BI Questions (Intermediate Management Accounting)

SCREEN LABELLING CONVENTIONS

The following illustration identifies the naming conventions used to explain the steps required to produce the dashboard
containing multiple visualization.

Visualizations Fields
Tabs Command buttons pane pane

Groups Groups

Visualization
screen

7 / 16
 Power BI Questions (Intermediate Management Accounting)

The tasks given in the Required section of the question are broken into steps. The
following exhibit highlights each of the steps in numbers.

1 3 5

4 6

Getting started:

 Open Power BI and click the Get Data icon in the External data group.

 In the Get Data dialog box, select Excel.

8 / 16
 Power BI Questions (Intermediate Management Accounting)

Find the Question1-PROBDATA file from your files and click “Open”. Select both
tables as follows:

 After clicking Load, the following should be displayed in the Fields pane:

Task 1

Step 1: Prepare card visualization for total variable cost per unit.

 Variable table: Variables calculated based on existing data fields are rarely stored in
a database. As such, the total variable cost per unit (number of units in finished
product × cost) should be calculated.
i. In the Fields pane, click on the three dots beside the Variable table name and
select New Column.

9 / 16
Power BI Questions (Intermediate Management Accounting)

ii. At the top of the visualization screen a formula bar will appear:

Delete “Column =” and type “Total Variable Cost =

'Variable'[Cost]*'Variable'[Number of units in finished product]”.

Resulting Fields pane:

 Click on the Card visualization tool from the Visualizations pane.

 Click on the check box to the left of Total Variable Cost in the Variable table. The
visualization screen should display the sum of the variable cost per unit in the card.

 To format the total variable costs number , click on the Total Variable costs in the
Fields pane, then under Modelling choose $ under Format.
 To create a title on the card, click the card, then click on the Formatting tool in the

Visualizations pane : Click Title to On and click the dropdown to add the title
and set the font size.

10 / 16
Power BI Questions (Intermediate Management Accounting)

Step 2: Prepare card visualization for total annual fixed costs.

 First click outside the box of the newly created card. Click on the Card
visualization tool from the Visualizations pane.
 Click on the check box to the left of ∑ FCost in the Fixed table. The visualization
screen should display the sum of the annual fixed costs in the card. Check that the
format is correct.

Task 2

Step 3: Prepare bar graph for variable costs.

 First click outside the box of the newly created card. Click on the Stacked Column

Chart visualization tool from the Visualizations pane.

 Click on the check boxes in the Variable table in the following order:

11 / 16
Power BI Questions (Intermediate Management Accounting)

 To place the legend to the left of the chart, click on the Format tool in the
Visualizations pane and change Position to Left. (The Format tool gives the user
control over formatting, such as font, colour, labels, and so on. Formatting processes
are user friendly and can be experimented with to achieve the desired effect.)

Step 4: Prepare bar graph for fixed costs.

Use the same procedure as in Step 3 using the Fixed table. Select the data in the
following order:

Task 3

Step 5: Prepare the pie chart for variable costs.

Up to this point, every variable and fixed cost has been presented. These next two
steps provide a visualization indicating a summary of how much of these costs are
product costs and how much are period costs.

12 / 16
Power BI Questions (Intermediate Management Accounting)

 Click on the Pie Chart visualization tool from the Visualizations pane.
 Click on the check boxes in the Variable table in the following order:

 This results in a pie chart showing the portion of total variable costs relating to period
and product costs.

Step 6: Prepare the pie chart for fixed costs.

Use the same procedure as in Step 5 using the Fixed table. Select data in the following
order:

Task 4

Step 7: Capture user input.

The visualizations created so far provide the user with basic information on cost data —
in this case, budgeted figures. As such, they are classified as descriptive visualizations.
Adding a “what-if” analysis adds a predictive element. Let’s say a planning team wants
to determine how a selected selling price affects the break-even point. The following
steps illustrate how data (selling price) can be captured from the user to perform this
analysis.

 In the Modeling tab, click on the New Parameter What If command button.
 When the What-if parameter dialog box appears, fill it in with the following data and
select OK:

13 / 16
Power BI Questions (Intermediate Management Accounting)

 This results in a visualization box that includes the words Selling Price, a box for
user input, and a slider that allows the user to slide to select selling prices between
$1,200 and $3,000. The user input data is captured in a variable called Selling Price
Value. The Fields pane now includes a new table called Selling Price with the
following variables:

This table is not part of the original data. Instead, it is there to capture the Selling
Price Value variable input by the user.

Step 8: Calculate the break-even point.

This step consists of calculating the break-even point given the total fixed and unit
variable costs from the data given in the tables and the user input selling price. Because
the selling price is dynamic, the break-even point should change every time the user

14 / 16
Power BI Questions (Intermediate Management Accounting)

enters a different selling price. All variables required to calculate the break-even point
are available in the Fields pane as follows.

 The first step is to create a measure with a formula that calculates the break-even
point using those variables. In the Fields pane, click on the three dots beside the
Selling price table name and select New Measure.

At the top of the visualization screen a formula bar will appear:

 Delete “Measure =” and type the break-even formula as follows:

BE = SUM('Fixed'[FCost]) / ('Selling price'[Selling price Value] - SUM('Variable'[Total
Variable Cost]))

The Selling price table will now contain the break-even (BE) formula as follows:

 Click on the Card visualization tool from the Visualizations pane. Then select BE
from the Selling price table. The break-even point in units will be shown on the card
and updated every time a different sales price is entered.

Overall, the visualization is designed to be dynamic. Click on the Product Costs section
of either of the pie charts and note the following:

15 / 16
Power BI Questions (Intermediate Management Accounting)

 All period costs on the bar chart become shaded.

 The break-even point in units will be recalculated without the period costs.
Task 5

Arrange the individual items created into a format that is similar as shown in the
question. (Note: You may want to review videos provided at the beginning of this
appendix on formatting to gain knowledge about how to format your visuals).

16 / 16