무료 레시피 원가·메뉴 마진 엑셀 계산기: 수율, Q-팩터, 식재료비율 (수식 설명 포함)

구성

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

시트	용도	입력하는 것	자동 계산되는 것
Start Here	목표와 메뉴 요약	목표 식재료비율, Q-팩터	원가 계산 레시피 수, 평균 식재료비율, 목표 초과 레시피, 최고 식재료비율 품목, 최고 마진 품목, 평균 접시 원가와 가격, 불일치 줄
Ingredients	재료당 한 행(60)	이름, 분류, 구매 단위와 가격, 레시피 단위, 구매당 단위 수, 수율 %, 날짜	레시피 단위당 원가
Recipes	레시피별 재료당 한 행(120)	레시피, 재료, 수량	단위, 단가, 줄 원가, 조회 상태
Menu	메뉴당 한 행(20)	레시피, 분류, 인분, 메뉴 가격	재료 원가, Q-팩터, 접시 원가, 식재료비율, 매출총이익, 목표 달성 가격, 상태
Lists	드롭다운	분류, 단위, 메뉴 분류	없음
Checks	데이터 검사 6개	없음	OK 또는 Review

시작하기 전에

주요 재료의 최신 송장을 모으세요. 구매하는 포장과 가격이 필요해요.
재료별 레시피 단위(oz, each, fl oz)와 포장당 단위 수를 정하세요. 손질하거나 껍질을 벗기는 재료는 수율을 추정하세요.
레시피를 접시당 재료와 수량으로 적으세요(배치 단위라면 Menu 탭에서 인분을 설정).

단계별 사용법

목표 설정
Start Here B6은 목표 식재료비율, B7은 목록에 없는 가니시·기름·양념용으로 모든 접시에 더하는 Q-팩터(2–5%)예요.
재료 단가 입력
Ingredients 탭에 구매 방식, 가격, 레시피 단위, 구매 단위당 레시피 단위 수, 수율을 입력하면 레시피 단위당 원가가 계산돼요.
레시피 만들기
Recipes 탭에 레시피 이름(Menu 탭 드롭다운), 재료(드롭다운), 수량을 입력하면 단위와 원가를 찾아와요. 조회가 "Not found"면 이름이 Ingredients 탭과 다른 거예요.
메뉴 가격 설정
Menu 탭에 품목별 분류, 레시피당 인분, 가격을 입력하면 접시 원가, 식재료비율, 마진, 목표 달성 가격이 계산돼요.
Start Here 읽기
목표 초과 품목, 최고 식재료비율 품목, 달러 마진이 가장 큰 품목, 평균 접시 원가와 가격을 보여 줘요. 차트가 레시피별 식재료비율을 정렬해요.
Checks 탭이 표시한 것 수정
불일치 재료 이름, 원가 없는 재료, 중복 이름, 가격이나 레시피 줄이 없는 메뉴, 50–100% 밖의 수율을 잡아내요.

수식 지도

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

시트	셀	함수	계산 내용	읽는 값	쓰이는 곳
Ingredients	`H5` 64행까지 아래로 복사됨	IF, OR	레시피 단위당 원가	D5, F5, G5	모든 레시피 줄이 조회해요.
Recipes	`E5` 124행까지 아래로 복사됨	IF, IFERROR, INDEX, MATCH	단가 조회	B5	줄 원가가 이 값에 수량을 곱해요.
Recipes	`G5` 124행까지 아래로 복사됨	IF, ISNUMBER, MATCH	조회 상태	B5	Start Here B16과 Checks 탭에서 세요.
Menu	`D5` 24행까지 아래로 복사됨	IF, SUMIF	1인분 재료 원가	C5	접시 원가의 기반이에요.
Menu	`F5` 24행까지 아래로 복사됨	IF	접시 원가	D5, E5	식재료비율, 마진, 목표 달성 가격이 모두 여기서 시작해요.
Menu	`H5` 24행까지 아래로 복사됨	IF, OR	식재료비율	F5, G5	상태 판정에서 목표와 비교하고 Start Here에 차트로 그려져요.
Menu	`J5` 24행까지 아래로 복사됨	IF	목표 달성 가격	F5	현재 가격과 비교하세요. 차이가 크면 가격 결정이거나 레시피 손질 대상이에요.
Menu	`K5` 24행까지 아래로 복사됨	IF	상태	H5	조건부 서식이 세 상태에 색을 입혀요.
Start Here	`B13`	IFERROR, INDEX, MATCH, MAX	최고 매출총이익	—	퍼센트보다 달러가 중요해요. $28 접시의 40% 식재료비가 $7 사이드의 20%보다 더 벌어요.
Checks	`B7`	IF, SUMPRODUCT, COUNTIF	재료 이름이 고유한지	—	조용히 틀리는 조회가 가장 찾기 어려운 오류예요.

모든 수식 설명

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

레시피 단위당 원가Ingredients!H5 · 64행까지 아래로 복사됨

=IF(OR(A5="", F5=0, G5=0), "", D5/F5/G5)

구매 가격을 포장당 레시피 단위 수로 나누고, 다시 수율로 나눠 손질로 버리는 부분의 비용을 제공하는 부분이 지게 해요.

부분별 설명

OR(A5="", F5=0, G5=0): 빈 행이거나 포장 크기·수율이 없으면 빈칸.
D5/F5: 수율 반영 전 레시피 단위당 가격.
/G5: 수율(0.9 = 90% 가용)로 나누면 가용 원가가 올라가요.

예시 데이터로 보면: D5 = 4.14, F5 = 1, G5 = 95.0% → H5 결과 4.36

어떻게 쓰이나: 모든 레시피 줄이 조회해요.

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

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

단가 조회Recipes!E5 · 124행까지 아래로 복사됨

=IF(B5="", "", IFERROR(INDEX(Ingredients!$H$5:$H$64, MATCH(B5, Ingredients!$A$5:$A$64, 0)),
    0))

Ingredients 탭에서 재료를 찾아 레시피 단위당 원가를 돌려줘요.

부분별 설명

MATCH(B5, Ingredients!$A$5:$A$64, 0): 재료의 행 번호(정확히 일치).
INDEX(Ingredients!$H$5:$H$64, …): 그 행의 원가.
IFERROR(…, 0): 이름을 못 찾으면 0으로 두어 줄 원가에 오류가 나지 않게.

예시 데이터로 보면: B5 = Chicken breast → E5 결과 4.36

어떻게 쓰이나: 줄 원가가 이 값에 수량을 곱해요.

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

배우기: 엑셀 INDEX와 MATCH 함수 사용법 →

조회 상태Recipes!G5 · 124행까지 아래로 복사됨

=IF(B5="", "", IF(ISNUMBER(MATCH(B5, Ingredients!$A$5:$A$64, 0)),
    "OK", "Not found"))

재료 이름이 Ingredients 탭과 일치하는지 보여 줘서, 오타로 인한 0 원가가 눈에 띄게 해요.

부분별 설명

MATCH(B5, Ingredients!$A$5:$A$64, 0): 찾으면 숫자, 못 찾으면 오류.
ISNUMBER(…): 찾았을 때 TRUE.

예시 데이터로 보면: B5 = Chicken breast → G5 결과 OK

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

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

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

1인분 재료 원가Menu!D5 · 24행까지 아래로 복사됨

=IF(A5="", "", SUMIF(Recipes!$A$5:$A$124,
    A5, Recipes!$F$5:$F$124)/IF(C5=0,
    1, C5))

이 품목의 모든 레시피 줄을 더하고 레시피가 만드는 인분 수로 나눠요.

부분별 설명

SUMIF(Recipes!$A$5:$A$124, A5, Recipes!$F$5:$F$124): 이 레시피의 줄 원가 합계.
IF(C5=0, 1, C5): 인분. 빈칸은 1로 취급.

예시 데이터로 보면: C5 = 1 → D5 결과 4.3

어떻게 쓰이나: 접시 원가의 기반이에요.

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

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

접시 원가Menu!F5 · 24행까지 아래로 복사됨

=IF(A5="", "", D5+E5)

재료 원가 + Q-팩터.

부분별 설명

D5: 1인분 재료 원가.
+E5: Q-팩터: D5 × Start Here B7.

예시 데이터로 보면: D5 = 4.3, E5 = 12.9% → F5 결과 4.43

어떻게 쓰이나: 식재료비율, 마진, 목표 달성 가격이 모두 여기서 시작해요.

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

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

식재료비율Menu!H5 · 24행까지 아래로 복사됨

=IF(OR(A5="", G5=0), "", F5/G5)

접시 원가를 메뉴 가격으로 나눠요.

부분별 설명

OR(A5="", G5=0): 품목이나 가격이 없으면 빈칸.
F5/G5: 접시 원가 ÷ 가격.

예시 데이터로 보면: F5 = 4.43, G5 = 16 → H5 결과 27.7%

어떻게 쓰이나: 상태 판정에서 목표와 비교하고 Start Here에 차트로 그려져요.

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

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

목표 달성 가격Menu!J5 · 24행까지 아래로 복사됨

=IF(A5="", "", F5/'Start Here'!$B$6)

이 품목을 정확히 목표 식재료비율에 맞추는 메뉴 가격.

부분별 설명

F5: 접시 원가.
/'Start Here'!$B$6: 목표로 나눔(0.30 → 가격 = 원가 ÷ 0.3).

예시 데이터로 보면: F5 = 4.43 → J5 결과 14.76

어떻게 쓰이나: 현재 가격과 비교하세요. 차이가 크면 가격 결정이거나 레시피 손질 대상이에요.

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

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

상태Menu!K5 · 24행까지 아래로 복사됨

=IF(H5="", "", IF(H5<='Start Here'!$B$6,
    "OK", IF(H5<='Start Here'!$B$6+0.05, "Watch", "Over")))

목표 이하면 OK, 5포인트 이내 초과면 Watch, 그 이상이면 Over.

부분별 설명

H5="": 데이터 없음: 빈칸.
H5<='Start Here'!$B$6: 목표 이하.
H5<='Start Here'!$B$6+0.05: 5포인트 이내 초과.

예시 데이터로 보면: H5 = 27.7% → K5 결과 OK

어떻게 쓰이나: 조건부 서식이 세 상태에 색을 입혀요.

바꾸는 방법: 0.05를 바꾸면 Watch 구간이 바뀌어요.

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

최고 매출총이익Start Here!B13

=IFERROR(INDEX(Menu!$A$5:$A$24,
         MATCH(MAX(Menu!$I$5:$I$24), Menu!$I$5:$I$24, 0)),
         "")

Menu 탭에서 달러 마진이 가장 큰 값을 찾아 품목 이름을 돌려줘요.

부분별 설명

MAX(Menu!$I$5:$I$24): 가장 큰 매출총이익.
MATCH(…, Menu!$I$5:$I$24, 0): 그 행.
INDEX(Menu!$A$5:$A$24, …): 그 행의 품목.

예시 데이터로 보면: B13 결과 Pan-Seared Salmon

어떻게 쓰이나: 퍼센트보다 달러가 중요해요. $28 접시의 40% 식재료비가 $7 사이드의 20%보다 더 벌어요.

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

배우기: 엑셀 INDEX와 MATCH 함수 사용법 →

재료 이름이 고유한지Checks!B7

=IF(SUMPRODUCT((Ingredients!$A$5:$A$64<>"")*(COUNTIF(Ingredients!$A$5:$A$64, Ingredients!$A$5:$A$64)>1))=0,
    "OK", "Review")

이름이 중복되면 조회가 조용히 첫 번째를 돌려주므로, 두 번 이상 나오는 이름이 몇 개인지 세요.

부분별 설명

COUNTIF(Ingredients!$A$5:$A$64, Ingredients!$A$5:$A$64)>1: 두 번 이상 나오는 이름이면 TRUE.
(Ingredients!$A$5:$A$64<>"")*…: 빈 행 무시.
SUMPRODUCT(…)=0: 중복 없음.

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

어떻게 쓰이나: 조용히 틀리는 조회가 가장 찾기 어려운 오류예요.

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

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

팁과 한계

용량: 재료 60개, 레시피 줄 120개, 메뉴 20개. 더 필요하면 Ctrl+H로 범위($64, $124, $24)를 늘리세요.
매달 최신 송장으로 재료 단가를 갱신하세요. Last updated 열이 오래된 항목을 보여 주고, 모든 접시 원가가 한 번에 갱신돼요.
Q-팩터는 모든 레시피에 소금·기름·가니시를 적는 것을 대신해요. 직접 적는다면 0으로 두세요.
목표 달성 가격은 출발점이지 규칙이 아니에요. 달러 마진 열과 함께 보세요. 목표는 퍼센트가 아니라 좌석당 달러예요.
배치 레시피(수프, 소스)는 Recipes 탭에 전체 배치를 입력하고 Menu 탭 인분에 나오는 포션 수를 설정하세요.

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

레시피 원가 및 메뉴 마진 엑셀 계산기

이 템플릿으로 할 수 있는 일

구성

시작하기 전에

단계별 사용법

목표 설정

재료 단가 입력

레시피 만들기

메뉴 가격 설정

Start Here 읽기

Checks 탭이 표시한 것 수정

수식 지도

모든 수식 설명

팁과 한계

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