“개인 사업자 주목” 매출 추이, 세금 계산 등 엑셀 백분율 공식 완전 정복

엑셀의 백분율 공식은 세금 결정에서부터 증감분 계산에 이르기까지 일상적인 크고 작은 문제들을 해결해 준다. 분수를 백분율로 변환하기, 총 판매 금액 중 세금 계산하기, 총액 백분율, 백분율 증감분, 완료 백분율 구하기 등 몇 가지 사례를 소개한다.

분수를 백분율로 변환하기
백분율은 100을 기준으로 했을 때 차지하는 부분이다. 백분율을 구하려면 분사(분수 중 위에 있는 숫자)를 분모(분수 중 아래에 있는 숫자)로 나눈 값에 100을 곱하면 된다. 예를 들어, 6/12을 소수로 나타내면 6을 12로 나눈 값인 0.5인데, 여기에 100을 곱하면 50%라는 백분율이 나온다.
엑셀에서 분수를 백분율로 변환하려면 함수는 필요없고 서식 변경만 하면 된다. 예를 들면 다음과 같다.

1. A열(A2~A11)에 분수 10개를 입력한다. 참고로, 엑셀에서는 분수가 기약 분수로 자동 변환된다. 예를 들어, 6/10은 3/5로 자동으로 바뀐다. 엑셀 기본 값은 소수이기에 분수로 나타내려면 해당 범위를 선택한 다음 ‘표시 형식’의 드롭다운 메뉴를 클릭해 ‘분수’ 서식을 지정해야 한다.

2. A열의 분수를 B열로 복사한다.

3. 범위를 선택한 뒤 홈 탭의 ‘표시 형식’에서 드롭다운 메뉴를 선택한 뒤 ‘백분율’을 클릭한다.

총 판매 금액 중 세금 계산
어떤 회사들은 제품 가격에 세금을 포함시켜 판매한 후 나중에 세금을 따로 계산해 국세청에 납부한다. 세전 가격 계산 방법은 판매하는 가격(또는 총 영수액)을 1.0에 세율을 더한 숫자로 나누면 된다. 예를 들어, 지역 판매세율이 9%인 곳에서 50달러에 산 전등의 실제 소매가는 50달러를 1.09로 나눈 값인 45.87달러다. 따라서 이 제품의 세금은 50달러에서 소매가를 뺀 4.13달러다. 두 숫자를 더해서 다시 50달러가 나오면 된다. 판매세만 따로 확인하려면 45.87달러에 9%를 곱해 보면 된다.

품목이 한 개 뿐이면 계산기를 사용해도 되지만, 주간 또는 월간 제품 판매 내역에서 세금을 계산하려면 공식을 한 번만 입력해두고 이를 전체 판매 및 재고 시트에 복사하면 된다.

1. A열(A2~A14)에 12개 정도의 제품을 입력한다.

2. B열(B2~B14)에 해당 총 영수 가격(세금 포함)을 입력한다.

3. C열(C2~C14)에 임의의 판매세 백분율을 여러 개 입력한다. (여러 숫자로 실험해 조기 위해서다). 소숫점 이하의 숫자가 있는 빅분율(예 4.75%)을 입력해본다. 대부분의 세율은 정수로 떨어지지 않기 때문이다.

4. D2셀에 =SUM(B2/(C2+1))을 입력한다. 이 공식의 목적은 세율을 정수 제수로 변환(예: 9%를 1.09)한 다음 총 영수 가격(198.56달러)을 정수 제수(1.09)로 나누어 정확한 소매 가격(세전, 182.17달러)을 구하는 것이다.

5. D2에서 D14까지 공식을 복사한다.

6. E2에는 B2에서 D2를 빼(=SUM(B2-D2))서 국세청에 납부할 실제 세금을 계산한다. E2에서 E14까지 공식을 복사한다.

7. 구한 값을 재차 확인하기 위해 F2에 =SUM(D2*C2)를 입력하고, F14까지 복사한다. E열과 F열이 일치하면 데이터가 정확한 것이다.

총액 백분율
자영업자나 재택 근무자에 대한 국세청 공제(임대료, 공과금, 주택 유지보수 비용 중 사무실 부분)는 주책 총 면적에서 사무실 면적을 차감하는 방식으로 결정된다. 이들 총액의 백분율에 해당하는 금액을 공제받을 수 있다. 먼저 사무실 면적을 주택 총면적으로 나눈 값의 백분율을 기준으로 간접비를 계산한다.

1. 주택 총 면적을 B2에 입력한다.

2. 사무실 총 면적을 C2에 입력한다.

3. D2에 =SUM(C2/B2)을 입력해 사무실 면적 백분율(이 경우 25%)을 구한다.

4. 아래쪽으로 A열에 주택 및 사무실 간접비 품목(임대료, 전기료 등)을 입력한다.

5. B열에 각 품목의 월간 비용을 입력한다.

6. C5에 =SUM(B5*12)를 입력하고 C12까지 복사해 연간 총액을 구한다.

7. D5에 =SUM(C5*$D$2)를 입력하고 D12까지 복사한다. D2는 절대값이어야 한다. 공식을 절대적으로 만드는 달러 기호는 함수 키 F4를 사용해서 추가할 수 있다. 그러면 D열에 각 셀에 D2가 곱해진다.

8. 13번 행에 B열, C열, D열의 총합을 구한다.

이제 주택 전체에 사용한 것과 사무실에서만 사용한 월간 및 연간 간접비를 파악할 수 있다. D13에 가정 사무실 공제 총액(5,088.60달러)이 표시된다.

9. 총 간접비의 품목별 백분율을 계산하려면 E5에 =SUM(B5/$B$13)을 입력해 E12까지 복사한다. 이들 백분율을 이용해 월간/연간 간접비가 정상 범위에 있는지 판단할 수 있다.

가격 증감 백분율
특히 소매업의 경우 대부분의 사업체의 소유주와 경영자는 매출부터 급여에 이르기까지 거의 모든 것에 대한 증감 백분율을 알고 싶어한다. 다음 공식을 사용하면 회사 내 증감 백분율을 계산할 수 있다.

통합 문서에 ‘Increase-Decrease(증감)’ 탭과 ‘SalesTax(판매세)’ 탭을 만든다. 판매세 탭에는 소매 판매가 정보가 들어간다.

1. 증감 탭의 A열에 12개 정도의 제품 품목을 입력한다.

2. B열과 D열에 각 품목의 판매 수량을 입력한다.

3. 1월 판매 C2에 =SUM(SalesTax!D2*’Increase-Decrease’!B2)을 입력하고 C14까지 복사한다. ‘SalesTax’ 탭의 D열 소매 판매가에 현재 ‘Increase-Decrease’ 탭 B열의 수량을 곱하는 공식이다.

4. 2월 판매 E2에 =SUM(SalesTax!D2*’Increase-Decrease’!D2)을 입력하고 E14까지 복사한다.

5. F2에 =SUM(E2-C2)/C2)를 입력한다. 양수는 1월과 2월 사이의 매출 증가 백분율을 나타내고 음수는 매출 감소 백분율을 나타낸다.

작업 또는 프로젝트 완료 백분율
프로젝트 관리 소프트웨어 프로그램에 돈을 쓰는 대신 다음 공식들을 이용하면 정해진 간격마다 각 프로젝트의 계획과 흐름을 완료 백분율로 관리할 수 있다.

1. A열에 6개 정도의 진행 중인 프로젝트 이름을 입력한다.

2. B열과 C열에 각 프로젝트의 시작일과 종료일을 입력한다.

3. (현재까지) 프로젝트 완료 상태를 판단하기 위해 종료일에서 시작일을 뺀다. D2열에 =SUM(C2-B2)를 입력해 D7까지 복사한다.

4. E열에 현재까지 완료된 일수를 입력한다. 앞으로 변경하게 될 유일한 데이터 열이다. 예를 들면, 이 스프레드시트에 하루 또는 일주일에 한 번 접속해서 이 열의 데이터를 수정하면 F열과 G열(남은 일수와 완료 백분율)에서 정확한 결론을 얻을 수 있다.

5. 각 프로젝트의 남은 일수를 구하려면 F2에 =SUM(D2-E2)을 입력하고 F7까지 복사한다. 이 숫자들은 E열의 숫자(완료된 일수)에 따라 계속해서 변한다.

6. 마지막으로 =SUM(E2/D2)을 입력해 지금까지 완료된 작업/프로젝트의 백분율을 구한다.

editor@itworld.co.kr

JD Sartain editor@itworld.co.kr
저작권자 한국IDG & ITWorld, 무단 전재 및 재배포 금지