Biplab4444
New Member
Can any one help me to calculate closing stock valuation and profit. Please find the attached template.
FIFO Method
- Thread starter Biplab4444
- Start date
NARAYANK991
Excel Ninja
Hi ,
In such applications , you need to upload a workbook which has as much variety as possible ; 3 rows of data are not sufficient.
Can you upload data which spans at least a few months , and has data spanning 50 to 100 rows ?
Narayan
In such applications , you need to upload a workbook which has as much variety as possible ; 3 rows of data are not sufficient.
Can you upload data which spans at least a few months , and has data spanning 50 to 100 rows ?
Narayan
Biplab4444
New Member
Biplab4444
New Member
Thanks for your input, Please find the file with one months data
NARAYANK991
Excel Ninja
Hi ,
Can you explain how you get 22,500 for the first outflow of 15 units ?
If we take the FIFO principle , then it should be as follows :
10 units - 1200/unit - 12,000
5 units - 1500/unit - 7,500
for a total of 19,500.
Narayan
Can you explain how you get 22,500 for the first outflow of 15 units ?
If we take the FIFO principle , then it should be as follows :
10 units - 1200/unit - 12,000
5 units - 1500/unit - 7,500
for a total of 19,500.
Narayan
Biplab4444
New Member
Hi....
Thanks Again
You are absolutely correct
But here I want the value of renaming quantities i.e 15*1500 = 22500 [At that point we have 15 units of 1500 value]
It would be better for me if you add an extra column as outflow and show your described calculation as well as my three fields [i.e Closing Stock Unit,Total and Profit]
Biplab4444
New Member
Try......
1] Insert a new Column H for "Cost of Goods sold"
2] In "Purchase" C2, enter : 0
and C2 >> Format cell >> Custom >> enter the Type box in :
;;"NAV"
3] In "Cost of Goods Sold" H3, formula copy down :
=SUMPRODUCT((SUM(E$3:E3)>SUMIF(OFFSET($B$2,,,ROW(B$1:B1)),">0"))*(SUM(E$3:E3)-SUMIF(OFFSET(B$2,,,ROW(B$1:B1)),">0"))*(C$3:C3-C$2:C2))-SUM(H$2:H2)
4] In "Closing Stock, Unit" I3, formula copy down :
=N(I2)+B3-E3
5] In "Closing Stock, Total" J3, formula copy down :
=N(J2)+D3-H3
6] In "Profit" K3, formula copy down :
=G3-H3
Regards
Bosco
1] Insert a new Column H for "Cost of Goods sold"
2] In "Purchase" C2, enter : 0
and C2 >> Format cell >> Custom >> enter the Type box in :
;;"NAV"
3] In "Cost of Goods Sold" H3, formula copy down :
=SUMPRODUCT((SUM(E$3:E3)>SUMIF(OFFSET($B$2,,,ROW(B$1:B1)),">0"))*(SUM(E$3:E3)-SUMIF(OFFSET(B$2,,,ROW(B$1:B1)),">0"))*(C$3:C3-C$2:C2))-SUM(H$2:H2)
4] In "Closing Stock, Unit" I3, formula copy down :
=N(I2)+B3-E3
5] In "Closing Stock, Total" J3, formula copy down :
=N(J2)+D3-H3
6] In "Profit" K3, formula copy down :
=G3-H3
Regards
Bosco
Biplab4444
New Member
bobhc
Excel Ninja
Checked the search box and it does work,Search result
http://chandoo.org/forum/search/1954259/?q=FIFO+Method&o=date&c[node]=4
http://chandoo.org/forum/search/1954259/?q=FIFO+Method&o=date&c[node]=4
Another option being without helper column (Cost of Goods Sold)
1] In "Purchase" C2, enter : 0
and, C2 >> Format cell >> Custom >> in the Type box enter :
;;"NAV"
2] In "Closing Stock, Unit" H3, formula copy down :
=N(H2)+B3-E3
3] In "Closing Stock, Total" I3, formula copy down :
=SUMPRODUCT((((SUMIF(OFFSET(B$3,,,ROW(B$1:B1)),">0")-SUM(E$3:E3))*(SUM(E$3:E3)<SUMIF(OFFSET(B$3,,,ROW(B$1:B1)),">0")))-(SUM(E$3:E3)<SUMIF(OFFSET(B$2,,,ROW(B$1:B1)),">0"))*(SUMIF(OFFSET(B$2,,,ROW(B$1:B1)),">0")-SUM(E$3:E3))),C$3:C3)
4] In "Profit" J3, formula copy down :
=G3+SUM(G$2:G2)-SUMPRODUCT((SUM(E$3:E3)>SUMIF(OFFSET(B$2,,,ROW(B$1:B1)),">0"))*(SUM(E$3:E3)-SUMIF(OFFSET(B$2,,,ROW(B$1:B1)),">0"))*(C$3:C3-C$2:C2))-SUM(J$2:J2)
Regards
Bosco