Progressive Pricing Explained (Using FIFO Approach):
Today I will explain how to use a Progressive Pricing formula to Calculate the Value of certain goods purchased at different price level.
Lets consider this problem: You are a Purchase Manager how purchase Chocolates from different suppliers. In your inventory is present a stock of 15000 Kg of Chocolates that is issued to the factory on FIFO basis. FIFO means that the stock that is purchased first will be issued first. Now you want to calculate how much worth chocolate has been issued to the factory.
A manual practice will required you to multiply the issue with the stock issued for each price level. The process is not hectic if the number of suppliers are small, but what if it goes to multiple dozen?? The process can be simplified if you use spreadsheet for the purpose.
Example: Consider following Table. It contains the Suppliers, Items, Qtty, Rate & Cum. The first four columns are self explanatory, the last column is the Accumulate sum of the total chocolates present in the stock. This column will facilitate the working of the formula we are going to discuss.
Download this Excel Sheet and it will facilitate the learning process.
In Cell H1 i have entered the Qtty of the Chocolates issued and in G5 entered the following Formula:
=IF($H$2-E5<1,IF($H$2+C5-SUM($C$5:C5)<1,0,$H$2+C5-SUM($C$5:C5)),C5)
The first part of the outer IF() check whether the stock under consideration is exhaustive to full fill the demand. In out case, it is 2000 against the qtty issued (14751) so the argument evaluates to False, Since it evaluates to false, the second condition of the IF() is the result that we find in the cell G5.
The Same process continues till we reach Cell G10. In Cell G10 the stock of chocolates is 3000 Kg while we have already issued 12000 Kg from previous suppliers (The 12000 Kg is evident from the Cumulative Qtty Column). We will be issuing not all 3000 Kg but just 2751 to make it to 14751 Kg.
=IF($H$2-E10<1,IF($H$2+C10-SUM($C$5:C10)<1,0,$H$2+C10-SUM($C$5:C10)),C10)
At this point the first part of the outer IF() will return True when it evaluates $H$2-E10<1, here it will be less then 1, triggering the True portion of the First IF(). The Second IF() will ensure that we get only 2751 out of 3000 this way:
$H$2+C10-SUM($C$5:C10)<1 = 14751 + 3000 - 15000 = 17751 - 15000 = 2751
Since the statement is False, the second of the the IF() will be evaluated to give you 2751 in G10, had it been True, we would have got Zero in it, just like we get it in Cell G11.
The Formula in Column H5 Simply Multiply the quantity with the respective rate to give it to you the total amount of chocolate issued
I will try to make another post explain a single-formula for the whole process. Hopefully in that approach, we will not be needing this table at all.
Fell free to give feedback on this post.
Today I will explain how to use a Progressive Pricing formula to Calculate the Value of certain goods purchased at different price level.
Lets consider this problem: You are a Purchase Manager how purchase Chocolates from different suppliers. In your inventory is present a stock of 15000 Kg of Chocolates that is issued to the factory on FIFO basis. FIFO means that the stock that is purchased first will be issued first. Now you want to calculate how much worth chocolate has been issued to the factory.
A manual practice will required you to multiply the issue with the stock issued for each price level. The process is not hectic if the number of suppliers are small, but what if it goes to multiple dozen?? The process can be simplified if you use spreadsheet for the purpose.
Example: Consider following Table. It contains the Suppliers, Items, Qtty, Rate & Cum. The first four columns are self explanatory, the last column is the Accumulate sum of the total chocolates present in the stock. This column will facilitate the working of the formula we are going to discuss.
Download this Excel Sheet and it will facilitate the learning process.
In Cell H1 i have entered the Qtty of the Chocolates issued and in G5 entered the following Formula:
=IF($H$2-E5<1,IF($H$2+C5-SUM($C$5:C5)<1,0,$H$2+C5-SUM($C$5:C5)),C5)
The first part of the outer IF() check whether the stock under consideration is exhaustive to full fill the demand. In out case, it is 2000 against the qtty issued (14751) so the argument evaluates to False, Since it evaluates to false, the second condition of the IF() is the result that we find in the cell G5.
The Same process continues till we reach Cell G10. In Cell G10 the stock of chocolates is 3000 Kg while we have already issued 12000 Kg from previous suppliers (The 12000 Kg is evident from the Cumulative Qtty Column). We will be issuing not all 3000 Kg but just 2751 to make it to 14751 Kg.
=IF($H$2-E10<1,IF($H$2+C10-SUM($C$5:C10)<1,0,$H$2+C10-SUM($C$5:C10)),C10)
At this point the first part of the outer IF() will return True when it evaluates $H$2-E10<1, here it will be less then 1, triggering the True portion of the First IF(). The Second IF() will ensure that we get only 2751 out of 3000 this way:
$H$2+C10-SUM($C$5:C10)<1 = 14751 + 3000 - 15000 = 17751 - 15000 = 2751
Since the statement is False, the second of the the IF() will be evaluated to give you 2751 in G10, had it been True, we would have got Zero in it, just like we get it in Cell G11.
The Formula in Column H5 Simply Multiply the quantity with the respective rate to give it to you the total amount of chocolate issued
I will try to make another post explain a single-formula for the whole process. Hopefully in that approach, we will not be needing this table at all.
Fell free to give feedback on this post.
No comments:
Post a Comment