How to Calculate Percentage Increase - Download as Excel by vkv75576

VIEWS: 0 PAGES: 7

More Info
									                                              80488696-0fad-4d42-96b3-8020ac8c8817.xls



                                Existing Condition                       After Improvements            Legend
                                                                                              Type original
                                                             Increase/Decrease
Annual Revenue                                                     Sales
                                                                                              estimates in each rose
                                                                                              field
                                                                                              Type assumed
                                                             Increase/Decrease
Scrap Rate as % of Sales                                           Scrap
                                                                                              impacts to each factor
                                                                                              in green fields
                                                                                              DO NOT TYPE -
Direct Labor as a % of                                       Increase/Decrease
                                                                                              Formulas will
Sales                                                           Productivity
                                                                                              calculate
                                                                                              DO NOT TYPE -
All Overhead Labor as a %                                    Increase/Decrease
                                                                                              Formulas will
of Sales                                                        Productivity
                                                                                              calculate

                                                             Increase/Decrease
Material as a % of Sales                                     Material % Content

                               Equals
                                                         Formula assumes that half of
                               Sales -
                                                            the other expenses will
                              (Scrap +
All other expenses             Labor +
                                                           increase in same ratio as
                                                         sales increase and the others
                             Material +
                                                                will remain fixed
                             Net Profit)


Net Profit as a % of Sales

                                            Net Increase/Decrease in Profit

                                                            Increase/Decrease in
                                                           inventory investment?
    ROI Calculations         Rough Cash Impactors
                                                        Increase/Decrease in Cash
                                                               into Cap Ex

                               Estimated Cash Increase/Decrease (assuming no change in
                                          AP/AR payment/collection practices)




Revision B                                                     Worksheet                              prepared by Cristi Cristich
             80488696-0fad-4d42-96b3-8020ac8c8817.xls




Revision B                  Worksheet                   prepared by Cristi Cristich
                                                   80488696-0fad-4d42-96b3-8020ac8c8817.xls



                                Existing Condition                      After Improvements                                 Legend
                                                                                                              Type original
                                                             Increase/Decrease
Annual Revenue                       $10,000,000                                        10%    $11,000,000 estimates in each
                                                                   Sales
                                                                                                              rose field
                                                                                                              Type assumed
                                                             Increase/Decrease
Scrap Rate as % of Sales        3%           $300,000              Scrap
                                                                                        -10%      $297,000 impacts to each factor
                                                                                                              in green fields
                                                                                                              DO NOT TYPE -
Direct Labor as a % of                                       Increase/Decrease
                               15%          $1,500,000          Productivity
                                                                                        -10%    $1,485,000 Formulas will
Sales                                                                                                         calculate
                                                                                                              DO NOT TYPE -
All Overhead Labor as a %                                    Increase/Decrease
                               20%          $2,000,000          Productivity
                                                                                        -10%    $1,980,000 Formulas will
of Sales                                                                                                      calculate

                                                             Increase/Decrease
Material as a % of Sales       20%          $2,000,000       Material % Content
                                                                                        0%      $2,200,000

                               Equals
                                                         Formula assumes that half of
                               Sales -
                                                            the other expenses will
                              (Scrap +
All other expenses            Labor +
                                            $3,200,000     increase in same ratio as            $3,360,000
                                                             sales increase and the
                             Material +
                                                            others will remain fixed
                             Net Profit)


Net Profit as a % of Sales     10%          $1,000,000                                          $1,678,000

                                            Net Increase/Decrease in Profit                       $678,000

                                                            Increase/Decrease in
                                                           inventory investment?
                                                                                                         $0
     ROI Calculations        Rough Cash Impactors
                                                           Increase/Decrease in
                                                             Cash into Cap Ex
                                                                                                  $100,000

                               Estimated Cash Increase/Decrease (assuming no change in
                                          AP/AR payment/collection practices)
                                                                                                  $578,000




Revision B                                                     Completed Example                                                Prepared by Cristi Cristich
                                             80488696-0fad-4d42-96b3-8020ac8c8817.xls



                                            Step 1 - Complete Baseline Information

                                 Existing Condition                                   Type in rose fields only!

Annual Revenue                                                                Type the annual revenue in gross dollars


                                                             Type the estimated scrap rate expressed as a percentage of sales; if you
                                                             only know the amount of scrap you had as an actual number, divide that
Scrap Rate as % of Sales                                      number by your gross sales on a calculator and they type the resulting
                                                                                  percentage into the red field


Direct Labor as a % of
                                                               Type the estimated labor percentages - direct labor & overhead labor
Sales                                                            expressed as a percentage of gross sales. If you do not know the
                                           DO NOT TYPE
                                                             breakdown between direct and overhead just put the total number in direct
All Overhead Labor as a %                    IN YELLOW                      labor and skip the overhead labor section.
of Sales                                       AREAS.
                                           THESE FIELDS
                                            CALCULATE.        Type the estimated material percentage content your company has in its
Material as a % of Sales                                                              products on average,
                               Equals
                               Sales -
                              (Scrap +
All other expenses             Labor +
                                                            This field will automatically calculate once all other red areas are completed.
                             Material +
                             Net Profit)

Net Profit as a % of Sales                                            Type in your estimated percentage of net, pre-tax profit.




                                                          Work Instruction
Revision B                                                    4 of 7                                                   prepared by Cristi Cristich
                                                    80488696-0fad-4d42-96b3-8020ac8c8817.xls



                                         Step 2 - Calculate Possible Profit & Cash Impacts By Factor

                      After Improvements                                                      Type in green fields only!
                                                                    Type a percentage change in sales - increase is a positive percentage and
  Increase/Decrease        Sales
                                                                                      decrease is a negative percentage.
                                                                    Type a percentage change in scrap - increase is a positive percentage and
  Increase/Decrease        Scrap
                                                                                      decrease is a negative percentage.

 Increase/Decrease Productivity                 DO NOT TYPE      Type a percentage change in productivity for both labor categories -
                                                  IN YELLOW  increased productivity is a negative percentage and decrease is a positive
                                                    AREAS.                                   percentage.
 Increase/Decrease Productivity
                                                THESE FIELDS
  Increase/Decrease Material %                   CALCULATE. Type a percentage change in scrap - increase is a positive percentage and
            Content                                                                      decrease is a negative percentage.
  Formula assumes that half of the
                                                                    This field calculates based on the assumptions in the grey column on the
   other expenses will increase in
                                                                    left. If you know how much in this category is variable tied to sales and
same ratio as sales increase and the
      others will remain fixed
                                                                    how much is fixed you can attempt to alter this but it is not recommended.

                               Step 3 - View Total Net Income Impact & Estimate Cash Impact for ROI Analysis

                                                DO NOT TYPE -          Type in green fields only! This field will calculate once all the red and
       Net Increase/Decrease in Profit
                                                WILL CALCULATE              green fields above this step have been properly populated.

                                                                    If you think your total inventory will go up to service the additional sales you
 Increase/Decrease in inventory
                                                                       anticipate or (hopefully) go down due to your new improved lean culture
          investment?
                                                                       type the total number of change estimated - higher inventory is positive.

                                                                     If you think you will have to invest some of your cash into acquiring cap ex
 Increase/Decrease in Cash into                                         or servicing additional debts incurred as a result of capital investments
            Cap Ex                                                  financed to achieve the sales change you assumed then type it here. Keep
                                                                         in mind that most early kaizens require little or no capital investment.

     Estimated Cash Increase/Decrease
                                                DO NOT TYPE -       Type in green fields only! This field will reflect an accurate value once ALL
      (assuming no change in AP/AR
                                                WILL CALCULATE                  red & green fields have been properly populated.
       payment/collection practices)


                                                                  Work Instruction
Revision B                                                            5 of 7                                                    prepared by Cristi Cristich
             80488696-0fad-4d42-96b3-8020ac8c8817.xls




                         Work Instruction
Revision B                   6 of 7                     prepared by Cristi Cristich
             80488696-0fad-4d42-96b3-8020ac8c8817.xls




                         Work Instruction
Revision B                   7 of 7                     prepared by Cristi Cristich

								
To top