A recent situation created in my office when a vendor rushed into my office and started shouting. He was annoyed by the non-serious behavior of our purchasing staff. When my boss started addressing his grievances he claimed that his payments have been pending for long period of time, even more then the credit period in the purchase order.
The situation was later analyzed to see who actually is responsible for the delay in payments. So we started by answering following question:
How much amount in Rs. is pending?
How many bills are pending for payment?
How much amount (and bills) is actually due by now?
What is the share of each purchaser
We start with the following dummy data, just few entries to elaborate the process. (The data has been produced using DK Data Generator, freely available from internet).
Creating the Basic Table
Q1 =SUMPRODUCT(($E$6:$E$20=B23)*($C$6:$C$20))
Q2 =SUMPRODUCT(($E$6:$E$20=B23)*1)
Q3 =SUMPRODUCT(($E$6:$E$20=B23)*($F$6:$F$20="DUE")*($C$6:$C$20))
Q4 =SUMPRODUCT(($E$6:$E$20=B23)*($F$6:$F$20="DUE")*1)
Creating Frequency Bin:
A frequency charts simply a frequency histogram to plot the situation. Following formulas has been used.
Getting Count =SUMPRODUCT(($G$6:$G$20>=C30)*($G$6:$G$20<=D30)*1)
Creating Bars =REPT("g",E30)
Interpretation:
Hope that this posts explained something useful! Take care
The situation was later analyzed to see who actually is responsible for the delay in payments. So we started by answering following question:
How much amount in Rs. is pending?
How many bills are pending for payment?
How much amount (and bills) is actually due by now?
What is the share of each purchaser
We start with the following dummy data, just few entries to elaborate the process. (The data has been produced using DK Data Generator, freely available from internet).
Creating the Basic Table
Q1 =SUMPRODUCT(($E$6:$E$20=B23)*($C$6:$C$20))
Q2 =SUMPRODUCT(($E$6:$E$20=B23)*1)
Q3 =SUMPRODUCT(($E$6:$E$20=B23)*($F$6:$F$20="DUE")*($C$6:$C$20))
Q4 =SUMPRODUCT(($E$6:$E$20=B23)*($F$6:$F$20="DUE")*1)
Creating Frequency Bin:
A frequency charts simply a frequency histogram to plot the situation. Following formulas has been used.
Getting Count =SUMPRODUCT(($G$6:$G$20>=C30)*($G$6:$G$20<=D30)*1)
Creating Bars =REPT("g",E30)
Interpretation:
The interpretation is easy. You can see that out of the total
outstanding bills to be paid, Mr. A has contributed the most in accumulating
these bills. While Mr. D is leading for the case of the bills that are actually
due and need to be paid. Both of these guys need to work really hard over
billing. The histogram reveals another critical piece of information. Most of
the bills have been dealed well over the credit limit i.e. 45 days. The avg.
pending time is about 150 days (because this is not an ideal bell-shaped curve
and the histogram is more looks a square). So we can understand why people
rushed in our office and shouted.
Download the File: Store Performance
Download the File: Store Performance
Hope that this posts explained something useful! Take care
