Challenge #457: Profit and Loss Statement

[AYXAcademy]

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:

• Summarizing Data
• Changing Data Layouts

Happy new year!

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

The Academy Team

[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......

 

[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

[shrestha_nitesh]

Spoiler

 

[balajilolla2]

Spoiler

Solution Attached

[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:

[KLTerrell]

@Carolyn This is so handy!! Thank you!

[dbelisle]

completed

[Hub119]

Solution attached.

Spoiler

[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