Index

Sr. No. Date Topics Pg. No Sign
1 23-01-2025 **Explore What-If Analysis Feature of Excel –

  1. Scenario Manager

  2. Goal Seek

  3. Data Table** | | | | 2 | 30-01-2025 | **Data Analysis –

  4. Pivot Table** | | | | 3 | 06-02-2025 | Learning Tableau | | | | 4 | 20-02-2025 | Learning Tableau | | |

Session 1 (Explore What-If Analysis Feature of Excel)

  1. Learn how to use What-If Analysis feature of Excel.

    1. Steps to Create Data and perform Scenario Manager

      1. First input the data, such as

        1. Price: 32
        2. Qty: 100

      2. Now in Total Revenue column, the value would be price cell * Qty cell (=C3*C4)

        1. Total Revenue: 3200

      3. Now in Transport Cost column, the value would be Total Revenue * 10% (=C5*10%)

        1. Transport Cost: 320

      4. Now in Item Cost column, the value would be 20 * Qty (=20*C4)

        1. Item Cost: 2000

      5. Now in Total Cost column, the value would be Transport Cost + Item Cost (=C6+C7)

        1. Total Cost: 2320

      6. Now in Profit column, the value would be Total revenue - Total Cost (=C5-C8)

        1. Profit: 880

      7. Steps to create a create scenarios 1.

      8. Snapshot of current Scenario (Qty is 100).

        image.png

      9. Now, create what-if analysis, scenarios to see the changes.

        1. Scenario 1 (what If Qty is 200)

          image.png

        2. Scenario 2 (what If Qty is 300)

          image.png

    2. Goal and seek

      image.png

      image.png

      image.png

      image.png

    3. Data Table

      1. Copy the same data in separate sheet.

        image.png

      2. Create 2 columns, 1 for Qty and 1 for Profit.

        image.png

      3. Now below the profit column, put the formula (=profit value [=C9])

        image.png

      4. Now, write all the qty, which we want to see the profit of (Scenarios).

        image.png

      5. Now select the both qty and profit column and use Data Table feature of What-If Analysis to see the updated profit.

        image.png

      6. Updated Profit

        image.png

    4. Excel File where all the above operations were performed.

      What-If-Analysis.xlsx

Session 2 (Pivot Table and Pivot Chart)

  1. Create a sample employee dataset in excel and generate Pivot Table and Chart.

    1. Employee Data Set.

      image.png

    2. Select the entire dataset and go to the menu bar and click on insert.

    3. Now click on PivotTable and generate Pivot table in a new sheet.

    4. Choose fields "Emp Name" and “Net Salary” to add to report

      image.png