04 ROCE by shrey420

VIEWS: 3 PAGES: 30

									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                             HCL
                                                                                                                    Sandisk
                                                TOTAL             Kodak            iPod            Memory                            Education      HCL Gaming
                                                                                                                     USB
                                                                                                    Card                              Laptop
  @ Last 6 months volume in pieces                  10,047                  916           1012            2722             4811                              583
  @ 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                       Rs.880,000
  Distributor Sale price including VAT
  Mark Up                                        Rs.411,103        Rs.145,231      Rs.144,291        Rs.16,268        Rs.32,098              Rs.0       Rs.72,661
  Distributor Mark Up % on Purchase
                                                        3.66              4.00            3.00            2.50             2.50             9.00            9.00
  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             Rs.0      Rs.807,339
  Sale Price including VAT
  VAT%                                                  7.9%           12.5%              5.0%            5.0%             5.0%            13.5%           13.5%

  @ 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             Rs.0      Rs.711,312
  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%                     Observations:
  Miscellaneous                                   Rs.11,649            0.10%                         Rs.11,649            0.10%
  Bank Guarantee                                   Rs.4,368            0.04%                                              0.00%                     Bank Guarantee cost is cheap a
  Bank Interest - Cost of Capital                 Rs.35,987            0.31%                         Rs.68,365            0.59%                     Working Capital cost is higher w
  Working Capital - Rs.                                          Rs.3,084,559                                       Rs.5,859,837                    ROCE will be higher with Bank G
  Working Capital - Weeks                                                   1.0                                                2.0                  Own funds used can increase R
  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
  ROCE - %                                                                  120                                                30

  Own funds used can increase ROCE dramatically - especially when there is no Bank Guarantee
                                              <New Principal   <New Principal   <New Principal   <New Principal
                Nintendo         TFT
                                                 No.1>            No.2>            No.3>            No.4>

                                          3

                                  Rs.56,000

                       Rs.0         Rs.554              Rs.0             Rs.0             Rs.0             Rs.0

                       4.00            1.00


                       Rs.0       Rs.55,446             Rs.0             Rs.0             Rs.0             Rs.0

                      13.5%            5.0%


                       Rs.0       Rs.52,805             Rs.0             Rs.0             Rs.0             Rs.0




Observations:

Bank Guarantee cost is cheap and drives ROCE
Working Capital cost is higher without Bank Guarantee. High Working Capital can destroy ROCE
ROCE will be higher with Bank Guarantee - A Win-Win situation for all
Own funds used can increase ROCE dramatically - especially when there is no Bank Guarantee
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
Tele Sell
Calls / Day                                           80
Calls Planned / Day / Person                          80
Calls / Month                                       2000
Productivity                                        20%
Bills / Month                                        400
Girls Needed                                           1
 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
GM / Branch Manager / Finance Manager, etc., are Organisational Costs, not related to business
Manager : Not taken. Managers are hired because owner cannot manage business or is using his time elsewhere - hence, manager cost to be debited to the business where the owner sits
Owner salary not taken - even if called GM, etc.
Computer Operator : Direct Manpower, if bills are >200/day. Additionally he will do Reports, Data Entry, etc.
Delivery - 2 Trucks for 3 or 1 Truck for 2
Driver does collection
Fuel for Bikes : Taken as Salesman salary per Day
                      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




the business where the owner sits
 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


Calculations:
                                                   3 Wheeler             4 Wheeler          Computer + Printer
 Life (Years)                                           6                    8                      3
 Purchase Price                                        Rs.325,000           Rs.350,000              Rs.55,000
 Interest Per Annum Reducing Balance                             14                    14
 Instalment Months                                               24                    24
 Interest : Year 1                                      Rs.45,500             Rs.49,000                     Rs.0
 Interest : Year 2                                      Rs.22,750             Rs.24,500                     Rs.0
 Total Interest                                         Rs.68,250             Rs.73,500                     Rs.0
 Interest / Year                                        Rs.11,375                Rs.9,188                   Rs.0
 Total Interst / Month                                       Rs.948                Rs.766                   Rs.0
 Depreciation / Year                                    Rs.54,167             Rs.43,750             Rs.18,333
 Depreciation / Month                                       Rs.4,514             Rs.3,646               Rs.1,528
 Salvage Value                                          Rs.65,000             Rs.70,000
 Salvage / Year                                         Rs.10,833                Rs.8,750                   Rs.0
 Salvage / Month                                             Rs.903                Rs.729                   Rs.0
 Purchase Value / Month                                     Rs.4,514             Rs.3,646               Rs.1,528
 Insurance Rate % Per Annum Reducing Balance                   2.10                  2.10                   2.10
 Insurance / Month                                           Rs.345                  Rs.0                 Rs.64
 Cost / Month                                               Rs.9,417             Rs.7,328               Rs.3,120
 Cost / Day                                                  Rs.314

 Cost of 3 Wheeler                             Depreciation Years      Depreciation %          Insurance %

 Rs.325,000.00                                          6                   16.7                   2.1
                                                                       W Depriciated
                                                  Depreciation                                  Insurance
                                                                          Value
                                                                                                        Rs.6,825
 After Year 1                                           Rs.54,167           Rs.270,833                  Rs.5,688
After Year 2                          Rs.54,167           Rs.216,667          Rs.4,550
After Year 3                          Rs.54,167           Rs.162,500          Rs.3,413
After Year 4                          Rs.54,167           Rs.108,333          Rs.2,275
After Year 5                          Rs.54,167             Rs.54,167         Rs.1,138
After Year 6                          Rs.54,167                  Rs.0                Rs.0
                                                  Total                      Rs.23,888
                                                  Per Month                       Rs.332

Cost of 4 Wheeler            Depreciation Years     Depreciation %      Insurance %

Rs.350,000.00                        8                    12.5              2.1
                                                    W Depriciated
                               Depreciation                              Insurance
                                                       Value
                                                                              Rs.7,350
After Year 1                          Rs.43,750           Rs.306,250          Rs.6,431
After Year 2                          Rs.43,750           Rs.262,500          Rs.5,513
After Year 3                          Rs.43,750           Rs.218,750          Rs.4,594
After Year 4                          Rs.43,750           Rs.175,000          Rs.3,675
After Year 5                          Rs.43,750           Rs.131,250          Rs.2,756
After Year 6                          Rs.43,750             Rs.87,500         Rs.1,838
After Year 7                          Rs.43,750             Rs.43,750             Rs.919
After Year 8                          Rs.43,750                  Rs.0                Rs.0
                                                  Total                      Rs.33,075
                                                  Per Month                       Rs.345

Cost of Computer + Printer   Depreciation Years     Depreciation %      Insurance %

Rs.55,000.00                         3                    33.3              2.1
                                                    W Depriciated
                               Depreciation                              Insurance
                                                       Value
                                                                              Rs.1,155
After Year 1                          Rs.18,333             Rs.36,667             Rs.770
After Year 2                          Rs.18,333             Rs.18,333             Rs.385
After Year 3                          Rs.18,333                  Rs.0                Rs.0
                                                  Total                    Rs.2,310.00
                                                  Per Month                   Rs.64.17
Guidelines / Assumptions:

 Delivery may be shared, hence costs could be lower
 If only 4 wheeler is wanted or a mix of 3 & 4 wheelers, then
  change cell B4
 If Computer in Decimal, that means it is shared
 25 Work Days assumed
 Vehicle Rates are with body
 All costs apportioned over life of vehicle
 If bicycles and 2 wheelers are used change Number of Vehicles
  (B4), Fuel Rate / Litre (B8) to get to cost of Delivery mode
Distributor :                            <Insert Distributor Name here>
Location :                               <Insert Distributor Location here>
Space Cost
                                                                                       Sandisk
                                                                                                     Sandisk
                                            TOTAL           Kodak         iPod         Memory
                                                                                                      USB
                                                                                        Card
@ Pieces / Case                                                     20            25         200           200
@ Last 6 Months Volume of Pieces              10,047             916          1012         2722           4811
@ Last 6 Months Volume of Cases                   151               46           40          14            24
With Bank Guarantee
Stock Level needed in Days                          15              21            15             5             5
Stock on Floor                                                      32            20             2             4
Space in Square Feet                                 9           3.8             2.4         0.3           0.5
Rate / Square Feet / Month                       Rs.14
Space Cost / Month                             Rs.120
Without Bank Guarantee
Stock Level needed in Days                          10              15            7              5             5
Stock on Floor                                                      23             9             2             4
Space in Square Feet                                 6           2.7             1.1         0.3           0.5
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%
Cost per Month                                    7,960        5,155

Insurance is for Average Stock Holding
  HCL                                                    <New            <New            <New            <New
Education       HCL Gaming   Nintendo       TFT         Principal       Principal       Principal       Principal
 Laptop                                                  No.1>           No.2>           No.3>           No.4>
           10           24              6           1               0               0               0               0
            0          583              0           3               0               0               0               0
       -               24          -               3           -               -               -               -


           15           15              7           7               0               0               0               0
            0           12              0           1               0               0               0               0
       -               1.5         -              0.1          -               -               -               -




           15          15              10         10           -               -               -               -
            0           12              0           1               0               0               0               0
       -               1.5         -              0.1          -               -               -               -
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
Assumptions:

1 of 2 - manual or computerised
Debts / Month = 0.4% of Sales
arantee = Value of credit received from HCL
own capital, he gets extra margin.
 Distributor :                 <Insert Distributor Name here>
 Location :                    <Insert Distributor Location here>
 Contribution to Business and Credit Days
                                                                                   Sandisk
                                                                                                  Sandisk
                                   TOTAL            Kodak             iPod         Memory
                                                                                                   USB
                                                                                    Card
Claim %                                                     5%               1%
Claim Value                        Rs.308,740       Rs.188,800        Rs.49,540          Rs.0            Rs.0
 Large Dealer %                                             50%              50%         50%              50%
 Medim Dealer %                                             15%              15%         15%              15%
 Weekly %                                                   20%              20%         20%              20%
 Cash %                                                     15%              15%         15%              15%
Sale Value                      Rs.11,649,000      Rs.3,776,000     Rs.4,954,000   Rs.667,000     Rs.1,316,000
 Large Dealer                    Rs.5,804,900      Rs.1,888,000     Rs.2,477,000   Rs.333,500      Rs.658,000
 Medim Dealer                    Rs.1,747,350       Rs.566,400       Rs.743,100    Rs.100,050      Rs.197,400
 Weekly                          Rs.2,327,000       Rs.755,200       Rs.990,800    Rs.133,400      Rs.263,200
 Cash                            Rs.1,769,750       Rs.566,400       Rs.743,100    Rs.100,050      Rs.197,400
Credit Days in Market
 Large Dealer                                                30               21             15             15
 Medim Dealer                                                14               14              0              0
 Weekly                                                       7                7              0              0
 Cash                                                         0                0              0              0
Credit Value in Market           Rs.5,825,837      Rs.2,416,640     Rs.2,427,460   Rs.166,750      Rs.329,000
 Large Dealer                    Rs.4,431,530      Rs.1,888,000     Rs.1,733,900   Rs.166,750      Rs.329,000
 Medim Dealer                      Rs.943,880       Rs.352,427       Rs.462,373          Rs.0            Rs.0
 Weekly                            Rs.450,427       Rs.176,213       Rs.231,187          Rs.0            Rs.0
 Cash                                       Rs.0
Weighted @ Credit Days                       15             19               15               8              8
Purchase with VAT from HCL      Rs.11,237,897      Rs.3,630,769     Rs.4,809,709   Rs.650,732     Rs.1,283,902

 With Bank Guarantee
 Credit Days Received                        23              30               21             14             14
 Credit Days Received in Rs.     Rs.8,735,457      Rs.3,630,769     Rs.3,366,796   Rs.303,675      Rs.599,154
 Average Stock Days                          15              21               15              5              5
 Stock Value                     Rs.5,685,439      Rs.2,541,538     Rs.2,404,854   Rs.108,455      Rs.213,984
 Working Capital in Days                      7             10                9              -2             -2
 Working Capital in Weeks                   1.0             1.5              1.2         -0.2             -0.2
 Working Capital in Rs.          Rs.3,084,559      Rs.1,516,209     Rs.1,515,058    -Rs.28,470      -Rs.56,171
 Rotations                                   53             36               42          -243            -243
 Without Bank Guarantee
 Credit Days Received                        11              21                7             -2             -2
 Credit Days Received in Rs.     Rs.3,956,980      Rs.2,541,538     Rs.1,122,265    -Rs.43,382      -Rs.85,593
 Average Stock Days                          10              15                7              5              5
 Stock Value                     Rs.3,682,241      Rs.1,815,385     Rs.1,122,265   Rs.108,455      Rs.213,984
 Working Capital in Days                     14             13               15              15             15
Working Capital in Weeks                      2.0              1.9             2.1             2.1              2.1
Working Capital in Rs.              Rs.5,859,837      Rs.1,879,286     Rs.2,477,000     Rs.318,587       Rs.628,577
Rotations                                     26                28              25              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.
  HCL
                                                               <New Principal <New Principal <New Principal
Education        HCL Gaming       Nintendo        TFT
                                                                  No.1>          No.2>          No.3>
 Laptop
         8%               8%
       Rs.0         Rs.70,400           Rs.0            Rs.0            Rs.0            Rs.0           Rs.0
       50%               50%             50%            15%
       15%               15%             15%            15%
       20%               20%             20%            15%
       15%               15%             15%            55%             100%           100%           100%
       Rs.0        Rs.880,000           Rs.0      Rs.56,000             Rs.0            Rs.0           Rs.0
       Rs.0        Rs.440,000           Rs.0       Rs.8,400              Rs.0           Rs.0           Rs.0
       Rs.0        Rs.132,000           Rs.0       Rs.8,400              Rs.0           Rs.0           Rs.0
       Rs.0        Rs.176,000           Rs.0       Rs.8,400              Rs.0           Rs.0           Rs.0
       Rs.0        Rs.132,000           Rs.0      Rs.30,800              Rs.0           Rs.0           Rs.0


            21            21                 21           21
            21            21                 21           21
             7                7               7            7
             0                0               0            0               0               0              0
       Rs.0        Rs.472,267           Rs.0      Rs.13,720             Rs.0            Rs.0           Rs.0
       Rs.0        Rs.308,000           Rs.0       Rs.5,880              Rs.0           Rs.0           Rs.0
       Rs.0        Rs.123,200           Rs.0       Rs.5,880              Rs.0           Rs.0           Rs.0
       Rs.0         Rs.41,067           Rs.0       Rs.1,960              Rs.0           Rs.0           Rs.0


     #DIV/0!              16          #DIV/0!             7           #DIV/0!        #DIV/0!        #DIV/0!
       Rs.0        Rs.807,339           Rs.0      Rs.55,446             Rs.0            Rs.0           Rs.0


            30            30                 15           15
       Rs.0        Rs.807,339           Rs.0      Rs.27,723              Rs.0           Rs.0           Rs.0
            15            15                  7            7
       Rs.0        Rs.403,670           Rs.0      Rs.12,937              Rs.0           Rs.0           Rs.0
     #DIV/0!               1          #DIV/0!             -1          #DIV/0!        #DIV/0!        #DIV/0!
     #DIV/0!             0.2          #DIV/0!           -0.1          #DIV/0!        #DIV/0!        #DIV/0!
       Rs.0        Rs.138,997           Rs.0      -Rs.1,065              Rs.0           Rs.0           Rs.0
     #DIV/0!             331          #DIV/0!           -560          #DIV/0!        #DIV/0!        #DIV/0!


            15            15                              10
       Rs.0        Rs.403,670           Rs.0      Rs.18,482              Rs.0           Rs.0           Rs.0
            15            15                 10           10
       Rs.0        Rs.403,670           Rs.0      Rs.18,482              Rs.0           Rs.0           Rs.0
     #DIV/0!              16          #DIV/0!             7           #DIV/0!        #DIV/0!        #DIV/0!
#DIV/0!         2.3    #DIV/0!        1.1    #DIV/0!   #DIV/0!   #DIV/0!
  Rs.0    Rs.542,667     Rs.0    Rs.13,720     Rs.0      Rs.0      Rs.0
#DIV/0!          23    #DIV/0!         50    #DIV/0!   #DIV/0!   #DIV/0!
<New Principal
   No.4>



          Rs.0




         100%
          Rs.0
          Rs.0
          Rs.0
          Rs.0
          Rs.0




             0
          Rs.0
          Rs.0
          Rs.0
          Rs.0


       #DIV/0!
          Rs.0




          Rs.0


          Rs.0
       #DIV/0!
       #DIV/0!
          Rs.0
       #DIV/0!



          Rs.0


          Rs.0
       #DIV/0!
#DIV/0!
  Rs.0
#DIV/0!

								
To top