Assignment -34 | Financial Performance Dashboard

Excepted timing : 10 minutes

Sample Data

| Year | Revenue | Expenses | Profit |

|------|---------|----------|--------|

| 2020 | 100000  | 80000    | 20000  |

| 2021 | 120000  | 90000    | 30000  |

| 2021 | 150000  | 120000   | 50000  |

| 2020 | 100000  | 80000    | 20000  |

| 2022 | 110000  | 90000    | 22000  |

| 2022 | 150000  | 100000   | 50000  |

| 2020 | 160000  | 80000    | 21000  |

| 2021 | 120000  | 91000    | 30000  |

| 2022 | 150000  | 120000   | 52000  |

| 2020 | 120000  | 80000    | 20000  |

| 2023 | 120000  | 92000    | 35000  |

| 2024 | 180000  | 100000   | 50000  |

 


Task :
  1. Create a PivotTable to summarize Revenue, Expenses, and Profit by Year.
  2. Insert a PivotChart to visualize the trend of Revenue, Expenses, and Profit over the years using a line chart.
  3. Calculate the profit margin (Profit / Revenue) for each year using formulas.
  4. Use sparklines to display mini charts for Revenue, Expenses, and Profit trends for each year.
  5. Create a slicer to filter data by year and update all charts accordingly.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

Steps to Perform:

  1. Create a PivotTable to Summarize Revenue, Expenses, and Profit by Year:
    • Select the sample data including Year, Revenue, Expenses, and Profit.
    • Go to Insert > PivotTable > PivotTable to create the PivotTable.
    • Drag Year to Rows and Revenue, Expenses, Profit to Values area (summarize as needed).
  2. Insert a PivotChart to Visualize Revenue, Expenses, and Profit Trends:
    • With the PivotTable selected, go to Insert > PivotChart > Line Chart to create the chart.
    • Customize the chart to display Year on the x-axis and Revenue, Expenses, Profit on the y-axis.
  3. Calculate Profit Margin for Each Year Using Formulas:
    • Enter =Profit / Revenue in a new column (assuming Profit is in column D and Revenue is in column C) to calculate profit margin as a percentage.
  4. Use Sparklines to Display Mini Charts for Revenue, Expenses, and Profit Trends:
    • Select a range of cells where you want to insert sparklines (e.g., next to each year's data).
    • Go to Insert > Sparklines > Line to create mini charts for Revenue, Expenses, and Profit trends for each year.
  5. Create a Slicer to Filter Data by Year and Update All Charts:
    • Ensure your PivotTable and PivotChart are selected.
    • Go to Insert > Slicer > Year to create the slicer.
    • Use the slicer to filter data by year and observe how all linked charts (PivotChart and sparklines) update accordingly.

Detailed Steps: