Challenge #457: Profit and Loss Statement

Weekly Challenge LMS Banner.png

Hello Community Members,

A solution to last week’s challenge can be found here.

This challenge was submitted by Jacob Ward. Thank you, @SauronsBane, for this excellent contribution.

As the year wraps up, your role as a business analyst at a rapidly growing start-up takes a new turn. The VP of Finance has reached out, seeking help with the massive general ledger (GL) data that has grown beyond what the financial analysts can manage in Excel. Your task? Automate the workflow to create a consolidated Profit and Loss (P&L) statement for monthly reporting.

You have been provided with 2024’s GL data and the chart of accounts. Now, it is your job to organize the transaction data monthly according to the GL classes to streamline the financial reporting process.

Need a refresher? Review the following lessons in Academy to gear up:

Happy new year!

Data source: https://www.kaggle.com/datasets/irfansharif/generalledger (adjusted for challenge goals).

The Academy Team

Challenge_457_start_file.yxzp

Challenge_457_solution_file.yxzp

Data Analysis

Data Preparation

Core

Basic

Accepted answers

All comments

KLTerrell

Fun! And I will absolutely be adding this to a lesson for my accounting technology undergrads!

Spoiler

To get the sort order right, I had to use the Date Time Parse tool to number the months, then used Select to rename the months their actual names. I feel like there's a better way out there......
Alteryx Challenge 457.png

Challenge_457_start_file - KT Solution.yxmd

olga_strubbe

Thank you, Jacob @SauronsBane

This was a timely challenge with the year-end reporting right around the corner.

I've made my solution into an app where the user can select the year to bring in the P&L data.

If anyone knows how to include 'All Years' in the filter, please let me know - appreciate it!

Spoiler

Challenge_457_OS.yxwz

shrestha_nitesh

Spoiler

Challenge 457 Solution.yxzp

balajilolla2

Spoiler

Solution Attached

Screenshot 2024-12-30 095103.jpg

Challenge_457_start_file.yxmd

Carolyn

Solved!

Spoiler

@KLTerrell - To handle getting the months in the right order, what I did was....

Spoiler

I created a month column with the month number followed by the month name (e.g. 01January). The "01" lets the Cross Tab tool sort it into the order that I want. Then I use the Dynamic Rename Tool to remove the "01" and just leave "January".

It's a handy trick whenever I want to force a Cross Tab order that isn't alphabetical

Formula:

Dynamic Rename:

Challenge_457_start_file.yxzp

KLTerrell

@Carolyn This is so handy!! Thank you!

dbelisle

completed

Challenge_457-DB.yxmd

Hub119

Solution attached.

Spoiler

Challenge_457_CH Solved.yxzp

olga_strubbe

Yippee! I was able to figure out how to include an option to select all years or just a specific years using an app.

Spoiler

Challenge_457_OS_updated.yxwz

cpoveromo13

I'm confused - why is the data not grouped by month? I don't think it's accurate to combine January 2019 data and January 2024 data. Am I missing something?

tammybrown_tds

Spoiler

challenge_457_tbrown.yxmd

jpowellsdak

Great challenge! It took a minute to figure out why my totals weren't matching the output.

Spoiler

Finally realized that sorting before adding cross tab did not ensure that month column headers would be sorted correctly, had to add the month num, then remove it after doing cross tab.

challenge 457.jpg

Challenge_457_start_file.yxmd

quianalee88

This challenge had me working into the night! I just could not get past summarizing the months. When I finally broke down and reviewed the solution, I saw the DateParse tool change the date into two digits. I wanted to hit myself. With every challenge, even the ones that stump me, I'm learning new ways to solve analytical problems. So, it's a win all the way around!

Challenge_457_start_fileC.yxmd

Fangfang

First time join the challenge. Not sure how to insert spoiler. And... why someone's flow is so complicated while mine are so simple? Am I missing some important rule?

Challenge_457_Solution_Fang.yxmd

Dhrish

Spoiler

Challenge_457_start_file.yxmd

AkimasaKajitani

My solution!

Spoiler

Challenge_457_start_file_AK.yxzp

RolandSchubert

Spoiler

challenge_457_solution_rsc.yxmd

Sebastian_Chaieb2

Nice and easy one

Spoiler

Challenge_457_Solution_SC.yxzp

jfissel

Spoiler

Challenge_457_jfissel.yxmd

NaiLo

Managed to match the output file but I'm a bit hard-pressed to understand why an annual P&L would combine months for all years? I'm guessing a better use case would be to summarize by month for each year and output multiple reports grouped by year to show the P&L statements between 2019 and 2024