Assignment -31 | Monthly Sales Dashboard

Excepted timing : 10 minutes

Sample Data

| Month       | Product A | Product B | Product C |

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

| January     | 1000      | 1200      | 800       |

| February    | 1500      | 900       | 1100      |

| March       | 1800      | 1700      | 600       |

| April       | 1000      | 1200      | 800       |

| May         | 1300      | 1900      | 100       |

| June        | 1800      | 700       | 600       |

| July        | 1200      | 1200      | 800       |

| August      | 1500      | 2900      | 110       |

| September   | 2800      | 2700      | 600       |

 


Task :
  1. Create a line chart to visualize the sales trend for each product over the three months.
  2. Use formulas to calculate the total sales and average sales for each product.
  3. Insert a combo chart to compare the sales of Product A and Product B using columns and Product C using a line.
  4. Add data labels and format the chart axes to enhance readability.
  5. Create a slicer to filter data by month 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 Line Chart to Visualize Sales Trend for Each Product:
    • Select the data range including Month and sales data for each product (Product A, Product B, Product C).
    • Go to Insert > Line Chart > Line to create a line chart.
    • Customize the chart with Month on the x-axis and sales values on the y-axis for each product.
  2. Calculate Total Sales and Average Sales for Each Product Using Formulas:
    • Use SUM function to calculate total sales and AVERAGE function to calculate average sales for each product across the months.
    • Example for total sales of Product A: =SUM(B2:B4) assuming sales data for Product A is in column B.
    • Example for average sales of Product A: =AVERAGE(B2:B4).
  3. Insert a Combo Chart to Compare Sales of Products A, B, and C:
    • Select the data range including Month and sales data for each product.
    • Go to Insert > Combo Chart > Combo.
    • Choose Product A and Product B for columns and Product C for a line in the combo chart.
    • Customize the chart with appropriate titles, axes labels, and legend.
  4. Add Data Labels and Format Chart Axes to Enhance Readability:
    • Select the chart and go to Chart Tools > Design > Add Chart Element > Data Labels > Inside End to add data labels.
    • Right-click on the chart axes (x-axis and y-axis) and choose Format Axis to adjust formatting options such as axis labels, scale, and number format for clarity.
  5. Create a Slicer to Filter Data by Month and Update All Charts:
    • Select any cell within your data range.
    • Go to Insert > Slicer and choose Month as the field to create the slicer.
    • Use the slicer to filter data by month and observe how all charts linked to the data range update dynamically.

Detailed Steps: