엑셀·구글시트 마진율 계산법｜원가와 판매가로 마진 구하기

상품을 판매하다 보면 얼마에 사서 얼마에 팔았고, 그래서 실제로 얼마가 남았는지 계산해야 할 때가 있습니다. 판매가에서 원가를 빼면 남는 금액은 쉽게 구할 수 있지만, 그 금액이 판매가의 몇 %인지 계산하려고 하면 조금 헷갈릴 수 있습니다.

엑셀이나 구글시트에서는 원가와 판매가만 입력해 두면 마진액과 마진율을 자동으로 계산할 수 있습니다. 상품이 많아질수록 매번 계산기를 두드리는 것보다 수식을 한 번 만들어 두는 편이 훨씬 편합니다.

이번 글에서는 원가 30,000원인 상품을 50,000원에 판매하는 예시를 기준으로 마진액과 마진율을 구하는 방법, 원하는 마진율에 맞춰 판매가를 계산하는 방법, 수수료와 배송비까지 포함해 실제로 남는 금액을 확인하는 방법까지 차례대로 살펴보겠습니다.

마진액과 마진율은 어떻게 계산할까?

먼저 마진액과 마진율의 차이부터 알아두면 계산이 훨씬 쉬워집니다.

마진액은 상품을 판매한 금액에서 상품을 사거나 만드는 데 들어간 원가를 뺀 금액입니다.

마진액 = 판매가 - 원가

예를 들어 원가가 30,000원이고 판매가가 50,000원이라면 다음과 같이 계산합니다.

50,000 - 30,000 = 20,000원

즉 50,000원에 판매했을 때 원가를 제외하고 남는 금액은 20,000원입니다.

이번에는 이 20,000원이 판매가 50,000원에서 몇 %를 차지하는지 계산해 보겠습니다. 이것이 판매가 기준 마진율입니다.

마진율 = (판매가 - 원가) ÷ 판매가 × 100

같은 숫자를 넣으면 다음과 같습니다.

(50,000 - 30,000) ÷ 50,000 × 100 = 40%

따라서 원가 30,000원인 상품을 50,000원에 판매하면 마진액은 20,000원이고, 판매가 기준 마진율은 40%입니다.

엑셀·구글시트에서 마진액 계산하기

이제 같은 계산을 엑셀이나 구글시트에 넣어보겠습니다.

예를 들어 표를 아래처럼 만든다고 가정하겠습니다.

• A열 : 상품명
• B열 : 원가
• C열 : 판매가
• D열 : 마진액
• E열 : 마진율

B2 셀에 원가 30,000원, C2 셀에 판매가 50,000원이 입력되어 있다면 D2 셀에는 아래 수식을 넣습니다.

=C2-B2

C2의 판매가에서 B2의 원가를 빼는 수식입니다. 결과는 20,000원으로 표시됩니다.

상품이 여러 개라면 D2 셀에 입력한 수식을 아래쪽 행까지 복사하면 됩니다. 각 행에 입력된 원가와 판매가를 기준으로 마진액이 자동으로 계산됩니다.

엑셀·구글시트에서 마진율 계산하기

마진율은 판매가에서 마진액이 차지하는 비율을 계산하면 됩니다.

B2가 원가이고 C2가 판매가라면 E2 셀에는 아래 수식을 입력합니다.

=(C2-B2)/C2

이 수식은 먼저 판매가에서 원가를 빼 마진액을 구한 뒤, 그 금액을 판매가로 나눕니다.

원가 30,000원, 판매가 50,000원을 넣으면 계산 결과는 0.4가 됩니다. E2 셀의 표시 형식을 백분율(%)로 바꾸면 40%로 표시됩니다.

백분율 표시를 사용하지 않고 숫자 40으로 표시하고 싶다면 수식에 100을 곱할 수도 있습니다.

=((C2-B2)/C2)*100

다만 엑셀이나 구글시트에서는 =(C2-B2)/C2 수식을 사용한 뒤 셀 표시 형식을 백분율로 바꾸는 방식이 더 간단합니다.

마진율과 원가 대비 이익률은 왜 다를까?

마진 계산에서 가장 많이 헷갈리는 부분입니다. 같은 20,000원의 이익이라도 판매가 50,000원을 기준으로 나누는지, 원가 30,000원을 기준으로 나누는지에 따라 퍼센트가 달라집니다.

원가 30,000원, 판매가 50,000원인 상품을 다시 예로 들어보겠습니다.

판매가를 기준으로 계산하면

(50,000 - 30,000) ÷ 50,000 = 40%

판매가 50,000원 중에서 20,000원이 남았으므로 40%입니다.

원가를 기준으로 계산하면

(50,000 - 30,000) ÷ 30,000 = 약 66.7%

이번에는 원가 30,000원과 비교했을 때 이익 20,000원이 얼마나 큰지를 계산한 값입니다.

그래서 같은 상품인데도 하나는 40%, 다른 하나는 약 66.7%가 됩니다. 판매관리표를 만들 때는 단순히 '마진율'이라고만 적기보다 판매가 기준인지 원가 기준인지 표시해 두면 헷갈리지 않습니다.

원하는 마진율에 맞춰 판매가 구하기

이번에는 판매가가 아직 정해지지 않았다고 생각해 보겠습니다.

원가가 30,000원인 상품에서 판매가 기준 마진율을 40%로 만들고 싶다면 얼마에 판매해야 할까요?

마진율이 40%라는 것은 판매가에서 마진이 40%이고, 나머지 60%가 원가라는 뜻입니다.

원가 30,000원이 판매가의 60%에 해당하므로 전체 판매가는 아래처럼 구할 수 있습니다.

판매가 = 원가 ÷ (1 - 목표 마진율)

40%를 소수로 바꾸면 0.4이므로 다음과 같습니다.

30,000 ÷ (1 - 0.4) = 50,000원

엑셀이나 구글시트에서 B2가 원가이고 C2에 목표 마진율 40%가 입력되어 있다면 아래 수식을 사용할 수 있습니다.

=B2/(1-C2)

C2에는 숫자 40이 아니라 40%를 입력합니다. 이렇게 하면 상품마다 원하는 마진율을 정해두고 판매가를 계산할 수 있습니다.

수수료와 배송비까지 빼면 실제로 남는 금액은 달라집니다

온라인에서 상품을 판매한다면 판매가에서 원가만 뺀 금액이 실제 이익과 같지 않을 수 있습니다.

오픈마켓 수수료나 결제 수수료가 빠질 수 있고, 배송비를 판매자가 부담하는 경우도 있기 때문입니다.

예를 들어 아래와 같은 상품이 있다고 하겠습니다.

• 상품 원가 : 30,000원
• 판매가 : 50,000원
• 판매 수수료 : 5,000원
• 판매자 부담 배송비 : 3,000원

원가만 뺀다면 20,000원이 남지만, 수수료와 배송비까지 빼면 실제로 남는 금액은 12,000원입니다.

50,000 - 30,000 - 5,000 - 3,000 = 12,000원

엑셀이나 구글시트에서 B2가 원가, C2가 판매가, D2가 수수료, E2가 배송비라면 실제 마진액은 아래처럼 계산합니다.

=C2-B2-D2-E2

이 12,000원이 판매가 50,000원의 몇 %인지 확인하려면 아래 수식을 사용합니다.

=(C2-B2-D2-E2)/C2

실제로 얼마가 남는지 확인하려면 상품 원가뿐 아니라 수수료와 배송비처럼 판매자가 부담하는 비용도 함께 빼서 보는 편이 정확합니다.

수수료가 10%처럼 비율로 정해져 있다면

판매 수수료가 5,000원처럼 정해진 금액이 아니라 판매가의 10%처럼 계산되는 경우도 있습니다.

예를 들어 판매가가 C2이고 D2에 수수료율 10%가 입력되어 있다면 수수료 금액은 아래처럼 구합니다.

=C2*D2

판매가가 50,000원이고 수수료율이 10%라면 수수료는 5,000원입니다.

B2에 원가가 입력되어 있다면 수수료까지 제외한 마진액은 아래처럼 계산할 수 있습니다.

=C2-B2-(C2*D2)

배송비가 E2에 따로 입력되어 있다면 배송비까지 빼면 됩니다.

=C2-B2-(C2*D2)-E2

상품별로 원가, 판매가, 수수료율, 배송비를 각각 다른 열에 넣어두면 숫자가 바뀌어도 마진액이 자동으로 다시 계산됩니다.

판매가가 비어 있을 때 오류가 나오지 않게 하기

마진율 수식을 여러 행에 미리 넣어두면 아직 판매가를 입력하지 않은 행에서 오류가 표시될 수 있습니다.

이때는 IF 함수와 OR 함수를 함께 사용해 판매가가 없을 때는 계산하지 않도록 만들 수 있습니다.

=IF(OR(C2="",C2=0),"",(C2-B2)/C2)

수식을 나눠서 보면 어렵지 않습니다.

• C2="" : C2가 비어 있는지 확인
• C2=0 : 판매가가 0인지 확인
• 둘 중 하나에 해당하면 빈칸 표시
• 정상적인 판매가가 입력되어 있으면 마진율 계산

상품관리표처럼 빈 행에도 수식을 미리 넣어두는 경우에 사용할 수 있습니다.

IF 함수의 기본 사용법과 AND·OR 조건을 함께 사용하는 방법은 구글시트·엑셀 IF 함수 사용법｜AND·OR 조건 실무 예제 에서 더 자세히 확인할 수 있습니다.

마진 계산표는 어떤 항목으로 만들면 될까?

실제 판매관리용 표를 만든다면 아래 항목부터 시작하면 됩니다.

• 상품명
• 상품 원가
• 판매가
• 판매 수수료 또는 수수료율
• 배송비
• 마진액
• 마진율

상품을 몇 개 판매했는지까지 관리하고 싶다면 판매수량 열을 하나 더 추가할 수 있습니다.

예를 들어 한 개를 판매했을 때 남는 마진액이 F2에 있고 판매수량이 H2에 있다면 총 마진은 아래처럼 계산합니다.

=F2*H2

한 개를 팔 때 12,000원이 남고 10개를 판매했다면 총 마진은 120,000원이 됩니다.

이렇게 표를 만들어 두면 원가나 판매가, 수수료가 바뀌었을 때 실제로 남는 금액도 바로 확인할 수 있습니다.

마진율 계산에서 기억할 공식

처음에는 수식이 많아 보이지만 기본 계산은 아래 세 가지만 기억하면 됩니다.

• 마진액 : 판매가 - 원가
• 판매가 기준 마진율 : (판매가 - 원가) ÷ 판매가
• 원하는 마진율로 판매가 계산 : 원가 ÷ (1 - 목표 마진율)

실제 판매에서는 여기에서 수수료, 배송비, 포장비처럼 판매자가 부담하는 비용을 추가로 빼면 실제로 남는 금액에 더 가까워집니다.

엑셀이나 구글시트에서는 원가와 판매가, 수수료 같은 항목을 각각 다른 열에 입력하고 계산식을 미리 넣어두면 상품이 많아져도 같은 수식을 계속 사용할 수 있습니다.