# How to Calculate Percentage Increase - Download as Excel by vkv75576

VIEWS: 0 PAGES: 7

• pg 1
```									                                              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

Revision B                  Worksheet                   prepared by Cristi Cristich

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

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

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

Work Instruction
Revision B                   6 of 7                     prepared by Cristi Cristich