Docstoc

Stock Analysis Template

Document Sample
Stock Analysis Template Powered By Docstoc
					What-If Model – Users Guide

The What-If Model will allow a team the ability to model the potential financial impact of a variety of initiatives upo
financial statements. This scenario, or “what-if”, modeling will incorporate the company’s historical financial statem
projections in conjunction with inputs provided by the Quantitative Model. The historical and projected financial sta
by the client will serve as the models “base” or “null” state. The inputs provided by the Quantitative Model provide
modeled with the client projections. The What-If Model will then provide expected changes in financial statements
statement metrics from the base assumptions. These expected changes allow the team to target and prioritize the sce
greatest expected benefits. This prioritization will aid further discussions with management when determining furth
performed.

The model is segregated into four sections: inputs, calculations, statements, and analysis. Only the input section is e
sections are protected and read only. The model’s format does have the capacity to be modified quickly. For examp
business’s financial statements cannot be fit into the current format, the current format can be appended or changed
different format. Additionally, if the operating assumptions are inaccurate, they can also be altered to accommodate
assumptions. Outlined below is a description of Section One, and how particular areas operate within the section. S
are protected and therefore have no operational details.

Input Template:
Project Costs
Segregate Capitalized and Expensed Project Costs based on the expenditure characteristics.
Include project fees
Historical Financial Information
Income Statement - Based on audited financial report data, categorize revenues and expenses into the broad categori
Balance Sheet - Further, categorize assets, liabilities and equity into the appropriate balance sheet line items provide
Operating Assumptions
Pre-Implementation and Base Year: these are darkened to illustrate that they are not inputs, but calculations for infor
Income Statement Assumptions are percentage changes in the current year from the prior year.
Balance Sheet Assumptions are the assumption’s percentage of total assets.
Post-Implementation:
Sales revenue growth percentages are used for all projected years. The financial statements use these percentages by
prior year sales dollars by one plus the growth rate for each year (compounding).
The following items’ projected years’ costs are constant (i.e. equal to the base year) and are direct inputs from histor
controllable expenses, other accrued expenses, taxes payable, other current liabilities, long-term notes payable, defer
The following items use growth percentage changes for all projected years: R&D expenses, G&A expenses, interest
payable, notes payable, and distributions payable. The financial statements use these percentages by multiplying the
by one plus the growth rate input for each year. Growth is not proportionate to sales.
The following items use growth percentage changes for all projected years: selling expenses, accounts receivable, in
expenses, other current assets, other noncurrent assets, accounts payable, unearned revenue, and current portion of lo
financial statements multiply the prior year dollars by one plus the growth rate input. Further, this product is multipl
sales growth input for each year to vary these items proportionally with sales.
Machinery & Equipment and Building and Plant, for all projected years, are estimated capital expenditures in whole
Intangible Asset Amortization
Additional Assumptions
Client should provide their applicable tax rates, weighted average cost of capital, and interest rate on excess cash.
Further, Asset lives and additional asset information should be captured.

Section Two – Calculations
Protected Sheet, Read-only Access
Section Three – Financial Statements
Protected Sheet, Read-only Access
Section Four - Analysis
Protected Sheet, Read-only Access
mpact of a variety of initiatives upon a company’s
 mpany’s historical financial statements and five-year
 storical and projected financial statements provided
 y the Quantitative Model provide the variables to be
 d changes in financial statements and financial
eam to target and prioritize the scenarios with the
nagement when determining further work to be


 alysis. Only the input section is editable. All other
o be modified quickly. For example, if a subject
 mat can be appended or changed to accommodate the
 n also be altered to accommodate company-specific
 reas operate within the section. Sections Two – Four




d expenses into the broad categories provided.
e balance sheet line items provided.

  t inputs, but calculations for information purposes.
 e prior year.


 atements use these percentages by multiplying the

 ) and are direct inputs from historical data: other
 es, long-term notes payable, deferred taxes payable,
 xpenses, G&A expenses, interest expense, interest
 se percentages by multiplying the prior year dollars
es.
 expenses, accounts receivable, inventory, prepaid
 revenue, and current portion of long-term debt. The
ut. Further, this product is multiplied by one plus the

 ted capital expenditures in whole dollars.
nd interest rate on excess cash.
Maximize ERP Project                                          Financial Model                                          Input - 113868e1-d799-4633-aa57-a29871ae1484.xls



       MODEL INPUT
        I
             Project                                                    2000        2001       2002         2003       2004
                Project Costs-Capitalized                                       5          0          0            0          0

                Project Costs-Expensed                                          1          1          1            1          1

                Project Fees                                                 0.5       0.5        0.5           0.5       0.5

                Date (MM/DD/YYYY)                                       7/1/1999


        II
             Historical Financial Information           Pre-Implementation                                Base Year*
                Income Statement                 1996          1997                 1998                    1999

                   Sales Revenue                                                                            111,211
                   Net Sales Revenue                                                                        111,211
                   Cost of Sales                                                                             60,001
                   Selling Expenses                                                                               0
                   R&D Expenses                                                                                   0
                   General and Administrative                                                                 7,518
                   Total Non-Sales Expenses                                                                   7,518
                   Depreciation                                                                               4,884
                   Amortization                                                                                   0
                   Other Controllable Expenses                                                                  250
                   Interest Expense                                                                             647
                   Interest Income                                                                                0
                   Miscellaneous Income                                                                           0
                   Income Tax                                                                                32,631

                   Preferred Dividends                                                                             0

       * Current or Post-Implementation Year




STRICTLY CONFIDENTIAL --
Unaudited Preliminary Draft
For discussion and review purposes only
                                                                  5 of 18                                                                            8/18/2011, 4:23 PM
Maximize ERP Project                                                Financial Model                           Input - 113868e1-d799-4633-aa57-a29871ae1484.xls



       MODEL INPUT
          Historical Financial Information                    Pre-Implementation                 Base Year*
             Balance Sheet                             1996          1997             1998         1999
                Current Assets
                    Cash                                                                             1,652
                    Accounts Receivable                                                              6,860
                    Inventory                                                                        5,472
                    Prepaid Expenses                                                                   875
                    Other Current Assets                                                             4,790

                 Noncurrent Assets
                   Machinery and Equipment                                                               0
                   Building and Plant                                                               61,962
                   Accumulated Depreciation                                                              0
                   Deferred Income Tax Asset                                                             0
                   Other Noncurrent Assets                                                           2,534

                 Intangible Assets
                    Deferred Fin./ License/Startup                                                       0
                    Goodwill                                                                             0
                    Other Intangibles                                                                    0
                    Accumulated Amortization                                                             0

                 Current Liabilities
                   Interest Payable                                                                      0
                   Accounts Payable                                                          0       7,677
                   Other Accrued Expenses                                                            4,445
                   Unearned Revenue                                                                      0
                   Notes Payable                                                                     4,109
                   Current Portion of Long Term Debt                                                     0
                   Taxes Payable                                                                     2,359
                   Distributions Payable                                                                 0
                   Other Current Liabilities                                                         8,153
                 Noncurrent Liabilities
                   Long-Term Notes                                                                  10,901
                   Deferred Income Tax Liability                                                    10,939
                   Deferred Taxes Payable                                                              770

                 Shareholder Equity
                     Retained Earnings                                                              49,365
                     Common Equity                                                                   2,822
                     Additional Paid-in Capital                                                          0
                     Preferred Stock                                                                   668
                     Treasury Stock                                                                (16,977)
                     Other Equity                                                                   (1,086)
                     Number of Shares - Common Stock                                                 2,428
       * Current or Post-Implementation Year




STRICTLY CONFIDENTIAL --
Unaudited Preliminary Draft
For discussion and review purposes only
                                                                        6 of 18                                                             8/18/2011, 4:23 PM
Maximize ERP Project                                       Financial Model                                        Input - 113868e1-d799-4633-aa57-a29871ae1484.xls



       MODEL INPUT
       III                                          Pre-Implementation           Base Year*                      Post-Implementation Growth
             Operating Assumptions                1996     1997      1998           1999    Benchmark    2000        2001     2002      2003         2004
                Income Statement Assumptions

                   Sales Revenue                          #DIV/0!   #DIV/0!        #DIV/0!              5.00%       5.00%     5.00%     5.00%       5.00%
                   Other Controllable Expenses                        0.00%           250                 250         250       250       250         250
                   R&D Expenses                           #DIV/0!   #DIV/0!        #DIV/0!              0.00%       0.00%     0.00%     0.00%       0.00%
                   Selling Expenses                       #DIV/0!   #DIV/0!        #DIV/0!              0.00%       0.00%     0.00%     0.00%       0.00%
                   General and Administrative             #DIV/0!   #DIV/0!        #DIV/0!              0.00%       0.00%     0.00%     0.00%       0.00%

             Projected Expenses
                    Interest Expense                  0        0             0        647               0.0%         0.0%      0.0%      0.0%        0.0%

                                                    Pre-Implementation                                          Post-Implementation Percentages
                   Gross Margin Percentage          N/A     N/A        N/A           46.0%              46.0%        46.0%    46.0%      46.0%      46.0%
                   Cost of Sales (% Rev)         #DIV/0! #DIV/0!    #DIV/0!            54%              54.0%        54.0%    54.0%      54.0%      54.0%

       * Current or Post-Implementation Year




STRICTLY CONFIDENTIAL --
Unaudited Preliminary Draft
For discussion and review purposes only
                                                               7 of 18                                                                            8/18/2011, 4:23 PM
Maximize ERP Project                                                        Financial Model                                         Input - 113868e1-d799-4633-aa57-a29871ae1484.xls



       MODEL INPUT
                                                                    Pre-Implementation            Base Year*                        Post-Implementation Growth
          Operating Assumptions                                   1996     1997      1998            1999    Benchmark      2000        2001     2002      2003         2004
              Balance Sheet Items

              Current Assets
                 Accounts Receivable                             #DIV/0!   #DIV/0!   #DIV/0!            8%                   0%          0%        0%         0%         0%
                 Inventory                                       #DIV/0!   #DIV/0!   #DIV/0!            7%                   0%          0%        0%         0%         0%
                 Prepaid Expenses                                #DIV/0!   #DIV/0!   #DIV/0!            1%                   0%          0%        0%         0%         0%
                 Other Current Assets                            #DIV/0!   #DIV/0!   #DIV/0!            6%                   0%          0%        0%         0%         0%
              Noncurrent Assets
                 Capital Expenditures:
                     Machinery and Equipment                          0       0      #DIV/0!            0%                $0.00       $0.00     $0.00      $0.00       $0.00
                     Building and Plant                               0       0           0            74%                $0.00       $0.00     $0.00      $0.00       $0.00
                 Other Noncurrent Assets                         #DIV/0! #DIV/0!     #DIV/0!            3%                   0%          0%        0%         0%          0%
              Current Liabilities
                 Interest Payable                                     0       0           0               0                  0%          0%        0%         0%         0%
                 Accounts Payable                                #DIV/0! #DIV/0!     #DIV/0!             7%                  0%          0%        0%         0%         0%
                 Other Accrued Expenses                               0       0           0           4,445                  0%          0%        0%         0%         0%
                 Unearned Revenue                                #DIV/0! #DIV/0!     #DIV/0!             0%                  0%          0%        0%         0%         0%
                 Notes Payable                                        0       0           0           4,109                  0%          0%        0%         0%         0%
                 Current Portion of Long Term Debt               #DIV/0! #DIV/0!     #DIV/0!             0%                  0%          0%        0%         0%         0%
                 Taxes Payable                                        0       0           0           2,359                  0%          0%        0%         0%         0%
                 Distributions Payable                                0       0           0               0                  0%          0%        0%         0%         0%
                 Other Current Liabilities                            0       0           0           8,153                  0%          0%        0%         0%         0%
              Noncurrent Liabilities
                 Long-Term Notes Payable                              0         0             0      10,901                  0%          0%        0%         0%         0%
                 Deferred Taxes Payable                               0         0             0         770                  0%          0%        0%         0%         0%
              Equity Assumptions
                 Preferred Stock                                      0         0             0         668                  668        668        668        668        668
                 Treasury Stock                                       0         0             0     (16,977)             (16,977)   (16,977)   (16,977)   (16,977)   (16,977)
              Cash Flow Assumptions
                 Preferred Stock Dividends                                                              6%
                 Dividend Distributions to Common Shareholders

                 Days in Year                                                                          365

       * Current or Post-Implementation Year




STRICTLY CONFIDENTIAL --
Unaudited Preliminary Draft
For discussion and review purposes only
                                                                                8 of 18                                                                              8/18/2011, 4:23 PM
Maximize ERP Project                                                                   Financial Model        Input - 113868e1-d799-4633-aa57-a29871ae1484.xls



       MODEL INPUT
       IV
            Intangible Assets                             Amortization    Percent
                                                            Period       Amortizable

               Deferred Fin./ License/Startup                     10

               Goodwill                                           40

               Other Intangibles                                   5

               Project Costs-Capitalized                          10

               Project Fees                                       10       100.00%

        V
            Additional Assumptions

               Effective Federal Income Tax Rate                                                     86.07%

               Weighted Average Cost of Capital                                                      10.00%

               Effective State Income Tax Rate                                                       0.00%

               Interest Income Rate on Excess Cash Balances                                          0.00%

               Asset Lives
                  Machinery and Equipment                                                                7

                  Building and Plant                                                                    15

               Fair Market Value of Assets in Excess of Book:
                  Inventory                                                                              0
                  Fixed Assets                                                                           0

               Current Liabilities Assumed                                                               0




STRICTLY CONFIDENTIAL --
Unaudited Preliminary Draft
For discussion and review purposes only
                                                                                           9 of 18                                          8/18/2011, 4:23 PM
Maximize ERP Project                                                         Financial Model   Calculations - 113868e1-d799-4633-aa57-a29871ae1484.xls




    CALCULATIONS
       Book Depreciation                                        Existing                        2000       2001       2002      2003        2004
          Existing Basis                                 Life        Basis
             Machinery and Equipment                       7            0                          0         0          0          0          0
             Building and Plant                        12.70       61,962                      4,879     4,879      4,879      4,879      4,879
                 Total                                                                         4,879     4,879      4,879      4,879      4,879

          Incremental Capital Expenditures
                                2000 Machinery and Equipment                                       0         0          0          0          0
                                2000 Building and Plant                                            0         0          0          0          0
                                                                                                   0         0          0          0          0
                              2001 Machinery and Equipment                                                   0          0          0          0
                              2001 Building and Plant                                                        0          0          0          0
                                                                                                             0          0          0          0
                              2002 Machinery and Equipment                                                              0          0          0
                              2002 Building and Plant                                                                   0          0          0
                                                                                                                        0          0          0
                              2003 Machinery and Equipment                                                                         0          0
                              2003 Building and Plant                                                                              0          0
                                                                                                                                   0          0
                              2004 Machinery and Equipment                                                                                    0
                              2004 Building and Plant                                                                                         0
                                                                                                                                              0
          Total Book Depreciation                                                              4,879     4,879      4,879      4,879      4,879




STRICTLY CONFIDENTIAL --
Unaudited Preliminary Draft
For discussion and review purposes only
                                                                                10 of 18                                            8/18/2011, 4:23 PM
Maximize ERP Project                                                                   Financial Model          Calculations - 113868e1-d799-4633-aa57-a29871ae1484.xls



    CALCULATIONS
       Income Taxes                                                                                               2000      2001       2002      2003        2004

          Income from Continuing Operations                                                                     40,475   43,164     45,986     48,951     52,063
             Taxable Income Before NOL                                                                          40,475   43,164     45,986     48,951     52,063

             Less: NOL Utilized                                                                                     0         0          0          0          0

                 Taxable Income                                                                                 40,475   43,164     45,986     48,951     52,063
                                                                                                      Blended
          Cash Income Taxes                             State @ 0.00%        Federal @ 86.07%          86.07%   34,837   37,151     39,581     42,132     44,810
          Income Taxes, per Books                                                                               34,837   37,151     39,581     42,132     44,810
          Current Year Deferred Income Tax Benefit (Expense) - Temporary Difference                                  0        0          0          0          0

          Cumulative Deferred Income Tax Asset (Liability)                                                          0         0          0          0          0

          Net Operating Loss (“NOL”), Beginning Balance                                                             0         0          0          0          0
          Additional NOL Created                                                                                    0         0          0          0          0
          NOL Utilized                                                                                              0         0          0          0          0
          NOL, Ending Balance                                                                                       0         0          0          0          0




STRICTLY CONFIDENTIAL --
Unaudited Preliminary Draft
For discussion and review purposes only
                                                                                           11 of 18                                                  8/18/2011, 4:23 PM
Maximize ERP Project                                                                Financial Model   Calculations - 113868e1-d799-4633-aa57-a29871ae1484.xls



    CALCULATIONS
       Intangible Asset Amortization                                  Existing                         2000       2001       2002      2003        2004
                                                             Life           Basis
          Lic. Agr. / Def. Fin.                               10
              Beginning Balance                                                                           0         0          0          0          0
              Amortization                                                                                0         0          0          0          0
                 Ending Balance                                                                           0         0          0          0          0

          Goodwill                                               40
             Beginning Balance                                                                            0         0          0          0          0
             Amortization                                                                                 0         0          0          0          0
                Ending Balance                                                                            0         0          0          0          0

          Incremental Project Expenditures
          Project Costs-Capitalized                              10
          Project Fees                                           10


                                2000 Project Costs-Capitalized                                            0         1          1          1          1
                                2000 Project Fees                                                         0         0          0          0          0
                                                                                                          0         1          1          1          1
                                2001 Project Costs-Capitalized                                                      0          0          0          0
                                2001 Project Fees                                                                   0          0          0          0
                                                                                                                    0          0          0          0
                                2002 Project Costs-Capitalized                                                                 0          0          0
                                2002 Project Fees                                                                              0          0          0
                                                                                                                               0          0          0
                                2003 Project Costs-Capitalized                                                                            0          0
                                2003 Project Fees                                                                                         0          0
                                                                                                                                          0          0
                                2004 Project Costs-Capitalized                                                                                       0
                                2004 Project Fees                                                                                                    0
                                                                                                                                                     0

          Other Intangibles                                       5
             Beginning Balance                                                                            0         0          0          0          0
             Amortization                                                                                 0         0          0          0          0
                 Ending Balance                                                                           0         0          0          0          0

          Total Amortization                                                                              0         1          1          1          1




STRICTLY CONFIDENTIAL --
Unaudited Preliminary Draft
For discussion and review purposes only
                                                                                       12 of 18                                            8/18/2011, 4:23 PM
Maximize ERP Project                                                    Financial Model                             Calculations - 113868e1-d799-4633-aa57-a29871ae1484.xls



    CALCULATIONS
                                                                                    Pre-Implementation                                 Post-Implementation
                                                                             1996         1997      1998     1999     2000      2001         2002      2003      2004
       SVA
         Income From Continuing Operations                                      0          0          0    37,911   40,475   43,164       45,986    48,951     52,063
         Less: W.A.C.C. x (Total Noncurrent Assets + Working Capital)           0          0          0     5,740    6,300    6,897        7,534     8,212      8,933
            SVA                                                                 0          0          0    32,171   34,175   36,266       38,453    40,739     43,130

       NPV & IRR
         Net Cash Flow                                                                                               9,829   10,177       10,536    10,914     11,310
         Less: Total Project Costs                                                                                       7        2            2         2          2
            Cash Outflow/Inflow                                                                                      9,823   10,175       10,535    10,912     11,309




STRICTLY CONFIDENTIAL --
Unaudited Preliminary Draft
For discussion and review purposes only
                                                                           13 of 18                                                                       8/18/2011, 4:23 PM
Maximize ERP Project                                          Financial Model                                    Statements - 113868e1-d799-4633-aa57-a29871ae1484.xls



       BALANCE SHEET (Million of Dollars)

                                                    Pre-Implementation                                            Post-Implementation
       ASSETS                                     1996     1997       1998        1999       2000         2001           2002         2003          2004

           Current Assets
             Cash                                     0        0              0    1,652      11,481       21,658         32,194        43,108        54,418
             Accounts Receivable                      0        0              0    6,860       7,203        7,563          7,941         8,338         8,755
             Inventory                                0        0              0    5,472       5,746        6,033          6,335         6,651         6,984
             Prepaid Expenses                         0        0              0      875         919          965          1,013         1,064         1,117
             Other Current Assets                     0        0              0    4,790       5,030        5,281          5,545         5,822         6,113
                 Total Current Assets                 0        0              0   19,649      30,378       41,500         53,028        64,983        77,387

           Noncurrent Assets
             Machinery and Equipment                  0        0              0        0           0            0               0            0             0
             Building and Plant                       0        0              0   61,962      61,962       61,962          61,962       61,962        61,962
             Less: Accumulated Depreciation           0        0              0        0      (4,879)      (9,758)        (14,637)     (19,516)      (24,394)
                Net Fixed Assets                      0        0              0   61,962      57,083       52,204          47,325       42,446        37,568

              Deferred Income Tax Asset               0        0              0        0           0            0              0             0             0
              Other Noncurrent Assets                 0        0              0    2,534       2,661        2,794          2,933         3,080         3,234

              Intangible Assets
                 Deferred Fin./ License/Startup       0        0              0          0           0            0             0             0             0
                 Goodwill                             0        0              0          0           0            0             0             0             0
                 Project Costs-Capitalized                                                           5            5             5             5             5
                 Project Fees                                                                        1            1             2             2             3
                 Other Intangibles                    0        0              0          0           0            0             0             0             0
                 Less: Accumulated Amortization                                                     (0)          (1)           (1)           (2)           (3)
                    Total Intangible Assets           0        0              0          0           5            5             5             5             5

                 Total Noncurrent Assets              0        0              0   64,496      59,749       55,003         50,264        45,531        40,806

           Total Assets                               0        0              0   84,145      90,127       96,503        103,292       110,515       118,194




STRICTLY CONFIDENTIAL --
Unaudited Preliminary Draft
For discussion and review purposes only
                                                                   14 of 18                                                                         8/18/2011, 4:23 PM
Maximize ERP Project                                                Financial Model                                  Statements - 113868e1-d799-4633-aa57-a29871ae1484.xls



       BALANCE SHEET (Million of Dollars)

                                                          Pre-Implementation                                          Post-Implementation
       LIABILITIES                                      1996     1997       1998        1999       2000       2001           2002           2003        2004
          Current Liabilities
             Interest Payable                               0        0              0        0           0          0               0             0            0
             Accounts Payable                               0        0              0    7,677       8,061      8,464           8,887         9,331        9,798
             Other Accrued Expenses                         0        0              0    4,445       4,445      4,445           4,445         4,445        4,445
             Unearned Revenue                               0        0              0        0           0          0               0             0            0
             Notes Payable                                  0        0              0    4,109       4,109      4,109           4,109         4,109        4,109
             Current Portion of Long Term Debt              0        0              0        0           0          0               0             0            0
             Taxes Payable                                  0        0              0    2,359       2,359      2,359           2,359         2,359        2,359
             Distributions Payable                          0        0              0        0           0          0               0             0            0
             Other Current Liabilities                      0        0              0    8,153       8,153      8,153           8,153         8,153        8,153
                 Total Current Liabilities                  0        0              0   26,743      27,127     27,530          27,953        28,397       28,864

           Noncurrent Liabilities
             Long-Term Notes                                0        0              0   10,901      10,901     10,901          10,901        10,901       10,901
             Deferred Income Tax Liability                  0        0              0   10,939      10,939     10,939          10,939        10,939       10,939
             Deferred Taxes Payable                         0        0              0      770         770        770             770           770          770
                Total Noncurrent Liabilities                0        0              0   22,610      22,610     22,610          22,610        22,610       22,610

           Total Liabilities                                0        0              0   49,353      49,737     50,140          50,563        51,007       51,474

       SHAREHOLDER’S EQUITY

           Retained Earnings                                0        0              0    49,365     54,963      60,936         67,302         74,080      81,293
           Common Equity                                    0        0              0     1,736      1,736       1,736          1,736          1,736       1,736
           Additional Paid in Capital                       0        0              0         0          0           0              0              0           0
           Preferred Stock                                  0        0              0       668        668         668            668            668         668
           Treasury Stock                                   0        0              0   (16,977)   (16,977)    (16,977)       (16,977)       (16,977)    (16,977)
           Total Shareholder’s Equity                       0        0              0    34,792     40,390      46,363         52,729         59,507      66,720

           Total Liabilities and Shareholder’s Equity       0        0              0   84,145      90,127     96,503        103,292        110,515      118,194




STRICTLY CONFIDENTIAL --
Unaudited Preliminary Draft
For discussion and review purposes only
                                                                         15 of 18                                                                       8/18/2011, 4:23 PM
Maximize ERP Project                                                     Financial Model                                   Statements - 113868e1-d799-4633-aa57-a29871ae1484.xls




       STATEMENT OF INCOME AND RETAINED EARNINGS (Million of Dollars)

                                                               Pre-Implementation                                           Post-Implementation
                                                             1996     1997       1998        1999       2000       2001            2002           2003         2004

           Sales Revenue                                         0        0              0   111,211    116,772    122,610         128,741        135,178      141,937
           Net Sales Revenue                                     0        0              0   111,211    116,772    122,610         128,741        135,178      141,937
           Cost of Sales                                         0        0              0    60,001     63,001     66,151          69,459         72,932       76,578
           Gross Margin on Sales                                 0        0              0    51,210     53,771     56,459          59,282         62,246       65,358

           Gross Margin Percentage                                                 N/A        46.05%     46.05%     46.05%           46.05%        46.05%        46.05%

           Expense Projections '99 Forward
              Project Costs-Expensed                                                                          1          1                1             1             1
              R&D Expenses                                       0        0              0         0          0          0                0             0             0
              Other Controllable Expenses                                                        250        250        250              250           250           250
              Selling Expenses                                   0        0              0         0          0          0                0             0             0
              General and Admin Expenses                         0        0              0     7,518      7,518      7,518            7,518         7,518         7,518
                  Total Non-Sales Expenses                       0        0              0     7,768      7,769      7,769            7,769         7,769         7,769
           Earnings Before Interest, Tax, Depr. and Amort.       0        0              0    43,442     46,002     48,690           51,513        54,477        57,589

              Depreciation                                       0        0              0     4,884      4,879      4,879            4,879         4,879         4,879
              Amortization                                       0        0              0         0          0          1                1             1             1
                Total Operating Expenses                         0        0              0    12,652     12,648     12,648           12,649        12,649        12,649

           Earnings Before Interest and Tax (“EBIT”)             0        0              0    38,558     41,122     43,811           46,633        49,598        52,710

           Total Interest Expense                                0        0              0      647        647            647          647               647          647

           Interest Income                                                                         0          0          0                0             0             0
           Miscellaneous Income                                  0        0              0         0          0          0                0             0             0
           Income from Continuing Operations                     0        0              0    37,911     40,475     43,164           45,986        48,951        52,063

           Provision for State & Federal Tax                     0        0              0    32,631     34,837     37,151           39,581        42,132        44,810
           Net Income Before Dividends                           0        0              0     5,280      5,638      6,013            6,406         6,819         7,252

           Preferred Dividends                                   0        0              0         0         40         40               40            40            40
           Net Income Available to Common Shareholders           0        0              0     5,280      5,598      5,973            6,366         6,779         7,212

           Retained Earnings, Beginning of Period                                                   0    49,365     54,963           60,936        67,302        74,080
           Less: Dividends                                                                                  (40)       (40)             (40)          (40)          (40)
           Retained Earnings, End of Period                      0        0              0     5,280     54,963     60,936           67,302        74,080        81,293




STRICTLY CONFIDENTIAL --
Unaudited Preliminary Draft
For discussion and review purposes only
                                                                              16 of 18                                                                         8/18/2011, 4:23 PM
Maximize ERP Project                                                                 Financial Model                                  Statements - 113868e1-d799-4633-aa57-a29871ae1484.xls




       CASH FLOW STATEMENT (Million of Dollars)

                                                                           Pre-Implementation                                          Post-Implementation
                                                                         1996    1997       1998         1999       2000       2001           2002           2003        2004

           Net Income Before Dividends                                                                                5,638       6,013          6,406          6,819       7,252
           Adjustments to Reconcile Net Income to Cash from Operations
              Project Costs-Capitalized                                                                                  (5)          0              0              0           0
              Project Fees                                                                                               (1)         (1)            (1)            (1)         (1)
              Depreciation and Amortization                                                                           4,879       4,879          4,880          4,880       4,880
              Change in Accounts Receivable                                                                            (343)       (360)          (378)          (397)       (417)
              Change in Inventory                                                                                      (274)       (287)          (302)          (317)       (333)
              Change in Prepaid Expenses                                                                                (44)        (46)           (48)           (51)        (53)
              Change in Other Current Assets                                                                           (240)       (251)          (264)          (277)       (291)
                 Change in Total Current Assets                              0        0              0          0      (900)       (945)          (992)        (1,042)     (1,094)
              Change in Interest Payable                                                                                  0           0              0              0           0
              Change in Accounts Payable                                                                                384         403            423            444         467
              Change in Current Portion of Long Term Debt                                                                 0           0              0              0           0
              Accrued Expenses and Bonuses                                                                                0           0              0              0           0
              Change in Customer Deposits                                                                                 0           0              0              0           0
                 Change in Total Current Liabilities                         0        0              0          0       384         403            423            444         467
              Change in Other Noncurrent Assets                                                                        (127)       (133)          (140)          (147)       (154)
              Change in Deferred Income Tax                                                                               0           0              0              0           0
              Change in Deferred Taxes Payable                                                                            0           0              0              0           0
                 Cash Provided (Used) by Operating Activities                0        0              0          0     9,869      10,217         10,576         10,954      11,350

           Investing Activities
              Capital Expenditures:                                                                                        0          0              0              0           0
                  Cash Provided (Used) by Investing Activities               0        0              0          0          0          0              0              0           0

           Financing Activities
              Change in Long-Term Notes                                                                                   0           0              0              0           0
              Change in Notes Payable                                                                                     0           0              0              0           0
              Preferred Dividends                                                                                       (40)        (40)           (40)           (40)        (40)
                  Cash Provided (Used) by Financing Activities                                                          (40)        (40)           (40)           (40)        (40)
              Net Cash Flow Available to Common Shareholders                 0        0              0          0     9,829      10,177         10,536         10,914      11,310

              Dividend Distributions to Common Shareholders                                                               0           0              0              0           0
              Net Cash Flow                                                  0        0              0          0     9,829      10,177         10,536         10,914      11,310



              Cash, Beginning Balance                                                                                 1,652      11,481         21,658         32,194      43,108
              Cash, Ending Balance                                           0        0              0          0    11,481      21,658         32,194         43,108      54,418




STRICTLY CONFIDENTIAL --
Unaudited Preliminary Draft
For discussion and review purposes only
                                                                                          17 of 18                                                                       8/18/2011, 4:23 PM
Maximize ERP Project                                                                        Financial Model                                     Analyses - 113868e1-d799-4633-aa57-a29871ae1484.xls



     ANALYSIS

                                                                              Pre-Implementation                                                        Post-Implementation
        Financial and Operating Ratios                                 1996          1997        1998          1999       Benchmark   2000       2001          2002         2003          2004

           Liquidity Ratios
              Current Ratio (Current Assets/Current Liabilities)                                                    0.7                   1.1         1.5           1.9         2.3            2.7
              Quick Ratio (Cash, Equivalents and Accounts Receivable/Current Liabilities)                           0.3                   0.7         1.1           1.4         1.8            2.2
              Working Capital (Current Assets Less Current Liabilities)                                         (7,094)                3,251      13,970        25,075      36,586         48,523

           Activity Ratios
              Sales/Average Receivables                                                                           32.4                  16.6        16.6          16.6        16.6           16.6
                  Days in Average Receivables                                                                     11.3                  22.0        22.0          22.0        22.0           22.0
              Cost of Sales/Average Inventory                                                                     21.9                  11.2        11.2          11.2        11.2           11.2
                  Days in Average Inventory                                                                       16.6                  32.5        32.5          32.5        32.5           32.5
              Cost of Sales/Average Accounts Payable                                                              15.6                   8.0         8.0           8.0         8.0            8.0
                  Days in Average Accounts Payable                                                                23.4                  45.6        45.6          45.6        45.6           45.6
              Working Capital Turnover (Net Sales Revenue/(Average Working Capital)                              (15.7)                 35.9        14.2           6.6         4.4            3.3
              Fixed Asset Turnover (Net Sales Revenue/Average Net Fixed Assets)                                    3.6                   2.0         2.2           2.6         3.0            3.5
              Asset Turnover (Net Sales Revenue/Average Total Assets)                                              2.6                   1.3         1.3           1.3         1.3            1.2

           Profitability Ratios
              Net Income/Average Shareholder’s Equity                                                             0.30                  0.15        0.14          0.13        0.12           0.11

           Growth
              Sales Growth                                                                                                                5%          5%            5%          5%             5%
              EPS                                                                                                 2.17                  2.32        2.48          2.64        2.81           2.99

           Cost
              NI/Sales (Profit Margin)                                                                             5%                     5%          5%            5%          5%             5%
              Gross Margin                                                                                        46%                    46%         46%           46%         46%            46%
              SG&A as a % of Sales                                                                                 7%                     7%          6%            6%          6%             5%

           Capital
              Assets/Sales                                           #DIV/0!      #DIV/0!       #DIV/0!            76%                   77%         79%           80%         82%            83%
              SVA                                                          0            0             0         32,171                34,175      36,266        38,453      40,739         43,130

           Project
              ROI                                                    #DIV/0!      #DIV/0!       #DIV/0!            15%                   14%         13%           12%         11%            11%
              NPV                                                                                              $39,729
              IRR                                                                                             #NUM!




STRICTLY CONFIDENTIAL --
Unaudited Preliminary Draft
For discussion and review purposes only
                                                                                               18 of 18                                                                               8/18/2011, 4:23 PM

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:16
posted:8/18/2011
language:English
pages:18
Description: Stock Analysis Template document sample