Create a waterfall chart to show the variation in inventory level by quarter

Create a waterfall chart to show the variation in inventory level by quarter

Problem Statement

Create a waterfall plot for the given data. Show the variation of inventory from 2015 to 2016 with changes in each quarter. The data is given in the attachment.

How to perform analysis

Step 1: Open Sigma Magic
  1. Click on the Sigma Magic button on the Excel toolbar.
  2. Click on the New button to create a new project.
Step 2: Data Preparation
  1. Open the Water fall.xlsx attachment
  2. Select the data and convert it to table format by clicking Ctrl-T.


Step 3: Add the analysis template
  1. Click on the Tool Wizard to add the analysis template.
  2. Click on Graph and then Waterfall Chart.


Step 4: Specify the analysis options
A new Waterfall worksheet will be added to your workbook, and the following dialog box will be opened. Click on the Setup tab on the right. Accept the default values and specify the analysis options



Next, click on the Data tab and specify the data. Drag and drop the Table1 Value to the to the Analysis Variables and Table 1 Label to the Categorical Variables.



If you need to make changes to the charts, specify the optional settings in the Charts tab.

 Labels:

  • Add a title for the chart.
  • Label the X-axis and Y-axis appropriately.

Appearance:

  • Adjust colors, font sizes, or other visual elements as needed.
  • Enable/disable gridlines or background shading.
Finally, click the Verify tab to ensure all the inputs are okay and shown in a green checkmark.



Step5: Generate analysis results
Click on the OK button to generate analysis results. The following figure shows an example output screen.


Interpreting Results

The following conclusion can be drawn from this analysis.
    Starting Position: The inventory was at 100 units in the year 2015

    Quarterly variations in 2016:
    1. Q1: 5 units increment to 105
    2. Q2: 8 units decrement to 97
    3. Q3: 15 units increment to 112
    4. Q4: 2 units increment to 114
    Generic conclusions:
    1. The highest positive variation was in Q3 2016 (+15 units)
    2. Only one decline took place in Q2 2016 (-8 units)
    3. Overall trend indicates net inventory growth from 100 to 114 units (+14 units total)
    4. The inventory fluctuations indicate seasonal variations in demand or strategic inventory management decisions.
    5. The final inventory position at the end of 2016 was 14% higher than the starting position in 2015, indicating an overall expansion in inventory levels despite the mid-year reduction in Q2.



      • Related Articles

      • Create a multi-vari chart to understand the source of variation

        Problem Statement Create a Multi-Vari chart for the data given below. Determine which variable contributes the most to the variation of the output. The data for this exercise is attached. How to perform analysis Step 1: Open Sigma Magic Click on the ...

      • Waterfall chart overview

        A Waterfall Chart is a data visualization tool that shows how an initial value changes through a series of intermediate steps (increases or decreases) to reach a final value. It is often represented as a series of bars where each bar represents a ...

      • Create a run chart for company revenue

        Problem Statement The following data represents Apple's quarterly revenue and earnings per share (EPS) from 2007 to 2014. Construct a time series plot of Apple's revenue by quarter. Create a run chart for the revenue over time and determine if the ...

      • Bar chart frequently asked questions

        What is a bar chart? A bar chart is a graphical representation of data where individual bars represent different categories or groups. The length or height of each bar is proportional to the value or frequency of the category it represents. When ...

      • Pie chart frequently asked questions

        What is a pie chart? A pie chart is a circular chart divided into slices to illustrate numerical proportions. Each slice represents a category’s contribution to the whole, with the size of each slice proportional to the value it represents. When ...