# 04 ROCE

```					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
•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
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%
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>
Rs.50,000,000       Rs.11,649,000

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
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