04 ROCE

Document Sample
04 ROCE Powered By Docstoc
					Welcome to the ROCE WorkBook
Introduction :
  1 This tool is now provided to you to find out the ROCE of your Distributor
  2 To find out the health of the business
  3 To take the required corrective measurements, if any


Instructions:
  1 This workbook is having interlinked worksheets
  2 You are suggested to keep a copy of this workbook in a safe place, so that in case of any file corruption, you can use the
  3 The first 3 worksheets, i.e., "Read Me First", "About ROCE" and "ROCE vs. P&L" are mainly to make you aware about
  4 The primary worksheet in this workbook, is "Sale Summary", where all the calculations gets consolidated and shows the
  5 Start working on this workbook by filling up the the cells of "Sale Summary" Sheet
  6 Most of the cells in this workbook is "locked" to minimise chances of file and formula corruption.
  7              You are supposed to fill the cells highlighted with "yellow"
  8              Watch for cells with comments. Instructions are provided on action to be taken on that cell.
  9 Some worksheets are provided with additional Guidelines and Instructions. Read them carefully before working on that
 10 Maintain separate workbook for each and every Distributor in yoir respective area.
 that in case of any file corruption, you can use the copy.
OCE vs. P&L" are mainly to make you aware about ROCE and this workbook.
 l the calculations gets consolidated and shows the result.


 le and formula corruption.


 n action to be taken on that cell.
 tions. Read them carefully before working on that sheet.
What is ROCE ? And why calculate it ?

 ROCE = Return On Capital Employed
 ROCE determines the viability of the business
 ROCE = (net margin-cost) * rotations or stock turns
 ROCE must be well above the commercial bank prime lending rate at the time after bank inter



Return on Capital Employed

Step 1 : Calculating Net Margin

     Gross
     Margin                             Includes

                       •Direct People - Only those required          •Managers Costs
                       •Direct Vehicle - Converted to Revenue Cost   •Extra Manpower not require
                       •Direct Billing Cost                          •Higher Costs of Manpower
                       •Direct Telephone                             •Distributor luxuries at office
                       •Shared Electricity                           •Office Space
    Cost of            •Direct Insurance - Stocks / Vehicle          •Central Overhead Apportion
    Operatio           •Shared Computer Operator                     •Opportunity Cost of Owned
       n               •Direct Accounts Staff - As specified         •Overdraft Interest
                       •Direct Warehouse Staff - As required         •Notional Costs
                       •Overheads directly related to business
                       •Petty Cash expenses - Refreshments / Water
                       •Rental - If space is hired
                       •Bank Interest on Working Capital
      Net              •Bank Guarantee Cost
     Margin




Step 2 : Calculating Rotations

                                      52 Weeks
        Rotations
                                Weeks of (Stock + Credit)




Step 3 : Calculating ROCE
                                               Cost of
                         Cost of                52
                Gross
       ROCE              Operatio
                Margin               Weeks of (Stock + Credit)
                            n




or
               Net
     ROCE                Rotations
              Margin
ing rate at the time after bank interest




                           Excludes

           Managers Costs - Not required
           Extra Manpower not required
           Higher Costs of Manpower
           Distributor luxuries at office
           Office Space
           Central Overhead Apportionment
           Opportunity Cost of Owned Space
           Overdraft Interest
           Notional Costs
           52
Weeks of (Stock + Credit)
                                                                           Balance Sheet
                                      ROCE           Profit & Loss (P&L)
                                                                               (B/S)
Rent of Office / Warehouse       Warehouse - Hired          Yes
Cost of Chairs / Tables                                                        Yes
Vehicles / Computers             As Revenue Cost                               Yes
Depriciation                         Yes - Life         Yes - 4 Years
Salvage                              Yes - Life         Yes - 1 Time
Fuel                                   Yes                  Yes
Maintenance                            Yes                  Yes
Manpower Cost                          Yes                  Yes
Billing Cost                           Yes                  Yes
Turnover Taxes                         Yes                  Yes
Income Taxes                            No                  Yes
Banking Cost : Interest                Yes                  Yes
Banking Cost : Bank Guarantee          Yes                  Yes
Telephone                              Yes                  Yes
ElectricityY                           Yes                  Yes
Stock                                  Yes                                     Yes
Owners OfficeE                                              Yes
Owners Vehicle - Official                                                      Yes
Running Cost of Owners Vehicle                              Yes
Cost of Credit in Market               Yes                                     Yes
Cost of Stock Rotations                Yes
Overdraft Cost                                              Yes
Manpower above norms                                        Yes
Managers Cost                                               Yes
Claims                                 Small
Cost / Person                      As per norms            Actual
Notional / Opportunity Costs            No                   No                 No
  Distributor :                            <Insert Distributor Name here>
  Location :                               <Insert Distributor Location here>
                                                                                                   Sandisk
                                                                                                                    Sandisk
                                                TOTAL             Kodak            iPod            Memory
                                                                                                                     USB
                                                                                                    Card
  @ Last 6 months volume in pieces                  10,047                  916           1012            2722             4811
  @ Last 6 months value in Rs. :
                                              Rs.11,649,000      Rs.3,776,000     Rs.4,954,000      Rs.667,000      Rs.1,316,000
  Distributor Sale price including VAT
  Mark Up                                        Rs.411,103        Rs.145,231      Rs.144,291        Rs.16,268        Rs.32,098
  Distributor Mark Up % on Purchase
                                                        3.66              4.00            3.00            2.50             2.50
  Price
  @ Last 6 months value in Rs. - HCL
                                              Rs.11,237,897      Rs.3,630,769     Rs.4,809,709      Rs.650,732      Rs.1,283,902
  Sale Price including VAT
  VAT%                                                  7.9%           12.5%              5.0%            5.0%             5.0%

  @ Last 6 months value in Rs. - HCL
                                              Rs.10,414,652      Rs.3,227,350     Rs.4,580,675      Rs.619,744      Rs.1,222,764
  Sale Price excluding VAT - Realisation


                                              With Bank                                          Without Bank
                                                                % DB Cost                                          % DB Cost
                                              Guarantee                                           Guarantee
  Cost / Month                                  Rs.199,674              1.71                        Rs.224,847            1.93
  People                                          Rs.66,690            0.57%                         Rs.66,690           0.57%
  Vehicles                                        Rs.14,393            0.12%                         Rs.14,393           0.12%
  Computers                                        Rs.8,330            0.07%                           Rs.8,330           0.07%
  Space Rental                                       Rs.120            0.00%                             Rs.87            0.00%
  Goods Insurance                                  Rs.7,960            0.07%                           Rs.5,155           0.04%
  Bill Cost                                          Rs.248            0.00%                            Rs.248            0.00%
  Telephone / Fax                                  Rs.2,013            0.02%                           Rs.2,013           0.02%
  Broadband                                          Rs.189            0.00%                            Rs.189            0.00%
  Electricity                                      Rs.1,134            0.01%                           Rs.1,134           0.01%
  Provision for Bad Debts                         Rs.46,596            0.40%                         Rs.46,596            0.40%
  Miscellaneous                                   Rs.11,649            0.10%                         Rs.11,649            0.10%
  Bank Guarantee                                   Rs.4,368            0.04%                                              0.00%
  Bank Interest - Cost of Capital                 Rs.35,987            0.31%                         Rs.68,365            0.59%
  Working Capital - Rs.                                          Rs.3,084,559                                       Rs.5,859,837
  Working Capital - Weeks                                                   1.0                                                2.0
  Bank Guarantee Value - Rs.                                     Rs.8,735,457
  Weighted @ Stock - Days                                                   15                                                 10
  Weighted @ Stock - Value in Rs.                                Rs.5,685,439                                       Rs.3,682,241
  Credit - Days                                                             23                                                 11
  Rotations - No. of Times / Year                                          53                                                  26
  Net Margin from Business - %                                            1.9                                                 1.7
  Net Margin from Business - Rs.                                   Rs.218,476                                        Rs.194,191
  ROCE Per Annum - Rs.                                           Rs.3,181,310                                       Rs.2,582,894
  ROCE - %                                                                  103                                                44

  ROCE will be higher with Bank Guarantee - A Win-Win situation for all
With Own Funds Lying Idle
  Cost / Month - Rs.                                               Rs.163,687                                        Rs.156,482
  Cost as % of Distributors Sale                                          1.41                                             1.34
  Net Margin from Business - %                                            2.25                                                1.16
  Net Margin from Business - Rs.                                   Rs.253,192                                        Rs.129,988
  ROCE - Rs.                                                   Rs.368,683,089                                     Rs.172,893,926
Distributor :                       <Insert Distributor Name here>
Location :                          <Insert Distributor Location here>
Distributor Business Size / Month         Current          HCL Business
                                         Rs.50,000,000       Rs.11,649,000
Share of Business %                               19%

Order Taking                            2 per Week            Weekly             12 Days
Outlets to be covered                                10                   600
Visits per Month                                      8                     4                2
Calls per Month                                      80                  2,400               0
Calls per Day per Person                             15                    30               30
Working Days                                         25                    25               25
Total Required                                       0.2                   3.2             0.0
Productivity %                                      65%                   50%              50%
Bills / Month                                         1                    12                0
Bills / Day                                           0                     0                0
Round off to the highest number
 Total
Rs.61,649,000


26 Days         TOTAL
            0            610
            1
            0           2,480
           30             29
           25
          0.0           3.413   3
          50%            50%
            0             13
            0              1
Distributor :                             <Insert Distributor Name here>
Location :                                <Insert Distributor Location here>
People Cost                                  Number          Salary/Month      Fuel Cost         Bonus        Max Incentive   Likely Incentive   Per Head
Salesman                                              3.0         Rs.7,000         Rs.1,250          Rs.583        Rs.1,750          Rs.1,225      Rs.10,058
Supervisor Rounded                                    0.4        Rs.10,500         Rs.1,875          Rs.875        Rs.2,625          Rs.1,838      Rs.15,088
Tele-Sell Sales Girl                                  1.0         Rs.7,000                           Rs.583        Rs.1,750          Rs.1,225       Rs.8,808
Computer Operator (@200 Bills/Operator)               2.0        Rs.13,500                         Rs.1,125                              Rs.0      Rs.14,625
Driver                                                0.6        Rs.10,000                           Rs.833        Rs.2,500          Rs.2,000      Rs.12,833
Truck Helper                                          0.6         Rs.6,000                           Rs.500        Rs.1,500          Rs.1,200       Rs.7,700
Shared Finance Resources                              0.3        Rs.15,000                         Rs.1,250                                        Rs.16,250
Total                                                  7                                                                                          Rs.85,363
                                                            These are indicative costs and bonuses. Minor changes are allowed.

Guidelines / Assumptions:
Sales Manager - not required
Monthly Total
     Rs.30,175
                 Exclusive
      Rs.5,658
                 Manpower
      Rs.8,808
      Rs.5,534
                   Cost of
      Rs.7,275
                 Apportioned
      Rs.4,365    Manpower
      Rs.4,875
    Rs.66,690
Distributor :                    <Insert Distributor Name here>
Location :                       <Insert Distributor Location here>
                                     3 Wheeler            4 Wheeler           Computer
Number of Vehicles / Computers                   0.57                  0.00              2.0
Vehicle Recovery Cost / Month              Rs.9,417               Rs.7,328        Rs.3,120
KM Travelled / Month                           1750                   1750
KM Travelled / Day                                70                    70
Fuel Rate / Litre                             Rs.44                   Rs.44
Mileage / Litre - KM                               6                     6
Litres / Month                                   292                   292
Fuel Cost / Month                         Rs.12,833            Rs.12,833
Maintenance / Month                        Rs.3,139               Rs.2,443        Rs.1,040
Vehicle Cost / Month : Total              Rs.14,393                    Rs.0       Rs.8,330
Vehicle Cost / Month / Vehicle            Rs.25,390        #DIV/0!                Rs.4,160
Vehicle Cost / Day / Vehicle               Rs.1,016        #DIV/0!                  Rs.166
Distributor :                          <Insert Distributor Name here>
Location :                             <Insert Distributor Location here>
Space Cost

                                          TOTAL            Kodak        iPod


@ Pieces / Case                                                    20           25
@ Last 6 Months Volume of Pieces             10,047             916         1012
@ Last 6 Months Volume of Cases                   151              46          40
With Bank Guarantee
Stock Level needed in Days                          15             21           15
Stock on Floor                                                     32           20
Space in Square Feet                                9           3.8            2.4
Rate / Square Feet / Month                       Rs.14
Space Cost / Month                           Rs.120
Without Bank Guarantee
Stock Level needed in Days                         10              15           7
Stock on Floor                                                     23            9
Space in Square Feet                                6           2.7            1.1
Rate / Square Feet / Month                       Rs.14
Space Cost / Month                               Rs.87



Guidelines / Assumptions:
Area / Space Conversion
Number of Cases                                     50
Space required in Sq. Feet                           6


Approximate Rent per Square Feet
Big Cities                                       Rs.14
Other Cities                                      Rs.7


Owned Space - Opportunity Cost cannot be taken


Insurance cost of Goods                   With BG        Without BG
Rate per Month                                   0.14%        0.14%
Distributor :                     <Insert Distributor Name here>
Location :                        <Insert Distributor Location here>
Miscellaneous Cost
                                         Rate               Number                 Cost
Bill Cost
  Computerised                                    0.6                  413                Rs.248
  Manual                                          0.4                  413                Rs.165
Telephone / Month                                                                      Rs.2,013
Broadband / Month                                                                         Rs.189
Electricity - Shared / Month                Rs.6,000                                   Rs.1,134
Miscellaneous / Month                                                                Rs.11,649
Provision for Bad Debts / Month                                                      Rs.46,596
Bank Guarantee Cost
  Value of Bank Guarantee                Rs.8,735,457
  Cost %                                        0.60%                                  Rs.4,368

Bank Interest - With Bank Guarantee
Average Working Capital                  Rs.3,084,559
Rate of Interest per Annum                         14                        Guidelines / Assumptions:
Rate of Interest per Month                       1.17
Cost of Capital                            Rs.35,987                         B6 or B7, cancel 1 of 2 - manual or computerised
Bank Interest - Without Bank Guarantee                                       Provision for Bad Debts / Month = 0.4% of Sales
Average Working Capital                  Rs.5,859,837                        Value of Bank Guarantee = Value of credit received from
Rate of Interest per Annum                         14                        If Distributor uses own capital, he gets extra margin.
Rate of Interest per Month                       1.17
Cost of Capital                            Rs.68,365
 Distributor :                 <Insert Distributor Name here>
 Location :                    <Insert Distributor Location here>
 Contribution to Business and Credit Days
                                                                                   Sandisk
                                   TOTAL            Kodak             iPod         Memory
                                                                                    Card
Claim %                                                     5%               1%
Claim Value                        Rs.308,740       Rs.188,800        Rs.49,540          Rs.0
 Large Dealer %                                             50%              50%         50%
 Medim Dealer %                                             15%              15%         15%
 Weekly %                                                   20%              20%         20%
 Cash %                                                     15%              15%         15%
Sale Value                      Rs.11,649,000      Rs.3,776,000     Rs.4,954,000   Rs.667,000
 Large Dealer                    Rs.5,804,900      Rs.1,888,000     Rs.2,477,000   Rs.333,500
 Medim Dealer                    Rs.1,747,350       Rs.566,400       Rs.743,100    Rs.100,050
 Weekly                          Rs.2,327,000       Rs.755,200       Rs.990,800    Rs.133,400
 Cash                            Rs.1,769,750       Rs.566,400       Rs.743,100    Rs.100,050
Credit Days in Market
 Large Dealer                                                30               21             15
 Medim Dealer                                                14               14              0
 Weekly                                                       7                7              0
 Cash                                                         0                0              0
Credit Value in Market           Rs.5,825,837      Rs.2,416,640     Rs.2,427,460   Rs.166,750
 Large Dealer                    Rs.4,431,530      Rs.1,888,000     Rs.1,733,900   Rs.166,750
 Medim Dealer                      Rs.943,880       Rs.352,427       Rs.462,373          Rs.0
 Weekly                            Rs.450,427       Rs.176,213       Rs.231,187          Rs.0
 Cash                                       Rs.0
Weighted @ Credit Days                       15             19               15               8
Purchase with VAT from HCL      Rs.11,237,897      Rs.3,630,769     Rs.4,809,709   Rs.650,732

 With Bank Guarantee
 Credit Days Received                        23              30               21             14
 Credit Days Received in Rs.     Rs.8,735,457      Rs.3,630,769     Rs.3,366,796   Rs.303,675
 Average Stock Days                          15              21               15              5
 Stock Value                     Rs.5,685,439      Rs.2,541,538     Rs.2,404,854   Rs.108,455
 Working Capital in Days                      7             10                9              -2
 Working Capital in Weeks                   1.0             1.5              1.2         -0.2
 Working Capital in Rs.          Rs.3,084,559      Rs.1,516,209     Rs.1,515,058    -Rs.28,470
 Rotations                                   53             36               42          -243
 Without Bank Guarantee
 Credit Days Received                        11              21                7             -2
 Credit Days Received in Rs.     Rs.3,956,980      Rs.2,541,538     Rs.1,122,265    -Rs.43,382
 Average Stock Days                          10              15                7              5
 Stock Value                     Rs.3,682,241      Rs.1,815,385     Rs.1,122,265   Rs.108,455
 Working Capital in Days                     14             13               15              15
Working Capital in Weeks                      2.0              1.9             2.1             2.1
Working Capital in Rs.              Rs.5,859,837      Rs.1,879,286     Rs.2,477,000     Rs.318,587
Rotations                                     26                28              25              25



Guidelines / Assumptions:
Credit Days : Take Contractual
To show what the Distributor is taking actually, insert REAL Credit taken
Average Stock taken as Distributor Stock is NOT always 4 weeks : Physical + Transit
Rotations measures the number of times the same working capital was used in a year to generate the sale of the year.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:8
posted:11/15/2011
language:English
pages:20