fs analisis lengkap

8/6/2019 FS Analisis Lengkap

1/85

Prepared by Matt H. Evans 06/15/2011 Page 1

Financial Analysis & ForecastingPrepared by Matt H. Evans, CPA, CMA, CFM

Purpose of Spreadsheet: Revised: 11/22/2002 active

To illustrate concepts related to financial analysis and forecasting.

The financial analysis uses a combination of ratios and industry averages toevaluate the financial performance of the company. Trend line graphs are also

generated, comparing the company's performance with the industry averages.Finally, the historical information is used to prepare a set of pro forma

financial statements using both linear and non-linear functions.

Required Inputs:

You will need to collect financial statements for several reporting periods. If you

want to benchmark the performance against the industry, then you will also need

to collect industry averages. The spreadsheet is setup to capture five reportingperiods (annual, quarterly, monthly). All input fields 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 comment 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:

Worksheet Title Purpose

2 Enter general information here - used on several worksheets.

3 Enter comparative balances sheets for up to five periods.4 Enter comparative income statements for up to five periods.

5 Enter comparative cash flow statements for up to five periods.

Wksh2

Wksh3 Wksh4

Wksh5 Wksh6

Wksh7 Wksh8Wksh9 Wksh10

Wksh11 Wksh12

Wksh13 Wksh14

Wksh15 Wksh16

General Input

Balance SheetIncome Statement

Cash Flow Statement
http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/


2/85


Caution: If you enter less than five years of historical information, certain worksheet

formulas may have to be revised.

B) Output Worksheets for evaluating financial performance:

6 Calculates key financial information for further analysis.

7 Calculates a series of ratios for further analysis.

8 Compare ratio analysis to industry averages.

9 Horizontal analysis with corresponding trend lines.

10 Common size financials in percentages and graphs.

C) Pro Forma / Forecasted Financials for Budgeting:

11 Set of pro forma financials using simple assumptions

12 Set of pro forma financials using linear trending

13 Set of pro forma financials using exponential smoothing

14 Example of Scenario Analysis and Goal Seek Analysis15 Preliminary budget analysis

16 Set of budgets per various assumptions and forecasts.

Note: Some additional worksheets (Answer Reports 1 & 2) may appear in the spreadsheet

due to the running of Solver.

Macros:

No macros have been used in this spreadsheet to give everyone some assurance that no viruses

are contained in the spreadsheet. However, you are free to add your own macros to save time.

Tools > Macro > Record New Macro

Excel Functions:

This spreadsheet uses certain financial functions (such as =TREND) which might not be

found in your version of Microsoft Excel. To take full advantage of financial and statistical

functions, you should install the Add On package titled: Analysis TookPak. Go to the main

tool 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 Solver

Add-in since this is useful for solving special forecasting issues (such as finding the

Key Financial Data

Ratio Analysis

Benchmark Analysis

Horizontal Analysis

Vertical Analysis

Pro Forma - Simple

Pro Forma - Regression

Pro Forma - Exponential

Scenario AnalysisBudget Analysis

Final Budgets
http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/


3/85


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 if 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: [email protected]
mailto:[email protected]:[email protected]


4/85


General Input Panel activeThe following general information should be entered:

Note: Sample data has been entered in the input cells to help you get started.

2-1 Name of Company => X Y Z Corporation USA

2-2 Reporting Periods => Annual (Annual, Semi-annual, Quarterly or Monthly)

2-3 Number of Days in Reporting Period a 365

What reporting periods will be entered?

2-4 Most Current Period 2000(1999, July 1998, 6/30/97, etc.)

2-5 Previous Period 1999 (1999, July 1998, 6/30/97, etc.)

2-6 2nd Previous Period 1998(1999, July 1998, 6/30/97, etc.)

2-7 3rd Previous Period 1997(1999, July 1998, 6/30/97, etc.)

2-8 4th Previous Period 1996(1999, July 1998, 6/30/97, etc.)

2-9 Number of historical periods to be analyzed 5

How are the amounts expressed in the financial statements?

(such as: in millions of dollars, thousands of Canadian dollars, etc.)

2-10 millions of dollars

Home

Wksh3 Wksh4

Wksh5 Wksh6

Wksh7 Wksh8

Wksh9 Wksh10


Wksh15 Wksh16
http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/http://opt/scribd/conversion/tmp/scratch2631/


5/85


Balance Sheet for

X Y Z Corporation USAmillions of dollars

Annual Annual Annual Annual Annual

Period Period Period Period Period

Description 1996 1997 1998 1999 2000

Cash and Cash Equivalents 990 950 901 998 870

Short Term Marketable Securitie 10 15 12 6 11

Accounts Receivable 1020 1550 1830 2250 3040

Inventory 1005 1360 1650 1900 2060

Other Current Assets 870 1150 1370 1650 1530

Total Current Assets #REF! #REF! #REF! #REF! #REF!

Fixed Assets 14006 17605 21826 26950 28100

Accumulated Depreciation -1280 -1700 -2100 -2550 -3010

Net Fixed Assets 12726 15905 19726 24400 25090Longterm Investments 360 320 120 590 905

Investments in Other Companies 65 250 412

Intangibles and Other Assets 100 110 105 135 195

Total Non Current Assets #REF! #REF! #REF! #REF! #REF!

Total Assets #REF! #REF! #REF! #REF! #REF!

Accounts Payable 2050 3150 3290 3870 4800

Short Term Borrowings 1200 1830 2580 3100 3550

Short Term Portion of LT Debt 12 15 25 30 36

Other Current Liabilities 1050 1250 1480 1590 1301Total Current Liabilities #REF! #REF! #REF! #REF! #REF!

Longterm Debt / Borrowings 1160 1750 2600 3600 3950

Other Longterm Liabilities 650 750 701 890 995

Total Non Current Liabilities 1810 2500 3301 4490 4945

Total Liabilities #REF! #REF! #REF! #REF! #REF!

Preferred Equity

Common Equity 2044 2005 2069 2090 2120


6/85


Balance Sheet for




Description 1996 1997 1998 1999 2000 Additional Paid in Capital 5013 4900 5159 5626 5628

Retained Earnings 5097 7050 9840 15050 20005

Adj for Foreign Currency Transl 275 120 -550 -2147 -6722

Treasury Stock -1405 -1460 -1480 -1520 -1550

Total Shareholder Equity #NAME? #NAME? #NAME? #NAME? #NAME?

Total Liabilities & Equity #NAME? #NAME? #NAME? #NAME? #NAME?

Check: Assets = Liab + Equity #REF! #REF! #REF! #REF! #REF!

Comment => #REF! #REF! #REF! #REF! #REF!

NonDepreciable Fixed Assets 0 0 0 0 0

Deferred Taxes 112 101 90 98 109

Goodwill Write Off 0 0 0 0 0

No of Common Shares o/s 1,320 1,290 1,302 1,345 1,322

Par Value of Common Stock $10.00 $10.00 $10.00 $10.00 $10.00

No of Preferred Shares o/s 0 0 0 0 0

Par Value of Preferred Stock

Market Price of Common Stock $22.65 $28.90 $37.05 $33.60 $29.40Market Price of Preferred Stock $0.00 $0.00 $0.00 $0.00 $0.00

Preferred Dividends in Arrears 0 0 0 0 0

Liquidating value of Preferred St 0 0 0 0 0

Book Value per Share #NAME? #NAME? #NAME? #NAME? #NAME?

Dividends per Common Share $1.01 $1.49 $1.89 $1.75 $1.76

Dividend Payout Ratio #NAME? #NAME? #NAME? #NAME? #NAME?

Cash Dividends to Preferred Sto

Cash Dividends to Common Sto 1330 1918 2461 2354 2329

Total Dividends Paid 1330 1918 2461 2354 2329


7/85


Income Statement for




Description 1996 1997 1998 1999 2000 Net Sales 12,060 16,700 21,170 24,700 27,400

Other Operating Revenues 16 19 26 37 48

Total Revenues 12,076 16,719 21,196 24,737 27,448

Cost of Goods Sold -4950 -7050 -8233 -9050 -10150

Other Operating Expenses -11 -13 -17 -22 -28

Total Direct Expenses -4961 -7063 -8250 -9072 -10178

Selling, General & Administrativ -3300 -3880 -4637 -5670 -7120

Operating Income 3815 5776 8309 9995 10150

Interest Expenses -117 -122 -216 -282 -304Foreign Exchange (Loss) Gain

Associated Company (Loss) Gai -22

Other NonOperating (Loss) Gai 17

Income Tax Expense -790 -1005 -2050 -2105 -2660

Reserve Charges

Income Before Extra Ord Item #REF! #REF! #REF! #REF! #REF!

Extra Ordinary Items (Loss) Gai

Tax Effects of Extraordinary Ite

Minority Interests 17 302 219 303 515

Net Income #NAME? #NAME? #NAME? #NAME? #NAME?

Primary EPS #NAME? #NAME? #NAME? #NAME? #NAME?

Earnings Before Int & Taxes #NAME? #NAME? #NAME? #NAME? #NAME?

Depreciation & Amortization -310 -420 -400 -450 -460

Research & Devel Expenses

Capitalized Interest Expense -16 -19 -33 -39 -30

Interest Income 4 6 11 19 27

Total Non Operating Expenses #REF! #REF! #REF! #REF! #REF!


8/85


Income Statement for




Description 1996 1997 1998 1999 2000 Total Extra Ordinary Items #NAME? #NAME? #NAME? #NAME? #NAME?

Tax Rate 21.36% 17.78% 25.33% 21.67% 27.02%


9/85


Cash Flow Statement for




Description 1996 1997 1998 1999 2000


Depreciation and Amortization 310 420 400 450 460

(Increase) Decrease Defer Taxes -2 11 11 -8 -11

(Gain) Loss on Sale of Assets -55 45

(Increase) Decrease Current Assets -162 #REF! #REF! #REF! #REF!

Increase (Decrease) Current Liab 206 #REF! #REF! #REF! #REF!

Cash Flow from Operations #REF! #REF! #REF! #REF! #REF!

Capital Expenditures -1455 -2750 -3880 -5220 -4108Acquisition in Other Co's -135

Proceeds from Sales of Assets 112 35 150 182

Purchases of Investments -712 -1979 -1801 -2314 -2609

Sale of Investments 162 129 330 221 50

Other Investment Activities 33 -166 61 -12

Cash Provided (Used) from Investmt #REF! #REF! #REF! #REF! #REF!

Proceeds from Borrowings 1070 1044 1460 1880 1105

Payments on Borrowings -1112 -650 -898 -801 -961

Dividends Paid to Shareholders -1330 -1918 -2461 -2354 -2329

Proceeds from Minority Interest 5 12 7 7 8

Issue Stock / Exercise Options 195 1 45 13 6

Purchase / Retire Common Stock

Other Financing Activities -75 -12

Cash Provided (Used) from Financin #REF! #REF! #REF! #REF! #REF!

Increase (Decrease) to Cash #REF! #REF! #REF! #REF! #REF!

Beginning Cash Balance 1010 990 950 901 998


10/85


Cash Flow Statement for




Description 1996 1997 1998 1999 2000 Ending Cash Balance #REF! #REF! #REF! #REF! #REF!

Check: Should agree to Balance Sheet #REF! #REF! #REF! #REF! #REF!

Comment => #REF! #REF! #REF! #REF! #REF!


11/85


Key Financial Data for




Description 1996 1997 1998 1999 2000 EBITDA :

Income before ExtraOrd Items #REF! #REF! #REF! #REF! #REF!

Interest Expense 117 122 216 282 304

Capitalized Interest Expense 16 19 33 39 30

Income Tax Expense 790 1005 2050 2105 2660

Reserve Charges


EBITDA #REF! #REF! #REF! #REF! #REF!

EBITDA Margin #REF! #REF! #REF! #REF! #REF!

Free Cash Flow:

Operating Cash Flow #REF! #REF! #REF! #REF! #REF!

Investment Cash Flows #REF! #REF! #REF! #REF! #REF!

Preferred Dividends Paid (fixed)

Redemption of Fixed Obligations -1112 -650 -898 -801 -961

Other Critical Outlays -35 -45 -42 -30 -25

Free Cash Flow #REF! #REF! #REF! #REF! #REF!

Working Capital:

Current Assets #REF! #REF! #REF! #REF! #REF!

Current Liabilities #REF! #REF! #REF! #REF! #REF!

Working Capital #REF! #REF! #REF! #REF! #REF!

Liquid Capital:

Cash and Cash Equivalents 990 950 901 998 870

Marketable Securities 10 15 12 6 11


Notes Receivable 0 0 0 0 0


12/85






Description 1996 1997 1998 1999 2000 Total Current Liabilities #REF! #REF! #REF! #REF! #REF!

Long Term Debt -1160 -1750 -2600 -3600 -3950

Preferred Equity

Liquid Capital #REF! #REF! #REF! #REF! #REF!

The following valuation indicators are very simple and basic; they are used as quick, rough estmat

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 $0.00 $0.00

Total Market Capitalization $29,898 $37,281 $48,239 $45,192 $38,867

Present Value:

Normalized Cash Flow Weight %' 5.00% 10.00% 15.00% 30.00% 40.00%

Normalized Cash Flow #REF!

Number of Future Periods 15

Required Rate of Return 11.00%

Present Value of Free Cash Flow #REF!

Present Value of Selling Price $315,000


13/85






Description 1996 1997 1998 1999 2000 Normalized Net Income #REF!

Capitalization Rate 12.00%

Nominal Growth Rate 3.50%

Net Capitalization Rate 8.50%

Value based on Earnings #REF!

Operating Leverage #REF! #REF! #REF! #REF!

Financial Leverage #NAME? #NAME? #NAME? #NAME?

Total Leverage #NAME? #NAME? #NAME? #NAME?

Check Totals 0.00 #NAME? #NAME? #NAME? #NAME?


14/85


Ratio Analysis for

X Y Z Corporation USA



Title of Ratio 1996 1997 1998 1999 2000

Acid Test Ratio 0.47 0.40 0.37 0.38 0.40

Current Ratio #REF! #REF! #REF! #REF! #REF!

Operating Cash Flow to Net Incom #NAME? #NAME? #NAME? #NAME? #NAME?

Liquidity Index:

Cash - Days Removed 0 0 0 0 0

Cash Balance 990 950 901 998 870

Cash Balance Total 0 0 0 0 0

Marketable Sec - Days Removed 11 12 16 15 14

Marketable Securities Balance 10 15 12 6 11

Marketable Securities Total 110 180 192 90 154

Receivables - Days Removed 34 30 31 32 36

Receivable Balance 1020 1550 1830 2250 3040

Receivable Balance Total 34257 46158 56217 72213 110751

Inventory - Days Removed 79 61 67 72 71

Inventory Balance 1005 1360 1650 1900 2060

Inventory Balance Total 79745 83261 110092 136018 146676

Other - Days Removed 16 22 26 21 19Other Current Assets Balance 870 1150 1370 1650 1530

Other Current Assets Total 13920 25300 35620 34650 29070

Liquidity Index (Days) 33 31 35 36 38

Z Score:

1.2 x (working capital / total assets) #REF! #REF! #REF! #REF! #REF!

1.4 x (retained earn / total assets) #REF! #REF! #REF! #REF! #REF!

3.3 x (EBIT / total assets) #REF! #REF! #REF! #REF! #REF!


15/85


Ratio Analysis for




Title of Ratio 1996 1997 1998 1999 2000 .6 x (market value equity / b.v. debt) 15.46 12.78 11.13 7.53 5.90

.999 x (sales / total assets) #REF! #REF! #REF! #REF! #REF!

Z Score #REF! #REF! #REF! #REF! #REF!

Receivable Turnover:

Credit Sales 11520 15750 20080 23200 26500

Average Receivable Balance 1060 1285 1690 2040 2645

Receivable Turnover 10.9 12.3 11.9 11.4 10.0

Days Required to Collect A/R 34 30 31 32 36

Inventory Turnover:

Average Inventory Balance 1,046 1183 1505 1775 1980

Inventory Turnover 4.6 6.0 5.5 5.1 5.1

Days in Inventory 79 61 67 72 71

Total Asset Turnover #REF! #REF! #REF! #REF! #REF!

Operating Assets Ratio #REF! #REF! #REF! #REF! #REF!

Gross Profit Margin 59% 58% 61% 63% 63%

Operating Margin 32% 35% 39% 40% 37%


16/85


Ratio Analysis for




Title of Ratio 1996 1997 1998 1999 2000 Net Profit Margin #NAME? #NAME? #NAME? #NAME? #NAME?

Direct Cost to Operating Revenue 41% 42% 39% 37% 37%

Capitalization Rate / Asset Return:

Net Operating Income 3000 4749 6204 7829 7408

Total Investments / Operating Assets #REF! #REF! #REF! #REF! #REF!

Capitalization Rate / Return #REF! #REF! #REF! #REF! #REF!

Return on Shareholder Equity 24% 33% 35% 33% 26%

Debt to Total Assets #REF! #REF! #REF! #REF! #REF!

Debt to Common Equity 1 1 1 1 1

Times Interest Earned #NAME? #NAME? #NAME? #NAME? #NAME?

Price to Earnings (P/E) #NAME? #NAME? #NAME? #NAME? #NAME?

Price to Book Value #NAME? #NAME? #NAME? #NAME? #NAME?

Stock Yield 4.45% 5.14% 5.10% 5.21% 5.99%


17/85


Benchmark Analysis for


active You need to collect benchmark data on the respective industry for the subject company.

Enter the benchmark data in the appropriate input cells. This information is used to

generate trend line graphs in this worksheet. If you do not have any benchmark data,then fill in the highlighted yellow cell with numeric zero "0". The "industry" line will be null

in the graph and show only the company specific ratio trend.



Ref Description 1996 1997 1998 1999 2000

8-1 Acid Test Ratio - Industry 0.51 0.52 0.50 0.49 0.48

7-1 Acid Test Ratio - Company 0.47 0.40 0.37 0.38 0.40

8-2 Current Ratio - Industry 1.12 1.10 1.05 1.08 1.07

7-2 Current Ratio - Company #REF! #REF! #REF! #REF! #REF!

8-3 Receivable Turnover - Industry 8.50 8.70 8.40 8.20 8.30

7-21 Receivable Turnover - Company 10.87 12.26 11.88 11.37 10.02

8-4 Days to Collect A/R - Industry 41 39 40 41 41

7-22 Days to Collect A/R - Company 34 30 31 32 36

8-5 Inventory Turnover - Industry 4.40 4.45 4.20 4.30 4.387-25 Inventory Turnover - Company 4.60 5.96 5.47 5.10 5.13

8-6 Days in Inventory - Industry 86 88 81 89 82

7-26 Days in Inventory - Company 79 61 67 72 71

8-7 Asset Turnover - Industry 1.60 1.55 1.62 1.68 1.69

7-27 Asset Turnover - Company #REF! #REF! #REF! #REF! #REF!

8-8 Gross Profit Margin - Industry 55.00% 52.00% 60.00% 62.00% 63.00%

Home Wksh2

Wksh3 Wksh4

Wksh5 Wksh6

Wksh7

Wksh9 Wksh10


Wksh15 Wksh16


18/85


7-29 Gross Profit Margin - Company 58.96% 57.78% 61.11% 63.36% 62.96%

8-10 Net Profit Margin - Industry 20.00% 23.00% 22.00% 19.00% 20.00%

7-31 Net Profit Margin - Company #NAME? #NAME? #NAME? #NAME? #NAME?

8-11 Return on Total Assets - Industry 38.00% 36.00% 37.00% 34.00% 36.00%

8-12 Return on Total Assets - Company 30.00% #REF! #REF! #REF! #REF!

8-13 Return on Equity - Industry 26.00% 22.00% 24.00% 19.00% 20.00%

8-14 Return on Equity - Company 23.93% 33.44% 35.28% 33.42% 25.89%

8-15 Debt to Equity - Industry 35% 38% 31% 34% 32%

7-38 Debt to Equity - Company 50% 63% 63% 57% 53%

8-16 Times Interest Earned - Industry 40 38 36 39 43

7-39 Times Interest Earned - Company #NAME? #NAME? #NAME? #NAME? #NAME?

We added the following two ratios to assess if the Company has excessive growth:

4-13 Provision for Taxes -790 -1005 -2050 -2105 -2660

8-17 Trading Ratio - Company #NAME? #NAME? #NAME? #NAME? #NAME?

8-18 Trading Ratio - Industry 1.26 1.28 1.31 1.30 1.27

8-19 Net Sales to Net Worth - Company #NAME? #NAME? #NAME? #NAME? #NAME?

8-20 Net Sales to Net Worth - Industry 1.22 1.20 1.19 1.22 1.20

Source for Benchmark Data: Almanac of Business and Industrial Financial Ratios by Leo Troy, Prentice Hall

Graphs appear below for each of the above comparisons:

0.60

Acid Ratio Comparison


19/85


1996 1997 1998 1999 20000.00

0.10

0.20

0.30

0.40

0.50

Acid Test Ratio- Industry

Acid Test Ratio- Company

Periods

Ratio

1996 1997 1998 1999 20000.00

0.20

0.40

0.60

0.80

1.00

1.20

Current Ratio Comparison

Current Ratio -Industry

Current Ratio -Company

Periods

Ratio

Receivable Turnover Comparison


20/85


1996 1997 1998 1999 20000.00

2.00

4.006.00

8.00

10.00

12.00

14.00ReceivableTurnover -Industry

Receivable

Turnover -Company

Periods

Turnove

rRate

1996 1997 1998 1999 2000

10

20

30

40

50

Receivable Collection Comparison

Days to CollectA/R - Industry

Days to CollectA/R - Company

Periods

Daysto

CollectA/R

Inventor Turnover Com arison


21/85


1996 1997 1998 1999 20000.00

1.00

2.00

3.00

4.005.00

6.00

7.00InventoryTurnover -

IndustryInventoryTurnover -Company

Periods

Turnover

Rate

1996 1997 1998 1999 2000

20

40

60

80

100

Inventory Days Comparison

Days inInventory -Industry

Days in

Inventory -Company

Periods

DaysHeldin

Invent o

ry


22/85


1996 1997 1998 1999 20000.00

0.50

1.00

1.50

2.00

Asset Turnover Comparison

Asset Turnover- Industry

Asset Turnover- Company

Periods

TurnoverRate

1996 1997 1998 1999 20000.00%

10.00%

20.00%

30.00%

40.00%50.00%

60.00%

70.00%

Gross Profit Margin

Gross ProfitMargin -

IndustryGross ProfitMargin -Company

GrossPro

fitMa

rgin


23/85


1996 1997 1998 1999 20000.00%

5.00%

10.00%

15.00%

20.00%

25.00%

30.00%

35.00%

Net Profit Margin

Net ProfitMargin -Industry

Net ProfitMargin -Company

Periods

NetProfitMargin

96 97 98 99 00

0.00%

10.00%

20.00%

30.00%

40.00%

Return on Total Assets

Return on TotalAssets -Industry

Return on TotalAssets -Company

eturnonTotalAss e

ts


24/85


1 1 1 1 2

Periods

R

1996

1997

1998

1999

2000

0.00%

10.00%

20.00%

30.00%

40.00%

Return on Equity Comparison

Return on TotalAssets -Industry

Return on TotalAssets -Company

PeriodsReturnonCommon

Equity

20%

30%

40%

50%

60%

70%

Debt to Equity Comparison

Debt to Equity -Industry

Debt to Equity -Company

toEquityRatio


25/85


1996 1997 1998 1999 20000%

Periods

Debt

1996 1997 1998 1999 20000

10

20

30

40

50

60

Times Interest Earned

Times InterestEarned -Industry

Times Interest

Earned -Company

Periods

TimesInter

estEarn

ed

1.50

2.00

2.50

3.00

Trading Ratio Comparison

Trading Ratio -Industry

Trading Ratio -tio


26/85


1996 1997 1998 1999 20000.00

0.50

1.00

Periods

Ra

1996 1997 1998 1999 20000.00

0.50

1.00

1.50

2.002.50

3.00

Sales to Worth Comparison

Net Sales toNet Worth -Industry

Net Sales toNet Worth -CompanyR

atio


27/85



28/85



29/85



30/85



31/85



32/85



33/85



34/85



35/85



36/85



37/85


Horizontal Analysis for


Horizontal Analysis expresses change between periods as percentages for each account in

the financial statements. The basic formula for horizontal analysis is:

% change = (most recent period - previous period) / previous period



Description 1996 1997 1998 1999 2000

Growth in Net Sales 32.50% 38.47% 26.77% 16.67% 10.93%

Cost of Goods Sold 29.00% 42.42% 16.78% 9.92% 12.15%

Growth in Gross Profits 2.90% -1.99% 5.76% 3.68% -0.64%

Growth in Interest Expense 16.50% 4.27% 77.05% 30.56% 7.80%

Growth in Income Tax Expense 12.90% 27.22% 103.98% 2.68% 26.37%

Growth in Non Operating Expenses 3.80% #REF! #REF! #REF! #REF!

Growth in Minority Interest 96.00% 1676.47% -27.48% 38.36% 69.97%

Growth in Net Income 33.50% #NAME? #NAME? #NAME? #NAME?

Growth in Earnings Per Share 32.60% #NAME? #NAME? #NAME? #NAME?

Cash and Cash Equivalents 3.08% -4.04% -5.16% 10.77% -12.83%

Short Term Marketable Securities 12.00% 50.00% -20.00% -50.00% 83.33%

Accounts Receivable 26.70% 51.96% 18.06% 22.95% 35.11%

Inventory 19.90% 35.32% 21.32% 15.15% 8.42%

Other Current Assets 26.70% 32.18% 19.13% 20.44% -7.27%

Total Current Assets 21.50% #REF! #REF! #REF! #REF!


38/85


Net Fixed Assets 17.80% 24.98% 24.02% 23.69% 2.83%

Longterm Investments 6.20% -11.11% -62.50% 391.67% 53.39%

Investments in Other Companies 0.00% -100.00% #DIV/0! #DIV/0! 64.80%

Intangibles and Other Assets 16.50% 10.00% -4.55% 28.57% 44.44%

Total Non Current Assets 32.50% #REF! #REF! #REF! #REF!

Growth in Total Assets 1.05% #REF! #REF! #REF! #REF!

Accounts Payable 26.20% 53.66% 4.44% 17.63% 24.03%

Short Term Borrowings 33.50% 52.50% 40.98% 20.16% 14.52%

Short Term Portion of LT Debt 16.70% 25.00% 66.67% 20.00% 20.00%

Other Current Liabilities 12.80% 19.05% 18.40% 7.43% -18.18%

Total Current Liabilities 38.02% #REF! #REF! #REF! #REF!

Longterm Debt / Borrowings 46.00% 50.86% 48.57% 38.46% 9.72%

Other Longterm Liabilities 11.30% 15.38% -6.53% 26.96% 11.80%

Total Non Current Liabilities 37.10% 38.12% 32.04% 36.02% 10.13%

Growth in Total Liabilities 31.05% #REF! #REF! #REF! #REF!

Preferred Equity 0.00% #DIV/0! #DIV/0! #DIV/0! #DIV/0!

Common Equity 2.60% -1.91% 3.19% 1.01% 1.44%

Additional Paid in Capital 1.50% -2.25% 5.29% 9.05% 0.04%

Retained Earnings 38.00% 38.32% 39.57% 52.95% 32.92%

Adj for Foreign Currency Transl -166.00% -56.36% -558.33% 290.36% 213.09%

Treasury Stock 2.01% 3.91% 1.37% 2.70% 1.97%

Growth in Total Equity (Net Worth) 6.11% #NAME? #NAME? #NAME? #NAME?

Sustainable Growth Rate #1 #NAME? #NAME? #NAME? #NAME? #NAME?

Sustainable Growth Rate #2 #NAME? #NAME? #NAME? #NAME? #NAME?

Growth in Market Capitalization 14.50% 24.69% 29.39% -6.32% -14.00%


39/85


Vertical Analysis for


Vertical analysis expresses financial statements as percentages. On the Balance Sheet,

Total Assets is assigned 100% and on the Income Statement, Total Revenues is assigned 100%.

expressed in percentages



Account Title 1996 1997 1998 1999 2000

Cash and Cash Equivalents #REF! #REF! #REF! #REF! #REF!

Short Term Marketable Securities #REF! #REF! #REF! #REF! #REF!

Accounts Receivable #REF! #REF! #REF! #REF! #REF!

Inventory #REF! #REF! #REF! #REF! #REF!

Other Current Assets #REF! #REF! #REF! #REF! #REF!Current Assets #REF! #REF! #REF! #REF! #REF!

Net Fixed Assets #REF! #REF! #REF! #REF! #REF!

Longterm Investments #REF! #REF! #REF! #REF! #REF!

Investments in Other Companies #REF! #REF! #REF! #REF! #REF!

Intangibles and Other Assets #REF! #REF! #REF! #REF! #REF!

Non Current Assets 77.28% 76.47% 77.59% 78.86% 77.98%


Accounts Payable #REF! #REF! #REF! #REF! #REF!

Short Term Borrowings #REF! #REF! #REF! #REF! #REF!

Short Term Portion of LT Debt #REF! #REF! #REF! #REF! #REF!

Other Current Liabilities #REF! #REF! #REF! #REF! #REF!

Total Current Liabilities #REF! #REF! #REF! #REF! #REF!

Longterm Debt / Borrowings #REF! #REF! #REF! #REF! #REF!

Other Longterm Liabilities #REF! #REF! #REF! #REF! #REF!

Total NonCurrent Liabilities #REF! #REF! #REF! #REF! #REF!


40/85


Total Liabilities #REF! #REF! #REF! #REF! #REF!

Preferred Equity #NAME? #NAME? #NAME? #NAME? #NAME?

Common Equity #NAME? #NAME? #NAME? #NAME? #NAME?

Additional Paid in Capital #NAME? #NAME? #NAME? #NAME? #NAME?

Retained Earnings #NAME? #NAME? #NAME? #NAME? #NAME?Adj for Foreign Currency Transl #NAME? #NAME? #NAME? #NAME? #NAME?

Treasury Stock #NAME? #NAME? #NAME? #NAME? #NAME?

Total Equity #NAME? #NAME? #NAME? #NAME? #NAME?

Total Liabilities & Equity #NAME? #NAME? #NAME? #NAME? #NAME?

Total Revenues 100.00% 100.00% 100.00% 100.00% 100.00%

Cost of Goods Sold 40.99% 42.17% 38.84% 36.58% 36.98%

Gross Profit 58.96% 57.78% 61.11% 63.36% 62.96%

Operating Expenses 27.42% 23.28% 21.96% 23.01% 26.04%

Non Operating Expenses 7.51% 6.64% 10.79% 9.65% 10.80%

Income Before Extra Ord Items #REF! #REF! #REF! #REF! #REF!



41/85




Enter Your Forecast Periods => 2001 2002 2003 2004 2005

Pro Forma Income Statement

Gross Revenues 30,742 34,431 38,562 43,190 48,373Growth Assumptions 12.00% 12.00% 12.00% 12.00% 12.00%


Growth Assumptions 39.11% 39.11% 39.11% 39.11% 39.11%

Operating Expenses -7483 -8381 -9387 -10513 -11775

Growth Assumptions 24.34% 24.34% 24.34% 24.34% 24.34%

NonOperating Expenses -3200 -3200 -3600 -3600 -4000

ExtraOrdinary Items 650 650 650 700 700

Net Income 8685 10033 11143 12884 14378

Pro Forma Cash Flow Statement

Sources of Operating Cash Flow:

Net Income 8685 10033 11143 12884 14378Depreciation and Amortization 470 490 500 520 550

(Increase) Decrease Defer Taxes

(Gain) Loss on Sale of Assets 15 9 2 3 6

(Increase) Decrease Current Assets #REF! -996 -1116 -1249 -1399

Increase (Decrease) Current Liab #REF! 1291 1446 1620 1814

Operating Cash Flow #REF! 10827 11975 13777 15348

Investment Sources of Cash Flow:


42/85





Planned Sale of Assets 100 60 20 25 35

Planned Sale of Investments 2200 2100 1900 1800 1700

Other Investment Sources to be usedTotal Investment Sources of Cash 2300 2160 1920 1825 1735

Planned Investments:

Capital Expenditures -3500 -3000 -3100 -2700 -2600

Acquisitions in Other Co's -500 -750 -1200 -650 -350


Total Investment Applications of Cash -7000 -7250 -8800 -9350 -9950

Cash Flow from Financing Activities:

Proceeds from Loans & Debt 1300 1000 950 750 650Proceeds from Minority Interest 20 60 80 90 100

Other Financing Activities

Total Financing Sources of Cash 1320 1060 1030 840 750

Cash Flow Applied for Financing:

Payments on Loans & Debt -1500 -1000 -600 -500 -500


Purchase / Retire Stock -2000 -2000 -1500 -1000 -500


Total Financing Applications of Cash -6000 -6000 -6100 -7000 -8000

Total Change to Cash #REF! 797 25 92 -117

Beginning Cash Balance 870 #REF! #REF! #REF! #REF!

Forecasted Ending Balance #REF! #REF! #REF! #REF! #REF!

Pro Forma Balance Sheet


43/85






Short Term Marketable Securities

Accounts Receivable 3074 3443 3856 4319 4837Inventory 2459 2754 3085 3455 3870



Fixed Assets 31600 34600 37700 40400 43000


Net Fixed Assets 28120 30630 33230 35410 37460

Longterm Investments 1705 3105 5705 9905 15205

Investments in Other Companies 912 1662 2862 3512 3862

Intangibles and Other Assets 200 240 320 400 650Total Non Current Assets 30937 35637 42117 49227 57177





Other Current Liabilities 1845 2066 2314 2591 2902

Total Current Liabilities 10790 12081 13522 15136 16945




Total Liabilities 15240 16581 18422 20286 22245

Preferred Equity

Common Equity 2200 2200 2200 2200 2200

Additional Paid in Capital 5700 5700 5700 5700 5700


44/85






Adj for Foreign Currency Transl -5000 -4000 -2500 -1500 -500

Treasury Stock -3550 -5550 -7050 -8050 -8550Total Equity 25540 31572 38715 46098 53976

External Financing Required (EFR) #REF! #REF! #REF! #REF! #REF!


45/85


ProForma Financials (Linear Trend Model)


A set of pro forma (forecasted) financial statements are generated using the results of

the historical analysis in the previous worksheets. A statistical method known as

active linear regression is used to predict future values. If you have important assumptions

that are important to the forecast, then these assumptions should over-ride thelinear calculations since we want our forecast to be as accurate as possible.

Key Point => If your historical information has gradual trends, then linear regression

is an appropriate model for forecasting. However, if your historical information has

distinct steps up or down, then you should consider using a smoothing model .

Before we adopt a regression model, it's a good idea to generate a scatter graph of the actual data

and observe if there is a clear trend for fitting a straight regression line into the data:

The calculation of linear values is determined by defining the slope of the line and the y intercept:

Order Total Rev Linear Slope Intercept

Formula for Linear Trendline: Year variable x actual y Value y m factor b factor

1996 1 12,076 12,683 3876.20 8806.60

y = ( m * x ) + b 1997 2 16,719 16,559

Home Wksh2

Wksh3 Wksh4

Wksh5 Wksh6

Wksh7 Wksh8

Wksh9 Wksh10

Wksh11

Wksh13 Wksh14

Wksh15 Wksh16

1994 1996 1998 2000 20020

5,00010,00015,00020,00025,00030,000

Total Revenues

TotalRevenues

Periods

TotalReve

nues


46/85


1998 3 21,196 20,435

m: slope of line 1999 4 24,737 24,311

x: independent variable 2000 5 27,448 28,188

b: y intercept 2001 6 32,064

2002 7 35,940

2003 8 39,816

2004 9 43,6922005 10 47,569

The degree of linear fit with the actual data can be expressed as R Sq 0.9888



2001 2002 2003 2004 2005


12-1 Gross Revenues 32064 35940 39816 43692 47569

12-2 Cost of Goods Sold -11607 -12847 -14087 -15327 -16567

12-3 Operating Expenses -7750 -8693 -9636 -10579 -11522

12-4 Operating Income 12707 14400 16093 17786 19480

12-5 Non Operating Expenses #REF! #REF! #REF! #REF! #REF!

4-25 Extra Ordinary Items #NAME? #NAME? #NAME? #NAME? #NAME?

Net Income #REF! #REF! #REF! #REF! #REF!




47/85


Net Income #REF! #REF! #REF! #REF! #REF!


(Increase) Decrease Defer Taxes


(Increase) Decrease Current Asset #REF! -1047 -1047 -1047 -1047

Increase (Decrease) Current Liab #REF! 1357 1357 1357 1357

Operating Cash Flow #REF! #REF! #REF! #REF! #REF!



Planned Sale of Investments 2200 2100 1900 1800 1700

Other Investment Sources to be us

Total Investment Sources of Cash 2300 2160 1920 1825 1735



Acquisitions -500 -750 -1200 -650 -350


Total Investment Applications of C -7000 -7250 -8800 -9350 -9950


Proceeds from Loans & Debt 1300 1000 950 750 650




Cash Flow Applied for Financing:Payments on Loans & Debt -1500 -1000 -600 -500 -500


Purchase / Retire Stock -2000 -2000 -1500 -1000 -500


Total Financing Applications of Cas -6000 -6000 -6100 -7000 -8000

Total Change to Cash #REF! #REF! #REF! #REF! #REF!


48/85





Cash and Cash Equivalents #REF! #REF! #REF! #REF! #REF!Short Term Marketable Securities


Inventory 2565 2875 3185 3495 3805



Fixed Assets 31600 34600 37700 40400 43000


Net Fixed Assets 28120 30630 33230 35410 37460




Total Non Current Assets 30937 35637 42117 49227 57177





Other Current Liabilities 1924 2156 2389 2622 2854

Total Current Liabilities 11252 12609 13961 15312 16664





Preferred Equity


49/85


Common Equity 2200 2200 2200 2200 2200


Retained Earnings #REF! #REF! #REF! #REF! #REF!

Adj for Foreign Currency Transl -5000 -4000 -2500 -1500 -500

Treasury Stock -3550 -5550 -7050 -8050 -8550

Total Equity #REF! #REF! #REF! #REF! #REF!



50/85


ProForma Financials (Exponential Smoothing / Weighted Moving Average)


A set of pro forma (forecasted) financial statements are generated using the results of

the historical analysis in the previous worksheets. A statistical method known as

exponential smoothing is used to plot a trend over historical data. Additionally, we

active can use a weighted moving average to forecast future periods.Key Point => If you have a general upward historical trend, weighted average will tend

to underestimate forecasted values and vice versa (downward trend = overestimate).

Exponential Smoothing and Weighted Moving Averages for Total Revenues:

Years => 1996 1997 1998 1999 2000 2001

Total Revenues - Historical 12076 16719 21196 24737 27448

Total Revenues - Exponential 12076 12076 16719 21196 24737

Total Revenues - Wt Moving Avg 11105 15230 18890 21605 22965 26070

Smoothing Factor must be between 0 and 1 1 Total weights should add up to => 100.00%

Set Smoothing Factor 1

Assign weights to appropriate periods 0.00% 1.50% 4.50% 34.50% 59.50% 100.00%

Find the Optimal Smoothing Factor:

Total Exponent

Revenues Amounts Difference Square

12076 12076

16719 12076 4643 21557449

21196 16719 4477 20043529

24737 21196 3541 12538681

27448 24737 2711 7349521

Mean Squared Error 12297836

Find the Optimal Moving Weights:

Total Weighted

Revenues Amounts Difference Square

12076 11105 971 942841

Home Wksh2

Wksh3 Wksh4

Wksh5 Wksh6

Wksh7 Wksh8

Wksh9 Wksh10

Wksh11 Wksh12

Wksh14

Wksh15 Wksh16


51/85


16719 15230 1489 2217121

21196 18890 2306 5317636

24737 21605 3132 9809424

27448 22965 4483 20100607

Mean Squared Error 7677526



2001 2002 2003 2004 2005


Gross Revenues 26070 26413 26316 26355 26340

2001 2002 2003 2004 2005

5,000

10,000

15,000

20,000

25,000

30,000

Exponential Comparison

TotalRevenues -Historical

Total

Revenues -Exponential

TotalRevenues - WtMoving Avg

Periods

TotalReve n

ues


52/85




Operating Income 9527 9979 10227 10197 9740



Net Income 6977 7429 7277 7297 6440



Net Income 6977 7429 7277 7297 6440


(Increase) Decrease Defer Taxes 0 0 0 0 0


(Increase) Decrease Current Assets #REF! -92 26 -11 4

Increase (Decrease) Current Liab #REF! 120 -34 14 -5

Operating Cash Flow #REF! 7956 7771 7824 6995



Planned Sale of Investments 2200 2100 1900 1800 1700Other Investment Sources to be used

Total Investment Sources of Cash 2300 2160 1920 1825 1735



Acquisitions -500 -750 -500


Total Investment Applications of Cash -6000 -6750 -6600 -4900 -5600


53/85



Proceeds from Loans & Debt 1300 2000 3000 4500 7000





Payments on Loans & Debt -1500 -1800 -2500 -4000 -6000


Purchase / Retire Stock


Total Financing Applications of Cash -4000 -4800 -6500 -8000 -9000

Total Change to Cash #REF! 626 -329 1339 1230





Short Term Marketable Securities


Inventory 2086 2113 2105 2108 2107



Fixed Assets 31600 34600 37700 41600 46200


Net Fixed Assets 28120 30630 33230 36610 40660




Total Non Current Assets 29812 33997 38247 40827 44127


54/85






Other Current Liabilities 1564 1585 1579 1581 1580Total Current Liabilities 9155 9274 9236 9244 9234





Preferred Equity

Common Equity 2200 2200 2200 2200 2200



Adj for Foreign Currency Transl -5000 -3500 -1000

Treasury Stock -1550 -1550 -1550 -1550 -1550

Total Equity 18855 17355 15855 12855 9855



55/85


2002 2003 2004 2005

26413 26316 26,355 26,340


56/85



57/85



58/85



59/85



60/85


Scenario Analysis for


We can copy our forecast into a new worksheet and do scenario analysis and goal-seek analysis.

Although Microsoft Excel includes Scenario Manager, it can be easier and quicker to simply do our

scenario analysis manually. We can use Goal Seek to find a value for a cell given a corresponding

active formula in another cell.



2001 2002 2003 2004 2005

Pro Forma Income Statement (Simple Projection Method)

Scenario => Non Operating Expenses will decline by $ 100,000 in year 2003 and again in year 2005:

Gross Revenues 30742 34431 38562 43190 48373

Cost of Goods Sold -12024 -13467 -15083 -16893 -18920Operating Expenses -7483 -8381 -9387 -10513 -11775



Net Income 8,685 10,033 11,643 13,384 15,378

Instead of copying our forecast into this worksheet, we can simply do scenario analysis directly in

the forecast itself.

Pro Forma Income Statement (Linear Trend Method)

Goal Seek => What total revenues are required if Net Income must be $ 13.5 million in Year 2004?

Gross Revenues 32064 35940 39816 44301 47569



Non Operating Expenses -3549 -4088 -4627 -5166 -5705

Extra Ordinary Items 271 271 271 271 271

Net Income (formula cells) 9430 10584 11738 13500 14046

Do not change formula cells (goals) to values

Home Wksh2

Wksh3 Wksh4

Wksh5 Wksh6

Wksh7 Wksh8

Wksh9 Wksh10

Wksh11 Wksh12

Wksh13

Wksh15 Wksh16


61/85


when using Goal Seek (Tools > Goal Seek)

This is the target cell that Goal Seek is seeking

to change per the value 13,500 in cell G34


62/85


Budget Analysis for


Once we complete our forecast, we can summarize and review it before finalizing it

into the form of budgets. We also need to summarize our assumptions that should

go into our final budget. We can start our budget process by reviewing the different

revenue forecast:active



2001 2002 2003 2004 2005

Summarize Revenue Forecast:

Simple Projection Model 30,742 34,431 38,562 43,190 48,373

Linear Trend Model 32064 35940 39816 43692 47569

Wt Moving Avg Model 26070 26413 26316 26355 26340Declining Growth Model 29644 31422 32679 33170 31000

Historical Data 12076 16719 21196 24737 27448

Projection using declining growth rates:

Gross Revenues 29644 31422 32679 33170 31000

Home Wksh2

Wksh3 Wksh4

Wksh5 Wksh6

Wksh7 Wksh8

Wksh9 Wksh10

Wksh11 Wksh12

Wksh13 Wksh14

Wksh16

40,000

50,000

60,000

Forecast Comparisons

SimpleProjectionModel

Linear TrendModel

Wt Movingenues


63/85


In addition to using linear models for forecasting, we can apply several non-linear (curve) models:

Logarithmic - Used when rate of change in data suddenly shifts upward or downward.

Power - Used when rate of change in data occurs at a specific rate.

Exponential - Used when rate of change is increasing or decreasing at ever higher rates.

Polynomial - Used when rate of change fluctuates with no pattern.

Logarithmic Trend

Actual Predicted Slope Intercept

Formula for Logarithmic Trendline x factor Values Value y c factor b factor

1 12,076 -11242 9600.92 11242.34

y = ( c * LN (x)) - b 2 16,719 -4587

3 21,196 -695

LN: Natural Logarithm 4 24,737 2067

5 27,448 4210

6 5960

7 74408 8722

9 9853

10 10865

Power Trendline


y = b * x^c x factor Values Value y c factor b factor

1 12,076 11951 0.52 9.39

2001 2002 2003 2004 20050

10,000

20,000

, Avg ModelDecliningGrowth Model

Historical Data

Periods

TotalRe


64/85


2 16,719 17109 11951.33

3 21,196 21104

4 24,737 24493

5 27,448 27491

6 30212

7 32721

8 350639 37267

10 39356

Exponential Trendline < - calculate using c an


x factor Values Value y c factor b factor ( c * x) EXP

y = b * EXP ( c * x ) 1 12,076 13061 0.2 9.27 0.20339 1.23

2 16,719 16007 10657.5 0.40678 1.5

3 21,196 19618 0.61017 1.84

4 24,737 24043 0.81356 2.26

5 27,448 29465 1.01695 2.766 36111 1.22034 3.39

7 44256 1.42373 4.15

8 54238 1.62712 5.09

9 66472 1.83051 6.24

10 81464 2.03390 7.64

Polynomial Trendline

Actual Predicted

y = (c2 * x^2) + (c1 * x^1) + b x factor Values Value y c2 c1 b

1 12,076 11997 -342.86 5933.34 6406.62 16,719 16902

3 21,196 21121

4 24,737 24654

5 27,448 27502

6 29664

7 31140

8 31930

9 32035


65/85


10 31454

Summarize Non Linear Curves on Graph:

1996 1997 1998 1999 2000 2001 2002

Logarithmic Trendline -11242 -4587 -695 2067 4210 5960 7440

Power Trendline 11951 17109 21104 24493 27491 30212 32721

Exponential Trendline 13061 16007 19618 24043 29465 36111 44256

Polynomial Trendline 11997 16902 21121 24654 27502 29664 31140

Actual Revenues 12,076 16,719 21,196 24,737 27,448

1996

1997

1998

1999

2000

2001

2002

2003

2004

2005

(20,000)

20,000

40,000

60,000

80,000

100,000

Non Linear Trends

LogarithmicTrendline

PowerTrendline

ExponentialTrendline

PolynomialTrendline

ActualRevenues

TotalR

evenue

s


66/85


Variance Analysis of Past Budgets:

Accuracy in the budget process should be examined to determine the degree of error or

variance in the budget process. If the variance is high, this indicates a need to improve

planning techniques within the company. TBD: To be Determined

(Wksht 16)

1996 1997 1998 1999 2000 2001

Income Statement Items

Total Revenues - Budgeted 10500 14500 22500 28500 30000 30500

Total Revenues - Actual 12076 16719 21196 24737 27448 TBD

% difference from actual -13% -13% 6% 15% 9% #VALUE!

Cost of Goods Sold - Budgeted -4500 -6500 -8648 -9650 -11000 -11929

Cost of Goods Sold - Actual -4950 -7050 -8233 -9050 -10150 TBD


Operating Income - Budgeted 3100 4900 9000 11000 10,500 11146

Operating Income - Actual 3815 5776 8309 9995 10150 TBD


Net Income - Budgeted 2100 4100 6500 9000 9300 7986

Net Income - Actual #NAME? #NAME? #NAME? #NAME? #NAME? TBD% difference from actual #NAME? -17% 4% 14% 21% #VALUE!

Balance Sheet Items

Current Assets - Budgeted 3450 4400 6250 7500 7900 #REF!

Current Assets - Actual #REF! #REF! #REF! #REF! #REF! TBD

% difference from actual #REF! -12% 8% 10% 5% #VALUE!

NonCurrent Assets - Budgeted 10900 14000 22000 27500 29,500 31555

Periods


67/85


NonCurrent Assets - Actual #REF! #REF! #REF! #REF! #REF! TBD

% difference from actual #REF! -14% 10% 8% 11% #VALUE!

Current Liabilities - Budgeted 3750 5600 8200 9050 9400 10715

Current Liabilities - Actual #REF! #REF! #REF! #REF! #REF! TBD

% difference from actual #REF! -10% 11% 5% -3% #VALUE!

NonCurrent Liabilities - Budgeted 1750 2450 3750 4900 5100 5250

NonCurrent Liabilities - Actual 1810 2500 3301 4490 4945 TBD


Ratio Items

Current Ratio - Budgeted 1 1 1 1 0.80 #REF!

Current Ratio - Actual #REF! #REF! #REF! #REF! #REF! TBD

% difference from actual #REF! -2% -2% 5% 3% #VALUE!

Total Asset Turnover - Budgeted 0.68 0.72 0.80 0.80 0.79 #REF!

Total Asset Turnover - Actual #REF! #REF! #REF! #REF! #REF! TBD% difference from actual #REF! -8% -3% 4% -2% #VALUE!

Gross Profit Margin - Budgeted 60% 60% 60% 60% 60% 1

Gross Profit Margin - Actual 59% 58% 61% 63% 63% TBD

% difference from actual 2% 4% -2% -5% -5% #VALUE!

Net Profit Margin - Budgeted 19% 25% 28% 30% 28% 26%

Net Profit Margin - Actual #NAME? #NAME? #NAME? #NAME? #NAME? TBD

% difference from actual #NAME? -16% -5% -6% 0% #VALUE!

Debt to Common Equity - Budget 0.45 0.50 0.60 0.65 0.55 #REF!

Debt to Common Equity - Actual 1 1 1 1 1 TBD

% difference from actual -11% -20% -4% 13% 4% #VALUE!

Return on Equity - Budgeted 20% 29% 32% 35% 30% #REF!

Return on Equity - Actual 24% 33% 35% 33% 26% TBD

% difference from actual -16% -13% -9% 5% 16% #VALUE!


68/85


We can use specific measurements to track and control forecasting errors:

Mean Absolute Error - An absolute value of forecast errors, does not place weight on the

amount of the error. Calculated as the sum of (actual values - predicted values) / n.

Mean Square Error - Similar to Mean Absolute Error, but does place more emphasis on

the amount of error; i.e. an error of 8 is twice as significant as 4. Calculated as the

sum of (actual values - predicted values)^2 / n.

Root Mean Square Error - To make the Mean Square Error useful and comparable to the Mean

Absolute Error, we can take the square root of the Mean Square Error. We can then use this

as a guide to establish an error limit or standard for flagging unacceptable errors. Is Error

Actual Forecasted Error Outside

Example: Total Revenues Period Revenues Revenues Error Absolute Squared Limit?

n: total number of periods 1996 12076 10500 1576 1576 2483776 No

1997 16719 14500 2219 2219 4923961 Yes

1998 21196 22500 -1304 1304 1700416 No1999 24737 28500 -3763 3763 14160169 Yes

2000 27448 30000 -2552 2552 6512704 Yes

n => 5 Sum => -3824 3824 14622976 Yes

Mean Absolute Error 765

Mean Square Error 2924595

Root Mean Sqr Error 1,710

Establish Error Limits 1,710


69/85



70/85



71/85


d b factor - >

Predicted

Value y

13061

16007

19618

24043

2946536111

44256

54238

66472

81464


72/85


2003 2004 2005

8722 9853 10865

35063 37267 39356

54238 66472 81464

31930 32035 31454


73/85



74/85



75/85



76/85


Final Budgets for


Now that we have analyzed our historical data and placed it into a set of

forecast, we can pull it all together with our assumptions for a final budget.

Many of these assumptions should be included in our forecast for improved

accuracy. However, we need to fine tune and finalize all assumptionsactive so that we can produce a final finished budget for planning purposes.

Budget

Period

Ref 2001 Assumptions & Comments

Operating Plan

16-1 Total Revenues 30500 Based on review of Pro Forma Financials, Marketing, etc.

16-2 Cost of Goods Sold -11929 Volume projections, production budgets, and vertical analysis16-3 Operating Expenses -7424 Average % of Sales per Vertical Analysis16-4 Operating Income 11146

16-5 Interest Expenses -310 Based on anticipated levels of debt and past history

16-6 Income Taxes -3300 Based on anticipated taxable income and effective rate16-7 Other Non Operating Expenses -200 Provision for contingency was added on this line item

16-8 Earnings Before Extra Ord Items 733616-9 Extra Ordinary Items 650 Per our Simple Model Forecast

16-10 Net Income 7986

Financial Plan

Budgeted Cash Flows

16-11 Net Income 798616-12 Depreciation and Amortization 470 Review of Simple Model Forecast and Capital Expenditure Bud

16-13 (Increase) Decrease Defer Taxes

Home Wksh2

Wksh3 Wksh4

Wksh5 Wksh6

Wksh7 Wksh8

Wksh9 Wksh10

Wksh11 Wksh12

Wksh13 Wksh14Wksh15


77/85


16-14 (Gain) Loss on Sale of Assets 15 Per Simple Forecast Model

16-15 (Increase) Decrease Current Assets #REF! Same formula as used in forecast models

16-16 (Increase) Decrease Current Liab #REF! Same formula as used in forecast models

16-17 Operating Cash Flow #REF!

Investment Sources of Cash:

16-18 Planned Sale of Assets 100 Per Simple Model Forecast

16-19 Planned Sale of Investments 2200 Per Simple Model Forecast

16-20 Other Investment Sources to be use

16-21 Total Investment Sources of Cash 2300


16-22 Capital Expenditures -4500 Budgeted $ 4.5 million in Capital Expenditure Budget

16-23 Acquisitions in Other Co's -350 Per forecast, strategic plan, and other budgets

16-24 Purchases of Investments -2500 Per forecast, strategic plan, and other budgets

16-25 Total Investment Applications of Cas -7350

Cash Flow from Financing Activities

16-26 Proceeds from Loans & Debt 1450 Per Financing Requirements and other budgets

16-27 Proceeds from Minority Interest 15 Per historical financials and investment budget

16-28 Other Financing Activities

16-29 Total Financing Sources of Cash 1465


16-30 Payments on Loans and Debt -1250 Per forecast and other budgets

16-31 Dividends Paid to Shareholders -2500 Per Simple Model Forecast

16-32 Purchase / Retire Stock -1500 Per strategic plan and other budgets16-33 Other Financing Activities

16-34 Total Financing Applications of Cas -5250

16-35 Total Change to Cash #REF!

16-36 Beginning Cash Balance 870

16-37 Forecasted Ending Balance #REF!


78/85


Budgeted Balance Sheet

16-38 Cash and Cash Equivalents #REF! Per above

16-39 Short Term Marketable Securities 10 Per historical financials

16-40 Accounts Receivable 3050 Same formula as used in forecast models

16-41 Inventory 2440 Same formula as used in forecast models

16-42 Other Current Assets 1983 Same formula as used in forecast models

16-43 Total Current Assets #REF!

16-44 Fixed Assets 32600 Same formula as used in forecast models

16-45 Accumulated Depreciation -3480 Same formula as used in forecast models

16-46 Net Fixed Assets 29120

16-47 Longterm Investments 1205 Same formula as used in forecast models

16-48 Investments in Other Companies 1000 Per review of forecast and strategic plans

16-49 Intangibles and Other Assets 230 Per review of forecast and historical balances

16-50 Total Non Current Assets 31555

16-51 Total Assets #REF!

16-52 Accounts Payable 5185 Same formula as used in forecast models

16-53 Short Term Borrowings 3660 Same formula as used in forecast models

16-54 Short Term Portion of LT Debt 40 Per review of forecast and historical information

16-55 Other Current Liabilities 1830 Same formula as used in forecast models

16-56 Total Current Liabilities 10715

16-57 Longterm Debt / Borrowings 4150 Same formula as used in forecast models

16-58 Other Longterm Liabilities 1100 Per review of historical information and expected growth rates.

16-59 Total Non Current Liabilities 5250

16-60 Total Liabilities 15965

16-61 Preferred Stock

16-62 Common Equity 2200 Per Simple Model Forecast

16-63 Additional Paid in Capital 5700 Per Simple Model Forecast

16-64 Retained Earnings 25491 Same formula as used in forecast models

16-65 Adj for Foreign Currency Translation -5000 Per Simple Model Forecast


79/85


16-66 Treasury Stock -3050 Same formula as used in forecast models

16-67 Total Equity 25341

16-68 Total Liabilities and Equity 41306

16-69 External Financing Required #REF!


80/85


get


81/85



82/85



83/85


Microsoft Excel 9 0 Answer Report


84/85


Microsoft Excel 9.0 Answer Report

Worksheet: [Detail_Analysis.xls]13 - Pro Forma (Exp)

Report Created: 3/16/2002 3:42:28 PM

Target Cell (Min)

Cell Name Original Value Final Value

$G$60Mean Squared Error Square 20525549 12297836

Adjustable Cells


$D$24 Set Smoothing Factor 1 1

Constraints

Cell Name Cell Value Formula Status Slack

$D$23Smoothing Factor must be between 0 and $D$23>=$D$2 Binding

$D$24 Set Smoothing Factor 1 $D$24


85/85

Microsoft Excel 9.0 Answer Report

Worksheet: [Detail_Analysis.xls]13 - Pro Forma (Exp)

Report Created: 3/16/2002 5:19:05 PM

Target Cell (Min)


$G$46Mean Squared Error Square 65536151 59853342

Adjustable Cells


$D$25Assign weights to appropriate periods 0.00% 0.00%

$E$25 Assign weights to appropriate periods 2.00% 1.50%

$F$25 Assign weights to appropriate periods Total weights should add up t 5.00% 4.50%

$G$25Assign weights to appropriate periods 35.00% 34.50%

$H$25 Assign weights to appropriate periods 60.00% 59.50%

Constraints

Cell Name Cell Value Formula Status Slack

$I$25 Assign weights to appropriate periods 100.00% $I$25=$E$23 Binding 0

$D$25 Assign weights to appropriate periods 0.00% $D$25=$D$2 Not Binding ###

fs analisis lengkap

Documents