엑셀로 배우는 비즈니스 시뮬레이션

Conclusiton_170713.pdf

0.07MB

Shortcut_170713.pptx

1.86MB

Presentation_170713.pdf

0.60MB

Graph_170713.pdf

0.50MB

FinancialModel_2_170713.pdf

0.78MB

FinancialModel_1_170713.pdf

0.74MB

excel_practice.xlsx

0.04MB

키워드

피쉬본fish born 벨류드라이버 재무 시뮬레이션에 있어서 엑셀의 포멧 트레이스 감응도 분석 데이터 테이블 케이스 분석 실무에서의 벨류드라이버 추정: 앙케이트 조사, 유사기업비교, 과거데이터와의 비교, 경영전략을 통한 벨류드라이버 추정 추세선을 통한 추정 평균치를 통한 추정 추세선을 통한 검증 주식에서의 벨류드라이버 추정: 사업보고서, IR보고서(중장기전략), 공시/뉴스, 유사기업비교, 과거데이터비교

1,2강: 강좌의 목적

3강: 재무모델이란?

4강: 피시본 수익구성

• 피시본: 재무모델의 설계도

• 벨류드라이버: 최종적으로 계산에 영향을 미치는 변수. 위에서는 판매수, 성장율, 가격, 재료비, 임대로비

  

• 최초부터 설계를 디테일하게 짜지 않는다. - > 대충 짜놓고 천천히 구체화시킨다

• 숫자는 가능한한 서로 연동시킨다: 고정비는 어쩔 수 없다고 하더라도, 변동비와 같은 경우는 매출의 10%와 같이 매출과 연동시키는 것이 가능하다.

5강: 엑셀 포맷의 정비

• 커뮤니케이션의 효율성을 위해, 투자은행에서는 포맷을 매우 중시

  

  

• 숫자표기: alt A로 전체 선택후 컴머를 모든 셀에 적용

6강: 재무모델의 작성

• 피쉬본(재무설계도)를 근거로 엑셀의 항목을 큰 개념부터 작은 개념 순으로 적어나감(매출부터 성장률까지)

  

  

• 벨류드라이버만 적어주면, 사칙연산을 통해 그 상위개념은 계산이 가능하므로, 벨류드라이버를 가장 먼저 상수로 입력

• 일본어 자판의 병신성 때문에, =을 안적고 +로 적음. 진짜 병신같음.

7강: 계산체크

• F2키: 계산식과 계산식이 연결된 셀이 표시된다
• 트레이스: 1. 참조하고 있는 곳 2. 참조되는 곳을 시각적으로 보여줌 G23

트레이스

8강: 엑셀 포맷의 정비

• 배경선 전부 없애기: alt + w + vg
• 셀서식: 맨 아래위에 두꺼운 선그어서 여기서부터 여기까지가 표입니다 라는걸 표시한후, 중간선은 점선으로 처리
• 표제목에서 진짜 표가 시작되는 행까지는 선 삭제
• 열 폭: 구분1과 구분2열의 열폭을 1로 조정
  • 열폭을 띄어쓰기로 조정하지 않는 이유: 1. 보기쉽다. 2. 같은 레벨끼리 이동하기가 쉽다(매출에서 비용으로 ctrl+방향키로 이동하기 쉬움)
• 각 컬럼의 폭 정리
• 숫자 행의 헤더는 오른쪽으로 붙임
• 숫자색변경: 상수 파란색, 계산식 검정, 다른 시트 참조 초록
  • 색변경할때 F4키를 이용하면 매우 편리
  • 모델 변경시 상수를 변경해 모델의 결과치를 변경해가기 때문에, 무엇이 상수인지를 구분하는 것은 중요
    ※상수는 벨류드라이버이므로 계산식안에 포함되지 않고 반드시 가장 밖으로 드러나게 표시해야한다
• 빈칸은 N/A로 처리

9강: 과제

가격을 10% 늘렸을 때랑, 수량을 10%늘렸을 때 어느 쪽이 순이익이 더 클까?

• 매출은 동일하다!
• 하지만 수량은 비용에 영향을 미치기 때문에수량이 , 늘어나면 비용이 증가한다.
• 따라서, 가격이 상승할 때가 순이익은 더 크다

10강: 재무모델의 구체화

초반에 언급했다시피 재무모델은 초반에는 간단하게 묘사하고, 이후 살을 붙여가며 디테일을 추가한다.

• 재무모델이란 누가봐도 쉬워야 하므로, [매출대비비율]이라는 쉬운 명칭으로 10% 표시

  

• 완성된 모델에서 상수를 이리저리 변경해보는 것을 통해 1. 어떤 벨류드라이버가 이익에 큰 영향을 미치는지 2. 숫자 미스를 체크하는 것이 가능 3. 장래의 수익의 예측이 가능

12강: 손익분기점의 계산

• goal Seek: 벨류드라이버를 조정하면서 손익분기점을 찾는건 매우 귀찮다. 이때 사용하는 것이 goal seek 기능
  • 데이타 - what if분석 - goalSeek: alt+ a + w + g
    

    

    

13강: 감응도분석1

• 감응도분석: 감응도분석의 가장 큰 목적은 어떤 변수의 임펙트가 큰지를 평가하는 것

  

• 데이터테이블이 존재할 시, 엑셀이 무거워지므로 [옵션- 수식- 테이블이외 자동]으로 설정

• 데이터 테이블의 계산은 F9를 눌러 수동으로 진행

  

• 데이타테이블이란

  • 데이타소스를 참조하여 작성
  • 데이타테이블을 데이타소스의 옆에 둘지 아래에 둘지
    • 기본적으로 엑셀에서 표는 옆으로 나열하는 것보다 세로로 나열하는 것이 좋음: 1. 컬럼이 주가되기 때문에 2. 행이 추가될시 방해가 되기 때문에
    • 데이터테이블을 아래에 두는 것도 방해가됨: 행이 추가되거나 필터를 걸시에 방해가 되므로
  • 옆도 아래도 안되므로 대각선 아래에 둠

• 데이타테이블 작성

  • 행과 열에 예측하고 싶은 시나리오의 가격과 판매수량을 표시
  • 비교하고 싶은 값(영업이익)을 가장 첫번째 셀에 표시(360,000) - > 엑셀에게 영업이익을 도출하기 위한 계산식을 참조시키기 위함
  • 데이타테이블 작성: 데이타 - what if분석 - data table: alt+ a + w + t
  • 판매수의 성장율과 가격 상수의 위치를 엑셀에게 참조시킴

14강: 감응도분석2- 포맷정리

• 枠線정리
• 첫번째 셀에 있는 비교값은 삭제하면 안되므로, 폰트색을 흰색으로 처리
• 컬럼의 레전드(판매량의성장율)를 중앙으로 가져오고 싶은데, [셀결합 후 중앙으로 맞추기]기능은 왠만해서는 사용하지 않는 것이 좋다
  • 셀서식 - > 배치 - > 선택범위내에서 중앙
• 행과 열의 상수의 색을 파란색으로 변경
• 条件付き書式에서 조건을 걸어 셀의 색을 변경

15강: 케이스 분석1

• 혹시 계산식이 추가되거나 변경되었을 경우, 복사한 모든 테이블에 대해서 계산식을 바꿔야 하기 때문에 리스크가 크다.
• 따라서, 계산식은 무조건 하나로 관리하는 편이 좋다.(코딩으로 따지면, 같은 내용의 함수가 3개 있는 것)
  

  

16강: 케이스분석 2- 1

【시나리오 시트】

• 시나리오 시트: Choose함수를 사용하여, A1 값에 따라 참조하는 인덱스가 바뀜
• A1은 수익계획 시트의 A1을 참조(다른 시트 참조하므로 초록색)

【수익계획 시트】

• A1은 시나리오의 번호를 입력
• B3은 시나리오 시트의 C9를 참조(다른 시트 참조하므로 초록색)
• F8 G8 H8 은 시나리오 시트의 E9 G9 F9를 참조(다른 시트 참조하므로 초록색)

17강: 케이스분석 3- 1

이번 케이스 분석에서는 가격뿐만 아니라 재료비에 따른 시나리오도 고려해본다.

【시나리오 시트】

• 가격 아래에, 1개당재료비표를 복붙
• 트레이스를 통해 복붙 후 제대로 참조하고 있는지 확인

【수익계획 시트】

• 새롭게 추가한 1개당 재료비를 시나리오시트에서 참조시킴

22강: 장래계획의 책정 스텝

• 손익계산서의 구조화: 매출을 가격 * 수량, 수량을 인구_선택률_1인당구매수/내용년수 등으로 구조화한다
• 연동: 광고비가 늘어나면 판매수가 늘어나는 것과 같이, 손익계산서 내부의 관련있는 수끼리 연동. 미래 판매수 = 기존 판매수 + 광고비*2%

23강: 손익계산서의 구조화

피쉬본의 작성

24강: 상관관계분석1

가설: 판매량은 광고비와 인과를 가지고 있다.

• 실제 판매량과 광고비의 상관관계를 분석해본 결과 약 62%의 상관이 나옴

  • 높지도 낮지도 않은 애매한 수치
  • 異常値을 의심해본다.
  • 크리스마스때는 평소보다 더 높게 팔리므로 위의 주황색으로 포인트된 부분은 이상치일 가능성이 크다.
  • 이상치를 제외하고 다시 상관을 그려본다
  • 연말 특수에 팔린 400개(이상치라 예상되는 수치)를 제외하고 선형을 그려본다.
    

    

• 이상치를 제외하니 판매량과 광고비사이에 높은 상관관계가 증명

  

• 계절적, 일시적인 요인에 의한 변수의 변동은 그러한 요인을 외부로 분리해 계산하지 않으면, 정확한 상관을 얻을 수 없음

27강: 상관분석의 주의점

28강: 장래 계획

장래계획을 세우기 위해서는 아래의 2가지가 준비되어 있어야한다.

1. 피시본(손익계산서의 구조화 설계도)
2. 변수간의 상관관계 분석
   

   

• 벨류드라이버는 위의 방법들을 통해서 추정할 수 있다.

29강: 앙케이트/유사기업 비교

• 가격전략: 1. 코스트베이스 2. 가치베이스 3. 경쟁비교베이스
• 가격: 코스트베이스 < 적정가격P < MIN(가치베이스, 경쟁비교베이스)

30강: 과거와의 비교

• 과거의 추세는 수치로 보지 말고 그래프화해서 시각적으로 보아야 정확히 알 수 있다.

31강: 경영전략

• 경영전략을 반영하여, 손익계산서의 벨류드라이버를 변형

• 재료비: 3달러에서 2.5달러로 변경

• 광고비: 매달 3만엔 증액

• 연말: 연말 판매수를 1.5배로 확대

  

• 추세선을 그려가며, 추정치에 위화감이 있는지 없는지 체크

  

• 앙케이트 조사, 유사기업비교, 과거데이터와의 비교, 경영전략을 손익계산서의 각 벨류드라이버에 반영해 추정치를 적어나감

• 주식에서 손익계산서의 벨류드라이버를 예측해 나갈때, 위와같은 소스가 없으므로 사업보고서, IR보고서(중장기전략), 공시/뉴스, 유사기업비교, 과거데이터비교를 통해 벨류드라이버를 추정

  • 과거데이터를 통한 추정: 1. 추세선을 통한 추정 2. 평균치를 통한 추정

• 주식에서는 데이터가 부족해 손익계산서의 구조화가 불가능하므로, 피시본(재무시뮬레이션의 설계도)를 그리는 것이 불가능. 즉, 손익계산서상의 항목 하나하나(매출, 매출원가, 판관비, 영업외 수익 비용)등이 벨류드라이버

• 단, 피시본을 그려도 데이터가 없으므로 의미는 없지만, 각 회계계정을 세부적으로 구조화해봄으로써 예측의 정도를 높이는 것은 가능. 예를 들어, 구조조정을 진행한다고 하면 판관비가 줄어드므로 비용이 감소를 예측한다거나, 원유값이 하락한다고 하면 매출원가가 줄어든다거나..

  

32강: Uncontrolable variables

33강: 모델 완성

34강: 타당성체크

35강: 엑셀포맷

• 실적과 추정치의 경계선을 표시
• 추정 근거를 기입

36강: 바텀업, 탑다운

• 바텀업 방식: 지금까지 했던 방식대로, 벨류드라이버에서 매상, 이익을 추정하는 방법. 합리적으로 장래의 이익을 예측해야 할 때 알맞음(주식).
• 탑다운 방식: 매출, 이익의 목표를 정한 후, 그에 맞춰 벨류드라이버를 역산하는 방법.
  • 탑다운 방식이 사용되는 예: 사장이 내년 매출 30% 늘이고 싶다고 하면, 일단 매출을 30% 올려놓고 그 안에서 어떤 벨류드라이버들을 조정하여 새로운 매출을 맞출지를 추정

37강: 스케일러블

스케일러블 비즈니스란, 코스트를 늘이지 않아도, 매출이 코스트보다 더 빠르게 증가하는 것

39강: 세로봉 그래프

• 그래프색: 모든 그래프가 같은색이면 무슨 그래프를 봐야하는지 알기힘드므로 강조하고 싶은 그래프에 색을 칠하고 그 외는 회색으로 함
• 숫자색: 그래프의 색과 일치
• 그래프 굵기: [데이터계열의 서식설정] - > [요소의간격]
• 目盛線: 너무 많으면 보기 힘듬, 3~4개 정도로만
• 단위: 세로축 오른쪽 위에 표시. 텍스트박스를 만들어서 단위 기입후 그래프에 추가
• 타이틀: 되도록이면 データ元가 되는 표의 타이틀을 참조

40강: 2축그래프

• 영업이익은 작아서 잘 보이지도 않음
  

  

• 그래프 종류 변경 - > 組み合わせ - > 第2軸クリック

41강: 누적세로봉그래프

• 오른쪽 그래프에서 합계치를 내는게 조금 어려움

• 합계 데이터를 작성 후 이것을 누적그래프로 작성
  

  

  

• 주황색에 숫자를 표시 - > 주황색인 합계의 색을 투명으로 변경 - > 숫자가 조금 위에 있으므로 - > 데이터 라벨 설정에서 숫자를 아래로 내림

• 세로축이 합계까지 계산한 금액으로 산정돼 너무 높으므로 최대치를 변경 - > 라벨의 위치를 아래에서 옆으로 변경

42강: 워터풀 차트

• 오른쪽 위의 테이블처럼, 누적금액을 표시하고 누적금액 분의 그래프 색을 이후에 투명처리

43강: 가로봉그래프

• 작은 순으로 표시되므로 「軸の反転」기능으로 뒤집음

44강: 프레젠테이션 인트로 G87

45강: 프레젠의 구성

• 피쉬본은 전체적인 스토리의 흐름이 되는 가장 기본이 되는 설계이다.
  

  

46강: 매출, 영업이익

47강: 매출

48강: 비용

50강: 프레젠의 요령

1.피쉬본을 전체의 설계로써 사용한다
2.1개의 페이지에는 1개의 그래프와 1개의 메세지만 전달한다.
3.엑셀의 데이터를 가지고 올때는 그림으로 붙여 넣는다.
4.프레젠의 앞이나 뒤에 전제로써 사용된 용어의 정의를 명확히한다.

51강: 엑셀쇼트컷

• 어플리케이션키 + R: 셀 높이 설정
  • 마우스 오른쪽 클릭은 모두 어플리케이션키로 대체가능
• alt+h+p: show percetage
• alt+h+k: comma
• alt+h+0: 표시 자릿수 증가
• alt+h+9: 표시 자리수 감소
• alt+h+h: 배경색 변경
• ctrl+1: 셀의 선 설정
• ctrl+space: 열 전체 선택
• alt+h+o+i: 셀 가로 자동 설정
• alt+h+a(lign)+r: 오른쪽 정렬
• alt+h+a+l: 왼쪽 정렬
• alt+h+a+c: 중간 정렬
• alt+h+f(ont)+c(olor): 중간 정렬
• f4: 방금했던 작업 반복
• alt+i(nsert)+r(ow): 행 추가
• alt+i(nsert)+c(olumn): 열 추가
• ctrl+pagedown, pageup: 시트 이동
• ctrl+home: a1로 돌아옴
• shift+alt+→, ←: 셀을 숨기고 싶을 때는 「비표시」 보다는 「그룹화」
• alt+a+h: 그룹화 셀 숨기기
• alt+a+j: 그룹화 셀 표시
• alt+e+s: 나누기 붙이기, 더하기 붙이기, 곱하기 붙이기 등 연산 붙이기 가능