Financial Analysis

Financial Analysis & Forecasting Purpose of Spreadsheet:

Prepared by Sumit Kumar Jain CA,CS,B.com(Hons.) Revised:

11/22/2002

active Wksh3

To illustrate concepts related to financial analysis and forecasting. The financial analysis uses a combination of ratios and industry averages to evaluate 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.

Wksh5 Wksh7 Wksh9 Wksh11 Wksh13 Wksh15

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 reporting periods (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 2 3 4 5

Title General Input Balance Sheet Income Statement Cash Flow Statement

Purpose Enter general information here - used on several worksheets. Enter comparative 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 formulas may have to be revised. B) Output Worksheets for evaluating financial performance: 6 Key Financial Data Calculates key financial information for further analysis. 7 Ratio Analysis Calculates a series of ratios for further analysis. 8 Benchmark Analysis Compare ratio analysis to industry averages. 9 Horizontal Analysis Horizontal analysis with corresponding trend lines. 10 Vertical Analysis Common size financials in percentages and graphs. C) Pro Forma / Forecasted Financials for Budgeting: 11

Pro Forma - Simple

Set of pro forma financials using simple assumptions

12 13 14 15 16

Pro Forma - Regression Pro Forma - Exponential Scenario Analysis Budget Analysis Final Budgets

Set of pro forma financials using linear trending Set of pro forma financials using exponential smoothing Example of Scenario Analysis and Goal Seek Analysis Preliminary budget analysis 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 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 I’ll be glad to improve the financial model. My email address is: [email protected]

CA,CS,B.com(Hons.) Wksh2 Wksh4 Wksh6 Wksh8 Wksh10 Wksh12 Wksh14 Wksh16

sed on several worksheets. s for up to five periods. ents for up to five periods. ments for up to five periods. certain worksheet

n for further analysis. ther analysis.

ding trend lines. ages and graphs.

mple assumptions

near trending xponential smoothing Goal Seek Analysis

ptions and forecasts.

General Input Panel

Home

The following general information should be entered: Note: Sample data has been entered in the input cells to help you get started.

Wksh3 Wksh5 Wksh7

2-1

Name of Company => XYZ Ltd

Wksh9 Wksh11

2-2

Reporting Periods =>

Annual

(Annual, Semi-annual, Quarterly or Monthly)

2-3

Number of Days in Reporting Period are

Wksh13 Wksh15

365

What reporting periods will be entered? 2-4 2-5 2-6 2-7 2-8

Most Current Period Previous Period 2nd Previous Period 3rd Previous Period 4th Previous Period

2008 (1999, July 1998, 6/30/97, etc.) 2007 (1999, July 1998, 6/30/97, etc.) 2006 (1999, July 1998, 6/30/97, etc.) 2005 (1999, July 1998, 6/30/97, etc.) 2004 (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 Rs.

active Wksh4 Wksh6 Wksh8 Wksh10 Wksh12 Wksh14 Wksh16

Balance Sheet for XYZ Ltd

Description

Annual Period 2004

Annual Period 2005

millions of Rs. Annual Period 2006

Annual Period 2007

Annual Period 2008

Cash and Cash Equivalents Short Term Marketable Securities Accounts Receivable Inventory Other Current Assets Total Current Assets

990 10 1,020 1,005 870 3,895

950 15 1,550 1,360 1,150 5,025

901 12 1,830 1,650 1,370 5,763

998 6 2,250 1,900 1,650 6,804

870 11 3,040 2,060 1,530 7,511

Fixed Assets Accumulated Depreciation Net Fixed Assets Longterm Investments Investments in Other Companies Intangibles and Other Assets Total Non Current Assets Total Assets

14,006 (1,280) 12,726 360 65 100 13,251 17,146

17,605 (1,700) 15,905 320 0 110 16,335 21,360

21,826 (2,100) 19,726 120 0 105 19,951 25,714

26,950 (2,550) 24,400 590 250 135 25,375 32,179

28,100 (3,010) 25,090 905 412 195 26,602 34,113

Accounts Payable Short Term Borrowings Short Term Portion of LT Debt Other Current Liabilities Total Current Liabilities

2,050 1,200 12 1,050 4,312

3,150 1,830 15 1,250 6,245

3,290 2,580 25 1,480 7,375

3,870 3,100 30 1,590 8,590

4,800 3,550 36 1,301 9,687

Longterm Debt / Borrowings Other Longterm Liabilities Total Non Current Liabilities Total Liabilities

1,160 650 1,810 6,122

1,750 750 2,500 8,745

2,600 701 3,301 10,676

3,600 890 4,490 13,080

3,950 995 4,945 14,632

Prepared by Matt H. Evans 10/12/2009

Page 7

Balance Sheet for XYZ Ltd

Description

Annual Period 2004

Annual Period 2005

millions of Rs. Annual Period 2006

Annual Period 2007

Annual Period 2008

Preferred Equity Common Equity Additional Paid in Capital Retained Earnings Adj for Foreign Currency Transl Treasury Stock Total Shareholder Equity

0 2,044 5,013 5,097 275 (1,405) 11,024

0 2,005 4,900 7,050 120 (1,460) 12,615

0 2,069 5,159 9,840 (550) (1,480) 15,038

0 2,090 5,626 15,050 (2,147) (1,520) 19,099

0 2,120 5,628 20,005 (6,722) (1,550) 19,481

Total Liabilities & Equity

17,146

21,360

25,714

32,179

34,113

0 Balances

0 Balances

0 Balances

0 Balances

0 Balances

0 112 0 1,320 $10.00 0

0 101 0 1,290 $10.00 0

0 90 0 1,302 $10.00 0

0 98 0 1,345 $10.00 0

0 109 0 1,322 $10.00 0

$22.65 $0.00 0 0 $8.35 $1.01

$28.90 $0.00 0 0 $9.78 $1.49

$37.05 $0.00 0 0 $11.55 $1.89

$33.60 $0.00 0 0 $14.20 $1.75

$29.40 $0.00 0 0 $14.74 $1.76

Check: Assets = Liab + Equity ? Comment =>

NonDepreciable Fixed Assets Deferred Taxes Goodwill Write Off No of Common Shares o/s Par Value of Common Stock No of Preferred Shares o/s Par Value of Preferred Stock Market Price of Common Stock Market Price of Preferred Stock Preferred Dividends in Arrears Liquidating value of Preferred Stk Book Value per Share Dividends per Common Share

Prepared by Matt H. Evans 10/12/2009

Page 8

Balance Sheet for XYZ Ltd

Description Dividend Payout Ratio Cash Dividends to Preferred Stock Cash Dividends to Common Stock Total Dividends Paid

Annual Period 2004

Annual Period 2005

45.47% 0 1,330 1,330

38.61% 0 1,918 1,918

millions of Rs. Annual Period 2006 39.44% 0 2,461 2,461

Prepared by Matt H. Evans 10/12/2009

Annual Period 2007 29.76% 0 2,354 2,354

Annual Period 2008 30.24% 0 2,329 2,329

Page 9

Income Statement for XYZ Ltd Annual Period 2004 12,060 16 12,076 (4,950) (11) (4,961) (3,300) 3,815

Annual Period 2005 16,700 19 16,719 (7,050) (13) (7,063) (3,880) 5,776

millions of Rs. Annual Period 2006 21,170 26 21,196 (8,233) (17) (8,250) (4,637) 8,309

Annual Period 2007 24,700 37 24,737 (9,050) (22) (9,072) (5,670) 9,995

Annual Period 2008 27,400 48 27,448 (10,150) (28) (10,178) (7,120) 10,150

Interest Expenses Foreign Exchange (Loss) Gain Associated Company (Loss) Gain Other NonOperating (Loss) Gain Income Tax Expense Reserve Charges Income Before Extra Ord Items

(117) 0 0 0 (790) 0 2,908

(122) 0 0 17 (1,005) 0 4,666

(216) 0 (22) 0 (2,050) 0 6,021

(282) 0 0 0 (2,105) 0 7,608

(304) 0 0 0 (2,660) 0 7,186

Extra Ordinary Items (Loss) Gain Tax Effects of Extraordinary Items Minority Interests Net Income

0 0 17 2,925

0 0 302 4,968

0 0 219 6,240

0 0 303 7,911

0 0 515 7,701

Primary EPS Earnings Before Int & Taxes Depreciation & Amortization Research & Devel Expenses Capitalized Interest Expense Interest Income

$2.22 3,832 (310) 0 (16) 4

$3.85 6,095 (420) 0 (19) 6

$4.79 8,506 (400) 0 (33) 11

$5.88 10,298 (450) 0 (39) 19

$5.83 10,665 (460) 0 (30) 27

Description Net Sales Other Operating Revenues Total Revenues Cost of Goods Sold Other Operating Expenses Total Direct Expenses Selling, General & Administrative Operating Income

Prepared by Matt H. Evans 10/12/2009

Page 10

Income Statement for XYZ Ltd

Description Total Non Operating Expenses Total Extra Ordinary Items Tax Rate

Annual Period 2004 (907) 17 21.36%

Annual Period 2005 (1,110) 302 17.78%

millions of Rs. Annual Period 2006 (2,288) 219 25.33%

Prepared by Matt H. Evans 10/12/2009

Annual Period 2007 (2,387) 303 21.67%

Annual Period 2008 (2,964) 515 27.02%

Page 11

Cash Flow Statement for XYZ Ltd

Description Net Income Depreciation and Amortization (Increase) Decrease Defer Taxes (Gain) Loss on Sale of Assets (Increase) Decrease Current Assets Increase (Decrease) Current Liab Cash Flow from Operations

Annual Period 2004

millions of Rs. Annual Period 2006

Annual Period 2005

Annual Period 2007

Annual Period 2008

2,925 310 (2) (55) (162) 206 3,222

4,968 420 11 0 (1,130) 1,933 6,202

6,240 400 11 45 (738) 1,130 7,088

7,911 450 (8) 0 (1,041) 1,215 8,527

7,701 460 (11) 0 (707) 1,097 8,540

Capital Expenditures Acquisition in Other Co's Proceeds from Sales of Assets Purchases of Investments Sale of Investments Other Investment Activities Cash Provided (Used) from Investmts

(1,455) (135) 112 (712) 162 33 (1,995)

(2,750) 0 35 (1,979) 129 (166) (4,731)

(3,880) 0 0 (1,801) 330 61 (5,290)

(5,220) 0 150 (2,314) 221 (12) (7,175)

(4,108) 0 182 (2,609) 50 0 (6,485)

Proceeds from Borrowings Payments on Borrowings Dividends Paid to Shareholders Proceeds from Minority Interest Issue Stock / Exercise Options Purchase / Retire Common Stock Other Financing Activities Cash Provided (Used) from Financing

1,070 (1,112) (1,330) 5 195 0 (75) (1,247)

1,044 (650) (1,918) 12 1 0 0 (1,511)

1,460 (898) (2,461) 7 45 0 0 (1,847)

1,880 (801) (2,354) 7 13 0 0 (1,255)

1,105 (961) (2,329) 8 6 0 (12) (2,183)

(20)

(40)

(49)

97

(128)

Increase (Decrease) to Cash

Prepared by Matt H. Evans 10/12/2009

Page 12

Cash Flow Statement for XYZ Ltd

Description Beginning Cash Balance Ending Cash Balance Check: Should agree to Balance Sheet Comment =>

Annual Period 2004 1,010 990 0 Balances

Annual Period 2005 990 950 0 Balances

millions of Rs. Annual Period 2006 950 901 0 Balances

Prepared by Matt H. Evans 10/12/2009

Annual Period 2007 901 998 0 Balances

Annual Period 2008 998 870 0 Balances

Page 13

Key Financial Data for XYZ Ltd

Description EBITDA : Income before ExtraOrd Items Interest Expense Capitalized Interest Expense Income Tax Expense Reserve Charges Depreciation and Amortization EBITDA EBITDA Margin Free Cash Flow: Operating Cash Flow Investment Cash Flows Preferred Dividends Paid (fixed) Redemption of Fixed Obligations Other Critical Outlays Free Cash Flow Working Capital: Current Assets Current Liabilities Working Capital Liquid Capital: Cash and Cash Equivalents Marketable Securities Accounts Receivable Notes Receivable Total Current Liabilities Long Term Debt Preferred Equity Liquid Capital

Annual Period 2004

Annual Period 2005

millions of Rs. Annual Period 2006

Annual Period 2007

Annual Period 2008

2,908 117 16 790 0 310 4,141

4,666 122 19 1,005 0 420 6,232

6,021 216 33 2,050 0 400 8,720

7,608 282 39 2,105 0 450 10,484

7,186 304 30 2,660 0 460 10,640

34%

37%

41%

42%

39%

3,222 (1,995) 0 (1,112) (35) 80

6,202 (4,731) 0 (650) (45) 776

7,088 (5,290) 0 (898) (42) 858

8,527 (7,175) 0 (801) (30) 521

8,540 (6,485) 0 (961) (25) 1,069

3,895 4,312 (417)

5,025 6,245 (1,220)

5,763 7,375 (1,612)

6,804 8,590 (1,786)

7,511 9,687 (2,176)

990 10 1,020 0 (4,312) (1,160) 0 (3,452)

950 15 1,550 0 (6,245) (1,750) 0 (5,480)

901 12 1,830 0 (7,375) (2,600) 0 (7,232)

998 6 2,250 0 (8,590) (3,600) 0 (8,936)

870 11 3,040 0 (9,687) (3,950) 0 (9,716)

The following valuation indicators are very simple and basic; they are used as quick, rough estmates. Market Capitalization: Market Cap - Common Stk Market Cap - Preferred Stk Total Market Capitalization Present Value: Normalized Cash Flow Weight %'s

$29,898 $0.00 $29,898

$37,281 $0.00 $37,281

$48,239 $0.00 $48,239

$45,192 $0.00 $45,192

$38,867 $0.00 $38,867

5.00%

10.00%

15.00%

30.00%

40.00%

Key Financial Data for XYZ Ltd

Description Normalized Cash Flow Number of Future Periods Required Rate of Return Present Value of Free Cash Flow Present Value of Selling Price Present Value of Business

Annual Period 2004

Annual Period 2005

millions of Rs. Annual Period 2006

Annual Period 2007

$315,000 <= estimated selling price

Revenue Multiplier: Recent Gross Revenues Average Competitive Rev Multiplier Value based on Revenue Multiple Capitalization of Earnings: Normalized Net Income Weights % Normalized Net Income Capitalization Rate Nominal Growth Rate Net Capitalization Rate Value based on Earnings

Annual Period 2008 794 15 11.00% $5,711 $65,836 $71,547

27,448 3.14 $86,187

5.00%

25.00%

30.00%

35.00% 6,681 12.00% 3.50% 8.50% $78,605

Operating Leverage

1.31

1.49

1.21

0.14

Financial Leverage

1.46

0.61

1.12

(0.65)

Total Leverage

1.92

0.91

1.36

(0.09)

1.92

0.91

1.36

(0.09)

Check Totals

5.00%

0.00

Ratio Analysis for XYZ Ltd

Title of Ratio

Annual Period 2004

Annual Period 2005

Annual Period 2006

Annual Period 2007

Annual Period 2008

Acid Test Ratio

0.47

0.40

0.37

0.38

0.40

Current Ratio

0.90

0.80

0.78

0.79

0.78

Operating Cash Flow to Net Income

1.10

1.25

1.14

1.08

1.11

0 990 0 11 10 110 34 1,020 34,257 79 1,005 79,745 16 870 13,920 33

0 950 0 12 15 180 30 1,550 46,158 61 1,360 83,261 22 1,150 25,300 31

0 901 0 16 12 192 31 1,830 56,217 67 1,650 110,092 26 1,370 35,620 35

0 998 0 15 6 90 32 2,250 72,213 72 1,900 136,018 21 1,650 34,650 36

0 870 0 14 11 154 36 3,040 110,751 71 2,060 146,676 19 1,530 29,070 38

(0.03) 0.42 0.74 15.46 0.70 17.29

(0.07) 0.46 0.94 12.78 0.78 14.90

(0.08) 0.54 1.09 11.13 0.82 13.51

(0.07) 0.65 1.06 7.53 0.77 9.94

(0.08) 0.82 1.03 5.90 0.80 8.48

11,520 1,060 10.9

15,750 1,285 12.3

20,080 1,690 11.9

23,200 2,040 11.4

26,500 2,645 10.0

34

30

31

32

36

1,046 4.6

1,183 6.0

1,505 5.5

1,775 5.1

1,980 5.1

79

61

67

72

71

0.7

0.8

0.8

0.8

0.8

Liquidity Index: Cash - Days Removed Cash Balance Cash Balance Total Marketable Sec - Days Removed Marketable Securities Balance Marketable Securities Total Receivables - Days Removed Receivable Balance Receivable Balance Total Inventory - Days Removed Inventory Balance Inventory Balance Total Other - Days Removed Other Current Assets Balance Other Current Assets Total Liquidity Index (Days) Z Score: 1.2 x (working capital / total assets) 1.4 x (retained earn / total assets) 3.3 x (EBIT / total assets) .6 x (market value equity / b.v. debt) .999 x (sales / total assets) Z Score

Receivable Turnover: Credit Sales Average Receivable Balance Receivable Turnover Days Required to Collect A/R

Inventory Turnover: Average Inventory Balance Inventory Turnover Days in Inventory Total Asset Turnover

Ratio Analysis for XYZ Ltd

Title of Ratio

Annual Period 2004

Annual Period 2005

Annual Period 2006

Annual Period 2007

Annual Period 2008

Operating Assets Ratio

0.97

0.98

0.99

0.97

0.96

Gross Profit Margin

59%

58%

61%

63%

63%

Operating Margin

32%

35%

39%

40%

37%

Net Profit Margin

24%

30%

29%

32%

28%

Direct Cost to Operating Revenues

41%

42%

39%

37%

37%

Capitalization Rate / Asset Return: Net Operating Income Total Investments / Operating Assets Capitalization Rate / Return

3,000 16,621 18.05%

4,749 20,930 22.69%

6,204 25,489 24.34%

7,829 31,204 25.09%

7,408 32,601 22.72%

Return on Shareholder Equity

24%

33%

35%

33%

26%

Debt to Total Assets

0.36

0.41

0.42

0.41

0.43

Debt to Common Equity

0.50

0.63

0.63

0.57

0.53

33

50

39

37

35

10.2

7.5

7.7

5.7

5.0

2.7

3.0

3.2

2.4

2.0

4.45%

5.14%

5.10%

5.21%

5.99%

Times Interest Earned

Price to Earnings (P/E) Price to Book Value Stock Yield

Home

Wksh2

Wksh3

Wksh4

Wksh5

Wksh6

Wksh7

active

Wksh9

Wksh10

Wksh11

Wksh12

Wksh13

Wksh14

Wksh15

Wksh16

Benchmark Analysis for XYZ Ltd 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.

Annual Period 2004

Annual Period 2005

Annual Period 2006

Ref

Description

8-1 7-1

Acid Test Ratio - Industry Acid Test Ratio - Company

0.51 0.47

0.52 0.40

0.50 0.37

8-2 7-2

Current Ratio - Industry Current Ratio - Company

1.12 0.90

1.10 0.80

1.05 0.78

8-3 7-21

Receivable Turnover - Industry Receivable Turnover - Company

8.50 10.87

8.70 12.26

8.40 11.88

8-4 7-22

Days to Collect A/R - Industry Days to Collect A/R - Company

41 34

39 30

40 31

8-5 7-25

Inventory Turnover - Industry Inventory Turnover - Company

4.40 4.60

4.45 5.96

4.20 5.47

8-6 7-26

Days in Inventory - Industry Days in Inventory - Company

86 79

88 61

81 67

8-7 7-27

Asset Turnover - Industry Asset Turnover - Company

1.60 0.70

1.55 0.78

1.62 0.82

8-8 7-29

Gross Profit Margin - Industry Gross Profit Margin - Company

55.00% 58.96%

52.00% 57.78%

60.00% 61.11%

8-10 7-31

Net Profit Margin - Industry Net Profit Margin - Company

20.00% 24.22%

23.00% 29.71%

22.00% 29.44%

8-11 8-12

Return on Total Assets - Industry Return on Total Assets - Company

38.00% 30.00%

36.00% 25.80%

37.00% 26.51%

8-13 8-14

Return on Equity - Industry Return on Equity - Company

26.00% 23.93%

22.00% 33.44%

24.00% 35.28%

8-15 7-38

Debt to Equity - Industry Debt to Equity - Company

35% 50%

38% 63%

31% 63%

8-16 7-39

Times Interest Earned - Industry Times Interest Earned - Company

40 33

4-13 8-17 8-18

We added the following two ratios to assess if the Company has excessive growth: Provision for Taxes (790) (1,005) (2,050) Trading Ratio - Company 1.24 1.37 1.52 Trading Ratio - Industry 1.26 1.28 1.31

8-19 8-20

Net Sales to Net Worth - Company Net Sales to Net Worth - Industry

1.18 1.22

38 50

1.44 1.20

36 39

1.63 1.19

Source for Benchmark Data: Almanac of Business and Industrial Financial Ratios by Leo Troy, Prentice Ha Graphs appear below for each of the above comparisons:

Acid Ratio Comparison 0.60 0.50

Ratio

0.40

Acid Test Ratio - Industry Acid Test Ratio - Company

0.30 0.20 0.10 0.00 2004

2005

2006

2007

2008

Periods

Current Ratio Comparison 1.20 1.00

Ratio

0.80

Current Ratio Industry Current Ratio Company

0.60 0.40 0.20 0.00 2004

2005

2006 Periods

2007

2008

0.40

Company

0.20 0.00 2004

2005

2006

2007

2008

Periods

Receivable Turnover Comparison 14.00

Turnover Rate

12.00 Receivable Turnover Industry Receivable Turnover Company

10.00 8.00 6.00 4.00 2.00 0.00 2004

2005

2006

2007

2008

Periods

Days to Collect A/R

Receivable Collection Comparison 45 40 35 30 25 20 15 10 5 0 2004

Days A/R Days A/R -

2005

2006

2007

to Collect Industry to Collect Company

2008

Periods

Inventory Turnover Comparison 7.00

Turnover Rate

6.00 Inventory Turnover Industry Inventory Turnover Company

5.00 4.00 3.00 2.00 1.00 0.00 2004

2005

2006

2007

2008

Turnover Rate

6.00 Inventory Turnover Industry Inventory Turnover Company

5.00 4.00 3.00 2.00 1.00 0.00 2004

2005

2006

2007

2008

Periods

Inventory Days Comparison Days Held in Inventory

100 80

Days in Inventory Industry Days in Inventory Company

60 40 20 0 2004

2005

2006

2007

2008

Periods

Turnover Rate

Asset Turnover Comparison 1.80 1.60 1.40 1.20 1.00 0.80 0.60 0.40 0.20 0.00 2004

Asset Turnover - Industry Asset Turnover - Company

2005

2006

2007

2008

Periods

Gross Profit Margin rofit Margin

70.00% 60.00% 50.00% 40.00% 30.00%

Gross Profit Margin Industry Gross Profit

Gross Profit Margin Gross Profit Margin

70.00% 60.00% Gross Profit Margin Industry Gross Profit Margin Company

50.00% 40.00% 30.00% 20.00% 10.00% 0.00% 2004

2005

2006

2007

2008

Periods

Net Profit Margin 35.00%

Net Profit Margin

30.00% Net Profit Margin Industry Net Profit Margin Company

25.00% 20.00% 15.00% 10.00% 5.00% 0.00% 2004

2005

2006

2007

2008

Periods

Return on Total Assets

Return on Total Assets 40.00% 35.00% 30.00% 25.00% 20.00% 15.00% 10.00% 5.00% 0.00% 2004

Return on Total Assets Industry Return on Total Assets Company 2005

2006

Periods

2007

2008

Retur

10.00% 5.00% 0.00% 2004

Company 2005

2006

2007

2008

Periods

Return on Common Equity

Return on Equity Comparison 40.00% 35.00% 30.00% 25.00% 20.00% 15.00% 10.00% 5.00% 0.00% 2004

Return on Total Assets Industry Return on Total Assets Company 2005

2006

2007

2008

Periods

Debt to Equity Comparison Debt to Equity Ratio

70% 60% 50%

Debt to Equity - Industry Debt to Equity - Company

40% 30% 20% 10% 0% 2004

2005

2006

2007

2008

Periods

Times Interest Earned Times Interest Earned

60 50 Times Interest Earned Industry Times Interest Earned Company

40 30 20 10 0 2004

2005

2006 Periods

2007

2008

Times Interest Ear

Times Interest Earned Industry Times Interest Earned Company

40 30 20 10 0 2004

2005

2006

2007

2008

Periods

Trading Ratio Comparison 3.00 2.50

Ratio

2.00

Trading Ratio Industry Trading Ratio Company

1.50 1.00 0.50 0.00 2004

2005

2006

2007

2008

Periods

Sales to Worth Comparison 3.00 2.50 Net Sales to Net Worth Industry Net Sales to Net Worth Company

Ratio

2.00 1.50 1.00 0.50 0.00 2004

2005

2006 Periods

2007

2008

ubject company.

nchmark data, y" line will be null

Annual Period 2007

Annual Period 2008

0.49 0.38

0.48 0.40

1.08 0.79

1.07 0.78

8.20 11.37

8.30 10.02

41 32

41 36

4.30 5.10

4.38 5.13

89 72

82 71

1.68 0.77

1.69 0.80

62.00% 63.36%

63.00% 62.96%

19.00% 31.98%

20.00% 28.06%

34.00% 27.33%

36.00% 23.23%

19.00% 33.42%

20.00% 25.89%

34% 57%

32% 53%

39 37

43 35

(2,105) 1.44 1.30

(2,660) 1.49 1.27

1.45 1.22

1.63 1.20

cial Ratios by Leo Troy, Prentice Hall

Acid Test Ratio - Industry Acid Test Ratio - Company

n

Current Ratio Industry Current Ratio Company

Company

son

Receivable Turnover Industry Receivable Turnover Company

ison

Days A/R Days A/R -

to Collect Industry to Collect Company

son

8

Inventory Turnover Industry Inventory Turnover Company

Inventory Turnover Industry Inventory Turnover Company

8

n

Days in Inventory Industry Days in Inventory Company

8

n

Asset Turnover - Industry Asset Turnover - Company

Gross Profit Margin Industry Gross Profit

Gross Profit Margin Industry Gross Profit Margin Company

Net Profit Margin Industry Net Profit Margin Company

08

Return on Total Assets Industry Return on Total Assets Company

Company

on

Return on Total Assets Industry Return on Total Assets Company

n

Debt to Equity - Industry Debt to Equity - Company

Times Interest Earned Industry Times Interest Earned Company

Times Interest Earned Industry Times Interest Earned Company

Trading Ratio Industry Trading Ratio Company

n

Net Sales to Net Worth Industry Net Sales to Net Worth Company

Horizontal Analysis for XYZ Ltd 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

Annual Period 2004

Annual Period 2005

Annual Period 2006

Annual Period 2007

Annual Period 2008

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%

2.90%

-1.99%

5.76%

3.68%

-0.64%

16.50% 12.90%

4.27% 27.22%

77.05% 103.98%

30.56% 2.68%

7.80% 26.37%

3.80%

22.38%

106.13%

4.33%

24.17%

Growth in Minority Interest

96.00%

1676.47%

-27.48%

38.36%

69.97%

Growth in Net Income

33.50%

69.85%

25.60%

26.78%

-2.65%

Growth in Earnings Per Share

32.60%

73.80%

24.45%

22.73%

-0.96%

Cash and Cash Equivalents Short Term Marketable Securities Accounts Receivable Inventory Other Current Assets Total Current Assets

3.08% 12.00% 26.70% 19.90% 26.70% 21.50%

-4.04% 50.00% 51.96% 35.32% 32.18% 29.01%

-5.16% -20.00% 18.06% 21.32% 19.13% 14.69%

10.77% -50.00% 22.95% 15.15% 20.44% 18.06%

-12.83% 83.33% 35.11% 8.42% -7.27% 10.39%

Net Fixed Assets Longterm Investments Investments in Other Companies Intangibles and Other Assets Total Non Current Assets

17.80% 6.20% 0.00% 16.50% 32.50%

24.98% -11.11% -100.00% 10.00% 23.27%

24.02% -62.50% #DIV/0! -4.55% 22.14%

23.69% 391.67% #DIV/0! 28.57% 27.19%

2.83% 53.39% 64.80% 44.44% 4.84%

1.05%

24.58%

20.38%

25.14%

6.01%

26.20% 33.50% 16.70% 12.80% 38.02%

53.66% 52.50% 25.00% 19.05% 44.83%

4.44% 40.98% 66.67% 18.40% 18.09%

17.63% 20.16% 20.00% 7.43% 16.47%

24.03% 14.52% 20.00% -18.18% 12.77%

Growth in Gross Profits Growth in Interest Expense Growth in Income Tax Expense Growth in Non Operating Expenses

Growth in Total Assets Accounts Payable Short Term Borrowings Short Term Portion of LT Debt Other Current Liabilities Total Current Liabilities

Longterm Debt / Borrowings Other Longterm Liabilities Total Non Current Liabilities

46.00% 11.30% 37.10%

50.86% 15.38% 38.12%

48.57% -6.53% 32.04%

38.46% 26.96% 36.02%

9.72% 11.80% 10.13%

Growth in Total Liabilities

31.05%

42.85%

22.08%

22.52%

11.87%

0.00% 2.60% 1.50% 38.00% -166.00% 2.01%

#DIV/0! -1.91% -2.25% 38.32% -56.36% 3.91%

#DIV/0! 3.19% 5.29% 39.57% -558.33% 1.37%

#DIV/0! 1.01% 9.05% 52.95% 290.36% 2.70%

#DIV/0! 1.44% 0.04% 32.92% 213.09% 1.97%

6.11%

14.43%

19.21%

27.00%

2.00%

Sustainable Growth Rate #1 Sustainable Growth Rate #2

13.05% 10.88%

20.53% 12.91%

21.36% 13.91%

23.47% 9.94%

18.06% 7.83%

Growth in Market Capitalization

14.50%

24.69%

29.39%

-6.32%

-14.00%

Preferred Equity Common Equity Additional Paid in Capital Retained Earnings Adj for Foreign Currency Transl Treasury Stock Growth in Total Equity (Net Worth)

Vertical Analysis for XYZ Ltd 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%.

Account Title

Annual Period 2004

expressed in percentages Annual Annual Annual Period Period Period 2005 2006 2007

Annual Period 2008

Cash and Cash Equivalents Short Term Marketable Securities Accounts Receivable Inventory Other Current Assets Current Assets

5.77% 0.06% 5.95% 5.86% 5.07% 22.72%

4.45% 0.07% 7.26% 6.37% 5.38% 23.53%

3.50% 0.05% 7.12% 6.42% 5.33% 22.41%

3.10% 0.02% 6.99% 5.90% 5.13% 21.14%

2.55% 0.03% 8.91% 6.04% 4.49% 22.02%

Net Fixed Assets Longterm Investments Investments in Other Companies Intangibles and Other Assets Non Current Assets

74.22% 2.10% 0.38% 0.58% 77.28%

74.46% 1.50% 0.00% 0.51% 76.47%

76.71% 0.47% 0.00% 0.41% 77.59%

75.83% 1.83% 0.78% 0.42% 78.86%

73.55% 2.65% 1.21% 0.57% 77.98%

100.00%

100.00%

100.00%

100.00%

100.00%

Accounts Payable Short Term Borrowings Short Term Portion of LT Debt Other Current Liabilities Total Current Liabilities

11.96% 7.00% 0.07% 6.12% 25.15%

14.75% 8.57% 0.07% 5.85% 29.24%

12.79% 10.03% 0.10% 5.76% 28.68%

12.03% 9.63% 0.09% 4.94% 26.69%

14.07% 10.41% 0.11% 3.81% 28.40%

Longterm Debt / Borrowings Other Longterm Liabilities Total NonCurrent Liabilities

6.77% 3.79% 10.56%

8.19% 3.51% 11.70%

10.11% 2.73% 12.84%

11.19% 2.77% 13.95%

11.58% 2.92% 14.50%

Total Liabilities

35.71%

40.94%

41.52%

40.65%

42.89%

Preferred Equity Common Equity Additional Paid in Capital Retained Earnings Adj for Foreign Currency Transl Treasury Stock Total Equity

0.00% 11.92% 29.24% 29.73% 1.60% -8.19% 64.29%

0.00% 9.39% 22.94% 33.01% 0.56% -6.84% 59.06%

0.00% 8.05% 20.06% 38.27% -2.14% -5.76% 58.48%

0.00% 6.49% 17.48% 46.77% -6.67% -4.72% 59.35%

0.00% 6.21% 16.50% 58.64% -19.71% -4.54% 57.11%

100.00%

100.00%

100.00%

100.00%

100.00%

Total Assets

Total Liabilities & Equity

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%

7.51%

6.64%

10.79%

9.65%

10.80%

Income Before Extra Ord Items

24.08%

27.91%

28.41%

30.76%

26.18%

Net Income

24.22%

29.71%

29.44%

31.98%

28.06%

Non Operating Expenses

Enter Your Forecast Periods =>

Annual Period 2001

Annual Period 2002

Annual Period 2003

Annual Period 2004

Annual Period 2005

Pro Forma Income Statement Gross Revenues Growth Assumptions

30,742 12.00%

34,431 12.00%

38,562 12.00%

43,190 12.00%

48,373 12.00%

Cost of Goods Sold Growth Assumptions

(12,024) 39.11%

(13,467) 39.11%

(15,083) 39.11%

(16,893) 39.11%

(18,920) 39.11%

Operating Expenses Growth Assumptions

(7,483) 24.34%

(8,381) 24.34%

(9,387) 24.34%

(10,513) 24.34%

(11,775) 24.34%

NonOperating Expenses

(3,200)

(3,200)

(3,600)

(3,600)

(4,000)

650

650

650

700

700

8,685

10,033

11,143

12,884

14,378

Sources of Operating Cash Flow: Net Income Depreciation and Amortization (Increase) Decrease Defer Taxes (Gain) Loss on Sale of Assets (Increase) Decrease Current Assets Increase (Decrease) Current Liab

8,685 470 0 15 (789) 1,073

10,033 490 0 9 (996) 1,291

11,143 500 0 2 (1,116) 1,446

12,884 520 0 3 (1,249) 1,620

14,378 550 0 6 (1,399) 1,814

Operating Cash Flow

9,453

10,827

11,975

13,777

15,348

Investment Sources of Cash Flow: Planned Sale of Assets Planned Sale of Investments Other Investment Sources to be used Total Investment Sources of Cash

100 2,200 0 2,300

60 2,100 0 2,160

20 1,900 0 1,920

25 1,800 0 1,825

35 1,700 0 1,735

Planned Investments: Capital Expenditures Acquisitions in Other Co's Purchases of Investments Total Investment Applications of Cash

(3,500) (500) (3,000) (7,000)

(3,000) (750) (3,500) (7,250)

(3,100) (1,200) (4,500) (8,800)

(2,700) (650) (6,000) (9,350)

(2,600) (350) (7,000) (9,950)

1,300 20

1,000 60

950 80

750 90

650 100

ExtraOrdinary Items Net Income

Pro Forma Cash Flow Statement

Cash Flow from Financing Activities: Proceeds from Loans & Debt Proceeds from Minority Interest

Enter Your Forecast Periods => Other Financing Activities Total Financing Sources of Cash

Annual Period 2001

Annual Period 2002

Annual Period 2003

Annual Period 2004

Annual Period 2005

0 1,320

0 1,060

0 1,030

0 840

0 750

(1,500) (2,500) (2,000) 0 (6,000)

(1,000) (3,000) (2,000) 0 (6,000)

(600) (4,000) (1,500) 0 (6,100)

(500) (5,500) (1,000) 0 (7,000)

(500) (7,000) (500) 0 (8,000)

73

797

25

92

(117)

870 943

943 1,740

1,740 1,765

1,765 1,857

1,857 1,740

Cash and Cash Equivalents Short Term Marketable Securities Accounts Receivable Inventory Other Current Assets Total Current Assets

943 0 3,074 2,459 1,998 8,475

1,740 0 3,443 2,754 2,238 10,175

1,765 0 3,856 3,085 2,507 11,213

1,857 0 4,319 3,455 2,807 12,438

1,740 0 4,837 3,870 3,144 13,591

Fixed Assets Accumulated Depreciation Net Fixed Assets Longterm Investments Investments in Other Companies Intangibles and Other Assets Total Non Current Assets

31,600 (3,480) 28,120 1,705 912 200 30,937

34,600 (3,970) 30,630 3,105 1,662 240 35,637

37,700 (4,470) 33,230 5,705 2,862 320 42,117

40,400 (4,990) 35,410 9,905 3,512 400 49,227

43,000 (5,540) 37,460 15,205 3,862 650 57,177

Total Assets

39,412

45,812

53,330

61,665

70,768

Accounts Payable Short Term Borrowings Short Term Portion of LT Debt Other Current Liabilities Total Current Liabilities

5,226 3,689 30 1,845 10,790

5,853 4,132 30 2,066 12,081

6,556 4,627 25 2,314 13,522

7,342 5,183 20 2,591 15,136

8,223 5,805 15 2,902 16,945

3,750 700 4,450

3,750 750 4,500

4,100 800 4,900

4,350 800 5,150

4,500 800 5,300

15,240

16,581

18,422

20,286

22,245

Cash Flow Applied for Financing: Payments on Loans & Debt Dividends Paid to Shareholders Purchase / Retire Stock Other Financing Activities Total Financing Applications of Cash Total Change to Cash Beginning Cash Balance Forecasted Ending Balance

Pro Forma Balance Sheet

Longterm Debt / Borrowings Other Longterm Liabilities Total Non Current Liabilities Total Liabilities

Enter Your Forecast Periods =>

Annual Period 2001

Annual Period 2002

Annual Period 2003

Annual Period 2004

Annual Period 2005

Preferred Equity Common Equity Additional Paid in Capital Retained Earnings Adj for Foreign Currency Transl Treasury Stock Total Equity

0 2,200 5,700 26,190 (5,000) (3,550) 25,540

0 2,200 5,700 33,222 (4,000) (5,550) 31,572

0 2,200 5,700 40,365 (2,500) (7,050) 38,715

0 2,200 5,700 47,748 (1,500) (8,050) 46,098

0 2,200 5,700 55,126 (500) (8,550) 53,976

External Financing Required (EFR)

(1,368)

(2,341)

(3,807)

(4,720)

(5,453)

Wksh2

Wksh3

Wksh4

Wksh5

Wksh6

Wksh7

Wksh8

Wksh9

Wksh10

Wksh11

active

Wksh13

Wksh14

Wksh15

Wksh16

ProForma Financials (Linear Trend Model) XYZ Ltd 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 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 the linear 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:

Total Revenues 30,000

Total Revenues

Home

25,000 20,000 15,000

Row 10

10,000 5,000 0 2002

2004 2006 Periods

2008

2010

The calculation of linear values is determined by defining the slope of the line and the y intercept: Order Total Rev Linear Formula for Linear Trendline: Year variable x actual y Value y 2004 1 12,076 12,683 y=(m*x)+b 2005 2 16,719 16,559 2006 3 21,196 20,435 m: slope of line 2007 4 24,737 24,311 x: independent variable 2008 5 27,448 28,188 b: y intercept 2001 6 32,064 2002 7 35,940 2003 8 39,816 2004 9 43,692 2005 10 47,569 The degree of linear fit with the actual data can be expressed as R Square

Annual Period

Annual Period

Annual Period

0.9888

Annual Period

2001

2002

2003

2004

Pro Forma Income Statement 12-1

Gross Revenues

1,054

4,930

8,807

12,683

12-2

Cost of Goods Sold

(1,687)

(2,927)

(4,167)

(5,407)

12-3

Operating Expenses

(206)

(1,149)

(2,092)

(3,035)

12-4

Operating Income

(839)

854

2,548

4,241

12-5

Non Operating Expenses

764

225

(314)

(853)

4-25

Extra Ordinary Items

271

271

271

271

Net Income

197

1,351

2,505

3,659

Sources of Operating Cash Flow: Net Income Depreciation and Amortization (Increase) Decrease Defer Taxes (Gain) Loss on Sale of Assets (Increase) Decrease Current Assets Increase (Decrease) Current Liab

197 470 0 15 7,226 (9,318)

1,351 490 0 9 (1,047) 1,357

2,505 500 0 2 (1,047) 1,357

3,659 520 0 3 (1,047) 1,357

Operating Cash Flow

(1,410)

2,160

3,317

4,492

Investment Sources of Cash Flow: Planned Sale of Assets Planned Sale of Investments Other Investment Sources to be used Total Investment Sources of Cash

100 2,200 0 2,300

60 2,100 0 2,160

20 1,900 0 1,920

25 1,800 0 1,825

Planned Investments: Capital Expenditures Acquisitions Purchases of Investments Total Investment Applications of Cash

(3,500) (500) (3,000) (7,000)

(3,000) (750) (3,500) (7,250)

(3,100) (1,200) (4,500) (8,800)

(2,700) (650) (6,000) (9,350)

1,300 20 0 1,320

1,000 60 0 1,060

950 80 0 1,030

750 90 0 840

Pro Forma Cash Flow Statement

Cash Flow from Financing Activities: Proceeds from Loans & Debt Proceeds from Minority Interest Other Financing Activities Total Financing Sources of Cash Cash Flow Applied for Financing:

Payments on Loans & Debt Dividends Paid to Shareholders Purchase / Retire Stock Other Financing Activities Total Financing Applications of Cash

(1,500) (2,500) (2,000) 0 (6,000)

(1,000) (3,000) (2,000) 0 (6,000)

(600) (4,000) (1,500) 0 (6,100)

(500) (5,500) (1,000) 0 (7,000)

(10,790)

(7,870)

(8,633)

(9,193)

870 (9,920)

(9,920) (17,790)

(17,790) (26,423)

(26,423) (35,616)

Cash and Cash Equivalents Short Term Marketable Securities Accounts Receivable Inventory Other Current Assets Total Current Assets

(9,920) 0 105 84 69 (9,662)

(17,790) 0 493 394 320 (16,582)

(26,423) 0 881 705 572 (24,265)

(35,616) 0 1,268 1,015 824 (32,509)

Fixed Assets Accumulated Depreciation Net Fixed Assets Longterm Investments Investments in Other Companies Intangibles and Other Assets Total Non Current Assets

31,600 (3,480) 28,120 1,705 912 200 30,937

34,600 (3,970) 30,630 3,105 1,662 240 35,637

37,700 (4,470) 33,230 5,705 2,862 320 42,117

40,400 (4,990) 35,410 9,905 3,512 400 49,227

Total Assets

21,275

19,055

17,852

16,718

179 127 30 63 399

838 592 30 296 1,756

1,497 1,057 25 528 3,107

2,156 1,522 20 761 4,459

Longterm Debt / Borrowings Other Longterm Liabilities Total Non Current Liabilities

3,750 700 4,450

3,750 750 4,500

4,100 800 4,900

4,350 800 5,150

Total Liabilities

4,849

6,256

8,007

9,609

0 2,200 5,700 17,702 (5,000) (3,550) 17,052

0 2,200 5,700 16,052 (4,000) (5,550) 14,402

0 2,200 5,700 14,557 (2,500) (7,050) 12,907

0 2,200 5,700 12,716 (1,500) (8,050) 11,066

Total Change to Cash Beginning Cash Balance Forecasted Ending Balance

Pro Forma Balance Sheet

Accounts Payable Short Term Borrowings Short Term Portion of LT Debt Other Current Liabilities Total Current Liabilities

Preferred Equity Common Equity Additional Paid in Capital Retained Earnings Adj for Foreign Currency Transl Treasury Stock Total Equity

External Financing Required (EFR)

(625)

(1,603)

(3,063)

(3,957)

of the actual data

d the y intercept: Slope m factor 3876.20

Annual Period

Intercept b factor 8806.60

2005

16,559 (6,647) (3,978) 5,934 (1,392) 271 4,813

4,813 550 0 6 (1,047) 1,357 5,679

35 1,700 0 1,735

(2,600) (350) (7,000) (9,950)

650 100 0 750

(500) (7,000) (500) 0 (8,000) (9,786) (35,616) (45,402)

(45,402) 0 1,656 1,325 1,076 (41,345) 43,000 (5,540) 37,460 15,205 3,862 650 57,177 15,832 2,815 1,987 15 994 5,811 4,500 800 5,300 11,111 0 2,200 5,700 10,529 (500) (8,550) 9,379

(4,658)

Wksh2

Wksh3

Wksh4

Wksh5

Wksh6

Wksh7

Wksh8

Wksh9

Wksh10

Wksh11

Wksh12

active

Wksh14

Wksh15

Wksh16

ProForma Financials (Exponential Smoothing / Weighted Moving Average) XYZ Ltd 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 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 Total Revenues - Historical 12,076 16,719 21,196 Total Revenues - Exponential 12,076 12,076 16,719 Total Revenues - Wt Moving Avg 11,105 15,230 18,890 Smoothing Factor must be between 0 and 1 Set Smoothing Factor Assign weights to appropriate periods

0 1.00 0.00%

1 Total weights should add u 1.50%

4.50%

Total Exponent Revenues Amounts 12,076 12,076 16,719 12,076 21,196 16,719 24,737 21,196 27,448 24,737 Mean Squared Error

Difference 0 4,643 4,477 3,541 2,711

Total Weighted Revenues Amounts 12,076 11,105 16,719 15,230 21,196 18,890 24,737 21,605 27,448 22,965 Mean Squared Error

Difference 971 1,489 2,306 3,132 4,483

Find the Optimal Smoothing Factor:

Find the Optimal Moving Weights:

Exponential Comparison 30,000 25,000

otal Revenues

Home

20,000 15,000 10,000

Total Revenues Historical Total Revenues Exponential Total

Exponential Comparison 30,000

Total Revenues

25,000

Total Revenues Historical Total Revenues Exponential Total Revenues - Wt Moving Avg

20,000 15,000 10,000 5,000 0 2001

2002

2003

2004

2005

Periods

Annual Period 2001

Annual Period 2002

Annual Period 2003

Pro Forma Income Statement Gross Revenues

26,070

26,413

26,316

Cost of Goods Sold

(10,197)

(10,207)

(9,860)

Operating Expenses

(6,346)

(6,226)

(6,229)

9,527

9,979

10,227

(3,200)

(3,200)

(3,600)

650

650

650

6,977

7,429

7,277

Sources of Operating Cash Flow: Net Income Depreciation and Amortization (Increase) Decrease Defer Taxes (Gain) Loss on Sale of Assets (Increase) Decrease Current Assets Increase (Decrease) Current Liab

6,977 470 0 15 472 (562)

7,429 490 0 9 (92) 120

7,277 500 0 2 26 (34)

Operating Cash Flow

7,372

7,956

7,771

Investment Sources of Cash Flow: Planned Sale of Assets Planned Sale of Investments

100 2,200

60 2,100

20 1,900

Operating Income NonOperating Expenses ExtraOrdinary Items Net Income

Pro Forma Cash Flow Statement

Other Investment Sources to be used Total Investment Sources of Cash

0 2,300

0 2,160

0 1,920

Planned Investments: Capital Expenditures Acquisitions Purchases of Investments Total Investment Applications of Cash

(3,500) (500) (2,000) (6,000)

(3,000) (750) (3,000) (6,750)

(3,100) (500) (3,000) (6,600)

Cash Flow from Financing Activities: Proceeds from Loans & Debt Proceeds from Minority Interest Other Financing Activities Total Financing Sources of Cash

1,300 20 0 1,320

2,000 60 0 2,060

3,000 80 0 3,080

Cash Flow Applied for Financing: Payments on Loans & Debt Dividends Paid to Shareholders Purchase / Retire Stock Other Financing Activities Total Financing Applications of Cash

(1,500) (2,500) 0 0 (4,000)

(1,800) (3,000) 0 0 (4,800)

(2,500) (4,000) 0 0 (6,500)

992

626

(329)

870 1,862

1,862 2,488

2,488 2,159

Cash and Cash Equivalents Short Term Marketable Securities Accounts Receivable Inventory Other Current Assets Total Current Assets

1,862 0 2,607 2,086 1,695 8,249

2,488 0 2,641 2,113 1,717 8,959

2,159 0 2,632 2,105 1,711 8,606

Fixed Assets Accumulated Depreciation Net Fixed Assets Longterm Investments Investments in Other Companies Intangibles and Other Assets Total Non Current Assets

31,600 (3,480) 28,120 705 912 75 29,812

34,600 (3,970) 30,630 1,605 1,662 100 33,997

37,700 (4,470) 33,230 2,705 2,162 150 38,247

Total Assets

38,061

42,956

46,853

4,432 3,128 30 1,564

4,490 3,170 30 1,585

4,474 3,158 25 1,579

Total Change to Cash Beginning Cash Balance Forecasted Ending Balance

Pro Forma Balance Sheet

Accounts Payable Short Term Borrowings Short Term Portion of LT Debt Other Current Liabilities

Total Current Liabilities

9,155

9,274

9,236

Longterm Debt / Borrowings Other Longterm Liabilities Total Non Current Liabilities

3,750 700 4,450

3,950 750 4,700

4,450 800 5,250

Total Liabilities

13,605

13,974

14,486

Preferred Equity Common Equity Additional Paid in Capital Retained Earnings Adj for Foreign Currency Transl Treasury Stock Total Equity

0 2,200 5,700 17,505 (5,000) (1,550) 18,855

0 2,200 5,700 14,505 (3,500) (1,550) 17,355

0 2,200 5,700 10,505 (1,000) (1,550) 15,855

5,602

11,627

16,513

External Financing Required (EFR)

ving Average)

1999 24,737 21,196 21,605

2000 27,448 24,737 22,965

Total weights should add up to =>

al enues torical al enues onential al

34.50%

Square 0 21,557,449 20,043,529 12,538,681 7,349,521 12,297,836

Square 942,841 2,217,121 5,317,636 9,809,424 20,100,607 7,677,526

59.50%

2001

26,070 100.00% 100.00%

2002

2003

26,413

26,316

2004

26,355

2005

26,340

al enues torical al enues onential al enues - Wt ving Avg

Annual Period 2004

Annual Period 2005

26,355

26,340

(9,694)

(9,740)

(6,464)

(6,859)

10,197

9,740

(3,600)

(4,000)

700

700

7,297

6,440

7,297 520 0 3 (11) 14

6,440 550 0 6 4 (5)

7,824

6,995

25 1,800

35 1,700

0 1,825

0 1,735

(3,900) 0 (1,000) (4,900)

(4,600) 0 (1,000) (5,600)

4,500 90 0 4,590

7,000 100 0 7,100

(4,000) (4,000) 0 0 (8,000)

(6,000) (3,000) 0 0 (9,000)

1,339

1,230

2,159 3,497

3,497 4,728

3,497 0 2,636 2,108 1,713 9,955

4,728 0 2,634 2,107 1,712 11,181

41,600 (4,990) 36,610 1,905 2,162 150 40,827

46,200 (5,540) 40,660 1,205 2,162 100 44,127

50,782

55,308

4,480 3,163 20 1,581

4,478 3,161 15 1,580

9,244

9,234

4,950 800 5,750

5,950 800 6,750

14,994

15,984

0 2,200 5,700 6,505 0 (1,550) 12,855

0 2,200 5,700 3,505 0 (1,550) 9,855

22,932

29,469

Home

Wksh2

Wksh3

Wksh4

Wksh5

Wksh6

Wksh7

Wksh8

Wksh9

Wksh10

Wksh11

Wksh12

Wksh13

active

Wksh15

Wksh16

Scenario Analysis for XYZ Ltd 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 formula in another cell. Annual Period 2001

Annual Period 2002

Annual Period 2003

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 Cost of Goods Sold Operating Expenses NonOperating Expenses ExtraOrdinary Items Net Income

30,742 (12,024) (7,483) (3,200) 650 8,685

34,431 (13,467) (8,381) (3,200) 650 10,033

38,562 (15,083) (9,387) (3,100) 650 11,643

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 Cost of Goods Sold Operating Expenses Non Operating Expenses Extra Ordinary Items Net Income (formula cells)

32,064 (11,607) (7,750) (3,549) 271 9,430

35,940 (12,847) (8,693) (4,088) 271 10,584

39,816 (14,087) (9,636) (4,627) 271 11,738

Do not change formula cells (goals) to when using Goal Seek (Tools > Goal S

This is the target cell that Goal Seek is to change per the value 13,500 in cell

ysis and goal-seek analysis. and quicker to simply do our a cell given a corresponding

Annual Period 2004

Annual Period 2005

ar 2003 and again in year 2005: 43,190 (16,893) (10,513) (3,100) 700 13,384

48,373 (18,920) (11,775) (3,000) 700 15,378

scenario analysis directly in

e $ 13.5 million in Year 2004? 44,301 (15,327) (10,579) (5,166) 271 13,500

47,569 (16,567) (11,522) (5,705) 271 14,046

Do not change formula cells (goals) to values when using Goal Seek (Tools > Goal Seek)

This is the target cell that Goal Seek is seeking o change per the value 13,500 in cell G34

Wksh2

Wksh3

Wksh4

Wksh5

Wksh6

Wksh7

Wksh8

Wksh9

Wksh10

Wksh11

Wksh12

Wksh13

Wksh14

active

Wksh16

Budget Analysis for XYZ Ltd 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:

Annual Period 2001 Summarize Revenue Forecast: Simple Projection Model Linear Trend Model Wt Moving Avg Model Declining Growth Model Historical Data

Annual Period 2002

Annual Period 2003

30,742 1,054 26,070 29,644 12,076

34,431 4,930 26,413 31,422 16,719

38,562 8,807 26,316 32,679 21,196

29,644

31,422

32,679

Projection using declining growth rates: Gross Revenues

Forecast Comparisons 60,000 Simple Projection Model Linear Trend Model Wt Moving Avg Model Declining Growth Model Historical Data

50,000

Total Revenues

Home

40,000 30,000 20,000 10,000 0 2001

2002

2003

2004

2005

Periods 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 Formula for Logarithmic Trendline

1 2 3 4 5 6 7 8 9 10

Actual Predicted Values Value y 12,076 (11,242) 16,719 (4,587) 21,196 (695) 24,737 2,067 27,448 4,210 5,960 7,440 8,722 9,853 10,865

1 2 3 4 5 6 7 8 9 10

Actual Predicted Values Value y 12,076 11,951 16,719 17,109 21,196 21,104 24,737 24,493 27,448 27,491 30,212 32,721 35,063 37,267 39,356

1 2 3 4 5 6 7 8 9 10

Actual Predicted Values Value y 12,076 13,061 16,719 16,007 21,196 19,618 24,737 24,043 27,448 29,465 36,111 44,256 54,238 66,472 81,464

x factor 1 2

Actual Predicted Values Value y 12,076 #VALUE! 16,719 #VALUE!

x factor

y = ( c * LN (x)) - b LN: Natural Logarithm

Power Trendline y = b * x^c

x factor

Exponential Trendline x factor y = b * EXP ( c * x )

Polynomial Trendline y = (c2 * x^2) + (c1 * x^1) + b

3 4 5 6 7 8 9 10

21,196 24,737 27,448

#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

1996 (11,242) 11,951 13,061 #VALUE! 12,076

1997 (4,587) 17,109 16,007 #VALUE! 16,719

1998 (695) 21,104 19,618 #VALUE! 21,196

Summarize Non Linear Curves on Graph: Logarithmic Trendline Power Trendline Exponential Trendline Polynomial Trendline Actual Revenues

Non Linear Trends 100,000

Total Revenues

80,000 60,000 40,000 20,000 0 (20,000)

1997 1999 2001 2003 2005 1996 1998 2000 2002 2004 Periods

Variance Analysis of Past Budgets:

Logari Trendl Power Trendl Expon Trendl Polyno Trendl Actual Reven

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 2004

2005

2006

Income Statement Items Total Revenues - Budgeted Total Revenues - Actual % difference from actual

10,500 12,076 -13%

14,500 16,719 -13%

22,500 21,196 6%

Cost of Goods Sold - Budgeted Cost of Goods Sold - Actual % difference from actual

(4,500) (4,950) -9%

(6,500) (7,050) -8%

(8,648) (8,233) 5%

Operating Income - Budgeted Operating Income - Actual % difference from actual

3,100 3,815 -19%

4,900 5,776 -15%

9,000 8,309 8%

Net Income - Budgeted Net Income - Actual % difference from actual

2,100 2,925 -28%

4,100 4,968 -17%

6,500 6,240 4%

Balance Sheet Items Current Assets - Budgeted Current Assets - Actual % difference from actual

3,450 3,895 -11%

4,400 5,025 -12%

6,250 5,763 8%

10,900 13,251 -18%

14,000 16,335 -14%

22,000 19,951 10%

Current Liabilities - Budgeted Current Liabilities - Actual % difference from actual

3,750 4,312 -13%

5,600 6,245 -10%

8,200 7,375 11%

NonCurrent Liabilities - Budgeted NonCurrent Liabilities - Actual % difference from actual

1,750 1,810 -3%

2,450 2,500 -2%

3,750 3,301 14%

Ratio Items Current Ratio - Budgeted Current Ratio - Actual % difference from actual

0.92 0.90 2%

0.79 0.80 -2%

0.76 0.78 -2%

Total Asset Turnover - Budgeted Total Asset Turnover - Actual % difference from actual

0.68 0.70 -3%

0.72 0.78 -8%

0.80 0.82 -3%

Gross Profit Margin - Budgeted Gross Profit Margin - Actual % difference from actual

60% 59% 2%

60% 58% 4%

60% 61% -2%

NonCurrent Assets - Budgeted NonCurrent Assets - Actual % difference from actual

Net Profit Margin - Budgeted Net Profit Margin - Actual % difference from actual

19% 24% -22%

25% 30% -16%

28% 29% -5%

Debt to Common Equity - Budgeted Debt to Common Equity - Actual % difference from actual

0.45 0.50 -11%

0.50 0.63 -20%

0.60 0.63 -4%

Return on Equity - Budgeted Return on Equity - Actual % difference from actual

20% 24% -16%

29% 33% -13%

32% 35% -9%

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. Actual Forecasted Example: Total Revenues Period Revenues Revenues n: total number of periods 1996 12,076 10,500 1997 16,719 14,500 1998 21,196 22,500 1999 24,737 28,500 2000 27,448 30,000 n => 5 Sum => Mean Absolute Error Mean Square Error Root Mean Sqr Error Establish Error Limits

before finalizing it ions that should wing the different

Annual Period 2004

Annual Period 2005

43,190 12,683 26,355 33,170 24,737

48,373 16,559 26,340 31,000 27,448

33,170

31,000

Simple Projection Model Linear Trend Model Wt Moving Avg Model Declining Growth Model Historical Data

veral non-linear (curve) models: ward or downward.

ng at ever higher rates.

Slope c factor 9600.92

Intercept b factor 11242.34

Slope c factor 0.52

Intercept b factor 9.39 11951.33

Slope c factor 0.2

Intercept b factor 9.27 10657.5

c2 #VALUE!

c1 #VALUE!

< - calculate using c and b factor - > Predicted ( c * x) EXP Value y 0.20339 1.23 13,061 0.40678 1.5 16,007 0.61017 1.84 19,618 0.81356 2.26 24,043 1.01695 2.76 29,465 1.22034 3.39 36,111 1.42373 4.15 44,256 1.62712 5.09 54,238 1.83051 6.24 66,472 2.03390 7.64 81,464

b #VALUE!

1999 2,067 24,493 24,043 #VALUE! 24,737

2000 4,210 27,491 29,465 #VALUE! 27,448

2001 5,960 30,212 36,111 #VALUE!

Trends

003 2005 2004

Logarithmic Trendline Power Trendline Exponential Trendline Polynomial Trendline Actual Revenues

2002 7,440 32,721 44,256 #VALUE!

2003 2004 2005 8,722 9,853 10,865 35,063 37,267 39,356 54,238 66,472 81,464 #VALUE! #VALUE! #VALUE!

the degree of error or es a need to improve

2007

2000

(Wksht 16) 2001

28,500 24,737 15%

30,000 27,448 9%

30,500 TBD #VALUE!

(9,650) (9,050) 7%

(11,000) (10,150) 8%

(11,929) TBD #VALUE!

11,000 9,995 10%

10,500 10,150 3%

11,146 TBD #VALUE!

9,000 7,911 14%

9,300 7,701 21%

7,986 TBD #VALUE!

7,500 6,804 10%

7,900 7,511 5%

8,253 TBD #VALUE!

27,500 25,375 8%

29,500 26,602 11%

31,555 TBD #VALUE!

9,050 8,590 5%

9,400 9,687 -3%

10,715 TBD #VALUE!

4,900 4,490 9%

5,100 4,945 3%

5,250 TBD #VALUE!

0.83 0.79 5%

0.80 0.78 3%

0.77 TBD #VALUE!

0.80 0.77 4%

0.79 0.80 -2%

0.77 TBD #VALUE!

60% 63% -5%

60% 63% -5%

0.61 TBD #VALUE!

30% 32% -6%

28% 28% 0%

26% TBD #VALUE!

0.65 0.57 13%

0.55 0.53 4%

0.48 TBD #VALUE!

35% 33% 5%

30% 26% 16%

24% TBD #VALUE!

Absolute 1,576 2,219 1,304 3,763 2,552 3,824

Error Squared 2,483,776 4,923,961 1,700,416 14,160,169 6,512,704 14,622,976

not place weight on the dicted values) / n.

ce more emphasis on alculated as the

and comparable to the Mean rror. We can then use this eptable errors. Error 1,576 2,219 (1,304) (3,763) (2,552) (3,824)

Mean Absolute Error Mean Square Error Root Mean Sqr Error Establish Error Limits

765 2,924,595 1,710 1,710

Is Error Outside Limit? No Yes No Yes Yes Yes

Home

Wksh2

Wksh3

Wksh4

Wksh5

Wksh6

Wksh7

Wksh8

Wksh9

Wksh10

Wksh11

Wksh12

Wksh13

Wksh14

Wksh15

active

Final Budgets for XYZ Ltd 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 assumptions so that we can produce a final finished budget for planning purposes. Budget Period 2001

Ref Operating Plan 16-1 16-2 16-3 16-4

Total Revenues Cost of Goods Sold Operating Expenses Operating Income

30,500 (11,929) (7,424) 11,146

16-5 16-6 16-7

Interest Expenses Income Taxes Other Non Operating Expenses

(310) (3,300) (200)

16-8 16-9

Earnings Before Extra Ord Items Extra Ordinary Items

7,336 650

16-10

Net Income

7,986

Financial Plan Budgeted Cash Flows 16-11 16-12 16-13 16-14 16-15 16-16

Net Income Depreciation and Amortization (Increase) Decrease Defer Taxes (Gain) Loss on Sale of Assets (Increase) Decrease Current Assets (Increase) Decrease Current Liab

7,986 470 0 15 (724) 988

16-17

Operating Cash Flow

8,735

16-18 16-19 16-20 16-21

Investment Sources of Cash: Planned Sale of Assets Planned Sale of Investments Other Investment Sources to be used Total Investment Sources of Cash

100 2,200 0 2,300

16-22

Planned Investments: Capital Expenditures

(4,500)

16-23 16-24 16-25

Acquisitions in Other Co's Purchases of Investments Total Investment Applications of Cash

(350) (2,500) (7,350)

16-26 16-27 16-28 16-29

Cash Flow from Financing Activities Proceeds from Loans & Debt Proceeds from Minority Interest Other Financing Activities Total Financing Sources of Cash

1,450 15 0 1,465

16-30 16-31 16-32 16-33 16-34

Cash Flow Applied for Financing: Payments on Loans and Debt Dividends Paid to Shareholders Purchase / Retire Stock Other Financing Activities Total Financing Applications of Cash

(1,250) (2,500) (1,500) 0 (5,250)

16-35

Total Change to Cash

16-36 16-37

Beginning Cash Balance Forecasted Ending Balance

(100) 870 770

Budgeted Balance Sheet 16-38 16-39 16-40 16-41 16-42 16-43

Cash and Cash Equivalents Short Term Marketable Securities Accounts Receivable Inventory Other Current Assets Total Current Assets

770 10 3,050 2,440 1,983 8,253

16-44 16-45 16-46 16-47 16-48 16-49 16-50

Fixed Assets Accumulated Depreciation Net Fixed Assets Longterm Investments Investments in Other Companies Intangibles and Other Assets Total Non Current Assets

32,600 (3,480) 29,120 1,205 1,000 230 31,555

16-51

Total Assets

39,808

16-52 16-53 16-54 16-55 16-56

Accounts Payable Short Term Borrowings Short Term Portion of LT Debt Other Current Liabilities Total Current Liabilities

5,185 3,660 40 1,830 10,715

16-57 16-58 16-59

Longterm Debt / Borrowings Other Longterm Liabilities Total Non Current Liabilities

4,150 1,100 5,250

16-60

Total Liabilities

15,965

16-61 16-62 16-63 16-64 16-65 16-66 16-67

Preferred Stock Common Equity Additional Paid in Capital Retained Earnings Adj for Foreign Currency Translation Treasury Stock Total Equity

0 2,200 5,700 25,491 (5,000) (3,050) 25,341

16-68

Total Liabilities and Equity

41,306

16-69

External Financing Required

(1,499)

a and placed it into a set of ssumptions for a final budget. ed in our forecast for improved finalize all assumptions et for planning purposes.

Assumptions & Comments

Based on review of Pro Forma Financials, Marketing, etc. Volume projections, production budgets, and vertical analysis Average % of Sales per Vertical Analysis

Based on anticipated levels of debt and past history Based on anticipated taxable income and effective rate Provision for contingency was added on this line item

Per our Simple Model Forecast

Review of Simple Model Forecast and Capital Expenditure Budget Per Simple Forecast Model Same formula as used in forecast models Same formula as used in forecast models

Per Simple Model Forecast Per Simple Model Forecast

Budgeted $ 4.5 million in Capital Expenditure Budget

Per forecast, strategic plan, and other budgets Per forecast, strategic plan, and other budgets

Per Financing Requirements and other budgets Per historical financials and investment budget

Per forecast and other budgets Per Simple Model Forecast Per strategic plan and other budgets

Per above Per historical financials Same formula as used in forecast models Same formula as used in forecast models Same formula as used in forecast models

Same formula as used in forecast models Same formula as used in forecast models Same formula as used in forecast models Per review of forecast and strategic plans Per review of forecast and historical balances

Same formula as used in forecast models Same formula as used in forecast models Per review of forecast and historical information Same formula as used in forecast models

Same formula as used in forecast models Per review of historical information and expected growth rates.

Per Simple Model Forecast Per Simple Model Forecast Same formula as used in forecast models Per Simple Model Forecast Same formula as used in forecast models

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 $G$60 Mean Squared Error Square

Original Value 20,525,549

Final Value 12,297,836

Adjustable Cells Cell Name $D$24 Set Smoothing Factor

Original Value 0.70

Final Value 1.00

Constraints Cell Name $D$23 Smoothing Factor must be between 0 and 1 $D$24 Set Smoothing Factor

Cell Value

Formula Status Slack 0 $D$23>=$D$23 Binding 0 1.00 $D$24<=$E$23 Binding 0

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) Cell $G$46 Mean Squared Error Square

Name

Original Value 65,536,151

Adjustable Cells Cell Name $D$25 Assign weights to appropriate periods $E$25 Assign weights to appropriate periods $F$25 Assign weights to appropriate periods Total weights should add up to => $G$25 Assign weights to appropriate periods $H$25 Assign weights to appropriate periods

Original Value 0.00% 2.00% 5.00% 35.00% 60.00%

Constraints Cell Name $I$25 Assign weights to appropriate periods $D$25 Assign weights to appropriate periods $E$25 Assign weights to appropriate periods $F$25 Assign weights to appropriate periods Total weights should add up to => $G$25 Assign weights to appropriate periods $H$25 Assign weights to appropriate periods $D$25 Assign weights to appropriate periods $E$25 Assign weights to appropriate periods $F$25 Assign weights to appropriate periods Total weights should add up to => $G$25 Assign weights to appropriate periods $H$25 Assign weights to appropriate periods

Cell Value 100.00% 0.00% 1.50% 4.50% 34.50% 59.50% 0.00% 1.50% 4.50% 34.50% 59.50%

Final Value 59,853,342

Final Value 0.00% 1.50% 4.50% 34.50% 59.50%

Formula $I$25=$E$23 $D$25<=$E$23 $E$25<=$E$23 $F$25<=$E$23 $G$25<=$E$23 $H$25<=$E$23 $D$25>=$D$23 $E$25>=$D$23 $F$25>=$D$23 $G$25>=$D$23 $H$25>=$D$23

Status Binding Not Binding Not Binding Not Binding Not Binding Not Binding Binding Not Binding Not Binding Not Binding Not Binding

Slack 0 1 0.99 0.96 0.66 0.41 0.00% 1.50% 4.50% 34.50% 59.50%