英文版的财务分析,主要做预算用,外企的朋

1、Financial Analysis & ForecastingPrepared by Matt H. Evans, CPA, CMA, CFMPurpose of Spreadsheet:Revised:2002/11/22activeWksh3To illustrate concepts related to financial analysis and forecasting. Wksh5The financial analysis uses a combination of ratios and industry averages to Wksh7evaluate the financ

2、ial performance of the company. Trend line graphs are alsoWksh9generated, comparing the companys performance with the industry averages.Wksh11Finally, the historical information is used to prepare a set of pro forma Wksh13financial statements using both linear and non-linear functions.Wksh15Required

3、 Inputs:You will need to collect financial statements for several reporting periods. If youwant to benchmark the performance against the industry, then you will also needto collect industry averages. The spreadsheet is setup to capture five reportingperiods (annual, quarterly, monthly). All input fi

4、elds are highlighted in yellow.For best results, SEC Filings are suggested since these reports provide more detail than published financial statements.Note: A small red triangle in the upper right corner of a cell indicates that a comment has been inserted. Point your mouse over the cell and the com

5、ment will appear.If a cell appears in red, this indicates a warning concerning a calculation.Worksheets:This spreadsheet consists of the following worksheets, divided into three sections:A) Input Worksheets for financial analysis using historical data:WorksheetTitlePurpose2General InputEnter general

6、 information here - used on several worksheets.3Balance SheetEnter comparative balances sheets for up to five periods.4Income StatementEnter comparative income statements for up to five periods.5Cash Flow StatementEnter comparative cash flow statements for up to five periods.Caution: If you enter le

7、ss than five years of historical information, certain worksheet formulas may have to be revised.B) Output Worksheets for evaluating financial performance:6Key Financial DataCalculates key financial information for further analysis.7Ratio AnalysisCalculates a series of ratios for further analysis.8Be

8、nchmark AnalysisCompare ratio analysis to industry averages.9Horizontal AnalysisHorizontal analysis with corresponding trend lines.10Vertical AnalysisCommon size financials in percentages and graphs.C) Pro Forma / Forecasted Financials for Budgeting:11Pro Forma - SimpleSet of pro forma financials us

9、ing simple assumptions12Pro Forma - RegressionSet of pro forma financials using linear trending13Pro Forma - ExponentialSet of pro forma financials using exponential smoothing14Scenario AnalysisExample of Scenario Analysis and Goal Seek Analysis15Budget AnalysisPreliminary budget analysis16Final Bud

10、getsSet of budgets per various assumptions and forecasts.Note: Some additional worksheets (Answer Reports 1 & 2) may appear in the spreadsheetdue to the running of Solver.Macros:No macros have been used in this spreadsheet to give everyone some assurance that no virusesare contained in the spreadshe

11、et. However, you are free to add your own macros to save time.Tools Macro Record New MacroExcel Functions:This spreadsheet uses certain financial functions (such as =TREND) which might not befound in your version of Microsoft Excel. To take full advantage of financial and statisticalfunctions, you s

12、hould install the Add On package titled: Analysis TookPak. Go to the maintool bar, select Tools = Add-Ins = check the Analysis TookPak option, insert your Excel CD and install the Analysis ToolPak. Also, you might want to install the SolverAdd-in since this is useful for solving special forecasting

13、issues (such as finding the optimal exponential factor).Compatibility:This spreadsheet was created with Microsoft Excel 2000. Older versions of Excel (such as 97) may not be compatible with this spreadsheet. Corrections:With any “attempt” to build an Excel Model, I can easily make some mistakes.So i

14、f you have suggestions to make the model better, drop me an email and Ill be glad to improve the financial model. My email address is:Wksh2Wksh4Wksh6Wksh8Wksh10Wksh12Wksh14Wksh16Prepared by Matt H. Evans, CPA, CMA, CFMPurposeEnter general information here - used on several worksheets.Enter comparati

15、ve balances sheets for up to five periods.Enter comparative income statements for up to five periods.Enter comparative cash flow statements for up to five periods.Caution: If you enter less than five years of historical information, certain worksheet Calculates key financial information for further

16、analysis.Calculates a series of ratios for further analysis.Horizontal analysis with corresponding trend lines.Common size financials in percentages and graphs.Set of pro forma financials using simple assumptionsSet of pro forma financials using linear trendingSet of pro forma financials using expon

17、ential smoothingExample of Scenario Analysis and Goal Seek AnalysisSet of budgets per various assumptions and forecasts.Note: Some additional worksheets (Answer Reports 1 & 2) may appear in the spreadsheetGeneral Input PanelHomeThe following general information should be entered:Wksh3Note: Sample da

18、ta has been entered in the input cells to help you get started.Wksh5Wksh72-1Name of Company = X Y Z Corporation USAWksh9Wksh112-2Reporting Periods =Annual(Annual, Semi-annual, Quarterly or Monthly)Wksh13Wksh152-3Number of Days in Reporting Period are365 What reporting periods will be entered?2-4Most

19、 Current Period2000 (1999, July 1998, 6/30/97, etc.)2-5Previous Period1999 (1999, July 1998, 6/30/97, etc.)2-62nd Previous Period1998 (1999, July 1998, 6/30/97, etc.)2-73rd Previous Period1997 (1999, July 1998, 6/30/97, etc.)2-84th Previous Period1996 (1999, July 1998, 6/30/97, etc.)2-9Number of his

20、torical periods to be analyzed 5How are the amounts expressed in the financial statements?(such as: in millions of dollars, thousands of Canadian dollars, etc.)2-10millions of dollars activeWksh4Wksh6Wksh8Wksh10Wksh12Wksh14Wksh16Prepared by Matt H. Evans 2022/2/23Page 7Balance Sheet forX Y Z Corpora

21、tion USA millions of dollarsAnnualAnnualAnnualAnnualAnnualPeriodPeriodPeriodPeriodPeriodDescription19961997199819992000Cash and Cash Equivalents990950901998870Short Term Marketable Securities101512611Accounts Receivable1,0201,5501,8302,2503,040Inventory 1,0051,3601,6501,9002,060Other Current Assets8

22、701,1501,3701,6501,530Total Current Assets3,8955,0255,7636,8047,511Fixed Assets14,00617,60521,82626,95028,100Accumulated Depreciation-1,280-1,700-2,100-2,550-3,010Net Fixed Assets12,72615,90519,72624,40025,090Longterm Investments 360320120590905Investments in Other Companies6500250412Intangibles and

23、 Other Assets100110105135195Total Non Current Assets13,25116,33519,95125,37526,602Total Assets17,14621,36025,71432,17934,113 Accounts Payable2,0503,1503,2903,8704,800Short Term Borrowings1,2001,8302,5803,1003,550Short Term Portion of LT Debt1215253036Other Current Liabilities1,0501,2501,4801,5901,30

24、1Total Current Liabilities4,3126,2457,3758,5909,687Longterm Debt / Borrowings1,1601,7502,6003,6003,950Other Longterm Liabilities650750701890995Total Non Current Liabilities1,8102,5003,3014,4904,945Total Liabilities6,1228,74510,67613,08014,632Preferred Equity00000Prepared by Matt H. Evans 2022/2/23Pa

25、ge 8Balance Sheet forX Y Z Corporation USA millions of dollarsAnnualAnnualAnnualAnnualAnnualPeriodPeriodPeriodPeriodPeriodDescription19961997199819992000Common Equity2,0442,0052,0692,0902,120Additional Paid in Capital5,0134,9005,1595,6265,628Retained Earnings5,0977,0509,84015,05020,005Adj for Foreig

26、n Currency Transl275120-550-2,147-6,722Treasury Stock-1,405-1,460-1,480-1,520-1,550Total Shareholder Equity11,02412,61515,03819,09919,481Total Liabilities & Equity17,14621,36025,71432,17934,113Check: Assets = Liab + Equity ?00000Comment =BalancesBalancesBalancesBalancesBalancesNonDepreciable Fixed A

27、ssets00000Deferred Taxes1121019098109Goodwill Write Off00000No of Common Shares o/s1,3201,2901,3021,3451,322Par Value of Common Stock$10.00$10.00$10.00$10.00$10.00No of Preferred Shares o/s00000Par Value of Preferred StockMarket Price of Common Stock$22.65$28.90$37.05$33.60$29.40Market Price of Pref

28、erred Stock$0.00$0.00$0.00$0.00$0.00Preferred Dividends in Arrears00000Liquidating value of Preferred Stk00000Book Value per Share$8.35$9.78$11.55$14.20$14.74Dividends per Common Share$1.01$1.49$1.89$1.75$1.76Dividend Payout Ratio45.47%38.61%39.44%29.76%30.24%Cash Dividends to Preferred Stock00000Pr

29、epared by Matt H. Evans 2022/2/23Page 9Balance Sheet forX Y Z Corporation USA millions of dollarsAnnualAnnualAnnualAnnualAnnualPeriodPeriodPeriodPeriodPeriodDescription19961997199819992000Cash Dividends to Common Stock1,3301,9182,4612,3542,329Total Dividends Paid1,3301,9182,4612,3542,329Prepared by

30、Matt H. Evans 2022/2/23Page 10Income Statement forX Y Z Corporation USAmillions of dollarsAnnualAnnualAnnualAnnualAnnualPeriodPeriodPeriodPeriodPeriodDescription19961997199819992000Net Sales12,06016,70021,17024,70027,400Other Operating Revenues1619263748Total Revenues12,07616,71921,19624,73727,448Co

31、st of Goods Sold-4,950-7,050-8,233-9,050-10,150Other Operating Expenses-11-13-17-22-28Total Direct Expenses-4,961-7,063-8,250-9,072-10,178Selling, General & Administrative-3,300-3,880-4,637-5,670-7,120Operating Income3,8155,7768,3099,99510,150Interest Expenses-117-122-216-282-304Foreign Exchange (Lo

32、ss) Gain00000Associated Company (Loss) Gain00-2200Other NonOperating (Loss) Gain017000Income Tax Expense-790-1,005-2,050-2,105-2,660Reserve Charges00000Income Before Extra Ord Items2,9084,6666,0217,6087,186Extra Ordinary Items (Loss) Gain00000Tax Effects of Extraordinary Items00000Minority Interests

33、17302219303515Net Income2,9254,9686,2407,9117,701Primary EPS$2.22$3.85$4.79$5.88$5.83Earnings Before Int & Taxes3,8326,0958,50610,29810,665Depreciation & Amortization-310-420-400-450-460Research & Devel Expenses00000Capitalized Interest Expense-16-19-33-39-30Interest Income46111927Total Non Operatin

34、g Expenses-907-1,110-2,288-2,387-2,964Prepared by Matt H. Evans 2022/2/23Page 11Income Statement forX Y Z Corporation USAmillions of dollarsAnnualAnnualAnnualAnnualAnnualPeriodPeriodPeriodPeriodPeriodDescription19961997199819992000Total Extra Ordinary Items17302219303515Tax Rate21.36%17.78%25.33%21.

35、67%27.02%Prepared by Matt H. Evans 2022/2/23Page 12Cash Flow Statement forX Y Z Corporation USAmillions of dollarsAnnualAnnualAnnualAnnualAnnualPeriodPeriodPeriodPeriodPeriodDescription19961997199819992000Net Income2,9254,9686,2407,9117,701Depreciation and Amortization310420400450460(Increase) Decre

36、ase Defer Taxes-21111-8-11(Gain) Loss on Sale of Assets-5504500(Increase) Decrease Current Assets-162-1,130-738-1,041-707Increase (Decrease) Current Liab2061,9331,1301,2151,097Cash Flow from Operations3,2226,2027,0888,5278,540Capital Expenditures-1,455-2,750-3,880-5,220-4,108Acquisition in Other Cos

37、-1350000Proceeds from Sales of Assets112350150182Purchases of Investments-712-1,979-1,801-2,314-2,609Sale of Investments16212933022150Other Investment Activities33-16661-120Cash Provided (Used) from Investmts-1,995-4,731-5,290-7,175-6,485Proceeds from Borrowings1,0701,0441,4601,8801,105Payments on B

38、orrowings-1,112-650-898-801-961Dividends Paid to Shareholders-1,330-1,918-2,461-2,354-2,329Proceeds from Minority Interest512778Issue Stock / Exercise Options195145136Purchase / Retire Common Stock00000Other Financing Activities-75000-12Cash Provided (Used) from Financing-1,247-1,511-1,847-1,255-2,1

39、83Increase (Decrease) to Cash-20-40-4997-128Beginning Cash Balance1,010990950901998Prepared by Matt H. Evans 2022/2/23Page 13Cash Flow Statement forX Y Z Corporation USAmillions of dollarsAnnualAnnualAnnualAnnualAnnualPeriodPeriodPeriodPeriodPeriodDescription19961997199819992000Ending Cash Balance99

40、0950901998870Check: Should agree to Balance Sheet00000Comment =BalancesBalancesBalancesBalancesBalancesKey Financial Data forX Y Z Corporation USA millions of dollarsAnnualAnnualAnnualAnnualAnnualPeriodPeriodPeriodPeriodPeriodDescription19961997199819992000EBITDA :Income before ExtraOrd Items2,9084,

41、6666,0217,6087,186Interest Expense117122216282304Capitalized Interest Expense1619333930Income Tax Expense7901,0052,0502,1052,660Reserve Charges00000Depreciation and Amortization310420400450460EBITDA4,1416,2328,72010,48410,640EBITDA Margin34%37%41%42%39%Free Cash Flow:Operating Cash Flow3,2226,2027,0

42、888,5278,540Investment Cash Flows-1,995-4,731-5,290-7,175-6,485Preferred Dividends Paid (fixed)00000Redemption of Fixed Obligations-1,112-650-898-801-961Other Critical Outlays -35-45-42-30-25Free Cash Flow807768585211,069Working Capital:Current Assets3,8955,0255,7636,8047,511Current Liabilities4,312

43、6,2457,3758,5909,687Working Capital-417-1,220-1,612-1,786-2,176Liquid Capital:Cash and Cash Equivalents990950901998870Marketable Securities101512611Accounts Receivable1,0201,5501,8302,2503,040Notes Receivable00000Total Current Liabilities-4,312-6,245-7,375-8,590-9,687Long Term Debt-1,160-1,750-2,600

44、-3,600-3,950Preferred Equity00000Liquid Capital-3,452-5,480-7,232-8,936-9,716The following valuation indicators are very simple and basic; they are used as quick, rough estmates.Market Capitalization:Market Cap - Common Stk$29,898$37,281$48,239$45,192$38,867Market Cap - Preferred Stk$0.00$0.00$0.00$

45、0.00$0.00Total Market Capitalization$29,898$37,281$48,239$45,192$38,867Present Value:Normalized Cash Flow Weight %s5.00%10.00%15.00%30.00%40.00%Normalized Cash Flow794Key Financial Data forX Y Z Corporation USA millions of dollarsAnnualAnnualAnnualAnnualAnnualPeriodPeriodPeriodPeriodPeriodDescriptio

46、n19961997199819992000Number of Future Periods 15Required Rate of Return 11.00%Present Value of Free Cash Flow$5,711Present Value of Selling Price$315,000 20012002200320042005Pro Forma Income Statement Gross Revenues30,74234,43138,56243,19048,373Growth Assumptions12.00%12.00%12.00%12.00%12.00%Cost of

47、 Goods Sold-12,024-13,467-15,083-16,893-18,920Growth Assumptions39.11%39.11%39.11%39.11%39.11% Operating Expenses-7,483-8,381-9,387-10,513-11,775Growth Assumptions24.34%24.34%24.34%24.34%24.34%NonOperating Expenses -3,200-3,200-3,600-3,600-4,000ExtraOrdinary Items 650650650700700 Net Income8,68510,0

48、3311,14312,88414,378 Pro Forma Cash Flow StatementSources of Operating Cash Flow:Net Income8,68510,03311,14312,88414,378Depreciation and Amortization470490500520550(Increase) Decrease Defer Taxes00000(Gain) Loss on Sale of Assets159236(Increase) Decrease Current Assets-789-996-1,116-1,249-1,399Incre

49、ase (Decrease) Current Liab1,0731,2911,4461,6201,814 Operating Cash Flow9,45310,82711,97513,77715,348 Investment Sources of Cash Flow: Planned Sale of Assets10060202535Planned Sale of Investments2,2002,1001,9001,8001,700Other Investment Sources to be used00000Total Investment Sources of Cash2,3002,1

50、601,9201,8251,735Planned Investments:Capital Expenditures-3,500-3,000-3,100-2,700-2,600Acquisitions in Other Cos-500-750-1,200-650-350Purchases of Investments-3,000-3,500-4,500-6,000-7,000Total Investment Applications of Cash-7,000-7,250-8,800-9,350-9,950Cash Flow from Financing Activities:Proceeds

51、from Loans & Debt1,3001,000950750650Proceeds from Minority Interest20608090100Other Financing Activities00000Total Financing Sources of Cash1,3201,0601,030840750AnnualAnnualAnnualAnnualAnnualPeriodPeriodPeriodPeriodPeriodEnter Your Forecast Periods =20012002200320042005Cash Flow Applied for Financin

52、g:Payments on Loans & Debt-1,500-1,000-600-500-500Dividends Paid to Shareholders-2,500-3,000-4,000-5,500-7,000Purchase / Retire Stock-2,000-2,000-1,500-1,000-500Other Financing Activities00000Total Financing Applications of Cash-6,000-6,000-6,100-7,000-8,000Total Change to Cash737972592-117Beginning

53、 Cash Balance8709431,7401,7651,857Forecasted Ending Balance9431,7401,7651,8571,740Pro Forma Balance SheetCash and Cash Equivalents9431,7401,7651,8571,740Short Term Marketable Securities00000Accounts Receivable3,0743,4433,8564,3194,837Inventory 2,4592,7543,0853,4553,870Other Current Assets1,9982,2382

54、,5072,8073,144Total Current Assets8,47510,17511,21312,43813,591Fixed Assets31,60034,60037,70040,40043,000Accumulated Depreciation-3,480-3,970-4,470-4,990-5,540Net Fixed Assets28,12030,63033,23035,41037,460Longterm Investments 1,7053,1055,7059,90515,205Investments in Other Companies9121,6622,8623,512

55、3,862Intangibles and Other Assets200240320400650Total Non Current Assets30,93735,63742,11749,22757,177Total Assets39,41245,81253,33061,66570,768Accounts Payable5,2265,8536,5567,3428,223Short Term Borrowings3,6894,1324,6275,1835,805Short Term Portion of LT Debt3030252015Other Current Liabilities1,845

56、2,0662,3142,5912,902Total Current Liabilities10,79012,08113,52215,13616,945Longterm Debt / Borrowings3,7503,7504,1004,3504,500Other Longterm Liabilities700750800800800Total Non Current Liabilities4,4504,5004,9005,1505,300Total Liabilities15,24016,58118,42220,28622,245Preferred Equity00000Common Equi

57、ty2,2002,2002,2002,2002,200AnnualAnnualAnnualAnnualAnnualPeriodPeriodPeriodPeriodPeriodEnter Your Forecast Periods =20012002200320042005Additional Paid in Capital5,7005,7005,7005,7005,700Retained Earnings26,19033,22240,36547,74855,126Adj for Foreign Currency Transl-5,000-4,000-2,500-1,500-500Treasur

58、y Stock-3,550-5,550-7,050-8,050-8,550Total Equity25,54031,57238,71546,09853,976External Financing Required (EFR)-1,368-2,341-3,807-4,720-5,453HomeWksh2ProForma Financials (Linear Trend Model)Wksh3Wksh4X Y Z Corporation USAWksh5Wksh6Wksh7Wksh8A set of pro forma (forecasted) financial statements are g

59、enerated using the results ofWksh9Wksh10the historical analysis in the previous worksheets. A statistical method known as Wksh11activelinear regression is used to predict future values. If you have important assumptions Wksh13Wksh14that are important to the forecast, then these assumptions should ov

60、er-ride theWksh15Wksh16linear calculations since we want our forecast to be as accurate as possible.Key Point = If your historical information has gradual trends, then linear regressionis an appropriate model for forecasting. However, if your historical information has distinct steps up or down, the