Mission #04

Context

Pedro showed our work to João Paulo, the financial manager of the operation in Brazil. He was fascinated with the potential of the tool and with the dynamism of the information.

They talked about the need to get the information directly from the database, as this would greatly facilitate the process of updating the reports. However, João Paulo pointed out that this request must be authorized by the headquarters in the USA.

Nobody better than Afonso, Brazil CEO, to conduct this conversation with the headquarters and sell our project to HPN in the USA.

As we have so far worked only with B2B sales, Afonso asked Pedro and João Paulo to create a financial/accounting report of Income Statement, or Profit and Loss Statement (PnL). This is the report that the Brazilian office needs to send to the American headquarters as “accountability”.

Currently, João Paulo receives this PnL in an Excel file, but he feels it lacks a greater visual appeal that highlights the most relevant information.

Afonso then requested that this report be created in Power BI and, along with the other tasks made so far, he believes he has more subsidies to get the credentials from the headquarters to expand the project globally in HPN.

Along with the report, Afonso pointed out that it would be important for those involved in the project to have their LinkedIn profiles updated, bearing in mind that it is likely that the matrix team will take a look at the profiles to analyze experiences and other relevant information.

Well, now it’s time to get down to business, and get ready to impress the US team. I have no doubt that we will achieve this goal.

Attention!

In the file for this mission there are four sheets:

  1. IncomeStatement
  2. AccountHeader
  3. Account
  4. Finance

The first sheet is how João Paulo receives the Income Statement every month, which is created by his team based on the next three sheets.

In this report are present the values for both operations: B2B (resales) and B2C (final consumer). Therefore, you will be able to notice that the sales values are higher than in the previous missions.

Goal

These are your tasks for this new mission:

  1. Import the tables from the file available in this mission and build the DRE in the same original structure (IncomeStatement sheet):

  1. Create the measures “% VA” for the Vertical Analysis (percentage of the account amount to Net Sales in the same month) and “% HA” for the Horizontal Analysis (percentage variation month over month for the same account), similar to the example above.

  2. Create a conditional formatting in the measure “% HA” to show in a more visual way which accounts have increased or decreased over the months.

  3. Create four cards to show the total value for:

  1. Gross Sales $
  2. Net Sales $
  3. Gross Margin %
  4. Net Income $
  1. Create a graph to show which accounts have the highest operating expenses.