안녕하세요. 일상에서 얻는 정보를 모두 기억할 수 없기 때문에 일상에서 일어나는 에피소드들의 정보들을 기록하여 기억하고자 하는 시점에 기록된 정보를 활용할 수 있도록 글을 쓰고자 하는 이작가입니다.
저 역시 지나간 모든 과거의 경험들을 전부 기억할 수 없기 때문에 경험을 통해 얻은 지식과 정보를 기록함으로써 필요한 시점에 과거의 경험의 정보를 활용해 보기 위해 이 글을 작성합니다.
기록된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다.
엑셀(excel) 작업으로 판매일보를 관리 하거나 작성하고 판매 금액 정산까지 계산하고 관리 하는 서식을 이번 내용에서 간단하게 작성해 보고 입력된 데이터 값을 참조 하여 정산까지 계산 할 수 있는 수식을 작성해 보고자 합니다.
1. 판매 일보 간단 서식.
판매일보는 오픈마켓을 예상하며 간단한 서식으로 작성해 보았습니다.
오픈마켓은 자신의 직접 홈페이지 온라인몰을 운영하는것이 아니고 G마켓, 11번가, 쿠팡등의 상품 또는 제품 판매 사이트 플랫폼에 입점하여 자신의 제품을 판매 하는 온라인몰 또는 쇼핑몰을 운영하는것이기 때문에 판매수수료, 정산 수수료등과 같이 공제 금액이 발생되고 정산 또한 바로바로 이루어지지 않기 때문에 판매 금액 또는 수익 정산 예정 금액을 어느정도 파악하고 있어야 예산을 계획할 수 있다는점에서 판매 일보를 작성하는것이 좋을것입니다.
판매일보 간단 샘플 서식은 총 3개의 시트로 판매내역, 판매정산내역, 참조시트로 구성하여 작성해 보았고 각각의 시트는 다음과 같은 내용으로 적용해 보았습니다.
1-1. 판매내역 시트.
판매 내역 시트에는 일자별로 판매된 내역을 직접 입력하는 시트로 B열부터 H열까지 직접 입력해 주고 나머지 셀은 수식으로 작성하여 직접 입력된 데이터 값을 참조 하여 계산하고자 하는 데이터 값이 자동으로 입력 되도록 작성해 보았습니다.
직접 입력 데이터 값은 판매일자, 판매처, 구매자이름, 제품명 또는 상품명, 주문 수량, 주문금액, 배송비를 입력 할 수 있습니다.
A열은 B열에 입력한 날짜의 월만 추출하여 값이 입력되도록 하고, i열은 정산 예정 금액, J열은 영업이익, K열은 주문금액에 대한 공급가액, L열은 부가세액, M열은 판매 수수료를 수식에 의한 계산 결과값이 표기 되도록 합니다.
수식으로 작성된 셀의 수식은 다음과 같이 2행의 셀을 기준으로 작성 할 수 있고 필요한 행까지 수식 복사하여 사용할 수 있습니다.
| ① A열 / 월. =IF($B2="","",MONTH(B2)) ② i열 / 정산 예정 금액. =SUM(K2-M2) ※ 공급가액 - 판매수수료 ③ J열 / 영업 이익. =SUM(I2-H2-L2) ※ 정산 예정 금액 - 배송비 - 부가세액 ④ K열 / 공급가액 =ROUND(SUM(G2/1.1),0) ※ 주문금액 / 1.1 (소수점 첫째자리에서 반올림 후 소수점 절사) ⑤ L열 / 부가세액 =SUM(G2-K2) ※ 주문금액 - 공급가액 ⑥ 판매수수료 =(VLOOKUP(판매내역!$C2,참조!$A$2:$B$7,2,FALSE)/100)*G2 ※ 참조시트의 각 오픈마켓 판매 수수료 참조. |
1-2. 판매정산내역 시트.
판매정산내역 시트에는 판매내역 시트에 입력된 내역을 바탕으로 A1셀에는 년도, B1셀에는 월을 입력하면 판매내역에 작성된 내용을 해당 연도와 월의 내역을 수식에 의해서 모두 자동으로 데이터 값이 입력되는 셀입니다.
판매정산내역 시트를 작성해본 이유로는 판매내역을 월별 또는 1년단위로 작성할 경우 각 오픈마켓에서의 판매 금액, 정산금액, 그리고 확인하고자 하는 년월의 판매일자별 데이터를 한눈에 보기 위한 시트로 실제 총 판매 금액과 공급가액, 부가세액을 확인할 수 있기 때문에 해당 월의 총 이익과 비용을 계산하기 위한 시트로 작성해 보았습니다.
각각의 수식은 시작 첫셀을 기준으로 작성하며 수식은 다음과 같이 작성 할 수 있습니다.
| 1. A1셀 직접 데이터 입력값으로 확인하고자 하는 년도를 입력. 2. B1셀 직접 데이터 입력값으로 확인하고자 하는 월을 입력. 3. B3셀, C3셀, D3셀, E3셀 확인하고자 하는 년도와 월의 각 오픈마켓 총 판매 금액 / 정산예정금액 / 공급가액 / 부가세액 =SUM(B4:B9) / =SUM(C4:C9) / =SUM(D4:D9) / =SUM(E4:E9) 4. B4, 5 6 7 8 9셀 B4셀 / =SUM($M$4:$M$50) B5셀 / =SUM($N$4:$N$50) B6셀 / =SUM($O$4:$O$50) B7셀 / =SUM($P$4:$P$50) B8셀 / =SUM($Q$4:$Q$50) B9셀 / =SUM($R$4:$R$50) 5. C4셀 수식 작성 후 수식 복사 =SUMIFS(판매내역!$I$2:$I$1000, 판매내역!$A$2:$A$1000,판매정산내역!$B$1, 판매내역!$C$2:$C$1000,판매정산내역!$A4) 6. D4셀 수식 작성 후 수식 복사 =SUMIFS(판매내역!$K$2:$K$1000, 판매내역!$A$2:$A$1000,판매정산내역!$B$1, 판매내역!$C$2:$C$1000,판매정산내역!$A4) 7. E4셀 수식 작성 후 수식 복사 =SUMIFS(판매내역!$L$2:$L$1000, 판매내역!$A$2:$A$1000,판매정산내역!$B$1, 판매내역!$C$2:$C$1000,판매정산내역!$A4) 8. G셀 =IFERROR(DATE(A1,B1,1),"") 9. G5셀 수식 작성 후 수식 복사 =IFERROR(IF(MONTH(G4+DAY(1))=MONTH(G4)+1,"",G4+DAY(1)),"") ※ 8번 9번 참조 글 엑셀 날짜 시작일자부터 종료일자까지 자동으로 입력 하는 수식 작성 방법. 10. H4셀 수식 작성 후 수식 복사 =SUM(M4:R4) 11. i4셀 수식 작성 후 수식 복사 =SUMIFS(판매내역!$M$2:$M$1000, 판매내역!$B$2:$B$1000,판매정산내역!$G4) 12. J4셀 수식 작성 후 수식 복사 =SUMIFS(판매내역!$K$2:$K$1000, 판매내역!$B$2:$B$1000,판매정산내역!$G4) 13. K4셀 수식 작성 후 수식 복사 =SUMIFS(판매내역!$L$2:$L$1000, 판매내역!$B$2:$B$1000,판매정산내역!$G4) 14. L4셀 수식 작성 후 수식 복사 =SUMIFS(판매내역!$H$2:$H$1000, 판매내역!$B$2:$B$1000,판매정산내역!$G4) 15. M4셀 수식 작성 후 수식 복사 =SUMIFS(판매내역!$G$2:$G$1000, 판매내역!$B$2:$B$1000,판매정산내역!$G4, 판매내역!$C$2:$C$1000,판매정산내역!M$2) 16. N4셀 수식 작성 후 수식 복사 =SUMIFS(판매내역!$G$2:$G$1000, 판매내역!$B$2:$B$1000,판매정산내역!$G4, 판매내역!$C$2:$C$1000,판매정산내역!N$2) 17. O4셀 수식 작성 후 수식 복사 =SUMIFS(판매내역!$G$2:$G$1000, 판매내역!$B$2:$B$1000,판매정산내역!$G4, 판매내역!$C$2:$C$1000,판매정산내역!O$2) 18. P4셀 수식 작성 후 수식 복사 =SUMIFS(판매내역!$G$2:$G$1000, 판매내역!$B$2:$B$1000,판매정산내역!$G4, 판매내역!$C$2:$C$1000,판매정산내역!P$2) 19. Q4셀 수식 작성 후 수식 복사 =SUMIFS(판매내역!$G$2:$G$1000, 판매내역!$B$2:$B$1000,판매정산내역!$G4, 판매내역!$C$2:$C$1000,판매정산내역!Q$2) 20. R4셀 수식 작성 후 수식 복사 =SUMIFS(판매내역!$G$2:$G$1000, 판매내역!$B$2:$B$1000,판매정산내역!$G4, 판매내역!$C$2:$C$1000,판매정산내역!R$2) 21. 3행 H열 ~ R열 =SUM(합계 범위) |
크게 어려운 수식은 없고 sum함수와 sumifs함수가 주된 함수로 작성된 수식의 결과값이며, 참조할 범위만을 염두해 두고 수식을 작성합니다.
따라서 샘플 예제 서식으로 사용자의 환경에 맞게 서식을 수정하고 추가 할 경우 추가되는 서식셀의 수식은 참조할 범위를 고려하여 수식을 작성할 수 있도록 하고, 8번, 9번의 수식은 해당월의 시작일자부터 종료일자까지 자동으로 결과값을 표기 하는 작성 방법에 대해 이전에 작성한 글을 함께 참고해 보는것이 좋겠습니다.
엑셀(Excel) 입력한 날짜의 해당 월 시작일부터 종료일까지 자동으로 작성되는 수식을 작성해 보자.
1-3. 참조 시트.
참조 시트는 직접 데이터를 입력하는 시트로 각 오픈마켓 판매처마다의 수수료를 입력하여 판매내역 시트와 판매정산내역 시트에서 판매수수료를 계산할때 참조 할 수 있는 참조값을 입력해 주도록 하고
샘플 예제 서식에서는 사용하지는 않았지만 제품마다의 제품 단가로 제품 공급가액과 제품 부가세액을 입력을 하게 되면 판매내역 시트에서 제품 공급가액과 부가세액을 가져올 수 있어 실제 영업이익과 예정 부가세액을 조금 더 구체적으로 계산할 수 있을것입니다.
■ 샘플 예제 서식에서는 오픈마켓 또는 쇼핑몰을 예상하여 간단한 판매일보 서식을 작성해 보았습니다만 간단한 서식이라도 쇼핑몰 또는 온라인몰뿐만 아니라 오프라인 영업자분들이 활용할 수 있는 서식또한 충분히 해당 서식을 참고 하여 작성할 수 있을것입니다.
또한 추가로 계산하고자 하는 데이터 값을 수식에 의해 계산하고 산출해 냄으로써 조금 더 구체적인 숫자와 데이터 관리를 할 수 있고 판매 내역을 바탕으로 다양한 분석을 시도해 볼 수 있을것입니다.
이상으로 "글쓰는이작가의 엑셀(Excel) 시간"은 마무리 하고,
"판매일보 간단 서식과 정산 예정 금액 계산 수식 작성" 예제 샘플을 하단에 첨부하였으니 필요한곳에서 활용할 수 있는 수식으로 적용해 보시길 바라겠습니다.
긴 글 읽어주신 여러분들께 감사 말씀 드리며 많은 도움이 될 수 있기를 바라겠습니다.
※ 판매일보 간단 서식과 정산 예정 금액 계산 수식 작성 샘플 예제 서식 다운로드.
'엑셀(Excel)' 카테고리의 다른 글
| 엑셀(Excel) 현재 날짜와 시간 바로 입력 하는 단축키. (0) | 2022.11.25 |
|---|---|
| 엑셀(Excel) 행과 열을 추가 하는데 다른 행과 열의 표 또는 테이블에는 영향을 주지 않고 추가 또는 삭제 하는 방법. (0) | 2022.11.25 |
| 엑셀(Excel) 근속 기간에 따라 수당 차등 지급 계산 수식 작성을 해보자. (0) | 2022.11.24 |
| 엑셀(Excel) 정해진 데이터 값 이외 데이터 값 입력 방지 드롭다운 목록 만들기 엑셀 데이터 유효성 검사 기능을 사용해 보자. (0) | 2022.11.24 |
| 엑셀(Excel) 경력기간 또는 00년00월00일 + 00년00월00일 날짜 합산 텍스트가 포함된 날짜 합계 수식 작성을 해보자. (1) | 2022.11.24 |
댓글