MGMT8575

Final Project Instructions Group Project (25%)

January 2025
 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING
Assignment Objective
This group project is designed to give you a hands-on opportunity to apply the knowledge and skills you've
acquired throughout the semester. You will be working through the five essential steps of the data analysis
process integrating three (3) new quarters of data for our company, Edge Communications into the HR-Report
you created in Assignments 1 and 2.

This allows you to combine new information with existing data, enabling you to analyze all quarters
comprehensively. By doing so, you'll be able to identify patterns, trends and, provide recommendations. You
will take this information, create an appealing and strategic Power BI Dashboard as well as record an insightful
7-10 minute video presentation for senior leaders.

Assignment & Software Requirements

 Groups of 4 – 6 students maximum.
 The assignment has individual deliverables as well as group deliverables
 Each student must submit their own upgraded HR-Workbook (including any corrections that need to be
made based on instructor feedback from Assignment #2).
 One Microsoft Power BI storyboard document on behalf of the group, demonstrating key metrics over
time that would be of interest to Senior Leaders.
 One 7-10 minute Recorded presentation to Senior Leaders highlighting key metrics, analysis, trends
and recommendations. This presentation is to be recorded in Zoom, with cameras on, and
accompanied by a slide deck.
 The objective of this presentation is to provide Senior Leaders with an overview of relevant KPI’s,
concerns, opportunities and recommendations.
 Use Microsoft Excel on a Windows-based PC for optimal functionality as PowerQuery has limitations
on macOS. Conestoga College computers have the required Excel version if needed and you can also
utilize Excel via Office 365 on the Conestoga IT site.
 Academic integrity will be enforced.

Scenario
You and your HR team at Edge Communications have collected meaningful data and designed an excellent
Power Query and report for the organization. Now, you and your team can integrate three new bi-annual sets
of data into the system providing you with further insight into the organization’s workforce.

To capitalize on this insight, you have scheduled a meeting with the CEO and Senior Leadership Team to paint a
picture of the current workforce, concerns, opportunities and recommendations. Within this meeting, you will
display the Microsoft Power BI dashboard you’ve created highlighting relevant KPI’s or metrics and discuss
recommendations based on the analytics you have completed.

When designing your dashboard and presentation, think about what is relevant to Senior Leadership. What
would they care about? How is each location doing in comparison to each other? What about on the national
side? Did the recommendations you made in Assignment #2 (i.e. the Absenteeism exercise) make any impacts?

January 2025 Page 1 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING
Compare 2024-Q2 to Q4. Compare 2024-Q2 to the same quarter in 2025. Compare the Q4’s together. Are
there seasonal trends? Annual trends?

Within your recommendations, include how you would create an employee survey to gain qualitative insight
into your current metrics. Design 5 written response questions you would include in this survey and what you
hope to gain from these questions.

Assignment Instructions
Building off the HR-Report designed in Assignment #2, students are to update their workbooks with any
corrections required as per faculty feedback. Once the file is up-to-date, students will utilize the PowerQuery
and add more detail to the report – specifically three new data sets. Once this data has been included,
students will create a Power BI dashboard and record a 7 – 10-minute presentation to leaders.

Assignment Submission Requirements

 Each individual student must upload their own corrected, upgraded, and completed LAST NAME, FIRST
NAME [Link] to the submission folder.

 One Microsoft Power BI storyboard document on behalf of the group, demonstrating key metrics over
time that would be of interest to Senior Leaders – including longitudinal metrics and snapshot metrics
from the final biannual report (Q4 2025).

 Record a 7-to-10-minute group Presentation to Senior Leaders highlighting key metrics, analysis,
trends and recommendations. This presentation is to be recorded in Zoom, with cameras on, and
accompanied by a PowerPoint slide deck and Power BI Dashboard.

Evaluation Criteria (25%)

Review the rubric in eConestoga which details grading breakdown.

January 2025 Page 2 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING

Contents
Folder and File Setup (Windows Explorer)........................................................................................................ 4
HR-Report: Implementing Assignment #2 Feedback (Excel & PowerQuery).................................................................4
Preparing Longitudinal Workforce Reports (Excel & Tables)..............................................................................4
EmployeeDatabaseKPIs.................................................................................................................................................4
EmployeeAbsenteeismKPIs...........................................................................................................................................8
Tracking Additional KPIs Longitudinally.......................................................................................................................11
Updating the EmployeeDatabase Query (Removing Turnover from Previous Fiscal Year)...............................12
Loading New (2024-Q4) Data & Updating KPI Tables (PowerQuery & Excel)...................................................17
Loading New (2025-Q2 and 2025-Q4) Data & Updating KPI Tables (PowerQuery & Excel).............................22
Finalizing your HR-Report Workbook for Grading............................................................................................24
Create a Microsoft Power BI Dashboard..........................................................................................................24
Record & upload a 7 – 10-minute Presentation...............................................................................................24

January 2025 Page 3 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING
Folder and File Setup (Windows Explorer)
1) Download all assignment files from eConestoga into the folder you created in Assignment #1
a) Assignment #3 Instructions
b) Three (3) new Bi-annual Reports
i) 2024-Q4
ii) 2025-Q2
iii) 2025-Q4

HR-Report: Implementing Assignment #2 Feedback (Excel &

PowerQuery)

2) Review Assignment #2 feedback from your Conestoga Professor and make any corrections/updates
required to bring your “HR-Report” Excel workbook up to your CHRO’s standards.
a) Contact your Conestoga Professor for support with any questions/concerns you have regarding their
Assignment #2 feedback.

Preparing Longitudinal Workforce Reports (Excel & Tables)

Before you load the 2024-Q4 dataset into your HR Report, you need to upgrade your “HR Report” workbook
with a new worksheet called “KPITracking” containing new tables designed to record/store key biannual KPI
metrics.
3) Add a new worksheet to your “HR Report” Excel workbook, name the new sheet “KPITracking”, and
drag/drop the new sheet (ie. tab) so that it falls beside the “Canada-QuarterlyFinancial” sheet (see below).

4) In order to track certain KPIs longitudinally (ie. through the passing of time, from report to report, etc.) you
must first build a reporting tool to help you consolidate desired metrics so that they are easy to copy into a
table that grows longer with each new biannual report.

EmployeeDatabaseKPIs
You will start by creating a reporting tool that will help you consolidate desired metrics from your
“EmployeeDatabase” worksheet and its dashboard:
a) Navigate to the new “KPITracking” worksheet and – from A1:A15 – create a list of all fourteen (14)
“EmployeeDatabase KPIs” calculated in your “EmployeeDatabase” worksheet dashboard:

i) EmployeeDatabase KPIs

ii) Total Headcount

iii) Total FTEs

January 2025 Page 4 of 25

MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING
iv) FTEs (Toronto)

v) FTEs (Montreal)

vi) FTEs (Vancouver)

vii) FTEs (Winnipeg)

viii) Hires (YTD)

ix) Exits (YTD)

x) Salaries (YTD)

xi) Salary (Avg)

xii) Salary (Med)

xiii) Benefits (YTD)

xiv) HCROI

xv) Profit Per EE

b) In cell B1, enter the same equation used in cell A1 of your “EmployeeDatabase” worksheet to reliably
display the Year and Quarter for the metics that will be reported in the cells below.

i) B1  =QuarterlyReportSpecifications[Year-Quarter]&" Workforce Report"

NOTE: You will reuse this exact same column header equation for all additional longitudinal
reporting tools (and their associated tables) to track various desired metrics longitudinally. Using
the same column header for all metrics measured during the same [Year-Quarter] report will make
it much easier to identify and compare your longitudinal metrics.

c) From cells B2:B15, enter equations that will effectively report each EmployeeDatabase dashboard
metric listed in cells A2:A15.

NOTE: Not all metrics from your “EmployeeDatabase” can be referenced directly since they sometimes
combine a calculated metric and concatenated text. When we track metrics over time, we only want
the calculated metric without the concatenated text.

i) B2  =COUNTIF(EmployeeDatabase[End Date],"")

NOTE: This is an example of an equation that needs to be redrafted to produce the desired metric
since the source cell for the metric (C6 in the “EmployeeDatabase” sheet) contains concatenated
text along with the desired metric.

January 2025 Page 5 of 25

MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING
ii) B3  ='Canada-QuarterlyFinancial'!H18

NOTE: This is an example of an equation that directly references the desired metric since the
source cell for the metric (H18 in the “Canada-QuarterlyFinancial” sheet) contains nothing except
the desired metric.

iii) B4  =COUNTIFS(EmployeeDatabase[Location],"Toronto",EmployeeDatabase[End Date],"")

iv) B5  =COUNTIFS(EmployeeDatabase[Location],"Montreal",EmployeeDatabase[End Date],"")

v) B6  =COUNTIFS(EmployeeDatabase[Location],"Vancouver",EmployeeDatabase[End Date],"")

vi) B7  =COUNTIFS(EmployeeDatabase[Location],"Winnipeg",EmployeeDatabase[End Date],"")

vii) B8  =COUNTIF(EmployeeDatabase[Hire Date],">="&QuarterlyReportSpecifications[Period Start

Date])

viii) B9  =COUNTIFS(EmployeeDatabase[End Date],"<="&QuarterlyReportSpecifications[Period

End Date],EmployeeDatabase[End Date],">="&QuarterlyReportSpecifications[Period Start Date])

ix) B10  =EmployeeDatabase!O3

x) B11  =EmployeeDatabase!O4

xi) B12  =EmployeeDatabase!O5

xii) B13  =EmployeeDatabase!O6

xiii) B14  =EmployeeDatabase!O7

xiv)B15  =EmployeeDatabase!O8

OPPORTUNITY: Please feel encouraged to add any additional metrics you wish to derive from the
“EmployeeDatabase” worksheet (beyond the 14 KPIs listed above) since there are many additional
KPIs you could choose to track from that worksheet (one very good choice would be to add “YTD
Bonus Payments” since you already have both salary and benefits tracked!). You will be building
additional “EmployeeAbsenteeism” and “Canada-QuarterlyFinancial” reporting tools and
longitudinal tables so only focus on additional KPIs from the “EmployeeDatabase” worksheet that
you wish to track. The choice is up to you and your group whether you only want the mandatory 14
KPIs listed above.

d) Review your consolidated metrics and apply the correct data type and decimal place specification to
each metric (as appropriate).

e) Bold the text in cells A1 and B1 to help distinguish the column headers of your unstructured ranges
since you will not be turning your reporting tools/helpers into tables in this rare instance.

January 2025 Page 6 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING

5) Now that you have consolidated your “EmployeeDatabase” sheet dashboard metrics into a reporting tool
that puts all of those metrics neatly into a two column range, it is time to create your first longitudinal KPI
tracking table: the “EmployeeDatabaseKPI” table.

a) Highlight the A1:B15 data range (including any additional “EmployeeDatabase” KPIs you chose to
calculate and track), copy the range to your clipboard, select cell F1, and paste as Values and Number
Formats.

b) Select the new range of data F1:G15 (if it’s not still selected) and press CTRL+T to transform the
unstructured range into a table (make sure you indicate that your new table has headers).

January 2025 Page 7 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING

i) Name your new table “EmployeeDatabaseKPIs”

c) Review your copied metrics and make sure the correct data type and decimal place specification is
applied to each metric (as appropriate).

EmployeeAbsenteeismKPIs
Next, you create a reporting tool that will help you consolidate desired metrics from your
“EmployeeAbsenteeism” worksheet and dashboard:
6) Navigate to your “KPITracking” sheet and:

a) type “EmployeeAbsenteeism KPIs” into cell A19.

b) In cell B19, enter the same equation used in cell A1 of your “EmployeeDatabase” worksheet to reliably
display the Year and Quarter for the metics that will be reported in the cells below.

January 2025 Page 8 of 25

MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING
i) B19  =QuarterlyReportSpecifications[Year-Quarter]&" Workforce Report"

NOTE: You will reuse this exact same column header equation for all additional longitudinal
reporting tools (and their associated tables) to track various desired metrics longitudinally. Using
the same column header for all metrics measured during the same [Year-Quarter] report will make
it much easier to identify and compare your longitudinal metrics.

c) From A20 downwards – create a list of all the “EmployeeAbsenteeism KPIs” calculated in your
“EmployeeAbsenteeism” worksheet dashboard that you want to track each biannual report (you can
track all of your absenteeism KPIs or just those you feel are important to the organization – it’s up to
you to decide).

d) From B20 downwards – add equations that effectively report the “EmployeeAbsenteeism KPIs” you
chose to list from A20 downwards.

NOTE: If you choose to track absenteeism KPIs that were not already calculated in your
“EmployeeAbsenteeism” worksheet and its dashboard then you will need to develop those equations
yourself directly. If you are having difficulty creating equations, search the internet or reach out to your
professor for guidance/support.

e) Review your consolidated EmployeeAbsenteeism KPIs and apply the correct data type and decimal
place specification to each metric (as appropriate).

f) Bold the text in cells A19 and B19 to help identify the column header row for this new unstructured
range of data (reporting tool)

NOTE: The screenshot below provides one example of what could be easily tracked using the KPIs that
are already calculated in the “EmployeeAbsenteeism” worksheet but you may choose to track different
KPIs depending on what you deem important.

January 2025 Page 9 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING

7) Now that you have consolidated all of the metrics you want to record/track from your
“EmployeeAbsenteeism” sheet, it is time to create your “EmployeeAbsenteeismKPIs” table.

a) Select all of the EmployeeAbsenteeism KPI metrics you created from A19 and B19 downwards (ie.
A19:B##), copy those two columns of data to your clipboard with CTRL+C, click on cell F19, then use
the Paste Special command (ie. CTRL+SHIFT+V), and paste as Values and Number Formats.

b) Press CTRL+T to turn your new unstructured data range into a table and name it
“EmployeeAbsenteeismKPIs”.

c) Review your consolidated Employee Absenteeism metrics and apply the correct data type and decimal
place specifications to each metric (as appropriate – if it’s not already accomplished).

January 2025 Page 10 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING
NOTE: You can reduce the width of the three empty columns (C:E) spacing your reporting tools from
your longitudinal KPI tracking tables to make your worksheet more readable.

Tracking Additional KPIs Longitudinally

Create at least three (3) additional reporting tools to consolidate any additional metrics/KPIs you wish to track
longitudinally (over time) by repeating the steps you used to create your “EmployeeDatabaseKPIs” and
“EmployeeAbsenteeismKPIs” rerporting tools and their respective longitudinal KPI tracking tables.

January 2025 Page 11 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING
ASSIGNMENT REQUIREMENT: Create at least three (3) additional longitudinal KPI tracking tables that track
categories of metrics you believe are important to the HR function and the financial performance of the
Canadian division. You must decide the focus of the additional metric/KPI tracking tables. To do this, repeat the
steps followed when creating the EmployeeDatabaseKPIs and EmployeeAbsenteeismKPIs. Some options you
might consider include:
 Canadian Quarterly Financial Performance (Offices)

 Canadian Quarterly Financial Performance (Company)

 USA Quarterly Financial Performance (Company)

 Canadian HR Metrics

 USA HR Metrics

 Any other Metrics/KPIs you can calculate from the “EmployeeDatabase” worksheet

NOTE: You can create hybrid KPI tracking tables that combine similar metrics. For instance,
Canadian Quarterly Financial Performance (Company) could reasonably be combined with the USA
Quarterly Financial Performance (Company), etc.

8) The steps you must follow to create your new longitudinal KPI tracking tables are the same used in the two
previous tracking tables you created.

Updating the EmployeeDatabase Query (Removing Turnover

from Previous Fiscal Year)
Now that you have storage tables for tracking biannual KPIs (workforce and financial), you need to make an
adjustment to your “EmployeeDatabase” query. Presently, it reports employment relationship information for
all employees in the Canadian division – even those employees that have exited the company. Currently, this is
not a problem since our source data doesn’t contain any information on employees that exited the company
prior to 2024-Q2, however that will change in 2025-Q2 and you need to adjust the programming of your
queries to filter out all employees that left prior to the start of the fiscal year.

To filter out all employees that left prior to the fiscal year of the data currently loaded into your “HR-Report”
workbook, you need to identify a column that contains data that can be filtered some specific way (in
PowerQuery) that results in the removal of such employees. Furthermore, you want to choose a column that,
once properly filtered, will remove employees that left prior to the fiscal year from all other queries so that
metrics like Employee Absenteeism are not calculating their figures using long-exited (and therefore irrelevant)
employees.

Since the “EmployeeDatabase” query forms the basis of nearly every other query in your “HR-Report”
workbook, that query is the best place to start looking for a column that can be filtered to exclude irrelevant
employees.

January 2025 Page 12 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING
Since the “YTD Days Employed Proration” column in your “EmployeeDatabase” query calculates the % of the
current fiscal year each employee has been employed with the Canadian division of Edge Communications,
filtering that column such that it only keeps rows (ie. “employees”) reporting a percentage value greater than
0% would result in the removal of all employees that have been employed for 0% of the current fiscal year.

This will be a great solution to ensuring your “HR-Report” only calculates Workforce KPIs in relation to
employees that have been working more than 0% of the current fiscal year. As a result, Workforce metics (ie.
headcount, FTE count, etc.) will report accurately and Financial metrics (ie. HCROI, Revenue per FTE, etc.) that
rely on accurate Workforce metrics will calculate accurately.
9) Open your “HR-Report” Excel workbook (updated/corrected in accordance with Assignment #2 feedback if
needed), access the Queries & Connections panel (which can also be found as a command in the Data
menu), right-click on your “EmployeeDatabase” query, and click the Edit command to open the
PowerQuery Editor.

10) In the PowerQuery Editor, with your “EmployeeDatabase” query selected from the Queries panel at the
left-side of your screen, click the drop-down menu on the “YTD Days Employed Proration” column header.

11) Navigate the drop-down menu to find the Number Filters command and then click the Greater Than…
command to specify that you only want to keep rows in this table that contain a number that is greater
than “0” in this “YTD Days Employed Proration” column.

January 2025 Page 13 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING

12) In the Filter Rows window, use the settings to keep only rows there “YTD Days Employed Proration” is
greater than zero and then press OK.

13) Before you click “Close and Load” to implement and save these changes, you must make a small
adjustment in the two queries that use data from the “EmployeeDatabase” query: namely, the
“EmployeeAbsenteeism” query and the “AbsenteeismAnalysis” query.

a) Click the “EmployeeAbsenteeism” query in your list of queries on the left-side of the PowerQuery
Editor.
January 2025 Page 14 of 25
MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING

i) The “EmployeeAbsenteeism” query uses the “SourceData” query as it’s starting point so your
changes to the “EmployeeDatabase” query will not prevent the “EmployeeAbsenteeism” query
from including all employees (even those that left the company prior to the current fiscal year) in
it’s calculations. However, this query still merges data from the “EmployeeDatabase” query (you
can see that “Merged Query” step further on up the list of Applied Steps) so the two queries do
intersect.

Consequently, when you load 2025-Q2 data into your “HR-Report” workbook, the
“EmployeeAbsenteeism” query will try to merge “EmployeeDatabase” data for employees that no
longer exist in the 2025 context. This is useful to you since it means that long-departed employees
in the “EmployeeAbsenteeism” query will have empty cells of data in all columns merged from the
“EmployeeDatabase” query. As such, you simply need to pick a column that is merged into the
“EmployeeAbsenteeism” from the “EmployeeDatabase” query and use the filter menu on that
column header to remove all rows that contain empty values in that colum. This is how you can do
that:

(1) Click the drop-down menu on the “Year-Quarter” column header and click the Remove Empty
command. This will remove any rows that contain empty cells of data in this “Year-Quarter“
column (because those employees don’t exist in your “EmployeeDatabase” query).

NOTE: You will not notice any immediate changes since you are still working with 2024-Q2 data
at present.

January 2025 Page 15 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING

b) Lastly, you need to do the same adjustment in the “AbsenteeismAnalysis” query. However, since the
“Year-Quarter” column in the “AbsenteeismAnalysis” query does not come from the merged
“EmployeeDatabase” query data, you need to select a different imported column of data to filter out
all rows containing empty cells in that column. In this case, the “Job Status” column originates from the
“EmployeeDatabase” query.

i) Click the “AbsenteeismAnalysis” query from the Query panel on the left side of Power Query
Editor.

ii) Navigate to the “Job Status” column, click the drop-down menu on the “Job Status” column header
and click the Remove Empty command. This will remove any rows that contain empty job data in
the 2025-Q2 biannual reporting once you load that data into your “HR-Report” workbook.

14) Click Close and Load… to save all of the edits you have made to your three queries. Consequent to your
changes, Employees that are not employed more than 0% of the fiscal year associated with the data
loaded into your “HR-Report” will be removed all Workforce and Financial calculations.

NOTE: Excel workbooks that have been created to process, calculate, and present data from one data
source (often providing a snapshot of an organization’s performance at a specific point in time) nearly
always require adjustment once updated data (often providing a subsequent snapshot of an organization’s
performance at a further point in time) is loaded into the Excel workbook. In this case, the adjustment you
needed to make was to correct for the overcounting of headcount and FTEs by preventing your workbook
January 2025 Page 16 of 25
 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING
from counting Employees that have left prior to the fiscal year of the loaded data (which was not a
problem for you when you were only looking at 2024-Q2 data alone).

Loading New (2024-Q4) Data & Updating KPI Tables

(PowerQuery & Excel)
Now that you have longitudinal KPI tracking tables for recording biannual KPIs (workforce and financial) over
time, you are now ready to load the 2024-Q4 data into your “HR-Report” Excel workbook.
15) Open your “HR-Report” Excel workbook (updated/corrected in accordance with Assignment #2 feedback if
needed), access the Queries & Connections panel (which can also be found as a command in the Data
menu), right-click on your “SourceData” query, and click the Edit command to open the PowerQuery
Editor.

16) In the PowerQuery Editor, with your “SourceData” query selected from the Queries panel at the left-side
of your screen, left-click the gear icon located on the right side of the Source step in your list of Applied
Steps.

January 2025 Page 17 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING
17) In the pop-up window, click the Browse command and use Windows Explorer to locate the “2024-Q4-
[Link]” file saved in your MGMT Assignment folder, click Import, and click OK.

18) To load this new data into your “HR-Report”, click Close and Load (ie. you do not need to use the Close
and Load to… command).
19) To make sure all of your queries are refreshed with the new 2024-Q4 data, click the Data menu, and click
the Refresh All drop-down command from the ribbon, and click the Refresh All command to refresh all of
your queries (not just one!).

20) Review all of the updated data in each sheet and table of your workbook.

NOTE: All of your data automatically updates to the 2024-Q4 data. This also means that all of your
calculated KPIs address the updated 2024-Q4 data and all of your dynamic worksheet titles and dynamic
column headers in your “KPITracking” worksheet automatically update to say “2024-Q4” instead of “2024-
Q2”. This is how you make your HR Report relevant to each new reporting cycle without having to build it
again from scratch (potentially in a manner that makes comparison of KPI calculations impossible).

January 2025 Page 18 of 25

MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING

January 2025 Page 19 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING
21) Remember, there is one important piece of missing data that must be entered manually for each report:
US Absences (see the Assignment #3 Scenario description for US “Total Absences” figures corresponding
to each biannual report).

a) To enter this number manually (provided to you by the Finance team in the Assignment #3 scenario
description above), navigate to the “Assignment2-Answers” sheet, locate cell L4, and enter the 2024-
Q4 total absences figure for the US: 1980.

NOTE: It should be recognized that your Assignment #2 calculations have been updated to run 2024-
Q4 data. It may be worth noting any changes in the absenteeism metrics you analyzed in Assignment
#2. If this is something you wish to track longitudinally, you can change your source data back to 2024
and create a new KPI tracking table if you wanted. Otherwise, you can make handwritten notes!

22) To track your organization’s data longitudinally (ie. over time), you need to copy/paste (as Values and
Number Formats only) the updated KPIs you chose to track into their respective storage/tracking tables in
the “KPITracking” sheet.

NOTE: Check all your KPI reporting tools to make sure they are reporting KPIs properly. You want to make
sure that none of the equations you provided in Column B still exist as intended. If you notice that a cell is
reporting a number instead of the result of an equation then that number will be the figure from the
previous biannual report. In general, your KPI reporting tools should avoid recalculating a KPI and only do
so if there is no clean metric (ie. without concatenated text) to reference; in other words, your equation
should report the calculated metric as it is calculated elsewhere in the document (ie. =D4 or =H12)
wherever possible. If you discover an equation failure, you will need to fix that error with another equation
(and keep an eye on it going forwards).

a) To update the “EmployeeDatabaseKPIs” table, navigate to the “KPITracking” worksheet, select the
updated EmployeeDatabase KPIs reported in the correct KPI consolidation tool (cells B1:B15), copy the
selected data to your clipboard, select the first open column at the end of your
“EmployeeDatabaseKPIs” table (ie. H1), and paste as Values and Number Formats only.

January 2025 Page 20 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING

b) Make sure the right data types and decimal places are assigned to each value if they are not already.

23) Repeat the steps above for each of the longitudinal KPI tracking tables you created (ie.
EmployeeAbsenteeismKPIs, Canadian Office Performance KPIs, US Financial Performance KPIs, etc.). You
should have at least four (4) total longitudinal KPI tracking tables (but you may have more if you decided to
track additional KPIs for your analysis of organizational performance).

NOTE: Now is the perfect time to assess the performance of the Canadian Division to review what has
changed from 2024-Q2 to 2024-Q4 and form an analysis of that performance that includes the
identification of key performance metrics you want to keep watching into the next biannual reporting
cycle (2025-Q2). To help yourself along, ask yourself questions like:

a) “What positions did we hire? In which Canadian office did we hire them?”

b) “What positions became vacant? In which Canadian office did they become vacant?”

January 2025 Page 21 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING
c) “Which employees left the organization and what was their absence pattern like?”

d) “Which Canadian offices performed better or worse?”

e) “Has the Canadian or USA divisional performance improved or deteriorated? Have there been any
changes to their compensation model or apparent business strategy?”

Loading New (2025-Q2 and 2025-Q4) Data & Updating KPI

Tables (PowerQuery & Excel)
24) Before you load 2025-Q2 and 2025-Q4 data, you must return to your “Lists” worksheet and populate 2025-
Q2 and 2025-Q4 data into your “WorkingDays” table. Without this data, your KPIs will not calculate
correctly!

NOTE: You can look up the total working days ending Q2 2025 and Q4 2025 online:
[Link]

25) Now that you have properly set your “WorkingDays” table with 2025 calendar data, repeat the steps
above to change your source data to 2025-Q2. Refresh all of your tables, review the results across all
sheets, make sure your KPI reporting tools are reporting correct metrics for adding to your longitudinal
tables (ie. make sure all the equations in Column B are still working correctly!), make notes/observations,
and then copy/paste (as Values and Number Formats only) the 2025-Q2 KPI tracker data into the first open
column at the end of your KPI tracking tables.

WARNING: Make sure you update the “Total Absences” amount in your “Assignment2-Answers” sheet
using the updated 2025-Q2 value provided in the Assignment #3 Scenario description.

26) Once all 2025-Q2 data is reviewed and tracked appropriately in your “KPITracking” worksheet tables, load
the 2025-Q4 data and review/track that final semiannual workforce/financial report accordingly.

27) Save your completed HR-Report as it is now ready for loading into Power BI. You can return to your HR-
Report anytime and make adjustments, create new longitudinal tables, and review Assignment #1 and
Assignment #2 calculations and answers anytime to support the development of your Power BI dashboard
and final PowerPoint or PowerBI presentation to the Canadian Division executive.

January 2025 Page 22 of 25

MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING

January 2025 Page 23 of 25

 MGMT8575 FINAL PROJECT: LONGITUDINAL ANALYSIS & POWER
BI DASHBOARDING
Finalizing your HR-Report Workbook for Grading
28) Submit your corrected individual HR-Report Workbooks for grading.

Create a Microsoft Power BI Dashboard

29) As a group, review your organization’s KPI’s and metrics. What do you notice? What story/stories do you
want to tell your Senior Leadership Team in your presentation? Once you determine this, create a
dashboard in Power BI to help explain your story. This can be integrated into your presentation or a
standalone reference document.

Review resources, videos and references within the course shell that walk you through best practices when
it comes to the final step in the data analytics process – Envision and Communicate your Findings.

As a reminder, there is also a Microsoft Power BI module within MindTap located in the Course Shell that
would be a good resource

30) Submit 1 file on behalf of the group for grading.

Record & upload a 7 – 10-minute Presentation

31) As a group, prepare and record a 7 – 10-minute PPT presentation to Senior Leaders highlighting key
metrics, analysis, trends and recommendations. This presentation is to be recorded in Zoom, with cameras
on, and accompanied by a slide deck.
32) The objective of this presentation is to provide Senior Leaders with an overview of relevant KPI’s,
concerns, opportunities and recommendations.
33) Within your recommendations, be sure to include the five survey questions you designed!
34) Review resources, videos and references within the course shell that walk you through best practices when
it comes to the final step in the data analytics process – Envision and Communicate your Findings.
35) Upload Zoom recording for grading.

January 2025 Page 24 of 25