Key insights

• Report Formatting and Measures

  • 📊 The tutorial demonstrates creating new measures and formatting a report, including distributor, export, and wholesale revenue values. It also includes steps for formatting, color-coding, and adding interactive buttons to the report.
  • 📈 Creating new measures for distributor, export, and wholesale revenue, Formatting the report with color-coding and transparency settings, Adding interactive buttons for page navigation, Demonstrating steps for creating and formatting measures and charts

• Formatting and Calculations

  • 📑 A tutorial on formatting and calculating gross revenue, gross profit, and profit after tax. The speaker explains how to change formatting, calculate gross revenue, gross profit, and profit after tax using specific formulas and data manipulation techniques.
  • 💰 Changing formatting for headers, columns, and rows, Calculating gross revenue without a separate column, Formatting and renaming items for clarity, Calculating gross profit using specific logic and renaming, Calculating profit after tax using new measures, Calculating revenue by channel using tax calculations

• Data Visualization

  • 📊 The speaker demonstrates different ways to calculate the actual versus budget percentage using conditional formatting and functions. They also show how to color-code the budget number based on its value and use a slicer to visualize the data by month.
  • 📈 Demonstration of different methods to calculate actual versus budget percentage, Using conditional formatting and functions for calculation, Color-coding budget numbers based on their value, Utilizing a slicer to visualize data by month

• Creating Measures and Calculations

  • 🧮 The speaker demonstrates how to filter and create a new measure in a table, modifies an 'if' condition, and uses variables for cleaner calculations. The process involves creating measures for actuals, budget, and variance.
  • 🔄 Filtering and creating a new measure in a table for transaction level 2, Modifying the 'if' condition for the total actual consolidate measure, Using variables for cleaner and easy-to-understand calculations, Creating measures for total budget, total budget with consolidate, and renaming measures, Creating a new measure for actual versus budget variance

• Data Manipulation and Organization

  • 🔣 The video discusses data manipulation and organization using Excel functions and table structures to create a consolidated income statement with desired results.
  • 📊 Counting the number of rows in a data structure, Calculating subtotal with a condition, Adjusting the format and layout of the data, Creating a new major to consolidate data, Manipulating income statement structure, Using Excel functions and filtering for desired results

• Budget and Income Statement Structure

  • 💰 Demonstration of creating a budget and income statement structure, including data modeling, table formatting, and calculation of measures.
  • 📑 Creating a budget and income statement structure, Data modeling for a simple date table, Formatting the income statement table using row index and subtotal, Calculation of measures for actual total income, Saving the file after completing the steps

• Data Import and Transformation

  • 📉 The video segment discusses the creation of an income statement using data from Excel, including importing profit and loss statement, actual data, and budget.
  • 🔄 Operating income equals gross profit minus operating expenses, Income before tax equals income before tax expenses minus income tax, Importing profit and loss statement from Excel, Transforming and extracting data for the income statement, Importing actual data and budget data for the income statement

• Income Statement Basics

  • 📊 The video explains how to create an income statement using an Excel template and reviews the five key areas of an income statement.
  • 📈 Income statement shows business performance from revenue to profit after tax, Using an Excel template to structure and display data for Power BI, Five key areas of an income statement: revenue and adjustment, cost of goods sold, operating expenses, operating income and margin, profit and loss, Gross profit equals net sales minus cost of goods sold

Q&A

• What does the tutorial demonstrate about creating new measures and formatting a report?

  The tutorial demonstrates creating new measures for distributor, export, and wholesale revenue, formatting the report with color-coding and transparency settings, and adding interactive buttons for page navigation. It also includes steps for creating and formatting measures and charts.

• What does the tutorial cover regarding formatting and calculating gross revenue, gross profit, and profit after tax?

  The tutorial covers changing formatting for headers, columns, and rows, calculating gross revenue without a separate column, formatting and renaming items for clarity, calculating gross profit using specific logic and renaming, calculating profit after tax using new measures, and calculating revenue by channel using tax calculations.

• What methods are demonstrated for calculating actual versus budget percentage?

  The video demonstrates different methods to calculate actual versus budget percentage, including using conditional formatting and functions for calculation. It also shows color-coding budget numbers based on their value and utilizing a slicer to visualize data by month.

• How are measures created for actuals, budget, and variance?

  The video discusses filtering and creating a new measure in a table, modifying the 'if' condition for the total actual consolidate measure, using variables for cleaner calculations, and creating measures for total budget, total budget with consolidation, and renaming measures. It also covers creating a new measure for actual versus budget variance.

• What techniques are used for data manipulation and organization in Excel?

  The video shows techniques such as counting the number of rows in a data structure, calculating subtotals with a condition, adjusting the format and layout of the data, as well as creating a new major to consolidate data. It involves manipulating the income statement structure, using Excel functions, and filtering for desired results.

• What are the steps involved in creating a budget and income statement structure in Excel?

  The video demonstrates creating a budget and income statement structure, including data modeling, table formatting, and calculation of measures. It involves importing actual and budget data, data modeling for a simple date table, formatting the income statement table using row index and subtotal, and calculating measures for actual total income.

• How is gross profit calculated?

  Gross profit is calculated as net sales minus cost of goods sold.

• What are the five key areas of an income statement discussed in the video?

  The video reviews the five key areas of an income statement: revenue and adjustment, cost of goods sold, operating expenses, operating income and margin, and profit and loss.

• What does the video cover?

  The video covers the creation of an income statement in Excel, including importing profit and loss statement data, data modeling, table formatting, and calculating measures for actual and budget data. It also discusses data manipulation and organization using Excel functions and table structures to create a consolidated income statement.

• 00:16 The video explains how to create an income statement using an Excel template and reviews the five key areas of an income statement.
• 03:03 The video segment discusses the creation of an income statement using data from Excel, including importing profit and loss statement, actual data, and budget.
• 09:02 Demonstration of creating a budget and income statement structure, including data modeling, table formatting, and calculation of measures.
• 15:58 The video discusses data manipulation and organization using Excel functions and table structures to create a consolidated income statement with desired results.
• 24:40 The speaker demonstrates how to filter and create a new measure in a table, modifies an 'if' condition, and uses variables for cleaner calculations. The process involves creating measures for actuals, budget, and variance.
• 32:23 The speaker demonstrates different ways to calculate the actual versus budget percentage using conditional formatting and functions. They also show how to color-code the budget number based on its value and use a slicer to visualize the data by month.
• 41:38 A tutorial on formatting and calculating gross revenue, gross profit, and profit after tax. The speaker explains how to change formatting, calculate gross revenue, gross profit, and profit after tax using specific formulas and data manipulation techniques.
• 51:24 The tutorial demonstrates creating new measures and formatting a report, including distributor, export, and wholesale revenue values. It also includes steps for formatting, color-coding, and adding interactive buttons to the report.