18.6 Excel을 활용한 장기 예측 작성

'시트' 활용하기

Excel에서 예산을 작성하는 일은 사업의 규모와 복잡성, 그리고 Excel 통합 문서에 작성된 공식과 종속 관계의 수에 따라 매우 간단할 수도 있고 극도로 복잡할 수도 있습니다.

Excel로 예측을 작성하는 과정은 이 장에서 방금 살펴본 것과 같은 단계와 흐름을 따르지만, 소프트웨어 프로그램이 계산을 대신해 준다는 강점이 있습니다. 먼저 Excel의 몇 가지 핵심 공식을 사용하는 매출 예측부터 시작합니다.

1. 첫째, 매출은 전년보다 18% 높을 것으로 예상됩니다. 따라서 연간 총예측치는 전년도 총매출에 연결된 간단한 링크와 곱셈 함수를 사용하여 계산합니다. 그림 18.19에서 셀 O4의 공식이 “='Figure 18.12'!N4*1.18”임을 확인할 수 있습니다. 이 공식은 단순히 전년도 매출을 18% 증가시키는 계산을 수행합니다.
2. 다음으로, 매출을 월별로 배분합니다. 그림 18.19의 셀 B5를 보면 예측 손익계산서 시트가 전년도 손익계산서(그림 18.12) 시트의 연간 매출 대비 비율에 연결되어 있음을 알 수 있습니다. 그런 다음 셀 B4에서는 O4의 예측 총매출에 전년도 1월의 연간 매출 대비 비율을 곱하는 공식으로 1월 매출을 추정합니다. 이 공식이 그 곱에 다시 0.98을 곱한다는 점에 유의하십시오. 이는 Clear Lake가 전년도 마지막 분기에 한 제품 라인을 단종했으며, 경영진은 이로 인해 새해 첫 분기의 매출이 약 2% 감소할 것으로 보기 때문입니다.

   

3. Clear Lake가 예측 손익계산서를 계속 채워 나가면서 다음으로 보게 되는 공식은 B8의 순매출을 계산하는 간단한 합계 공식입니다(그림 18.20 참조). B4의 총매출에서 B7의 매출 반품(수익 차감 항목) 및 에누리를 빼는 간단한 공식입니다. 비슷한 공식이 매출총이익을 계산하는 B10과 당기순이익을 계산하는 B18에도 있습니다.
4. 셀 B9에서는 B4의 매출에 0.5, 즉 50%를 곱하는 곱셈 공식을 볼 수 있습니다. 이는 경영진이 적어도 대부분의 분기에는 매출원가가 전년과 같게 유지될 것으로 보고 있으며, 전년도의 비율이 50%였기 때문입니다.
5. 임차료, 감가상각비, 급여는 모두 전년도와 동일하게 유지되는 고정비용이므로 단순히 직접 입력합니다.
6. 셀 B14에 있는 공과금 계산은 매출 계산과 다소 비슷합니다. O14의 총 공과금 비용에 B4의 당월 매출을 O4의 연간 총매출로 나눈 값을 곱합니다. 이렇게 하면 연간 매출에서 차지하는 비율에 따라 공과금 비용이 월별로 배분됩니다.

Clear Lake의 예측 재무상태표는 예측 손익계산서 및 전년도 손익계산서와 매우 밀접하게 연결되어 있습니다. 그림 18.21의 C7에서는 당월과 이전 3개월 매출의 합계를 기말 매출채권 잔액의 추정치로 사용하는 덧셈 공식을 볼 수 있습니다. 재고에 대한 공식도 비슷하지만 미래를 내다보는 방식입니다. C8에서는 예측 손익계산서의 당월과 향후 3개월 매출원가를 더하여 재고를 추정합니다.

C10의 유동자산 합계는 선택한 모든 셀의 값을 더하는 SUM 공식으로 계산합니다. 단기투자자산이나 보통주처럼 변동이 예상되지 않는 금액은 셀에 숫자로 단순히 입력합니다. 손익계산서와 마찬가지로 소계는 자산 총계가 C13, 유동부채가 C17, 자본 총계가 C24, 부채 및 자본 총계가 C25에 있습니다. C23의 이익잉여금은 전년도 말의 기말 이익잉여금 잔액(B열에 숨겨져 있음)을 가져와 예측 손익계산서의 1월 당기순이익을 더함으로써 당월 기말 잔액을 구합니다.

재무상태표와 마찬가지로 현금 예측 역시 예측 재무상태표뿐만 아니라 예측 손익계산서의 데이터에 크게 의존합니다. 연초의 경우 그림 18.22에서 B4의 공식이 예측 재무상태표에서 현금 잔액을 가져오는 것을 볼 수 있습니다. B6에서는 공식이 전년도 손익계산서에서 이전 3개월의 매출을 가져옵니다. 판매 후 90일이 지나서 고객으로부터 현금을 회수한다고 가정하기 때문입니다. 매입채무, 임차료, 급여, 공과금에도 같은 방식을 사용합니다. 이 공식들은 가정한 지급 시점에 따라 이전 달의 비용을 가져옵니다. 예를 들어 공과금은 30일 이내에 지급된다고 가정하므로, 2월의 현금 유출은 예측 손익계산서의 1월 공과금 비용으로 가정합니다. 이자 지급은 1월과 2월에는 0으로 가정하지만, 3월에는 D14의 공식이 예측 손익계산서의 1월, 2월, 3월 이자비용을 합산한다는 점에 유의하십시오. 이자를 분기마다 지급하기 때문입니다.

마지막으로 C4의 공식에 주목하십시오. 어떤 달의 기초 현금 잔액은 전월의 기말 현금 잔액과 같습니다. 따라서 새로운 달을 시작하기 위해 B18의 수치를 C4에 연결합니다.

Excel 함수를 사용하여 균형 맞추기

예측의 초안을 개략적으로 잡고 나면, 그때부터 세부 조정이 시작됩니다. 예측 손익계산서에서 1월 매출을 2% 줄였던 것처럼, 추가 정보를 활용해 공식을 조정할 수 있습니다. 비용, 소계, 재무제표의 대부분(전부는 아님)을 공식으로 서로 연결해 두었기 때문에, 경영진은 예측 통합 문서를 활용해 시나리오 분석과 민감도 분석도 수행할 수 있습니다. 이는 본질적으로 "만약 ~라면?"이라고 묻고 그 결과를 살펴보는 것입니다. 그러나 작업을 마친 뒤 예측을 확정하기 전에, 재무제표(특히 이름 그대로 균형을 이뤄야 하는 재무상태표)가 균형을 이루는지 확인하는 것이 중요합니다.

전 과정에 걸쳐 공식을 사용해 소계를 계산했다는 점에 주목하십시오. 이렇게 하면 소계가 정확하고 필요에 따라 바뀌도록 할 수 있습니다. 또한 기말 현금 잔액과 기초 현금 잔액 같은 수치를 연결하여 균형이 맞도록 했습니다. 아마도 가장 쉬우면서도 가장 중요한 일은 재무상태표의 균형이 맞는지 확인하는 것입니다. 이는 총자산을 총부채 및 자본과 비교하는 간단한 공식으로 확인할 수 있습니다. 그림 18.23에서 셀 C27에서 한쪽에서 다른 쪽을 빼면 결과가 $0이 되어야 함을 알 수 있습니다. 차이가 있으면 공식이 이를 강조 표시하므로, 원인을 조사하고 시트를 수정하여 균형을 맞춰야 합니다.