무료 비영리 보조금 예산 대비 실적·소진율 엑셀 추적기 (수식 설명 포함)

구성

통합 문서에는 시트가 6개 있어요. 입력하는 시트는 몇 개뿐이고 나머지는 계산돼요.

시트	용도	입력하는 것	자동 계산되는 것
Start Here	기준일, 속도 허용치, 포트폴리오 요약	기준일, 속도 허용치	진행 중 보조금, 지원액, 지출, 잔액, 초과·미달 속도 보조금, 예산 초과 항목, 다음 보고 기한과 해당 보조금, 코딩 오류 지출
Grants	지원금당 한 행(10)	ID, 프로그램, 후원자, 시작, 종료, 지원액, 보고 주기	예산, 누적 지출, 잔액, 소진율, 경과 기간 %, 속도, 다음 보고 기한
Budget	보조금·분류별 승인 항목(60)	보조금 ID, 분류, 승인액, 약정액	누적 실적, 가용액, 차이, 사용률
Expenses	보조금에 코딩된 모든 지출(300행)	날짜, 보조금 ID, 분류, 출처, 금액, 참조, 설명	코딩 검사
Lists	드롭다운	분류, 보고 주기, 출처	없음
Checks	데이터 검사 6개	없음	OK 또는 Review

시작하기 전에

지원 통지서를 모으세요. 금액, 기간 시작·종료, 보고 일정이 필요해요.
후원자가 승인한 그대로 분류별 예산을 입력하세요. 분류 이름이 지출 코딩과 일치해야 해요.
회계 시스템에서 보조금 코딩 지출을 날짜, 보조금, 분류, 금액과 함께 내보내세요.

단계별 사용법

기준일과 허용치 설정
Start Here B6은 소진율과 경과 기간을 재는 기준일이에요(=TODAY()를 입력하면 항상 최신). B7은 보조금이 표시되기 전까지 소진율이 경과 기간과 얼마나 달라도 되는지예요(예시 10%).
보조금 등록
Grants 탭에 ID, 프로그램, 후원자, 시작·종료일, 지원액, 보고 주기(드롭다운)를 입력하면 행의 나머지가 계산돼요.
승인 예산 입력
Budget 탭에 보조금·분류별 한 행과 승인액을 입력하세요. E열에 약정액(서명했지만 미지급인 계약)을 넣으면 가용 잔액이 현실적이 돼요.
지출 기록
Expenses 탭에 날짜, 보조금 ID, 분류, 출처, 금액, 참조를 입력하세요. 보조금·분류 조합이 Budget 탭에 있으면 코딩 검사가 OK, 없으면 Check예요.
속도 표시 읽기
Grants L열: Over pace는 일찍 바닥난다는 뜻, Under pace는 돈이 남는다는(또는 무비용 연장이 필요하다는) 뜻이에요. Start Here가 둘 다 세요.
보고 계획
Grants M열이 다음 보고 기한을 주고, Start Here B16–B17이 가장 가까운 것을 보여 줘요. Checks 탭은 코딩 오류 지출, 지원액과 맞지 않는 예산, 기간 밖 지출, 예산 항목 없는 보조금, 날짜나 금액 없는 지출, 중복 ID를 잡아내요.

수식 지도

통합 문서의 모든 수식을 시트와 열별로 정리했어요. 복사된 수식 열은 한 번만 세고, 적용 범위를 함께 표시해요.

시트	셀	함수	계산 내용	읽는 값	쓰이는 곳
Grants	`H5` 14행까지 아래로 복사됨	IF, SUMIFS	누적 지출	A5	잔액, 소진율, 차트가 모두 써요.
Grants	`K5` 14행까지 아래로 복사됨	IF, MAX, MIN	경과 기간 %	D5, E5	소진율과 비교하는 잣대예요.
Grants	`L5` 14행까지 아래로 복사됨	IF	속도	J5, K5	Start Here B13과 B14에서 세고 조건부 서식으로 색을 입혀요.
Grants	`M5` 14행까지 아래로 복사됨	IF, EOMONTH, CEILING	다음 보고 기한	D5, E5, N5	Start Here B16이 기준일 이후 가장 이른 것을 가져가요.
Budget	`D5` 64행까지 아래로 복사됨	IF, SUMIFS	예산 항목별 누적 실적	A5, B5	가용액, 차이, 사용률이 여기서 나와요.
Budget	`F5` 64행까지 아래로 복사됨	IF	가용액	C5, D5, E5	음수면 이미 초과한 항목이에요.
Expenses	`H5` 304행까지 아래로 복사됨	IF, COUNTIFS	코딩 검사	B5, C5	Start Here B18과 Checks 탭에서 세요.
Start Here	`B9`	COUNTIFS	진행 중 보조금	B6	대표 수치예요.
Start Here	`B16`	IFERROR, MINIFS	다음 보고 기한	B6	B17이 어느 보조금인지 찾아요.
Checks	`B7`	IF, SUMPRODUCT, IFERROR, SUMIF	보조금 기간 밖 지출이 없는지	—	후원자는 기간 밖 비용을 불인정해요. 보고서보다 먼저 잡아요.

모든 수식 설명

아래 수식은 파일에서 그대로 가져온 것이라 여기서 읽는 내용이 셀에 들어 있는 내용과 정확히 같아요. 엑셀에서 셀을 클릭하면 수식 입력줄에서 볼 수 있어요.

누적 지출Grants!H5 · 14행까지 아래로 복사됨

=IF(A5="", "", SUMIFS(Expenses!$E$5:$E$304,
    Expenses!$B$5:$B$304, A5,
    Expenses!$A$5:$A$304, "<="&'Start Here'!$B$6))

이 보조금의 기준일 이전 지출을 더해요.

부분별 설명

Expenses!$E$5:$E$304: 금액.
Expenses!$B$5:$B$304, A5: 보조금 ID 일치.
Expenses!$A$5:$A$304, "<="&'Start Here'!$B$6: 기준일 이전 날짜.

예시 데이터로 보면: A5 = G-01 → H5 결과 136,739.44

어떻게 쓰이나: 잔액, 소진율, 차트가 모두 써요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →

경과 기간 %Grants!K5 · 14행까지 아래로 복사됨

=IF(A5="", "", MAX(0, MIN(1, ('Start Here'!$B$6-D5)/(E5-D5+1))))

보조금 시작부터 기준일까지의 일수를 보조금 기간 길이로 나누고 0%와 100% 사이로 제한해요.

부분별 설명

'Start Here'!$B$6-D5: 경과 일수.
E5-D5+1: 기간 일수(양끝 포함).
MIN(1, …): 종료 후에도 100%를 넘지 않음.
MAX(0, …): 시작 전에도 0% 아래로 가지 않음.

예시 데이터로 보면: D5 = 2026년 1월 1일, E5 = 2026년 12월 31일 → K5 결과 84.1%

어떻게 쓰이나: 소진율과 비교하는 잣대예요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 MIN, MAX 함수 사용법 →

속도Grants!L5 · 14행까지 아래로 복사됨

=IF(J5="", "", IF(J5-K5>'Start Here'!$B$7,
    "Over pace", IF(K5-J5>'Start Here'!$B$7, "Under pace", "On pace")))

소진율이 경과 기간을 허용치보다 많이 앞서면 Over pace, 허용치보다 많이 뒤지면 Under pace, 그 외는 On pace.

부분별 설명

J5-K5>'Start Here'!$B$7: 허용치 이상으로 앞섬.
K5-J5>'Start Here'!$B$7: 허용치 이상으로 뒤짐.

예시 데이터로 보면: J5 = 76.0%, K5 = 84.1% → L5 결과 On pace

어떻게 쓰이나: Start Here B13과 B14에서 세고 조건부 서식으로 색을 입혀요.

바꾸는 방법: 수식 대신 Start Here의 허용치를 조정하세요.

배우기: 엑셀 IF 함수 사용법 →

다음 보고 기한Grants!M5 · 14행까지 아래로 복사됨

=IF(A5="", "", IF(N5="Final only",
    E5+30, IF(N5="Monthly", EOMONTH('Start Here'!$B$6, 0)+15, IF(N5="Quarterly", EOMONTH(D5-1, CEILING(('Start Here'!$B$6-D5+1)/91.3125, 1)*3)+30, EOMONTH(D5-1, CEILING(('Start Here'!$B$6-D5+1)/182.625, 1)*6)+30))))

시작일과 주기로 현재 보고 기간의 끝을 구한 뒤 30일(월간은 15일)을 더해요. 분기는 경과 일수 ÷ 91.3의 CEILING이 분기 번호가 되고, 시작 전달부터 분기당 3개월씩 EOMONTH로 기간 끝을 구해요.

부분별 설명

N5="Final only": 최종 보고: 종료일 + 30일.
N5="Monthly": 이번 달 말 + 15일.
CEILING(('Start Here'!$B$6-D5+1)/91.3125, 1): 보조금의 몇 번째 분기인지(반기는 182.625).
EOMONTH(D5-1, 분기*3)+30: 그 분기 말 + 30일.

예시 데이터로 보면: D5 = 2026년 1월 1일, E5 = 2026년 12월 31일, N5 = Quarterly → M5 결과 2027년 1월 30일

어떻게 쓰이나: Start Here B16이 기준일 이후 가장 이른 것을 가져가요.

바꾸는 방법: +30을 후원자 조건에 맞게 바꾸세요. 분기가 보조금 시작이 아닌 고정 달력 기준이면 D5를 그 날짜로 바꾸세요.

배우기: 엑셀 텍스트 함수와 날짜 함수 사용법 →

예산 항목별 누적 실적Budget!D5 · 64행까지 아래로 복사됨

=IF(A5="", "", SUMIFS(Expenses!$E$5:$E$304,
    Expenses!$B$5:$B$304, A5,
    Expenses!$C$5:$C$304, B5,
    Expenses!$A$5:$A$304, "<="&'Start Here'!$B$6))

보조금 합계와 같지만 분류까지 맞춰, 승인 항목마다 자기 지출을 보여 줘요.

부분별 설명

Expenses!$B$5:$B$304, A5: 보조금 일치.
Expenses!$C$5:$C$304, B5: 분류 일치.
Expenses!$A$5:$A$304, "<="&'Start Here'!$B$6: 기준일까지.

예시 데이터로 보면: A5 = G-01, B5 = Personnel → D5 결과 80,641.08

어떻게 쓰이나: 가용액, 차이, 사용률이 여기서 나와요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →

가용액Budget!F5 · 64행까지 아래로 복사됨

=IF(A5="", "", C5-D5-E5)

승인액 − 실적 − 약정액: 이 항목에서 아직 쓸 수 있는 금액.

부분별 설명

C5: 승인액.
-D5: 누적 실적.
-E5: 약정됐지만 미지급.

예시 데이터로 보면: C5 = 96,000, D5 = 80,641.08, E5 = 0 → F5 결과 15,358.92

어떻게 쓰이나: 음수면 이미 초과한 항목이에요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 기본 수식 작성법: 더하기, 빼기, 곱하기, 나누기 →

코딩 검사Expenses!H5 · 304행까지 아래로 복사됨

=IF(A5="", "", IF(COUNTIFS(Budget!$A$5:$A$64, B5, Budget!$B$5:$B$64, C5)=1,
    "OK", "Check"))

이 보조금과 분류의 Budget 행을 세요. 정확히 1이면 승인 항목에 코딩된 거예요.

부분별 설명

Budget!$A$5:$A$64, B5: 보조금 일치.
Budget!$B$5:$B$64, C5: 분류 일치.
=1: 정확히 한 항목.

예시 데이터로 보면: B5 = G-04, C5 = Other direct → H5 결과 OK

어떻게 쓰이나: Start Here B18과 Checks 탭에서 세요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 COUNTIF, COUNTIFS 함수 사용법 →

진행 중 보조금Start Here!B9

=COUNTIFS(Grants!$D$5:$D$14,
          "<="&B6, Grants!$E$5:$E$14,
          ">="&B6)

기간에 기준일이 포함되는 보조금.

부분별 설명

Grants!$D$5:$D$14, "<="&B6: 기준일 이전 시작.
Grants!$E$5:$E$14, ">="&B6: 기준일 이후 종료.

예시 데이터로 보면: B6 = 2026년 11월 4일 → B9 결과 3

어떻게 쓰이나: 대표 수치예요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 COUNTIF, COUNTIFS 함수 사용법 →

다음 보고 기한Start Here!B16

=IFERROR(MINIFS(Grants!$M$5:$M$14,
         Grants!$M$5:$M$14, ">="&B6),
         "")

모든 보조금에서 기준일 이후 가장 이른 보고일.

부분별 설명

Grants!$M$5:$M$14: 다음 보고일.
Grants!$M$5:$M$14, ">="&B6: 오늘 이후 날짜만.
MINIFS(…): 그중 가장 이른 것.

예시 데이터로 보면: B6 = 2026년 11월 4일 → B16 결과 2026년 11월 30일

어떻게 쓰이나: B17이 어느 보조금인지 찾아요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 MIN, MAX 함수 사용법 →

보조금 기간 밖 지출이 없는지Checks!B7

=IF(SUMPRODUCT((Expenses!$A$5:$A$304<>"")*IFERROR((Expenses!$A$5:$A$304<SUMIF(Grants!$A$5:$A$14, Expenses!$B$5:$B$304, Grants!$D$5:$D$14))+(Expenses!$A$5:$A$304>SUMIF(Grants!$A$5:$A$14, Expenses!$B$5:$B$304, Grants!$E$5:$E$14)), 0))=0,
    "OK", "Review")

모든 지출에 대해 SUMIF(ID가 고유하면 조회처럼 동작)로 보조금 시작·종료를 찾아 날짜를 검사해요.

부분별 설명

SUMIF(Grants!$A$5:$A$14, Expenses!$B$5:$B$304, Grants!$D$5:$D$14): 각 지출의 보조금 시작일.
Expenses!$A$5:$A$304<…: 시작 전 날짜.
…>종료: 종료 후 날짜.
SUMPRODUCT(…)=0: 그런 지출 없음.

예시 데이터로 보면: B7 결과 OK

어떻게 쓰이나: 후원자는 기간 밖 비용을 불인정해요. 보고서보다 먼저 잡아요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →

팁과 한계

용량: 보조금 10건, 예산 항목 60개, 지출 300건. 더 필요하면 Ctrl+H로 범위($14, $64, $304)를 늘리세요.
평소에는 기준일을 =TODAY()로 두고, 보고서를 준비할 때는 고정 날짜를 입력해 작성 중 숫자가 바뀌지 않게 하세요.
Under pace도 Over pace만큼 문제예요. 후원자는 안 쓴 돈을 알아채고, 무비용 연장은 종료일 전에 요청해야 해요.
약정액(Budget 탭 E열)이 "남은 예산"과 "실제로 쓸 수 있는 돈"을 가르는 기준이에요. 계약 서명 시 갱신하세요.
예시의 다음 보고 로직은 기간이 보조금 시작일부터 간다고 가정해요. 후원자가 달력 분기로 보고받으면 수식을 고정 날짜로 바꾸세요.

도움이 되었나요? 잘못된 내용이 있나요? 의견 보내기 · admin@arumsoft.com

비영리 보조금 예산 대비 실적 및 소진율 엑셀 추적기

이 템플릿으로 할 수 있는 일

구성

시작하기 전에

단계별 사용법

기준일과 허용치 설정

보조금 등록

승인 예산 입력

지출 기록

속도 표시 읽기

보고 계획

수식 지도

모든 수식 설명

팁과 한계

이 템플릿에 쓰인 기능 배우기