Financial Modeling Seminar

18
2013 瑪亞學院 系列講座 Financial Modeling 財務模型實務 蔡鴻德執行長 瑪亞投資有限公司 Markis Capital Limited
  • date post

    22-Sep-2014
  • Category

    Business

  • view

    418
  • download

    0

description

Basics of Financial Modeling - Financial Statement Analysis, Excel Functions (vlookup, sum, indirect, offset, column, iserr, if, datevalue, concatenate, etc. Forecasting sales, Cost breakdown, cash flow & expenses, sensitivity table, weighted average shares, rolling averages.

Transcript of Financial Modeling Seminar

Page 1: Financial Modeling Seminar

2013 瑪亞學院 系列講座

Financial Modeling

財務模型實務

蔡鴻德執行長 瑪亞投資有限公司 Markis Capital Limited

Page 2: Financial Modeling Seminar

Financial Statements Analysis 2

Ratios Analysis

Over Time

Vs. Peers

vs. Metrics

Page 3: Financial Modeling Seminar

3

Financial Statements

• A measure of "flow"

• Aka Profit & Loss Statement

• 2 Typical Styles

Income Statement

• A measure of "flow"

• 3 components – Operating Investing, Financing

• 2 styles of Operating CF

Cash Flow Statement

• A "snapshot"

• 3 components – Assets, Liabilities, and Equity Balance Sheet

• A measure of "flow" / change

• Indicates changes in shareholders equity

Shareholders' Equity (Optional)

Page 4: Financial Modeling Seminar

Functions 4

Page 5: Financial Modeling Seminar

VLOOKUP(Match()) 5

=VLOOKUP($A7,Q_Actual!$1:$65536,MATCH(L$1,

Q_Actual!$1:$1),FALSE)/1000

Page 6: Financial Modeling Seminar

SUM(INDIRECT…. 6

=SUM(INDIRECT("Semi!"&"R"&ROW()&"C"&F$6,FAL

SE):INDIRECT("Semi!"&"R"&ROW()&"C"&F$7,FALSE)

)

F$6 = Columnstart

F$7 = Columnend

=SUM(INDIRECT("A2:A" & B1))

Page 7: Financial Modeling Seminar

SUM(OFFSET()) 7

SUM( OFFSET( )) calculates the sum of the cell or range of cells returned by the OFFSET( ) function

=SUM(OFFSET(A1,0,0,n,1)) If n = 10, then it sums A1:A10 =SUM(OFFSET(A1,0,0,1,n)) If n = 10, then it sums A1:J1 =SUM(OFFSET(A1,0,0,n,y)) If n = 5 and y = 10, then it sums A1:J5

Page 8: Financial Modeling Seminar

Standard Quarterly Tables 8

Reported vs. Estimates

New vs. Old Estimates (Estimate Revisions)

Quarterly Changes (YoY, QoQ, Margins)

Earnings Bridge

Sales Breakdown Pie Chart

P&Q Column & Line Chart

Page 9: Financial Modeling Seminar

P&Q Column & Line Chart 9

Page 10: Financial Modeling Seminar

Formatting 10

First Column "n.m"

=IF(COLUMN()=2,"n.m.",C98-B98)

Estimates vs. Reported Variance

=IF(ISERR(P6-Q6),"n.a.",P6-Q6)

% Difference

=IF(Q6<0,(IF(P6>0,"n.m",-(P6/Q6-1))),P6/Q6-1)

Concatenate, Right, Left, Mid

Shorter version: =DATEVALUE(C2&"-"&B2&"-"&D2)

Page 11: Financial Modeling Seminar

Usages 11

Page 12: Financial Modeling Seminar

Forecasting Sales 12

YoY/QoQ % Growth By Geography

By Business Units

Market Share (%) By Product

P x Q Capacity * Utilization Rate * Yield = Quantity

Unit Price & FX Rate = Price

Incremental Sales LY Sales + New Stores * Sales / New Stores

Monthly Sales

Page 13: Financial Modeling Seminar

Cost Breakdown 13

Create Pie Chart of Cost Breakdown – typical cost

structures

Fixed Costs, Variable Costs, Semi-Variable Costs

Raw Materials, WIP, Finished Goods

Overhead

Depreciation

Page 14: Financial Modeling Seminar

Cash Flow & Expenses 14

The Relationship between Depreciation & Capital

Expenditures

The Choice of Operating Leases, Capital Leases, and

Purchasing

Page 15: Financial Modeling Seminar

Sensitivity Table 15

What-if Analysis

Data Table

Column vs. Row

DCF sensitivity table

Terminal value growth

### 0.5% 1.5% 2.5% 3.5% 4.5%

8% 3.7 5.1 7.0 9.7 13.9

9% 5.8 7.8 10.7 15.2 23.4

10% 5.8 7.8 10.7 15.2 23.4

11% 3.7 5.1 7.0 9.7 13.9

12% 1.0 1.7 2.7 3.9 5.5

WA

CC

Page 16: Financial Modeling Seminar

Weighted Average Shares 16

=(A)*((D)/360)*(C)+(B

)*(1-((D)/360)-(E)

=D31*(D36/360)+D35*

(E36/360)+E35*(F36/36

0)+F35*(G36/360)

Shares (2330)

Dividend day 04/06/14

Shares before ex-dividend (A) 202,666,190

Shares after ex-dividend (B) 233,765,970

Split mulitplier (C) 0.8765

Day in dividend (1-360) (D) 163

WAVG (without cutting SHBS) 232,614,053

Shares held by subsidiaries ( E) 443,304

Weighted average of shares 232,170,749

Shares (2615)

Dividend day 2008/1/31 2008/8/14 2008/11/5

Shares before ex-dividend (i) 20,722,915 20,783,578 21,822,757

Surplus capital 0 1,039,179 0

CB 60,663 0 0

Cancellation of treasury stock 0 0 47,025

Shares after ex-dividend (ii) 20,783,578 21,822,757 21,775,733

Day in dividend (1-360) (iii) 29 194 81 56

Weighted average of shares 21,166,842

Page 17: Financial Modeling Seminar

Rolling Average 17

Useful in filling in missing data between data points

=(C27-C15)/12+C15

Page 18: Financial Modeling Seminar

蔡鴻德 (Teddy H. Tsai) 董事暨執行長 (Director & CEO)

801 高雄市前金區大同二路20號9樓之2

電話: +886-7-241-2329 分機 123 手機: 0910723698

電子郵件: [email protected] 網站: http://www.markis.tw

Q&A

18