선입선출법에 따른 재고월 구하기

1. 문제

아래와 같이 월별 매입수량과 매출수량이 있을 때 선입선출법에 따라 남는 매입월(재고월)이 어떻게 되는지 구해보려고 합니다.

예를 들어 A품목의 경우 매입이 워낙 많고, 매출은 겨우 158개로 기초 재고도 소진하고 있지 못하며,

F품목의 경우는 5월까지의 매출 수량이 1,031개로 기초재고 1000개를 소진하고, 2월 매입 물량 34개 중 31개를 소진하고 3개가 남게 되므로 재고 월은 2월이 됩니다.

장기재고 수식(재고월 계산)(문제).xlsx

0.01MB

 

 

2. 해법

매입물량과 매출물량을 비교해서 매입물량이 최종적으로 매출물량을 커버하고 남는 달이 재고월이 됩니다.

따라서, 매입물량도 더해가고, 매출물량도 더해가야 하며, 수량이 초과되는지 여부를 판단해야 하므로 기존까지의 Match함수와 sum함수로는 처리하기가 너무 복잡해지는데,

Microsoft 365에 도입된 Scan 함수를 이용하면 누계를 하고, 비교하는 것을 한 번에 처리할 수 있으므로 너무 편리합니다.

 

가. Scan 함수의 구문

=SCAN ([initial_value], array, lambda(accumulator, value))

initial_value는 초기값으로 보통은 0이 되고, 생략가능합니다.

array는 배열로, lambda 함수로 처리할 값(value)의 범위가 됩니다. 다시 말해 array가 하나씩 lambda함수의 value로 대입됩니다.

accumulator는 누산기란 뜻으로, 초기값(initial_value)이 lambda함수식에 따라 처리된 후 반환될 값이 됩니다.

 

나. Scan 함수를 이용한 수식

매출수량 아래에 재고월이라는 표를 하나 추가하고,

C32셀에 =SCAN(0,$B3:C3,LAMBDA(s,c,s+c)) 이라고 입력하면

B3셀인 기초재고부터 C3인 1월까지의 매입수량 누계인 465와 579가 구해졌습니다.

 

lambda 수식에서 s는 초기값 0이고, c는 $B3:C3이며, B3셀에서 C3셀까지 이동하면서 계산하는데,

B3셀일 때는 s+c에서 s는 0이고, c는 456이므로 s+c=456이 되며, 이 값이 s값에 대입되고,

C3셀로 이동하면 s+c에서 s는 456이고, c는 123이므로 579가 되고 이것이 s에 대입되므로 579가 된 것입니다.

 

다. 재고월 구하기

이제 매출수량이 매입수량보다 작은 값일 때 월을 구하면 되므로 Match함수를 추가합니다. 그러면 수식은

=MATCH(SUM($C17:C17),SCAN(0,$B3:C3,LAMBDA(s,c,s+c)),1)이 됩니다.

 

위 수식에서 SUM($C17:C17)은 1월까지의 매출 누계 물량이며,

SCAN(0,$B3:C3,LAMBDA(s,c,s+c))은 위에서 구한 매입 물량 누계,

 

,1은 Match함수의 세 번째 인수로 작은 값 중 최댓값을 반환해 주기 위해 1로 입력한 것입니다.

 

위에서 F품목의 예를 들어 설명하면

5월에 매출물량 누계가 1,031개이고, 매입물량은 2월까지가 기초재고 1000 + 34로 가장 근접하므로 2월이 재고월이 되는 것입니다.

 

그런데, 대부분의 값이 #N/A입니다. 이것은 매출수량이 기초재고도 소진하지 못할 정도의 물량이기 때문에 그런 것입니다.

 

라. IfError 함수 추가

IfError 함수는 수식이 에러일 때 값을 정해주는 함수로 위와 같이 에러가 나는 경우는 기초 재고이므로 0(월)이라고 표시하면 됩니다.

 

따라서, 완성된 수식은

=IFERROR(MATCH(SUM($C17:C17),SCAN(0,$B3:C3,LAMBDA(s,c,s+c)),1),0)가 되고,

결과 화면은 아래와 같습니다.

 

B품목의 경우 9월에 5월로 표시되는데,

9월까지의 매출누계가 724개이고,

매입수량은 4월까지가 672이므로 이걸로는 부족하므로 5월 매입량 534를 더해야 합니다. 따라서, 재고월이 5월이 되는 것입니다.

 

장기재고 수식(재고월 계산)(해법).xlsx

0.02MB